如何在 CURSOR 循环内提交？

Question

我试图看看是否可以在游标循环中执行更新，并且更新的数据在循环的第二次迭代期间得到反映。

DECLARE cur CURSOR
FOR SELECT [Product], [Customer], [Date], [Event] FROM MyTable
WHERE [Event] IS NULL

OPEN cur
FETCH NEXT INTO @Product, @Customer, @Date, @Event
WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT * FROM MyTable WHERE [Event] = 'No Event' AND [Date] < @DATE
  -- Now I update my Event value to 'No Event' for records whose date is less than @Date
  UPDATE MyTable SET [Event] = 'No Event' WHERE [Product] = @Product AND [Customer] = @Customer AND [Date] < @DATE
   FETCH NEXT INTO @Product, @Customer, @Date, @Event
END
CLOSE cur
DEALLOCATE cur

假设当sql执行时所有记录的Event列都是NULL 在上面的 sql 中，我在游标循环内执行选择来查询 MyTable，其中事件值为“无事件”，但即使我在下一行中进行更新，查询也不会返回任何值。 因此，我在想是否有可能更新表并且更新的数据会反映在游标循环的下一次迭代中。

感谢您的帮助， 贾维德

Answer 1

首先，这里不需要光标。类似下面的内容将具有相同的语义（从所有 Event 均为 NULL 的起始位置开始）并且效率更高。

WITH T
     AS (SELECT [Event],
                RANK() OVER (PARTITION BY [Product], [Customer] 
                                 ORDER BY [Date] DESC) AS Rnk
         FROM   MyTable)
UPDATE T
SET    [Event] = 'No Event'
WHERE  Rnk > 1 

其次，关于标题中要在游标循环内提交的问题与其他地方相同。您只需要一个 COMMIT 语句。但是，如果您不在较大的事务中运行此操作，则 UPDATE 语句无论如何都会自动提交。

第三，你真正的问题似乎与提交无关。它与反映后续迭代中数据更新的游标有关。对于问题中的情况，您需要 DYNAMIC 光标

定义一个游标，反映对行进行的所有数据更改
当您滚动光标时其结果集。数据值，
行的顺序和成员资格在每次获取时都可以更改。

并非所有查询都支持动态游标。问题中的代码可以，但如果没有 ORDER BY ，则无法确定行的处理顺序以及您是否会看到可见的结果。我添加了一个 ORDER BY 和一个索引来支持这一点，以允许使用动态游标。

如果您尝试以下操作，您将看到游标仅提取一行，因为日期按降序处理，并且当处理第一行时，表会更新，以便没有更多行符合下一次提取的条件。如果您注释掉游标循环内的UPDATE，则会获取所有三行。

CREATE TABLE MyTable
  (
     [Product]  INT,
     [Customer] INT,
     [Date]     DATETIME,
     [Event]    VARCHAR(10) NULL,
     PRIMARY KEY ([Date], [Product], [Customer])
  )


INSERT INTO MyTable 
VALUES (1,1,'20081201',NULL), 
       (1,1,'20081202',NULL), 
       (1,1,'20081203',NULL)

DECLARE @Product  INT,
        @Customer INT,
        @Date     DATETIME,
        @Event    VARCHAR(10)

DECLARE cur CURSOR DYNAMIC TYPE_WARNING FOR
  SELECT [Product],
         [Customer],
         [Date],
         [Event]
  FROM   MyTable
  WHERE  [Event] IS NULL
  ORDER  BY [Date] DESC

OPEN cur

FETCH NEXT FROM cur INTO @Product, @Customer, @Date, @Event

WHILE @@FETCH_STATUS = 0
  BEGIN
      SELECT @Product,
             @Customer,
             @Date,
             @Event

      -- Now I update my Event value to 'No Event' for records whose date is less than @Date
      UPDATE MyTable
      SET    [Event] = 'No Event'
      WHERE  [Product] = @Product
             AND [Customer] = @Customer
             AND [Date] < @Date

      FETCH NEXT FROM cur INTO @Product, @Customer, @Date, @Event
  END

CLOSE cur

DEALLOCATE cur

DROP TABLE MyTable 

Answer 2

即使这有效，也不能保证正确的结果，因为您在查询中错过了 ORDER BY 子句。

据此，可以更新所有记录、没有记录或记录的任何随机子集。

您能用简单的英语解释一下您的存储过程应该做什么吗？

Answer 3

使用下面的模板

DECLARE @CCount int = 100
    DECLARE @Count int = 0
    DECLARE  @id AS BigInt
    
        
    DECLARE cur Cursor fast_forward for 
        SELECT t1.Id  
             FROM Table1 t1 WITH (NOLOCK) WHERE  <Some where clause>
    OPEN cur
    Begin Tran
    
    While (1=1)
    Begin   
        Fetch next from cur into @id
        If @@Fetch_Status <> 0
            break
    -- do some DML actions
        Delete From Table1 WITH (ROWLOCK) where Id = @id
        Set @count = @count + @@Rowcount
    
        if (@count % @CCount = 0)
        Begin   
            if (@count % 100 = 0)
                Print 'Table1: DML action ' + Cast(@count as Varchar(15)) + ' rows'
    -- for every 100 rows commit tran will trigger , and starts a new one.
            While @@Trancount > 0 Commit Tran
            Begin Tran
        End
    End 
    
    While @@Trancount > 0 Commit Tran
    Close cur
    Deallocate cur