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;
/

输出：

141
142
143
144
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

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

下表显示在 OPEN、FETCH 和 CLOSE 语句执行之前和之后，每个游标属性的值。

时间点	%FOUND 值	%ISOPEN 值	%NOTFOUND 值	%ROWCOUNT 值
OPEN 前	异常	FALSE	异常	异常
OPEN 后	NULL	TRUE	NULL	0
第一次 FETCH 前	NULL	TRUE	NULL	0
第一次 FETCH 后	TRUE	TRUE	FALSE	1
每次连续 FETCH 前，除了最后一行	TRUE	TRUE	FALSE	1
每次连续 FETCH 后，除了最后一行	TRUE	TRUE	FALSE	依赖数据
最后 FETCH 前	TRUE	TRUE	FALSE	依赖数据
最后 FETCH 后	FALSE	TRUE	TRUE	依赖数据
CLOSE 前	FALSE	TRUE	TRUE	依赖数据
CLOSE 后	异常	FALSE	异常	异常