SQL Server中游标(Cursor)的使用方法及示例代码详解

在SQL Server中，游标（Cursor）是一种用于逐行处理查询结果集的数据库对象。虽然基于集合的SQL操作通常更高效，但在需要逐行处理数据的复杂业务逻辑中，游标仍具有不可替代的作用。本文将详细讲解游标的类型、生命周期管理、性能优化及典型应用场景。

一、游标核心概念与类型

1.1 游标是什么？

游标是存储在SQL Server内存中的临时工作区，用于：

• 逐行遍历查询结果集

• 维护当前行的位置状态

• 允许对单行数据进行修改或删除

1.2 游标类型对比

二、游标生命周期管理

2.1 完整操作流程

-- 1. 声明游标
DECLARE cursor_name CURSOR 
[LOCAL | GLOBAL]
[FORWARD_ONLY | SCROLL]
[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]
FOR select_statement
[FOR {READ ONLY | UPDATE [OF column_list]}]

-- 2. 打开游标
OPEN cursor_name

-- 3. 遍历数据
FETCH [NEXT | PRIOR | FIRST | LAST] FROM cursor_name

-- 4. 关闭游标
CLOSE cursor_name

-- 5. 释放资源
DEALLOCATE cursor_name

2.2 关键参数详解

• LOCAL/GLOBAL：控制游标作用域（默认GLOBAL）

• SCROLL：允许随机访问（需配合SCROLL游标类型）

• FAST_FORWARD：优化只进游标性能（等效于FORWARD_ONLY + READ_ONLY）

三、典型应用场景示例

3.1 基础遍历操作

-- 声明静态只读游标
DECLARE employee_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT EmployeeID, Name, Salary 
FROM Employees 
WHERE Department = 'Sales'

OPEN employee_cursor

DECLARE @id INT, @name NVARCHAR(50), @salary DECIMAL(10,2)
FETCH NEXT FROM employee_cursor INTO @id, @name, @salary

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'ID: ' + CAST(@id AS NVARCHAR) + 
          ', Name: ' + @name + 
          ', Salary: ' + CAST(@salary AS NVARCHAR)
    
    -- 业务逻辑处理...
    
    FETCH NEXT FROM employee_cursor INTO @id, @name, @salary
END

CLOSE employee_cursor
DEALLOCATE employee_cursor

3.2 更新数据示例

DECLARE order_cursor CURSOR LOCAL DYNAMIC FOR
SELECT OrderID, TotalAmount 
FROM Orders 
WHERE Status = 'Pending'
FOR UPDATE OF TotalAmount  -- 声明可更新列

OPEN order_cursor

DECLARE @order_id INT, @new_amount DECIMAL(10,2)
FETCH NEXT FROM order_cursor INTO @order_id, @new_amount

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 业务逻辑计算新金额（示例：增加10%服务费）
    SET @new_amount = @new_amount * 1.10
    
    -- 更新当前行
    UPDATE Orders
    SET TotalAmount = @new_amount
    WHERE CURRENT OF order_cursor  -- 关键语法：定位到当前行
    
    FETCH NEXT FROM order_cursor INTO @order_id, @new_amount
END

CLOSE order_cursor
DEALLOCATE order_cursor

3.3 删除数据示例

DECLARE audit_cursor CURSOR LOCAL STATIC FOR
SELECT AuditLogID 
FROM AuditLogs 
WHERE CreateDate < DATEADD(MONTH, -6, GETDATE())

OPEN audit_cursor

DECLARE @log_id INT
FETCH NEXT FROM audit_cursor INTO @log_id

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 执行删除操作
    DELETE FROM AuditLogs 
    WHERE AuditLogID = @log_id
    
    FETCH NEXT FROM audit_cursor INTO @log_id
END

CLOSE audit_cursor
DEALLOCATE audit_cursor

四、性能优化策略

4.1 游标性能杀手

• 长时间持有游标：保持游标打开状态会锁定资源

• 频繁重新编译：在循环内动态构建查询语句

• 大数据量处理：超过10万行时应考虑替代方案

4.2 优化技巧

1. 最小化结果集：

   -- 错误示范：返回所有列
   SELECT * FROM BigTable
   
   -- 正确做法：只选择必要列
   SELECT ID, ProcessFlag FROM BigTable

2. 使用FAST_FORWARD游标：

   DECLARE fast_cursor CURSOR LOCAL FAST_FORWARD FOR
   SELECT TOP 1000 ID FROM Orders

3. 分块处理（Batching）：

   DECLARE @batch_size INT = 1000
   DECLARE @offset INT = 0
   
   WHILE 1=1
   BEGIN
       DECLARE batch_cursor CURSOR LOCAL FAST_FORWARD FOR
       SELECT ID FROM Orders
       ORDER BY ID
       OFFSET @offset ROWS
       FETCH NEXT @batch_size ROWS ONLY
       
       -- 处理逻辑...
       
       CLOSE batch_cursor
       DEALLOCATE batch_cursor
       
       IF @@ROWCOUNT < @batch_size BREAK
       SET @offset += @batch_size
   END

五、替代方案建议

在以下场景应优先考虑替代方案：

1. 基于集合的操作：

   -- 替代游标逐行更新
   UPDATE Orders
   SET TotalAmount *= 1.10
   WHERE Status = 'Pending'

2. 窗口函数：

   -- 替代游标计算行号
   SELECT 
       ID,
       ROW_NUMBER() OVER(ORDER BY Salary DESC) AS RowNum
   FROM Employees

3. 临时表/表变量：

   -- 替代游标存储中间结果
   SELECT * INTO #TempResults
   FROM ComplexQuery

六、最佳实践总结

1. 三思而后用：确认没有更高效的集合操作方案后再使用游标

2. 控制作用域：优先使用LOCAL游标避免资源泄漏

3. 及时释放：在TRY...CATCH中确保DEALLOCATE执行

4. 监控性能：通过sys.dm_exec_cursors动态管理视图监控活动游标

-- 查询活动游标信息
SELECT 
    session_id,
    cursor_id,
    name,
    creation_time,
    is_open,
    rows_in_cursor
FROM sys.dm_exec_cursors(0)

游标是SQL Server中处理复杂行级逻辑的利器，但应视为最后手段。合理使用游标可以简化代码逻辑，而滥用则可能导致性能灾难。开发者需要深入理解其工作原理，在灵活性与性能之间找到最佳平衡点。