Oracle 常用sql语句以及存储过程记录

Oracle 常用sql语句以及存储过程记录

1. 存储过程动态参数

   create or replace procedure p_demo_action(a_keyarray       in array_varchar2_50,
                                                        a_valuearray     in array_varchar2_50,
                                                        a_out_success    out varchar2,
                                                        a_out_msg        out varchar2,
                                                        A_OUT_KEYARRAY   OUT ARRAY_VARCHAR2_50,
                                                        A_OUT_VALUEARRAY OUT ARRAY_VARCHAR2_50,
                                                        A_OUT_CURSOR_01 OUT SYS_REFCURSOR,
                                                        a_out_cursor_name out varchar2) is
   v_name varchar2(50);                                                     
   begin
   v_name := f_get_value_by_key_in_array(a_keyArray,a_valuearray,'tcName');//获取Java里面传递过来的参数
   a_out_success := 'true';
   a_out_msg     := 'OK';
   exception
   when others then
   rollback;
   a_out_success := 'false';
   a_out_msg     := replace(sqlerrm, 'ORA-20000:');
   end;

2. 根据key建获取对应的value值函数

   create or replace function f_get_value_by_key_in_array(a_key_array   in array_varchar2_50,
                                                      a_value_array in array_varchar2_50,
                                                      a_key         in varchar2)
   return varchar2 is
   begin
   for i in 1 .. a_key_array.count loop
   if a_key = a_key_array(i) then
     return a_value_array(i);
   end if;
   end loop;
   return '';
   end;

3. 根据sql语句生成临时表

   create or replace procedure P_CREATE_TMP_TABLE_BRO(a_select_sql varchar2,
                                                    a_table      varchar) is
   --ex:call P_CREATE_TABLE_RUN('tb_cust_0a','select * from tb_customer_0a')
   v_sql      varchar2(30000);
   v_theCount number;
   /*
   *--模块功能：根据结果集生成数据表，等同SQL Server的[select into表]功能
   */
   begin
   if (upper(substr(a_table, 0, 4)) <> 'TMP_') then
     raise_application_error(-20001,
                             '违反命名规则:系统只接受以tmp_开头命名的数据表' ||
                             upper(substr(a_table, 0, 4)));
   end if;
   
   select count(1)
     into v_theCount
     from user_tables
    where table_name = upper(a_table);
   if (v_theCount > 0) then
     v_sql := f_drop_table(a_table);
     execute immediate v_sql;
   end if;
   
   select count(1)
     into v_theCount
     from user_tables
    where table_name = upper(a_table);
   if (v_theCount = 0) then
     v_sql := 'CREATE TABLE ' || a_table || ' AS ' || a_select_sql;
     execute immediate v_sql;
   end if;
   end;