问题描述
据我所知,使用 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.