tsql嵌套游标没有正确回滚继续

脚本#1-错误 如果你想测试的话。你遇到的问题是 db_cursor2 ,则退出循环而不关闭或释放光标。然后,当代码进入下一次迭代时,它会失败并出现此错误 A cursor with the name 'db_cursor2' already exists. 脚本#2-成功 以获得正确的结果。为了给它更多的颜色,你需要添加 CLOSE db_cursor2; DEALLOCATE db_cursoe2; 在你的 BEGIN CATCH .

DROP TABLE IF EXISTS #table_1, #table_2

CREATE TABLE #table_1
(
    [ID] INT,
    [Name] VARCHAR(5)
);
CREATE TABLE #table_2
(
    [ID] INT,
    [NAME] VARCHAR(5)
);

INSERT INTO #table_1 SELECT 1, 'j';
INSERT INTO #table_1 SELECT 2, 'j';

INSERT INTO #table_2 SELECT 1, 'j';
INSERT INTO #table_2 SELECT 2, 'j';

脚本#1-错误

DECLARE @ID INT;
DECLARE @name VARCHAR(5);

DECLARE db_cursor1 CURSOR LOCAL FOR SELECT [ID], [Name] FROM #table_1;

OPEN db_cursor1;
FETCH NEXT FROM db_cursor1
INTO @ID, @name;

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRANSACTION;
    BEGIN TRY

        PRINT('trying 1')
        --- inner cursor

        DECLARE db_cursor2 CURSOR LOCAL FOR SELECT [ID], [Name] FROM #table_2;

        OPEN db_cursor2;
        FETCH NEXT FROM db_cursor2
        INTO @ID, @name;

        WHILE @@FETCH_STATUS = 0
        BEGIN
            PRINT('trying 2')
            SELECT 1/0

            FETCH NEXT FROM db_cursor2
            INTO @ID, @name;
        END;

        CLOSE db_cursor2;
        DEALLOCATE db_cursor2;

        COMMIT TRANSACTION;

    END TRY
    BEGIN CATCH
        PRINT ERROR_MESSAGE();

        ROLLBACK TRANSACTION;
    END CATCH;

    FETCH NEXT FROM db_cursor1
    INTO @ID, @name;

END;

CLOSE db_cursor1;
DEALLOCATE db_cursor1;

脚本#2-成功

DECLARE @ID INT;
DECLARE @name VARCHAR(5);

DECLARE db_cursor1 CURSOR LOCAL FOR SELECT [ID], [Name] FROM #table_1;

OPEN db_cursor1;
FETCH NEXT FROM db_cursor1
INTO @ID, @name;

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRANSACTION;
    BEGIN TRY

        PRINT('trying 1')
        --- inner cursor

        DECLARE db_cursor2 CURSOR LOCAL FOR SELECT [ID], [Name] FROM #table_2;

        OPEN db_cursor2;
        FETCH NEXT FROM db_cursor2
        INTO @ID, @name;

        WHILE @@FETCH_STATUS = 0
        BEGIN
            PRINT('trying 2')
            SELECT 1/0

            FETCH NEXT FROM db_cursor2
            INTO @ID, @name;
        END;

        CLOSE db_cursor2;
        DEALLOCATE db_cursor2;

        COMMIT TRANSACTION;

    END TRY
    BEGIN CATCH
        PRINT ERROR_MESSAGE();

        -- was missing in above script
        CLOSE db_cursor2
        DEALLOCATE db_cursor2

        ROLLBACK TRANSACTION;
    END CATCH;

    FETCH NEXT FROM db_cursor1
    INTO @ID, @name;

END;

CLOSE db_cursor1;
DEALLOCATE db_cursor1;

因为您使用的是TRY-CATCH,所以如果TRY中有错误,代码将从CATCH中开始执行。

正如@paulwehland所说的,这是因为Try-Catch没有处理嵌套的游标。因此,下一次迭代将用已经存在的名称初始化游标。我提供了一个代码示例,它将在每个游标的第11次迭代中以预期的失败条件运行基本场景。

在这个例子中,我已经注释掉了代码中解决这个问题的部分。选择将该块放置在何处完全由您决定,但在嵌套游标声明之前或在Catch块内部进行检查是有意义的。

declare
    @id tinyint,
    @parent_id tinyint,
    @name varchar(255),
    @parent_name varchar(255);

declare
    @table
table
    (
    id tinyint not null primary key,
    [name] varchar(255) not null
    );

declare
    @target
table
    (
    parent_id tinyint not null,
    child_id tinyint not null,
    parent_name varchar(10) not null,
    child_name varchar(10) not null,
    primary key(parent_id, child_id)
    );

with cteNumber
as  (
    select top 11
        [id] = row_number() over (order by [object_id])
    from
        sys.objects
    )
insert into
    @table
select
    id,
    [name] = replicate('a', id)
from
    cteNumber;

declare
    db_cursor1 
cursor
    local keyset read_only forward_only 
for
    select
        0,
        id,
        'Initial', 
        [name]
    from
        @table;

open
    db_cursor1;
fetch
    next
from
    db_cursor1 
into
    @id,
    @parent_id, 
    @name,
    @parent_name;

while @@FETCH_STATUS = 0  
    begin
        begin transaction;

        begin try
            insert into @target
                (parent_id, child_id, parent_name, [child_name])
            values
                (@parent_id, @id, @parent_name, @name);

            --- inner cursor
            /*
            if CURSOR_STATUS('local', 'db_cursor2') = 1
                begin
                    close
                        db_cursor2;
                    deallocate
                        db_cursor2;
                end;
            -- */

            declare
                db_cursor2 
            cursor
                local keyset read_only forward_only 
            for
                select
                    id, 
                    [name]
                from
                    @table;

            open
                db_cursor2;

            fetch
                next
            from
                db_cursor2 
            into
                @id, 
                @name;

            while @@FETCH_STATUS = 0  
                begin
                    insert into @target
                        (parent_id, child_id, parent_name, [child_name])
                    values
                        (@parent_id, @id, @parent_name, @name);

                    fetch
                        next
                    from
                        db_cursor2 
                    into
                        @id,
                        @name;
                end;

            close
                db_cursor2;

            deallocate
                db_cursor2;

            commit transaction
        end try

        begin catch         
            print ERROR_MESSAGE();

            rollback transaction;
        end catch;

        fetch
            next
        from
            db_cursor1 
        into
            @id,
            @parent_id, 
            @name,
            @parent_name;
    end;

close
    db_cursor1;
deallocate
    db_cursor1;

select
    [Last @id] = @id,
    [Last @name] = @name,
    [Last @parent_id] = @parent_id,
    [Last @parent_name] = @parent_name;

select
    *
from
    @table;

select
    *
from
    @target;

您还可以使用游标变量的创建,并将嵌套的游标声明赋给它,这将消除处理重复名称的问题。见下表:

declare
    @id tinyint,
    @parent_id tinyint,
    @name varchar(255),
    @parent_name varchar(255);

declare
    @table
table
    (
    id tinyint not null primary key,
    [name] varchar(255) not null
    );

declare
    @target
table
    (
    parent_id tinyint not null,
    child_id tinyint not null,
    parent_name varchar(10) not null,
    child_name varchar(10) not null,
    primary key(parent_id, child_id)
    );

with cteNumber
as  (
    select top 11
        [id] = row_number() over (order by [object_id])
    from
        sys.objects
    )
insert into
    @table
select
    id,
    [name] = replicate('a', id)
from
    cteNumber;

declare
    @db_cursor2 cursor;

declare
    db_cursor1 
cursor
    local keyset read_only forward_only 
for
    select
        0,
        id,
        'Initial', 
        [name]
    from
        @table;

open
    db_cursor1;
fetch
    next
from
    db_cursor1 
into
    @id,
    @parent_id, 
    @name,
    @parent_name;

while @@FETCH_STATUS = 0  
    begin
        begin transaction;

        begin try
            insert into @target
                (parent_id, child_id, parent_name, [child_name])
            values
                (@parent_id, @id, @parent_name, @name);

            --- inner cursor

            set @db_cursor2 = cursor
                local keyset read_only forward_only 
            for
                select
                    id, 
                    [name]
                from
                    @table;

            open
                @db_cursor2;

            fetch
                next
            from
                @db_cursor2 
            into
                @id, 
                @name;

            while @@FETCH_STATUS = 0  
                begin
                    insert into @target
                        (parent_id, child_id, parent_name, [child_name])
                    values
                        (@parent_id, @id, @parent_name, @name);

                    fetch
                        next
                    from
                        @db_cursor2 
                    into
                        @id,
                        @name;
                end;

            close
                @db_cursor2;

            deallocate
                @db_cursor2;

            commit transaction
        end try

        begin catch         
            print ERROR_MESSAGE();

            rollback transaction;
        end catch;

        fetch
            next
        from
            db_cursor1 
        into
            @id,
            @parent_id, 
            @name,
            @parent_name;
    end;

close
    db_cursor1;
deallocate
    db_cursor1;

select
    [Last @id] = @id,
    [Last @name] = @name,
    [Last @parent_id] = @parent_id,
    [Last @parent_name] = @parent_name;

select
    *
from
    @table;

select
    *
from
    @target;