sql-cursors

SQL 游标：逐行处理

SQL 本质上是一种基于集合的语言，操作旨在一次处理整个行集。然而，在极少数情况下，你可能需要以更传统、过程化的方式逐行处理数据。游标（cursor）就是这样一个数据库对象，它允许你实现这一点：一次遍历结果集中的一行。

现代视角：基于集合 vs. 过程化逻辑

警告：游标应该是最后的手段。它们通常比基于集合的操作慢得多，并可能导致复杂且难以维护的代码。在使用游标之前，请务必尝试使用标准 SQL JOIN、窗口函数或 CTE 来解决问题。

那么，它们何时适用呢？游标通常仅限于存储过程，并保留用于难以或无法用单个 SQL 语句表达的复杂任务，例如：

对结果集中的每一行执行一系列复杂的存储过程。
执行依赖于前一行值的复杂、有状态的计算。
为每一行构建和执行动态 SQL 语句。

对于数据迁移、条件更新或简单计算等任务，基于集合的方法（例如 INSERT ... SELECT 或 UPDATE ... JOIN）在性能和简洁性方面几乎总是更优。

游标生命周期

管理游标涉及四个不同的步骤：

DECLARE（声明）：通过赋予游标一个名称并将其与定义要遍历的结果集的 SELECT 语句相关联来定义游标。
OPEN（打开）：执行 SELECT 语句并用结果行填充游标。此时游标指向第一行之前的位置。
FETCH（获取）：从游标中检索下一行并前进游标的位置。行中的数据通常存储在局部变量中以供处理。
CLOSE（关闭）：停用游标并释放其正在使用的资源和内存。这是关键的清理步骤。

语法概述（MySQL/MariaDB 示例）

-- 1. 声明游标和变量
DECLARE done INT DEFAULT FALSE;
DECLARE my_variable_type [DATATYPE];
DECLARE my_cursor CURSOR FOR SELECT my_column FROM my_table;

-- 声明一个处理程序以退出循环
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

-- 2. 打开游标
OPEN my_cursor;

-- 3. 循环并获取行
read_loop: LOOP
  FETCH my_cursor INTO my_variable;
  IF done THEN
    LEAVE read_loop;
  END IF;
  -- ... 处理变量 ...
END LOOP;

-- 4. 关闭游标
CLOSE my_cursor;

示例：不适合使用游标的任务

为了说明游标的语法以及为什么它经常是多余的，让我们看一个常见但错误的用例：将数据从一个表复制到另一个表。

首先，我们的表：

CREATE TABLE CUSTOMERS(
    ID INT PRIMARY KEY,
    NAME VARCHAR(100) NOT NULL,
    EMAIL VARCHAR(100)
);
INSERT INTO CUSTOMERS VALUES
    (1, 'Ramesh', 'ramesh@example.com'),
    (2, 'Khilan', 'khilan@example.com');

CREATE TABLE CUSTOMERS_BACKUP(
    ID INT PRIMARY KEY,
    NAME VARCHAR(100) NOT NULL
);

这是一个使用游标将每个客户的 ID 和 NAME 复制到备份表中的存储过程：

DELIMITER //
CREATE PROCEDURE BackupCustomersWithCursor()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE cust_id INT;
    DECLARE cust_name VARCHAR(100);

    -- 1. 声明游标
    DECLARE cust_cursor CURSOR FOR
        SELECT ID, NAME FROM CUSTOMERS;

    -- 声明当没有更多行时退出循环的处理程序
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 2. 打开游标
    OPEN cust_cursor;

    -- 3. 循环获取并插入
    copy_loop: LOOP
        FETCH cust_cursor INTO cust_id, cust_name;
        IF done THEN
            LEAVE copy_loop;
        END IF;
        INSERT INTO CUSTOMERS_BACKUP(ID, NAME) VALUES (cust_id, cust_name);
    END LOOP;

    -- 4. 关闭游标
    CLOSE cust_cursor;
END //
DELIMITER ;

更好的替代方案：基于集合的方法

尽管游标过程有效，但它不必要地复杂且缓慢。同样的结果可以通过一个单一、高效的基于集合的语句来实现：

INSERT INTO CUSTOMERS_BACKUP (ID, NAME)
SELECT ID, NAME FROM CUSTOMERS;

这个单一查询更容易编写、更容易阅读，并且在大型数据集上其性能将比基于游标的方法高出几个数量级。它展示了以数据集而非单个行来思考问题的强大之处。

关键要点

优先使用基于集合的操作：在考虑游标之前，始终尝试使用标准 SQL（JOIN、CASE、GROUP BY 等）来解决问题。
理解性能成本：游标会为每行获取引入额外开销，使其本质上比基于集合的操作慢。
仅在过程化必要时使用：将游标保留用于无法通过基于集合的替代方案处理的复杂、行依赖逻辑。
务必关闭游标：未能关闭游标可能会在表上留下锁并消耗服务器资源，直到会话结束。