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,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;