PL/SQL 游标变量

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

一、什么是游标变量     显示游标用于命名一个工作区域,其中保存多行查询的信息,而且该游标始终指向工作区域的内容。而游标变量类似于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;

-->Author : Robinson Cheng
-->Blog   : http://blog.csdn.net/robinson_0612
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传递主机游标变量,不能在服务器检索它,除非是在相同服务器调用中打开它

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

pl/sql中的参数模式(r4笔记第54天)

在平时的工作中,可能通过pl/sql传入参数来做一些特定的操作,参数模式一般有In,out.in out这几种 比如dbms_sqltune下的PREPARE_...

32340
来自专栏乐沙弥的世界

PL/SQL --> 包的创建与管理

包,是一个逻辑集合,是由PL/SQL类型以及PL/SQL子程序的集合。PL/SQL类型包括table类型,record类型。PL/SQL项则包括游标,游标...

8620
来自专栏不想当开发的产品不是好测试

mysql @value := 用法

背景 有这么一张表,记录名字和分数,现在需要按照成绩排名,存在并列名次的情况 ? 解决方法 思路:按照score从大到小排序,第一行数据就是第一名,第二行就是第...

31780
来自专栏杨建荣的学习笔记

简单实用的sql小技巧(第二篇)(r3笔记第86天)

ASCII和CHR 在平时的工作中,可能会在sql或者pl/sql块中嵌入特殊字符,比如空格,回车之类。这个时候可以使用ascii和chr来做字符和ascii码...

31480
来自专栏lgp20151222

java 调用mysql存储过程

由于本人的码云太多太乱了,于是决定一个一个的整合到一个springboot项目里面。

28020
来自专栏xingoo, 一个梦想做发明家的程序员

Elasticsearch——分词器对String的作用

关于String类型——分词与不分词 在Elasticsearch中String是最基本的数据类型,如果不是数字或者标准格式的日期等这种很明显的类型,其他的一般...

33180
来自专栏kevindroid

room的使用-以demo为例

52130
来自专栏JavaEdge

2018-08-24

函数可以嵌入在sql中使用的,可以在select中调用 存储过程要让sql的query 可以执行, 需要把 mysql_real_connect 的最后一个参...

7020
来自专栏Java帮帮-微信公众号-技术文章全总结

Oracle存储过程

Oracle存储过程 oracle 存储过程的基本语法 1.基本结构 CREATE OR REPLACE PROCEDURE 存储过程名字 ( 参数...

46350
来自专栏信安之路

sqlmap自带的tamper你了解多少?

sqlmap 是一款注入神器广为人知,里面的 tamper 常常用来绕过 WAF ,很实用的模块,但是却常常被新手忽略(比如我),今天就整理总结一下 tampe...

12000

扫码关注云+社区

领取腾讯云代金券