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