PL\SQL用户指南与参照6.2 转载

其它的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异常。我们有两种方法可以让游标变量(或参数)指向工作区：

1. 对游标变量使用OPEN-FOR语句。

1. 把一个已经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、游标变量的限制

目前，游标变量有以下限制：

1. 不能在包中声明游标变量。例如，下面的声明就是不允许的：

   CREATE?PACKAGE?emp_stuff?AS ??TYPE?empcurtyp?IS?REF?CURSOR ????RETURN?emp%ROWTYPE; ??emp_cv???empcurtyp;???--?not?allowed END?emp_stuff;

2. 处于另外一个服务器上的远程子程序不能接受游标变量的值。因此，我们不能使用RPC将游标变量从一个服务器传递到另一个服务器。
3. 如果我们把主游标变量传递到PL/SQL中，就不能在服务器端从这个游标变量中取得数据了，除非打开这个游标变量的操作也是在同一个服务器调用中进行的。
4. 不能简单的用比较操作符来判断两个游标变量是否是相等，不相等或空。
5. 不能为游标变量赋空值。
6. 不能在CREATE TABLE或CREATE VIEW语句中把字段类型指定为REF CURSOR类型。因为数据库字段是不能存放游标变量值的。
7. 不能把REF CURSOR类型作为集合的元素类型，也就是说，索引表，嵌套表和变长数组不能存放游标变量的值。
8. 游标和游标变量不能互相替换。如下例中，不能把适用于游标的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语句前后对应的游标属性值：

• 游标属性的一些实例

假设我们有一个名为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. 嵌套游标被用户显式地关闭
2. 父级游标被重新执行
3. 父级游标被关闭
4. 父级游标被取消
5. 在从嵌套游标的一个父级游标中取数据时发生错误。嵌套游标会被作为清理内容的一部分而被关闭

1、游标表达式的约束

1. 不能在隐式游标中使用游标表达式
2. 游标表达式只能出现在：
   1. 非子查询的SELECT语句中，并且这条语句不能是游标表达式本身的子查询
   2. 作为table函数的参数，出现在SELECT语句的FROM子句中
3. 游标表达式只能出现在查询说明的SELECT列表中
4. 游标表达式不能出现在视图声明中
5. 不能对游标表达式进行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; /

?