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

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

Answer 2

我用 WHILE 循环替换了一些游标。

DECLARE @SomeTable TABLE
(
     ID int IDENTITY (1, 1) PRIMARY KEY NOT NULL,
     SomeNumber int,
     SomeText varchar
)

DECLARE @theCount int
DECLARE @theMax int

DECLARE @theNumber int
DECLARE @theText varchar

INSERT INTO @SomeTable (SomeNumber, SomeText)
SELECT Number, Text
FROM PrimaryTable

SET @theCount = 1
SELECT @theMax = COUNT(ID) FROM @SomeTable

WHILE (@theCount <= @theMax)
BEGIN

     SET @theNumber = 0
     SET @theText = ''

     SELECT @theNumber = IsNull(Number, 0), @theText = IsNull(Text, 'nothing')
     FROM @SomeTable
     WHERE ID = @theCount

     -- Do something.
     PRINT 'This is ' + @theText + ' from record ' + CAST(@theNumber AS varchar) + '.'

     SET @theCount = @theCount + 1

END

PRINT 'Done'

Answer 3

好吧，习惯了过程式编程的应用程序开发人员通常会出于习惯而尝试以过程方式完成所有事情，即使是在 SQL 中也是如此。

大多数情况下，具有正确参数的 SELECT 可能会这样做 - 或者也许您正在处理 UPDATE 语句。

真正的要点是：您需要开始思考集合操作并告诉 RDBMS 您想要做什么 - 而不是如何一步一步地执行。

很难给出一个单一的、“正确”的答案......你几乎必须用一个具体的例子来展示它。

马克

Answer 4

我编写了一些代码来计算与给定年份相关的财务数据的运行总计。 在每个季度中，我必须将当前季度的值添加到运行总计中，同时适当处理 NULL，以便当当前季度的值为 NULL 时结转上一季度的运行总计。

最初，我使用游标来完成此操作，从功能角度来看，这满足了业务需求。 从技术角度来看，它被证明是一个令人震惊的事情，因为随着数据量的增加，代码花费的时间呈指数级增长。 解决方案是用相关子查询替换游标，这样既能满足功能需求，又能消除任何性能问题。

希望这有帮助，

比尔

Answer 5

当（看看我在那里做了什么）问题被提出并回答时，我想添加我的回答，以防它对其他人有帮助。 与许多其他人一样，我正在转换为 while 语句，但我想利用数据集中现有的主键，而不是计算结果集的大小。 我想出了这个模式，效果很好。 我对其他人和改进持开放态度。 谢谢！

declare @PrimaryKey int
/* get first record for looping */
select @PrimaryKey = min(PrimaryKey) from Table
/* it will be null when all records have iterated */
while (@PrimaryKey is not null)
  begin        
    /* do stuff */
    
    /* iterate to next key */
    select @PrimaryKey = min(PrimaryKey) from Table where PrimaryKey > @PrimaryKey
  end