SqlServer-简单的事务和游标结合

向右看齐 2022-05-29 09:35 202阅读 0赞

IF EXISTS (SELECT *  FROM  sys.procedures WHERE name='proc_tranDemo')
     DROP PROCEDURE  proc_tranDemo
     GO 
     
     CREATE PROC proc_tranDemo
     AS
        DECLARE @employeeId INT;
        DECLARE @errorCount INT; 
        DECLARE my_cursor CURSOR SCROLL
        FOR
            ( SELECT    employee_id
              FROM      hrt_staff_base
              WHERE     staff_status = -1
            );
        --打开游标
        OPEN my_cursor;
        --取第一个游标对象
        FETCH FIRST FROM my_cursor  INTO @employeeId;
        ---0 FETCH 语句成功
        ---1 FETCH 语句失败或此行不在结果集中
        ---2 被提取的行不存在
        WHILE ( @@FETCH_STATUS = 0 )
            BEGIN 
                SET @errorCount = 0;
                BEGIN TRAN my_tran;
                BEGIN TRY   
                UPDATE hrt_staff_base SET  sf_modifier='事务执行成功！' WHERE employee_id=@employeeId
                END TRY
                BEGIN CATCH 
                    SET @errorCount = @errorCount + 1;
                END CATCH;
                PRINT @errorCount;
                IF ( @errorCount > 0 )
                    BEGIN
                        ROLLBACK TRAN my_tran;
                    END;
                ELSE
                    BEGIN 
                        COMMIT TRAN my_tran;
                    END; 
              --取下一个游标      
                FETCH NEXT FROM my_cursor INTO @employeeId;                  
            END;
            
            --关闭游标
        CLOSE my_cursor; 
            --释放游标
        DEALLOCATE my_cursor;
            GO 
           
        
        --执行存储过程
        EXEC proc_tranDemo
        GO 
        SELECT * FROM hrt_staff_base WHERE  sf_modifier='事务执行成功！'

![Image 1][]

[Image 1]: