什么是 SQL Cursor？数据库游标详解入门 – wiki基地

---

什么是 SQL Cursor？数据库游标详解入门

在数据库操作中，我们通常使用 SQL 语句来处理数据。大多数 SQL 语句，尤其是 SELECT 语句，返回的是一个结果集 (Result Set)。结果集可以看作是一个虚拟的表格，包含满足查询条件的所有行。我们习惯于一次性处理整个结果集，这种方式被称为集合化操作 (Set-Based Operations)。集合化操作是 SQL 的核心优势，它效率高、简洁强大。

然而，在某些特定的场景下，我们可能需要像处理程序中的数组或列表一样，逐行地对结果集进行操作。例如，需要对结果集中的每一行执行一个复杂的计算、调用一个外部函数，或者根据当前行的值来决定下一行如何处理。这时，集合化操作就显得力不从心了。

为了解决这个问题，数据库系统引入了 游标 (Cursor) 的概念。

简单来说，SQL Cursor 是一种数据库对象，它允许你定位到结果集中的特定行，并逐行地处理这些数据。 想象一下你正在阅读一本厚厚的书，结果集就像是书中的所有页面（或经过筛选后的特定页面），而游标就像是你的书签。通过书签，你可以标记当前阅读的页面，然后一页一页地往后翻，在每一页上执行你需要的操作。

所以，游标的作用就是将通常的集合化处理方式，转换为更接近传统程序设计中的过程化 (Procedural) 处理方式，即逐行处理数据。

集合化操作 vs. 过程化操作 (Cursor)

理解游标的关键在于理解 SQL 的两种主要操作范式：

1. 集合化操作 (Set-Based Operations): 这是 SQL 的设计哲学。你告诉数据库你需要什么数据（通过 SELECT 语句描述条件），或者你想对一组数据做什么操作（UPDATE, DELETE, INSERT），但你不指定数据库如何去获取或处理这些数据。数据库引擎会自己决定最高效的方式来完成这个任务，通常是批量处理，效率非常高。

   • 例子: UPDATE Orders SET Status = 'Shipped' WHERE OrderDate < '2023-01-01'。这条语句会一次性找出所有符合条件的订单，并更新它们的状体，而不需要你逐个检查每个订单。

2. 过程化操作 (Procedural Operations) / 逐行处理 (Row-by-Row Processing): 这更接近传统编程语言的思维，使用循环结构（如 FOR 或 WHILE）来遍历数据集，并对每一项执行操作。在 SQL 中，游标就是实现这种逐行处理的主要机制。

   • 例子: 如果你使用游标来处理上面的订单更新任务，你可能会：打开一个游标，定位到符合条件的第一行订单；获取这一行的信息；更新这一行的状态；移动到下一行；重复直到所有行处理完毕。

重要对比:

• 效率: 集合化操作通常比游标快得多，尤其是在处理大量数据时。数据库引擎为集合化操作做了大量优化。
• 资源: 游标通常需要更多的系统资源（内存、锁定），并且可能导致死锁或性能瓶颈。
• 复杂性: 集合化 SQL 有时可能看起来复杂，但游标的实现代码往往更冗长、更难以理解和维护。
• 用途: 大多数数据库任务都可以而且应该用集合化方式完成。只有在极少数情况下，当集合化方式非常困难或不可能实现时，才考虑使用游标。

因此，SQL 游标是一种功能强大的工具，但它也带来了性能开销和复杂性。在决定使用游标之前，务必仔细思考是否能用集合化方式解决问题。

游标的工作原理和生命周期

一个典型的 SQL 游标有以下几个关键阶段，可以类比上面的“书签”例子：

1. 声明 (DECLARE CURSOR): 定义游标的名称、它将基于哪个 SELECT 语句的结果集，以及游标的特性（例如是只读的还是可更新的，是静态的还是动态的等）。这就像是决定你要读哪本书（表或连接），以及你要关注书中的哪些内容（WHERE 子句）。
2. 打开 (OPEN CURSOR): 执行与游标关联的 SELECT 语句，生成实际的结果集。游标被定位到结果集的第一行之前。这就像是翻开书本，准备开始阅读，书签放在第一页的前面。
3. 获取 (FETCH): 从结果集中读取一行数据到一组变量中。每次获取操作都会将游标向前移动到下一行。这是实际的“阅读”过程，你把当前书签页的内容读出来，然后把书签移到下一页。
4. 处理 (Process): 对刚刚获取的行数据执行逻辑操作。这是你根据书页内容进行思考或做笔记的阶段。
5. 循环 (Loop): 重复获取和处理步骤，直到到达结果集的末尾。你一页一页地往后翻书，直到读完最后一页。
6. 关闭 (CLOSE CURSOR): 释放当前结果集和游标占用的资源，但游标对象仍然存在。这就像是合上书本，但书签仍然放在里面。
7. 释放 (DEALLOCATE CURSOR): 彻底销毁游标对象。这就像是把书签从书中拿出来，并且把书也放回书架，结束了整个阅读过程。

如何使用 SQL 游标 (以 SQL Server T-SQL 为例)

不同的数据库系统（如 SQL Server, Oracle, MySQL, PostgreSQL）在游标的语法上可能略有差异，但基本概念和步骤是相似的。这里以 SQL Server 的 T-SQL 语法为例进行讲解。

基本语法结构:

“`sql
— 1. 声明游标
DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] [ FORWARD_ONLY | SCROLL ] [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] [ TYPE_WARNING ]
FOR select_statement [ FOR UPDATE OF column_name [,…n] ] ;

— 2. 打开游标
OPEN cursor_name;

— 3. 声明变量用于存储获取的数据
DECLARE @variable1 datatype, @variable2 datatype, … ;

— 4. 获取第一行数据
FETCH NEXT FROM cursor_name INTO @variable1, @variable2, … ;

— 5. 检查获取状态并循环处理
— @@FETCH_STATUS 是一个系统全局变量，表示上次 FETCH 操作的状态：
— 0: FETCH 操作成功
— -1: FETCH 操作失败或行不存在
— -2: FETCH 操作时，被获取的行已被删除
WHILE @@FETCH_STATUS = 0
BEGIN
— 6. 在这里对 @variable1, @variable2 等变量进行处理
— 执行你想对每一行做的操作 (UPDATE, INSERT, DELETE, 调用存储过程等)

-- 7. 获取下一行数据
FETCH NEXT FROM cursor_name INTO @variable1, @variable2, ... ;

END

— 8. 关闭游标
CLOSE cursor_name;

— 9. 释放游标
DEALLOCATE cursor_name;
“`

各部分的详细说明:

• DECLARE CURSOR:

  • cursor_name: 你为游标指定的名称。
  • [ LOCAL | GLOBAL ]: 游标的作用域。LOCAL (默认) 只在当前存储过程、触发器或脚本中可见；GLOBAL 在整个连接中可见。
  • [ FORWARD_ONLY | SCROLL ]:
    • FORWARD_ONLY: 游标只能向前移动（从第一行到最后一行）。这是最常见且性能最好的类型。
    • SCROLL: 游标可以向前或向后移动，可以定位到第一行、最后一行、绝对位置或相对位置。这需要更多资源。
  • [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]: 游标的类型，影响游标对基础数据变化的敏感度以及性能。
    • STATIC: 游标打开时，结果集的数据会被完整复制到 tempdb 中。游标中的数据是打开时的快照，不受后续对基础表进行的插入、更新、删除操作影响。适用于需要固定结果集进行遍历的场景，但对内存需求较大。
    • KEYSET: 游标打开时，结果集的键集（唯一标识符）会被复制到 tempdb 中。游标可以感知到基础表的更新和删除，但感知不到插入。性能介于 STATIC 和 DYNAMIC 之间。
    • DYNAMIC: 游标可以感知基础表的所有变化（插入、更新、删除）。获取每一行时，数据是实时的。这是最灵活但性能最低的类型。
    • FAST_FORWARD: 这是 FORWARD_ONLY 和 READ_ONLY 的组合优化选项，是默认类型。适用于只需要快速单向遍历结果集的场景。
  • [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]: 游标的并发控制选项。
    • READ_ONLY: 游标是只读的，不能通过游标修改基础数据。这是最推荐的选项，如果不需要更新数据的话。
    • SCROLL_LOCKS: 在读取每一行时，会在基础表上加锁，保证数据不会被其他事务所修改。会降低并发性。
    • OPTIMISTIC: 不加锁。当通过游标尝试修改数据时，会检查自上次读取以来数据是否被修改过。如果被修改，更新会失败。
  • [ TYPE_WARNING ]: 当游标类型从请求的类型隐式更改时发出警告。
  • select_statement: 定义游标结果集的 SELECT 查询。
  • [ FOR UPDATE OF column_name [,...n] ]: 指定可以通过游标更新哪些列。只有某些游标类型支持更新。

• OPEN cursor_name;: 执行 SELECT 语句并填充游标。

• FETCH NEXT FROM cursor_name INTO @variable1, …;: 从游标当前位置获取下一行数据，并将列值赋给相应的变量。
• WHILE @@FETCH_STATUS = 0 … END: 循环结构，只要上次 FETCH 成功（@@FETCH_STATUS 为 0），就执行循环体内的代码。
• CLOSE cursor_name;: 释放游标占用的临时资源。
• DEALLOCATE cursor_name;: 彻底销毁游标对象，释放其名称。

实际使用示例

假设我们有一个 Employees 表，包含 EmployeeID, FirstName, LastName, Salary, Department 等列。现在我们想找出市场部 (Marketing) 的员工，并为每位员工的工资增加 10%（这是一个简单的例子，完全可以用集合化 UPDATE 语句实现，但这里用游标演示）。

“`sql
— 声明变量用于存储从游标获取的数据
DECLARE @employeeID INT;
DECLARE @firstName NVARCHAR(50);
DECLARE @lastName NVARCHAR(50);
DECLARE @currentSalary DECIMAL(10, 2);
DECLARE @department NVARCHAR(50);

— 声明一个名为 EmployeeCursor 的游标
— 类型为 FAST_FORWARD (单向只读，性能较高)
DECLARE EmployeeCursor CURSOR FAST_FORWARD FOR
SELECT EmployeeID, FirstName, LastName, Salary, Department
FROM Employees
WHERE Department = ‘Marketing’;

— 打开游标
OPEN EmployeeCursor;

— 获取第一行数据到变量中
FETCH NEXT FROM EmployeeCursor
INTO @employeeID, @firstName, @lastName, @currentSalary, @department;

— 循环处理每一行，直到没有更多行
WHILE @@FETCH_STATUS = 0
BEGIN
— 打印当前员工信息 (作为处理示例)
PRINT ‘Processing EmployeeID: ‘ + CAST(@employeeID AS NVARCHAR) +
‘, Name: ‘ + @firstName + ‘ ‘ + @lastName +
‘, Current Salary: ‘ + CAST(@currentSalary AS NVARCHAR);

-- 对当前行的工资进行更新操作 (这是一个简单的演示，如果真的要更新，游标类型需要支持更新，并且要执行 UPDATE 语句)
-- 实际更新操作通常在循环内部执行一个单独的 UPDATE 语句，使用获取到的主键
-- 例如: UPDATE Employees SET Salary = Salary * 1.10 WHERE EmployeeID = @employeeID;
-- 注意: 如果游标是 READ_ONLY，这里不能通过游标本身进行更新。需要执行一个独立的 UPDATE 语句。

-- 假设我们只是打印更新后的理论工资
DECLARE @newSalary DECIMAL(10, 2) = @currentSalary * 1.10;
PRINT 'Hypothetical New Salary: ' + CAST(@newSalary AS NVARCHAR);
PRINT '---';

-- 获取下一行数据
FETCH NEXT FROM EmployeeCursor
INTO @employeeID, @firstName, @lastName, @currentSalary, @department;

END

— 关闭游标
CLOSE EmployeeCursor;

— 释放游标
DEALLOCATE EmployeeCursor;

PRINT ‘Cursor processing finished.’;
“`

重要提示: 上面的例子中，我们在游标循环内部只是打印信息。如果真的要更新数据，通常是声明一个可更新的游标类型（例如省略 READ_ONLY，并可能指定 FOR UPDATE 子句），然后在循环内部使用 UPDATE WHERE CURRENT OF cursor_name 语法，或者更常见、更灵活的方式是使用获取到的主键在循环内部执行一个标准的 UPDATE 语句：UPDATE Employees SET Salary = Salary * 1.10 WHERE EmployeeID = @employeeID;。但请记住，即使是这种方式，逐行执行 UPDATE 语句的性能也远不如集合化 UPDATE。

游标的类型 (更深入一点)

前面提到了几种游标类型，它们之间的主要区别在于：

1. 对基础数据变化的敏感度: STATIC 不敏感，KEYSET 对更新和删除敏感但对插入不敏感，DYNAMIC 对所有变化都敏感。
2. 性能和资源消耗: FORWARD_ONLY 和 FAST_FORWARD 通常性能最好，资源消耗最少。SCROLL、STATIC、DYNAMIC 消耗资源更多。
3. 可滚动性: SCROLL 允许前后移动，其他类型通常只允许向前或不允许移动。

对于初学者和大多数应用场景，如果确实需要使用游标，FAST_FORWARD 是最常用和推荐的类型，因为它提供了最佳的性能（作为游标而言）和最低的资源开销，并且是 SQL Server 的默认类型。它强制游标为 FORWARD_ONLY 和 READ_ONLY。

什么时候应该考虑使用游标？

尽管应该尽量避免使用游标，但在某些特定场景下，它们可能是最直接或唯一的解决方案：

1. 复杂的逐行逻辑: 当需要对每一行数据执行依赖于该行数据的复杂计算、条件判断或过程调用，且这些逻辑无法用集合化 SQL 有效表达时。
2. 调用外部函数或存储过程: 当需要为结果集中的每一行调用一个存储过程或一个外部函数，并且这些过程/函数不能处理集合输入时。
3. 维护数据库结构或权限: 在某些特定的数据库管理任务脚本中，可能需要遍历数据库对象（如表、存储过程）并对每一个对象执行操作（如修改权限、生成脚本）。
4. 遗留系统兼容性: 在维护旧系统时，可能不得不处理已经存在的、使用了游标的代码。

再次强调：在编写新代码时，务必优先考虑集合化解决方案。只有在确定无法用集合化方式解决时，才考虑游标。

什么时候绝对不应该使用游标？

1. 可以轻易用集合化 SQL 实现的任务: 大多数数据更新、删除、插入、聚合、筛选任务都可以通过 UPDATE, DELETE, INSERT INTO ... SELECT, MERGE, GROUP BY 等集合化语句高效完成。

   • 错误示例 (用游标更新大量数据):
     “`sql
     — BAD: Extremely inefficient for large tables
     DECLARE @ProductID INT, @Price DECIMAL(10,2);
     DECLARE ProductCursor CURSOR FOR SELECT ProductID, Price FROM Products;
     OPEN ProductCursor;
     FETCH NEXT FROM ProductCursor INTO @ProductID, @Price;
     WHILE @@FETCH_STATUS = 0
     BEGIN
     UPDATE Products SET Price = @Price * 1.10 WHERE ProductID = @ProductID; — 逐行更新
     FETCH NEXT FROM ProductCursor INTO @ProductID, @Price;
     END
     CLOSE ProductCursor;
     DEALLOCATE ProductCursor;

     — GOOD: Efficient set-based update
     — UPDATE Products SET Price = Price * 1.10; — 一次性更新所有行
     “`
     2. 对性能要求高的大规模数据处理: 游标的逐行处理特性在高并发或处理大量数据时会成为严重的性能瓶颈，导致响应时间长、锁竞争激烈等问题。
     3. 为了简化复杂的集合操作: 有时初学者觉得集合化 SQL 难以理解，转而使用游标来实现他们熟悉的循环逻辑。这通常会导致代码效率低下且难以优化。

游标的替代方案

在许多原本可能考虑使用游标的场景下，有更优的选择：

1. 集合化操作: 这是首选。使用 UPDATE/DELETE/INSERT 结合 JOIN、SUBQUERY、CASE 表达式等来一次性处理多行。
2. 临时表 (Temporary Tables) 或表变量 (Table Variables): 将中间结果存储在临时表或表变量中，然后对这些临时数据进行集合化操作。这可以将一个复杂的任务分解为多个更易于管理和优化的集合步骤。
3. 公用表表达式 (CTE – Common Table Expressions): 可以用来组织复杂的查询逻辑，尤其是递归 CTE 有时可以模拟一些需要迭代的场景（虽然用途有限）。

4. ** WHILE 循环结合单行操作 (使用 TOP 1): 在某些需要逐行处理 并且 在处理过程中数据可能发生变化（影响后续行的选择）的复杂场景下，可以使用 WHILE 循环结合 SELECT TOP 1 和 DELETE/UPDATE 的方式来模拟游标的行为，有时可能比动态游标性能更好，但也需谨慎使用，且同样是逐行处理。
   “`sql
   — Example alternative using WHILE and TOP 1
   DECLARE @ProcessingID INT;

   WHILE EXISTS(SELECT 1 FROM Orders WHERE Status = ‘Pending’) — Check if there are still rows to process
   BEGIN
   — Select the first row to process (you might need ORDER BY for determinism)
   SELECT TOP 1 @ProcessingID = OrderID FROM Orders WHERE Status = ‘Pending’ ORDER BY OrderDate;

   -- Process the row (e.g., update status)
   -- Perform complex logic here based on @ProcessingID
   UPDATE Orders SET Status = 'Processing' WHERE OrderID = @ProcessingID; -- Mark as processing to avoid picking it again
   
   -- Optionally, commit here if processing is long and independent
   -- COMMIT; -- Depends on transaction strategy
   

   END
   “`
   这种方式避免了游标的开销，但仍然是逐行处理，效率不高，且需要小心处理并发问题。

使用游标的最佳实践 (如果必须使用)

如果经过深思熟虑，确实需要使用游标，请遵循以下最佳实践来最小化其负面影响：

1. 使用最轻量级的游标类型: 优先使用 FAST_FORWARD。如果需要更新数据，考虑 FORWARD_ONLY 结合单独的 UPDATE 语句（使用主键）或者 KEYSET + OPTIMISTIC。避免使用 DYNAMIC 和 SCROLL，除非绝对必要。
2. 只获取必需的列: SELECT 语句只包含在游标循环中需要用到的列，减少 I/O 和内存消耗。
3. 在循环内部做尽量少的工作: 游标循环体内的代码越少越好，越快越好。避免在循环内执行大型查询或复杂计算。
4. 尽快关闭和释放游标: 在完成所有行处理后，立即 CLOSE 和 DEALLOCATE 游标，释放资源。
5. 在大型事务中谨慎使用: 如果游标在一个大型事务内部运行，它可能会长时间持有锁，影响其他操作。考虑在循环内部分批提交事务，但这需要仔细设计以确保数据一致性。
6. 使用 @@FETCH_STATUS 正确控制循环: 确保循环条件是 @@FETCH_STATUS = 0，并在循环末尾获取下一行。

总结

SQL Cursor 是数据库中用于实现逐行处理结果集的一种机制。它将通常高效的集合化操作转换为类似传统编程的循环遍历方式。

优点:

• 允许对结果集中的每一行执行复杂的、依赖于行数据的过程化逻辑。
• 在少数特定场景下，提供了集合化操作难以实现的灵活性。

缺点:

• 严重的性能开销: 逐行处理效率低下，尤其是在处理大量数据时。
• 资源消耗高: 需要额外的内存和锁资源。
• 增加复杂性: 代码更冗长，更难以编写、理解和维护。
• 可能导致并发问题和死锁。

核心建议:

永远优先考虑使用集合化 SQL 语句解决问题。只有在确定集合化方式无法实现，并且仔细权衡了性能和资源开销后，才考虑使用游标，并且务必选择最适合且最轻量级的游标类型，并遵循最佳实践。

希望这篇文章能帮助你理解 SQL 游标是什么，它为什么存在，如何使用，以及最重要的——什么时候该用它，什么时候不该用它。掌握集合化操作是成为一名高效 SQL 开发者的关键！

---