REF CURSOR 总结

REF 游标：
REF游标又称为动态游标，在运行时使不同的语句与之关联，动态关联结果集的临时对象，即在运行的时候动态决定执行查询。REF游标可以使用游标变量。
游标变量：
游标变量是一种引用REF游标类型的变量，只想动态关联的结果集。
游标变量的类型：
1.具有约束的游标变量，具有返回类型的游标变量也称为强游标。
2.无约束的游标变量，没有返回类型的游标变量也称为弱游标。
REF游标的作用：
实现程序间传递结果集的功能，利用REF cursor 也可以实现bulk sql 从而提高sql性能。
静态游标和REF游标的区别：
1.静态游标是静态定义，REF游标是动态关联
2.使用REF游标需要REF游标变量
3.REF游标可以作为参数进行传递，而静态游标是不能作为参数传递的。
REF游标的语法：
1.强类型REF游标：指定return datatype,REF游标变量的类型必须和return datatype一致。
语法：TYPE TYPE_NAME IS REF CURSOR RETURN DATATYPE;
2.弱类型REF游标：不指定return datatype 能和任何类型的cursor 变量匹配，用于获取任何结果集。
语法：TYPE TYPE_NAME IS REF CURSOR;
SYS_REFCURSOR：
主要用在过程中返回结果集，如果仅仅为了返回值，无需自己在包头中定义游标类型，只需直接使用sys_refcursor 即可轻松返回结果。
提示：
使用静态光标--通过静态SQL（但不用ref光标）--比使用ref光标效率高，而ref光标的使用仅限于以下几种情况：
把结果集返回给客户端；
在多个子例程之间共享光标（实际上与上面提到的一点非常类似）；
没有其他有效的方法来达到你的目标时，则使用ref光标，正如必须用动态SQL时那样；
简言之，首先考虑使用静态SQL，只有绝对必须使用ref光标时才使用ref光标，也有人建议尽量使用隐式游标，避免编写附加的游标控制代码（声明，打开，获取，关闭），也不需要声明变量来保存从游标中获取的数据。
PACKAGE
示例中涉及到包(package)和包体(package body)以下为package 用法的说明：
包结构：
一个包由两个分开的部分组成：包规范和包体
1.包定义（package）：包定义部分是为应用程序的接口，声明包内数据类型、变量、常量、游标、子程序和异常错误处理等元素，这些元素为包的公有元素。
语法：
CREATE [OR REPLACE] PACKAGE PACKAGE_NAME
{IS|AS}
[公有数据类型定义]
[公有游标声明]
[公有变量、常量声明]
[公有子程序声明]
END [PACKAGE_NAME];
2.包主体（package body）：包主体则是包定义部分的具体实现，它定义了包定义部分所声明的游标和子程序，在包主体中还可以声明包的私有元素。如果在包主体中的游标或子程序并没有在包头中定义，那么这个游标或子程序是私有的。
语法：
CREATE [OR REPLACE] PACKAGE BODY PACKAGE_NAME
{IS |AS}
[私有数据类型定义]
[私有变量、常量]
[私有子程序声明和定义]
[公有子程序定义]
BEGIN
执行部分（初始化部分）；
END [PACKAGE_NAME];
与类相同，包中的程序元素也分为公有元素和私有元素两种，这两种元素的区别是他们允许访问程序范围不同，即他们的作用域不同。公有元素不仅可以被包中的函数、过程所调用也可以被包外的PL/SQL程序访问，而私有元素只能被包内的函数和过程所访问。包定义和包主体分开编译，并作为两部分分开的对象存放在数据库字典中。包定义一定要在包主体前面编译，包主体可以没有，但是包定义一定要有，包的名称和包体的名称要保持一致。

示例1：强类型REF游标

DECLARE
TYPE REF_CURSOR IS REF CURSOR RETURN DJ_DJB%ROWTYPE;
------游标仅能打开DJ_DJB表的数据

REF_C REF_CURSOR;
----游标变量

V_DJB DJ_DJB%ROWTYPE;
SELECTioN VARCHAR2(1):=('&请输入：');
BEGIN
  IF SELECTION='1' THEN
    OPEN REF_C FOR SELECT * FROM DJ_DJB WHERE SLBH LIKE '2016%' AND ROWNUM<10;
    ELSIF SELECTION='2' THEN
    OPEN REF_C FOR SELECT * FROM DJ_DJB WHERE SLBH LIKE '2017%' AND ROWNUM<10;
    END IF;
    LOOP
      FETCH REF_C INTO V_DJB;
      EXIT WHEN REF_C%NOTFOUND;
      DBMS_OUTPUT.ENABLE(BUFFER_SIZE=>NULL);
      DBMS_OUTPUT.PUT_LINE(V_DJB.SLBH||','||V_DJB.BDCZH);
      END LOOP;
      CLOSE REF_C;
      END;

示例2：弱类型REF游标

DECLARE
TYPE REF_CURSOR IS REF CURSOR;
REF_C REF_CURSOR;

V_V1 VARCHAR2(100);

SELECTION VARCHAR2(1):=('&请输入：');

BEGIN
  IF SELECTION='1' THEN
    OPEN REF_C FOR SELECT TO_CHAR(BDCZH) FROM DJ_DJB WHERE SLBH LIKE '2016%' AND ROWNUM<10;
        -------弱类型游标对目标表没有限制，数据可以使来自任何表

    ELSIF SELECTION='2' THEN
    OPEN REF_C FOR SELECT TO_CHAR(QLRMC) FROM DJ_QLRGL WHERE SLBH LIKE '2017%' AND ROWNUM<10 AND QLRLX='权利人';
            -------弱类型游标对目标表没有限制，数据可以使来自任何表
    END IF;

    LOOP
      FETCH REF_C INTO V_V1;
      EXIT WHEN REF_C%NOTFOUND;
      DBMS_OUTPUT.ENABLE(BUFFER_SIZE=>NULL);
      DBMS_OUTPUT.PUT_LINE('输出结果值：'||V_V1);
      END LOOP;
      CLOSE REF_C;
      END;

示例3：SYS_REFCURSOR

DECLARE

V_DJB DJ_DJB%ROWTYPE;

REF_C SYS_REFCURSOR; ---利用SYS_REFCUSOR 来声明游标变量

SELECTION VARCHAR2(1):=('&请输入：');
BEGIN
  IF SELECTION='1' THEN
    OPEN REF_C FOR SELECT * FROM DJ_DJB WHERE SLBH LIKE '2016%' AND ROWNUM<10;
    ELSIF SELECTION='2' THEN
    OPEN REF_C FOR SELECT * FROM DJ_DJB WHERE SLBH LIKE '2017%' AND ROWNUM<11;
    END IF;

 LOOP
   FETCH REF_C INTO V_DJB;
   EXIT WHEN REF_C%NOTFOUND;
   DBMS_OUTPUT.ENABLE(BUFFER_SIZE=>NULL);
   DBMS_OUTPUT.PUT_LINE(V_DJB.SLBH||','||V_DJB.BDCZH);
   END LOOP;
   CLOSE REF_C;
   END;

示例4：REF 游标作为参数传递

CREATE OR REPLACE PACKAGE EMP_T
IS
TYPE REF_CURSOR IS REF CURSOR RETURN DJ_DJB%ROWTYPE;
PROCEDURE GET_BDCZH(REF_C IN OUT REF_CURSOR,SELECTION VARCHAR2);
PROCEDURE RETURN_C(SELECTION VARCHAR2);

END;

CREATE OR REPLACE PACKAGE BODY EMP_T 
IS
PROCEDURE GET_BDCZH (REF_C IN OUT REF_CURSOR,SELECTION VARCHAR2)
  IS
  BEGIN
    IF SELECTION =1 THEN
      OPEN REF_C FOR SELECT * FROM DJ_DJB WHERE SLBH LIKE '2016%' AND ROWNUM<10;
      ELSIF SELECTION=2 THEN
      OPEN REF_C FOR SELECT * FROM DJ_DJB WHERE SLBH LIKE '2017%' AND ROWNUM<10;
      END IF;
      END;

PROCEDURE RETURN_C (SELECTION VARCHAR2)
  IS
  V_1 REF_CURSOR;
  V_DJB dj_djb%ROWTYPE;

  BEGIN
    GET_BDCZH(V_1,SELECTION);
    LOOP
      FETCH V_1 INTO V_DJB;
      EXIT WHEN V_1%NOTFOUND;
      DBMS_OUTPUT.ENABLE(BUFFER_SIZE=>NULL);
      DBMS_OUTPUT.PUT_LINE(V_DJB.SLBH||','||V_DJB.BDCZH);
      END LOOP;
      CLOSE V_1;
      END;
      END;
---调用
begin
  EMP_T.RETURN_C(1);
  END;

示例5：使用BULK COLLECT INTO.......批量提取数据

DECLARE
TYPE REF_CURSOR IS REF CURSOR;
REF_C REF_CURSOR;

TYPE R_DJB IS TABLE OF DJ_DJB%ROWTYPE INDEX BY BINARY_INTEGER;
V_DJB R_DJB;
SELECTION VARCHAR2(1):=('&请输入：');

BEGIN
  IF SELECTION='1' THEN
  OPEN REF_C FOR SELECT * FROM DJ_DJB WHERE SLBH LIKE '2016%' AND ROWNUM<100;
  ELSIF SELECTION='2' THEN
  OPEN REF_C FOR SELECT * FROM DJ_DJB WHERE SLBH LIKE '2017%' AND ROWNUM<100;
  END IF;
  LOOP
    FETCH REF_C BULK COLLECT INTO V_DJB LIMIT 10;
    ---EXIT WHEN REF_C%NOTFOUND;
     DBMS_OUTPUT.ENABLE(BUFFER_SIZE=>NULL);
    ---FOR I IN 1..V_DJB.COUNT LOOP
    FOR I IN V_DJB.FIRST..V_DJB.LAST LOOP
      DBMS_OUTPUT.PUT_LINE(V_DJB(I).SLBH||','||V_DJB(I).BDCZH||'I 的值为：'||I);
      END LOOP;
      DBMS_OUTPUT.PUT_LINE('ROWCOUNT的值：'||REF_C%ROWCOUNT);
      EXIT WHEN REF_C%NOTFOUND;
      END LOOP;
      END;

BY WOLIAHaiTO 2018.04.03