代码之家  ›  专栏  ›  技术社区  ›  Ryan Barrett

为什么我的光标停在中间?

  •  1
  • Ryan Barrett  · 技术社区  · 15 年前


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

    桌子 containst 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
    

    上有一个后插入触发器 表2 这是用来记录变异的 表2 放入第三个表中,名称恰当 突变

    一点背景知识:这存在于一组小型支持表中。出于审计目的,项目要求记录对源数据所做的每个更改。带有日志记录的表格包含诸如银行账号之类的内容,大量资金将存入其中。最多有几千条记录,它们应该很少被修改。审计功能是用来阻止欺诈的:我们用“谁做的”来记录“发生了什么变化”。

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

    呸。现在回到问题上来。

    触发器的简化版本(真实版本对每列进行插入,并且还插入旧值):

    --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
    

    为什么代码在循环的中间退出?

    6 回复  |  直到 15 年前
        1
  •  9
  •   HLGEM    15 年前

    你的问题是你根本不应该使用光标!这是上面给出的示例的代码。

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

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

    应该检查使用游标的任何生产代码,用正确的基于集合的代码替换它。根据我的经验,90%以上的游标都可以以基于集合的方式编写。

        2
  •  4
  •   DForck42    15 年前

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

    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
    
        3
  •  3
  •   Ryan Barrett    15 年前

    Ryan,您的问题是@@FETCH\u状态对于连接中的所有游标都是全局的。

    文档中对此进行了解释,可以在MSDN上找到 here .

    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
    

    值得注意的是,这种行为不适用于嵌套游标。我已经做了一个快速的例子,它在SQLServer2008上返回预期的结果(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'
    
        4
  •  1
  •   cjk    15 年前

    此代码不会从游标获取任何其他值,也不会增加任何值。事实上,没有理由在这里实现游标。

    您的整个代码可以重写为:

    DECLARE @v1 int
    DECLARE @v2 int
    
    SELECT @v1 = Col1, @v2 = Col2
    FROM table
    
    IF(@v1>10)
        INSERT INTO table2(col1) VALUES (@v2)
    

        5
  •  1
  •   momo    15 年前

    以下是一个例子:

    INSERT LOG.DataChanges
    SELECT
       SchemaName = 'Schemaname',
       TableName = 'TableName',
       ColumnName = CASE ColumnID WHEN 1 THEN 'Column1' WHEN 2 THEN 'Column2' WHEN 3 THEN 'Column3' WHEN 4 THEN 'Column4' END
       ID = Key1,
       ID2 = Key2,
       ID3 = Key3,
       DataBefore = CASE ColumnID WHEN 1 THEN I.Column1 WHEN 2 THEN I.Column2 WHEN 3 THEN I.Column3 WHEN 4 THEN I.Column4 END,
       DataAfter = CASE ColumnID WHEN 1 THEN D.Column1 WHEN 2 THEN D.Column2 WHEN 3 THEN D.Column3 WHEN 4 THEN D.Column4 END,
       DateChange = GETDATE(),
       USER = WhateverFunctionYouAreUsingForThis
    FROM
       Inserted I
       FULL JOIN Deleted D ON I.Key1 = D.Key1 AND I.Key2 = D.Key2
       CROSS JOIN (
          SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
       ) X (ColumnID)
    

    在X表中,您可以使用第二列编写额外的行为代码,该列专门描述如何处理该列(假设您希望某些列一直发布,但其他列仅在值更改时发布)。重要的是,这是一个将行拆分为每列的交叉连接技术的示例,但是还有很多事情可以做。请注意,完全联接允许对插入、删除以及更新进行操作。

    this forum 更多关于这方面的信息。

        6
  •  0
  •   Lieven Keersmaekers    15 年前

    您应该将代码更改为

    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