• (4.38)sql server中的事务控制及try cache错误处理


    一、事务控制

    BEGIN TRY
    
    BEGIN TRAN;
    DECLARE @aaa NVARCHAR(MAX);
    SET @aaa = 9 / 0;
    
    COMMIT TRAN;
    END TRY
    BEGIN CATCH
    --【错误】--
    DECLARE
    @ErrorMessage NVARCHAR(MAX)
    , @ErrorSeverity INT
    , @ErrorState INT
    , @exception NVARCHAR(255);
    SELECT
    @ErrorMessage = ERROR_MESSAGE()
    , @ErrorSeverity = ERROR_SEVERITY()
    , @ErrorState = ERROR_STATE();
    PRINT '【!ERROR!】';
    SET @exception
    = '(State ' + CAST(@ErrorState AS NVARCHAR(20)) + ', Severity ' + CAST(@ErrorSeverity AS NVARCHAR(20)) + ') '
    + @ErrorMessage;
    PRINT @exception;
    ROLLBACK;
    PRINT '回滚成功'
    END CATCH;

    -- 结果
    
    

     相关案例:

    IF EXISTS (SELECT * FROM SYSOBJECTS WHERE name='my_sp_test' AND TYPE='P') BEGIN
        DROP PROCEDURE my_sp_test;
    END;
    GO
    create procedure my_sp_test @i int, @outstr varchar(100) out as
    begin try
        declare @j int;
        if @i<10 begin
          set @outstr = 'system exception.';
          set @j = 10/0;  -- 因为被除数为0,所以这里将会抛出一个系统的异常
        end
        else begin
          set @j = @i;
          set @outstr = 'customer exception';
          -- 抛出自定义的异常,在最后的catch块中统一处理异常
          RAISERROR (66666, -- Message id.
               16, -- Severity,
               1 -- State,
               ) ;    
        end;
    end try
    begin catch 
        if @@ERROR=66666 begin  -- 通过@@ERROR的值来判断是否是自定义的异常
            set @outstr = @outstr  + '---------------- customer exception';
        end;
        return;
    end catch;
    go

    二、错误处理函数

    其他:

    (1)@@ERROR :当前一个语句遇到错误,则返回错误号,否则返回0。需要注意的是@ERROR在每一条语句执行后会被立刻重置,因此应该在要验证的语句执行后检查数值或者是将它保存到局部变量中以备将来使用。

    (2)@@ROWCOUNT:返回当前一个语句影响的行数,需要注意的是它在每一条语句执行后会被立刻重置(包含其本身),因此应该在要验证的语句执行后检查数值或者是将它保存到局部变量中以备将来使用。

    (3)@@TRANCOUNT:当前事务数量

    (4)@@spid:当前事务的系统线程ID

    三、自定义错误(抛出异常)

    本部分转自:https://www.cnblogs.com/weixing/p/3930162.html

    BEGIN TRY
    RAISERROR ('Error raised in TRY block.', -- Message text.
    16, -- Severity.
    1 -- State.
    );
    END TRY
    BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;
    
    SELECT 
    @ErrorMessage = ERROR_MESSAGE(),
    @ErrorSeverity = ERROR_SEVERITY(),
    @ErrorState = ERROR_STATE();
    print @errorMessage
    RAISERROR (@ErrorMessage, -- Message text.
    @ErrorSeverity, -- Severity.
    @ErrorState -- State.
    );
    END CATCH;

    详细说明如下:

    raiserror 的作用: raiserror 是用于抛出一个错误。[ 以下资料来源于sql server 2005的帮助 ] 
    其语法如下:

    RAISERROR ( { msg_id | msg_str | @local_variable }
    { ,severity ,state }
    [ ,argument [ ,...n ] ]
    )
    [ WITH option [ ,...n ] ]

    简要说明一下:

    第一个参数:{ msg_id | msg_str | @local_variable }
    msg_id:表示可以是一个sys.messages表中定义的消息代号;
    使用 sp_addmessage 存储在 sys.messages 目录视图中的用户定义错误消息号。
    用户定义错误消息的错误号应当大于 50000。

    msg_str:表示也可以是一个用户定义消息,该错误消息最长可以有 2047 个字符;
    (如果是常量,请使用N'xxxx',因为是nvarchar的)
    当指定 msg_str 时,RAISERROR 将引发一个错误号为 5000 的错误消息。

    @local_variable:表示也可以是按照 msg_str 方式的格式化字符串变量。

    第二个参数:severity
    用户定义的与该消息关联的严重级别。(这个很重要)
    任何用户都可以指定 0 到 18 之间的严重级别。
    [0,10]的闭区间内,不会跳到catch;
    如果是[11,19],则跳到catch;
    如果[20,无穷),则直接终止数据库连接;

    第三个参数:state
    如果在多个位置引发相同的用户定义错误,
    则针对每个位置使用唯一的状态号有助于找到引发错误的代码段。

    介于 1 至 127 之间的任意整数。(state 默认值为1)
    当state 值为 0 或大于 127 时会生成错误!

    第四个参数:argument
    用于代替 msg_str 或对应于 msg_id 的消息中的定义的变量的参数。

    第五个参数:option
    错误的自定义选项,可以是下表中的任一值:
    LOG :在错误日志和应用程序日志中记录错误;
    NOWAIT:将消息立即发送给客户端;
    SETERROR:将 @@ERROR 值和 ERROR_NUMBER 值设置为 msg_id 或 50000;

    【4】实践参考(存储过程)

    任何一个地方出错,整个try块就不执行了。

    CREATE DATABASE TEST
    
    USE TEST
    /*=====================================================
    相关错误消 息如下:
    
    ERROR_NUMBER() 返回错误号。
    
    ERROR_SEVERITY() 返回严重性。
    
    ERROR_STATE() 返回错误状态号。
    
    ERROR_PROCEDURE() 返回出现错误的存储过程或 触发器的名称。
    
    ERROR_LINE() 返回导致错误的例程中的行 号。
    
    ERROR_MESSAGE() 返回错误消息的完整文本。
    ========================================================*/
    CREATE TABLE LogTable
    (
      ID              int identity(1,1),--错误序号
      ErrorNumber     int,--错误号
      ErrorSeverity   int,--严重性
      ErrorState      int,--错误状态号
      ErrorProducure  varchar(200),--出现错误的存储过程或 触发器的名称
      ErrorLine       int,--导致错误的例程中的行号
      ErrorMessage    varchar(200)--错误消息的完整文本
    )
    
    --===============除数不为0的异常捕获=================--
    IF EXISTS (SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'getWrong') AND xtype='P')
    DROP PROC getWrong
    go
    CREATE PROC getWrong
    AS
    BEGIN
         -----------------制造异常
         BEGIN TRY
         SELECT 1/0;
         -----------------捕获异常
         END TRY
         BEGIN CATCH
            INSERT INTO LogTable values(ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_PROCEDURE(), ERROR_LINE() ,ERROR_MESSAGE())
         END CATCH
    END
    
    --执行存储过程
    EXEC getWrong
    --查看日志表
    select * from LogTable
    --查看系统日志表
    SELECT * FROM sys.messages WHERE message_id=8134 AND language_id=2052

      

    【5】我的最佳实践(批处理)

    图中有SQL出错,除非是语法错误,否则即使有逻辑报错,也会继续顺序执行后面的语句,比如:

      

    那么我的最佳实践,就是为了捕捉批处理中,遇到的逻辑错误信息。 

    declare @error int,@error_msg varchar(200),@db_a varchar(100),@sql varchar(100),@msg varchar(1000)
    set @db_a='test'
    if not exists(select 1 from db_del.sys.tables where name='unite_log' ) 
    begin
    create table db_del..unite_log(
    id int identity(1,1) primary key,
    msg_type char(10) default('normal'),
    msg varchar(4000),recoveryTime datetime,
    recordday as convert(char(10),
    recoveryTime,120),
    db varchar(100));
    end
    
    set @sql='select 1/0'
    --exec(@sql)
    select 1/0 set @error=@@error; select @error_msg=isnull(text,0) from sys.messages where message_id=@error and language_id=2052; if @error=0 insert into db_del..unite_log([msg],[recoveryTime],[db]) values(@Msg+' | OK',getdate(),@db_a); else insert into db_del..unite_log([msg_type],[msg],[recoverytime],[db]) values('error',@sql+' | '+@error_msg,getdate(),@db_A);

      
    select * from db_del..unite_log
      

     可以根据参考文件中的获取更多信息信息,比如数据库名、登录名、主机名 等等

     批处理中也可以这样

    begin
    declare @temp_spid int
      BEGIN TRY
         SELECT 1/0;
         -----------------捕获异常
         END TRY
         BEGIN CATCH
            set @temp_spid=@@spid
            select ERROR_NUMBER() as 'error_num',
                    ERROR_SEVERITY() as 'ERROR_SEVERITY',
                    ERROR_STATE() as 'ERROR_STATE',
                    ERROR_PROCEDURE() as 'ERROR_PROCEDURE', 
                    ERROR_LINE() as 'ERROR_LINE' ,
                    ERROR_MESSAGE() as 'ERROR_MESSAGE'
            ,(select q.text from sys.dm_exec_requests r  cross  apply 
                sys.dm_exec_sql_text(r.sql_handle) q where session_id=@@spid) as 'Error_TEXT'
         END CATCH
    
        select 1
    end
        
    
    
    
     

    【6】我的最佳实践(存储过程)

    if db_id('test') is null  
        create database test;
    else
        use test;
    go
    
    /*=====================================================
    相关错误消 息如下:
    
    ERROR_NUMBER() 返回错误号。
    ERROR_SEVERITY() 返回严重性。
    ERROR_STATE() 返回错误状态号。
    ERROR_PROCEDURE() 返回出现错误的存储过程或 触发器的名称。
    ERROR_LINE() 返回导致错误的例程中的行 号。
    ERROR_MESSAGE() 返回错误消息的完整文本。
    ========================================================*/
    if object_id('LogTable') is null
    CREATE TABLE LogTable
    (
      ID              int identity(1,1),--错误序号
      ErrorNumber     int,--错误号
      ErrorSeverity   int,--严重性
      ErrorState      int,--错误状态号
      ErrorProducure  varchar(200),--出现错误的存储过程或 触发器的名称
      ErrorLine       int,--导致错误的例程中的行号
      ErrorMessage    varchar(200),--错误消息的完整文本
      ERROR_TEXT      varchar(8000),
      COMMAND_TYPE    varchar(50),
      LOGIN_NAME      varchar(100),
      DB_DBO          varchar(100),
      DB_NAME          varchar(100)
    )
    if object_id('temp1') is null
        create table temp1( id int);
    go
    --===============除数不为0的异常捕获=================--
    IF EXISTS (SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'getWrong') AND xtype='P')
        DROP PROC getWrong
    go
    CREATE PROC getWrong
    AS
    BEGIN
         -----------------制造异常
        
         BEGIN TRY
            insert into test..temp1 values(11);
             SELECT 1/0;
            insert into test..temp1 values(12);
        
         -----------------捕获异常
         END TRY
         BEGIN CATCH
            insert into test.dbo.LogTable
            select 
                    ERROR_NUMBER() as 'error_num',
                    ERROR_SEVERITY() as 'ERROR_SEVERITY',
                    ERROR_STATE() as 'ERROR_STATE',
                    ERROR_PROCEDURE() as 'ERROR_PROCEDURE', 
                    ERROR_LINE() as 'ERROR_LINE' ,
                    ERROR_MESSAGE() as 'ERROR_MESSAGE',
                    q.text as 'ERROR_TEXT',
                    r.command 'COMMAND_TYPE',
                    system_user as 'LOGIN_NAME',
                    user_name() as 'DB_DBO',
                    db_name(r.database_id) as 'DB_NAME'
                from sys.dm_exec_requests r  cross  apply 
                sys.dm_exec_sql_text(r.sql_handle) q 
                where session_id=@@spid
                
         END CATCH
        
        
        begin try 
         insert into test..temp1 values(3);
        end try
        begin catch
            insert into test.dbo.LogTable
            select 
                    ERROR_NUMBER() as 'error_num',
                    ERROR_SEVERITY() as 'ERROR_SEVERITY',
                    ERROR_STATE() as 'ERROR_STATE',
                    ERROR_PROCEDURE() as 'ERROR_PROCEDURE', 
                    ERROR_LINE() as 'ERROR_LINE' ,
                    ERROR_MESSAGE() as 'ERROR_MESSAGE',
                    q.text as 'ERROR_TEXT',
                    r.command,
                    system_user as 'login_name',
                    user_name() as 'DB_DBO',
                    db_name(r.database_id) as 'db_name'
                from sys.dm_exec_requests r  cross  apply 
                sys.dm_exec_sql_text(r.sql_handle) q 
                where session_id=@@spid
        
        end catch
        
    END
    go
    --执行存储过程
    EXEC getWrong
    go
    --查看日志表
    
    --查看系统日志表
    /*
        delete test..temp1;
        delete test..LogTable;
        select * from test..temp1
        select * from test..LogTable
    */
        
    
    

    上面的测试可知,try cache 会把自 错误行之后的所有语句都不执行,但错误行之前的语句还是执行成功了。

     

    【7】我的最佳实践(如何规避游标)

    --构造发放数据
    
        
        select  userid as '用户id',ceiling(sum(money)/1000.0)  as '需补偿次数',row_number() over(order by userid) as rn ,0 as flag
        into #temp1
        from db_tank..charge_money(nolock) 
        where date >='20200210 00:00:00'  AND  date <='20200210 16:10:00'
        group by userid
    
    
    
            
        --备份原始数据
            select * into db_del..userDate20200210
            from db_tank..sys_users_data t1    join #temp1 t2 on t1.userid = t2.[用户id] and t1.datatype = 6
    
    
        --declare
        declare @rn int,@rn_count int
        declare @userid int ,@add_num int ,@flag int
    
        --init
        set @rn=1
        select @rn_count = max(rn) from #temp1
    
        --main
        while @rn<=@rn_count
        begin
            select @userid = [用户id],@add_num = [需补偿次数],@flag = flag from #temp1 where rn=@rn
            IF @flag = 0
            begin
                update db_tank..sys_users_data
                set datab=datab+@add_num
                where userid=@userid 
                AND datatype=6
                
                update #temp1
                set flag=1
                where rn=@rn
            end
            set @rn=@rn+1
    
        end
        

    【8】结论

    (1)try cache 会把自 错误行之后的所有语句都不执行,但错误行之前的语句还是执行成功了。

       并且,如果一个事务中有多个try cache,如果不回滚那么依然会顺序执行。具体见【6】。如果要设置回滚 具体见 一

    (2)如果是单纯的批处理语句,如果有逻辑相关错误,后续的语句依然会执行。

      具体见【5】

    参考文件:

    (4.37)sql server中的系统函数

  • 相关阅读:
    java_十进制数转换为二进制,八进制,十六进制数的算法
    vim常用命令 vim键盘布局
    百度HTTPS加密搜索有什么用?
    delete
    hadoop2的automatic HA+Federation+Yarn配置的教程
    MinGW GCC下sleep()函数问题
    delete
    8天学通MongoDB——第一天 基础入门
    8天学通MongoDB——第六天 分片技术
    8天学通MongoDB——第五天 主从复制
  • 原文地址:https://www.cnblogs.com/gered/p/12371153.html
Copyright © 2020-2023  润新知