【Oracle】游标

个人主页：Guiat
归属专栏：Oracle

正文

1. 游标基础概述

游标是Oracle PL/SQL中用于处理查询结果集的重要机制，它允许我们逐行处理SQL查询返回的数据，为复杂的数据处理提供了强大的控制能力。

1.1 游标的概念与作用

游标本质上是指向查询结果集中某一行的指针，通过移动指针来逐行访问和处理数据。

1.2 游标的生命周期

游标的完整生命周期包含四个关键阶段：

1.3 游标的分类

Oracle提供了多种类型的游标来满足不同的需求：

2. 显式游标

显式游标是程序员显式声明、打开、读取和关闭的游标，提供了对查询结果集的完全控制。

2.1 显式游标的基本语法

2.1.1 声明游标

-- 基本游标声明
DECLARE
  CURSOR emp_cursor IS
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE department_id = 10;
    
  v_emp_id employees.employee_id%TYPE;
  v_first_name employees.first_name%TYPE;
  v_last_name employees.last_name%TYPE;
  v_salary employees.salary%TYPE;
BEGIN
  -- 游标操作
  NULL;
END;
/

2.1.2 带参数的游标

DECLARE
  -- 带参数的游标声明
  CURSOR emp_dept_cursor(p_dept_id NUMBER) IS
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE department_id = p_dept_id
    ORDER BY salary DESC;
    
  -- 使用%ROWTYPE简化变量声明
  emp_record emp_dept_cursor%ROWTYPE;
BEGIN
  -- 打开游标时传递参数
  OPEN emp_dept_cursor(20);
  
  LOOP
    FETCH emp_dept_cursor INTO emp_record;
    EXIT WHEN emp_dept_cursor%NOTFOUND;
    
    DBMS_OUTPUT.PUT_LINE('员工ID: ' || emp_record.employee_id || 
                        ', 姓名: ' || emp_record.first_name || ' ' || emp_record.last_name ||
                        ', 工资: ' || emp_record.salary);
  END LOOP;
  
  CLOSE emp_dept_cursor;
END;
/

2.2 游标的基本操作

2.2.1 完整的游标操作示例

DECLARE
  -- 声明游标
  CURSOR salary_cursor IS
    SELECT employee_id, first_name, last_name, salary, department_id
    FROM employees
    WHERE salary > 5000
    ORDER BY salary DESC;
    
  -- 声明记录类型变量
  emp_rec salary_cursor%ROWTYPE;
  v_counter NUMBER := 0;
  v_total_salary NUMBER := 0;
  
BEGIN
  DBMS_OUTPUT.PUT_LINE('=== 高薪员工报告 ===');
  
  -- 打开游标
  OPEN salary_cursor;
  
  -- 读取数据
  LOOP
    FETCH salary_cursor INTO emp_rec;
    
    -- 检查是否还有数据
    EXIT WHEN salary_cursor%NOTFOUND;
    
    v_counter := v_counter + 1;
    v_total_salary := v_total_salary + emp_rec.salary;
    
    DBMS_OUTPUT.PUT_LINE(v_counter || '. ' || 
                        emp_rec.first_name || ' ' || emp_rec.last_name ||
                        ' (ID: ' || emp_rec.employee_id || ')' ||
                        ' - 工资: $' || emp_rec.salary ||
                        ' - 部门: ' || emp_rec.department_id);
  END LOOP;
  
  -- 关闭游标
  CLOSE salary_cursor;
  
  -- 统计信息
  DBMS_OUTPUT.PUT_LINE('====================');
  DBMS_OUTPUT.PUT_LINE('总计: ' || v_counter || ' 名高薪员工');
  DBMS_OUTPUT.PUT_LINE('平均工资: $' || ROUND(v_total_salary / v_counter, 2));
  
EXCEPTION
  WHEN OTHERS THEN
    -- 确保游标关闭
    IF salary_cursor%ISOPEN THEN
      CLOSE salary_cursor;
    END IF;
    RAISE;
END;
/

2.3 游标属性

Oracle提供了多个游标属性来检查游标状态：

2.3.1 游标属性应用示例

DECLARE
  CURSOR dept_cursor IS
    SELECT department_id, department_name, manager_id
    FROM departments
    WHERE department_id BETWEEN 10 AND 50;
    
  dept_rec dept_cursor%ROWTYPE;
  
BEGIN
  -- 检查游标是否已打开
  IF NOT dept_cursor%ISOPEN THEN
    OPEN dept_cursor;
    DBMS_OUTPUT.PUT_LINE('游标已打开');
  END IF;
  
  LOOP
    FETCH dept_cursor INTO dept_rec;
    
    -- 使用%FOUND属性
    IF dept_cursor%FOUND THEN
      DBMS_OUTPUT.PUT_LINE('第 ' || dept_cursor%ROWCOUNT || ' 行: ' ||
                          dept_rec.department_name || ' (ID: ' || dept_rec.department_id || ')');
    END IF;
    
    -- 使用%NOTFOUND属性退出循环
    EXIT WHEN dept_cursor%NOTFOUND;
  END LOOP;
  
  DBMS_OUTPUT.PUT_LINE('总共处理了 ' || dept_cursor%ROWCOUNT || ' 个部门');
  
  -- 关闭游标
  CLOSE dept_cursor;
  
  -- 验证游标已关闭
  IF NOT dept_cursor%ISOPEN THEN
    DBMS_OUTPUT.PUT_LINE('游标已关闭');
  END IF;
  
END;
/

2.4 游标FOR循环

游标FOR循环是处理游标的简化语法，自动处理游标的打开、读取和关闭：

2.4.1 基本游标FOR循环

DECLARE
  CURSOR emp_cursor IS
    SELECT employee_id, first_name, last_name, hire_date, salary
    FROM employees
    WHERE department_id = 20
    ORDER BY hire_date;
    
BEGIN
  DBMS_OUTPUT.PUT_LINE('=== 部门20员工信息 ===');
  
  -- 游标FOR循环 - 自动管理游标生命周期
  FOR emp_rec IN emp_cursor LOOP
    DBMS_OUTPUT.PUT_LINE('员工: ' || emp_rec.first_name || ' ' || emp_rec.last_name ||
                        ', 入职日期: ' || TO_CHAR(emp_rec.hire_date, 'YYYY-MM-DD') ||
                        ', 工资: $' || emp_rec.salary);
  END LOOP;
  
END;
/

2.4.2 内联游标FOR循环

BEGIN
  DBMS_OUTPUT.PUT_LINE('=== 各部门平均工资统计 ===');
  
  -- 内联游标FOR循环 - 无需显式声明游标
  FOR dept_rec IN (
    SELECT d.department_name, 
           ROUND(AVG(e.salary), 2) as avg_salary,
           COUNT(e.employee_id) as emp_count
    FROM departments d
    JOIN employees e ON d.department_id = e.department_id
    GROUP BY d.department_name
    ORDER BY avg_salary DESC
  ) LOOP
    DBMS_OUTPUT.PUT_LINE('部门: ' || dept_rec.department_name ||
                        ', 平均工资: $' || dept_rec.avg_salary ||
                        ', 员工数: ' || dept_rec.emp_count);
  END LOOP;
  
END;
/

2.4.3 带参数的游标FOR循环

DECLARE
  CURSOR salary_range_cursor(p_min_sal NUMBER, p_max_sal NUMBER) IS
    SELECT employee_id, first_name, last_name, salary, department_id
    FROM employees
    WHERE salary BETWEEN p_min_sal AND p_max_sal
    ORDER BY salary;
    
BEGIN
  DBMS_OUTPUT.PUT_LINE('=== 工资范围 $5000-$10000 的员工 ===');
  
  FOR emp_rec IN salary_range_cursor(5000, 10000) LOOP
    DBMS_OUTPUT.PUT_LINE('ID: ' || emp_rec.employee_id ||
                        ', 姓名: ' || emp_rec.first_name || ' ' || emp_rec.last_name ||
                        ', 工资: $' || emp_rec.salary ||
                        ', 部门: ' || emp_rec.department_id);
  END LOOP;
  
END;
/

3. 隐式游标

隐式游标是Oracle自动为每个DML语句和单行SELECT语句创建的游标，由系统自动管理。

3.1 隐式游标的特点

3.2 隐式游标应用示例

3.2.1 DML操作中的隐式游标

DECLARE
  v_dept_id NUMBER := 90;
  v_location_id NUMBER := 1700;
  v_affected_rows NUMBER;
  
BEGIN
  -- 插入操作
  INSERT INTO departments (department_id, department_name, location_id)
  VALUES (v_dept_id, 'New Department', v_location_id);
  
  -- 检查插入是否成功
  IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('部门插入成功，影响行数: ' || SQL%ROWCOUNT);
  ELSE
    DBMS_OUTPUT.PUT_LINE('部门插入失败');
  END IF;
  
  -- 更新操作
  UPDATE employees
  SET salary = salary * 1.05
  WHERE department_id = 20 AND salary < 8000;
  
  v_affected_rows := SQL%ROWCOUNT;
  
  IF v_affected_rows > 0 THEN
    DBMS_OUTPUT.PUT_LINE('成功给 ' || v_affected_rows || ' 名员工加薪5%');
  ELSE
    DBMS_OUTPUT.PUT_LINE('没有符合条件的员工需要加薪');
  END IF;
  
  -- 删除操作
  DELETE FROM departments WHERE department_id = v_dept_id;
  
  IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('部门删除成功');
  END IF;
  
  -- 注意：隐式游标的%ISOPEN始终返回FALSE
  -- 因为它在语句执行后立即关闭
  DBMS_OUTPUT.PUT_LINE('隐式游标是否打开: ' || 
                      CASE WHEN SQL%ISOPEN THEN 'TRUE' ELSE 'FALSE' END);
  
END;
/

3.2.2 SELECT INTO语句中的隐式游标

DECLARE
  v_emp_name VARCHAR2(100);
  v_emp_salary NUMBER;
  v_emp_id NUMBER := 100;
  
BEGIN
  -- 单行SELECT INTO使用隐式游标
  BEGIN
    SELECT first_name || ' ' || last_name, salary
    INTO v_emp_name, v_emp_salary
    FROM employees
    WHERE employee_id = v_emp_id;
    
    -- 检查是否找到记录
    IF SQL%FOUND THEN
      DBMS_OUTPUT.PUT_LINE('找到员工: ' || v_emp_name || ', 工资: $' || v_emp_salary);
    END IF;
    
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('没有找到员工ID为 ' || v_emp_id || ' 的记录');
    WHEN TOO_MANY_ROWS THEN
      DBMS_OUTPUT.PUT_LINE('查询返回了多行记录');
  END;
  
END;
/

3.3 隐式游标与异常处理

隐式游标的使用需要特别注意异常处理：

CREATE OR REPLACE PROCEDURE process_employee_bonus(p_emp_id NUMBER, p_bonus_pct NUMBER)
AS
  v_current_salary NUMBER;
  v_new_bonus NUMBER;
  v_emp_name VARCHAR2(100);
  
BEGIN
  -- 获取员工信息
  BEGIN
    SELECT salary, first_name || ' ' || last_name
    INTO v_current_salary, v_emp_name
    FROM employees
    WHERE employee_id = p_emp_id;
    
    -- 计算奖金
    v_new_bonus := v_current_salary * p_bonus_pct / 100;
    
    DBMS_OUTPUT.PUT_LINE('员工 ' || v_emp_name || ' 当前工资: $' || v_current_salary);
    DBMS_OUTPUT.PUT_LINE('计算奖金 ' || p_bonus_pct || '%: $' || v_new_bonus);
    
    -- 更新奖金（假设有bonus列）
    -- UPDATE employees SET bonus = v_new_bonus WHERE employee_id = p_emp_id;
    
    IF SQL%ROWCOUNT > 0 THEN
      DBMS_OUTPUT.PUT_LINE('奖金更新成功');
    END IF;
    
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('错误: 员工ID ' || p_emp_id || ' 不存在');
    WHEN TOO_MANY_ROWS THEN
      DBMS_OUTPUT.PUT_LINE('错误: 查询返回多个员工记录');
  END;
  
END;
/

-- 调用存储过程
BEGIN
  process_employee_bonus(100, 10); -- 给员工100发放10%奖金
  process_employee_bonus(999, 5);  -- 不存在的员工ID
END;
/

4. REF游标

REF游标（游标变量）是一种特殊的游标类型，支持动态SQL和在子程序之间传递游标。

4.1 REF游标类型

4.2 强类型REF游标

4.2.1 声明和使用强类型REF游标

DECLARE
  -- 定义强类型REF游标
  TYPE emp_cursor_type IS REF CURSOR RETURN employees%ROWTYPE;
  
  -- 声明游标变量
  emp_cursor emp_cursor_type;
  emp_record employees%ROWTYPE;
  
  v_dept_id NUMBER := 20;
  
BEGIN
  -- 打开游标
  OPEN emp_cursor FOR
    SELECT * FROM employees 
    WHERE department_id = v_dept_id
    ORDER BY salary DESC;
  
  DBMS_OUTPUT.PUT_LINE('=== 部门 ' || v_dept_id || ' 员工列表 ===');
  
  LOOP
    FETCH emp_cursor INTO emp_record;
    EXIT WHEN emp_cursor%NOTFOUND;
    
    DBMS_OUTPUT.PUT_LINE('ID: ' || emp_record.employee_id ||
                        ', 姓名: ' || emp_record.first_name || ' ' || emp_record.last_name ||
                        ', 工资: $' || emp_record.salary);
  END LOOP;
  
  CLOSE emp_cursor;
  
  DBMS_OUTPUT.PUT_LINE('总共处理了 ' || emp_cursor%ROWCOUNT || ' 名员工');
  
END;
/

4.2.2 自定义记录类型的REF游标

DECLARE
  -- 定义自定义记录类型
  TYPE emp_summary_rec IS RECORD (
    emp_id NUMBER,
    full_name VARCHAR2(100),
    department VARCHAR2(50),
    salary NUMBER,
    hire_year NUMBER
  );
  
  -- 定义基于记录类型的REF游标
  TYPE emp_summary_cursor_type IS REF CURSOR RETURN emp_summary_rec;
  
  emp_cursor emp_summary_cursor_type;
  emp_rec emp_summary_rec;
  
BEGIN
  -- 打开游标
  OPEN emp_cursor FOR
    SELECT e.employee_id,
           e.first_name || ' ' || e.last_name,
           d.department_name,
           e.salary,
           EXTRACT(YEAR FROM e.hire_date)
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
    WHERE e.salary > 8000
    ORDER BY e.salary DESC;
  
  DBMS_OUTPUT.PUT_LINE('=== 高薪员工摘要报告 ===');
  
  LOOP
    FETCH emp_cursor INTO emp_rec;
    EXIT WHEN emp_cursor%NOTFOUND;
    
    DBMS_OUTPUT.PUT_LINE('员工: ' || emp_rec.full_name ||
                        ', 部门: ' || emp_rec.department ||
                        ', 工资: $' || emp_rec.salary ||
                        ', 入职年份: ' || emp_rec.hire_year);
  END LOOP;
  
  CLOSE emp_cursor;
  
END;
/

4.3 弱类型REF游标

4.3.1 使用SYS_REFCURSOR

DECLARE
  -- 声明弱类型REF游标
  my_cursor SYS_REFCURSOR;
  
  v_sql VARCHAR2(1000);
  v_table_name VARCHAR2(30) := 'employees';
  v_condition VARCHAR2(100) := 'department_id = 10';
  
  -- 动态处理不同的查询结果
  v_employee_id NUMBER;
  v_first_name VARCHAR2(50);
  v_last_name VARCHAR2(50);
  v_department_id NUMBER;
  v_salary NUMBER;
  
BEGIN
  -- 构建动态SQL
  v_sql := 'SELECT employee_id, first_name, last_name, department_id, salary 
            FROM ' || v_table_name || ' 
            WHERE ' || v_condition || '
            ORDER BY salary DESC';
  
  DBMS_OUTPUT.PUT_LINE('执行SQL: ' || v_sql);
  DBMS_OUTPUT.PUT_LINE('======================');
  
  -- 打开游标
  OPEN my_cursor FOR v_sql;
  
  LOOP
    FETCH my_cursor INTO v_employee_id, v_first_name, v_last_name, v_department_id, v_salary;
    EXIT WHEN my_cursor%NOTFOUND;
    
    DBMS_OUTPUT.PUT_LINE('ID: ' || v_employee_id ||
                        ', 姓名: ' || v_first_name || ' ' || v_last_name ||
                        ', 部门: ' || v_department_id ||
                        ', 工资: $' || v_salary);
  END LOOP;
  
  CLOSE my_cursor;
  
END;
/

4.3.2 动态查询处理

CREATE OR REPLACE PROCEDURE dynamic_query_processor(
  p_table_name IN VARCHAR2,
  p_where_clause IN VARCHAR2 DEFAULT NULL,
  p_order_clause IN VARCHAR2 DEFAULT NULL
)
AS
  query_cursor SYS_REFCURSOR;
  v_sql VARCHAR2(4000);
  
  -- 使用DBMS_SQL.DESCRIBE_COLUMNS来处理不同的列类型
  v_desc_tab DBMS_SQL.DESC_TAB;
  v_col_cnt NUMBER;
  v_cursor_id NUMBER;
  
BEGIN
  -- 构建基本SQL
  v_sql := 'SELECT * FROM ' || p_table_name;
  
  IF p_where_clause IS NOT NULL THEN
    v_sql := v_sql || ' WHERE ' || p_where_clause;
  END IF;
  
  IF p_order_clause IS NOT NULL THEN
    v_sql := v_sql || ' ORDER BY ' || p_order_clause;
  END IF;
  
  DBMS_OUTPUT.PUT_LINE('执行动态查询: ' || v_sql);
  DBMS_OUTPUT.PUT_LINE('===========================================');
  
  -- 打开游标
  OPEN query_cursor FOR v_sql;
  
  -- 这里简化处理，实际应用中可能需要更复杂的元数据处理
  DBMS_OUTPUT.PUT_LINE('查询执行成功，结果集已准备就绪');
  
  CLOSE query_cursor;
  
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('查询执行出错: ' || SQLERRM);
    IF query_cursor%ISOPEN THEN
      CLOSE query_cursor;
    END IF;
END;
/

-- 调用动态查询处理器
BEGIN
  dynamic_query_processor('employees', 'salary > 5000', 'salary DESC');
  dynamic_query_processor('departments', NULL, 'department_name');
END;
/

4.4 REF游标作为参数传递

4.4.1 函数返回REF游标

CREATE OR REPLACE FUNCTION get_employees_by_dept(p_dept_id NUMBER)
RETURN SYS_REFCURSOR
AS
  emp_cursor SYS_REFCURSOR;
BEGIN
  OPEN emp_cursor FOR
    SELECT employee_id, first_name, last_name, email, salary, hire_date
    FROM employees
    WHERE department_id = p_dept_id
    ORDER BY hire_date;
    
  RETURN emp_cursor;
END;
/

-- 使用返回的REF游标
DECLARE
  emp_cursor SYS_REFCURSOR;
  
  v_emp_id NUMBER;
  v_first_name VARCHAR2(50);
  v_last_name VARCHAR2(50);
  v_email VARCHAR2(100);
  v_salary NUMBER;
  v_hire_date DATE;
  
BEGIN
  -- 获取游标
  emp_cursor := get_employees_by_dept(20);
  
  DBMS_OUTPUT.PUT_LINE('=== 部门20员工列表 ===');
  
  LOOP
    FETCH emp_cursor INTO v_emp_id, v_first_name, v_last_name, v_email, v_salary, v_hire_date;
    EXIT WHEN emp_cursor%NOTFOUND;
    
    DBMS_OUTPUT.PUT_LINE('ID: ' || v_emp_id ||
                        ', 姓名: ' || v_first_name || ' ' || v_last_name ||
                        ', 邮箱: ' || v_email ||
                        ', 工资: $' || v_salary ||
                        ', 入职: ' || TO_CHAR(v_hire_date, 'YYYY-MM-DD'));
  END LOOP;
  
  CLOSE emp_cursor;
  
END;
/

4.4.2 存储过程的OUT参数REF游标

CREATE OR REPLACE PROCEDURE get_salary_statistics(
  p_dept_id IN NUMBER,
  p_emp_cursor OUT SYS_REFCURSOR,
  p_total_employees OUT NUMBER,
  p_avg_salary OUT NUMBER,
  p_min_salary OUT NUMBER,
  p_max_salary OUT NUMBER
)
AS
BEGIN
  -- 获取统计信息
  SELECT COUNT(*), 
         ROUND(AVG(salary), 2),
         MIN(salary),
         MAX(salary)
  INTO p_total_employees, p_avg_salary, p_min_salary, p_max_salary
  FROM employees
  WHERE department_id = p_dept_id;
  
  -- 打开游标返回详细信息
  OPEN p_emp_cursor FOR
    SELECT employee_id, 
           first_name || ' ' || last_name as full_name,
           salary,
           ROUND((salary - p_avg_salary), 2) as salary_diff,
           CASE 
             WHEN salary > p_avg_salary THEN '高于平均'
             WHEN salary < p_avg_salary THEN '低于平均'
             ELSE '等于平均'
           END as salary_level
    FROM employees
    WHERE department_id = p_dept_id
    ORDER BY salary DESC;
    
END;
/

-- 使用OUT参数REF游标
DECLARE
  emp_cursor SYS_REFCURSOR;
  v_total_count NUMBER;
  v_avg_sal NUMBER;
  v_min_sal NUMBER;
  v_max_sal NUMBER;
  
  v_emp_id NUMBER;
  v_full_name VARCHAR2(100);
  v_salary NUMBER;
  v_salary_diff NUMBER;
  v_salary_level VARCHAR2(20);
  
BEGIN
  -- 调用存储过程
  get_salary_statistics(20, emp_cursor, v_total_count, v_avg_sal, v_min_sal, v_max_sal);
  
  -- 显示统计信息
  DBMS_OUTPUT.PUT_LINE('=== 部门20工资统计 ===');
  DBMS_OUTPUT.PUT_LINE('员工总数: ' || v_total_count);
  DBMS_OUTPUT.PUT_LINE('平均工资: $' || v_avg_sal);
  DBMS_OUTPUT.PUT_LINE('最低工资: $' || v_min_sal);
  DBMS_OUTPUT.PUT_LINE('最高工资: $' || v_max_sal);
  DBMS_OUTPUT.PUT_LINE('========================');
  
  -- 显示详细信息
  LOOP
    FETCH emp_cursor INTO v_emp_id, v_full_name, v_salary, v_salary_diff, v_salary_level;
    EXIT WHEN emp_cursor%NOTFOUND;
    
    DBMS_OUTPUT.PUT_LINE('员工: ' || v_full_name ||
                        ', 工资: $' || v_salary ||
                        ', 与平均差: $' || v_salary_diff ||
                        ' (' || v_salary_level || ')');
  END LOOP;
  
  CLOSE emp_cursor;
  
END;
/

5. 游标高级特性

5.1 可更新游标

可更新游标允许通过游标直接更新或删除当前行。

5.1.1 FOR UPDATE子句

DECLARE
  CURSOR emp_cursor IS
    SELECT employee_id, first_name, last_name, salary, department_id
    FROM employees
    WHERE department_id = 20
    FOR UPDATE; -- 锁定查询的行
    
  emp_rec emp_cursor%ROWTYPE;
  v_new_salary NUMBER;
  
BEGIN
  DBMS_OUTPUT.PUT_LINE('=== 部门20员工工资调整 ===');
  
  OPEN emp_cursor;
  
  LOOP
    FETCH emp_cursor INTO emp_rec;
    EXIT WHEN emp_cursor%NOTFOUND;
    
    -- 根据当前工资计算新工资
    IF emp_rec.salary < 5000 THEN
      v_new_salary := emp_rec.salary * 1.15; -- 加薪15%
    ELSIF emp_rec.salary < 8000 THEN
      v_new_salary := emp_rec.salary * 1.10; -- 加薪10%
    ELSE
      v_new_salary := emp_rec.salary * 1.05; -- 加薪5%
    END IF;
    
    -- 使用WHERE CURRENT OF更新当前行
    UPDATE employees 
    SET salary = v_new_salary
    WHERE CURRENT OF emp_cursor;
    
    DBMS_OUTPUT.PUT_LINE('员工 ' || emp_rec.first_name || ' ' || emp_rec.last_name ||
                        ': $' || emp_rec.salary || ' -> $' || v_new_salary ||
                        ' (涨幅: ' || ROUND(((v_new_salary - emp_rec.salary) / emp_rec.salary * 100), 1) || '%)');
  END LOOP;
  
  CLOSE emp_cursor;
  
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('所有工资调整已提交');
  
END;
/

5.1.2 选择性锁定

DECLARE
  CURSOR emp_cursor IS
    SELECT employee_id, first_name, last_name, salary, commission_pct
    FROM employees
    WHERE department_id IN (80, 90)
    FOR UPDATE OF salary NOWAIT; -- 只锁定salary列，不等待
    
  emp_rec emp_cursor%ROWTYPE;
  v_bonus NUMBER;
  
BEGIN
  DBMS_OUTPUT.PUT_LINE('=== 销售和管理部门绩效奖金计算 ===');
  
  OPEN emp_cursor;
  
  LOOP
    FETCH emp_cursor INTO emp_rec;
    EXIT WHEN emp_cursor%NOTFOUND;
    
    -- 计算绩效奖金
    IF emp_rec.commission_pct IS NOT NULL THEN
      v_bonus := emp_rec.salary * emp_rec.commission_pct; -- 有提成的员工
    ELSE
      v_bonus := emp_rec.salary * 0.05; -- 无提成员工给5%奖金
    END IF;
    
    DBMS_OUTPUT.PUT_LINE('员工 ' || emp_rec.first_name || ' ' || emp_rec.last_name ||
                        ', 基本工资: $' || emp_rec.salary ||
                        ', 绩效奖金: $' || ROUND(v_bonus, 2));
    
    -- 可以在这里更新奖金字段
    -- UPDATE employees SET bonus = v_bonus WHERE CURRENT OF emp_cursor;
    
  END LOOP;
  
  CLOSE emp_cursor;
  
EXCEPTION
  WHEN OTHERS THEN
    IF emp_cursor%ISOPEN THEN
      CLOSE emp_cursor;
    END IF;
    IF SQLCODE = -54 THEN -- Resource busy
      DBMS_OUTPUT.PUT_LINE('错误: 记录正被其他会话使用');
    ELSE
      DBMS_OUTPUT.PUT_LINE('错误: ' || SQLERRM);
    END IF;
END;
/

5.2 批量操作(BULK COLLECT)

BULK COLLECT允许一次获取多行数据，提高性能。

5.2.1 基本BULK COLLECT

DECLARE
  TYPE emp_id_array IS TABLE OF employees.employee_id%TYPE;
  TYPE emp_name_array IS TABLE OF VARCHAR2(100);
  TYPE emp_salary_array IS TABLE OF employees.salary%TYPE;
  
  v_emp_ids emp_id_array;
  v_emp_names emp_name_array;
  v_emp_salaries emp_salary_array;
  
  v_total_salary NUMBER := 0;
  
BEGIN
  -- 使用BULK COLLECT一次获取所有数据
  SELECT employee_id, 
         first_name || ' ' || last_name,
         salary
  BULK COLLECT INTO v_emp_ids, v_emp_names, v_emp_salaries
  FROM employees
  WHERE department_id = 50
  ORDER BY salary DESC;
  
  DBMS_OUTPUT.PUT_LINE('=== 部门50员工信息（共' || v_emp_ids.COUNT || '人）===');
  
  -- 处理批量数据
  FOR i IN 1..v_emp_ids.COUNT LOOP
    v_total_salary := v_total_salary + v_emp_salaries(i);
    
    DBMS_OUTPUT.PUT_LINE(i || '. ID: ' || v_emp_ids(i) ||
                        ', 姓名: ' || v_emp_names(i) ||
                        ', 工资: $' || v_emp_salaries(i));
  END LOOP;
  
  DBMS_OUTPUT.PUT_LINE('==============================');
  DBMS_OUTPUT.PUT_LINE('工资总额: $' || v_total_salary);
  DBMS_OUTPUT.PUT_LINE('平均工资: $' || ROUND(v_total_salary / v_emp_ids.COUNT, 2));
  
END;
/

5.2.2 带LIMIT的BULK COLLECT

DECLARE
  CURSOR large_table_cursor IS
    SELECT employee_id, first_name, last_name, salary
    FROM employees;
    
  TYPE emp_record_array IS TABLE OF large_table_cursor%ROWTYPE;
  v_emp_batch emp_record_array;
  
  v_batch_size CONSTANT PLS_INTEGER := 100; -- 每批处理100行
  v_total_processed NUMBER := 0;
  
BEGIN
  DBMS_OUTPUT.PUT_LINE('=== 批量处理员工数据 ===');
  
  OPEN large_table_cursor;
  
  LOOP
    -- 使用LIMIT控制每次获取的行数
    FETCH large_table_cursor BULK COLLECT INTO v_emp_batch LIMIT v_batch_size;
    
    -- 处理当前批次的数据
    FOR i IN 1..v_emp_batch.COUNT LOOP
      v_total_processed := v_total_processed + 1;
      
      -- 这里可以进行复杂的业务处理
      -- 例如：数据转换、验证、插入到其他表等
      
      IF MOD(v_total_processed, 50) = 0 THEN
        DBMS_OUTPUT.PUT_LINE('已处理 ' || v_total_processed || ' 条记录...');
      END IF;
    END LOOP;
    
    -- 可以在这里提交事务，避免长事务
    -- COMMIT;
    
    -- 如果这批数据少于批次大小，说明已到末尾
    EXIT WHEN v_emp_batch.COUNT < v_batch_size;
    
  END LOOP;
  
  CLOSE large_table_cursor;
  
  DBMS_OUTPUT.PUT_LINE('批量处理完成，总共处理 ' || v_total_processed || ' 条记录');
  
END;
/

5.2.3 FORALL批量DML操作

DECLARE
  TYPE emp_id_array IS TABLE OF employees.employee_id%TYPE;
  TYPE salary_array IS TABLE OF employees.salary%TYPE;
  
  v_emp_ids emp_id_array;
  v_old_salaries salary_array;
  v_new_salaries salary_array;
  
BEGIN
  -- 获取需要调薪的员工信息
  SELECT employee_id, salary
  BULK COLLECT INTO v_emp_ids, v_old_salaries
  FROM employees
  WHERE department_id = 30
  AND salary < 6000;
  
  -- 计算新工资
  v_new_salaries := salary_array();
  v_new_salaries.EXTEND(v_emp_ids.COUNT);
  
  FOR i IN 1..v_emp_ids.COUNT LOOP
    v_new_salaries(i) := v_old_salaries(i) * 1.12; -- 加薪12%
  END LOOP;
  
  DBMS_OUTPUT.PUT_LINE('=== 批量工资调整 ===');
  DBMS_OUTPUT.PUT_LINE('准备调整 ' || v_emp_ids.COUNT || ' 名员工的工资');
  
  -- 使用FORALL进行批量更新
  FORALL i IN 1..v_emp_ids.COUNT
    UPDATE employees
    SET salary = v_new_salaries(i)
    WHERE employee_id = v_emp_ids(i);
  
  DBMS_OUTPUT.PUT_LINE('批量更新完成，影响行数: ' || SQL%ROWCOUNT);
  
  -- 显示调整详情
  FOR i IN 1..v_emp_ids.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('员工ID ' || v_emp_ids(i) ||
                        ': $' || v_old_salaries(i) ||
                        ' -> $' || v_new_salaries(i));
  END LOOP;
  
  COMMIT;
  
END;
/

6. 游标性能优化

6.1 游标性能考虑因素

6.2 性能对比示例

6.2.1 传统处理 vs BULK COLLECT

-- 传统逐行处理方式
CREATE OR REPLACE PROCEDURE process_employees_traditional
AS
  CURSOR emp_cursor IS
    SELECT employee_id, salary
    FROM employees;
    
  emp_rec emp_cursor%ROWTYPE;
  v_start_time NUMBER;
  v_end_time NUMBER;
  v_count NUMBER := 0;
  
BEGIN
  v_start_time := DBMS_UTILITY.GET_TIME;
  
  OPEN emp_cursor;
  LOOP
    FETCH emp_cursor INTO emp_rec;
    EXIT WHEN emp_cursor%NOTFOUND;
    
    -- 模拟处理操作
    v_count := v_count + 1;
    
    -- 可以在这里进行具体的业务处理
    NULL;
    
  END LOOP;
  CLOSE emp_cursor;
  
  v_end_time := DBMS_UTILITY.GET_TIME;
  
  DBMS_OUTPUT.PUT_LINE('传统方式处理 ' || v_count || ' 条记录');
  DBMS_OUTPUT.PUT_LINE('耗时: ' || (v_end_time - v_start_time) / 100 || ' 秒');
  
END;
/

-- BULK COLLECT批量处理方式
CREATE OR REPLACE PROCEDURE process_employees_bulk
AS
  TYPE emp_record_array IS TABLE OF employees%ROWTYPE;
  v_employees emp_record_array;
  
  v_start_time NUMBER;
  v_end_time NUMBER;
  v_count NUMBER := 0;
  
BEGIN
  v_start_time := DBMS_UTILITY.GET_TIME;
  
  SELECT * BULK COLLECT INTO v_employees FROM employees;
  
  FOR i IN 1..v_employees.COUNT LOOP
    v_count := v_count + 1;
    -- 处理每条记录
    NULL;
  END LOOP;
  
  v_end_time := DBMS_UTILITY.GET_TIME;
  
  DBMS_OUTPUT.PUT_LINE('BULK COLLECT方式处理 ' || v_count || ' 条记录');
  DBMS_OUTPUT.PUT_LINE('耗时: ' || (v_end_time - v_start_time) / 100 || ' 秒');
  
END;
/

-- 性能测试
BEGIN
  DBMS_OUTPUT.PUT_LINE('=== 游标性能对比测试 ===');
  process_employees_traditional;
  DBMS_OUTPUT.PUT_LINE('---');
  process_employees_bulk;
END;
/

结语
感谢您的阅读！期待您的一键三连！欢迎指正！