妙用MSSQL游标：25字窍门分享-猿码集

1. 游标介绍

游标是一种可以逐个处理记录的机制，通常应用于存储过程或函数中。在MSSQL中，游标可以使用DECLARE CURSOR语句定义，可以控制游标的方向和位置，可以通过FETCH NEXT和FETCH PRIOR等命令遍历查询结果集中的每一行。

游标与集合之间的区别在于，集合一次性返回所有结果，而游标则是一次返回一条记录，可以通过游标将查询结果集当做一个单独的表来处理。

2. 游标的应用场景

游标通常被用于需要逐行处理记录的场景中，例如需要分批处理大量数据，需要递归处理树结构数据等。

2.1 分批处理大量数据

当需要处理大量数据时，为了避免系统资源耗尽或操作时间过长，可以使用游标逐行处理记录。下面是一个使用游标进行分批处理数据的例子：


DECLARE @pageSize int = 100 --每批次处理行数
DECLARE @pageIndex int = 1  --当前批次号
DECLARE @totalRows int     --总行数
DECLARE @startRow int      --起始行号
DECLARE @endRow int        --结束行号
DECLARE @id int         --当前记录的ID
DECLARE @name varchar(50) --当前记录的名称
DECLARE cur CURSOR FOR 
SELECT id, name FROM [Table] ORDER BY id
SELECT @totalRows = COUNT(1) FROM [Table]
WHILE @pageIndex <= CEILING(@totalRows/@pageSize)
BEGIN
    SET @startRow = (@pageIndex - 1) * @pageSize + 1
    SET @endRow = @startRow + @pageSize - 1
    
    OPEN cur
    FETCH ABSOLUTE @startRow FROM cur INTO @id, @name
    
    WHILE @@FETCH_STATUS = 0 AND @startRow <= @endRow
    BEGIN
        /* 
            处理逻辑
        */
        SET @startRow += 1
        FETCH NEXT FROM cur INTO @id, @name
    END
    
    CLOSE cur
    DEALLOCATE cur
    
    SET @pageIndex += 1
END

上述代码会将表中的记录分批次（每批次100条）进行处理，这样可以有效避免处理过程中系统资源耗尽或操作时间过长。

2.2 递归处理树结构数据

树形结构通常被用于表示具有分层关系的数据，例如组织机构、分类目录等。在处理树形数据时，常常需要使用递归算法，而使用游标可以实现递归算法中的迭代过程。

下面是一个使用游标递归处理树形数据的例子：


CREATE TABLE Tree (
    id int PRIMARY KEY,
    parentId int NULL,
    name varchar(50)
)
INSERT INTO Tree VALUES (1, NULL, 'A')
INSERT INTO Tree VALUES (2, 1, 'B1')
INSERT INTO Tree VALUES (3, 1, 'B2')
INSERT INTO Tree VALUES (4, 2, 'C1')
INSERT INTO Tree VALUES (5, 2, 'C2')
INSERT INTO Tree VALUES (6, 3, 'C3')
INSERT INTO Tree VALUES (7, 3, 'C4')
DECLARE @parentId int = 1
DECLARE @parentId_list varchar(100) = '1'
DECLARE cur CURSOR FOR 
SELECT id, parentId, name FROM Tree WHERE parentId = @parentId
DECLARE @id int         --当前记录的ID
DECLARE @subName varchar(50) --当前记录的名称
DECLARE @subParentId_list varchar(100) --当前记录的父ID列表
OPEN cur
FETCH NEXT FROM cur INTO @id, @parentId, @subName
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @subParentId_list = @parentId_list + ',' + CAST(@id AS varchar)
    /* 
        处理逻辑
     */
    EXEC dbo.recursiveTree @id, @subParentId_list
    
    FETCH NEXT FROM cur INTO @id, @parentId, @subName
END
CLOSE cur
DEALLOCATE cur

上述代码会遍历树形结构，将每一个子节点当做一个父节点进行递归处理，并将其父节点的ID拼接到一个ID列表中。这种方法可以避免使用递归算法处理树结构时造成的性能问题。

3. 游标的注意事项

使用游标需要注意以下问题：

3.1 使用FETCH NEXT命令时要判断

使用FETCH NEXT命令遍历记录时，每次需要先使用IF @@FETCH_STATUS = 0判断查询是否还有下一条记录。例如：


DECLARE cur CURSOR FOR 
SELECT id FROM [Table] ORDER BY id
DECLARE @id int
OPEN cur
FETCH NEXT FROM cur INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
    /* 处理逻辑 */
    
    FETCH NEXT FROM cur INTO @id
END
CLOSE cur
DEALLOCATE cur

3.2 DECLARE CURSOR语句中使用的查询语句不要带FOR UPDATE/OPTIMISTIC

DECLARE CURSOR语句中使用的查询语句不要带FOR UPDATE/OPTIMISTIC，因为在实际操作中会引起死锁的情况。

3.3 及时关闭游标

游标使用完毕后一定要及时关闭和释放，否则会占用系统资源。可以使用CLOSE和DEALLOCATE命令关闭和释放游标：


DECLARE cur CURSOR FOR 
SELECT id FROM [Table] ORDER BY id
/* do something */
CLOSE cur
DEALLOCATE cur

3.4 避免过多使用游标

过多使用游标会导致性能问题，并且在处理大量数据时容易引起系统崩溃。因此，在应用中应尽量避免过多使用游标。

4. 总结

游标是一种逐行处理记录的机制，通常应用于需要逐个处理记录的场景中。使用游标可以实现分批处理大量数据、递归处理树形结构数据等操作。然而，过多使用游标会导致性能问题和系统崩溃，因此在应用中应尽量避免过多使用游标。

妙用MSSQL游标：25字窍门分享