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;

测试代码

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

输出

 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 ; 

完整代码

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;