问题描述
在我继续之前:是的,我知道与基于集合的操作相比,游标的性能很差.在这种特殊情况下,我在一个包含 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.