Oracle 11g Release 1 (11.1) 游标——显式游标

http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/static.htm#CIHCAHJA

本文内容

声明一个游标
打开一个游标
用游标获取一个（Fetch）数据
用游标获取全部（Fetch Bulk）数据
关闭一个游标
显式游标的属性

当需要准确控制查询时，可以在任何 PL/SQL 块（block）、子程序（subprogram）或包（package）的声明部分里声明一个显式游标。

可以使用三个语句来控制游标：OPEN、FETCH 和 CLOSE。

首先，用 OPEN 语句初始化游标，确定结果集；

之后，重复执行 FETCH 语句，知道所有数据行已经被检索，或使用 BULK COLLECT 子句一次性获取所有数据；

当最后一行数据已经被处理，可以用 CLOSE 语句释放游标。

该技术需要比其他技术，如 SQL 游标 FOR LOOP，更多的代码。但它的优点是灵活。你可以：

通过声明和打开多个游标，来并行处理很多查询

在一个循环迭代中处理多个行，掠过行，把处理分割到多个循环

本文示例使用的表，位于 Oracle OE Schema 示例。另外，示例在 SQL*PLUS 下运行。示例最后 "/" 符号可让 SQL*PLUS 编译代码；而在 PL/SQL Developer 下则可有可无。

声明一个游标

在语句中引用游标前，必须声明游标。给出游标的名称，并与指定查询关联。另外，也可以为游标声明一个返回类型，如 table_name%ROWTYPE。也可以在 WHERE 子句指定游标参数（不是本地变量）。游标参数可以有默认值。

示例 1：演示声明一个游标。

DECLARE

  my_emp_id NUMBER(6); -- variable for employee_id

  my_job_id VARCHAR2(10); -- variable for job_id

  my_sal    NUMBER(8, 2); -- variable for salary

  CURSOR c1 IS

    SELECT employee_id, job_id, salary FROM employees WHERE salary > 2000;

  my_dept departments%ROWTYPE; -- variable for departments row

  CURSOR c2 RETURN departments%ROWTYPE IS

    SELECT * FROM departments WHERE department_id = 110;

BEGIN

  NULL;

END;

游标不是 PL/SQL 变量，因此，不能分配一个值给游标，或是在表达式中使用游标。
游标和变量遵循相同的作用域规则。
游标可以带参数，可以出现在与其相关的查询中。游标的形参（formal parameters ）必须是 IN 参数（输入参数）；参数为查询提供值，但不能从查询中返回任何值。不能在游标参数上强加 NOT NULL 约束。

示例 2：演示游标参数。你可以初始化游标参数为默认值。可以传递不同数量的实参（actual parameters ）给游标。也可以添加新的行参，而无需改变现有的游标引用。

DECLARE

  CURSOR c1(low NUMBER DEFAULT 0, high NUMBER DEFAULT 99) IS

    SELECT *

      FROM departments

     WHERE department_id > low

       AND department_id < high;

BEGIN

  NULL;

END;

打开一个游标

打开游标执行查询，并确定结果集。

对于用 FOR UPDATE 子句声明的游标，OPEN 语句还会锁定那些行。

示例 3：演示打开一个游标。

DECLARE

  CURSOR c1 IS

    SELECT employee_id, last_name, job_id, salary

      FROM employees

     WHERE salary > 2000;

BEGIN

  OPEN c1;

END;

用游标获取一个（Fetch）数据

除非使用 BULK COLLECT 子句，否则，FETCH 语句每次在结果集中取出一行。每次取出当前行，游标在结果集中前进到下一行。你可以把每列存储到单独变量。也可以存储整行，通常变量用 %ROWTYPE 声明。

对于与游标关联的查询返回的每列，在 INTO 列表中必须是相对应的、类型兼容的变量。一般，使用带 LOOP 和 EXIT WHEN NOTFOUND 的 FETCH 语句。

示例 4：演示用游标获取一个（Fetch）数据。

DECLARE

  v_jobid    employees.job_id%TYPE; -- variable for job_id

  v_lastname employees.last_name%TYPE; -- variable for last_name

  CURSOR c1 IS

    SELECT last_name, job_id

      FROM employees

     WHERE REGEXP_LIKE(job_id, 'S[HT]_CLERK');

  v_employees employees%ROWTYPE; -- record variable for row

  CURSOR c2 is

    SELECT *

      FROM employees

     WHERE REGEXP_LIKE(job_id, '[ACADFIMKSA]_M[ANGR]');

BEGIN

  OPEN c1; -- open the cursor before fetching

  LOOP

    -- Fetches 2 columns into variables

    FETCH c1

      INTO v_lastname, v_jobid;

    EXIT WHEN c1%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(RPAD(v_lastname, 25, ' ') || v_jobid);

  END LOOP;

  CLOSE c1;

  DBMS_OUTPUT.PUT_LINE('-------------------------------------');

  OPEN c2;

  LOOP

    -- Fetches entire row into the v_employees record

    FETCH c2

      INTO v_employees;

    EXIT WHEN c2%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(RPAD(v_employees.last_name, 25, ' ') ||

                         v_employees.job_id);

  END LOOP;

  CLOSE c2;

END;

输出：

Nayer                    ST_CLERK

Mikkilineni              ST_CLERK

Landry                   ST_CLERK

Markle                   ST_CLERK

Bissot                   ST_CLERK

Atkinson                 ST_CLERK

Marlow                   ST_CLERK

Olson                    ST_CLERK

Mallin                   ST_CLERK

Rogers                   ST_CLERK

Gee                      ST_CLERK

Philtanker               ST_CLERK

Ladwig                   ST_CLERK

Stiles                   ST_CLERK

Seo                      ST_CLERK

Patel                    ST_CLERK

Rajs                     ST_CLERK

Davies                   ST_CLERK

Matos                    ST_CLERK

Vargas                   ST_CLERK

Taylor                   SH_CLERK

Fleaur                   SH_CLERK

Sullivan                 SH_CLERK

Geoni                    SH_CLERK

Sarchand                 SH_CLERK

Bull                     SH_CLERK

Dellinger                SH_CLERK

Cabrio                   SH_CLERK

Chung                    SH_CLERK

Dilly                    SH_CLERK

Gates                    SH_CLERK

Perkins                  SH_CLERK

Bell                     SH_CLERK

Everett                  SH_CLERK

McCain                   SH_CLERK

Jones                    SH_CLERK

Walsh                    SH_CLERK

Feeney                   SH_CLERK

OConnell                 SH_CLERK

Grant                    SH_CLERK

-------------------------------------

Greenberg                FI_MGR

Russell                  SA_MAN

Partners                 SA_MAN

Errazuriz                SA_MAN

Cambrault                SA_MAN

Zlotkey                  SA_MAN

Hartstein                MK_MAN

Higgins                  AC_MGR

可以在与游标关联的查询中，在游标作用域内引用 PL/SQL 变量。当游标打开时，计算查询中的所有变量。

示例 5：查询可以在游标的作用域内引用 PL/SQL 变量。每次取出的工资 salary 都被乘以 2，即使每次取出后，factor 变量自增 1。

DECLARE

  my_sal employees.salary%TYPE;

  my_job employees.job_id%TYPE;

  factor INTEGER := 2;

  CURSOR c1 IS

    SELECT factor * salary FROM employees WHERE job_id = my_job;

BEGIN

  OPEN c1; -- factor initially equals 2

  LOOP

    FETCH c1

      INTO my_sal;

    EXIT WHEN c1%NOTFOUND;

    factor := factor + 1; -- does not affect FETCH

  END LOOP;

  CLOSE c1;

END;

若想改变查询结果集或变量的值，必须关闭并用输入变量重新打开游标来设置新值。你可以用同一个游标，使用不同的 INTO 列表来单独获取。

示例 6：演示获取到不同的变量。

DECLARE

  CURSOR c1 IS

    SELECT last_name FROM employees ORDER BY last_name;

  name1 employees.last_name%TYPE;

  name2 employees.last_name%TYPE;

  name3 employees.last_name%TYPE;

BEGIN

  OPEN c1;

  FETCH c1

    INTO name1; -- this fetches first row

  FETCH c1

    INTO name2; -- this fetches second row

  FETCH c1

    INTO name3; -- this fetches third row

  CLOSE c1;

END;

若获取结果集中最后一行，不会定义目标变量的值。最终，FETCH 语句不会返回行。此时，不会产生异常。可以使用游标属性 %FOUND 或 %NOTFOUND 来检测。参考 Using Cursor Expressions。

用游标获取全部（Fetch Bulk）数据

BULK COLLECT 子句可以一次性从结果集获取所有行。参考 See Retrieving Query Results into Collections (BULK COLLECT Clause)。

示例 7：演示用游标获取全部（Fetch Bulk）数据。演示从一个游标获取全部数据到两个集合。

DECLARE

  TYPE IdsTab IS TABLE OF employees.employee_id%TYPE;

  TYPE NameTab IS TABLE OF employees.last_name%TYPE;

  ids   IdsTab;

  names NameTab;

  CURSOR c1 IS

    SELECT employee_id, last_name FROM employees WHERE job_id = 'ST_CLERK';

BEGIN

  OPEN c1;

  FETCH c1 BULK COLLECT

    INTO ids, names;

  CLOsE c1;

  -- Here is where you process the elements in the collections

  FOR i IN ids.FIRST .. ids.LAST LOOP

    IF ids(i) > 140 THEN

      DBMS_OUTPUT.PUT_LINE(ids(i));

    END IF;

  END LOOP;

  FOR i IN names.FIRST .. names.LAST LOOP

    IF names(i) LIKE '%Ma%' THEN

      DBMS_OUTPUT.PUT_LINE(names(i));

    END IF;

  END LOOP;

END;

输出：

Markle

Marlow

Mallin

Matos

关闭一个游标

CLOSE 语句禁用游标，并使结果集为未定义。可以重新打开已关闭的游标，用所有游标参数的值，和 WHERE 子句引用的变量，再次执行查询。任何在一个已关闭的游标上的操作都会产生预定义异常 INVALID_CURSOR。

显式游标的属性

每个显式游标和游标变量都有四个属性：%FOUND、%ISOPEN、%NOTFOUND 和 %ROWCOUNT。当追加到游标或游标变量名时，这些属性返回关于一个 SQL 语句执行很有用的信息。你可以在过程语句中使用游标属性，而不能在 SQL 语句。

显式游标属性返回执行一个多行查询的执行信息。当打开一个显式游标或一个游标变量时，确定满足与其关联查询的行，并形成结果集。行被从结果集中取出来。

%FOUND 属性：一个行已被取出？

打开一个游标或游标变量后，第一次取出前，%FOUND 返回 NULL。取出后，若最后最后取出已返回一行，则返回 %FOUND 返回 TRUE；若接下来的取出没有返回行，则返回 FALSE。

示例 8：演示 %FOUND。

DECLARE

  CURSOR c1 IS

    SELECT last_name, salary FROM employees WHERE ROWNUM < 11;

  my_ename  employees.last_name%TYPE;

  my_salary employees.salary%TYPE;

BEGIN

  OPEN c1;

  LOOP

    FETCH c1

      INTO my_ename, my_salary;

    IF c1%FOUND THEN

      -- fetch succeeded

      DBMS_OUTPUT.PUT_LINE('Name = ' || my_ename || ', salary = ' ||

                           my_salary);

    ELSE

      -- fetch failed, so exit loop

      EXIT;

    END IF;

  END LOOP;

END;

输出：

Name = King, salary = 24000

Name = Kochhar, salary = 17000

Name = De Haan, salary = 17000

Name = Hunold, salary = 9000

Name = Ernst, salary = 6000

Name = Austin, salary = 4800

Name = Pataballa, salary = 4800

Name = Lorentz, salary = 4200

Name = Greenberg, salary = 12000

Name = Faviet, salary = 9000

引用没有打开的游标或游标变量，会产生预定义异常 INVALID_CURSOR。

%ISOPEN 属性：游标是打开的？

若游标或游标变量是打开的，则 %ISOPEN 返回 TRUE；否则，返回 FALSE。

示例 9：演示 %ISOPEN。

DECLARE

  CURSOR c1 IS

    SELECT last_name, salary FROM employees WHERE ROWNUM < 11;

  the_name   employees.last_name%TYPE;

  the_salary employees.salary%TYPE;

BEGIN

  IF c1%ISOPEN = FALSE THEN

    -- cursor was not already open

    OPEN c1;

  END IF;

  FETCH c1

    INTO the_name, the_salary;

  CLOSE c1;

END;

%NOTFOUND 属性：没有获取行？

%NOTFOUND 与 %FOUND 相反。若接下来的取出返回一行，则 %NOTFOUND 为 FALSE；否则为 TRUE。

示例 10：演示 %NOTFOUND。当没有获取一行时，用 %NOTFOUND 退出循环。

DECLARE

  CURSOR c1 IS

    SELECT last_name, salary FROM employees WHERE ROWNUM < 11;

  my_ename  employees.last_name%TYPE;

  my_salary employees.salary%TYPE;

BEGIN

  OPEN c1;

  LOOP

    FETCH c1

      INTO my_ename, my_salary;

    IF c1%NOTFOUND THEN

      -- fetch failed, so exit loop

      -- Another form of this test is

      -- "EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;"

      EXIT;

    ELSE

      -- fetch succeeded

      DBMS_OUTPUT.PUT_LINE('Name = ' || my_ename || ', salary = ' ||

                           my_salary);

    END IF;

  END LOOP;

END;

输出：

Name = King, salary = 24000

Name = Kochhar, salary = 17000

Name = De Haan, salary = 17000

Name = Hunold, salary = 9000

Name = Ernst, salary = 6000

Name = Austin, salary = 4800

Name = Pataballa, salary = 4800

Name = Lorentz, salary = 4200

Name = Greenberg, salary = 12000

Name = Faviet, salary = 9000

在第一次获取前，%NOTFOUND 返回 NULL。若 FETCH 没有执行成功，则循环不能被退出，因为，只要 WHEN 条件为 TRUE，EXTI WHEN 语句就执行。安全起见，你可以使用下面语句：

EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;

引用没有开发的游标或游标变量，会产生预定义异常 INVALID_CURSOR。

%ROWCOUNT 属性：目前为止受影响的行有多少？

当打开游标或游标变量时，%ROWCOUNT 为 0。在第一次取出前，%ROWCOUNT 也为 0。此后，它为目前为止受影响行的数量。若接下来的取出返回一行，则数量是递增的。

示例 11：演示 %ROWCOUNT。

DECLARE

  CURSOR c1 IS

    SELECT last_name FROM employees WHERE ROWNUM < 11;

  name employees.last_name%TYPE;

BEGIN

  OPEN c1;

  LOOP

    FETCH c1

      INTO name;

    EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;

    DBMS_OUTPUT.PUT_LINE(c1%ROWCOUNT || '. ' || name);

    IF c1%ROWCOUNT = 5 THEN

      DBMS_OUTPUT.PUT_LINE('--- Fetched 5th record ---');

    END IF;

  END LOOP;

  CLOSE c1;

END;

输出：

1. King

2. Kochhar

3. De Haan

4. Hunold

5. Ernst

--- Fetched 5th record ---

6. Austin

7. Pataballa

8. Lorentz

9. Greenberg

10. Faviet