• oracle中REF Cursor用法

    from:http://www.111cn.net/database/Oracle/42873.htm

    1，什么是 REF游标 ？
     动态关联结果集的临时对象。即在运行的时候动态决定执行查询。
     
    2，REF 游标 有什么作用？
     实现在程序间传递结果集的功能，利用REF CURSOR也可以实现BULK SQL，从而提高SQL性能。

    3，静态游标和REF 游标的区别是什么？
     ①静态游标是静态定义，REF 游标是动态关联；
     ②使用REF 游标需REF 游标变量。
     ③REF 游标能做为参数进行传递，而静态游标是不可能的。
     
    4，什么是REF 游标变量？
     REF游标变量是一种 引用 REF游标类型  的变量，指向动态关联的结果集。

    5，怎么使用  REF游标 ？
     ①声明REF 游标类型，确定REF 游标类型；
      ⑴强类型REF游标：指定retrun type，REF 游标变量的类型必须和return type一致。
       语法：Type   REF游标名   IS   Ref Cursor Return  结果集返回记录类型；
      ⑵弱类型REF游标：不指定return type，能和任何类型的CURSOR变量匹配，用于获取任何结果集。
       语法：Type   REF游标名   IS   Ref Cursor；

     ②声明Ref 游标类型变量；
      语法：变量名  已声明Ref 游标类型；
      
     ③打开REF游标，关联结果集 ；
      语法：Open   Ref 游标类型变量   For   查询语句返回结果集；
      
     ④获取记录，操作记录；
      语法：Fatch    REF游标名 InTo   临时记录类型变量或属性类型变量列表；
      
     ⑤关闭游标，完全释放资源；
      语法：Close   REF游标名；
     
     例子：强类型REF游标

     代码如下 复制代码
     /*conn scott/tiger*/
     Declare 
      Type MyRefCurA IS  REF CURSOR RETURN emp%RowType;
      Type MyRefCurB IS  REF CURSOR RETURN emp.ename%Type;
      vRefCurA  MyRefCurA;
      vRefCurB  MyRefCurB;
      vTempA  vRefCurA%RowType;
      vTempB  vRefCurB.ename%Type;
      
     Begin
      Open  vRefCurA  For Select  *  from   emp   Where  SAL > 2000;
      Loop
       Fatch  vRefCurA InTo  vTempA;
       Exit  When  vRefCurA%NotFound;
       DBMS_OUTPUT.PUT_LINE(vRefCurA%RowCount||'  '|| vTempA.eno||'  '||vTempA.ename ||'  '||vTempA.sal)
      End Loop;
      Close vRefCurA;
      
      DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------------------------------');
      
      Open  vRefCurB  For Select  ename  from   emp   Where  SAL > 2000;
      Loop
       Fatch  vRefCurB InTo  vTempB;
       Exit  When  vRefCurB%NotFound;
       DBMS_OUTPUT.PUT_LINE(vRefCurB%RowCount||'  '||vTempB)
      End Loop;
      Close vRefCurB; 
      
      DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------------------------------');   
      
      Open  vRefCurA  For Select  *  from   emp   Where  JOB = 'CLERK';
      Loop
       Fatch  vRefCurA InTo  vTempA;
       Exit  When  vRefCurA%NotFound;
       DBMS_OUTPUT.PUT_LINE(vRefCurA%RowCount||'  '|| vTempA.eno||'  '||vTempA.ename ||'  '||vTempA.sal)
      End Loop;
      Close vRefCurA;
     End;
     


     例子：弱类型REF游标
     

     代码如下 复制代码

    /*conn scott/tiger*/
     Declare
      Type MyRefCur  IS  Ref  Cursor;
      vRefCur MyRefCur;
      vtemp  vRefCur%RowType;
     Begin
      Case(&n)
       When  1 Then Open vRefCur  For Select   *   from emp;
       When  2 Then Open vRefCur  For Select   *   from dept;
       Else 
        Open vRefCur  For Select   eno,  ename  from emp Where JOB = 'CLERK';
      End Case;
      Close  vRefCur;
     End;

    6，怎样让REF游标作为参数传递？

     代码如下 复制代码

    --作为函数返回值
    create or replace function returnacursor return sys_refcursor 
    is
       v_csr sys_refcursor;
    begin
        open v_csr for select a1 from test3;
        return v_csr;
    end;
    /

    declare
    c sys_refcursor;
    a1 char(2);
    begin
      c:=returnacursor;
      loop
        fetch c into a1;
        exit when c%notfound;
        dbms_output.put_line(a1);
      end loop;
      close c;
    end;
    /

    --作为参数
    create or replace procedure proc_ref_cursor (rc in sys_refcursor) as
      v_a number;
      v_b varchar2(10);
      
    begin
      loop
        fetch rc into v_a, v_b;
        exit when rc%notfound;
        dbms_output.put_line(v_a || ' ' || v_b);
      end loop;
    end;
    /

    declare
    v_rc sys_refcursor;
    begin
      open v_rc for 
      select a1,a2 from test3;
      proc_ref_cursor(v_rc);
      close v_rc;
    end;
    /


    REF CURSOR 示例包括下列三个 Visual Basic 示例，演示如何使用 REF CURSOR。

    示例  说明  
    在 OracleDataReader 中检索 REF CURSOR 参数
     此示例执行一个 PL/SQL 存储过程，返回 REF CURSOR 参数，并将值作为 OracleDataReader 读取。
     
    使用 OracleDataReader 从多个 REF CURSOR 检索数据
     此示例执行一个 PL/SQL 存储过程，返回两个 REF CURSOR 参数，并使用 OracleDataReader 读取值。
     
    使用一个或多个 REF CURSOR 填充 DataSet
     此示例执行一个 PL/SQL 存储过程，返回两个 REF CURSOR 参数，并使用返回的行填充 DataSet。
     

    要使用这些示例，可能需要创建 Oracle 表，并且必须创建 PL/SQL 包和包正文。

    创建 Oracle 表
    这些示例使用 Oracle Scott/Tiger 架构中定义的表。大多数 Oracle 安装均包括 Oracle Scott/Tiger 架构。如果此架构不存在，可以使用 {OracleHome}rdbmsadminscott.sql 中的 SQL 命令文件创建供这些示例使用的表和索引。

    创建 Oracle 包和包正文
    这些示例要求服务器上存在以下 PL/SQL 包和包正文。在 Oracle 服务器上创建以下 Oracle 包

     代码如下 复制代码

    CREATE OR REPLACE PACKAGE BODY CURSPKG AS
        PROCEDURE OPEN_ONE_CURSOR (N_EMPNO IN NUMBER,
                                   IO_CURSOR IN OUT T_CURSOR)
        IS 
            V_CURSOR T_CURSOR; 
        BEGIN 
            IF N_EMPNO <> 0 
            THEN
                 OPEN V_CURSOR FOR 
                 SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME 
                      FROM EMP, DEPT 
                      WHERE EMP.DEPTNO = DEPT.DEPTNO 
                      AND EMP.EMPNO = N_EMPNO;

            ELSE 
                 OPEN V_CURSOR FOR 
                 SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME 
                      FROM EMP, DEPT 
                      WHERE EMP.DEPTNO = DEPT.DEPTNO;

            END IF;
            IO_CURSOR := V_CURSOR; 
        END OPEN_ONE_CURSOR;

        PROCEDURE OPEN_TWO_CURSORS (EMPCURSOR OUT T_CURSOR,
                                    DEPTCURSOR OUT T_CURSOR)
        IS 
            V_CURSOR1 T_CURSOR; 
            V_CURSOR2 T_CURSOR; 
        BEGIN 
            OPEN V_CURSOR1 FOR SELECT * FROM EMP;
            OPEN V_CURSOR2 FOR SELECT * FROM DEPT;
            EMPCURSOR  := V_CURSOR1; 
            DEPTCURSOR := V_CURSOR2; 
        END OPEN_TWO_CURSORS; 
    END CURSPKG;
    /


     

    Oracle提供REF CURSOR，通过该功能可以实现在程序间传递结果集的功能，利用REF CURSOR也可以实现BULK SQL，从而提高SQL性能。

    使用scott用户的emp表实现以下测试案例：

     代码如下 复制代码

    SQL> desc emp
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    EMPNO NOT NULL NUMBER(4)
    ENAME VARCHAR2(10)
    JOB VARCHAR2(9)
    MGR NUMBER(4)
    HIREDATE DATE
    SAL NUMBER(7,2)
    COMM NUMBER(7,2)
    DEPTNO NUMBER(2)

    使用ref cursor获得结果集输出：


    SQL> set serveroutput on
    SQL> DECLARE
    2 TYPE mytable IS TABLE OF emp%ROWTYPE;
    3 l_data mytable;
    4 l_refc sys_refcursor;
    5 BEGIN
    6 OPEN l_refc FOR
    7 SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp;
    8 
    9 FETCH l_refc BULK COLLECT INTO l_data;
    10 
    11 CLOSE l_refc;
    12 
    13 FOR i IN 1 .. l_data.COUNT
    14 LOOP
    15 DBMS_OUTPUT.put_line ( l_data (i).ename
    16 || ' was hired since '
    17 || l_data (i).hiredate
    18 );
    19 END LOOP;
    20 END;
    21 /
    SMITH was hired since 17-DEC-80
    ALLEN was hired since 20-FEB-81
    WARD was hired since 22-FEB-81
    JONES was hired since 02-APR-81
    MARTIN was hired since 28-SEP-81
    BLAKE was hired since 01-MAY-81
    CLARK was hired since 09-JUN-81
    SCOTT was hired since 19-APR-87
    KING was hired since 17-NOV-81
    TURNER was hired since 08-SEP-81
    ADAMS was hired since 23-MAY-87
    JAMES was hired since 03-DEC-81
    FORD was hired since 03-DEC-81
    MILLER was hired since 23-JAN-82

    PL/SQL procedure successfully completed.


    -The End-
  • 相关阅读:
    矩阵价值和
    排列组合问题
    X国的军队
    石子合并加强版
    P1042 乒乓球
    Dinner 点餐
    一文看尽图像分类问题
    [Udemy] Recommender Systems and Deep Learning in Python
    推荐系统学习
    [NLP] 酒店名归类
  • 原文地址：https://www.cnblogs.com/liuqiyun/p/6552700.html
Copyright © 2020-2023  润新知