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

﹏ヽ暗。殇╰゛Y 2021-09-27 09:38 227阅读 0赞

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