SQL Server 存储过程的运用

概述

最近因为业务的需求写了一段时间存储过程，发现之前写的存储过程存在一些不严谨的地方，特别是TRY...CATCH中嵌套事务的写法；虽然之前写的并没有错，但是还是埋藏着很大的隐患在里面。希望这篇文章能给大家一些参考；文章内容有点长还望耐心阅读。

1.插入测试数据

----创建表
DROP TABLE score
GO
CREATE TABLE [dbo].[score](
    id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    name VARCHAR(50) NOT NULL,
    score INT NOT NULL CHECK (score>=0),
    months INT NOT NULL,
    createtime DATETIME NOT NULL DEFAULT GETDATE()
)

---根据姓名月份查询分数
CREATE INDEX IX_score_name ON score(name,months) include(score)
---根据月份查询最高分数
CREATE INDEX IX_score_months ON score(months) include(name,score)
---创建姓名和月份组合的唯一索引
CREATE UNIQUE INDEX IX_score_months_name ON score(months,name)

------插入测试数据
TRUNCATE TABLE score

INSERT INTO score(name,score,months) 
VALUES('li',50,10),('chen',70,10),('zhang',80,10),('wang',90,10),('li',50,11),('chen',70,11),('zhang',80,11),('wang',90,11)

SELECT * FROM score;

2.THROW

THROW是在2012版本中引入的，在有些场景当中，应用程序端不做一些合法性的验证，这些验证会被放在数据库端来验证。当数据库端验证输入的信息不合法时需要主动抛出异常来中断代码的执行。

THROW既可以接收错误信息抛错提示，同时也可以手动抛出错误到CATCH中。语法如下：

;THROW

THROW [ { error_number | @local_variable }, 

        { message | @local_variable }, 

        { state | @local_variable } ]  

[ ; ]

参数

error_number
表示异常的常量或变量。 error_number是int并且必须为大于或等于 50000 且小于或等于 2147483647，如果CATCH中使用RAISERROR来接收错误信息那么指定的error_number必须在sys.messages 中存在；如果使用CATCH来接收则不需要。

消息
描述异常的字符串或变量。 消息是nvarchar(2048)。

状态
在 0 到 255 之间的常量或变量，指示与消息关联的状态。 状态是tinyint。

注意：

1.THROW代码前必须要用分号，因为THROW会中断代码的执行，所以如果将THROW放在CATCH中时必须放在ROLLBACK TRAN之后，否则不会回滚事务导致对象一直处于提交状态被锁。

2.THROW放CATCH中可以达到RAISERROR一样的效果，同时还简便了代码。

3. THROW能返回正确的错误代码行号，而RAISERROR没办法

参考：https://docs.microsoft.com/zh-cn/sql/t-sql/language-elements/throw-transact-sql

3.sp_addmessage

自定义错误号

EXEC sp_addmessage  
    @msgnum = 60000,  
    @severity = 16,  
    @msgtext = N'Manual cast wrong ',
    @lang = 'us_english'; 

EXEC sp_addmessage  
    @msgnum = 60000,  
    @severity = 16,  
    @msgtext = N'手动抛错',  
    @lang = '简体中文';

注意：自定义错误号必须大于50000

调用存储过程

1.查询存储过程

----查询存储过程
CREATE PROCEDURE  Pro_score
(@Option VARCHAR(50),
 @name VARCHAR(50)='',
 @months INT=''
)
AS
BEGIN

---查询指定人分数
IF @Option='GetScore'
   BEGIN
        SELECT name,
               score
        FROM score
        WHERE name=@name

   END

----查询指定月份最高分数
IF @Option='MonthMaxScore'
   BEGIN
        SELECT Top 1 
             name,
             score
        FROM score
        WHERE months=@months
        ORDER BY score

   END



END

调用存储过程：

EXEC Pro_score @Option='GetScore',@name='li'
EXEC Pro_score @Option='MonthMaxScore',@months=11

3.修改存储过程

CREATE PROCEDURE [dbo].[Pro_Insert_score]
(@Option VARCHAR(50),
@name VARCHAR(50)='',
@months INT=0,
@score INT=0
) 
AS
BEGIN
DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
 IF @Option='InsertScore'
     BEGIN
     
        -----使用事务
        BEGIN TRY  
                BEGIN TRAN 
                INSERT INTO score(name,score,months) 
                VALUES(@name,@score,@months)

                ----插入重复值报错事务回滚
                INSERT INTO score(name,score,months) 
                VALUES(@name,@score,@months)

                COMMIT TRAN  
                ----执行成功
                RETURN 0
            END TRY  
            BEGIN CATCH 
                IF @@TRANCOUNT > 0
                    ROLLBACK TRAN
                SELECT  @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
                RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) ;
                ----执行失败
                RETURN 1
            END CATCH

     END
 
END

调用存储过程

----调用存储过程2
DECLARE @status INT
EXEC @status=Pro_Insert_score @Option='InsertScore',@name='chen',@months=12,@score=90
SELECT @status

可以发现使用RAISERROR抛错出来的行号和消息号都是错误的，50000这个消息号其实是不存在的，它是保留的一个统一的消息号。

可以通过查询sys.message查询对应的消息号

SELECT * FROM score WHERE name='chen'
SELECT * FROM sys.messages WHERE message_id=2601 and language_id=2052

4.手动抛错中断

手动抛错也是这篇文章主要要讲的一个知识点，在有一些业务场景当中有一些验证操作需要在数据库中进行，甚至必须在更新之后进行但是又是正常的提交操作，在这种情况下就需要手动进行验证是否需要执行下面的代码。，见过很多程序员写存储过程喜欢在每一个判断的地方加上RETURN操作，目的是为了不执行后面的代码，同时又在RETURN前加上ROLLBACK操作。这虽然是一个办法，但是在事务中运用RETURN是一个很危险的操作，弄不好会导致事务一直处于打开操作导致表一直被锁住，在生成环境是很危险的操作。

建议使用THROW来手动进行抛错，THROW抛错会相当于触发一个11-19级别的错误，这样会跳到CATCH中做ROLLBACK操作。

注意：THROW前必须以分号开头，如果THROW前有代码以分号结尾也可以。

CREATE PROCEDURE [dbo].[Pro_score_throw]
(@Option VARCHAR(50),
 @name VARCHAR(50)='',
 @months INT=0,
 @score INT=0
) 
AS
BEGIN
DECLARE @ErrorNum INT,@ErrorSeverity INT,@ErrorState INT,@ErrorLine INT,@ErrorPro VARCHAR(200),@ErrorMessage NVARCHAR(4000);
IF @Option='UpdateScore'
    BEGIN
    
             -----使用事务
                  BEGIN TRY  
                        BEGIN TRAN 
                        UPDATE score
                        SET score=score+@score
                        WHERE name=@name AND months=@months

                        ----在有些业务场景有些判断必须等操作完了才能去做判断是否能继续执行下去
                        IF (SELECT score FROM score WHERE name=@name AND months=@months)>100
                        BEGIN
                             
                             ;THROW 60000,'分数不能大于100',111 

                        END
                        COMMIT TRAN  
        
                    END TRY  
                    BEGIN CATCH 
             
                     ROLLBACK TRAN
                        ;THROW
                    END CATCH

        ----执行成功
        RETURN 0
    END

END

调用存储过程

DECLARE @status INT
EXEC @status=Pro_score_throw @Option='UpdateScore',@name='chen',@months=10,@score=40
SELECT @status

5.存储过程调用存储过程

CREATE TABLE Tbtran
(Id INT NOT NULL PRIMARY KEY,
Name VARCHAR(10)
)

INSERT INTO Tbtran(Id,Name) VALUES(1,'a'),(2,'b')

创建存储过程

CREATE PROC SpChangeTranCall
(@Option VARCHAR(50),
@Id INT =NULL,
@Name VARCHAR(50)=NULL OUTPUT
)
AS
DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
DECLARE @ReturnNum  INT ---Return返回值
DECLARE @OutputName VARCHAR(50)
BEGIN
SET XACT_ABORT ON   
IF @OPTION='a'
   BEGIN
           SELECT 
                @Name=Name
           FROM Tbtran
           WHERE Id=@Id

           IF @Name='a'
           BEGIN
                RETURN 2
           END
           IF @Name='b'
           BEGIN
                RETURN 3
           END

          INSERT INTO Tbtran(Id) VALUES(1)
    END
IF @OPTION='b'
   BEGIN
        BEGIN TRY  
            BEGIN TRAN  

            INSERT INTO Tbtran(Id,Name) VALUES(3,'c');

            ----调用存储过程,内部存储过程不使用事务
            EXEC @ReturnNum=SpChangeTranCall @OPTION='a',@Id=@Id,@Name=@OutputName OUTPUT
            ---接收内部存储过程OUTPUT返回值
            SELECT @OutputName
            ---判断调用存储过程是否执行成功
            IF @ReturnNum>0
            BEGIN
                 ROLLBACK TRAN
                 RETURN  @ReturnNum
            END

            COMMIT TRAN 
            ---成功标志,放在COMMIT后
            RETURN 0 
        END TRY  
        BEGIN CATCH  
            IF @@TRANCOUNT > 0
                ROLLBACK TRAN
            SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); 
            RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) 
            ---失败标志,放在最后
            RETURN 1
        END CATCH 
    END 
END

调用存储过程

DECLARE @a INT
SET @a=-2
EXEC @a=SpChangeTranCall @OPTION='b',@Id=1
SELECT @a AS ReturnNum

存储过程调用存储过程事务的三种处理方法:

1.内部存储过程不要包含事务，因为内部ROLLBACK会直接回滚到外部的BEGIN TRAN导致外部的ROLLBACK没有对应的COMMIT;

2.还有一种方法是在调用内部存储过程之前使用保存点“SAVE TRAN TRAN1”,同时内部存储过程的ROLLBACK TRAN必须指定事务保存点，例如“ROLLBACK TRAN TRAN1”,这样内部存储过程回滚就只会回滚到保持点.

3.在外部存储过程的CATCH块的ROLLBACK前加上IF @@TRANCOUNT > 0判断条件

如果不需要使用RETURN 1来标志执行失败，可以使用THROW来替代RAISERROR来接受返回的错误值

ALTER PROC SpChangeTranCall
(@Option VARCHAR(50),
@Id INT =NULL,
@Name VARCHAR(50)=NULL OUTPUT
)
AS
DECLARE @ReturnNum  INT ---Return返回值
DECLARE @OutputName VARCHAR(50)
BEGIN
SET XACT_ABORT ON   
IF @OPTION='a'
   BEGIN
           SELECT 
                @Name=Name
           FROM Tbtran
           WHERE Id=@Id

           IF @Name='a'
           BEGIN
                RETURN 2
           END
           IF @Name='b'
           BEGIN
                RETURN 3
           END

          INSERT INTO Tbtran(Id) VALUES(1)
    END
IF @OPTION='b'
   BEGIN
        BEGIN TRY  
            BEGIN TRAN  

            INSERT INTO Tbtran(Id,Name) VALUES(3,'c');

            ----调用存储过程,内部存储过程不使用事务
            EXEC @ReturnNum=SpChangeTranCall @OPTION='a',@Id=@Id,@Name=@OutputName OUTPUT
            ---接收内部存储过程OUTPUT返回值
            SELECT @OutputName
            ---判断调用存储过程是否执行成功
            IF @ReturnNum>0
            BEGIN
                 ROLLBACK TRAN
                 RETURN  @ReturnNum
            END

            COMMIT TRAN 
            ---成功标志,放在COMMIT后
            RETURN 0 
        END TRY  
        BEGIN CATCH  
            IF @@TRANCOUNT > 0
                ROLLBACK TRAN
            ;THROW
        END CATCH 
    END 
END

调用存储过程

DECLARE @a INT
SET @a=-2
EXEC @a=SpChangeTranCall @OPTION='b',@Id=3
SELECT @a AS ReturnNum

注意：THROW接收的返回错误行是准确的错误行，而RAISERROR返回的错误行是不正确的。

6.事务嵌套事务的理解

---事务1
BEGIN TRAN
        ---事务2
        BEGIN TRAN

        COMMIT TRAN /ROLLBACK TRAN 

COMMIT TRAN /ROLLBACK TRAN

对于事务嵌套事务，事务2的ROLLBACK操作会直接回滚到事务1的BEGIN TRAN，会导致事务1的ROLLBACK没有对应的BEGIN TRAN。处理方法可以在调用事务2之前定义一个事务保存点或者在事务1的ROLLBACK前加上IF @@TRANCOUNT > 0判断条件是否存在事务需要回滚。

7.SET XACT_ABORT ON

并不是所有的错误都能被CATCH所接收。对于严重级别为0-10（信息性消息）和20-25（致命的消息）是不能被CATCH所接收的，这时如果在事务中遇到了这类的报错那么通用会导致事务处理打开状态，这时就需要开启XACT_ABORT。当开启XACT_ABORT后只要代码中存在报错就会执行回滚操作，而不管错误的级别。例如：

CREATE TABLE [dbo].[AA](
    [id] [int] NULL
) ON [PRIMARY]
GO
CREATE PROC Pro_bb
(@Option VARCHAR(50))
AS
BEGIN
IF @OPTION='a'
   BEGIN
       TRUNCATE TABLE AA;
       SELECT * FROM AA;
        ----事务1
        BEGIN TRY  
            BEGIN TRAN  
                 INSERT INTO AA SELECT 2  
                 SELECT * FROM AA;
                 INSERT INTO #BB SELECT 1 
            COMMIT TRAN;  
        END TRY  
        BEGIN CATCH  
            IF @@TRANCOUNT > 0
            ROLLBACK TRAN;  
            ;THROW
        END CATCH 
    END
END

由于临时表#BB不存在，导致插入报错，但是严重级别又小于11导致CATCH接收不到错误，这时查看发现事务处于打开状态，而且表AA也被锁住。

EXEC Pro_bb @OPTION='a';
DBCC OPENTRAN;

加上事务前加上 SET XACT_ABORT ON

ALTER TABLE [dbo].[AA](
    [id] [int] NULL
) ON [PRIMARY]
GO
CREATE PROC Pro_bb
(@Option VARCHAR(50))
AS
BEGIN
IF @OPTION='a'
   BEGIN
   SET XACT_ABORT ON  
       TRUNCATE TABLE AA;
       SELECT * FROM AA;
        ----事务1
        BEGIN TRY  
            BEGIN TRAN  
                 INSERT INTO AA SELECT 2  
                 SELECT * FROM AA;
                 INSERT INTO #BB SELECT 1 
            COMMIT TRAN;  
        END TRY  
        BEGIN CATCH  
            IF @@TRANCOUNT > 0
            ROLLBACK TRAN;  
            ;THROW
        END CATCH 
    END
END

再次执行

EXEC Pro_bb @OPTION='a';
DBCC OPENTRAN;

没有处于打开的事务而且事务也执行了回滚操作。

总结

1.THROW后面的语句不会被执行；如果不需要Return 0和Return 1来作为成功失败的标志那么可以使用THROW来替代RAISERROR，否则使用RAISERROR。不要使用THROW抛错又使用RAISERROR来接收错误，在事务嵌套事务的写法中如果内部事务使用RAISERROR来接收THROW返回的报错不会执行后面的ROLLBACK。

2.建议在ROLLBACK前统一加上IF @@TRANCOUNT > 0判断条件，这样可以避免因为内部的ROLLBACK回滚或者RETURN操作导致ROLLBACK没有对应的COMMIT。

3.CATCH只是用来处理TRY报错之后的逻辑，不要认为代码执行到了CATCH的ROLLBACK就会结束处理，除非是在ROLLBACK后加入了RETURN或者THROW之类的中断代码执行的命令，否则代码还将继续执行ROLLBACK之后的代码甚至END CATCH之后的代码（如果存在）。

4.如果存储过程调用存储过程，内部的存储过程不使用事务也不需要使用TRY来接收错误，可以参考上面的“5.存储过程调用存储过程”。

5.虽然OUTPUT也能用来接收存储过程的执行情况，但是RETURN的作用可以跳出存储过程不执行后面的代码，所以避免即使用RETURN作为执行成功失败的标志又使用OUTPUT来标志执行成功和失败的标记，OUTPUT更多用来返回内部存储过程执行结果的值而不是用来做成功失败的标志。

备注：

作者：pursuer.chen

博客：http://www.cnblogs.com/chenmh

本站点所有随笔都是原创，欢迎大家转载；但转载时必须注明文章来源，且在文章开头明显处给明链接，否则保留追究责任的权利。

《欢迎交流讨论》

posted @ 2017-11-20 09:37 pursuer.chen 阅读(8902) 评论(6) 编辑收藏举报

刷新页面返回顶部

pursuer.chen

LIVE AND LEARN

SQL Server 存储过程的运用

概述

1.插入测试数据

2.THROW

3.sp_addmessage

调用存储过程

1.查询存储过程

3.修改存储过程

4.手动抛错中断

6.事务嵌套事务的理解

7.SET XACT_ABORT ON

总结

公告