SQL Server 2000 游标无法获取 NEXT 的问题
我有一个 SQL Server 2000 数据库。我需要运行以下游标来对各个记录触发触发器（触发器一次仅对一条记录进行操作）。
DECLARE @REC as bigint
DECLARE coil1_cursor CURSOR FOR
SELECT Rec# FROM coil1 WHERE Timestamp BETWEEN '2011-05-10 06:00:00' AND '2011-05-10 07:00:00'
OPEN coil1_cursor
FETCH NEXT FROM coil1_cursor INTO @REC
WHILE (@@FETCH_STATUS=0)
BEGIN
Print @Rec
UPDATE coil1 SET ShiftLength=Null WHERE Rec#=@REC
FETCH NEXT FROM coil1_cursor INTO @REC
END
CLOSE coil1_cursor
DEALLOCATE coil1_cursor
如果我注释掉 UPDATE 行，我将按预期从 Rec# 字段中获取序列号。如果我取消注释 UPDATE 行，批处理将进入无限循环。当我停止批处理时，它只显示更新它到达的第一条记录。就像 FETCH NEXT 被卡住了。有什么想法吗？
I've got a SQL Server 2000 db. I need to run the following cursor to fire a trigger on individual records (the trigger will only operate on one record at a time).
DECLARE @REC as bigint
DECLARE coil1_cursor CURSOR FOR
SELECT Rec# FROM coil1 WHERE Timestamp BETWEEN '2011-05-10 06:00:00' AND '2011-05-10 07:00:00'
OPEN coil1_cursor
FETCH NEXT FROM coil1_cursor INTO @REC
WHILE (@@FETCH_STATUS=0)
BEGIN
Print @Rec
UPDATE coil1 SET ShiftLength=Null WHERE Rec#=@REC
FETCH NEXT FROM coil1_cursor INTO @REC
END
CLOSE coil1_cursor
DEALLOCATE coil1_cursor
If I comment out the UPDATE line, I get sequential numbers from Rec# field as expected. If I uncomment the UPDATE line, the batch goes into an infinite loop. When I stop the batch, it only shows updating the first record it gets to. It's like the FETCH NEXT gets stuck. Any ideas why?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您正在使用默认光标选项（包括动态）。尝试使用更有效的光标来实现您的目的：
但对于真正的解决方案，乔是绝对正确的 - 这根本不应该是光标。您可以使用单个更新语句完成同样的事情。没有游标，没有无限循环，没有等待：（
顺便说一句，
[Timestamp]是一个可怕的列名，因为它表示与日期或时间无关的数据类型。Rec #也不是很棒。）You're using the default cursor options (including dynamic). Try using a much more efficient cursor for your purposes:
But for the real fix, Joe is absolutely right - this shouldn't be a cursor at all. You can accomplish the same thing with a single update statement. No cursor, no infinite loops, no waiting:
(As an aside,
[Timestamp]is a horrible column name because it represents a data type that has nothing to do with date or time.Rec#is not fabulous either. YMMV.)因为您正在更新游标引用的表，所以您应该将游标声明为 STATIC。
Because you're updating the table referenced by your cursor, you should declare your cursor as STATIC.