前面介绍游标的时候,可以看到,美定义一个游标,就为其绑定一个查询语句,这种游标称为静态游标。游标变量是另一种类型的游标,在定义时并不绑定到具体的查询,而是可以打开任何类型兼容的查询,灵活性相当大。
静态游标和游标变量就像是常量与变量的区别,游标变量优点类似于高级语言中的指针,它指向的是一块内存地址而不是具体的内容,因此声明一个游标变量其实就是创建了一个指针,而不是创建了具体的内容。在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_CURSOR
,ROWTYPE_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;
比如这样是错的:
--创建包规范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;
联系客服