Oracle PL/SQL 游标
4.88/5 (11投票s)
本文将通过示例为您简要介绍游标。
引言
游标是一个临时内存区域（上下文区域），Oracle 在此执行 SQL 语句。Oracle 将每条 SELECT 语句与一个游标关联起来，以在此上下文区域中保存查询信息。
游标类型
游标有两种类型：隐式游标和显式游标。
隐式游标： Oracle 自动（隐式）控制或处理执行的 SQL 语句的信息。在此过程中，用户不知道隐式游标。Oracle 自动执行 OPEN、FETCH 和 CLOSE 操作。
显式游标： 显式游标用于返回多行数据的查询。这些游标在 PL/SQL 块的 DECLARE 部分显式声明。此声明允许在游标返回数据时顺序处理每一行数据。在显式游标中，DECLARE、OPEN、FETCH 和 CLOSE 操作由程序员完成。
使用显式游标的过程
- 声明：游标被初始化到临时内存区域。
- 打开：声明的游标被打开，并分配临时内存区域。
- 获取：已声明并打开的游标现在可以从数据中检索行。
- 关闭：
CLOSE语句禁用游标，并释放临时内存区域。
游标属性
这些属性返回有关 SQL 语句执行的有用信息。游标属性可以在过程语句中使用，但不能在 SQL 语句中使用。
| 游标属性 | 语法 | 描述 |
|---|---|---|
%NOTFOUND
|
cursor_name%NOTFOUND
|
%NOTFOUND 如果上次获取没有返回行，则返回 TRUE，否则如果上次获取返回行，则返回 FALSE。 |
%FOUND
|
cursor_name%FOUND
|
%FOUND 如果游标已打开，则返回 TRUE，并获取行直到最后一次获取。如果最后一次获取没有返回任何行，则返回 FALSE。 |
%ROWCOUNT
|
cursor_name%ROWCOUNT
|
%ROWCOUNT 跟踪从游标中获取的行数，直到游标关闭。 |
%ISOPEN
|
cursor_name%ISOPEN
|
%ISOPEN 如果其游标或游标变量已打开，则返回 TRUE，否则 %ISOPEN 返回 FALSE。 |
创建游标的通用语法如下：
CURSOR cursor_name IS select_statement;
- cursor_name : 游标名称
- select_statement : 返回多行的
select查询
注意：在编程语言中，标准化是最重要的部分。当您在声明部分声明游标时，始终以 c_cursor_name 开头，这样就能清楚地表明该名称指的是游标。
在我们开始使用显式游标之前，让我们了解一下...
记录类型
记录是复合数据结构，它是一组数据项，类似于数据库表的行，每个项都有自己的名称和数据类型。假设记录是一个变量，它保存一个表行或表中的某些列。如果您想了解更多关于 RECORD 的信息，可以参考 PL/SQL 用户指南和参考手册第 5 章。
PL/SQL 只支持三种类型的记录：基于表的记录、基于游标的记录和程序员定义的记录。
- 基于表的记录是指其整个结构类似于表的列的记录。
- 基于游标的记录是指其结构类似于预定义游标的项（元素）的记录。
- 在 PL/SQL 块的声明部分创建的游标和记录数据类型，具有相同的变量、相同的数据类型和相同的顺序，称为程序员定义的记录。
注意：要创建基于表和基于游标的记录，始终首选使用 %ROWTYPE 属性。%ROWTYPE 属性提供了一个记录类型，表示数据库表中的一行。该记录可以存储从表中选择或从游标或游标变量中获取的整个行数据。要了解更多关于 %ROWTYPE 的信息，请参考 PL/SQL 用户指南和参考手册第 2 章。
示例 1. 基于表的记录
DECLARE
vr_emp employees%ROWTYPE;
/* variable vr_emp is a record type existing database table employees.
record vr_emp has a similar structure to row of the employees table.*/
BEGIN
SELECT *
INTO vr_emp -- INTO clause always notifies only single row can be fetch
FROM employees
WHERE employee_id = 100;
/* to display each element of record,reference each attribute of record with dot notation*/
DBMS_OUTPUT.PUT_LINE('Employee Details : '||vr_emp.employee_id
||' '||vr_emp.first_name||' '||vr_emp.last_name||' '||vr_emp.salary);
END;
Employee Details : 100 Steven King 24000
示例 2. 基于游标的记录
DECLARE
CURSOR c_emp_detail IS
SELECT employee_id,first_name,last_name,salary
FROM employees;
rec_emp_detail c_emp_detail%ROWTYPE;
/* A cursor based record is based on elements of pre-Defined cursor.
A cursor based record can be only declared after its corresponding
cursor, else an error occurs.*/
BEGIN
OPEN c_emp_detail;
LOOP
FETCH c_emp_detail INTO rec_emp_detail;
EXIT WHEN c_emp_detail%NOTFOUND; -- cursor attribute to exit when no rows found to fetch.
DBMS_OUTPUT.PUT_LINE('Employees Details : '||' '||rec_emp_detail.employee_id
||' '||rec_emp_detail.first_name||' '||rec_emp_detail.last_name);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total number of rows : '||c_emp_detail%ROWCOUNT);
-- cursor attribute to find the total number of rows executed.
CLOSE c_emp_detail;
END;
Employees Details : 198 Donald OConnell
Employees Details : 199 Douglas Grant
Employees Details : 200 Jennifer Whalen
-----------------------------
Employees Details : 196 Alana Walsh
Employees Details : 197 Kevin Feeney
Total number of rows : 107
示例 3. 程序员定义的记录
DECLARE
CURSOR c_emp_detail IS
SELECT employee_id,first_name,last_name,salary
FROM employees;
/*declaring a record datatype, with same datatype of tables of database using %TYPE attribute,
with same order of corresponding cursor */
TYPE type_rectype IS RECORD
(emp_id employees.employee_id%TYPE,
f_name employees.first_name%TYPE,
l_name employees.last_name%TYPE,
s_salary employees.salary%TYPE
);
rec_type type_rectype; --variable of record datatype.
BEGIN
OPEN c_emp_detail;
LOOP
FETCH c_emp_detail INTO rec_type; -- Fetches the cursor into record variable.
EXIT WHEN c_emp_detail%NOTFOUND;
-- variable is part of each record datatype,so to reference it use dot notation in DBMS_OUTPUT.
DBMS_OUTPUT.PUT_LINE('Employees Details : '||' '||rec_type.emp_id
||' '||rec_type.f_name||' '||rec_type.l_name||' '||rec_type.s_salary);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total number of Employees : '||c_emp_detail%ROWCOUNT);
CLOSE c_emp_detail;
END;
Employees Details : 198 Donald OConnell 2600
Employees Details : 199 Douglas Grant 2600
Employees Details : 200 Jennifer Whalen 4400
-----------------------------
Employees Details : 196 Alana Walsh 3100
Employees Details : 197 Kevin Feeney 3000
Total number of Employees : 107
嵌套循环中的游标
在阅读示例 4 之前，请确保您对嵌套循环概念有更好的理解，因为嵌套循环中的游标概念与嵌套循环完全相同。要了解更多关于循环的信息，请参考 PL/SQL 用户指南和参考手册第 4 章。
示例 4. 嵌套循环中的游标
DECLARE
CURSOR c_dept IS
SELECT *
FROM departments
WHERE manager_id IS NOT NULL
ORDER BY department_name;
r_dept c_dept%ROWTYPE;
-- Declaration of department cursor and record variable.
CURSOR c_emp (c_dept_no departments.department_id%TYPE) IS
SELECT *
FROM employees
WHERE department_id = c_dept_no;
r_emp c_emp%ROWTYPE;
-- Declaration of employees cursor and record variable.
BEGIN
OPEN c_dept;
LOOP
FETCH c_dept INTO r_dept;
EXIT WHEN c_dept%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('----------------------------------');
DBMS_OUTPUT.PUT_LINE('Department Name : '||r_dept.department_name);
DBMS_OUTPUT.PUT_LINE('----------------------------------');
OPEN c_emp(r_dept.department_id);
LOOP
FETCH c_emp INTO r_emp;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employees Details : '||r_emp.employee_id
||' '||r_emp.first_name||' '||r_emp.last_name||' '||r_emp.salary);
END LOOP;
CLOSE c_emp;
END LOOP;
CLOSE c_dept;
END;
----------------------------------
Department Name : Accounting
----------------------------------
Employees Details : 205 Shelley Higgins 12000
Employees Details : 206 William Gietz 8300
-----------------------------
----------------------------------
Department Name : Shipping
----------------------------------
Employees Details : 198 Donald OConnell 2600
Employees Details : 199 Douglas Grant 2600
-----------------------------
c_emp 游标在 c_dept_no 中指定参数。每次调用 c_emp 游标时，它只返回由参数（c_dept_no）指定的 department_id 的员工列表。在示例 4 中，如果我们分离单个游标 c_emp，它就变成了一个参数化游标。
游标 For 循环
在游标 for 循环中，打开、获取和关闭的过程是隐式处理的。这使得程序员可以轻松地编写和维护块。
在游标 for 循环中，在每次迭代之前，PL/SQL 会获取到隐式声明的记录中。
循环内部的语句序列对满足查询的每一行执行一次。
当循环退出时，游标会自动关闭。
即使您使用 EXIT 或 GOTO 语句在所有行都被获取之前退出循环，游标也会关闭。
示例 5. 隐式游标 for 循环
BEGIN
FOR item IN(SELECT department_name,d.department_id,last_name,job_id,salary
FROM departments d JOIN employees e
ON e.department_id = d.department_id
WHERE JOB_ID = 'IT_PROG'
AND salary > 4800)
LOOP
DBMS_OUTPUT.PUT_LINE(item.last_name||' '||item.department_name
||' '||item.department_id||' '||item.job_id||' '||item.salary);
END LOOP;
END;
Hunold IT 60 IT_PROG 9000
Ernst IT 60 IT_PROG 6000
如果您需要在同一过程的不同部分引用相同的查询，您可以声明一个带有该特定查询的游标，并使用游标 for 循环获取结果。为此，我将使用示例 5 中的相同 select 查询。
示例 6. 显式游标 for 循环
DECLARE
CURSOR c_detail IS
SELECT department_name,d.department_id,last_name,job_id,salary
FROM departments d JOIN employees e
ON e.department_id = d.department_id
WHERE JOB_ID = 'IT_PROG'
AND salary > 4800;
BEGIN
FOR item IN c_detail
LOOP
DBMS_OUTPUT.PUT_LINE(item.last_name||' '||item.department_name
||' '||item.department_id||' '||item.job_id||' '||item.salary);
END LOOP;
END;
Hunold IT 60 IT_PROG 9000
Ernst IT 60 IT_PROG 6000
示例 7. 使用游标 FOR 循环的嵌套游标
如果您注意到，示例 4 和示例 7 的输出是相同的，区别在于示例 7。我们使用的是游标 for 循环，其中记录变量声明了 r_dept 和 r_emp，游标的打开、获取和关闭都是由每次循环迭代自动完成的，直到根据游标中的特定查询获取所有行。
变量 v_dept_id 被初始化为 c_dept 游标当前记录的 department_id。c_dept 游标通过此变量将 c_emp 游标关联起来。
因此，当处理 c_emp 游标时，它会检索 department_id 与 c_dept 游标当前记录匹配的员工。
c_dept 游标的每次迭代只会执行 DBMS_OUTPUT 一次。DBMS_OUTPUT 将为 c_emp 游标循环的每次迭代执行一次，为每个员工生成一行输出。
DECLARE
v_dept_id departments.department_id%TYPE;
CURSOR c_dept IS
SELECT *
FROM departments
WHERE manager_id IS NOT NULL
ORDER BY department_name;
CURSOR c_emp IS
SELECT *
FROM employees
WHERE department_id = v_dept_id;
BEGIN
FOR r_dept IN c_dept
LOOP
v_dept_id := r_dept.department_id;
DBMS_OUTPUT.PUT_LINE('----------------------------------');
DBMS_OUTPUT.PUT_LINE('Department Name : '||r_dept.department_name);
DBMS_OUTPUT.PUT_LINE('----------------------------------');
FOR r_emp IN c_emp
LOOP
DBMS_OUTPUT.PUT_LINE('Employee Name : '||r_emp.last_name);
END LOOP;
END LOOP;
END;
----------------------------------
Department Name : Accounting
----------------------------------
Employee Name : Higgins
Employee Name : Gietz
----------------------------------
--------------------
----------------------------------
Department Name : Shipping
----------------------------------
Employee Name : OConnell
Employee Name : Grant
--------------------
示例 8. 在嵌套游标 For 循环中使用参数
CREATE OR REPLACE PROCEDURE print_emp_dept(v_lo_id IN locations.location_id%TYPE)
IS
v_flag departments.department_id%TYPE;
CURSOR c_locations IS
SELECT *
FROM locations
WHERE location_id = v_lo_id;
CURSOR c_departments(v_loc_id locations.location_id%TYPE) IS
SELECT l.location_id,department_name,department_id
FROM locations l JOIN departments d
ON l.location_id = d.location_id
WHERE l.location_id = v_loc_id
AND d.manager_id IS NOT NULL;
CURSOR c_employees (v_dept_id departments.department_id%TYPE,
v_loc_id locations.location_id%TYPE) IS
SELECT d.department_id,employee_id,first_name,last_name,salary,job_id,city
FROM locations l JOIN departments d
ON l.location_id = d.location_id
JOIN employees e
ON d.department_id = e.department_id
WHERE d.department_id = v_dept_id
AND l.location_id = v_loc_id;
BEGIN
FOR r_location IN c_locations
LOOP
DBMS_OUTPUT.PUT_LINE
('Location ID : '||r_location.location_id||
' Belong''s to '||r_location.city||' city');
DBMS_OUTPUT.PUT_LINE('In city '||r_location.city||' '||'Departments are ');
FOR r_department IN c_departments(r_location.location_id)
LOOP
DBMS_OUTPUT.PUT_LINE('-----------------------------------------');
DBMS_OUTPUT.PUT_LINE('Department ID :
'||r_department.department_id||' '||'Location ID : '||
r_department.location_id||' '||
'Department Name : '||r_department.department_name);
DBMS_OUTPUT.PUT_LINE('-----------------------------------------');
v_flag := r_department.department_id;
EXIT WHEN v_flag IS NULL;
FOR r_employee IN c_employees
(r_department.department_id,r_location.location_id)
LOOP
DBMS_OUTPUT.PUT_LINE(r_employee.employee_id||'
'||r_employee.first_name||' '||
r_employee.last_name||' work''s in city '||r_employee.city);
END LOOP;
END LOOP;
END LOOP;
IF v_flag IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Not Available');
END IF;
END print_emp_dept;
调用过程
CALL print_emp_dept(3000);
BEGIN
print_emp_dept(1700);
END;
Location ID : 1400 Belong's to Southlake city
In city Southlake Departments are
-----------------------------------------
Department ID :60 Location ID : 1400 Department Name : IT
-----------------------------------------
103 Alexander Hunold work's in city Southlake
104 Bruce Ernst work's in city Southlake
105 David Austin work's in city Southlake
106 Valli Pataballa work's in city Southlake
107 Diana Lorentz work's in city Southlake
声明了祖父游标 c_locations。它是一个位置集合，在调用过程 print_emp_dept 时带有一个数据库中可用的 location_id 参数。
声明了父游标 c_departments。它接收 location_ID 参数以生成属于参数 location_ID 的部门列表。
子游标 c_employees 接收两个参数，department_id 和 location_id。通过这种方式，它生成在不同部门但相同 location_id 工作的员工列表。
祖父游标循环开始，只显示城市名称和 location_id 通过 DBMS_OUTPUT。父游标循环开始。它从祖父游标中获取 location_id 参数，并显示 department_id、location_id 和 department_name。
子游标循环开始。它从父游标中获取 department_id 参数，并从祖父游标中获取 location_id 参数。由于使用了祖父游标的参数，显示了 employee_id 和员工姓名以及他们工作的城市。
在此完整过程中，子游标循环首先结束，然后父游标循环结束，然后祖父游标循环结束。
示例 9. 用于 UPDATE 和 WHERE CURRENT CLAUSE
FOR UPDATE 子句仅在游标中用于数据库表的 update 或 delete 语句时使用。通常，当程序员执行 SELECT 语句时，不会锁定行。使用 FOR UPDATE 子句的主要目的是在游标内执行 update 或 delete 语句时锁定行，并限制其他用户对特定数据库表执行任何更新。一旦游标内的更新完成，放置在执行块内的 COMMIT 或 ROLLBACK 将释放锁。现在，带有特定列名（即 FOR UPDATE salary）的 FOR UPDATE 子句将仅根据 select 语句锁定 salary 列，即使存在 join 条件；否则，FOR UPDATE 子句将锁定该特定表的整个行。
NOWAIT 是一个可选关键字，如果行已经被其他程序员锁定，则控制立即返回给程序员，以便他在此期间可以做其他工作，然后再尝试。如果您省略该关键字，则等待时间可能会很长。
WHERE CURRENT OF 子句只有在游标中使用 FOR UPDATE 子句时才能使用。
WHERE CURRENT OF 子句只引用获取最新行的游标。
WHERE CURRENT OF CLAUSE 对于消除 update 子句中的 where 条件非常有用。
首先，我创建了一个演示表，因为我不想更改数据库实际表中的数据。
create table emp1 as select * from employees;
create table dept1 as select * from departments;
DECLARE
CURSOR c_sal_update IS
SELECT employee_id,first_name,last_name,job_id,department_name,e.department_id,salary
FROM dept1 d , emp1 e
WHERE e.department_id = 80
FOR UPDATE OF salary NOWAIT;
rec_sal c_sal_update%ROWTYPE;
BEGIN
OPEN c_sal_update; -- rows are locked.
LOOP
FETCH c_sal_update INTO rec_sal;
EXIT WHEN c_sal_update%NOTFOUND;
IF rec_sal.job_id = 'SA_MAN' THEN
UPDATE emp1
SET salary = rec_sal.salary + 1000
WHERE CURRENT OF c_sal_update;
END IF;
END LOOP;
COMMIT; -- rows are unlocked.
CLOSE c_sal_update;
END;
输出
SELECT employee_id,first_name,last_name,job_id,department_id,salary
FROM emp1
WHERE job_id = 'SA_MAN'
AND department_id = 80;
EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID DEPARTMENT_ID SALARY
----------- --------------- ----------------- ---------- ------------- --------
145 John Russell SA_MAN 80 15000
146 Karen Partners SA_MAN 80 14500
147 Alberto Errazuriz SA_MAN 80 13000
148 Gerald Cambrault SA_MAN 80 12000
149 Eleni Zlotkey SA_MAN 80 11500
示例 10. 带有显式游标的游标属性
DECLARE
CURSOR c_high_sal IS
SELECT *
FROM (SELECT employee_id,first_name,last_name,salary
FROM employees ORDER BY salary DESC)
WHERE ROWNUM < 11;
high_sal c_high_sal%ROWTYPE;
BEGIN
IF NOT c_high_sal%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('Cursor is Closed');
END IF;
OPEN c_high_sal;
IF c_high_sal%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('Cursor is open');
END IF;
LOOP
FETCH c_high_sal INTO high_sal;
IF c_high_sal%FOUND THEN
DBMS_OUTPUT.PUT_LINE(high_sal.employee_id||' '||high_sal.first_name
||' '||high_sal.last_name||' '||high_sal.salary);
ELSE
EXIT; -- the same as exit when c_high_sal%NOTFOUND;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(' Number of rows fetched : '||c_high_sal%ROWCOUNT);
CLOSE c_high_sal;
IF NOT c_high_sal%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('Cursor is closed ');
END IF;
END;
Cursor is Closed
Cursor is open
100 Steven King 24000
101 Neena Kochhar 17000
102 Lex De Haan 17000
145 John Russell 14000
146 Karen Partners 13500
201 Michael Hartstein 13000
205 Shelley Higgins 12000
108 Nancy Greenberg 12000
147 Alberto Errazuriz 12000
168 Lisa Ozer 11500
Number of rows fetched : 10
Cursor is closed
前面示例中声明的游标执行薪水最高的员工。在游标中使用子查询。声明 high_sal 为 %ROWTYPE。
游标尚未打开，%ISOPEN 属性用于在 IF-THEN 语句的帮助下查看游标是否打开，其中 %ISOPEN 变为 TRUE。
游标已打开，现在要使用 DBMS_OUTPUT 显示，%ISOPEN 与 IF-THEN 语句一起使用，其中 %ISOPEN 变为 TRUE。
现在循环开始，游标 c_high_sal 被获取到变量 high_sal 中，再次使用 %FOUND 属性与 IF-THEN-ELSE 语句一起显示薪水最高的员工列表，其中 %FOUND 属性在游标最后一次获取之前变为 true 并退出循环。
一旦退出循环，DBMS_OUTPUT 会获取循环的最后一行，并使用 %ROWCOUNT 属性显示该数字。游标已关闭。
游标关闭后，使用 IF-THEN 语句的帮助来显示游标已关闭。
示例 11. 带有隐式游标的游标属性
DECLARE
v_dept departments.department_id%TYPE := 270;
v_dept_name departments.department_name%TYPE;
BEGIN
DELETE FROM dept1 WHERE department_id = v_dept;
IF SQL%FOUND THEN
INSERT INTO dept1 VALUES(270,'Personnel',200,1700);
END IF;
DBMS_OUTPUT.PUT_LINE('Number of rows inserted : '||SQL%ROWCOUNT);
SELECT department_name INTO v_dept_name
FROM dept1
WHERE department_id = 270;
DBMS_OUTPUT.PUT_LINE('Department Name : '||v_dept_name);
END;
Number of rows inserted : 1
Department Name : Personnel
隐式游标可以通过 %FOUND 和 %ROWCOUNT 属性告诉您 update、insert 和 delete 影响了多少行。
我们已经在前面的示例中创建了一个名为 dept1 的表。在下面的示例中，变量 v_dept 和 v_dept_name 被表 departments 引用，因为我们正在处理表 dept1，除非结构或数据类型与另一个表（即 departments 和 dept1）不同，否则不会出现任何错误。
从 dept1 表中删除 department_id 为 270 的行。
使用 SQL 而不是游标名，因为它是一个没有游标名的隐式游标。
使用 %FOUND 属性与 IF-THEN 语句一起将新部门名称（即 Personnel）插入到 dept1 表中。插入后，要了解有多少行受到影响，请在 DBMS_OUTPUT 中使用 %ROWCOUNT。
在相同的执行块中，使用 v_dept_name，我们通过 select 语句获取新的部门名称。
我没有放置任何提交，因为我不想更新任何行，所以稍后我会回滚它。
要了解更多关于隐式游标及其属性的信息，请参考 PL/SQL 用户指南和参考手册第 6 章。
注意：在隐式游标和显式游标之间，最快的是隐式游标，因为游标的打开和关闭以及循环都是由 Oracle 自动处理的。游标 for 循环也是一种隐式游标，其中游标的打开和关闭是隐式完成的。但应根据需求使用游标，因为每种类型的游标都有其自身的优缺点。要了解更多信息，您可以参考 Steven Feuerstein 的书 PL-SQL Programming 第 5 版。
本文未涵盖 Refs 游标、带有批量操作的游标、带有包的游标和带有异常的游标等概念，这些超出了本文的范围。我可能会在下一篇文章中涵盖所有这些概念，感谢您阅读本文。