Oracle（38）如何使用游标？

游标（Cursor）在PL/SQL中用于逐行处理查询结果集。使用游标可以更灵活地操作数据库中的数据，特别是当需要对查询结果进行逐行处理时。下面详细介绍如何使用游标，并结合代码示例进行说明。

使用游标的步骤

1. 定义游标：声明游标并指定SQL查询。
2. 打开游标：执行SQL查询，并将结果集存储在游标中。
3. 提取数据：逐行提取数据进行处理。
4. 关闭游标：释放游标资源。

代码示例

下面是一个详细的显式游标使用示例，展示如何定义、打开、提取和关闭游标。

1. 创建表和插入示例数据

首先，创建一个测试表 employees 并插入一些示例数据。

CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    salary NUMBER
);

INSERT INTO employees (employee_id, first_name, last_name, salary)
VALUES (1, 'John', 'Doe', 5000);

INSERT INTO employees (employee_id, first_name, last_name, salary)
VALUES (2, 'Jane', 'Smith', 6000);

INSERT INTO employees (employee_id, first_name, last_name, salary)
VALUES (3, 'Alice', 'Brown', 7000);

COMMIT;

2. 使用显式游标

下面的示例展示了如何使用显式游标逐行处理 employees 表中的数据。

DECLARE
    -- 定义游标
    CURSOR emp_cursor IS
        SELECT employee_id, first_name, last_name, salary
        FROM employees;
        
    -- 定义变量来存储游标提取的数据
    v_employee_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
    -- 打开游标
    OPEN emp_cursor;
    
    LOOP
        -- 提取游标中的数据到变量
        FETCH emp_cursor INTO v_employee_id, v_first_name, v_last_name, v_salary;
        
        -- 退出循环的条件：游标已提取完所有数据
        EXIT WHEN emp_cursor%NOTFOUND;
        
        -- 处理提取的数据
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id || ', Name: ' || v_first_name || ' ' || v_last_name || ', Salary: ' || v_salary);
    END LOOP;
    
    -- 关闭游标
    CLOSE emp_cursor;
END;
/

游标属性

游标有一些属性可以用来获取游标的状态信息：

1. %FOUND：在执行 FETCH 语句后，如果成功提取到数据，则返回 TRUE。
2. %NOTFOUND：在执行 FETCH 语句后，如果没有提取到数据，则返回 TRUE。
3. %ISOPEN：如果游标已经打开，则返回 TRUE。
4. %ROWCOUNT：返回游标提取的行数。

示例：使用游标属性

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name, last_name, salary
        FROM employees;
        
    v_employee_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
    OPEN emp_cursor;
    
    LOOP
        FETCH emp_cursor INTO v_employee_id, v_first_name, v_last_name, v_salary;
        
        EXIT WHEN emp_cursor%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id || ', Name: ' || v_first_name || ' ' || v_last_name || ', Salary: ' || v_salary);
        
        -- 显示当前提取的行数
        DBMS_OUTPUT.PUT_LINE('Row Count: ' || emp_cursor%ROWCOUNT);
    END LOOP;
    
    CLOSE emp_cursor;
END;
/

使用参数化游标

游标可以带参数，这样可以在打开游标时传递不同的参数值，从而动态地控制查询。

示例：参数化游标

DECLARE
    CURSOR emp_cursor (p_min_salary NUMBER) IS
        SELECT employee_id, first_name, last_name, salary
        FROM employees
        WHERE salary >= p_min_salary;
        
    v_employee_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
    -- 打开游标并传递参数
    OPEN emp_cursor(6000);
    
    LOOP
        FETCH emp_cursor INTO v_employee_id, v_first_name, v_last_name, v_salary;
        
        EXIT WHEN emp_cursor%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id || ', Name: ' || v_first_name || ' ' || v_last_name || ', Salary: ' || v_salary);
    END LOOP;
    
    CLOSE emp_cursor;
END;
/

隐式游标

隐式游标是由PL/SQL引擎自动创建和管理的游标，用于处理单行查询结果。隐式游标通常用于简单的查询操作，如 SELECT INTO 语句。

示例：使用隐式游标

DECLARE
    v_first_name employees.first_name%TYPE;
    v_last_name employees.last_name%TYPE;
    v_salary employees.salary%TYPE;
BEGIN
    -- 使用隐式游标执行单行查询
    SELECT first_name, last_name, salary
    INTO v_first_name, v_last_name, v_salary
    FROM employees
    WHERE employee_id = 1;
    
    -- 输出查询结果
    DBMS_OUTPUT.PUT_LINE('Name: ' || v_first_name || ' ' || v_last_name || ', Salary: ' || v_salary);
END;
/

总结

游标在PL/SQL中是处理查询结果集的重要工具。显式游标提供了更多的控制和灵活性，适用于需要逐行处理查询结果的复杂操作；而隐式游标则简化了操作，适用于简单的单行查询。通过定义、打开、提取和关闭游标，可以有效地管理和处理查询结果，同时利用游标属性和参数化游标可以实现更灵活的查询处理。了解和掌握游标的使用方法，可以帮助开发者更好地操作和管理数据库中的数据。