首页 > 代码库 > 动态Ref Cursor的定义与调用
动态Ref Cursor的定义与调用
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 ||‘=‘‘‘|| v_bank_account||‘‘‘‘;
elsif i_table_name = ‘T_ACCOUNT_APPLY_TBL‘ then
v_update_sql := ‘ update ‘ || i_table_name || ‘ set ‘ || i_coulumn ||
‘ = decode( substr(ACCOUNT_NO,1,4) ‘ ||
‘,‘ || ‘‘‘9900‘‘‘ || ‘,‘ || ‘‘‘12340-01111281‘‘‘ ||
‘,‘ || v_bank_account_new ||‘ ) where‘ || i_coulumn ||‘=‘‘‘|| v_bank_account||‘‘‘‘;
elsif i_table_name = ‘T_LSIF_OTHERS‘ then
v_update_sql := ‘ update ‘ || i_table_name || ‘ set KOUZAINO = ‘ ||
v_bank_account_new ||‘‘ || ‘ where BANKCN <> ‘‘9900‘‘ and ‘|| i_coulumn ||‘=‘‘‘|| v_bank_account||‘‘‘‘;
v_update_sql2 := ‘ update ‘ || i_table_name ||
‘ set SITENCNJYO =‘‘123‘‘, ‘ ||
‘ SITENCNKA = ‘‘40‘‘, ‘ ||
‘ KOUZAINO = ‘‘01111281‘‘ ‘ ||
‘ where BANKCN = ‘‘9900‘‘‘;
elsif i_table_name = ‘T_LSIF_GETUMATU_NENMATU_MASTER‘ then
v_update_sql := ‘ update ‘ || i_table_name || ‘ set KOUZAINO = ‘ ||
v_bank_account_new ||‘‘ || ‘ where BANKCN <> ‘‘9900‘‘ and ‘|| i_coulumn ||‘=‘‘‘|| v_bank_account||‘‘‘‘;
v_update_sql2 := ‘ update ‘ || i_table_name ||
‘ set SITENCN =‘‘12340‘‘, ‘ ||
‘ KOUZAINO = ‘‘01111281‘‘ ‘ ||
‘ where BANKCN = ‘‘9900‘‘‘;
elsif i_table_name = ‘T_LSIF_KAIKEI‘ then
v_update_sql := ‘ update ‘ || i_table_name || ‘ set WUZANO = ‘ ||
v_bank_account_new ||‘‘ ||
‘ where GANKOWUMEYISHOUWUCN <> ‘‘9900‘‘ and ‘|| i_coulumn ||‘=‘‘‘|| v_bank_account||‘‘‘‘;
v_update_sql2 := ‘ update ‘ || i_table_name ||
‘ set SHITEYINMEYISYOUWUCN =‘‘123‘‘, ‘ ||
‘ WUZANO = ‘‘01111281‘‘ ‘ ||
‘ where GANKOWUMEYISHOUWUCN = ‘‘9900‘‘‘;
elsif i_table_name = ‘T_LSIF_IDOUEXTR‘ and i_coulumn = ‘KOUZAINO_ZEN‘ then
v_update_sql := ‘ update ‘ || i_table_name ||
‘ set KOUZAINO_ZEN = ‘ || v_bank_account_new ||‘‘ ||
‘ where GINKOUCN_ZEN <> ‘‘9900‘‘ and ‘|| i_coulumn ||‘=‘‘‘|| v_bank_account||‘‘‘‘;
v_update_sql2 := ‘ update ‘ || i_table_name ||
‘ set SHITENCNE_ZEN =‘‘123‘‘, ‘ ||
‘ SHITENCNGE_ZEN = ‘‘40‘‘, ‘ ||
‘ KOUZAINO_ZEN = ‘‘01111281‘‘ ‘ ||
‘ where GINKOUCN_ZEN = ‘‘9900‘‘‘;
elsif i_table_name = ‘T_LSIF_IDOUEXTR‘ and i_coulumn = ‘KOUZAINO_GO‘ then
v_update_sql := ‘ update ‘ || i_table_name || ‘ set KOUZAINO_GO = ‘ ||
v_bank_account_new ||‘‘ ||
‘ where GINKOUCN_GO <> ‘‘9900‘‘ and ‘|| i_coulumn ||‘=‘‘‘|| v_bank_account||‘‘‘‘;
v_update_sql2 := ‘ update ‘ || i_table_name ||
‘ set SHITENCNE_GO =‘‘123‘‘, ‘ ||
‘ SHITENCNGE_GO = ‘‘40‘‘, ‘ ||
‘ KOUZAINO_GO = ‘‘01111281‘‘ ‘ ||
‘ where GINKOUCN_GO = ‘‘9900‘‘‘;
elsif i_table_name = ‘T_DIAGNOSIS_CHARGE_DATA‘ then
v_update_sql := ‘ update ‘ || i_table_name || ‘ set ACCOUNT_NO = ‘ ||
v_bank_account_new ||‘‘ || ‘ where BANK_CN <> ‘‘9900‘‘ and ‘|| i_coulumn ||‘=‘‘‘|| v_bank_account||‘‘‘‘;
v_update_sql2 := ‘ update ‘ || i_table_name ||
‘ set BRANCH_CN =‘‘123‘‘, ‘ ||
‘ ACCOUNT_NO = ‘‘01111281‘‘ ‘ ||
‘ where BANK_CN = ‘‘9900‘‘‘;
elsif i_table_name = ‘T_IFE_MONTHLY_GETUMATU_MASTER‘ then
v_update_sql := ‘ update ‘ || i_table_name || ‘ set ‘ || i_coulumn ||
‘ = decode( BANKCN ‘ || ‘,‘ ||
‘‘‘9900‘‘‘ || ‘,‘ || ‘‘‘12340-01111281‘‘‘ || ‘,‘ ||
v_bank_account_new ||‘ ) where ‘|| i_coulumn ||‘=‘‘‘|| v_bank_account||‘‘‘‘;
else
v_update_sql := ‘ update ‘ || i_table_name || ‘ set ‘ || i_coulumn ||
‘ = decode( bank_code ‘ || ‘,‘ ||
‘‘‘9900‘‘‘ || ‘,‘ || ‘‘‘12340-01111281‘‘‘ || ‘,‘ ||
v_bank_account_new ||‘ ) where ‘|| i_coulumn ||‘=‘‘‘|| v_bank_account||‘‘‘‘;
end if;
v_bank_account_new:=‘‘;--clear data
execute immediate v_update_sql;
if v_update_sql2 is not null then
execute immediate v_update_sql2;
end if;
pkg_pub_scd_ci.p_batch_commit();
EXCEPTION
when others then
rollback to point;
o_result := PKG_LS_PUB_CODE_CST.BATCH_RESULT__FAIL;
o_error_str := i_table_name || ‘,‘;
pkg_pub_scd_ci.p_log_error(‘p_bank_acco_mask ,failed to mask table:‘ ||
i_table_name || ‘,error info:‘ ||
sqlerrm || ‘-------update SQL=‘ ||
v_update_sql);
m_has_exception := true;
end;
END LOOP;
CLOSE v_bank_account_curs;
END;
end p_bank_acco_mask;
动态创建cursor的函数原理同上，其他带参数的cursor具体可以参考下面的：
--procedure返回记录集：
----------------------声明一个Package--------------
CREATE OR REPLACE PACKAGE pkg_test
AS
TYPEmyrctypeIS REF CURSOR;
PROCEDURE get_r(p_id NUMBER,p_rc OUT myrctype); --Package中声明名为get 的Procedure(只有接口没内容)
END pkg_test;
-----------------声明Package Body，即上面Package中的内容，包括Procedure get---------------------
CREATE OR REPLACE PACKAGE BODY pkg_test
AS
PROCEDURE get_r(p_id NUMBER,p_rc OUT myrctype)
IS
sqlstr VARCHAR2 (500);
BEGIN
IF p_id = 0 THEN
OPEN p_rc FOR
SELECT ID, NAME, sex, address, postcode, birthday
FROM student;
ELSE
sqlstr :=
‘select id,name,sex,address,postcode,birthday
from student where id=:w_id‘; --w_id是个参数，
--以下 p_rc是个REF CURSOR游标类型，而且是OUT型参数，即可返回一个记录集了。USING p_id就是替换上面SQL中:w_id值拉:)
OPEN p_rc FOR sqlstr USING p_id;
END IF;
END get;
END pkg_test;
--function返回记录集的例子，原理和上面相同，而是用function的return值来返回记录集。
函数返回记录集:
建立带ref cursor定义的包和包体及函数：
复制代码 代码如下:
CREATE OR REPLACE
package pkg_test as
type myrctype is ref cursor;
function get_r(intID number) return myrctype;
end pkg_test;
/
CREATE OR REPLACE
package body pkg_test as
--函数体
function get_r(intID number) return myrctype is
rc myrctype; --定义ref cursor变量
sqlstr varchar2(500);
begin
if intID=0 then
--静态测试，直接用select语句直接返回结果
open rc for select id,name,sex,address,postcode,birthday from student;
else
--动态sql赋值，用:w_id来申明该变量从外部获得
sqlstr := ‘select id,name,sex,address,postcode,birthday from student where id=:w_id‘;
--动态测试，用sqlstr字符串返回结果，用using关键词传递参数
open rc for sqlstr using intid;
end if;
return rc;
end get;
end pkg_test;
动态Ref Cursor的定义与调用