事务控制及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】错误处理与相关函数
官网查询：https://docs.microsoft.com/zh-cn/sql/t-sql/functions/error-transact-sql?view=sql-server-ver15
【2.1】全局变量与错误处理函数
（1）@@ERROR ：当前一个语句遇到错误，则返回错误号，否则返回0。需要注意的是@ERROR在每一条语句执行后会被立刻重置，因此应该在要验证的语句执行后检查数值或者是将它保存到局部变量中以备将来使用。
（2）@@ROWCOUNT：返回当前一个语句影响的行数，需要注意的是它在每一条语句执行后会被立刻重置（包含其本身），因此应该在要验证的语句执行后检查数值或者是将它保存到局部变量中以备将来使用。
（3）@@TRANCOUNT：当前事务数量
（4）@@spid：当前事务的系统线程ID
【2.2】错误严重性级别（error_severity()函数可以查到）
sql server 错误等级编号为0~25 之间的int数字
通常分为以下三种级别类型
- 提示级别：等级号0~9 不会在查询分析器里面弹出错误信息，会返回提示性信息
- 错误警告级别:等级号=10 表示客户端输入信息导致错误而产生的相关错误警告信息
- 错误级别：等级号11~19 mssql会返回错误信息给客户端
- 严重级别：等级号20~25 mssql 会强制关闭当前连接 终止此错误
【2.3】raiserror 详解
深入参考：https://www.cnblogs.com/xugang/archive/2011/04/09/2010216.html
RAISERROR ( { msg_id | msg_str | @local_variable }
{ ,severity ,state }
[ ,argument [ ,...n ] ]
)
[ WITH option [ ,...n ] ]
{ ,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；
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；
基本演示
DECLARE @raiseErrorCode nvarchar(50)
SET @raiseErrorCode = CONVERT(nvarchar(50), YOUR UNIQUEIDENTIFIER KEY)
RAISERROR('%s INVALID ID. There is no record in table',16,1, @raiseErrorCode)
【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来确定事务被打开了多长时间、事务的类型以及事务的状态。 -- 使用这个故障排除技术可以回到应用程序去查明查询调用的被抛弃的事务(打开但从未提交)，以及那些运行时间太长或对于应用程序来说是不必要的不恰当事务。
【9】SET XACT_ABORT 参数在存储过程与事务中的作用
当 SET XACT_ABORT 为 ON 时，如果执行 Transact-SQL 语句产生运行时错误，则整个事务将终止并回滚。 当 SET XACT_ABORT 为 OFF 时，有时只回滚产生错误的 Transact-SQL 语句，而事务将继续进行处理。 如果错误很严重，那么即使 SET XACT_ABORT 为 OFF，也可能回滚整个事务。 OFF 是默认设置。
参考文件：
