1.定义：
type ref_cur is ref cursor;
2.动态cursor作为out参数 存储过程的实现
举个最简单的例子，根据table name动态获取cursor
PROCEDURE P_GET_CUR(I_TABLE_NAME IN VARCHAR2, O_REF_CUR OUT REF_CUR) AS
BEGIN
IF UPPER(I_TABLE_NAME) = 'T_BANK_ACCOUNT' THEN
OPEN O_REF_CUR FOR
SELECT BANK_ACCOUNT
FROM T_BANK_ACCOUNT
WHERE BANK_ACCOUNT IS NOT NULL;
ELSIF UPPER(I_TABLE_NAME) = 'T_FB_PAYMENT_JP' THEN
OPEN O_REF_CUR FOR
SELECT BANK_ACCOUNT
FROM T_FB_PAYMENT_JP
WHERE BANK_ACCOUNT IS NOT NULL;
END IF;
END P_GET_CUR;
3.调用,用项目中mask 口座番号的功能为例，跟大家分享下：
procedure p_bank_acco_mask(i_table_name in varchar2,
i_coulumn in varchar,
o_error_str in out varchar2,
o_result in out number,
m_has_exception in out boolean) as
v_update_sql varchar(800);
v_update_sql2 varchar(800);
i int := 1;
v_bank_account VARCHAR2(50);
v_bank_account_new VARCHAR2(50);
v_char VARCHAR(1);
v_bank_account_curs ref_cur;
v_select_sql VARCHAR2(800);
begin
o_result := PKG_LS_PUB_CODE_CST.BATCH_RESULT__SUCCESS;
BEGIN
p_get_cur(i_table_name,v_bank_account_curs);
savepoint point;
v_update_sql2 := '';
LOOP
FETCH v_bank_account_curs INTO v_bank_account;
EXIT WHEN v_bank_account_curs%NOTFOUND;
BEGIN
for i in 1 .. length(v_bank_account) LOOP
v_select_sql:='SELECT SUBSTR('||i_coulumn||','||i||',1) from '|| i_table_name ||' where '||i_coulumn||'='''||v_bank_account||''' and rownum<=1';
v_char:=f_get_char(v_select_sql);
IF v_char IS NULL THEN
RETURN;
END IF;
IF v_char='2' OR v_char='3' THEN--1
v_char:='1';
ELSIF v_char='5' OR v_char='6' THEN--4
v_char:='4';
ELSIF v_char='8' OR v_char='9' THEN--7
v_char:='7';
END IF;
v_bank_account_new:=v_bank_account_new||v_char;
END LOOP;
--should support bank_code='9900'
if i_table_name = 'T_DT_ACCOUNT_JP' then
v_update_sql := ' update ' || i_table_name || ' set ' || i_coulumn ||
' = decode( Head_Bank_Code ' || ',' ||
'''9900''' || ',' || '''12340-01111281''' || ',' ||
v_bank_account_new ||' ) where' || i_coulumn ||'='''|| v_bank_account||'''';
elsif i_table_name = 'T_CASH_BANK' then
v_update_sql := ' update ' || i_table_name || ' set ' || i_coulumn ||
' = decode( CASH_BANK ' || ',' ||
'''9900''' || ',' || '''12340-01111281''' || ',' ||
v_bank_account_new ||' ) where' || i_coulumn ||'='''|| v_bank_account||'''';
elsif i_table_name = 'T_CASH_BANK_LOG' then
v_update_sql := ' update ' || i_table_name || ' set ' || i_coulumn ||
' = decode( CASH_BANK ' || ',' ||
'''9900''' || ',' || '''12340-01111281''' || ',' ||
v_bank_account_new ||' ) where' || i_coulumn ||'='''|| v_bank_account||'''';
elsif i_table_name = 'T_AGM_AGENT' then
v_update_sql := ' update ' || i_table_name ||
' set COMM_ACCOUNT = ' || v_bank_account_new ||'' ||
' where COMM_PAY_BANK_CODE <> ''9900'' and '|| i_coulumn ||'='''|| v_bank_account||'''';
v_update_sql2 := ' update ' || i_table_name ||
' set COMM_PAY_BRANCH_CODE =''12340'', ' ||
' COMM_ACCOUNT = ''01111281'' ' ||
' where COMM_PAY_BANK_CODE = ''9900''';
elsif i_table_name = 'T_SHOSHIHARAIRIREKIJOUHOU_TBL' then
v_update_sql := ' update ' || i_table_name || ' set ' || i_coulumn ||
' = decode( substr(BANK_ACCOUNT_NO,2,4) ' ||
',' || '''9900''' || ',' || '''12340-01111281''' ||
',' || v_bank_account_new ||' ) where' || i_coulumn ||'='''|| v_bank_account||'''';
elsif i_table_name = 'T_NYUSHUTUKINRIREKIJOUHOU' then
v_update_sql := ' update ' || i_table_name || ' set ' || i_coulumn ||
' = decode( substr(KOUZA_NO,2,4) ' ||
',' || '''9900''' || ',' || '''12340-01111281''' ||
',' || v_bank_account_new ||' ) where' || i_coulumn |