1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
|
create or replace function my_concat(tableName varchar2,type varchar2)
return varchar2
is
type typ_cursor is ref cursor;
v_cursor typ_cursor;
v_temp varchar2(30);
v_result varchar2(4000):= '';
v_sql varchar2(200);
begin
v_sql := 'select COLUMN_NAME from user_tab_columns where table_name = ''' || upper(tableName) || ''' order by COLUMN_ID asc';
open v_cursor for v_sql;
loop
fetch v_cursor into v_temp;
exit when v_cursor%notfound;
if type = 'select' or type = 'insert' then
v_result := v_result ||', ' || v_temp;
elsif type = 'update' then
v_result := v_result ||', ' || v_temp || ' = ?';
elsif type = 'javabean' then
v_result := v_result ||',bean.get' || upper(substr(v_temp,1,1)) || lower(substr(v_temp,2)) || '()';
end if;
end loop;
return substr(v_result,2);
end;
create or replace procedure autoGenerateSQL(
tableName varchar2,
type varchar2,
out_result out varchar2
)
is
sql_insert varchar2(2000);
sql_update varchar2(2000);
sql_select varchar2(2000);
javabean_str varchar2(2000);
field_num integer; --字段个数
type_info varchar2(20); --参数类型判断信息
begin
sql_insert := 'INSERT INTO ' || upper(tableName) || '(' || my_concat(tableName,type) || ') VALUES (';
sql_update := 'UPDATE ' || upper(tableName) || ' SET ';
sql_select := 'SELECT ';
javabean_str := '';
type_info := '';
select count(*) into field_num from user_tab_columns where table_name=upper(tableName);
select decode(type,'insert',type,'update',type,'select',type,'javabean',type,'error') into type_info from dual;
if field_num = 0 then -- 表不存在时
out_result := '表不存在！请重新输入!';
elsif type_info = 'error' then --type参数错误时
out_result := 'type参数错误：参数必须小写，类型只能是insert、update、select、javabean之一';
elsif field_num > 0 then
if type = 'insert' then --生成insert 语句
for i in 1..field_num
loop
sql_insert := sql_insert || '?';
if i < field_num then
sql_insert := sql_insert || ', ';
end if;
end loop;
sql_insert := sql_insert || ')';
out_result := sql_insert;
elsif type = 'update' then --生成update 语句
sql_update := sql_update || my_concat(tableName,type);
out_result := sql_update;
elsif type = 'select' then --生成select 语句
sql_select := sql_select || my_concat(tableName,type) || ' FROM ' || upper(tableName) || ' A';
out_result := sql_select;
elsif type = 'javabean' then --生成javabean的get方法
javabean_str := my_concat(tableName,type);
out_result := javabean_str;
end if;
end if;
end autoGenerateSQL;
|