SQL 游标内的游标
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/469019/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Cursor inside cursor
提问by Orkun Balkanc?
Main problem is about changing the index of rows to 1,2,3.. where contact-id and type is the same. but all columns can contain exactly the same data because of some ex-employee messed up and update all rows by contact-id and type. somehow there are rows that aren't messed but index rows are same. It is total chaos.
主要问题是将行的索引更改为 1,2,3.. 其中 contact-id 和 type 是相同的。但是所有列都可以包含完全相同的数据，因为一些前员工搞砸了并按联系人 ID 和类型更新了所有行。不知何故，有些行没有被弄乱，但索引行是相同的。这是完全的混乱。
I tried to use an inner cursor with the variables coming from the outer cursor. But It seems that its stuck in the inner cursor.
我尝试使用带有来自外部游标的变量的内部游标。但它似乎卡在内部光标中。
A part of the query looks like this:
查询的一部分如下所示：
Fetch NEXT FROM OUTER_CURSOR INTO @CONTACT_ID, @TYPE
While (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
DECLARE INNER_CURSOR Cursor
FOR
SELECT * FROM CONTACTS
where CONTACT_ID = @CONTACT_ID
and TYPE = @TYPE
Open INNER_CURSOR
Fetch NEXT FROM INNER_CURSOR
While (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
What can be the problem? Is @@FETCH_STATUS ambiguous or something?
可能是什么问题？@@FETCH_STATUS 是模棱两可还是什么？
EDIT: everything looks fine if i don't use this code inside inner cursor:
编辑：如果我不在内部光标内使用此代码，一切看起来都很好：
UPDATE CONTACTS
SET INDEX_NO = @COUNTER
where current of INNER_CURSOR
EDIT: here is the big picture:
编辑：这是大图：
BEGIN TRAN
DECLARE @CONTACT_ID VARCHAR(15)
DECLARE @TYPE VARCHAR(15)
DECLARE @INDEX_NO SMALLINT
DECLARE @COUNTER SMALLINT
DECLARE @FETCH_STATUS INT
DECLARE OUTER_CURSOR CURSOR
FOR
SELECT CONTACT_ID, TYPE, INDEX_NO FROM CONTACTS
WHERE
CONTACT_ID IN (SELECT CONTACT_ID FROM dbo.CONTACTS
WHERE CONTACT_ID IN(...)
GROUP BY CONTACT_ID, TYPE, INDEX_NO
HAVING COUNT(*) > 1
OPEN OUTER_CURSOR
FETCH NEXT FROM OUTER_CURSOR INTO @CONTACT_ID, @TYPE, @INDEX_NO
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
SET @COUNTER = 1
DECLARE INNER_CURSOR CURSOR
FOR
SELECT * FROM CONTACTS
WHERE CONTACT_ID = @CONTACT_ID
AND TYPE = @TYPE
FOR UPDATE
OPEN INNER_CURSOR
FETCH NEXT FROM INNER_CURSOR
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
UPDATE CONTACTS
SET INDEX_NO = @COUNTER
WHERE CURRENT OF INNER_CURSOR
SET @COUNTER = @COUNTER + 1
FETCH NEXT FROM INNER_CURSOR
END
CLOSE INNER_CURSOR
DEALLOCATE INNER_CURSOR
FETCH NEXT FROM OUTER_CURSOR INTO @CONTACT_ID, @TYPE, @INDEX_NO
END
CLOSE OUTER_CURSOR
DEALLOCATE OUTER_CURSOR
COMMIT TRAN
采纳答案by Orkun Balkanc?
I don't fully understand what was the problem with the "update current of cursor"but it is solved by using the fetch statement twice for the inner cursor:
我不完全理解“更新游标电流”的问题是什么，但通过对内部游标使用两次 fetch 语句解决了这个问题：
FETCH NEXT FROM INNER_CURSOR
WHILE (@@FETCH_STATUS <> -1)
BEGIN
UPDATE CONTACTS
SET INDEX_NO = @COUNTER
WHERE CURRENT OF INNER_CURSOR
SET @COUNTER = @COUNTER + 1
FETCH NEXT FROM INNER_CURSOR
FETCH NEXT FROM INNER_CURSOR
END
回答by Mark Brittingham
You have a variety of problems. First, why are you using your specific @@FETCH_STATUS values? It should just be @@FETCH_STATUS = 0.
你有各种各样的问题。首先，为什么要使用特定的 @@FETCH_STATUS 值？它应该只是@@FETCH_STATUS = 0。
Second, you are not selecting your inner Cursor intoanything. And I cannot think of any circumstance where you would select all fields in this way - spell them out!
其次，您没有将内部 Cursor 选择到任何内容中。而且我想不出任何情况下您会以这种方式选择所有字段 - 将它们拼写出来！
Here's a sample to go by. Folder has a primary key of "ClientID" that is also a foreign key for Attend. I'm just printing all of the Attend UIDs, broken down by Folder ClientID:
这是一个示例。文件夹有一个主键“ClientID”，也是Attend 的外键。我只是打印所有参加 UID，按文件夹 ClientID 细分：
Declare @ClientID int;
Declare @UID int;
DECLARE Cur1 CURSOR FOR
SELECT ClientID From Folder;
OPEN Cur1
FETCH NEXT FROM Cur1 INTO @ClientID;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Processing ClientID: ' + Cast(@ClientID as Varchar);
DECLARE Cur2 CURSOR FOR
SELECT UID FROM Attend Where ClientID=@ClientID;
OPEN Cur2;
FETCH NEXT FROM Cur2 INTO @UID;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Found UID: ' + Cast(@UID as Varchar);
FETCH NEXT FROM Cur2 INTO @UID;
END;
CLOSE Cur2;
DEALLOCATE Cur2;
FETCH NEXT FROM Cur1 INTO @ClientID;
END;
PRINT 'DONE';
CLOSE Cur1;
DEALLOCATE Cur1;
Finally, are you SUREyou want to be doing something like this in a stored procedure? It is very easy to abuse stored procedures and often reflects problems in characterizing your problem. The sample I gave, for example, could be far more easily accomplished using standard select calls.
最后，你肯定你想要做这样的事情在存储过程中？很容易滥用存储过程，并且经常反映在描述您的问题时的问题。例如，我给出的示例可以使用标准的 select 调用更容易地完成。
回答by cmsjr
You could also sidestep nested cursor issues, general cursor issues, and global variable issues by avoiding the cursors entirely.
您还可以通过完全避免游标来回避嵌套游标问题、一般游标问题和全局变量问题。
declare @rowid int
declare @rowid2 int
declare @id int
declare @type varchar(10)
declare @rows int
declare @rows2 int
declare @outer table (rowid int identity(1,1), id int, type varchar(100))
declare @inner table (rowid int identity(1,1), clientid int, whatever int)
insert into @outer (id, type)
Select id, type from sometable
select @rows = count(1) from @outer
while (@rows > 0)
Begin
select top 1 @rowid = rowid, @id = id, @type = type
from @outer
insert into @innner (clientid, whatever )
select clientid whatever from contacts where contactid = @id
select @rows2 = count(1) from @inner
while (@rows2 > 0)
Begin
select top 1 /* stuff you want into some variables */
/* Other statements you want to execute */
delete from @inner where rowid = @rowid2
select @rows2 = count(1) from @inner
End
delete from @outer where rowid = @rowid
select @rows = count(1) from @outer
End
回答by Joel Coehoorn
This smells of something that should be done with a JOIN instead. Can you share the larger problem with us?
这有点像应该用 JOIN 来完成的事情。你能和我们分享更大的问题吗？
Hey, I should be able to get this down to a single statement, but I haven't had time to play with it further yet today and may not get to. In the mean-time, know that you should be able to edit the query for your inner cursor to create the row numbers as part of the query using the ROW_NUMBER()function. From there, you can fold the inner cursor into the outer by doing an INNER JOIN on it (you can join on a sub query). Finally, any SELECT statement can be converted to an UPDATE using this method:
嘿，我应该能够把它归结为一个单一的陈述，但我今天还没有时间进一步研究它，可能无法做到。同时，知道您应该能够编辑内部游标的查询，以使用ROW_NUMBER()函数创建行号作为查询的一部分。从那里，您可以通过对其执行 INNER JOIN 将内部光标折叠到外部（您可以加入子查询）。最后，任何 SELECT 语句都可以使用此方法转换为 UPDATE：
UPDATE [YourTable/Alias]
SET [Column] = q.Value
FROM
(
... complicate select query here ...
) q
Where [YourTable/Alias]is a table or alias used in the select query.
[YourTable/Alias]选择查询中使用的表或别名在哪里。
回答by Amy B
Do you do any more fetches? You should show those as well. You're only showing us half the code.
你还做更多的提取吗？你也应该展示这些。您只向我们展示了一半的代码。
It should look like:
它应该看起来像：
FETCH NEXT FROM @Outer INTO ...
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @Inner...
OPEN @Inner
FETCH NEXT FROM @Inner INTO ...
WHILE @@FETCH_STATUS = 0
BEGIN
...
FETCH NEXT FROM @Inner INTO ...
END
CLOSE @Inner
DEALLOCATE @Inner
FETCH NEXT FROM @Outer INTO ...
END
CLOSE @Outer
DEALLOCATE @Outer
Also, make sure you do not name the cursors the same... and any code (check your triggers) that gets called does not use a cursor that is named the same. I've seen odd behavior from people using 'theCursor' in multiple layers of the stack.
另外，请确保您没有将游标命名为相同的...并且被调用的任何代码（检查您的触发器）都不会使用名称相同的游标。我已经看到人们在堆栈的多层中使用“theCursor”的奇怪行为。
回答by Julian Salinas
I had the same problem,
我有同样的问题，
what you have to do is declare the second cursor as: DECLARE [second_cursor] Cursor LOCAL For
您需要做的是将第二个游标声明为：DECLARE [second_cursor] Cursor LOCAL For
You see"CURSOR LOCAL FOR" instead of "CURSOR FOR"
你看到“CURSOR LOCAL FOR”而不是“CURSOR FOR”
