【Oracle】游标

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

正文

1. 游标基础概述

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

1.1 游标的概念与作用

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

1.2 游标的生命周期

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

1.3 游标的分类

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

2. 显式游标

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

2.1 显式游标的基本语法

2.1.1 声明游标

-- 基本游标声明
DECLARECURSOR emp_cursor ISSELECT employee_id, first_name, last_name, salaryFROM employeesWHERE 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) ISSELECT employee_id, first_name, last_name, salaryFROM employeesWHERE department_id = p_dept_idORDER BY salary DESC;-- 使用%ROWTYPE简化变量声明emp_record emp_dept_cursor%ROWTYPE;
BEGIN-- 打开游标时传递参数OPEN emp_dept_cursor(20);LOOPFETCH 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 ISSELECT employee_id, first_name, last_name, salary, department_idFROM employeesWHERE salary > 5000ORDER BY salary DESC;-- 声明记录类型变量emp_rec salary_cursor%ROWTYPE;v_counter NUMBER := 0;v_total_salary NUMBER := 0;BEGINDBMS_OUTPUT.PUT_LINE('=== 高薪员工报告 ===');-- 打开游标OPEN salary_cursor;-- 读取数据LOOPFETCH 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));EXCEPTIONWHEN OTHERS THEN-- 确保游标关闭IF salary_cursor%ISOPEN THENCLOSE salary_cursor;END IF;RAISE;
END;
/

2.3 游标属性

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

2.3.1 游标属性应用示例

DECLARECURSOR dept_cursor ISSELECT department_id, department_name, manager_idFROM departmentsWHERE department_id BETWEEN 10 AND 50;dept_rec dept_cursor%ROWTYPE;BEGIN-- 检查游标是否已打开IF NOT dept_cursor%ISOPEN THENOPEN dept_cursor;DBMS_OUTPUT.PUT_LINE('游标已打开');END IF;LOOPFETCH dept_cursor INTO dept_rec;-- 使用%FOUND属性IF dept_cursor%FOUND THENDBMS_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 THENDBMS_OUTPUT.PUT_LINE('游标已关闭');END IF;END;
/

2.4 游标FOR循环

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

2.4.1 基本游标FOR循环

DECLARECURSOR emp_cursor ISSELECT employee_id, first_name, last_name, hire_date, salaryFROM employeesWHERE department_id = 20ORDER BY hire_date;BEGINDBMS_OUTPUT.PUT_LINE('=== 部门20员工信息 ===');-- 游标FOR循环 - 自动管理游标生命周期FOR emp_rec IN emp_cursor LOOPDBMS_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循环

BEGINDBMS_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_countFROM departments dJOIN employees e ON d.department_id = e.department_idGROUP BY d.department_nameORDER BY avg_salary DESC) LOOPDBMS_OUTPUT.PUT_LINE('部门: ' || dept_rec.department_name ||', 平均工资: $' || dept_rec.avg_salary ||', 员工数: ' || dept_rec.emp_count);END LOOP;END;
/

2.4.3 带参数的游标FOR循环

DECLARECURSOR salary_range_cursor(p_min_sal NUMBER, p_max_sal NUMBER) ISSELECT employee_id, first_name, last_name, salary, department_idFROM employeesWHERE salary BETWEEN p_min_sal AND p_max_salORDER BY salary;BEGINDBMS_OUTPUT.PUT_LINE('=== 工资范围 $5000-$10000 的员工 ===');FOR emp_rec IN salary_range_cursor(5000, 10000) LOOPDBMS_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操作中的隐式游标

DECLAREv_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 THENDBMS_OUTPUT.PUT_LINE('部门插入成功，影响行数: ' || SQL%ROWCOUNT);ELSEDBMS_OUTPUT.PUT_LINE('部门插入失败');END IF;-- 更新操作UPDATE employeesSET salary = salary * 1.05WHERE department_id = 20 AND salary < 8000;v_affected_rows := SQL%ROWCOUNT;IF v_affected_rows > 0 THENDBMS_OUTPUT.PUT_LINE('成功给 ' || v_affected_rows || ' 名员工加薪5%');ELSEDBMS_OUTPUT.PUT_LINE('没有符合条件的员工需要加薪');END IF;-- 删除操作DELETE FROM departments WHERE department_id = v_dept_id;IF SQL%FOUND THENDBMS_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语句中的隐式游标

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

3.3 隐式游标与异常处理

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

CREATE OR REPLACE PROCEDURE process_employee_bonus(p_emp_id NUMBER, p_bonus_pct NUMBER)
ASv_current_salary NUMBER;v_new_bonus NUMBER;v_emp_name VARCHAR2(100);BEGIN-- 获取员工信息BEGINSELECT salary, first_name || ' ' || last_nameINTO v_current_salary, v_emp_nameFROM employeesWHERE 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 THENDBMS_OUTPUT.PUT_LINE('奖金更新成功');END IF;EXCEPTIONWHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE('错误: 员工ID ' || p_emp_id || ' 不存在');WHEN TOO_MANY_ROWS THENDBMS_OUTPUT.PUT_LINE('错误: 查询返回多个员工记录');END;END;
/-- 调用存储过程
BEGINprocess_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 FORSELECT * FROM employees WHERE department_id = v_dept_idORDER BY salary DESC;DBMS_OUTPUT.PUT_LINE('=== 部门 ' || v_dept_id || ' 员工列表 ===');LOOPFETCH 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 FORSELECT e.employee_id,e.first_name || ' ' || e.last_name,d.department_name,e.salary,EXTRACT(YEAR FROM e.hire_date)FROM employees eJOIN departments d ON e.department_id = d.department_idWHERE e.salary > 8000ORDER BY e.salary DESC;DBMS_OUTPUT.PUT_LINE('=== 高薪员工摘要报告 ===');LOOPFETCH 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-- 构建动态SQLv_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;LOOPFETCH 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
)
ASquery_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-- 构建基本SQLv_sql := 'SELECT * FROM ' || p_table_name;IF p_where_clause IS NOT NULL THENv_sql := v_sql || ' WHERE ' || p_where_clause;END IF;IF p_order_clause IS NOT NULL THENv_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;EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('查询执行出错: ' || SQLERRM);IF query_cursor%ISOPEN THENCLOSE query_cursor;END IF;
END;
/-- 调用动态查询处理器
BEGINdynamic_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
ASemp_cursor SYS_REFCURSOR;
BEGINOPEN emp_cursor FORSELECT employee_id, first_name, last_name, email, salary, hire_dateFROM employeesWHERE department_id = p_dept_idORDER BY hire_date;RETURN emp_cursor;
END;
/-- 使用返回的REF游标
DECLAREemp_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员工列表 ===');LOOPFETCH 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_salaryFROM employeesWHERE department_id = p_dept_id;-- 打开游标返回详细信息OPEN p_emp_cursor FORSELECT 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_levelFROM employeesWHERE department_id = p_dept_idORDER BY salary DESC;END;
/-- 使用OUT参数REF游标
DECLAREemp_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('========================');-- 显示详细信息LOOPFETCH 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子句

DECLARECURSOR emp_cursor ISSELECT employee_id, first_name, last_name, salary, department_idFROM employeesWHERE department_id = 20FOR UPDATE; -- 锁定查询的行emp_rec emp_cursor%ROWTYPE;v_new_salary NUMBER;BEGINDBMS_OUTPUT.PUT_LINE('=== 部门20员工工资调整 ===');OPEN emp_cursor;LOOPFETCH emp_cursor INTO emp_rec;EXIT WHEN emp_cursor%NOTFOUND;-- 根据当前工资计算新工资IF emp_rec.salary < 5000 THENv_new_salary := emp_rec.salary * 1.15; -- 加薪15%ELSIF emp_rec.salary < 8000 THENv_new_salary := emp_rec.salary * 1.10; -- 加薪10%ELSEv_new_salary := emp_rec.salary * 1.05; -- 加薪5%END IF;-- 使用WHERE CURRENT OF更新当前行UPDATE employees SET salary = v_new_salaryWHERE 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 选择性锁定

DECLARECURSOR emp_cursor ISSELECT employee_id, first_name, last_name, salary, commission_pctFROM employeesWHERE department_id IN (80, 90)FOR UPDATE OF salary NOWAIT; -- 只锁定salary列，不等待emp_rec emp_cursor%ROWTYPE;v_bonus NUMBER;BEGINDBMS_OUTPUT.PUT_LINE('=== 销售和管理部门绩效奖金计算 ===');OPEN emp_cursor;LOOPFETCH emp_cursor INTO emp_rec;EXIT WHEN emp_cursor%NOTFOUND;-- 计算绩效奖金IF emp_rec.commission_pct IS NOT NULL THENv_bonus := emp_rec.salary * emp_rec.commission_pct; -- 有提成的员工ELSEv_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;EXCEPTIONWHEN OTHERS THENIF emp_cursor%ISOPEN THENCLOSE emp_cursor;END IF;IF SQLCODE = -54 THEN -- Resource busyDBMS_OUTPUT.PUT_LINE('错误: 记录正被其他会话使用');ELSEDBMS_OUTPUT.PUT_LINE('错误: ' || SQLERRM);END IF;
END;
/

5.2 批量操作(BULK COLLECT)

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

5.2.1 基本BULK COLLECT

DECLARETYPE 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,salaryBULK COLLECT INTO v_emp_ids, v_emp_names, v_emp_salariesFROM employeesWHERE department_id = 50ORDER BY salary DESC;DBMS_OUTPUT.PUT_LINE('=== 部门50员工信息（共' || v_emp_ids.COUNT || '人）===');-- 处理批量数据FOR i IN 1..v_emp_ids.COUNT LOOPv_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

DECLARECURSOR large_table_cursor ISSELECT employee_id, first_name, last_name, salaryFROM 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;BEGINDBMS_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 LOOPv_total_processed := v_total_processed + 1;-- 这里可以进行复杂的业务处理-- 例如：数据转换、验证、插入到其他表等IF MOD(v_total_processed, 50) = 0 THENDBMS_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操作

DECLARETYPE 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, salaryBULK COLLECT INTO v_emp_ids, v_old_salariesFROM employeesWHERE department_id = 30AND salary < 6000;-- 计算新工资v_new_salaries := salary_array();v_new_salaries.EXTEND(v_emp_ids.COUNT);FOR i IN 1..v_emp_ids.COUNT LOOPv_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.COUNTUPDATE employeesSET 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 LOOPDBMS_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
ASCURSOR emp_cursor ISSELECT employee_id, salaryFROM employees;emp_rec emp_cursor%ROWTYPE;v_start_time NUMBER;v_end_time NUMBER;v_count NUMBER := 0;BEGINv_start_time := DBMS_UTILITY.GET_TIME;OPEN emp_cursor;LOOPFETCH 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
ASTYPE 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;BEGINv_start_time := DBMS_UTILITY.GET_TIME;SELECT * BULK COLLECT INTO v_employees FROM employees;FOR i IN 1..v_employees.COUNT LOOPv_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;
/-- 性能测试
BEGINDBMS_OUTPUT.PUT_LINE('=== 游标性能对比测试 ===');process_employees_traditional;DBMS_OUTPUT.PUT_LINE('---');process_employees_bulk;
END;
/

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