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，更多的代码。但它的优点是灵活。你可以：
- 通过声明和打开多个游标，来并行处理很多查询
- 在一个循环迭代中处理多个行，掠过行，把处理分割到多个循环
声明一个游标
在语句中引用游标前，必须声明游标。给出游标的名称，并与指定查询关联。另外，也可以为游标声明一个返回类型，如 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;
查询可以在游标的作用域内引用 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 列表来单独获取。However, you can use a different INTO list on separate fetches with the same cursor. Each fetch retrieves another row and assigns values to the target variables, as shown inExample 6-12.
示例 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;
/
关闭一个游标
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;
/
引用没有开发的游标或游标变量，会产生预定义异常 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;
/
在第一次获取前，%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;
/
引用没有开发的游标或游标变量，会产生预定义异常 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 | 异常 | 异常 |
TAG:
