乘风原创程序

  • SQL Server异常代码处理的深入讲解
  • 2020/8/17 8:33:01
  • 前言

    SQL Server使用TRY...CATCH 结构实现TSQL语句的错误处理,TRY命令负责监控语句执行的情况,如果有TSQL语句发生异常,并且严重级别(Severity Level)大于10,并且小于20,那么CATCH命令会捕获到异常的错误。

    BEGIN TRY 
       { sql_statement | statement_block } 
    END TRY 
    BEGIN CATCH 
       [ { sql_statement | statement_block } ] 
    END CATCH

    数据库开发工程师需要把一条或多条语句写入到TRY代码块中,如果TRY代码块中的代码在执行过程中发生错误,那么在错误发生的点之后的代码不再执行,程序的控制权转移到CATCH代码块块中。如果TRY代码块没有发生错误,那么不会执行CATCH代码块,程序的控制权转移到END CATCH之后的语句。

    注意,在CATCH代码块中捕获的异常不会返回给调用程序,如果需要把错误消息返回给调用程序,需要在CATCH代码块中使用THROW(或RAISERROR,不推荐使用)命令显式抛出错误。

    一,获取异常消息

    在TSQL中,使用TRY和 CATCH编写异常处理代码块,在CATCH子句中,使用以下函数,能够获取异常发生时的信息。

    --返回发生错误的代码行号(LineNumber)
    ERROR_LINE ( ) 
    --返回错误号(ErrorNumber)
    ERROR_NUMBER ( ) 
    @@ERROR 
    --返回错误消息(ErrorMessage)
    ERROR_MESSAGE ( ) 
    --返回发生错误的SP Name
    ERROR_PROCEDURE ( ) 
    --返回错误的严重度(Error Severity)
    ERROR_SEVERITY ( ) 
    --返回错误的状态(Error State)
    ERROR_STATE()

    SQL Server抛出的一个错误,通常包括错误代码(Error Number)、严重级别(Severity Level)、错误状态(Error State)和错误消息(Error Message)等信息。

    1,错误代码

    错误代码,可以由变量@@ERROR 和函数ERROR_NUMBER()获得,用于返回上一条语句的错误代码,该代码唯一标识该错误。

    2,错误的严重级别

    错误的严重程序(Severity Level)共有24个级别,表明SQL Sever遇到问题的类型,Severity Level是一个int类型,可以由函数ERROR_SEVERITY() 返回,数值越大,说明问题越严重。

    按照错误对系统的影响程序,把严重级别分为四组:

    • 0-10:信息,可以认为是warning
    • 11-16:错误,是用户代码导致的
    • 17-19:非常严重的错误,只能由系统管理员来修复
    • 20-24:致命的错误,可能导致整个系统无法正常使用

    从17-19,错误不能被用户修正,只能由系统管理员来修复问题。

    从20-24,这个级别的错误遇到的情况比较少,一旦遇到,那么基本上表明整个数据库系统遇到了非常严重的错误:

    3,错误状态

    错误状态(Error State)是用户自定义的编码,用于使开发者能够轻易识别引起异常的确切位置。

    4,错误消息

    错误消息,是关于错误的描述性文本,可以是SQL Server系统预定义的错误信息,也可以是THROW命令抛出的用户自定义的文本。

    二,抛出异常消息

    在SQL Server 2012及之后的版本中,使用 Throw 关键字代替RAISERROR,用于抛出异常,并将执行控制权转移到Catch 代码块。

    THROW [error_number, error_message, error_state];

    参数注释:

    • error_number:错误代码,是一个int类型,数值必须大于5000,小于 2147483647,这是用户自定义的错误代码。
    • error_message:错误消息,类型是nvarchar(2048)
    • state:跟错误相关联的一个state,类型是tinyint,取值范围是:0-255

    注意:在THROW语句之前的语句,必须以分号; 结尾。

    当THROW语句用于抛出自定义的异常时,severty level 常常被设置为默认的16;当THROW用于re-throw,此时THROW 没有任何参数,处于CATCH代码块中,仅仅用于把CATCH捕获的异常重新抛出,severty level,state,错误消息跟原始异常相同。

    例子1,抛出自定义的异常:

    BEGIN TRY 
      SELECT 1/0
    END TRY 
    BEGIN CATCH 
      ;THROW 51000, 'Divide by zero error encountered', 1;  
    END CATCH;

    SQL Server抛出的异常消息是,自定义的错误代码是51000,严重级别(Severity Level)是16,错误状态是1,错误行是5:

    Msg 51000, Level 16, State 1, Line 5
    Divide by zero error encountered

    例子2,重抛异常,把系统检测到的错误从Catch代码块中抛出:

    BEGIN TRY 
      SELECT 1/0
    END TRY 
    BEGIN CATCH 
      ;THROW;  
    END CATCH;

    SQL Server抛出的异常消息是,错误代码是8134,严重级别(Severity Level)是16,错误状态是1,错误行是2:

    Msg 8134, Level 16, State 1, Line 2
    Divide by zero error encountered.

    三,不受TRY...CATCH结构影响的错误

    TRY...CATCH 只捕获严重级别从11到19的错误,不会捕获严重级别是1-10,20-24的错误。

    如果session会系统管理员使用KILL命令杀掉,那么TRY...CATCH结构不会捕获。

    四,在事务中处理异常

    如果在TRY代码块生成的错误,导致当前事务的状态变成无效,那么该事务就是不可提交的事务(uncommittable transaction)。一个不可提交的事务,只能执行read操作,或者回滚(ROLLBACK TRANSACTION),不能执行TSQL语句来执行写操作,该事务也不能提交。函数XACT_STATE() 返回-1表示,当前的事务是不可提交的事务;返回1表示当前的事务是可以提交的。数据库开发人员需要通过XACT_STATE() 来对事务执行提交或回滚的操作。

    例如,在事务中处理异常,可以参考以下代码,在实际应用程序,可以把异常信息记录在数据表中,便于进行故障排除:

    -- SET XACT_ABORT ON will render the transaction uncommittable when the constraint violation occurs.
    SET XACT_ABORT ON; 
     
    BEGIN TRY 
      BEGIN TRANSACTION; 
        -- A FOREIGN KEY constraint exists on this table. This statement will generate a constraint violation error.
        DELETE FROM Production.Product 
        WHERE ProductID = 980; 
      -- If the delete operation succeeds, commit the transaction. The CATCH block will not execute.
      COMMIT TRANSACTION; 
    END TRY 
    BEGIN CATCH 
      -- Test XACT_STATE for 0, 1, or -1. 
      -- If 1, the transaction is committable. 
      -- If -1, the transaction is uncommittable and should be rolled back.
      -- XACT_STATE = 0 means there is no transaction and a commit or rollback operation would generate an error.
     
      -- Test whether the transaction is uncommittable.
      IF (XACT_STATE()) = -1 
      BEGIN 
        --Logging Exception info, as the transaction is in an uncommittable state. Rolling back transaction.
        SELECT 
          ERROR_NUMBER() AS ErrorNumber, 
          ERROR_SEVERITY() AS ErrorSeverity, 
          ERROR_STATE() AS ErrorState, 
          ERROR_PROCEDURE() AS ErrorProcedure, 
          ERROR_LINE() AS ErrorLine, 
          ERROR_MESSAGE() AS ErrorMessage;  
        ROLLBACK TRANSACTION; 
      END; 
      -- Test whether the transaction is active and valid. 
      IF (XACT_STATE()) = 1 
      BEGIN 
        --'The transaction is committable. Committing transaction.' 
        COMMIT TRANSACTION;   
      END; 
    END CATCH;

    总结