执行环境：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;