FOR UPDATE 游标的基本语法

发布于 2024-10-09 07:55:09 字数 916 浏览 1 评论 0原文

好吧，我当然熟悉使用只读游标遍历表格，但我似乎找不到实际更新当前行的正确语法（光标页面也不是在线书籍中的更新页面似乎显示了这个简单的操作）：

DECLARE @counter int;
SET @counter = 1;
DECLARE myCursor CURSOR FOR
        SELECT RowID, Value FROM myTable
        FOR UPDATE OF Value;
OPEN myCursor;
WHILE @counter < 100
    FETCH NEXT FROM myCursor
    UPDATE myCursor SET Value = @Counter << DOESN'T WORK 
    SET @counter = @counter + 1
END
CLOSE myCursor
DEALLOCATE myCursor

我还尝试了 SET Value = @Counter 并在 FETCH 上使用 INTO @Value，但似乎也无法使其工作。

这显然过于简单化了，有更有效的方法来“倒数”一列。我不会用实际的计算来烦你。

是的，我确实需要一个游标，而不是整个表上的更新（每个连续行的值将基于取决于已写入的前一行的计算）。

最初在 SQL 2005 上对此进行测试，但我还需要将代码移植到 SQL 2000 和 2008。谢谢！

原文

Ok, I'm certainly familiar with walking through a table using a read-only cursor, but I can't seem to find the right syntax for actually updating the current row (Neither the cursor page nor the UPDATE page in books online seems to show this simple operation):

DECLARE @counter int;
SET @counter = 1;
DECLARE myCursor CURSOR FOR
        SELECT RowID, Value FROM myTable
        FOR UPDATE OF Value;
OPEN myCursor;
WHILE @counter < 100
    FETCH NEXT FROM myCursor
    UPDATE myCursor SET Value = @Counter << DOESN'T WORK 
    SET @counter = @counter + 1
END
CLOSE myCursor
DEALLOCATE myCursor

I also tried just SET Value = @Counter and using an INTO @Value on the FETCH, but couldn't seem to get that to work either.

This is obviously over-simplified, there are much more efficient ways to just "count" down a column. I won't bore you with the actual calculation.

Yes, I do need a cursor and not an UPDATE on the entire table (the value for each successive row will be based on a calculation that depends on the prior row already being written).

Testing this initially on SQL 2005, but I will need to port the code to SQL 2000 and 2008 as well. Thanks!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

爱，才寂寞 2024-10-16 07:55:09

您想要使用 WHERE CURRENT OF -

DECLARE complex_cursor CURSOR FOR
    SELECT a.BusinessEntityID
    FROM HumanResources.EmployeePayHistory AS a
    WHERE RateChangeDate <> 
         (SELECT MAX(RateChangeDate)
          FROM HumanResources.EmployeePayHistory AS b
          WHERE a.BusinessEntityID = b.BusinessEntityID) ;
OPEN complex_cursor;
FETCH FROM complex_cursor;
UPDATE HumanResources.EmployeePayHistory
SET PayFrequency = 2 
WHERE CURRENT OF complex_cursor;
CLOSE complex_cursor;
DEALLOCATE complex_cursor;
GO

来自 MSDN 。

You want to use WHERE CURRENT OF -

DECLARE complex_cursor CURSOR FOR
    SELECT a.BusinessEntityID
    FROM HumanResources.EmployeePayHistory AS a
    WHERE RateChangeDate <> 
         (SELECT MAX(RateChangeDate)
          FROM HumanResources.EmployeePayHistory AS b
          WHERE a.BusinessEntityID = b.BusinessEntityID) ;
OPEN complex_cursor;
FETCH FROM complex_cursor;
UPDATE HumanResources.EmployeePayHistory
SET PayFrequency = 2 
WHERE CURRENT OF complex_cursor;
CLOSE complex_cursor;
DEALLOCATE complex_cursor;
GO

That is from MSDN.

回复收藏 0 原文

~没有更多了~