第六章 PL/SQL与Oracle间交互(2)

四、使用游标FOR循环

在大多数需要使用显式游标的情况下，我们都可以用一个游标FOR循环来代替OPEN、FETCH和CLOSE语句。游标FOR循环隐式地声明了一个 %ROWTYPE类型的记录作为它的循环索引，打开游标，然后反复执行把结果集中的行放到索引中去，最后在所有行都被处理完成后关闭游标。

思考下面PL/SQL块例子，它能从一个实验中计算出结果，然后把结果保存在一张临时表中。FOR循环的索引c1_rec是被隐式声明的记录。它的每一个域都保存来自游标c1中取出的值。对独立的域的引用可以使用点标志。

DECLARE
  RESULT   temp.col1%TYPE;

  CURSOR c1 IS
    SELECT n1, n2, n3
      FROM data_table
     WHERE exper_num = 1;
BEGIN
  FOR c1_rec IN c1 LOOP
    /* calculate and store the results */
    RESULT  := c1_rec.n2 / (c1_rec.n1 + c1_rec.n3);
    INSERT INTO temp
         VALUES (RESULT, NULL, NULL);
  END LOOP;

  COMMIT;
END;

当进入游标FOR循环时后，游标的名称就不属于用OPEN语句打开的游标，也不属于封闭游标FOR循环。在每个循环之前，PL/SQL会把数据放到隐式声明的记录中去。记录的有效作用范围只在循环内，所以我们不能在循环的外部引用它。

循环内的语句序列会为每一个满足条件的结果行执行一次，当游标离开循环时，游标会被自动地关闭，这包括正常地使用EXIT或GOTO语句来结束循环，或是因异常抛出而退出循环的情况。

1、使用子查询代替显式游标

有时候我们并不需要声明游标，因为PL/SQL允许我们使用子查询来进行替代。下面的游标FOR循环先计算奖金值，然后把结果插入数据表中：

DECLARE
  bonus   REAL;
BEGIN
  FOR emp_rec IN (SELECT empno, sal, comm
                    FROM emp) LOOP
    bonus  := (emp_rec.sal * 0.05) + (emp_rec.comm * 0.25);
    INSERT INTO bonuses
         VALUES (emp_rec.empno, bonus);
  END LOOP;

  COMMIT;
END;

2、使用游标子查询

我们可以使用游标子查询(又称游标表达式)把一个查询结果集作为参数传递给函数。如下例：

SELECT * FROM TABLE(StockPivot(CURSOR(SELECT * FROM StockTable)));

游标子查询通常可以用在表函数中，这将在第八章详细讨论。

3、在游标FOR循环中为表达式定义别名

隐式声明的记录中每个域存放着最近取得的数据。记录的域名称和SELECT列表中的字段相对应。但是，如果SELECT中含有表达式时会发生什么呢？看一下下面的例子：

CURSOR c1 IS
  SELECT empno, sal + NVL (comm, 0) wages, job
    FROM ...

这样的情况下，我们就必须为表达式起一个别名。如下例，wages就是表达式sal+NVL(comm,0)的一个别名：

CURSOR c1 IS
  SELECT empno, sal + NVL (comm, 0) wages, job
    FROM ...

如果要引用对应的域，就得使用别名进行代替，如下例所示：

IF emp_rec.wages < 1000 THEN ...

4、为游标FOR循环传递参数

我们可以在游标FOR循环中把参数传递给游标。下例中，我们传递一个部门编号。然后计算出该部门应付给它的雇员的工资数额。并且，我们可以判断出有多少雇员的工资超过2000和/或他们的佣金大于他们的工资。

DECLARE
  CURSOR emp_cursor (dnum NUMBER) IS
    SELECT sal, comm
      FROM emp
     WHERE deptno = dnum;

  total_wages   NUMBER (11, 2) := 0;
  high_paid     NUMBER (4)     := 0;
  higher_comm   NUMBER (4)     := 0;
BEGIN
  /* The number of iterations will equal the number of rows
  returned by emp_cursor. */
  FOR emp_record IN emp_cursor (20) LOOP
    emp_record.comm  := NVL (emp_record.comm, 0);
    total_wages      := total_wages + emp_record.sal + emp_record.comm;
    IF emp_record.sal > 2000.00 THEN
      high_paid  := high_paid + 1;
    END IF;
    IF emp_record.comm > emp_record.sal THEN
      higher_comm  := higher_comm + 1;
    END IF;
  END LOOP;

  INSERT INTO temp
       VALUES (high_paid, higher_comm,
               'Total Wages: ' || TO_CHAR (total_wages));
  COMMIT;
END;

五、使用游标变量

跟游标一样，游标变量也是指向多行查询的结果集中的当前行。但是，游标与游标变量不同之处就和常量与变量不同之处类似。游标是静态的，而游标变量是动态的，因为游标变量并不与某个特定的查询相绑定。所以，游标变量可以打开任何类型兼容的查询，灵活性很大。

并且，我们还可以为游标变量赋新值，把它作为参数传递给本地和存储子程序。这就很容易地让我们把数据检索集中化处理。

游标变量可以在每个PL/SQL客户端使用。例如，我们可以在OCI或Pro*C这样的主环境中声明游标变量，然后把它作为输入主变量(绑定变量) 传给PL/SQL。并且，像Oracle Forms和Oracle Reports这样的含有PL/SQL引擎的开发工具，完全可以在客户端使用游标变量。Oracle服务器也有一个PL/SQL引擎。所以，我们可以在应用程序和服务器之间通过远程调用(RPC)来回传递游标变量。

1、什么是游标变量

游标变量同C或Pascal语言中的指针类似，它指向一块内存地址，而不是地址中的内容本身。所以，声明一个游标变量可以创建一个指针，而不是具体的内容。在PL/SQL中，指针是一个REF X类型，REF是REFERENCE的缩写，而X代表对象的类型。因此，游标变量的数据类型是REF CURSOR。

为了执行多行查询，Oracle会开启一个未命名的工作区来存放处理信息。我们可以用显式游标为工作区命名然后访问相关的信息；或者声明指向工作区的一个游标变量。无论在什么地方使用游标，它总是指向同一个查询工作区，而游标变量则可以指向不同的工作区。所以，游标和游标变量不能交互使用；也就是说，我们不能在该使用游标的地方使用游标变量，不能在该使用游标变量的地方使用游标。

2、为什么使用游标变量

我们主要是在PL/SQL存储子程序和各种客户端之间用游标变量来传递查询结果。PL/SQL和其他客户端程序都不拥有结果集，它们只是共享一个指向存放结果集工作区的指针而已。例如，一个OCI客户端，一个Oracle Forms应用程序和Oracle服务器可以引用同一个工作区。

只要有游标变量指向查询工作区，我们就可以引用它。因此，我们可以把游标变量的值自由地从一个作用域传递到另一个。例如，我们把主游标变量传递到嵌套在Pro*C程序中的PL/SQL块，游标变量指向的工作区就可以被访问。

如果客户端含有PL/SQL引擎，那么从客户端调用服务器端就不会有什么约束。假如我们在客户端声明游标变量，在服务器端打开并取得数据，然后把取得的结果返回给客户端。这些操作都是在服务器端完成，从而也减少了网络流量。

3、定义REF CURSOR类型

创建游标变量分为两个步骤。首先，我们得定义REF CURSOR类型，然后声明该类型的游标变量。我们可以在任何PL/SQL块、子程序或包内使用下面语法来定义REF CURSOR类型：

TYPE ref_type_name IS REF CURSOR [RETURN return_type];

ref_type_name是类型区分符，return_type必须是记录类型或是代表数据表中的行类型。在下面的例子中，我们把返回的数据类型指定为数据表dept的行类型：

DECLARE
  TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE;

REF CURSOR类型可以是强类型也可以是弱类型。如下例所示，强类型REF CURSOR需要指定返回类型，而弱类型没有这个要求：

DECLARE
  TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;   -- strong
  TYPE GenericCurTyp IS REF CURSOR;   -- weak

强类型REF CURSOR在编译时会检查类型兼容，这样能更好的避免发生错误。但是，弱类型REF CURSOR具有更大的灵活性，因为它允许我们把游标变量与任何相兼容的查询关联起来。

4、声明游标变量

定义了REF CURSOR后，就可以在PL/SQL块或子程序中声明该类型的游标变量了。在下面的程序中，我们声明了游标变量dept_cv：

DECLARE
TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE;
dept_cv DeptCurTyp;   -- declare cursor variable

注意：不能在包里声明游标变量。与打包变量不同，游标变量没有一个持久的状态。记住，声明游标变量就是创建了一个指针，它只是指向一个内容，本身并不存在，所以，游标变量是不能存放到数据库中的。变量的作用域规则也同样适用于游标变量。本地的PL/SQL游标变量在我们进入块或子程序时被初始化，在退出时被销毁。

在REF CURSOR定义的RETURN子句中，我们可以使用%ROWTYPE指定返回类型，如下例：

 

DECLARE
  TYPE tmpcurtyp IS REF CURSOR
    RETURN emp%ROWTYPE;

  tmp_cv   tmpcurtyp;   -- declare cursor variable

  TYPE empcurtyp IS REF CURSOR
    RETURN tmp_cv%ROWTYPE;

  emp_cv   empcurtyp;   -- declare cursor variable

同样，也可以使用%TYPE指定返回值类型，如下例：

DECLARE
  dept_rec   dept%ROWTYPE;   -- declare record variable

  TYPE deptcurtyp IS REF CURSOR
    RETURN dept_rec%TYPE;

  dept_cv    deptcurtyp;   -- declare cursor variable

最后一个例子，我们把RETURN子句的返回类型指定为用户自定义的RECORD类型：

DECLARE
  TYPE emprectyp IS RECORD (
    empno   NUMBER (4),
    ename   VARCHAR2(1O),
    sal     NUMBER (7, 2)
  );

  TYPE empcurtyp IS REF CURSOR
    RETURN emprectyp;

  emp_cv   empcurtyp;   -- declare cursor variable

• 作为参数的游标变量

我们可以把游标变量声明为函数和过程的形式参数。在下面例子中，我们定义REF CURSOR类型的EmpCurTyp，然后把该类型的游标变量作为过程的形式参数：

DECLARE
  TYPE empcurtyp IS REF CURSOR
    RETURN emp%ROWTYPE;

  PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp) IS   ...

注意：跟所有的指针一样，游标变量也能增加参数别名出现的可能性。

5、控制游标变量

我们可以使用三个语句来控制游标变量：OPEN-FOR、FETCH和CLOSE。首先，使用OPEN打开游标变量，然后从结果集中FETCH数据，当完成所有的处理后，就可以使用CLOSE语句关闭游标变量。

• 打开游标变量

OPEN-FOR语句把一个游标变量和一个多行查询关联起来并执行查询。语法如下：

OPEN {cursor_variable | :host_cursor_variable} FOR
{ select_statement
| dynamic_string [USING bind_argument[, bind_argument]...] };

host_cursor_variable是声明在PL/SQL主环境中的游标变量，dynamic_string代表多行查询的字符串表达式。

注意，这里只讨论使用静态SQL的情况。

与游标不同，游标变量是没有参数的。但这个并不影响灵活性，因为我们可以把整个查询传递给游标变量。被传递的查询语句可以使用主变量、PL/SQL变量、参数和函数。

下例中，我们首先打开游标变量emp_cv。与游标相似，我们也可以在游标变量上使用%FOUND、%NOTFOUND、%ISOPEN和%ROWCOUNT：

IF NOT emp_cv%ISOPEN THEN
  /* Open cursor variable. */
  OPEN emp_cv FOR SELECT * FROM emp;
END IF;

其它的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;