首页 > 代码库 > 动态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的定义与调用