动态游标不反映对基表的更新

Dynamic cursor not reflecting updates to base table(动态游标不反映对基表的更新)
本文介绍了动态游标不反映对基表的更新的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

问题描述

据我所知,使用 dynamic 游标将反映对基表所做的任何更改.为什么在下面的例子中没有出现这种情况?

It is my understanding that with a cursor that is dynamic will reflect any changes that are made to the base tables. Why doesn't that occur in the following example ?

我用一个表变量和一个具有相同结果的实际表尝试了这个.如果 @@fetch_status 循环开始后的那行没有被注释,我会得到我期望的结果.

I tried this with a table variable and an actual table with the same results. If the line after the beginning of the @@fetch_status loop is uncommented I get the results I expect.

declare @BalanceTable table
(
    LineId int not null identity(1, 1),
    Qty int not null,
    Price   money not null
)

insert into @BalanceTable (Qty, Price) values (3000, 1)
insert into @BalanceTable (Qty, Price) values (40, 2)
insert into @BalanceTable (Qty, Price) values (1, 1)
insert into @BalanceTable (Qty, Price) values (2000, 1)
insert into @BalanceTable (Qty, Price) values (4047, 2)
insert into @BalanceTable (Qty, Price) values (-3000, 1)
insert into @BalanceTable (Qty, Price) values (-38, 2)
insert into @BalanceTable (Qty, Price) values (3000, 1)

declare BalanceTable cursor
  dynamic for
    select LineId, Qty, Price
      from @BalanceTable
      order by LineId

declare @LineId int
declare @Qty int
declare @Price money

open BalanceTable

fetch next from BalanceTable into @LineId, @Qty, @Price

while @@fetch_status = 0
begin
    -- select @Qty = Qty, @Price = Price from @BalanceTable where LineId = @LineId

    declare @SearchLessZero bit
    set @SearchLessZero = case when @Qty > 0 then 1 else 0 end

    declare @OffsetLineId int
    declare @OffsetQty int
    set @OffsetLineId = -1

    while @Qty > 0 and @OffsetLineId is not null
    begin
        select @OffsetLineId = min(LineId)
          from @BalanceTable
          where LineId > @LineId and Price = @Price and 
            ((@SearchLessZero = 1 and Qty < 0) or (@SearchLessZero = 0 and Qty > 0))

        if @OffsetLineId is not null
        begin
            select @OffsetQty = Qty
              from @BalanceTable
              where LineId = @OffsetLineId

            if @Qty > -@OffsetQty
            begin
                set @Qty = @Qty + @OffsetQty
                set @OffsetQty = 0
            end
            else
            begin
                set @OffsetQty = @OffsetQty + @Qty
                set @Qty = 0
            end

            update @BalanceTable set Qty = @OffsetQty where LineId =     @OffsetLineId
        end
    end

    update @BalanceTable set Qty = @Qty where LineId = @LineId

    fetch next from BalanceTable into @LineId, @Qty, @Price
end

close BalanceTable
deallocate BalanceTable

select *
  from @BalanceTable
  order by LineId

推荐答案

在动态游标中只允许很少的执行计划运算符.如果游标查询的执行计划包含不允许的操作符,游标将转换为快照游标,因此不会看到更新.

Only very few execution plan operators are permitted in a dynamic cursor. If the execution plan for the cursor query contains a non-permitted operator, the cursor gets converted into a snapshot cursor and hence does not see updates.

如果您查看游标的执行计划,您会发现就是这样:

if you look at the execution plan for your cursor you see that just that happened:

查询中的问题运算符是排序.删除它,您将看到更新.

The problem operator in your query is the sort. Remove it and you will see updates.

如果需要对数据进行排序,在表中添加聚集索引,这样ORDER BY就不需要排序运算符了.

If you need the data sorted, add a clustered index to the table, so that the ORDER BY does not require a sort operator.

这篇关于动态游标不反映对基表的更新的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

相关文档推荐

Query with t(n) and multiple cross joins(使用 t(n) 和多个交叉连接进行查询)
Unpacking a binary string with TSQL(使用 TSQL 解包二进制字符串)
Max rows in SQL table where PK is INT 32 when seed starts at max negative value?(当种子以最大负值开始时,SQL 表中的最大行数其中 PK 为 INT 32?)
Inner Join and Group By in SQL with out an aggregate function.(SQL 中的内部连接和分组依据,没有聚合函数.)
Add a default constraint to an existing field with values(向具有值的现有字段添加默认约束)
SQL remove from running total(SQL 从运行总数中删除)