动态Ref Cursor的定义与调用

最新推荐文章于 2024-07-09 15:12:45 发布
最新推荐文章于 2024-07-09 15:12:45 发布
阅读量2.3k 收藏
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/tlycherry/article/details/40342817
版权

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;

 

 

我的梦想我追求
关注
Oracle 游标的使用——游标cursor 和 游标sys_refcursor 的使用
Shipley_Leo的博客
04-03 918
Oracle 游标的使用
JAVA执行带SYS_REFCURSOR游标的oracle存储过程返回结果集 会话级临时表
wh445306
12-06 1080
sys_refcursor是oracle9i以后系统定义的一个refcursor，主要作用是用于存储过程返回结果集。 存储过程结构如下： CREATE OR REPLACE PROCEDURE SP_CalcChargePrice /* 计算报备、评估收费基准价 @Charge=收费项目 @Amount=收费依据金额 @Kind=类别 1=报...
动态cursor 实例
u013305082的专栏
11-18 420
DECLARE       TYPE emp_cur IS REF CURSOR         RETURN emp%ROWTYPE;       empObj emp_cur;       empRecord emp%ROWTYPE;     BEGIN       OPEN empObj FOR           SELECT * FROM emp
存储过程返回游标
caozhifindjob的专栏
04-03 786
有俩种方法:一种是声明系统游标,一种是声明自定义游标,然后后面操作一样,参数类型为in out 或out(1)声明个人系统游标.(推荐)create or replace p_temp_procedure(    cur_arg out sys_refcursor;     --方法1)begin    open cur_arg for select * from tablename;end调用d
Vue中动态绑定Ref（两种方式）
最新发布
weixin_45935553的博客
07-09 2334
项目中ref绑定是常用的操作，那么如何实现动态绑定ref并实现动态获取呢？动态绑定的进阶写法又该如何使用？动态绑定Ref时重复存储Ref问题如何解决？用户动态创建多个空白表单，提交时需要获取当前所有表单数据该如何操作？
Oralce 存储过程 cursor、type x_cur is ref cursor、sys_refcursor 静态游标、强类型 弱类型动态游标、静态SQL 动态SQL 结合使用总结
许恒的博客
12-17 3448
直接上代码，使用的开发环境： Oracle Database 11g Release 2(11.2.0.4.0) - Enterprise Edition， PL/SQL Develop 12.0.7，Instantclient_12_2； PL/SQL Develop -&gt; New -&gt; Test Windoow：   游标 动态 遍历 cursor 匿名块.tst： ...
写存储过程时候 type cur is ref cursor 这句话是什么意思
热门推荐
无心人的终点
08-16 1万+
写存储过程时候 type cur is ref cursor 这句话是什么意思 CREATE OR REPLACE PACKAGE  '' AStype cur is ref cursor 整体的意思是“创建一个类型变量cur，它引用游标”，除了cur外，其余全是关键字。 TYPE cur：定义类型变量 is ref cursor：相当于数据类型，不过是引用游标的数据类型。 这种变量通
Oracle中动态cursor(游标)总结
u013305082的专栏
11-17 2728
Oracle中cursor(游标)总结 分类： Oracle 2008-11-14 19:26 1444人阅读 评论(0) 收藏 举报 oraclesqldelete活动存储 1. 游标: 容器，存储SQL语句影响行数。 2. 游标类型: 隐式游标，显示游标，REF游标。其中，隐式游标和显示游标属于静态游标（运行前将游标与SQL语句关联）,REF游标属于动
存储过程调用或接受另一存储过程返回的游标数据（Sys_refcursor类型）
Orcl
03-21 4416
一、示例 1.存储过程proc_A：定义如下： create or replace procedure proc_A(ID number, cur_OUT out sys_refcursor) is v_sql varchar2(2000); Begin v_sql:='select 总额, 自付, 自费 FROM 费用表 WHERE ID ='||ID; open cur
sys_refcursor使用案例
valkyrja110的博客
12-18 2816
sys_refcursor使用案例一、介绍二、sys_refcursor是一种数据类型 一、介绍 工作中看到了sys_refcursor的类型，之前没接触过，所以就上网搜一搜。可能是我技术比较垃圾，网上大部分的介绍都看不懂，没有一个最基础的介绍，最后我在微信公众号中看到了小白案例，在这里分享一下。 二、sys_refcursor是一种数据类型 sys_refcursor是一种数据类型，定义游标的数类型，网上介绍了什么各种静态、动态什么的，大家可以去参考，这里只介绍最基本的用法。 create or repl
获取Oracle过程中的OUT SYS_REFCURSOR值
邀月周记
11-20 3663
一个项目中的实例：获取Oracle过程中的返回SYS_REFCURSOR。注意：如果SYS_REFCURSOR为一个表或视图。可以通过表名%ROWTYPE获取每行数据，而不必另外定义type。 原过程大致如下： PROCEDURE P_USER_LOGIN(P_USERLOGINID IN VARCHAR2,P_PASSWORD IN VARCHAR2,P_SHOPID IN VARCHAR...
cursor 里实现动态sql
apple_wxs的专栏
01-23 595
cursor 里实现sql的动态拼接 [code="sql"]declare TYPE cur IS ref CURSOR; c cur; vs_sql varchar2(2000); vn_id number; vn_name varchar2(40); begin vs_sql := 'select name from xxx_t where id=:vn_...
参考游标(refcursor)的使用
feng250的博客
09-07 330
参考游标(refcursor)的使用 我们知道在sql server中可以直接在存储过程中使用select * from 来产生记录集，但在oracle的存储过程里却不可以。怎么办呢？可以用参考游标来实现。 参考游标（ref cursor）从Oracle 7.3开始引入，作用是允许在存储过程，函数，包中返回记录集。 在Oracle 9i之前，参考游标以如下方式定义： 首...
CURSOR 游标中动态表查询
fenyuxiao的专栏
10-19 932
--今天需要删除项目下面的所有发包，每个发包都有不同的类型，每个类型又有不同表，所以就遇到了动态FOR select表的情况declare @PROJECT_ID varchar(50),@tablename varchar(50), @wf_b_id varchar(50),@wfid varchar(20),@Sql varchar(500) set @PROJECT_ID='P2012
游标变量REF COUSOR(动态游标)使用实例
SPE_Andy
01-04 874
写存储过程时，会经常使用游标来处理数据。 一般在游标数据是变化时*也就是获取游标数据的表是变化的），使用动态游标。本例中，游标数据和传入参数有关，是变化的蛮久可以用动态游标来处理。 这里分享一个动态游标的使用实例，供大家参考。 1 定义游标---Cursor [CursorName] IS; 2 打开游标---Open [CursorName]; 3 操作数据---Fetch [Cursorname]； 4 关闭游标---Close [Curs
oracle 存储过程 ref cursor,oracle 存储过程及 REF CURSOR 的使用
weixin_39731845的博客
04-03 480
oracle 存储过程及 REF CURSOR 的使用基本使用方法及示例1, 基本结构:CREATE OR REPLACE PROCEDURE 存储过程名字(参数1IN NUMBER,参数2IN NUMBER)AS变量 1 INTEGER :=0;变量 2 DATE;BEGINEND 存储过程名字2, 无参形式的 procedure:-- 无参 procedurecreateorreplace p...
oracle ref cursor return,Oracle包和REF CURSOR
weixin_34901243的博客
04-02 384
首先，需要重新整理一下相关概念，oracle数据库中函数的作用，是只能被别人调用，存储过程可以单独执行，并且可以调用存储过程，首先，需要重新整理一下相关概念，Oracle数据库中函数的作用，是只能被别人调用，存储过程可以单独执行，并且可以调用存储过程，而包的作用包可以将任何出现在块声明的语句 ( 过程 , 函数 , 游标 , 游标 , 类型 , 变量 ) 放于包中 , 相当于一个容器 . 将声明语...
oracle 游标变量ref cursor详解
weixin_30892037的博客
12-26 147
oracle 游标变量ref cursor详解 分类： PL/SQL开发 2013-12-04 15:15 685人阅读 评论(0) 收藏 举报 oracleref cursor 一 介绍 像游标cursor一样，游标变量ref cursor指向指定查询结果集当前行。游标变量显得更加灵活因为其声明并不绑定指定查询。 其主要运用于PLSQL函数或存储过程以及其他编程语言...
ORACEL条件判断语句
cc_0101的博客
05-10 845
一、IF-ELSE判断1、if...then...end if2、if...then...else...end if3、if...then...elsif...else...end if二、CASE-WHEN函数1、case...when...then...else...end case三、DECODE()函数1、decode(value,if1...then1...if2...then2...e...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值