sql-server - 如何在 CURSOR 循环内提交？

Question

我正在尝试查看是否可以在游标循环中执行更新，并且此更新的数据在循环中的第二次迭代期间得到反射(reflect)。

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，其中事件值为“无事件”，但即使我在下一行进行更新，查询也不返回任何值。
所以，我在想是否有可能更新一个表，并且更新后的数据会反射(reflect)在游标循环的下一次迭代中。

谢谢你的帮助，
贾维德

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无论如何，语句将自动提交。

第三，您真正的问题似乎与提交无关。它是关于在后续迭代中反射(reflect)数据更新的游标。对于问题中的情况，您需要一个 DYNAMIC 光标

Defines a cursor that reflects all data changes made to the rows in its result set as you scroll around the cursor. The data values, order, and membership of the rows can change on each fetch.

并非所有查询都支持动态游标。问题中的代码将但没有 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