oracle 根据系统表自动生成简单的查询，修改，新增语句

执行环境：pl/sql。主要适用于java/ibatis。

创建测试表

create table task.nayi_180904_01 (
login_id varchar2(50),
user_name varchar2(50),
age number,
create_time date default sysdate,
score number
);
comment on column task.nayi_180904_01.score is 'round_2|';
insert into task.nayi_180904_01(login_id, user_name, age, score)
 values('123456', 'name', 33, 123.4567);
commit;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

测试代码

begin
 --第一个参数必须是带有用户名的完整表名。存储过程的所属用户必须有all_tab_columns，all_col_comments的查询权限
 task.nayi_test_p_180904('task.nayi_180904_01', '');
end;
1
2
3
4

输出

 select 
t1.login_id "loginId", 
t1.user_name "userName", 
t1.age "age", 
to_char(t1.create_time, 'yyyy/mm/dd') "createTime", 
round(t1.score, 2) "score" 
 from task.nayi_180904_01 t1 
 ; 
 update task.nayi_180904_01 t1 set 
t1.login_id = #params.loginId#,
t1.user_name = #params.userName#,
t1.age = #params.age#,
t1.create_time = to_date(#params.createTime#, 'yyyy/mm/dd'),
t1.score = #params.score#
 where 1 = 1 
 ; 
 insert into task.nayi_180904_01( 
login_id,
user_name,
age,
create_time,
score
 ) 
 values (
#params.loginId#,
#params.userName#,
#params.age#,
to_date(#params.createTime#, 'yyyy/mm/dd'),
#params.score#
 ) 
 ; 
 private String loginId ; 
 private String userName ; 
 private String age ; 
 private String createTime ; 
 private String score ; 
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

完整代码

create or replace procedure task.nayi_test_p_180904(in_full_name in varchar2, in_type in varchar2) is
 v_user_name varchar2(30000);
 v_table_name varchar2(30000);
 my_cur sys_refcursor;
 v_temp_str varchar2(500);
begin
 v_user_name := substr(in_full_name, 1, instr(in_full_name, '.') - 1);
 v_table_name := substr(in_full_name, instr(in_full_name, '.') + 1);
 if in_type = 's' or in_type = 'all' or in_type is null then
open my_cur for
select text from (
select ' select ' text from dual union all
select*from (
select case when t1.DATA_TYPE = 'DATE' then
 'to_char(' || 't1.' || lower(t1.COLUMN_NAME) || ', ''yyyy/mm/dd'')'
 when t1.DATA_TYPE = 'NUMBER' then
 case when instr(t2.comments, 'round_') > 0 then
 'round(' || 't1.' || lower(t1.COLUMN_NAME) || ', ' || regexp_replace(regexp_substr(t2.comments, 'round_\d+\|'), '[^0-9]+') || ') '
 else 't1.' || lower(t1.COLUMN_NAME)
 end
 else 't1.' || lower(t1.COLUMN_NAME)
 end ||
 ' "' ||
 substr(lower(t1.COLUMN_NAME), 1, 1) ||
 substr(replace(initcap(lower(t1.COLUMN_NAME)), '_', ''), 2) || '"' ||
 decode(count(1) over(), row_number() over(order by t1.COLUMN_ID), '', ',') ||
 ' '
 from all_tab_columns t1, all_col_comments t2
 where 1 = 1
 and t1.OWNER = t2.owner
 and t1.TABLE_NAME = t2.table_name
 and t1.COLUMN_NAME = t2.column_name
 and t1.TABLE_NAME = upper(v_table_name)
 and t1.OWNER = upper(v_user_name)
 order by t1.COLUMN_ID) 
union all
select ' from ' || lower(v_user_name) || '.' || lower(v_table_name) || ' t1 ' from dual
union all
select ' ; ' from dual
) t1;
 loop fetch my_cur into v_temp_str;
 exit when my_cur%notfound;
 dbms_output.put_line(v_temp_str);
 end loop;
 close my_cur;
 dbms_output.put_line('');
 dbms_output.put_line('');
 end if;
 if in_type = 'u' or in_type = 'all' or in_type is null then
open my_cur for
select*from (
select ' update ' || in_full_name || ' t1 set ' text from dual union all
select * from (
select 't1.' || lower(t1.COLUMN_NAME) || 
 ' = ' || 
 decode(t1.DATA_TYPE, 'DATE', 'to_date(', '') ||
 '#params.' || 
 substr(lower(t1.COLUMN_NAME), 1, 1) ||
 substr(replace(initcap(lower(t1.COLUMN_NAME)), '_', ''), 2) ||
 '#' || 
 decode(t1.DATA_TYPE, 'DATE', ', ''yyyy/mm/dd'')', '') ||
 decode(count(1) over(), row_number() over(order by t1.COLUMN_ID), '', ',')
 from all_tab_columns t1, all_col_comments t2
 where 1 = 1
 and t1.OWNER = t2.owner
 and t1.TABLE_NAME = t2.table_name
 and t1.COLUMN_NAME = t2.column_name
 and t1.TABLE_NAME = upper(v_table_name)
 and t1.OWNER = upper(v_user_name)
 order by t1.COLUMN_ID
 ) union all
 select ' where 1 = 1 ' from dual union all
 select ' ; ' from dual
 ) t1
 ;
 loop fetch my_cur into v_temp_str;
 exit when my_cur%notfound;
 dbms_output.put_line(v_temp_str);
 end loop;
 close my_cur;
 dbms_output.put_line('');
 dbms_output.put_line('');
 end if;
 if in_type = 'i' or in_type = 'all' or in_type is null then
open my_cur for
select*from ( 
select ' insert into ' || in_full_name || '( ' text from dual union all
select*from (
select lower(t1.COLUMN_NAME) || 
 decode(count(1) over(), row_number() over(order by t1.COLUMN_ID), '', ',') ||
 decode(mod(row_number() over(order by t1.COLUMN_ID), 5), 0, chr(10), '') 
 from all_tab_columns t1, all_col_comments t2 
 where 1 = 1
 and t1.OWNER = t2.owner
 and t1.TABLE_NAME = t2.table_name
 and t1.COLUMN_NAME = t2.column_name
 and t1.TABLE_NAME = upper(v_table_name)
 and t1.OWNER = upper(v_user_name)
 order by t1.COLUMN_ID
 )
union all 
select ' ) ' from dual
union all
select ' values (' from dual
union all
select*from (
select decode(t1.DATA_TYPE, 'DATE', 'to_date(', '') ||
 '#params.' || 
 substr(lower(t1.COLUMN_NAME), 1, 1) ||
 substr(replace(initcap(lower(t1.COLUMN_NAME)), '_', ''), 2) ||
 '#' || 
 decode(t1.DATA_TYPE, 'DATE', ', ''yyyy/mm/dd'')', '') ||
 decode(count(1) over(), row_number() over(order by t1.COLUMN_ID), '', ',') ||
 decode(mod(row_number() over(order by t1.COLUMN_ID), 5), 0, chr(10), '') 
 from all_tab_columns t1, all_col_comments t2 
 where 1 = 1
 and t1.OWNER = t2.owner
 and t1.TABLE_NAME = t2.table_name
 and t1.COLUMN_NAME = t2.column_name
 and t1.TABLE_NAME = upper(v_table_name)
 and t1.OWNER = upper(v_user_name)
 order by t1.COLUMN_ID
 )
 union all
select ' ) ' from dual
 union all
select ' ; ' from dual
) t1
; 
 loop fetch my_cur into v_temp_str;
 exit when my_cur%notfound;
 dbms_output.put_line(v_temp_str);
 end loop;
 close my_cur;
 dbms_output.put_line('');
 dbms_output.put_line('');
 end if;
 if in_type = 'j' or in_type = 'all' or in_type is null then
open my_cur for
select*from (
select ' private String ' || 
 substr(lower(t1.COLUMN_NAME), 1, 1) ||
 substr(replace(initcap(lower(t1.COLUMN_NAME)), '_', ''), 2) || ' ; '
 from all_tab_columns t1, all_col_comments t2
 where 1 = 1
 and t1.OWNER = t2.owner
 and t1.TABLE_NAME = t2.table_name
 and t1.COLUMN_NAME = t2.column_name
 and t1.TABLE_NAME = upper(v_table_name)
 and t1.OWNER = upper(v_user_name)
 order by t1.COLUMN_ID
 ) t1;
 loop fetch my_cur into v_temp_str;
 exit when my_cur%notfound;
 dbms_output.put_line(v_temp_str);
 end loop;
 close my_cur;
 end if;
end nayi_test_p_180904;