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）中的声明部分声明一个显式游标。
你必须为返回多行的查询声明显式游标。
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;
/
显式游标
当需要准确控制查询时，可以在任何 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;
/
TAG:
