为什么我的光标停在循环的中间？

Question

这里发布的代码是“示例”代码，不是生产代码。我这样做是为了使我正在解释的问题变得可读性/简洁性。

使用与下面类似的代码，我们遇到了一个奇怪的bug。每次插入后，会停止WHILE循环。

表包含100行，当插入在50行之后完成时，游标停止，只触及前50行。当插入在55之后完成时，它在55之后停止，依此类推。

-- This code is an hypothetical example written to express
-- an problem seen in production

DECLARE @v1 int
DECLARE @v2 int

DECLARE MyCursor CURSOR FAST_FORWARD FOR
SELECT Col1, Col2
FROM table

OPEN MyCursor

FETCH NEXT FROM MyCursor INTO @v1, @v2

WHILE(@@FETCH_STATUS=0)
BEGIN

  IF(@v1>10)
  BEGIN
    INSERT INTO table2(col1) VALUES (@v2)
  END

  FETCH NEXT FROM MyCursor INTO @v1, @v2

END

CLOSE MyCursor
DEALLOCATE MyCursor

table2上有一个AFTER INSERT触发器，用于将table2上的变异记录到第三个表中，并适当地命名为突变。这包含一个游标，插入以处理插入(每列以非常特定的方式记录突变，这需要游标)。

一些背景:这存在于一组小的支持表中。为了审核目的，项目需要记录对源数据所做的每一项更改。有日志记录的表格包含诸如银行帐号之类的东西，其中将存放大量的钱。最多有几千条记录，它们只应该很少被修改。审计功能是为了阻止欺诈:当我们用“谁做了它”来记录“改变了什么”时。

实现这一点的明显、快速和合乎逻辑的方法是，每次更新时存储整个行。这样我们就不需要光标了，它的性能会更好。然而，这种情况的政治意味着我束手无策。

呼。现在回到问题上。

触发器的简化版本(实版本每列执行一次插入，并插入旧值)：

--This cursor is an hypothetical cursor written to express
--an problem seen in production.

--On UPDATE a new record must be added to table Mutaties for
--every row in every column in the database.  This is required
--for auditing purposes.

--An set-based approach which stores the previous state of the row
--is expressly forbidden by the customer


DECLARE @col1 int
DECLARE @col2 int
DECLARE @col1_old int
DECLARE @col2_old int

--Loop through old values next to new values
DECLARE MyTriggerCursor CURSOR FAST_FORWARD FOR
SELECT i.col1, i.col2, d.col1 as col1_old, d.col2 as col2_old
FROM Inserted i
  INNER JOIN Deleted d ON i.id=d.id

OPEN MyTriggerCursor 

FETCH NEXT FROM MyTriggerCursor INTO @col1, @col2, @col1_old, @col2_old

--Loop through all rows which were updated
WHILE(@@FETCH_STATUS=0)
BEGIN

    --In production code a few more details are logged, such as userid, times etc etc

    --First column
    INSERT Mutaties (tablename, columnname, newvalue, oldvalue)
    VALUES ('table2', 'col1', @col1, @col1_old)

    --Second column
    INSERT Mutaties (tablename, columnname, newvalue, oldvalue)
    VALUES ('table2', 'col2', @col2, @col1_old)

    FETCH NEXT FROM MyTriggerCursor INTO @col1, @col2, @col1_old, @col2_old

END

CLOSE MyTriggerCursor
DEALLOCATE MyTriggerCursor

为什么代码存在于循环的中间？

Answer 1

瑞安，您的问题是@@FETCH_STATUS对于连接中的所有游标都是全局的。

因此触发器内的游标以-1的@@FETCH_STATUS结束。当控件返回到上面的代码时，最后一个@@FETCH_STATUS是-1，因此光标结束。

在MSDN 这里上可以找到的文档中解释了这一点。

您可以做的是使用局部变量来存储@@FETCH_STATUS，并将该局部变量放入循环中。所以你得到了这样的东西：

DECLARE @v1 int
DECLARE @v2 int
DECLARE @FetchStatus int

DECLARE MyCursor CURSOR FAST_FORWARD FOR
SELECT Col1, Col2
FROM table

OPEN MyCursor

FETCH NEXT FROM MyCursor INTO @v1, @v2

SET @FetchStatus = @@FETCH_STATUS

WHILE(@FetchStatus=0)
BEGIN

  IF(@v1>10)
  BEGIN
    INSERT INTO table2(col1) VALUES (@v2)
  END

  FETCH NEXT FROM MyCursor INTO @v1, @v2

  SET @FetchStatus = @@FETCH_STATUS

END

CLOSE MyCursor
DEALLOCATE MyCursor

值得注意的是，这种行为不适用于嵌套游标。我做了一个简单的例子，在SqlServer 2008上返回预期的结果(50)。

USE AdventureWorks
GO

DECLARE @LocationId smallint
DECLARE @ProductId smallint

DECLARE @Counter int
SET @Counter=0

DECLARE MyFirstCursor CURSOR FOR 
SELECT TOP 10 LocationId
FROM Production.Location

OPEN MyFirstCursor

FETCH NEXT FROM MyFirstCursor INTO @LocationId

WHILE (@@FETCH_STATUS=0)
BEGIN

    DECLARE MySecondCursor CURSOR FOR
    SELECT TOP 5 ProductID
    FROM Production.Product

    OPEN MySecondCursor

    FETCH NEXT FROM MySecondCursor INTO @ProductId

    WHILE(@@FETCH_STATUS=0)
    BEGIN

        SET @Counter=@Counter+1

        FETCH NEXT FROM MySecondCursor INTO @ProductId  

    END

    CLOSE MySecondCursor
    DEALLOCATE MySecondCursor

    FETCH NEXT FROM MyFirstCursor INTO @LocationId

END

CLOSE MyFirstCursor
DEALLOCATE MyFirstCursor

--
--Against the initial version of AdventureWorks, counter should be 50.
--
IF(@Counter=50)
    PRINT 'All is good with the world'
ELSE
    PRINT 'Something''s wrong with the world today'

Answer 2

您的问题是，您不应该使用游标在这一点！这是上面给出的例子的代码。

INSERT INTO table2(col1)
SELECT Col1 FROM table
where col1>10

您也不应该在触发器中使用游标，这会降低性能。如果有人在一次插入中添加了100,000行，这可能需要几分钟(甚至几个小时)，而不是毫秒或秒。我们在这里替换了一个(在我开始这个工作之前)，并将导入从40分钟减少到45秒。

任何使用游标的生产代码都应该检查，以便用正确的基于集合的代码替换它。根据我的经验，所有游标的90+%都可以以一种基于集合的方式重新编写。

Answer 3

这是对触发器的简单误解..。你根本不需要游标

if UPDATE(Col1)
begin

    insert into mutaties
    (
        tablename, 
        columnname, 
        newvalue
    )
    select
    'table2',
    coalesce(d.Col1,''),
    coalesce(i.Col1,''),
    getdate()
    from inserted i
        join deleted d on i.ID=d.ID
            and coalesce(d.Col1,-666)<>coalesce(i.Col1,-666)

end

基本上，这段代码所做的就是检查该列的数据是否被更新。如果是的话，它会比较新的和旧的数据，如果是不同的，就会插入到日志表中。

您的第一个代码示例可以很容易地被下面这样的代码替换

insert into table2 (col1)
select Col2
from table
where Col1>10