Oracle PL/SQL开发基础(第二十六弹:游标变量)

时间:2021-07-30 11:40:16

前面介绍游标的时候,可以看到,美定义一个游标,就为其绑定一个查询语句,这种游标称为静态游标。游标变量是另一种类型的游标,在定义时并不绑定到具体的查询,而是可以打开任何类型兼容的查询,灵活性相当大。

游标变量简介

静态游标和游标变量就像是常量与变量的区别,游标变量优点类似于高级语言中的指针,它指向的是一块内存地址而不是具体的内容,因此声明一个游标变量其实就是创建了一个指针,而不是创建了具体的内容。在PL/SQL中,指针使用REF作用前缀进行定义,因此游标变量就是REF CURSOR类型。
如:

DECLARE
TYPE emp_type IS REF CURSOR RETURN emp%ROWTYPE; --定义游标变量类型
emp_cur emp_type; --声明游标变量
emp_row emp%ROWTYPE; --定义游标结果值变量
BEGIN
OPEN emp_cur FOR SELECT * FROM emp; --打开游标
LOOP
FETCH emp_cur INTO emp_row; --循环提取游标数据
EXIT WHEN emp_cur%NOTFOUND; --循环退出检测
DBMS_OUTPUT.put_line ('员工名称:' || emp_row.ename);
END LOOP;
END;

游标变量创建和使用

游标变量的定义与集合或记录一样,必须通过两个声明步骤来实现一个游标变量的定义:
1. 创建一个游标变量类型
2. 基于该类型创建实际的游标变量

如:

DECLARE
TYPE emp_type IS REF CURSOR RETURN emp%ROWTYPE; --定义游标类型
TYPE gen_type IS REF CURSOR;
emp_cur emp_type; --声明游标变量
gen_cur gen_type;
BEGIN
OPEN emp_cur FOR SELECT * FROM emp WHERE deptno=20;
END;

RETURN是可选的,指定了RETURN后,游标变量是受约束的,要求游标必须具有特定的返回类型,这种游标称为强类型的游标变量。如果不指定RETURN,则游标变量是无约束的,这种游标称为弱类型的游标变量。

从Oracle 9i开始,Oracle提供了一个预定义的弱类型游标类型,名为SYS_REFCURSOR,上例中的gen_type可以直接使用SYS_REFCURSOR来代替。

弱类型的游标变量可以连续打开多次,分别为其赋不同的查询SELECT子句。
重新打开一个游标变量之前不需要关闭它,当用不同的查询语句打开同一个游标变量的时候,上一个查询将被丢弃掉。
如:

DECLARE
TYPE emp_curtype IS REF CURSOR; --定义游标类型
emp_cur emp_curtype; --声明游标类型的变量
BEGIN
OPEN emp_cur FOR SELECT * FROM emp;
--打开游标,查询emp所有列
OPEN emp_cur FOR SELECT empno FROM emp; --打开游标,查询emp表empno列
OPEN emp_cur FOR SELECT deptno FROM dept; --打开游标,查询dept表deptno列
END;

提取游标变量数据的方式四和静态游标没有区别,使用FETCH INTO,同样可以使用BULK COLLECT。当对游标变量操作结束时,也需要CLOSE掉。

处理游标变量异常

常见的异常有INVALID_CURSORROWTYPE_MISMATCH,如:

DECLARE
TYPE emp_curtype IS REF CURSOR; --定义游标类型
emp_cur1 emp_curtype; --声明游标类型的变量
emp_cur2 emp_curtype;
emp_row emp%ROWTYPE; --定义保存游标数据的记录类型
BEGIN
OPEN emp_cur1 FOR SELECT * FROM emp WHERE deptno=20;
--打开第1个游标
FETCH emp_cur1 INTO emp_row; --提取并显示游标信息
DBMS_OUTPUT.PUT_LINE('员工名称:'||emp_row.ename||' 部门编号:'||emp_row.deptno);
FETCH emp_cur2 INTO emp_row; --提取第2个游标变量将引发异常
EXCEPTION
WHEN INVALID_CURSOR THEN --异常处理
emp_cur2:=emp_cur1; --将emp_cur1指向的查询区域赋给emp_cur2
FETCH emp_cur2 INTO emp_row; --现在emp_cur1与emp_cur2指向相同的查询
DBMS_OUTPUT.PUT_LINE('员工名称:'||emp_row.ename||' 部门编号:'||emp_row.deptno);
OPEN emp_cur2 FOR SELECT * FROM emp WHERE deptno=30; --重新打开emp_cur2游标变量,利用相同的查询区域
FETCH emp_cur1 INTO emp_row; --由于emp_cur1与emp_cur2共享相同的查询区域,因此结果相同
DBMS_OUTPUT.PUT_LINE('员工名称:'||emp_row.ename||' 部门编号:'||emp_row.deptno);
END;
DECLARE
TYPE emp_curtype IS REF CURSOR; --定义游标类型
emp_cur emp_curtype; --声明游标类型的变量
emp_row emp%ROWTYPE; --声明游标数据结果类型
dept_row dept%ROWTYPE;
BEGIN
OPEN emp_cur FOR SELECT * FROM emp WHERE deptno=20;
--打开游标变量
FETCH emp_cur INTO dept_row; --提取到一个不匹配的类型中
EXCEPTION
WHEN ROWTYPE_MISMATCH THEN --处理ROWTYPE_MISMATCH异常
FETCH emp_cur INTO emp_row; --再次提取游标变量数据,输出结果
DBMS_OUTPUT.PUT_LINE('员工名称:'||emp_row.ename||' 部门编号:'||emp_row.deptno);
END;

在包中使用游标变量

包的概念在之后才会介绍,这里简要的看个例子:

--创建包规范
CREATE OR REPLACE PACKAGE emp_data_action AS
TYPE emp_type IS REF CURSOR RETURN emp%ROWTYPE; --定义强类型游标类型
--定义使游标变量的子程序
PROCEDURE getempbydeptno(emp_cur IN OUT emp_type,p_deptno NUMBER);
END emp_data_action;

--实现包体
CREATE OR REPLACE PACKAGE BODY emp_data_action AS
--创建在包规范中定义的过程
PROCEDURE getempbydeptno(emp_cur IN OUT emp_type,p_deptno NUMBER) IS
emp_row emp%ROWTYPE;

BEGIN
OPEN emp_cur FOR SELECT * from emp WHERE deptno=p_deptno; --打开游标变量
LOOP
FETCH emp_cur INTO emp_row; --提取数据
EXIT WHEN emp_cur%NOTFOUND;
--输出游标数据
DBMS_OUTPUT.PUT_LINE('员工名称:'||emp_row.ename||' 部门编号:'||emp_row.deptno);
END LOOP;
CLOSE emp_cur;
END;
END emp_data_action;

游标变量的限制

  • 不能在包规范中声明游标变量,只能声明游标类型。
  • 不能在创建表或创建视图的语句中把字段类型指定为REFCURSOR类型,数据库字段是不能存放游标变量值的。
  • 游标类型的参数不支持使用远程过程调用(RPC)将游标变量从一个服务器传递到另一个服务器。
  • 不能用比较操作符来判断两个游标变量是否相等、不相等或者为NULL。
  • 不能为游标变量赋空值。
  • 不能将REF CURSOR类型作为集合的元素类型,也就是说在索引表、嵌套表和 变长数组中不能存放游标变量的值。
  • 不能将在游标中使用的游标FOR循环用在游标变量上,也就是说游标和游标变量不要试图互相替换。

比如这样是错的

--创建包规范
CREATE OR REPLACE PACKAGE emp_data_action_err AS
TYPE emp_type IS REF CURSOR RETURN emp%ROWTYPE; --定义强类型游标类型
emp_cur emp_type;
--定义使游标变量的子程序
PROCEDURE getempbydeptno(emp_cur IN OUT emp_type,p_deptno NUMBER);
END emp_data_action_err;

这样也是错的

DECLARE
TYPE emp_curtype IS REF CURSOR; --定义游标类型
emp_cur emp_curtype; --声明游标类型的变量
BEGIN
FOR emp_row IN emp_cur LOOP
DBMS_OUTPUT.PUT_LINE(emp_row.ename);
END LOOP;
END;