(4.38)sql server中的事务控制及try cache错误处理【最佳实践】 sql server全局变量
一、事务控制
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
{ ,severity ,state }
[ ,argument [ ,...n ] ]
)
[ WITH option [ ,...n ] ]
简要说明一下:
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)
三、自定义错误(抛出异常)
本部分转自: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】
【9】SET XACT_ABORT 参数在存储过程与事务中的作用
当 SET XACT_ABORT 为 ON 时,如果执行 Transact-SQL 语句产生运行时错误,则整个事务将终止并回滚。 当 SET XACT_ABORT 为 OFF 时,有时只回滚产生错误的 Transact-SQL 语句,而事务将继续进行处理。 如果错误很严重,那么即使 SET XACT_ABORT 为 OFF,也可能回滚整个事务。 OFF 是默认设置。
【10】(触发器实践)给提示信息但不影响程序运行
set nocount on go if object_id('temp20201126') is not null drop table temp20201126 go create table temp20201126(id int); go if object_id('tri_insert_temp20201126') is not null drop trigger tri_insert_temp20201126 go create trigger tri_insert_temp20201126 on temp20201126 after insert as begin declare @id int select @id = id from inserted if @id>=10 begin raiserror('您插入的id值》10',16,1) print('raiserror之后') end end go select * from temp20201126 insert into temp20201126 values(13) select * from temp20201126
参考文件:
我的同类文档:https://www.cnblogs.com/gered/p/8746008.html#autoid-8-0-0