Oracle PL/SQL 游标详解

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

游标是 SQL 的工作区[1]：

2. 隐式游标

BEGIN
  UPDATE employees SET salary = salary * 1.1 WHERE dept_id = 10;
  
  DBMS_OUTPUT.PUT_LINE('Rows: ' || SQL%ROWCOUNT);
  
  IF SQL%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('No rows');
  END IF;
END;
/

2.1 属性

属性	说明
SQL%FOUND	有行
SQL%NOTFOUND	无行
SQL%ROWCOUNT	行数
SQL%ISOPEN	总是 FALSE

3. 显式游标

3.1 基本

DECLARE
  CURSOR c_emp IS SELECT * FROM employees WHERE dept_id = 10;
  v_emp c_emp%ROWTYPE;
BEGIN
  OPEN c_emp;
  LOOP
    FETCH c_emp INTO v_emp;
    EXIT WHEN c_emp%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_emp.name);
  END LOOP;
  CLOSE c_emp;
END;
/

3.2 FOR 循环

-- 简洁
BEGIN
  FOR rec IN (SELECT * FROM employees WHERE dept_id = 10) LOOP
    DBMS_OUTPUT.PUT_LINE(rec.name);
  END LOOP;
END;
/

-- 命名游标
DECLARE
  CURSOR c_emp IS SELECT * FROM employees;
BEGIN
  FOR rec IN c_emp LOOP
    DBMS_OUTPUT.PUT_LINE(rec.name);
  END LOOP;
END;
/

3.3 参数

DECLARE
  CURSOR c_emp(p_dept NUMBER) IS 
    SELECT * FROM employees WHERE dept_id = p_dept;
  v_emp c_emp%ROWTYPE;
BEGIN
  OPEN c_emp(10);
  LOOP
    FETCH c_emp INTO v_emp;
    EXIT WHEN c_emp%NOTFOUND;
    ...
  END LOOP;
  CLOSE c_emp;
END;
/

3.4 属性

属性	说明
c%FOUND	有行
c%NOTFOUND	无行
c%ROWCOUNT	行数
c%ISOPEN	已打开

4. REF CURSOR

4.1 弱类型

DECLARE
  v_cur SYS_REFCURSOR;
  v_emp employees%ROWTYPE;
BEGIN
  OPEN v_cur FOR SELECT * FROM employees WHERE dept_id = 10;
  LOOP
    FETCH v_cur INTO v_emp;
    EXIT WHEN v_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_emp.name);
  END LOOP;
  CLOSE v_cur;
END;
/

4.2 强类型

DECLARE
  TYPE emp_cur IS REF CURSOR RETURN employees%ROWTYPE;
  v_cur emp_cur;
  v_emp employees%ROWTYPE;
BEGIN
  OPEN v_cur FOR SELECT * FROM employees;
  ...
END;
/

4.3 返回

CREATE OR REPLACE FUNCTION get_emp_cur(p_dept NUMBER) RETURN SYS_REFCURSOR IS
  v_cur SYS_REFCURSOR;
BEGIN
  OPEN v_cur FOR SELECT * FROM employees WHERE dept_id = p_dept;
  RETURN v_cur;
END;
/

DECLARE
  v_cur SYS_REFCURSOR;
  v_emp employees%ROWTYPE;
BEGIN
  v_cur := get_emp_cur(10);
  LOOP
    FETCH v_cur INTO v_emp;
    EXIT WHEN v_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_emp.name);
  END LOOP;
  CLOSE v_cur;
END;
/

5. 动态 SQL

DECLARE
  v_cur SYS_REFCURSOR;
  v_sql VARCHAR2(4000);
  v_emp employees%ROWTYPE;
BEGIN
  v_sql := 'SELECT * FROM employees WHERE dept_id = :d';
  OPEN v_cur FOR v_sql USING 10;
  ...
  CLOSE v_cur;
END;
/

详细见：Oracle PL/SQL 动态 SQL 详解。

6. FOR UPDATE

6.1 锁

DECLARE
  CURSOR c_emp IS 
    SELECT * FROM employees WHERE dept_id = 10 
    FOR UPDATE;
  v_emp c_emp%ROWTYPE;
BEGIN
  OPEN c_emp;
  LOOP
    FETCH c_emp INTO v_emp;
    EXIT WHEN c_emp%NOTFOUND;
    UPDATE employees SET salary = salary * 1.1 
    WHERE CURRENT OF c_emp;
  END LOOP;
  CLOSE c_emp;
END;
/

6.2 列锁定

CURSOR c IS SELECT * FROM employees FOR UPDATE OF salary;

6.3 WAIT / SKIP / NOWAIT

FOR UPDATE NOWAIT;     -- 立即返回
FOR UPDATE WAIT 10;    -- 等 10 秒
FOR UPDATE SKIP LOCKED; -- 跳过锁定

7. BULK COLLECT

7.1 FETCH

DECLARE
  CURSOR c IS SELECT * FROM employees;
  TYPE emp_tab IS TABLE OF employees%ROWTYPE;
  v_emp emp_tab;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO v_emp LIMIT 1000;
    EXIT WHEN v_emp.COUNT = 0;
    
    FOR i IN 1..v_emp.COUNT LOOP
      ...
    END LOOP;
  END LOOP;
  CLOSE c;
END;
/

7.2 直接 SELECT

SELECT * BULK COLLECT INTO v_emp FROM employees;

详细见：Oracle BULK COLLECT 与 FORALL。

8. 隐式 FOR LOOP

-- 自动游标管理
BEGIN
  FOR rec IN (SELECT * FROM employees) LOOP
    DBMS_OUTPUT.PUT_LINE(rec.name);
  END LOOP;
END;
/

9. 游标变量

9.1 包

CREATE OR REPLACE PACKAGE emp_pkg AS
  CURSOR c_dept IS SELECT * FROM departments;
  TYPE dept_cur IS REF CURSOR RETURN departments%ROWTYPE;
END;
/

9.2 会话

DECLARE
  v_cur emp_pkg.dept_cur;
  v_dept departments%ROWTYPE;
BEGIN
  OPEN v_cur FOR SELECT * FROM departments;
  ...
END;
/

10. 性能

10.1 FOR LOOP

- 简洁
- 自动管理
- 性能好

10.2 BULK COLLECT

- 批量
- LIMIT
- 高效

10.3 显式

- 控制
- 复杂场景
- 谨慎关闭

详细见：Oracle PL/SQL 性能优化详解。

11. 应用场景

11.1 遍历

FOR rec IN (SELECT * FROM employees) LOOP
  -- 处理每行
END LOOP;

11.2 条件更新

DECLARE
  CURSOR c IS SELECT id, salary FROM employees FOR UPDATE;
BEGIN
  FOR rec IN c LOOP
    IF rec.salary > 10000 THEN
      UPDATE employees SET bonus = 1000 WHERE CURRENT OF c;
    ELSE
      UPDATE employees SET bonus = 500 WHERE CURRENT OF c;
    END IF;
  END LOOP;
END;
/

11.3 动态查询

CREATE PROCEDURE query_emp(p_filter VARCHAR2) IS
  v_cur SYS_REFCURSOR;
  v_sql VARCHAR2(4000);
BEGIN
  v_sql := 'SELECT * FROM employees WHERE ' || p_filter;
  OPEN v_cur FOR v_sql;
  -- 返回或处理
  CLOSE v_cur;
END;
/

11.4 批量

FETCH c BULK COLLECT INTO v_emp LIMIT 1000;
FORALL i IN 1..v_emp.COUNT
  INSERT INTO log VALUES (v_emp(i).id);

12. 常见坑与排错

12.1 ORA-01001

- 无效游标
- 已关闭

12.2 ORA-01002

- 反向 FETCH
- 顺序

12.3 ORA-06511

- 已打开
- 检查 ISOPEN

12.4 数据丢失

- %NOTFOUND 检查
- 处理后退出

13. 最佳实践

FOR LOOP：简洁
BULK COLLECT：批量
REF CURSOR：动态
关闭游标：资源
异常关闭：EXCEPTION
LIMIT：内存
FOR UPDATE：锁
CURRENT OF：更新
参数化：灵活
测试：验证

14. 参考资料

[1] Oracle Database PL/SQL Language Reference 19c, “Cursors” https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/explicit-cursor-declaration-and-definition.html