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

Question

我有以下脚本可以正常工作:

DECLARE db_cursor1 CURSOR LOCAL FOR 
SELECT  ID, Name table_1

OPEN db_cursor1 
FETCH NEXT FROM db_cursor1 INTO  @ID, @Name                      

WHILE @@FETCH_STATUS = 0  
BEGIN  
   BEGIN TRANSACTION
   BEGIN TRY

     <insert into table values>  

   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 

上述脚本工作正常，因为它回滚 db_cursor1 的当前迭代中的内容，然后在出现错误时继续进行下一次迭代。

当我有一个嵌套游标时就会出现问题。它回滚当前迭代中的内容，但不进行 cursor1 的下一次迭代。

  DECLARE db_cursor1 CURSOR LOCAL FOR 
  SELECT  ID, Name table_1

  OPEN db_cursor1 
  FETCH NEXT FROM db_cursor1 INTO  @ID, @Name                      

  WHILE @@FETCH_STATUS = 0  
  BEGIN  
     BEGIN TRANSACTION
     BEGIN TRY

     <insert into table values>  

      --- inner cursor

      DECLARE db_cursor2 CURSOR LOCAL FOR 
      SELECT  ID, Name table_2

      OPEN db_cursor2
      FETCH NEXT FROM db_cursor2 INTO  @ID, @Name                      

      WHILE @@FETCH_STATUS = 0  
      BEGIN             

          <insert into table values>  

          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 

Answer 1

我能够使用基本值在我的一端创建它，请参阅 脚本 #1 - 错误 如果你想测试它。您遇到的问题是，当您在 db_cursor2 中出现错误时, 退出循环而不关闭或取消分配游标。然后当代码进入下一次迭代时，它失败并出现此错误 A cursor with the name 'db_cursor2' already exists.请看脚本 #2 - 成功 以获得正确的结果。为了给它更多的颜色，你需要添加 CLOSE db_cursor2; DEALLOCATE db_cursoe2;在您的 BEGIN CATCH .

设置 , 专为 SQL Server 2016+ 设计

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;