PL/SQL 游标变量

PL/SQL 游标变量

转自：http://blog.csdn.net/leshami/article/details/7623525

游标变量与游标相似，有其共性，也有其不同点。
就其共性来说两者都是指向多行查询的结果集中的当前行。都要经历声明，打开，检索与关闭的过程。
所不同的是游标与游标变量类似于常量与变量。
游标是静态的，而游标变量是动态的，因为游标变量并不固定于某个特定的查询。
所以，游标变量可以打开任何类型兼容的查询。其次可以将游标变量作为参数传递给本地和存储子程序。
本文主要描述游标变量的使用。


一、什么是游标变量

      显式游标用于命名一个工作区域，其中保存多行查询的信息，而且该游标始终指向工作区域的内容。而游标变量类似于C 或Pascal 语言中
的指针，它指向一块内存地址，而不是地址中的内容本身。所以，声明一个游标变量可以创建一个指针，而不是具体的内容。
      在PL/SQL 中，为创建游标变量，首先需要申明一个REF CURSOR类型，然后声明该类型的一个变量。
      为了执行多行查询，Oracle 会开启一个未命名的工作区来存放处理信息。我们可以用显式游标为工作区命名然后访问相关的信息；或者声
明指向工作区的一个游标变量。无论在什么地方使用游标，它总是指向同一个查询工作区，而游标变量则可以指向不同的工作区。
      所以，游标和游标变量不能交互使用；也就是说，我们不能在该使用游标的地方使用游标变量，不能在该使用游标变量的地方使用游标。

二、游标变量使用的情形

      PL/SQL存储子程序和各种客户端之间可以使用游标变量来传递查询结果，这是游标变量最主要的作用。
      PL/SQL和其他客户端程序都不拥有结果集，它们只是共享一个指向存放结果集工作区的指针而已。
      例如，一个OCI客户端，一个Oracle Forms 应用程序和Oracle 服务器可以引用同一个工作区。
      只要有游标变量指向查询工作区，我们就可以引用它。因此，我们可以把游标变量的值自由地从一个作用域传递到另一个。
      例如，我们把主游标变量传递到嵌套在Pro*C 程序中的PL/SQL 块，游标变量指向的工作区就可以被访问。
      如果客户端含有PL/SQL引擎，那么从客户端调用服务器端就不会有什么约束。假如我们在客户端声明游标变量，在服务器端打开并取得数
据，然后把取得的结果返回给客户端。这些操作都是在服务器端完成，从而也减少了网络流量。


三、使用游标变量的几个关键步骤

1、定义和声明游标变量
  TYPE ref_type_name IS REF CURSOR [RETURN return_type];    --必须先定义REF CURSOR类型
              cursor_variable ref_type_name;                                            --接下来再定义游标变量
             
              ref_type_name:    指定自定义的类型名
              RETURN:                  指定REF CURSOR返回结果的数据类型
              cursor_variable: 定义游标变量的名字
              注：若指定RETURN子句，其数据类型必须是记录类型，此外，不能在包规范中定义游标变量。
                      其次若指定RETURN子句则为强游标类型，否则为弱游标类型。
                      能够把一个强游标类型与类型兼容的查询相关联，而弱游标类型可以与任何查询相关联。故强类型游标变量出错概率低，而弱类型游标更灵活。
                     
2、打开游标变量
        当打开游标变量时，游标变量便与特定的SELECT语句关联，执行查询，标识结果集。
      使用OPEN FOR可以为不同的查询打开相同的游标变量。
      再次打开它之前，无需关闭游标变量，但之前的查询会全部丢失。
        OPEN cursor_variable FOR select_statement;

 
3、从结果集检索数据行
        每次从结果集检索一次。
      需要注意的是强类型游标返回的数据类型必须与FETCH 语句中INTO所使用的变量类型兼容。
        其次查询列值的数量必须等于变量的数量，如果数量不匹配，则强类型游标在编译时出错，而弱类型游标则在运行时出错。
        FETCH cursor_variable INTO variable1,...variable2 ;                                                    --提取单行数据，需要配合循环语句来使用
        FETCH cursor_variable BULK COLLECT INTO collect1,collect2,...[LIMIT rows];      --提取多行数据，collect为集合变量

4、关闭游标变量
  CLOSE cursor_vairable;


四、定义REF CURSOR与声明游标变量示例  

--PL/SQL块内声明游标变量                   
DECLARE  
    TYPE emp_cur_type IS REF CURSOR RETURN emp%ROWTYPE;    -->定义具有返回类型的游标类型，此为强类型                                                                                                                                                                                                                                       
    TYPE dept_cur_type IS REF CURSOR;                                          -->定义无返回类型的游标类型，此为弱类型                                                                                                                                                                                                             
    emp_cv      emp_cur_type;                                                              -->接下来声明两个游标变量   
    dept_cv    dept_cur_type;  
BEGIN  
    NULL;  
END;  
 
--使用%type来定义游标变量的返回类型   
DECLARE  
    emp_type emp%ROWTYPE;                                                                      -->定义了一个隐式记录类型   
    TYPE emp_cur_type IS REF CURSOR RETURN emp_type%TYPE;    -->定义游标类型且使用%TYPE来返回的数据类型                                                                     
    emp_cv        emp_cur_type;                                                                    -->声明游标变量   
BEGIN  
    NULL;  
END;  
 
--基于自定义的记录类型作为游标变量的返回类型   
DECLARE  
    TYPE emp_rec_type IS RECORD                                                              -->定义了一个用户自定义的记录类型   
    (  
          empno          NUMBER( 4 )  
        ,ename          VARCHAR2( 10 )  
        ,hiredate    emp.hiredate%TYPE  
    );  
 
    TYPE emp_cur_type IS REF CURSOR RETURN emp_rec_type;  --定义具有返回类型的游标类型，且返回类型为用户自定义的记录类型   
    emp_cv    emp_cur_type;                                                              --声明游标变量                                                                                                                                                                                             
BEGIN  
    NULL;  
END;  
 
--游标变量作为函数或过程的参数   
DECLARE  
    TYPE emp_cur_type IS REF CURSOR                                            -->定义一个游标类型，其返回类型为emp的记录类型   
          RETURN emp%ROWTYPE;  
 
    emp_cur    emp_cur_type;                                                            -->声明游标变量   
 
--下面的本地过程用于处理游标变量的结果集   
--注，对于游标变量返回的结果集是一次性处理，而非对返回的每一行记录调用一次过程   
    PROCEDURE process_emp_cv( emp_cv IN emp_cur_type ) IS  -->形参emp_cv使用了emp_cur_type游标类型   
          person    emp%ROWTYPE;  
    BEGIN  
          DBMS_OUTPUT.put_line( '-----' );  
          DBMS_OUTPUT.put_line( 'Here are the names from the result set:' );  

          LOOP  
                FETCH emp_cv INTO person;  
                EXIT WHEN emp_cv%NOTFOUND;  
                DBMS_OUTPUT.put_line( 'Name = ' || person.ename || ' ' || person.hiredate );  
          END LOOP;  
    END;  
BEGIN  
    OPEN emp_cur FOR SELECT * FROM emp WHERE deptno = 10;    -->使用游标变量打开游标   
 
    process_emp_cv( emp_cur );          -->调用本地过程处理打开的游标变量   
 
    CLOSE emp_cur;                                                            -->显示关闭游标变量   
 
    OPEN emp_cur FOR SELECT * FROM emp WHERE job LIKE 'CLERK';    -->再次打开游标变量，返回了不同的结果集   
 
    process_emp_cv( emp_cur );      -->调用本地过程处理打开的游标变量   
 
    CLOSE emp_cur;                                                          -->显示关闭游标变量   
END;  


五、游标变量使用示例

1、包中使用游标变量  

CREATE PACKAGE emp_data AS  
    TYPE empcurtyp IS REF CURSOR  
          RETURN emp%ROWTYPE;  
 
    PROCEDURE open_emp_cv( emp_cv IN OUT empcurtyp );  
END emp_data;  
 
CREATE OR REPLACE PACKAGE BODY emp_data AS  
    PROCEDURE open_emp_cv( emp_cv IN OUT empcurtyp ) IS  
          each_emp    emp%ROWTYPE;  
    BEGIN  
          OPEN emp_cv FOR  
                SELECT *  
                FROM    emp  
                WHERE  ename LIKE 'A%';  
    END open_emp_cv;  
END emp_data;  
 
2、游标变量绑定到不同的返回类型(弱类型)  

CREATE PACKAGE get_data AS  
    TYPE ref_cur_type IS REF CURSOR;  
 
    PROCEDURE open_cv( ref_cv IN OUT ref_cur_type, choice INT );  
END get_data;  
 
CREATE PACKAGE BODY get_data AS  
    PROCEDURE open_cv (ref_cv IN OUT ref_cur_type, choice INT) IS  
    BEGIN  
          IF choice = 1 THEN  
                OPEN ref_cv FOR SELECT * FROM emp;  
          ELSIF choice = 2 THEN  
                OPEN ref_cv FOR SELECT * FROM dept;  
          ELSIF choice = 3 THEN  
                OPEN ref_cv FOR SELECT * FROM bonus;  
          END IF;  
    END;  
END get_data;  
 
3、强类型游标  

DECLARE  
    TYPE emp_rec_type IS RECORD                  -->定义一个记录类型   
    (  
          empno          emp.empno%TYPE  
        ,ename          emp.ename%TYPE  
        ,hiredate    emp.hiredate%TYPE  
    );  
 
    TYPE ref_cur_type IS REF CURSOR      -->定义了一个游标变量类型，且返回类型为emp_rec_type的记录类型   
          RETURN emp_rec_type;  
 
    emp_cv      ref_cur_type;                                          -->声明游标变量   
    emp_rec    emp_rec_type;                                          -->声明记录类型变量   
BEGIN  
    OPEN emp_cv FOR  
          SELECT empno, ename, hiredate  
          FROM    emp  
          WHERE  ename LIKE 'A%';  
 
    LOOP  
          FETCH emp_cv INTO emp_rec;    -->将游标变量的结果保存到记录变量中   
 
          EXIT WHEN emp_cv%NOTFOUND;  
          DBMS_OUTPUT.put_line( 'Name = ' || emp_rec.ename || '; ' || 'Hire Date = ' || emp_rec.hiredate );  
    END LOOP;  
 
    CLOSE emp_cv;  
END;  
 
4、绑定游标变量的结果到集合   

DECLARE  
    TYPE emp_rec_type IS RECORD                                                                -->定义一个记录类型   
    (  
          empno          emp.empno%TYPE  
        ,ename          emp.ename%TYPE  
        ,hiredate    emp.hiredate%TYPE  
    );  
 
    TYPE emp_nst_type IS TABLE OF emp_rec_type                                  -->定义基于记录类型的联合数组   
                                                    INDEX BY PLS_INTEGER;  
 
    TYPE ref_cur_type IS REF CURSOR                                                        -->定义游标变量类型并返回记录类型       
          RETURN emp_rec_type;                                                                        -->此处如果使用emp_nst_type会收到错误   
 
    emp_cv              ref_cur_type;                                                                -->声明游标变量   
    emp_collect    emp_nst_type;                                                                -->声明复合数据类型变量   
BEGIN  
    OPEN emp_cv FOR  
          SELECT empno, ename, hiredate  
          FROM    emp  
          WHERE  ename LIKE 'A%';  
 
    FETCH emp_cv  
    BULK COLLECT INTO emp_collect;                                                      -->使用bulk collect into将游标记录批量提取到复合变量中   
 
    CLOSE emp_cv;  
 
    FOR i IN emp_collect.FIRST .. emp_collect.LAST                          -->输出复合变量中的结果   
    LOOP  
          DBMS_OUTPUT.put_line( 'Name = ' || emp_collect( i ).ename || ', hiredate = ' || emp_collect( i ).hiredate );  
    END LOOP;  
END;  
 
5、SQL*Plus中操作游标变量  

--下面基于前面定义的包get_data，我们在SQL*Plus中来调用包中的游标变量并返回数据   
scott@CNMMBO> variable lv_ref_cv refcursor;  
scott@CNMMBO> variable lv_choice number;  
scott@CNMMBO> exec :lv_choice:=2;  
 
PL/SQL procedure successfully completed.  
 
scott@CNMMBO> exec get_data.open_cv(:lv_ref_cv,:lv_choice);  
 
PL/SQL procedure successfully completed.  
 
scott@CNMMBO> print lv_ref_cv  
 
      DEPTNO DNAME                  LOC  
---------- -------------- -------------   
              10 ACCOUNTING        NEW YORK  
              20 RESEARCH            DALLAS  
              30 SALES                  CHICAGO  
              40 OPERATIONS        BOSTON  
 
6、PL/SQL中调用包中的游标变量  

DECLARE  
    v_ref_cv                      get_data.ref_cur_type; -->声明一个基于包的弱类型游标变量   
    v_ref_cv_rec              dept%ROWTYPE;                  -->由于为弱类型，所以我们使用了对应表的记录类型作为返回类型来声明记录变量   
                                                                                          -->如果包中定义的为强类型游标变量，则上面的声明可以直接写为return_type%rowtype   
    v_choice                      PLS_INTEGER := 2;             
BEGIN  
    get_data.open_cv( v_ref_cv, v_choice );  
 
    LOOP  
          FETCH v_ref_cv INTO v_ref_cv_rec;  
 
          EXIT WHEN v_ref_cv%NOTFOUND;  
          DBMS_OUTPUT.put_line( 'current rec is ' || v_ref_cv_rec.dname || ',' || v_ref_cv_rec.loc );  
    END LOOP;  
END;  
 
current rec is ACCOUNTING,NEW YORK  
current rec is RESEARCH,DALLAS  
current rec is SALES,CHICAGO  
current rec is OPERATIONS,BOSTON  
 
PL/SQL procedure successfully completed.  
 
7、基于弱类型定义返回类型导致异常  

DECLARE  
    TYPE weak_ref_cur_type IS REF CURSOR;  
 
    weak_ref_cur              weak_ref_cur_type;  
    weak_ref_rec              weak_ref_cur%ROWTYPE; -->产生一个 PL/SQL 320 错误   
--    weak_ref_rec              dept%ROWTYPE;            --> 正确，使用自定义的返回类型   
--    weak_ref_rec              emp%ROWTYPE;        -->如果定义了与返回类型不兼容的类型则在运行时出现异常     
                                                                                  -->ORA-06504: PL/SQL: Return types of Result Set variables or query do not match   
BEGIN                                                                                       
    OPEN weak_ref_cur FOR SELECT * FROM dept;  
 
    FETCH weak_ref_cur INTO weak_ref_rec;  
 
    DBMS_OUTPUT.put_line( 'Current Rec is ' || weak_ref_rec.dname || ',' || weak_ref_rec.loc );  
 
    CLOSE weak_ref_cur;  
END;  
 
ERROR at line 5:  
ORA-06550: line 5, column 24:  
PLS-00320: the declaration of the type of this expression is incomplete or malformed  
ORA-06550: line 5, column 24:  
PL/SQL: Item ignored  
ORA-06550: line 10, column 28:  
PLS-00320: the declaration of the type of this expression is incomplete or malformed  
 
8、强类型编译时异常

-->下面定义的强类型游标变量中返回类型不兼容，则编译时抛出异常   

DECLARE  
    TYPE strong_ref_cur_type IS REF CURSOR  
          RETURN emp%ROWTYPE;  
 
    strong_ref_cur          strong_ref_cur_type;  
    strong_ref_rec          emp%ROWTYPE;  
BEGIN  
    OPEN strong_ref_cur FOR SELECT * FROM dept;    -->定义的返回类型为emp%ROWTYPE，而此时的查询为dept表类型   
 
    FETCH strong_ref_cur INTO strong_ref_rec;  
 
    CLOSE strong_ref_cur;  
END;  
 
ERROR at line 8:  
ORA-06550: line 8, column 28:  
PLS-00382: expression is of wrong type  
ORA-06550: line 8, column 4:  
PL/SQL: SQL Statement ignored  


六、使用游标变量注意事项

1、不能在包规范中定义游标变量
2、不能在其它服务器的远程子程序中使用游标变量，不能把游标变量传给通过数据库连接被调用的过程
3、当处理游标变量时，不要一起使用FOR UPDATE和OPEN FOR
4、不能使用比较运算符来测试游标变量的等价性、不等价性或者非空性
5、游标变量不能被赋予NULL值
6、REF CURSOR类型不能在CREATE TABLE或者VIEW语句中使用，因为不存在数据库列的等价数据类型
7、使用游标变量的存储过程只能被用作查询块数据源，它不能用于DML块数据源。
REF CURSOR适合于只依赖于SQL语句中(不是PL/SQL语句中)变量的查询
8、不能在联合数组、嵌套表、或者变长数组中存储游标变量
9、如果向PL/SQL传递主机游标变量，不能在服务器检索它，除非是在相同服务器调用中打开它

-->Author : Robinson Cheng   
-->Blog    : http://blog.csdn.net/robinson_0612