sql-server - 替换光标有哪些不同的方法？

Question

我想知道您在现有代码中替换 SQL Server 游标的经验，或者您如何解决程序人员使用游标来解决的问题，并基于集合进行解决。

光标用来解决什么问题？你是如何替换光标的？

Answer 1

尽量不要循环，只处理数据集。

您可以一次插入、更新、删除多行。这里是多行插入的示例:

INSERT INTO YourTable
        (col1, col2, col3, col4)
    SELECT
        cola, colb+Colz, colc, @X
        FROM ....
            LEFT OUTER JOIN ...
        WHERE...

当查看循环时，看看它在其中做了什么。如果只是插入/删除/更新，请重写为使用单个命令。如果存在 IF，请查看它们是否可以是插入/删除/更新时的 CASE 语句或 WHERE 条件。如果是这样，请删除循环并使用 set 命令。

我采用了循环并将其替换为基于集合的命令，并将执行时间从几分钟减少到几秒钟。我采用了具有许多嵌套循环和过程调用的过程，并保留了循环(不可能只使用插入/删除/更新)，但我删除了游标，并且看到了更少的锁定/阻塞和巨大的性能提升。这里有两种比光标循环更好的循环方法...

如果你必须循环，请在一组上执行如下操作:

--this looks up each row for every iteration
DECLARE @msg VARCHAR(250)
DECLARE @hostname sysname

--first select of currsor free loop
SELECT @hostname= min(RTRIM(hostname))
    FROM  master.dbo.sysprocesses (NOLOCK)
    WHERE  hostname <> ''

WHILE @hostname is not null
BEGIN
    set @msg='exec master.dbo.xp_cmdshell "net send ' 
        + RTRIM(@hostname) + ' '
        + 'testing  "'
    print @msg
    --EXEC (@msg)

    --next select of cursor free loop
    SELECT @hostname= min(RTRIM(hostname))
        FROM master.dbo.sysprocesses (NOLOCK)
        WHERE  hostname <> ''
        and hostname > @hostname
END

如果你有一组合理的项目(不是 100,000 个)要循环，你可以这样做:

--this will capture each Key to loop over
DECLARE @msg VARCHAR(250)
DECLARE @From   int
DECLARE @To     int
CREATE TABLE #Rows
(
     RowID     int not null primary key identity(1,1)
    ,hostname  varchar(100)
)

INSERT INTO #Rows
SELECT DISTINCT hostname
    FROM  master.dbo.sysprocesses (NOLOCK)
    WHERE  hostname <> ''
SELECT @From=0,@To=@@ROWCOUNT

WHILE @From<@To
BEGIN
    SET @From=@From+1

    SELECT @msg='exec master.dbo.xp_cmdshell "net send ' 
        + RTRIM(hostname) + ' '
        + 'testing  "'
        FROM #Rows WHERE RowID=@From
    print @msg
    --EXEC (@msg)
END