位置：文档库 > 数据库 > Oracle中游标的使用案例

Oracle中游标的使用案例

李云迪上传于 2020-03-05 03:08

《Oracle中游标的使用案例》

在Oracle数据库开发中，游标（Cursor）是处理查询结果集的核心工具。它允许开发者逐行访问SELECT语句返回的数据，尤其在需要复杂逻辑处理或多行数据操作的场景下，游标提供了灵活且高效的控制能力。本文将通过实际案例，系统讲解Oracle中游标的分类、使用方法及优化技巧，帮助开发者深入理解游标的实际应用价值。

一、游标基础概念

游标是数据库系统为查询结果分配的临时工作区，用于存储SELECT语句的执行结果。开发者可通过游标逐行获取数据，避免一次性加载所有数据到内存，从而提升性能。Oracle中的游标分为两类：

隐式游标：由Oracle自动管理，用于执行单行查询（如SELECT INTO）或DML操作（INSERT/UPDATE/DELETE）。
显式游标：需开发者显式声明、打开、获取和关闭，适用于多行数据处理的复杂场景。

二、显式游标的使用步骤

显式游标的完整生命周期包括声明、打开、获取数据、关闭四个阶段。以下是一个标准模板：

DECLARE
  -- 1. 声明游标及变量
  CURSOR cursor_name IS
    SELECT column1, column2 FROM table_name WHERE condition;
  v_var1 table_name.column1%TYPE;
  v_var2 table_name.column2%TYPE;
BEGIN
  -- 2. 打开游标
  OPEN cursor_name;
  
  -- 3. 循环获取数据
  LOOP
    FETCH cursor_name INTO v_var1, v_var2;
    EXIT WHEN cursor_name%NOTFOUND; -- 无数据时退出循环
    
    -- 业务逻辑处理
    DBMS_OUTPUT.PUT_LINE('Value1: ' || v_var1 || ', Value2: ' || v_var2);
  END LOOP;
  
  -- 4. 关闭游标
  CLOSE cursor_name;
EXCEPTION
  WHEN OTHERS THEN
    -- 异常处理
    IF cursor_name%ISOPEN THEN
      CLOSE cursor_name;
    END IF;
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;

三、游标使用案例详解

案例1：基础游标遍历员工表

需求：遍历员工表（EMPLOYEES），输出工资高于平均值的员工姓名和工资。

DECLARE
  CURSOR emp_cursor IS
    SELECT first_name, salary 
    FROM employees 
    WHERE salary > (SELECT AVG(salary) FROM employees);
  v_name employees.first_name%TYPE;
  v_salary employees.salary%TYPE;
BEGIN
  OPEN emp_cursor;
  DBMS_OUTPUT.PUT_LINE('Employees with above-average salary:');
  
  LOOP
    FETCH emp_cursor INTO v_name, v_salary;
    EXIT WHEN emp_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_name || ': $' || v_salary);
  END LOOP;
  
  CLOSE emp_cursor;
END;

此案例展示了游标如何结合子查询实现条件筛选，并通过循环逐行处理数据。

案例2：带参数的游标动态查询

需求：根据部门ID动态查询员工信息，部门ID由用户输入。

DECLARE
  v_dept_id NUMBER := &input_dept_id; -- 用户输入部门ID
  CURSOR dept_emp_cursor(p_dept_id NUMBER) IS
    SELECT employee_id, first_name, last_name
    FROM employees
    WHERE department_id = p_dept_id;
  v_emp_id employees.employee_id%TYPE;
  v_first_name employees.first_name%TYPE;
  v_last_name employees.last_name%TYPE;
BEGIN
  OPEN dept_emp_cursor(v_dept_id);
  DBMS_OUTPUT.PUT_LINE('Employees in Department ' || v_dept_id || ':');
  
  LOOP
    FETCH dept_emp_cursor INTO v_emp_id, v_first_name, v_last_name;
    EXIT WHEN dept_emp_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_emp_id || ': ' || v_first_name || ' ' || v_last_name);
  END LOOP;
  
  CLOSE dept_emp_cursor;
END;

参数化游标通过传递变量值实现动态查询，增强了代码的复用性。

案例3：使用FOR循环简化游标操作

需求：简化案例1的代码，使用FOR循环自动管理游标。

DECLARE
  CURSOR emp_cursor IS
    SELECT first_name, salary 
    FROM employees 
    WHERE salary > (SELECT AVG(salary) FROM employees);
BEGIN
  DBMS_OUTPUT.PUT_LINE('Employees with above-average salary:');
  
  FOR emp_rec IN emp_cursor LOOP
    DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ': $' || emp_rec.salary);
  END LOOP;
END;

FOR循环隐式处理了游标的打开、获取和关闭，使代码更简洁。

案例4：游标与BULK COLLECT结合提升性能

需求：批量获取员工数据以减少上下文切换开销。

DECLARE
  CURSOR emp_cursor IS
    SELECT employee_id, first_name FROM employees;
  TYPE emp_id_array IS TABLE OF employees.employee_id%TYPE;
  TYPE name_array IS TABLE OF employees.first_name%TYPE;
  v_emp_ids emp_id_array;
  v_names name_array;
BEGIN
  OPEN emp_cursor;
  
  LOOP
    FETCH emp_cursor BULK COLLECT INTO v_emp_ids, v_names LIMIT 100; -- 每次获取100行
    EXIT WHEN v_emp_ids.COUNT = 0;
    
    FOR i IN 1..v_emp_ids.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(v_emp_ids(i) || ': ' || v_names(i));
    END LOOP;
  END LOOP;
  
  CLOSE emp_cursor;
END;

BULK COLLECT通过批量绑定变量显著提升了大数据量下的处理效率。

案例5：游标变量实现动态SQL

需求：使用游标变量执行动态生成的SQL语句。

DECLARE
  TYPE emp_cursor_type IS REF CURSOR; -- 声明游标变量类型
  v_cursor emp_cursor_type;
  v_sql VARCHAR2(200);
  v_dept_id NUMBER := 10;
  v_emp_id employees.employee_id%TYPE;
  v_name employees.first_name%TYPE;
BEGIN
  v_sql := 'SELECT employee_id, first_name FROM employees WHERE department_id = :dept_id';
  
  OPEN v_cursor FOR v_sql USING v_dept_id; -- 动态SQL绑定参数
  
  LOOP
    FETCH v_cursor INTO v_emp_id, v_name;
    EXIT WHEN v_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_emp_id || ': ' || v_name);
  END LOOP;
  
  CLOSE v_cursor;
END;

游标变量支持动态SQL，适用于查询条件不确定的场景。

四、游标属性详解

Oracle为游标提供了多个属性，用于监控游标状态：

%FOUND：FETCH成功返回TRUE，否则FALSE。
%NOTFOUND：FETCH失败返回TRUE（无数据）。
%ISOPEN：游标打开时返回TRUE。
%ROWCOUNT：已获取的行数。

示例：

DECLARE
  CURSOR test_cursor IS SELECT * FROM dual;
BEGIN
  IF NOT test_cursor%ISOPEN THEN
    OPEN test_cursor;
  END IF;
  
  FETCH test_cursor BULK COLLECT INTO ...;
  DBMS_OUTPUT.PUT_LINE('Rows fetched: ' || test_cursor%ROWCOUNT);
  
  CLOSE test_cursor;
END;