新手的光标替换

视情况而定™

围绕一个或多个游标工作的能力将取决于该游标内部要执行的内容。不知道里面发生了什么，就没有办法说出来。可能没有解决方法，您必须逐行处理。

下面是一些例子。

不成套工作

此示例是最基本的示例，您可以一次查询整个数据集或部分数据集，但创建了游标并逐行查询数据。常用的替换为JOIN's, CROSS APPLY/OUTER APPLY和其他。

考虑以下数据集：

CREATE TABLE dbo.Lotr(LotrId int, CharacterName varchar(255), Val varchar(255));
CREATE TABLE dbo.LotrAttributes(LotrATtributeId int, LotrId int, AttrVal varchar(255));

INSERT INTO dbo.Lotr(LotrId,CharacterName,Val)
VALUES(1,'Frodo','Ring')
,(2,'Gandalf','Staff');

INSERT INTO dbo.LotrAttributes(LotrId,LotrATtributeId,AttrVal)
VALUES(1,1,'RingAttribute1')
,(1,2,'RingAttribute2')
,(1,3,'RingAttribute3')
,(2,4,'StaffAttribute1')
,(2,5,'StaffAttribute2');

Lotr可以通过循环遍历表来尝试查找每条记录并分别匹配。

光标：

DECLARE @LotrID int
DECLARE C CURSOR FOR SELECT LotrId from dbo.Lotr;
OPEN C
FETCH NEXT FROM C INTO @LotrID;
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT LotrATtributeId from dbo.LotrAttributes where LotrId = @LotrID;
FETCH NEXT FROM C INTO @LotrID;
END
CLOSE C
DEALLOCATE C

产生两个结果集

LotrATtributeId
1
2
3
LotrATtributeId
4
5

使用它时inner join，我们得到与一个结果集相同的结果。

SELECT LotrATtributeId from dbo.Lotr L
INNER JOIN dbo.LotrAttributes LA 
ON L.LotrId = LA.LotrId;

LotrATtributeId
1
2
3
4
5

字符串操作

一种常见的方法是用于FOR XML PATH('')替换游标内的字符串操作。

数据集

CREATE TABLE dbo.Lotr(LotrId int, CharacterName varchar(255), Val varchar(255));
CREATE TABLE dbo.LotrAttributes(LotrATtributeId int, LotrId int, AttrVal varchar(255));

INSERT INTO dbo.Lotr(LotrId,CharacterName,Val)
VALUES(1,'Frodo','Ring');

INSERT INTO dbo.LotrAttributes(LotrId,LotrATtributeId,AttrVal)
VALUES(1,1,'RingAttribute1')
,(1,2,'RingAttribute2')
,(1,3,'RingAttribute3');

带字符串操作的双光标

DECLARE @LotrId int, @CharacterName varchar(255), @Val varchar(255)
DECLARE @LotrATtributeId int, @AttrVal varchar(255)
DECLARE C CURSOR FOR
SELECT LotrId,CharacterName, Val FROM dbo.Lotr
OPEN C
FETCH NEXT FROM C INTO @LotrId,@CharacterName,@Val
WHILE @@FETCH_STATUS = 0
BEGIN

        SET @CharacterName +='|'+ @Val

        DECLARE D CURSOR FOR
        SELECT LotrATtributeId, AttrVal FROM dbo.LotrAttributes where LotrId = @LotrId
        OPEN D
        FETCH NEXT FROM D INTO @LotrATtributeId,@AttrVal
        WHILE @@FETCH_STATUS = 0
        BEGIN
        SET @CharacterName +='['+@AttrVal+ '],'

        FETCH NEXT FROM D INTO @LotrATtributeId,@AttrVal
        END
        CLOSE D 
        DEALLOCATE D

FETCH NEXT FROM C INTO @LotrId,@CharacterName,@Val
END
CLOSE C
DEALLOCATE C
SELECT LEFT(@CharacterName,len(@charactername)-1);

结果

(No column name)
Frodo|Ring[RingAttribute1],[RingAttribute2],[RingAttribute3],

使用 FOR XML PATH('') 删除游标

SELECT L.Charactername +'|'+ L.Val + (SELECT stuff((SELECT ','+QUOTENAME(AttrVal) FROM dbo.LotrAttributes LA WHERE LA.LotrId = L.LotrId FOR XML PATH('')), 1, 1, ''))
FROM
dbo.Lotr L;

*

真正的解决方法是弄清楚为什么以这种方式呈现数据，并更改应用程序/...以使其不需要这种格式，将其存储在某处，...

如果你的双手被束缚，这将是下一个最好的事情。

根据另一个表中的 Id 将前 10 个值插入到临时表中

数据

创建表 dbo.sometable(InsertTableId int, val varchar(255)); 创建表 dbo.Top10Table(Top10TableId int, InsertTableId int, val varchar(255));

INSERT INTO dbo.sometable(InsertTableId,val)
VALUES(1,'bla')
,(2,'blabla');
INSERT INTO dbo.Top10Table(Top10TableId,InsertTableId,Val)
VALUES(1,1,'WUW')
,(2,1,'WUW')
,(3,1,'WUW');

光标

CREATE TABLE #Top10Values(Top10TableId int, InsertTableId int, val varchar(255))

    DECLARE @InsertTableId int;
    DECLARE C CURSOR FOR select InsertTableId from dbo.sometable;
    OPEN C
    FETCH NEXT FROM C INTO @InsertTableId;
    WHILE @@FETCH_STATUS =0
    BEGIN
    INSERT INTO #Top10Values(Top10TableId,InsertTableId,val)
    SELECT top(10) Top10TableId,InsertTableId,Val FROM dbo.Top10Table 
    where InsertTableId = @InsertTableId
    ORDER BY Top10TableId 

    FETCH NEXT FROM C INTO @InsertTableId;
    END
    CLOSE C
    DEALLOCATE C

    SELECT * FROM  #Top10Values;
    DROP TABLE #Top10Values;

结果

Top10TableId    InsertTableId   val
1   1   WUW
2   1   WUW
3   1   WUW

CROSS APPLY用和替换光标CTE

CREATE TABLE #Top10Values(Top10TableId int, InsertTableId int, val varchar(255));
;WITH CTE 
AS
(
select InsertTableId  from dbo.sometable
)

INSERT INTO #Top10Values(Top10TableId,InsertTableId,val)
SELECT  T1T.Top10TableId,T1T.InsertTableId,T1T.Val 
FROM 
CTE
CROSS APPLY (SELECT TOP (10) Top10TableId,InsertTableId,Val from dbo.Top10Table T1T
WHERE T1T.InsertTableId = CTE.InsertTableId
) T1T ;

SELECT * FROM  #Top10Values;
DROP TABLE #Top10Values;

其他示例

• 使用CROSS APPLY 此处替换光标以选择每个供应商的动态项目集的示例。
• 此处是使用窗口函数替换光标的示例。

有时别无选择

如果您不能在集合中工作，并且必须逐行处理，您仍然可以优化游标。

加速光标的最大变化之一是添加LOCAL FAST_FORWARD它。

DECLARE C CURSOR LOCAL FAST_FORWARD FOR SELECT LotrId from dbo.Lotr

看看@AaronBertrand 的这篇博文，他解释了使用或不使用LOCAL&之类的光标设置时性能可能存在的差异FAST_FORWARD。

没有“通用替换”——你在这里隐藏了所有“肮脏的工作”，所以很难判断在这种情况下是否有特定的替换。在某些特定情况下，您一次处理一组行，无论是使用游标、while 循环还是任何其他迭代过程，在这些情况下转换为处理所有行的基于集合的过程一次会好很多。但是还有其他事情只需要一次完成一行，例如执行存储过程或每行一些动态 SQL，跨多个数据库的相同查询等。

游标与否，无论您使用声明游标还是其他一些循环结构（请参阅这篇文章），您所暗示和链接的问题都是相同的，并且当您必须做的事情必须在一行中完成时无关紧要反正时间。因此，如果您提供有关此光标正在做什么的一些具体细节，您可能会得到一些关于如何删除光标（或者您不能）的建议，但是您可以寻找一种可以应用的神奇消除所有光标的方法所有场景都会让你非常沮丧。

恕我直言，对于进入该语言的新人的一般建议应该是始终考虑您需要对一组行做什么，而不是对一组中的每一行做什么。语言上的差异是微妙的，但至关重要。如果人们将问题视为一组数据而不是一堆单独的行，那么他们可能不太可能默认使用游标。但是，如果他们来自不同类型的编程——迭代是最好/唯一的方式——除了简单地教他们 SQL Server 没有针对这种方式进行优化之外，我不知道有什么方法可以使这一点变得明显或自动的。

您的问题仍然要求进行一般更换，我仍然认为没有这样的事情。

Doug Lane 在 YouTube 上制作了一系列名为“T-SQL Level Up”的视频。该系列的一部分探讨了删除光标的一般方法，如下所示：

• 移除所有游标语言（declare cursor、open、fetch、while、close、deallocate 等）和其他变量声明
• 确定可以组合基于集合的操作的位置（例如SELECT，稍后在 an 中使用的由 a 填充的变量INSERT可能会被INSERT INTO...SELECT语句替换）
• 将条件逻辑 ( IF...ELSE) 移动到WHERE子句、CASE语句、子查询等中

正如此处其他出色的答案所指出的那样，对此没有灵丹妙药。但在我看来，这些视频是解决问题的一种非常直观的方法。

Doug 在每个部分都经历了三个越来越复杂的光标替换，我强烈建议您观看（因为整个交易在视频中表现得更好）：

• T-SQL 升级第 6 章替换游标第 1 部分
• T-SQL 升级第 6 章替换游标第 2 部分
• T-SQL 升级第 6 章替换游标第 3 部分