Oracle（36）什么是游标（Cursor）？

游标（Cursor）是PL/SQL中用于处理查询结果集的一种机制。它提供了一种逐行处理查询结果的方法，使得可以在PL/SQL代码中逐行访问和操作查询返回的数据。游标主要分为两种类型：显式游标和隐式游标。

游标的类型

1. 隐式游标：当PL/SQL执行一个SQL查询时，系统会自动创建一个隐式游标。例如，SELECT INTO语句会隐式地创建一个游标来存储查询结果。
2. 显式游标：显式游标由用户明确定义和控制，适用于需要逐行处理查询结果的情况。

显式游标的使用步骤

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中处理查询结果集的重要工具。通过显式游标，可以逐行处理查询结果，适用于需要对每一行数据进行复杂处理的情况。显式游标的使用步骤包括定义游标、打开游标、提取数据和关闭游标。游标属性可以帮助我们获取游标的状态信息。此外，参数化游标可以使查询更加灵活和动态。在实际开发中，合理使用游标可以提高代码的可读性和可维护性。