Oracle 11g Release 1 (11.1) 游标——在 PL/SQL 管理游标
http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/static.htm#i45288
本文内容
- SQL 游标（隐式）
- 显式游标
- 参考
PL/SQL 使用隐式游标和显式游标。PL/SQL 为所有 SQL 数据操纵语句隐式声明一个游标，包括只返回一行的查询。若你想准确控制查询，可以在任何 PL/SQL 块（block）、子程序（subprogram）或包（package）中的声明部分声明一个显式游标。
必须为返回多行的查询声明显式游标。
本文示例使用的表，位于 Oracle OE Schema 示例。另外，示例在 SQL*PLUS 下运行。示例最后 "/" 符号可让 SQL*PLUS 编译代码；而在 PL/SQL Developer 下则可有可无。
SQL 游标（隐式）
SQL 游标由 PL/SQL 自动管理。你不用写代码来处理这些游标。可以通过 SQL 游标的属性来追踪一个游标的执行信息。
SQL 游标属性
- %FOUND 属性：一个 DML 语句改变了行？
- %ISOPEN 属性：对 SQL 游标一直为 FALSE
- %NOTFOUND 属性：一个 DML 语句改变行失败了？
- %ROWCOUNT 属性：受影响的行有多少？
示例 1：演示 %FOUND 属性。
CREATE TABLE dept_temp AS SELECT * FROM departments;
DECLARE
dept_no NUMBER(4) := 270;
BEGIN
DELETE FROM dept_temp WHERE department_id = dept_no;
IF SQL%FOUND THEN
-- delete succeeded
INSERT INTO dept_temp VALUES (270, 'Personnel', 200, 1700);
END IF;
END;
/
示例 2：演示 %ROWCOUNT 属性。
CREATE TABLE employees_temp AS SELECT * FROM employees;
DECLARE
mgr_no NUMBER(6) := 122;
BEGIN
DELETE FROM employees_temp WHERE manager_id = mgr_no;
DBMS_OUTPUT.PUT_LINE('Number of employees deleted: ' ||
TO_CHAR(SQL%ROWCOUNT));
END;
/
输出：
Number of employees deleted: 8
显式游标
当需要准确控制查询时，可以在任何 PL/SQL 块（block）、子程序（subprogram）或包（package）的声明部分里声明一个显式游标。
可以使用三个语句来控制游标：OPEN、FETCH 和 CLOSE。
- 首先，用 OPEN 语句初始化游标，确定结果集；
- 之后，重复执行 FETCH 语句，直到所有数据行已经被取出，或使用 BULK COLLECT 子句一次性获取所有数据；
- 当最后一行数据已经被处理，可以用 CLOSE 语句释放游标。
该技术需要比其他技术，如 SQL 游标 FOR LOOP，更多的代码。它的优点是灵活。你可以：
- 通过声明和打开多个游标，来并行处理很多查询
- 在一个循环迭代中处理多个行，掠过行，把处理分割到多个循环
示例 3：演示 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;
/
示例 4：演示 BULK COLLECT 子句。
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
参考
