DB / Oracle / SQL 开发

Oracle 游标（Cursor）详解

2026.09.06

Oracle 游标（Cursor）详解

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

1. 概述

游标（Cursor） 是处理结果集的机制[1]：

类型说明
隐式游标SQL 自动创建
显式游标手动声明
REF CURSOR动态游标
游标变量引用游标

2. 隐式游标

2.1 SQL 隐式游标

BEGIN
  UPDATE employees SET salary = salary * 1.1 WHERE dept_id = 10;
  
  DBMS_OUTPUT.PUT_LINE('Rows affected: ' || SQL%ROWCOUNT);
END;

2.2 隐式游标属性

属性说明
SQL%FOUND影响行存在
SQL%NOTFOUND无影响行
SQL%ROWCOUNT影响行数
SQL%ISOPEN始终 FALSE
BEGIN
  UPDATE employees SET salary = 0 WHERE employee_id = 999;
  
  IF SQL%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('No rows updated');
  END IF;
END;

3. 显式游标

3.1 基本流程

DECLARE
  CURSOR c_emp IS
    SELECT employee_id, last_name, salary 
    FROM employees;
  
  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.last_name || ': ' || v_emp.salary);
  END LOOP;
  CLOSE c_emp;
END;

3.2 显式游标属性

属性说明
c%FOUND已获取行
c%NOTFOUND未获取行
c%ROWCOUNT已获取行数
c%ISOPEN游标是否打开

3.3 带参数游标

DECLARE
  CURSOR c_emp(p_dept_id NUMBER) IS
    SELECT * FROM employees WHERE dept_id = p_dept_id;
BEGIN
  OPEN c_emp(10);
  -- ...
  CLOSE c_emp;
END;

3.4 FOR 循环游标

-- 自动打开、获取、关闭
BEGIN
  FOR v_emp IN (SELECT * FROM employees WHERE dept_id = 10) LOOP
    DBMS_OUTPUT.PUT_LINE(v_emp.last_name);
  END LOOP;
END;

-- 带参数
DECLARE
  CURSOR c_emp IS SELECT * FROM employees WHERE dept_id = 10;
BEGIN
  FOR v_emp IN c_emp LOOP
    DBMS_OUTPUT.PUT_LINE(v_emp.last_name);
  END LOOP;
END;

4. REF CURSOR

4.1 强类型 REF CURSOR

DECLARE
  TYPE emp_cursor IS REF CURSOR RETURN employees%ROWTYPE;
  c_emp emp_cursor;
  v_emp employees%ROWTYPE;
BEGIN
  OPEN c_emp FOR SELECT * FROM employees WHERE dept_id = 10;
  LOOP
    FETCH c_emp INTO v_emp;
    EXIT WHEN c_emp%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_emp.last_name);
  END LOOP;
  CLOSE c_emp;
END;

4.2 弱类型 REF CURSOR

DECLARE
  TYPE generic_cursor IS REF CURSOR;
  c_gen generic_cursor;
  v_emp employees%ROWTYPE;
  v_dept departments%ROWTYPE;
BEGIN
  -- 动态打开不同查询
  OPEN c_gen FOR SELECT * FROM employees WHERE dept_id = 10;
  FETCH c_gen INTO v_emp;
  CLOSE c_gen;
  
  OPEN c_gen FOR SELECT * FROM departments;
  FETCH c_gen INTO v_dept;
  CLOSE c_gen;
END;

4.3 SYS_REFCURSOR

-- 系统预定义弱类型
DECLARE
  c_cur SYS_REFCURSOR;
  v_emp employees%ROWTYPE;
BEGIN
  OPEN c_cur FOR SELECT * FROM employees;
  LOOP
    FETCH c_cur INTO v_emp;
    EXIT WHEN c_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_emp.last_name);
  END LOOP;
  CLOSE c_cur;
END;

5. REF CURSOR 返回

5.1 存储过程返回

CREATE OR REPLACE PROCEDURE get_employees(
  p_dept_id IN NUMBER,
  p_cursor OUT SYS_REFCURSOR
) AS
BEGIN
  OPEN p_cursor FOR 
    SELECT * FROM employees WHERE dept_id = p_dept_id;
END;
/

-- 调用
DECLARE
  c_emp SYS_REFCURSOR;
  v_emp employees%ROWTYPE;
BEGIN
  get_employees(10, c_emp);
  LOOP
    FETCH c_emp INTO v_emp;
    EXIT WHEN c_emp%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_emp.last_name);
  END LOOP;
  CLOSE c_emp;
END;

5.2 函数返回

CREATE OR REPLACE FUNCTION get_emp_cursor(
  p_dept_id NUMBER
) RETURN SYS_REFCURSOR AS
  c_cur SYS_REFCURSOR;
BEGIN
  OPEN c_cur FOR 
    SELECT * FROM employees WHERE dept_id = p_dept_id;
  RETURN c_cur;
END;
/

6. BULK COLLECT

6.1 批量获取

DECLARE
  CURSOR c_emp IS SELECT * FROM employees;
  TYPE emp_table IS TABLE OF employees%ROWTYPE;
  v_emps emp_table;
BEGIN
  OPEN c_emp;
  LOOP
    FETCH c_emp BULK COLLECT INTO v_emps LIMIT 100;
    EXIT WHEN v_emps.COUNT = 0;
    
    FOR i IN 1..v_emps.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(v_emps(i).last_name);
    END LOOP;
  END LOOP;
  CLOSE c_emp;
END;

6.2 LIMIT 子句

-- 每次获取 100 行
FETCH c_emp BULK COLLECT INTO v_emps LIMIT 100;

6.3 优势

  • 减少 SQL/PL/SQL 上下文切换
  • 大幅提升性能
  • 适合批量处理

详细内容见：Oracle BULK COLLECT 与 FORALL。

7. FORALL

7.1 批量 DML

DECLARE
  TYPE id_table IS TABLE OF NUMBER;
  TYPE sal_table IS TABLE OF NUMBER;
  v_ids id_table;
  v_sals sal_table;
BEGIN
  SELECT employee_id, salary * 1.1 
  BULK COLLECT INTO v_ids, v_sals
  FROM employees WHERE dept_id = 10;
  
  FORALL i IN 1..v_ids.COUNT
    UPDATE employees SET salary = v_sals(i) WHERE employee_id = v_ids(i);
END;

7.2 FORALL 选项

-- SAVE EXCEPTIONS：跳过错误继续
FORALL i IN 1..v_ids.COUNT SAVE EXCEPTIONS
  UPDATE employees SET salary = v_sals(i) WHERE employee_id = v_ids(i);

-- INDICES OF：跳过 NULL
FORALL i IN INDICES OF v_ids
  UPDATE employees SET salary = v_sals(i) WHERE employee_id = v_ids(i);

-- VALUES OF
FORALL i IN VALUES OF v_indices
  UPDATE employees SET salary = v_sals(i) WHERE employee_id = v_ids(i);

8. 游标 FOR LOOP

8.1 简洁写法

BEGIN
  FOR v_emp IN (SELECT * FROM employees WHERE dept_id = 10) LOOP
    DBMS_OUTPUT.PUT_LINE(v_emp.last_name);
  END LOOP;
END;

8.2 优势

  • 自动声明变量
  • 自动打开/关闭游标
  • 自动 FETCH
  • 代码简洁

9. 动态 SQL 游标

9.1 动态 REF CURSOR

DECLARE
  c_cur SYS_REFCURSOR;
  v_sql VARCHAR2(1000);
  v_emp employees%ROWTYPE;
BEGIN
  v_sql := 'SELECT * FROM employees WHERE dept_id = :d';
  OPEN c_cur FOR v_sql USING 10;
  FETCH c_cur INTO v_emp;
  CLOSE c_cur;
END;

10. 常见坑与排错

10.1 ORA-01001: 无效游标

-- 游标未打开或已关闭
-- 检查 OPEN/FETCH/CLOSE 顺序

10.2 ORA-06504: 行类型不匹配

-- 修复：确保 %ROWTYPE 匹配
DECLARE
  CURSOR c_emp IS SELECT employee_id, last_name FROM employees;
  v_emp c_emp%ROWTYPE;  -- 正确
BEGIN
  ...
END;

10.3 游标泄漏

-- 未关闭游标
-- 修复：异常处理中关闭
BEGIN
  OPEN c_emp;
  ...
EXCEPTION
  WHEN OTHERS THEN
    IF c_emp%ISOPEN THEN
      CLOSE c_emp;
    END IF;
END;

10.4 性能差

修复：

-- 1. 使用 BULK COLLECT
-- 2. 使用 FORALL
-- 3. 加索引
-- 4. 限制 LIMIT

11. 最佳实践

  1. 优先 FOR LOOP：简洁高效
  2. 大批量用 BULK COLLECT：性能
  3. 批量 DML 用 FORALL：性能
  4. 异常处理关闭游标：避免泄漏
  5. 使用 %ROWTYPE：类型匹配
  6. REF CURSOR 用于动态：灵活
  7. LIMIT 100-1000：平衡内存
  8. 避免游标循环 DML：用 FORALL
  9. 检查 %NOTFOUND：避免重复处理
  10. 关闭游标：释放资源

12. 参考资料

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