本文深入探讨了PL/SQL中的游标概念，包括游标变量的使用方法、游标属性的功能及应用场景，以及游标表达式的特性。文章通过丰富的示例讲解了如何声明、打开、关闭游标变量，并介绍了如何在不同场景下高效使用游标。
其它的OPEN-FOR语句可以使用不同的查询打开同样的游标变量。在重新打开游标变量之前是不需要关闭它的(但对一个静态游标使用多次OPEN操作时，系统会抛出预定义异常CURSOR_ALREADY_OPEN)。为执行一个不同的查询而重新打开游标变量时，前面的查询结果就会丢失。
一般地，我们可以把游标变量传递给过程，然后由过程负责打开它，如下例，打包过程打开游标变量emp_cv：
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 PACKAGE BODY emp_data AS
...
PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp) IS
BEGIN
OPEN emp_cv FOR
SELECT *
FROM emp;
END open_emp_cv;
END emp_data;
当我们把游标变量声明为一个打开游标变量的子程序的形式参数时，就必须要指定参数模式为IN OUT模式。那样的话，子程序就可以把一个打开的游标变量返回给调用者。
另外，我们还可以使用独立的过程来打开游标变量。只要简单的在包里定义REF CURSOR类型，然后在一个独立的过程中引用它就行了。例如，如果我们创建了下面的无体包，我们就能在独立的过程中引用包中所定义的REF CURSOR了：
CREATE PACKAGE cv_types AS
TYPE genericcurtyp IS REF CURSOR ;
TYPE empcurtyp IS REF CURSOR
RETURN emp%ROWTYPE ;
TYPE deptcurtyp IS REF CURSOR
RETURN dept%ROWTYPE ;
...
END cv_types;
下例中，我们创建一个引用REF CURSOR类型EmpCurTyp的过程，这个类型是在包cv_types中定义的。
CREATE PROCEDURE open_emp_cv (emp_cv IN OUT cv_types.empcurtyp) AS
BEGIN
OPEN emp_cv FOR
SELECT *
FROM emp;
END open_emp_cv;
为把数据检索集中化处理，我们可以把一个存储过程中类型兼容的查询进行分组。在下面的例子中，打包过程声明了一个选择器作为它的形势参数。调用时，过程会为选定的查询打开游标变量：
CREATE PACKAGE emp_data AS
TYPE empcurtyp IS REF CURSOR
RETURN emp%ROWTYPE ;
PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp, choice INT);
END emp_data;
CREATE PACKAGE BODY emp_data AS
PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp, choice INT) IS
BEGIN
IF choice = 1 THEN
OPEN emp_cv FOR
SELECT *
FROM emp
WHERE comm IS NOT NULL ;
ELSIF choice = 2 THEN
OPEN emp_cv FOR
SELECT *
FROM emp
WHERE sal > 2500;
ELSIF choice = 3 THEN
OPEN emp_cv FOR
SELECT *
FROM emp
WHERE deptno = 20;
END IF ;
END ;
END emp_data;
为了获取更大的灵活性，我们可以把游标变量和选择器传递给过程，让它执行查询然后返回不同的查询结果。如下例所示：
CREATE PACKAGE admin_data AS
TYPE gencurtyp IS REF CURSOR ;
PROCEDURE open_cv (generic_cv IN OUT gencurtyp, choice INT);
END admin_data;
CREATE PACKAGE BODY admin_data AS
PROCEDURE open_cv (generic_cv IN OUT gencurtyp, choice INT) IS
BEGIN
IF choice = 1 THEN
OPEN generic_cv FOR
SELECT *
FROM emp;
ELSIF choice = 2 THEN
OPEN generic_cv FOR
SELECT *
FROM dept;
ELSIF choice = 3 THEN
OPEN generic_cv FOR
SELECT *
FROM salgrade;
END IF ;
END ;
END admin_data;
- 使用游标变量作为主变量
我们可以在OCI或Pro*C程序这样的PL/SQL主环境中声明游标变量。在使用游标变量之前，我们需要把它作为主变量传递给PL/SQL。在下面的Pro*C例子中，我们把主游标变量和选择器一并传递给PL/SQL块，然后为被选择的查询打开游标变量：
EXEC SQL BEGIN DECLARE SECTION;
...
/* Declare host cursor variable. */
SQL_CURSOR generic_cv;
int choice;
EXEC SQL END DECLARE SECTION;
...
/* Initialize host cursor variable. */
EXEC SQL ALLOCATE :generic_cv;
...
/* Pass host cursor variable and selector to PL/SQL block. */
EXEC SQL EXECUTE
BEGIN
IF :choice = 1 THEN
OPEN :generic_cv FOR
SELECT *
FROM emp;
ELSIF :choice = 2 THEN
OPEN :generic_cv FOR
SELECT *
FROM dept;
ELSIF :choice = 3 THEN
OPEN :generic_cv FOR
SELECT *
FROM salgrade;
END IF ;
END ;
END -EXEC;
主游标变量与任何查询的返回类型都兼容，它们就像PL/SQL中的弱类型游标变量一样。
- 从游标变量中取得数据
FETCH语句能从多行查询的结果集中取得数据，语法如下：
FETCH {cursor_variable_name | :host_cursor_variable_name}
[BULK COLLECT ]
INTO {variable_name[, variable_name]... | record_name};
下面的例子中，我们每次都从游标变量emp_cv中取出一条数据放到用户定义的记录emp_rec中：
LOOP
/* Fetch from cursor variable. */
FETCH emp_cv
INTO emp_rec;
EXIT WHEN emp_cv%NOTFOUND; -- exit when last row is fetched
-- process data record
END LOOP ;
我们可以使用BULK COLLECT子句批量地从游标变量中取得数据放到一个或多个集合中。如下例所示：
DECLARE
TYPE empcurtyp IS REF CURSOR
RETURN emp%ROWTYPE ;
TYPE namelist IS TABLE OF emp.ename%TYPE ;
TYPE sallist IS TABLE OF emp.sal%TYPE ;
emp_cv empcurtyp;
names namelist;
sals sallist;
BEGIN
OPEN emp_cv FOR
SELECT ename, sal
FROM emp;
FETCH emp_cv
BULK COLLECT INTO names, sals;
...
END ;
当游标变量被打开时，关联查询中的所有变量都会被计算。如果要改变查询中的结果集或要使用变量的最新值，我们就必须重新打开游标变量。不过我们可以为每一个从游标变量中取得数据使用不同的INTO子句。
PL/SQL能保证游标变量的返回类型与FETCH语句中的INTO子句后面的类型相兼容。对于游标变量的关联查询返回的每一个字段，INTO子句后面都必须有一个与之相对应的、类型兼容的域或变量。同样，字段的个数和域的个数也应该相同。否则的话，就会产生错误。如果游标变量是强类型的话，这个错误在编译期就会发生；如果是弱类型，错误会在运行时发生。在运行时，PL/SQL会在第一次取得数据之前抛出预定义异常 ROWTYPE_MISMATCH。所以，如果我们捕获到错误，并使用一个不同的INTO子句再次执行FETCH语句，就不会丢失数据。
如果我们把游标变量声明为从游标变量中取得数据的子程序的形式参数，那么我们必须指定参数模式为IN或IN OUT模式。但是，如果在子程序中还需要打开游标变量的话，就必须使用IN OUT模式。
如果我们从一个已经关闭了的或是一个未打开的游标变量中选取数据，PL/SQL就会抛出预定义异常INVALID_CURSOR。
- 关闭游标变量
CLOSE语句会关闭游标变量。如果执行了关闭操作，相关的结果集就不确定了。关闭操作的语法如下：
CLOSE {cursor_variable_name | :host_cursor_variable_name);
在下面的例子中，当最后一行数据也被处理完毕时，我们就可以关闭游标变量emp_cv：
LOOP
FETCH emp_cv
INTO emp_rec;
EXIT WHEN emp_cv%NOTFOUND;
-- process data record
END LOOP ;
/* Close cursor variable. */
CLOSE emp_cv;
当把游标变量作为用于关闭游标变量的子程序的形式参数时，我们必须指定它的参数模式为IN或IN OUT模式。
如果我们从一个已经关闭了的或是一个未打开的游标变量中选取数据，PL/SQL就会抛出预定义异常INVALID_CURSOR。
6、游标变量示例：主从表
思考下面的存储过程，它的作用是搜索图书馆数据库中的图书、期刊和磁带。主表存放标题和类别编号(其中1=书，2=期刊，3=磁带)。三个详细表分别保存特定类别的信息。在调用时，存储过程会按照标题来搜索主表，然后利用主表提供的类别编号到从详细表中检索详细内容。
CREATE PACKAGE cv_types AS
TYPE libcurtyp IS REF CURSOR ;
...
END cv_types;
CREATE PROCEDURE FIND_ITEM (title VARCHAR2 ,
lib_cv IN OUT cv_types.libcurtyp) AS
code BINARY_INTEGER ;
BEGIN
SELECT item_code
INTO code
FROM titles
WHERE item_title = title;
IF code = 1 THEN
OPEN lib_cv FOR
SELECT *
FROM books
WHERE book_title = title;
ELSIF code = 2 THEN
OPEN lib_cv FOR
SELECT *
FROM periodicals
WHERE periodical_title = title;
ELSIF code = 3 THEN
OPEN lib_cv FOR
SELECT *
FROM tapes
WHERE tape_title = title;
END IF ;
END FIND_ITEM;
7、游标变量示例：客户端PL/SQL块
一个客户端应用程序可能会使用下面的PL/SQL块来显示检索出来的信息：
DECLARE
lib_cv cv_types.libcurtyp;
book_rec books%ROWTYPE ;
periodical_rec periodicals%ROWTYPE ;
tape_rec tapes%ROWTYPE ;
BEGIN
get_title (:title); -- title is a host variable
FIND_ITEM (:title, lib_cv);
FETCH lib_cv
INTO book_rec;
display_book (book_rec);
EXCEPTION
WHEN ROWTYPE_MISMATCH THEN
BEGIN
FETCH lib_cv
INTO periodical_rec;
display_periodical (periodical_rec);
EXCEPTION
WHEN ROWTYPE_MISMATCH THEN
FETCH lib_cv
INTO tape_rec;
display_tape (tape_rec);
END ;
END ;
8、游标变量示例：Pro*C程序
下面的Pro*C程序让用户选择一张数据表，然后使用游标变量进行查询，并返回查询结果：
#include <stdio.h>
#include <sqlca.h>
void sql_error();
main()
{
char temp[32];
EXEC SQL BEGIN DECLARE SECTION;
char * uid = "scott/tiger" ;
SQL_CURSOR generic_cv; /* cursor variable */
int table_num; /* selector */
struct /* EMP record */
{
int emp_num;
char emp_name[11];
char job_title[10];
int manager;
char hire_date[10];
float salary;
float commission;
int dept_num;
} emp_rec;
struct /* DEPT record */
{
int dept_num;
char dept_name[15];
char location[14];
} dept_rec;
struct /* BONUS record */
{
char emp_name[11];
char job_title[10];
float salary;
} bonus_rec;
EXEC SQL END DECLARE SECTION;
/* Handle Oracle errors. */
EXEC SQL WHENEVER SQLERROR DO sql_error();
/* Connect to Oracle. */
EXEC SQL CONNECT :uid;
/* Initialize cursor variable. */
EXEC SQL ALLOCATE :generic_cv;
/* Exit loop when done fetching. */
EXEC SQL WHENEVER NOT FOUND DO break;
for (;;)
{
printf("\n1 = EMP, 2 = DEPT, 3 = BONUS" );
printf("\nEnter table number (0 to quit): " );
gets(temp);
table_num = atoi(temp);
if (table_num <= 0) break;
/* Open cursor variable. */
EXEC SQL EXECUTE
BEGIN
IF :table_num = 1 THEN
OPEN :generic_cv FOR
SELECT *
FROM emp;
ELSIF :table_num = 2 THEN
OPEN :generic_cv FOR
SELECT *
FROM dept;
ELSIF :table_num = 3 THEN
OPEN :generic_cv FOR
SELECT *
FROM bonus;
END IF ;
END ;
END -EXEC;
for (;;)
{
switch (table_num)
{
case 1: /* Fetch row into EMP record. */
EXEC SQL FETCH :generic_cv INTO :emp_rec;
break;
case 2: /* Fetch row into DEPT record. */
EXEC SQL FETCH :generic_cv INTO :dept_rec;
break;
case 3: /* Fetch row into BONUS record. */
EXEC SQL FETCH :generic_cv INTO :bonus_rec;
break;
}
/* Process data record here. */
}
/* Close cursor variable. */
EXEC SQL CLOSE :generic_cv;
}
exit(0);
}
void sql_error()
{
/* Handle SQL error here. */
}
9、游标变量示例：SQL*Plus中操作主变量
主变量就是一个声明在主环境中的变量，它会被传递到一个或多个PL/SQL程序中，在程序中可以跟其他的变量一样使用。在SQL*Plus环境里，可以使用命令VARIABLE来声明主变量。例如，我们可以像下面这样声明一个NUMBER类型的主变量：
VARIABLE return_code NUMBER
SQL*Plus和PL/SQL都能引用主变量，SQL*Plus还可以显示主变量的值。但是，在PL/SQL中引用主变量的时候，我们必须加上冒号(:)前缀，如下例所示：
DECLARE
...
BEGIN
:return_code := 0;
IF credit_check_ok(acct_no) THEN
:return_code := 1;
END IF ;
...
END ;
在SQL*Plus环境里，我们可以使用PRINT命令来显示主变量的值，例如：
SQL > PRINT return_code
RETURN_CODE
-----------
1
SQL*Plus中的REF CURSOR数据类型可以让我们声明游标变量，这样就可以使用存储子程序返回的查询结果。在下面的脚本中，我们声明了REFCURSOR类型的主变量。我们还可以在SQL*Plus中使用SET AUTOPRINT ON命令来自动地显示查询结果：
CREATE PACKAGE emp_data AS
TYPE emprectyp IS RECORD (
emp_id NUMBER (4),
emp_name VARCHAR2 (10),
job_title VARCHAR2 (9),
dept_name VARCHAR2 (14),
dept_loc VARCHAR2 (13)
);
TYPE empcurtyp IS REF CURSOR
RETURN emprectyp;
PROCEDURE get_staff (dept_no IN NUMBER , emp_cv IN OUT empcurtyp);
END ;
/
CREATE PACKAGE BODY emp_data AS
PROCEDURE get_staff (dept_no IN NUMBER , emp_cv IN OUT empcurtyp) IS
BEGIN
OPEN emp_cv FOR
SELECT empno, ename, job, dname, loc
FROM emp, dept
WHERE emp.deptno = dept_no AND emp.deptno = dept.deptno
ORDER BY empno;
END ;
END ;
/
COLUMN EMPNO HEADING Number
COLUMN ENAME HEADING Name
COLUMN JOB HEADING JobTitle
COLUMN DNAME HEADING Department
COLUMN LOC HEADING Location
SET AUTOPRINT ON
VARIABLE cv REFCURSOR
EXECUTE emp_data.get_staff(20, :cv)
10、在向PL/SQL块传递主游标变量时减少网络负荷
在向PL/SQL传递主游标变量时，我们可以把多个OPEN-FOR语句组合在一起使用，以便减少网络流量。例如，下面的PL/SQL块：
/* anonymous PL/SQL block in host environment */
BEGIN
OPEN :emp_cv FOR
SELECT *
FROM emp;
OPEN :dept_cv FOR
SELECT *
FROM dept;
OPEN :grade_cv FOR
SELECT *
FROM salgrade;
OPEN :pay_cv FOR
SELECT *
FROM payroll;
OPEN :ins_cv FOR
SELECT *
FROM insurance;
END ;
在Oracle Forms中，这种方法可能很有用，比如我们在填充一个多模块窗体的时候。
当我们向PL/SQL块传递一个主游标变量时(游标变量由该块负责打开)，游标变量指向的查询工作区在块结束后还是能够被访问的。这就能让我们的OCI或Pro*C程序在普通的游标操作中继续使用这些工作区。下面的例子中，我们在一个块中打开了多个这样的工作区：
BEGIN
OPEN :c1 FOR
SELECT 1
FROM DUAL;
OPEN :c2 FOR
SELECT 1
FROM DUAL;
OPEN :c3 FOR
SELECT 1
FROM DUAL;
OPEN :c4 FOR
SELECT 1
FROM DUAL;
OPEN :c5 FOR
SELECT 1
FROM DUAL;
...
END ;
赋给c1、c1、c1、c1、c1的游标都可以正常使用。当使用完毕时，只要像下面这样简单的关闭就可以了：
BEGIN
CLOSE :c1;
CLOSE :c2;
CLOSE :c3;
CLOSE :c4;
CLOSE :c5;
...
END ;
11、避免游标变量的错误
如果在赋值操作中的两个游标变量都是强类型，那么它们必须有着完全相同的数据类型。下面的例子中，虽然游标变量的返回类型相同，但是在赋值操作时也会引起异常，这是因为它们的数据类型不相同：
DECLARE
TYPE empcurtyp IS REF CURSOR
RETURN emp%ROWTYPE ;
TYPE tmpcurtyp IS REF CURSOR
RETURN emp%ROWTYPE ;
PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp, tmp_cv IN OUT tmpcurtyp) IS
BEGIN
...
emp_cv := tmp_cv; -- causes 'wrong type' error
END ;
如果其中一个或两个游标变量是弱类型，那它们就没必要类型相同了。
如果我们要对一个没有指向工作区的游标变量进行数据取得、关闭或调用游标属性的操作，PL/SQL就会跑出一个INVALID_CURSOR异常。我们有两种方法可以让游标变量(或参数)指向工作区：
- 对游标变量使用OPEN-FOR语句。
- 把一个已经OPEN过的主游标变量赋给PL/SQL游标变量。
下面的例子演示了如何使用这两种方法：
DECLARE
TYPE empcurtyp IS REF CURSOR
RETURN emp%ROWTYPE ;
emp_cv1 empcurtyp;
emp_cv2 empcurtyp;
emp_rec emp%ROWTYPE ;
BEGIN
/* The following assignment is useless because emp_cv1
does not point to a query work area yet. */
emp_cv2 := emp_cv1; -- useless
/* Make emp_cv1 point to a query work area. */
OPEN emp_cv1 FOR
SELECT *
FROM emp;
/* Use emp_cv1 to fetch first row from emp table. */
FETCH emp_cv1
INTO emp_rec;
/* The following fetch raises an exception because emp_cv2
does not point to a query work area yet. */
FETCH emp_cv2
INTO emp_rec; -- raises INVALID_CURSOR
EXCEPTION
WHEN INVALID_CURSOR THEN
/* Make emp_cv1 and emp_cv2 point to same work area. */
emp_cv2 := emp_cv1;
/* Use emp_cv2 to fetch second row from emp table. */
FETCH emp_cv2
INTO emp_rec;
/* Reuse work area for another query. */
OPEN emp_cv2 FOR
SELECT *
FROM old_emp;
/* Use emp_cv1 to fetch first row from old_emp table.
The following fetch succeeds because emp_cv1 and
emp_cv2 point to the same query work area. */
FETCH emp_cv1
INTO emp_rec; -- succeeds
END ;
把游标变量当作参数传递时一定要小心。在运行时，如果实参和形参的返回类型不兼容，PL/SQL就会抛出ROWTYPE_MISMATCH异常。
在下面的Pro*C程序中，我们定义了打包REF CURSOR类型，并指明返回类型为emp%ROWTYPE。下一步，创建一个引用这个新类型的过程。然后在PL/SQL块内为表dept的查询打开主游标变量。但是，在把打开的游标变量传递给存储过程的时候，由于形参和实参的返回类型不兼容，PL/SQL就抛出了ROWTYPE_MISMATCH异常。
CREATE PACKAGE cv_types AS
TYPE empcurtyp IS REF CURSOR
RETURN emp%ROWTYPE ;
...
END cv_types;
/
CREATE PROCEDURE open_emp_cv (emp_cv IN OUT cv_types.empcurtyp) AS
BEGIN
OPEN emp_cv FOR
SELECT *
FROM emp;
END open_emp_cv;
/
-- anonymous PL/SQL block in Pro*C program
EXEC SQL EXECUTE
BEGIN
OPEN :cv FOR
SELECT *
FROM dept;
...
open_emp_cv (:cv); -- raises ROWTYPE_MISMATCH
END ;
END -EXEC;
12、游标变量的限制
目前，游标变量有以下限制：
- 不能在包中声明游标变量。例如，下面的声明就是不允许的：
CREATE PACKAGE emp_stuff AS
TYPE empcurtyp IS REF CURSOR
RETURN emp%ROWTYPE ;
emp_cv empcurtyp; -- not allowed
END emp_stuff; - 处于另外一个服务器上的远程子程序不能接受游标变量的值。因此，我们不能使用RPC将游标变量从一个服务器传递到另一个服务器。
- 如果我们把主游标变量传递到PL/SQL中，就不能在服务器端从这个游标变量中取得数据了，除非打开这个游标变量的操作也是在同一个服务器调用中进行的。
- 不能简单的用比较操作符来判断两个游标变量是否是相等，不相等或空。
- 不能为游标变量赋空值。
- 不能在CREATE TABLE或CREATE VIEW语句中把字段类型指定为REF CURSOR类型。因为数据库字段是不能存放游标变量值的。
- 不能把REF CURSOR类型作为集合的元素类型，也就是说，索引表，嵌套表和变长数组不能存放游标变量的值。
- 游标和游标变量不能互相替换。如下例中，不能把适用于游标的FOR循环应用在游标变量上：
DECLARE
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE ;
emp_cv EmpCurTyp;
...
BEGIN
...
FOR emp_rec IN emp_cv LOOP ... -- not allowed
END ;
六、使用游标属性
每个显示的游标和游标变量都有四个属性：%FOUND、%ISOPEN、%NOTFOUND和%ROWCOUNT。这些属性都能返回关于数据操作语句执行的有用信息。我们可以在过程化语句中使用游标属性，但不能在SQL语句中使用。
1、显式游标属性一览
每个显式的游标和游标变量都有四个属性：%FOUND、%ISOPEN、%NOFOUND和%ROWCOUNT。我们可以在过程语句中使用这些属性，但不能再SQL语句中使用。
显式游标属性能返回多行查询的信息。当一个显式游标或游标变量被打开时，满足查询条件的行就会被做上标记，最终形成结果集。然后我们就可以就从结果集中取出行数据。
- %FOUND属性：行被取出了吗？
在一个游标或游标变量被打开后且在首次取得数据之前，%FOUND会产生NULL值。而此后每取得一行数据，它的值就为TRUE，直到最后一次取得数据失败，它的值变成FALSE。下例中，我们利用%FOUND的值来进行条件判断：
LOOP
FETCH c1
INTO my_ename, my_sal, my_hiredate;
IF c1%FOUND THEN -- fetch succeeded
...
ELSE -- fetch failed, so exit loop
EXIT ;
END IF ;
END LOOP ;
在没有打开游标或游标变量的时候使用%FOUND会引起预定义异常INVALID_CURSOR。
- %ISOPEN属性：游标打开了吗？
%ISOPEN在对应的游标或游标变量打开的时候返回TRUE;否则返回FALSE。下例中，我们用%ISOPEN来进行条件判断：
IF c1%ISOPEN THEN -- cursor is open
...
ELSE -- cursor is closed, so open it
OPEN c1;
END IF ;
- %NOTFOUND属性：FETCH失败了吗？
%NOTFOUND属性的作用和%FOUND属性正好相反。它在数据取得失败时返回TRUE，成功时返回FALSE。在下面的例子中，当FETCH语句没有取得数据的时候，我们使用%NOTFOUND来退出循环：
LOOP
FETCH c1
INTO my_ename, my_sal, my_hiredate;
EXIT WHEN c1%NOTFOUND;
...
END LOOP ;
在取数据之前，%NOTFOUND会返回NULL。所以，如果FETCH从来都没有成功执行的话，循环就不会退出。那是因为只有EXIT WHEN语句中的WHEN子句值为TRUE时，这条语句才能被执行。为了安全起见，我们可以使用下面的EXIT语句进行替换：
EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL ;
在没有打开游标或游标变量的时候使用%NOTFOUND会引起预定义异常INVALID_CURSOR。
- %ROWCOUNT属性：已经取得了多少条数据？
当游标或游标变量被打开时，%ROWCOUNT值为零。每成功取得一条数据，%ROWCOUNT的值就加一。下例中，我们用%ROWCOUNT来判断取得的数据是否超过十条，然后采取相关的对策：
LOOP
FETCH c1
INTO my_ename, my_deptno;
IF c1%ROWCOUNT > 10 THEN
...
END IF ;
...
END LOOP ;
在没有打开游标或游标变量的时候使用%ROWCOUNT会引起预定义异常INVALID_CURSOR。
下表是执行OPEN、FETCH或CLOSE语句前后对应的游标属性值：
| %FOUND | %ISOPEN | %NOTFOUND | %ROWCOUNT | ||
|---|---|---|---|---|---|
| OPEN | 之前 | 异常 | FALSE | 异常 | 异常 |
| OPEN | 之后 | NULL | TRUE | NULL | 0 |
| First FETCH | 之前 | NULL | TRUE | NULL | 0 |
| First FETCH | 之后 | TRUE | TRUE | FALSE | 1 |
| Next FETCH(es) | 之前 | TRUE | TRUE | FALSE | 1 |
| Next | 之后 | TRUE | TRUE | FALSE | 与数据条数相关 |
| Last FETCH | 之前 | TRUE | TRUE | FALSE | 与数据条数相关 |
| Last FETCH | 之后 | FALSE | TRUE | TRUE | 与数据条数相关 |
| CLOSE | 之前 | FALSE | TRUE | TRUE | 与数据条数相关 |
| CLOSE | 之后 | 异常 | FALSE | 异常 | 异常 |
注意：
| |||||
- 游标属性的一些实例
假设我们有一个名为data_table的数据表，用它来收集实验室的实验数据，并且我们需要分析实验1的数据。在下面的例子中，我们可以计算出实验结果并把它们放到一个名为temp的数据表中。
DECLARE
num1 data_table.n1%TYPE ; -- Declare variables
num2 data_table.n2%TYPE ; -- having same types as
num3 data_table.n3%TYPE ; -- database columns
RESULT temp.col1%TYPE ;
CURSOR c1 IS
SELECT n1, n2, n3
FROM data_table
WHERE exper_num = 1;
BEGIN
OPEN c1;
LOOP
FETCH c1
INTO num1, num2, num3;
EXIT WHEN c1%NOTFOUND; -- TRUE when FETCH finds no more rows
RESULT := num2 / (num1 + num3);
INSERT INTO temp
VALUES (RESULT, NULL , NULL );
END LOOP ;
CLOSE c1;
COMMIT ;
END ;
在接下来的例子中，我们会检查所有那些包含零件号码为5469的贮藏器，把它们的内容提取出来直到累计到1000个单位：
DECLARE
CURSOR bin_cur (part_number NUMBER ) IS
SELECT amt_in_bin
FROM bins
WHERE part_num = part_number AND amt_in_bin > 0
ORDER BY bin_num
FOR UPDATE OF amt_in_bin;
bin_amt bins.amt_in_bin%TYPE ;
total_so_far NUMBER (5) := 0;
amount_needed CONSTANT NUMBER (5) := 1000;
bins_looked_at NUMBER (3) := 0;
BEGIN
OPEN bin_cur (5469);
WHILE total_so_far < amount_needed LOOP
FETCH bin_cur
INTO bin_amt;
EXIT WHEN bin_cur%NOTFOUND;
-- if we exit, there's not enough to fill the order
bins_looked_at := bins_looked_at + 1;
IF total_so_far + bin_amt < amount_needed THEN
UPDATE bins
SET amt_in_bin = 0
WHERE CURRENT OF bin_cur;
-- take everything in the bin
total_so_far := total_so_far + bin_amt;
ELSE -- we finally have enough
UPDATE bins
SET amt_in_bin = amt_in_bin - (amount_needed - total_so_far)
WHERE CURRENT OF bin_cur;
total_so_far := amount_needed;
END IF ;
END LOOP ;
CLOSE bin_cur;
INSERT INTO temp
VALUES (NULL , bins_looked_at, '<- bins looked at' );
COMMIT ;
END ;
2、 隐式游标属性一览
隐式游标属性会返回一些关于INSERT、UPDATE、DELETE和SELECT INTO语句的执行信息。这些属性值总是与最近一次执行的语句相关。在Oracle打开SQL游标之前，隐式游标的所有属性都是NULL。
要注意SQL游标还有另外一个专门为FORALL语句设计的%BULK_ROWCOUNT属性。
隐式游标属性和显式游标相同，也有%FOUND、%ISOPEN、%NOTFOUND和%ROWCOUNT，并且这些属性的用法也和显式游标的类似，这里就不再详细说明。由于Oracle在执行完语句后会自动关闭SQL游标，所以SQL游标的%ISOPEN属性值总是FALSE。
- 隐式游标属性的使用准则
隐式游标的属性值总是与最后一次执行的语句相关，无论这个语句处于哪个作用域。所以，如果我们想把一个属性值保存起来以便以后使用，就要立即把它赋给一个布尔变量。下面的例子中依赖于IF语句的条件是很危险的，因为过程check_status可能会改变属性%NOTFOUND的值：
BEGIN
...
UPDATE parts SET quantity = quantity - 1 WHERE partno = part_id;
check_status(part_id); -- procedure call
IF SQL %NOTFOUND THEN -- dangerous!
...
END ;
END ;
我们可以像下面这样改善代码：
BEGIN
...
UPDATE parts SET quantity = quantity - 1 WHERE partno = part_id;
sql_notfound := SQL %NOTFOUND; -- assign value to Boolean variable
check_status(part_id);
IF sql_notfound THEN ...
END ;
如果SELECT INTO没有返回结果，PL/SQL就会抛出预定义异常NO_DATA_FOUND。如下例：
BEGIN
...
SELECT sal INTO my_sal FROM emp WHERE empno = my_empno;
-- might raise NO_DATA_FOUND
IF SQL %NOTFOUND THEN -- condition tested only when false
... -- this action is never taken
END IF ;
上面的检查是没有作用的，因为IF语句只在%NOTFOUND值是假的情况下才能进行检查。当PL/SQL抛出NO_DATA_FOUND异常，正常的执行就会终止，控制权被交给异常处理部分。
但一个调用聚合函数的SELECT INTO语句从来不会抛出异常NO_DATA_FOUND，因为聚合函数总会返回一个值或空。在这种情况下，%NOTFOUND就会产生FALSE，如下例：
BEGIN
...
SELECT MAX (sal) INTO my_sal FROM emp WHERE deptno = my_deptno;
-- never raises NO_DATA_FOUND
IF SQL %NOTFOUND THEN -- always tested but never true
... -- this action is never taken
END IF ;
EXCEPTION
WHEN NO_DATA_FOUND THEN ... -- never invoked
七、使用游标表达式
一个游标表达式能返回一个嵌套游标。结果集中的每一行跟平常一样，每个字段都包含一些值，其中的一些包含的是嵌套游标。因此，一个独立的查询就能返回从多个数据表间检索出来的相关值。我们可以用嵌套循环来处理结果集,然后再处理每一行中的嵌套游标。
PL/SQL支持把游标表达式作为游标声明、REF CURSOR声明和游标变量的一部分的查询。我们还可以在动态游标查询中使用游标表达式。语法如下：
CURSOR ( subquery )
在从父级游标取得数据时，嵌套游标就会被隐式地打开。嵌套游标只有在下面的情况下才会被关闭：
- 嵌套游标被用户显式地关闭
- 父级游标被重新执行
- 父级游标被关闭
- 父级游标被取消
- 在从嵌套游标的一个父级游标中取数据时发生错误。嵌套游标会被作为清理内容的一部分而被关闭
1、游标表达式的约束
- 不能在隐式游标中使用游标表达式
- 游标表达式只能出现在：
- 非子查询的SELECT语句中，并且这条语句不能是游标表达式本身的子查询
- 作为table函数的参数，出现在SELECT语句的FROM子句中
- 游标表达式只能出现在查询说明的SELECT列表中
- 游标表达式不能出现在视图声明中
- 不能对游标表达式进行BIND和EXECUTE操作
2、游标表达式的示例
下例中，我们要用一个游标取出某个指定ID的地区中所有的部门。在我们取得每一个部门名称的过程中，我们也会从另一张表取出该部门雇员的详细信息。
CREATE OR REPLACE PROCEDURE emp_report (p_locid NUMBER ) IS
TYPE refcursor IS REF CURSOR ;
-- The query returns only 2 columns, but the second column is
-- a cursor that lets us traverse a set of related information.
CURSOR c1 IS
SELECT l.city,
CURSOR (SELECT d.department_name,
CURSOR (SELECT e.last_name
FROM employees e
WHERE e.department_id = d.department_id
) AS ename
FROM departments d
WHERE l.location_id = d.location_id
) dname
FROM locations l
WHERE l.location_id = p_locid;
loccur refcursor;
deptcur refcursor;
empcur refcursor;
v_city locations.city%TYPE ;
v_dname departments.department_name%TYPE ;
v_ename employees.last_name%TYPE ;
BEGIN
OPEN c1;
LOOP
FETCH c1
INTO v_city, loccur;
EXIT WHEN c1%NOTFOUND;
-- We can access the column C1.city, then process the results of
-- the nested cursor.
LOOP
FETCH loccur
INTO v_dname, deptcur; -- No need to open
EXIT WHEN loccur%NOTFOUND;
LOOP
FETCH deptcur
INTO v_ename; -- No need to open
EXIT WHEN deptcur%NOTFOUND;
DBMS_OUTPUT.put_line (v_city || ' ' || v_dname || ' ' || v_ename);
END LOOP ;
END LOOP ;
END LOOP ;
CLOSE c1;
END ;
/
扫一扫
107
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
