如何更新 TSQL 中游标获取的列
在我继续之前:是的，我知道与基于集合的操作相比，游标的性能很差.在这种特殊情况下，我在一个包含 100 条左右记录的临时表上运行游标，并且该临时表总是相当小，因此性能不如灵活性重要.
Before I go any further: Yes, I know that cursors perform poorly compared with set-based operations. In this particular case I'm running a cursor on a temporary table of 100 or so records, and that temporary table will always be fairly small, so performance is less crucial than flexibility.
我的困难在于我无法找到如何更新由游标获取的列的示例.以前，当我使用游标时，我将值检索到变量中，然后根据这些值在每一步运行更新查询.这次我想更新临时表中的一个字段，但我不知道该怎么做.
My difficulty is that I'm having trouble finding an example of how to update a column fetched by a cursor. Previously when I've used cursors I've retrieved values into variables, then run an update query at each step based upon these values. On this occasion I want to update a field in the temporary table, yet I can't figure out how to do it.
在下面的示例中，我尝试根据使用 #t1.Product_ID 查找所需值的查询更新临时表 #t1 中的字段 CurrentPOs.您将在代码中看到我尝试使用符号 curPO.Product_ID 来引用它，但它不起作用.我还尝试对 curPO 使用更新语句，但也未成功.
In the example below, I'm trying to update the field CurrentPOs in temporary table #t1, based upon a query that uses #t1.Product_ID to look up the required value. You will see in the code that I have attempted to use the notation curPO.Product_ID to reference this, but it doesn't work. I have also attempted to use an update statement against curPO, also unsuccessfully.
我可以通过获取变量来使代码工作，但我想知道如何直接更新字段.
I can make the code work by fetching to variables, but I'd like to know how to update the field directly.
我想我可能遗漏了一些明显的东西，但有人可以帮忙吗?
I think I'm probably missing something obvious, but can anyone help?
declare curPO cursor
for select Product_ID, CurrentPOs from #t1
for update of CurrentPOs
open curPO
fetch next from curPO
while @@fetch_status = 0
begin
select OrderQuantity = <calculation>,
ReceiveQuantity = <calculation>
into #POs
from PurchaseOrderLine POL
inner join SupplierAddress SA ON POL.Supplier_ID = SA.Supplier_ID
inner join PurchaseOrderHeader POH ON POH.PurchaseOrder_ID = POL.PurchaseOrder_ID
where Product_ID = curPO.Product_ID
and SA.AddressType = '1801'
update curPO set CurrentPOs = (select sum(OrderQuantity) - sum(ReceiveQuantity) from #POs)
drop table #POs
fetch next from curPO
end
close curPO
deallocate curPO
推荐答案
在进行了更多的谷歌搜索后，我找到了部分解决方案.更新代码如下:
After doing a bit more googling, I found a partial solution. The update code is as follows:
UPDATE #T1
SET CURRENTPOS = (SELECT SUM(ORDERQUANTITY) - SUM(RECEIVEQUANTITY)
FROM #POS)
WHERE CURRENT OF CURPO
我仍然需要使用 FETCH INTO 来检索 #t1.Product_ID 并运行生成 #PO 的查询，所以我仍然想知道如果可以单独使用 FETCH.
I still had to use FETCH INTO, however, to retrieve #t1.Product_ID and run the query that produces #POs, so I'd still like to know if it's possible to use FETCH on it's own.
相关文章