幕雪

cursor怎么用什么是数据库cursor？为什么要用？怎么声明、打开、获取数据、循环处理、关闭与释放？有哪些替代方法和注意事项？


什么是数据库Cursor？

在数据库领域，我们通常谈论的“cursor”（游标）是指数据库游标，而非图形用户界面（GUI）中的鼠标指针。
数据库游标是一个数据库对象，它允许应用程序代码（如存储过程、函数或脚本）以逐行的方式遍历和处理SELECT语句返回的结果集。

与传统的SQL语句一次性处理整个结果集（称为集合操作）不同，游标提供了一种机制来指向结果集中的特定行，并一次只对这一行进行操作。
可以将其想象成在结果集上放置了一个指针，你可以移动这个指针，逐行地读取或修改数据。

数据库Cursor的类型

根据游标对底层数据变化的敏感度以及提供的数据访问能力，常见的数据库游标类型包括：

  • 静态游标 (Static Cursor): 当游标打开时，会将整个结果集的一个临时副本存储在内存或tempdb中。一旦游标打开，其结果集就不会受到游标打开后对底层数据所做的插入、更新或删除操作的影响。它提供了一个结果集的快照。
  • 动态游标 (Dynamic Cursor): 与静态游标相反，动态游标会反映游标打开后对底层数据所做的所有更改（插入、更新和删除）。当你从游标中获取（FETCH）数据时，看到的是最新的数据。这使得动态游标实现起来更复杂，性能开销也可能更大。
  • 键集驱动游标 (Keyset-Driven Cursor): 这种游标在打开时，会建立结果集中行的唯一标识符（键集）。它可以检测到对现有行的更新和删除，但通常无法检测到在游标打开后插入的新行。
  • 只进游标 (Forward-Only Cursor): 这是最简单和效率最高的游标类型（例如SQL Server中的FAST_FORWARD）。它只能向前遍历结果集，不能向后、跳过或回到第一行。通常对底层数据不敏感（类似静态），但不需要在打开时建立整个结果集的副本，而是逐行获取，因此资源消耗相对较低。

选择哪种类型的游标取决于你的具体需求，例如是否需要看到最新的数据，是否需要向前或向后遍历结果集等。

为什么要使用数据库Cursor？什么时候用？

尽管数据库设计鼓励使用集合操作以获得更好的性能，但在某些特定场景下，使用游标可能是必要或更简便的选择。

需要使用数据库Cursor的常见场景：

  • 逐行处理复杂逻辑： 当你需要对结果集中的每一行执行复杂的、依赖于特定行数据的操作时，例如：
    • 根据每一行的值调用外部系统或服务。
    • 根据每一行的数据进行复杂的计算，且该计算结果只影响当前行或需要独立记录。
    • 生成依赖于前一行或后续行数据的报告或输出（尽管有些可以通过窗口函数实现）。
  • 处理无法轻松转换为集合操作的任务： 有些遗留系统或特定的业务逻辑难以或不可能用标准的集合操作（如JOIN, WHERE, GROUP BY, UPDATE ... FROM）来高效实现。
  • 存储过程或函数中必须逐行处理中间结果集： 在某些数据库系统中，存储过程内部需要迭代处理一个临时的查询结果，游标提供了一种方式。
  • 数据库管理任务： 某些数据库维护脚本或管理工具可能需要游标来逐个处理数据库对象或执行特定操作。

重要提示： 数据库游标通常比等效的集合操作效率低得多，并且会消耗更多的服务器资源（内存、锁、tempdb空间）。因此，只有当无法通过集合操作或更优化的方法解决问题时，才应该考虑使用游标。 在可能的情况下，优先使用基于集合的SQL语句。

数据库Cursor怎么用？详细步骤

使用数据库游标通常遵循以下几个基本步骤：

  1. 声明游标 (Declare Cursor): 定义游标的名称、类型以及它将要遍历的SELECT语句。
  2. 打开游标 (Open Cursor): 执行SELECT语句并填充游标。
  3. 获取数据 (Fetch Data): 从游标中一次获取一行（或几行）数据到变量中。
  4. 处理数据 (Process Data): 对获取到的行数据执行所需的逻辑。
  5. 循环获取数据 (Loop Fetch): 重复步骤3和步骤4，直到游标中没有更多行。
  6. 关闭游标 (Close Cursor): 释放当前获取的数据及其相关的锁，但游标定义仍然存在。
  7. 释放游标 (Deallocate Cursor): 销毁游标定义并释放所有相关的系统资源。

下面以SQL Server为例，详细说明这些步骤及其语法：

1. 声明游标 (DECLARE CURSOR)

使用DECLARE CURSOR语句来定义游标。语法如下： 


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]]]

常用选项说明：

  • cursor_name: 你为游标指定的名称。
  • LOCAL | GLOBAL: 指定游标的作用域。LOCAL（默认）只在当前批处理、存储过程、触发器或函数中有效。GLOBAL在连接级别有效，直到断开连接或被释放。
  • FORWARD_ONLY | SCROLL: 指定游标是只能向前移动 (FORWARD_ONLY) 还是可以在结果集中任意移动 (SCROLL)。FORWARD_ONLY是默认且效率更高的选项。
  • STATIC | KEYSET | DYNAMIC | FAST_FORWARD: 指定游标的类型，前面已经解释过。FAST_FORWARD是FORWARD_ONLY和READ_ONLY的组合，通常是只读、向前遍历场景下的推荐选项。
  • READ_ONLY: 指定不能通过游标对底层数据进行修改。
  • FOR select_statement: 定义游标将要遍历的SELECT查询。

示例： 声明一个只进、只读游标来遍历某个表中的部分数据。 


DECLARE MySampleCursor CURSOR

FAST_FORWARD FOR

SELECT CustomerID, CustomerName, OrderAmount

FROM Orders

WHERE OrderDate >= '2023-01-01'

ORDER BY CustomerID;

2. 打开游标 (OPEN CURSOR)

声明游标后，需要使用OPEN语句来执行游标关联的SELECT语句并填充结果集。 


OPEN cursor_name;

示例： 


OPEN MySampleCursor;

执行OPEN语句后，游标指向结果集的上方（在第一行之前）。

3. 获取数据 (FETCH DATA)

使用FETCH语句从游标中获取一行数据到变量中。 


FETCH [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n ]

FROM cursor_name INTO @variable1 [, @variable2, ...n];

常用选项说明：

  • NEXT: 获取结果集中的下一行。对于FORWARD_ONLY游标，这是唯一允许的选项。
  • PRIOR: 获取结果集中的前一行。
  • FIRST: 获取结果集中的第一行。
  • LAST: 获取结果集中的最后一行。
  • ABSOLUTE n: 获取结果集中从开头数起的第n行（如果n为负数，则从结尾数起）。
  • RELATIVE n: 获取结果集中从当前位置偏移n行的那一行（如果n为负数，则向前偏移）。
  • @variable1 [, @variable2, ...n]: 用于存储获取到的行数据的变量列表。变量的数量和数据类型必须与SELECT语句中的列匹配。

示例： 获取下一行数据到声明的变量中。 


DECLARE @CustID INT, @CustName NVARCHAR(100), @OrderAmt DECIMAL(18, 2);



-- ... 声明并打开游标 ...



FETCH NEXT FROM MySampleCursor INTO @CustID, @CustName, @OrderAmt;

4. 循环获取和处理数据

通常，我们会在一个循环中重复执行FETCH语句，直到没有更多数据可获取。在SQL Server中，可以使用@@FETCH_STATUS系统函数来检查FETCH操作的结果。

  • @@FETCH_STATUS = 0: FETCH操作成功。
  • @@FETCH_STATUS = -1: FETCH操作失败或行被删除。
  • @@FETCH_STATUS = -2: 被获取的行不在结果集中（例如，在使用动态游标时）。对于FORWARD_ONLY游标，-1或-2都表示没有更多行。

使用WHILE循环是常见的模式： 


-- ... 声明并打开游标 ...



-- 第一次获取数据，进入循环前检查

FETCH NEXT FROM MySampleCursor INTO @CustID, @CustName, @OrderAmt;



-- 循环，直到FETCH失败或没有更多行

WHILE @@FETCH_STATUS = 0

BEGIN

    -- 在这里编写处理当前行 (@CustID, @CustName, @OrderAmt) 的逻辑

    PRINT 'Processing Customer: ' + CAST(@CustID AS NVARCHAR) + ' - ' + @CustName + ', Amount: ' + CAST(@OrderAmt AS NVARCHAR);

    

    -- 获取下一行数据

    FETCH NEXT FROM MySampleCursor INTO @CustID, @CustName, @OrderAmt;

END;

5. 关闭游标 (CLOSE CURSOR)

处理完所有数据或提前退出循环后，应该使用CLOSE语句关闭游标。这会释放当前持有的锁和资源，使游标可以被重新打开以获取新的结果集（如果需要）。 


CLOSE cursor_name;

示例： 


CLOSE MySampleCursor;

6. 释放游标 (DEALLOCATE CURSOR)

一旦你确定不再需要游标，就应该使用DEALLOCATE语句来彻底销毁游标定义并释放所有相关的系统资源。 


DEALLOCATE cursor_name;

示例： 


DEALLOCATE MySampleCursor;

注意： CLOSE和DEALLOCATE是两个不同的操作。CLOSE只是释放了结果集和锁，而DEALLOCATE是销毁了游标对象本身。通常，在游标使用完毕后，两者都应该执行，并且DEALLOCATE必须在CLOSE之后执行。

完整的SQL Server游标使用示例


-- 声明变量用于存储获取的数据

DECLARE @CustID INT, @CustName NVARCHAR(100), @OrderAmt DECIMAL(18, 2);



-- 声明游标

DECLARE MySampleCursor CURSOR

FAST_FORWARD FOR

SELECT CustomerID, CustomerName, OrderAmount

FROM Orders

WHERE OrderDate >= '2023-01-01'

ORDER BY CustomerID;



-- 打开游标

OPEN MySampleCursor;



-- 获取第一行数据

FETCH NEXT FROM MySampleCursor INTO @CustID, @CustName, @OrderAmt;



-- 循环处理数据，直到没有更多行

WHILE @@FETCH_STATUS = 0

BEGIN

    -- *** 在这里添加你的逐行处理逻辑 ***

    PRINT '处理客户: ' + CAST(@CustID AS NVARCHAR) + ', 姓名: ' + @CustName + ', 订单金额: ' + CAST(@OrderAmt AS NVARCHAR);

    

    -- 假设在这里可能进行复杂的业务操作，例如更新其他表或调用外部服务

    -- UPDATE CustomerStats SET TotalOrders = TotalOrders + 1 WHERE CustomerID = @CustID;

    -- 执行一个存储过程： EXEC SomeComplexProcess @CustID, @OrderAmt;

    

    -- 获取下一行数据

    FETCH NEXT FROM MySampleCursor INTO @CustID, @CustName, @OrderAmt;

END;



-- 关闭游标

CLOSE MySampleCursor;



-- 释放游标资源

DEALLOCATE MySampleCursor;

其他数据库系统（如Oracle、PostgreSQL）中的游标

虽然基本概念和步骤相似，但不同数据库系统的游标语法会有所不同。

  • Oracle (PL/SQL): 使用CURSOR cursor_name IS select_statement;声明，OPEN cursor_name;打开，FETCH cursor_name INTO variable_list;获取，使用LOOP ... END LOOP;或FOR ... IN ... LOOP ... END LOOP;循环，CLOSE cursor_name;关闭。Oracle的FOR循环隐式处理游标的打开、获取和关闭，非常方便。
  • PostgreSQL (PL/pgSQL): 使用DECLARE cursor_name CURSOR FOR select_statement;声明，OPEN cursor_name;打开，FETCH [direction] FROM cursor_name INTO variable_list;获取，使用LOOP ... END LOOP;结合FETCH和EXIT WHEN ...，CLOSE cursor_name;关闭。

在使用特定数据库时，请查阅其官方文档以获取准确的语法和最佳实践。

使用Cursor会占用多少资源？

数据库游标通常比集合操作消耗更多的系统资源，主要包括：

  • 内存： 特别是静态游标，需要在服务器内存或tempdb数据库中存储结果集的副本。结果集越大，占用的内存/tempdb空间就越多。
  • tempdb资源： 静态游标、键集驱动游标以及某些类型的动态游标可能需要在tempdb中创建工作表来存储结果集或键集。这会增加tempdb的I/O负载和空间使用。
  • 锁： 游标可能会在处理行时持有锁，特别是在进行更新操作时。如果游标处理时间长或涉及大量行，这些锁可能会阻塞其他会话，导致并发性问题。例如，SCROLL_LOCKS选项会确保在获取行时就加锁。
  • CPU开销： 逐行处理的本质导致数据库需要执行更多的操作（FETCH、检查状态、变量赋值等）来处理相同数量的数据，这通常比一次性处理整个集合的开销要高。
  • 网络开销： 在某些客户端-服务器架构中，使用服务器端游标（而不是在客户端获取所有数据）可以减少初始的网络传输量，但逐行获取数据仍然可能产生多次小的网络往返。

由于这些开销，长时间运行或处理大量数据的游标可能会显著影响数据库服务器的性能和并发性。

有没有什么替代Cursor的方法？

绝对有，并且在大多数情况下，替代方法是更优的选择。数据库的核心优势在于高效地处理数据集合。你应该优先考虑使用基于集合的操作来替代游标。

常见的Cursor替代方法包括：

  • 集合操作 (Set-Based Operations):
    • 使用UPDATE ... FROM ... JOIN ...语句一次性更新多行。
    • 使用INSERT ... SELECT ...语句一次性插入多行。
    • 使用MERGE语句进行插入、更新、删除的合并操作。
    • 利用WHERE子句、GROUP BY子句、聚合函数、JOIN等标准SQL特性来处理数据。

    这是最推荐的替代方法。 数据库查询优化器专门为集合操作进行了优化，效率远高于逐行处理。

  • 临时表 (Temporary Tables) 或表变量 (Table Variables):
    • 将需要处理的数据一次性插入到一个临时表或表变量中。
    • 然后，可以对这个临时表或表变量执行高效的集合操作。
    • 这适用于需要对中间结果进行多次操作的情况。
  • 公用表表达式 (CTE – Common Table Expressions):
    • 使用CTE来组织复杂的查询逻辑，将分步的逻辑组合在一个查询中，通常可以避免游标。
  • 窗口函数 (Window Functions):
    • 对于需要访问当前行以及与之相关的相邻行或一组行的场景（如排名、累计求和、计算差值等），窗口函数（如ROW_NUMBER(), LAG(), LEAD(), SUM() OVER(...)）可以非常高效地完成，而无需使用游标。
  • 批处理 (Batch Processing):
    • 如果必须逐行处理，可以考虑将任务分解成更小的批次。例如，在一个循环中，每次处理1000行，使用TOP或分页技术（如OFFSET/FETCH或基于主键的范围）来获取下一批数据。这样可以减少单个游标的开销和锁的持有时间。

在编写代码之前，花时间思考是否可以将业务逻辑转化为基于集合的操作。通常是可以的，并且这样做会带来显著的性能提升。

使用Cursor有哪些注意事项？

如果经过仔细评估，确实需要使用游标，那么请注意以下事项以尽量减少其负面影响：

  • 最小化游标的生存周期和范围： 只在需要时声明和打开游标，并在不再需要时立即关闭和释放它。避免在整个存储过程或批处理中保持游标打开状态。
  • 选择合适的游标类型： 如果只需要向前遍历且不需要看到其他会话的修改，使用FAST_FORWARD或FORWARD_ONLY游标。这是效率最高的类型。避免使用DYNAMIC或SCROLL游标，除非绝对必要，因为它们开销更大。
  • 只获取需要的列： SELECT语句中只包含你需要处理的列，避免使用SELECT *。这可以减少内存和tempdb的使用。
  • 处理结果集尽量小： 如果可能，在SELECT语句中使用WHERE子句过滤掉不需要的行，减少游标需要处理的数据量。
  • 谨慎进行修改操作： 如果需要通过游标更新或删除数据，使用WHERE CURRENT OF cursor_name语法（如果支持且合适）可以更直接地操作当前行。但要注意锁的问题。考虑在循环内部定期提交事务，以避免长时间持有锁，但也要注意数据一致性问题。
  • 错误处理： 在存储过程或脚本中使用TRY...CATCH块来捕获错误，并在发生错误时确保游标被关闭和释放，避免资源泄露。
  • 性能监控： 如果你使用了游标，务必监控其对数据库性能的影响，包括CPU、内存、I/O和锁等待。

总结

数据库游标提供了一种逐行处理结果集的能力，这在某些特定且复杂的场景下是必需的。然而，由于其固有的性能开销和资源消耗，游标通常被认为是数据库编程中的最后手段。在大多数情况下，优先考虑使用更高效、基于集合的SQL操作、临时表/表变量、CTE或窗口函数来解决问题。
当你确实需要使用游标时，务必理解其工作原理，选择最合适的类型，并遵循最佳实践来最小化其对系统性能的影响。


cursor怎么用