游标内的光标 [英] Cursor inside cursor

问题描述

主要问题是关于将行的索引更改为1,2,3 ..其中contact-id和type是相同的。但所有列可以包含完全相同的数据，因为一些前员工搞砸了，并通过联系人id和类型更新所有行。不知何故有行不会乱，但索引行是相同的。这是完全混乱。

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.

我试图使用一个内部游标和来自外部游标的变量。
但是它似乎停留在内部游标中。

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)

可能有什么问题？是@@ FETCH_STATUS不明确或有什么？

What can be the problem? Is @@FETCH_STATUS ambiguous or something?

编辑：如果我不在内部光标内使用此代码，一切看起来很好：

everything looks fine if i don't use this code inside inner cursor:

UPDATE CONTACTS
SET INDEX_NO = @COUNTER
where current of INNER_CURSOR

编辑：这是大图：

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

推荐答案

我不完全明白当前光标，但是通过对内部游标使用fetch语句两次来解决：

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 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

这篇关于游标内的光标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！