事务控制及try catch

【1】事务控制

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

【2】错误处理函数

其他：

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

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

（3）@@TRANCOUNT：当前事务数量

（4）@@spid：当前事务的系统线程ID

【3】自定义错误（抛出异常）

本部分转自：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】结论

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

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

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

　　具体见【5】

【8】常见事务DMV

-- 3.1.3 通过会话查询事务信息 ( P104 )
-- 这个技巧演示了如何找出更多活动事务的信息。为了进行演示，将描述一个常见的场景：你的应用程序会遇到很多高持时间的阻塞。
-- 你被告知这个应用程序经常在每个查询之前打开显式的事务。

-- 为了说明这个场景，将执行如下的SQL(表示应用程序代码引发了并发性问题)：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN

SELECT *
FROM HumanResources.Department

INSERT HumanResources.Department
(Name, GroupName)
VALUES('Test','OA')

-- 在另一个/新的SQL Server Management Studio 查询窗口，通过查询sys.dm_tran_session_transactions动态管理视图(DMV)来识别所打开的事务：

SELECT session_id, transaction_id, is_user_transaction, is_local
FROM sys.dm_tran_session_transactions
WHERE is_user_transaction = 1

-- 这个查询返回如下内容(你实际的会话ID和事务ID会有所不同)：
-----------------------------------------------------------------------------------------
session_id    transaction_id    is_user_transaction    is_local
54    47941    1    1
-----------------------------------------------------------------------------------------

-- 现在有会话ID可以使用了，可以通过查询 sys.dm_exec_connections 和 sys.dm_exec_sql_text 来挖掘最近执行的查询的详细信息：

SELECT s.text
FROM sys.dm_exec_connections c
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) s
WHERE session_id = 54

-----------------------------------------------------------------------------------------
text
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE    BEGIN TRAN    SELECT *  FROM HumanResources.Department    INSERT HumanResources.Department  (Name, GroupName)  VALUES('Test','OA')
-----------------------------------------------------------------------------------------

-- 因为也从对sys.dm_tran_session_transactions 的第一个查询中得到了事务的ID，所以可以使用sys.dm_tran_active_transactions 来了解更多事务本身的内容：

SEELCT transaction_begin_time,
  CASE transaction_type
    WHEN 1 THEN 'Read/write transaction'
    WHEN 2 THEN 'Read-only transaction'
    WHEN 3 THEN 'System transaction'
    WHEN 4 THEN 'Distributed transaction'
  END tran_type,
  CASE transaction_state
    WHEN 0 THEN 'not been completely initialized yet'
    WHEN 1 THEN 'initialized but not started'
    WHEN 2 THEN 'active'
    WHEN 3 THEN 'ended (read-only transaction)'
    WHEN 4 THEN 'commit initiated for distrubuted transaction'
    WHEN 5 THEN 'transaction prepared and waiting resolution'
    WHEN 6 THEN 'committed'
    WHEN 7 THEN 'being rolled back'
    WHEN 8 THEN 'been rolled back'
  END tran_state
FROM sys.dm_tran_active_transactions
WHERE transaction_id = 47941

-- 这个查询返回了事务开始时间、事务类型以及事务状态：
-----------------------------------------------------------------------------------------
transaction_begin_time    tran_type    tran_state
2010-08-13 11:19:08.750    Read/write transaction    active
-----------------------------------------------------------------------------------------

-- 解析
-- 这个技巧演示了如何使用各种DMV来排除故障和调查长时间运行的活动事务。你决定使用哪些列取决于你要排除什么故障。在这个场景中，我使用如下的故障排除步骤：
-- *1) 查询 sys.dm_tran_session_transactions 来显示会话ID和事务ID(各个事务的标识符)之间的映射；
-- *2) 查询 sys.dm_exec_connections 和 sys.dm_exec_sql_text 来查找会话最新执行的命令(引用 most_recent_sql_handle 列)；
-- *3) 最后，查询sys.dm_tran_active_transactions来确定事务被打开了多长时间、事务的类型以及事务的状态。

-- 使用这个故障排除技术可以回到应用程序去查明查询调用的被抛弃的事务(打开但从未提交)，以及那些运行时间太长或对于应用程序来说是不必要的不恰当事务。

参考文件：

（4.37）sql server中的系统函数