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语句前后对应的游标属性值：

  %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	异常	异常
注意： 如果在游标打开之前或关闭之后引用属性%FOUND、%NOTFOUND或%ROWCOUNT，都会引起INVALID_CURSOR异常。 第一个FETCH之后，如果结果集是空的，%FOUND会产生FALSE，%NOTFOUND会产生TRUE，%ROWCOUNT会产生0。

• 游标属性的一些实例

假设我们有一个名为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 ;
/