Oracle 常用sql语句以及存储过程记录
存储过程动态参数 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;
根据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;
根据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;
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;
(编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|