SQLserver 2012 增加了新的功能特性 throw ,一直没测试过,这两天又重新整理测试。之前测试过一次:qlServer try catch 捕获不到的一些错误及解决方法
更多概念可以参考:数据库引擎错误严重性
对于sql语句的异常,SqlServer 有 0~25 的严重级别定义:
0~10 :轻微级别
11~18:一般级别
19~25:严重级别
还想测试容易理解:
测试一:
-- 创建临时表方便测试
-- DROP TABLE #temp
CREATE TABLE #temp(ID INT IDENTITY(1,1),DTIME DATETIME)
-- 一般批处理操作
BEGIN
INSERT INTO #temp(DTIME)SELECT GETDATE()
SELECT 0+'A'
END
在这模块中,第一条语句执行成功了,临时表有一行数据。执行到下一个命令时 则出现错误,并终止程序运行。此时根据错误消息 Msg245 可查看系统错误消息模板。
select * from sys.messages where language_id=2052 AND message_id=245
这个批处理中并没有作为整个事务来处理,遇到错误即终止。
测试二:
-- 作为同一个事务处理
BEGIN TRAN
INSERT INTO #temp(DTIME)SELECT GETDATE()
SELECT 0+'A'
COMMIT TRAN
错误也是一样,但是整个事务被回滚了,临时表并没有数据插入。然而错误还是存在,没有被正确处理,有时候就很难定位错误。
测试三:
从 SQLserver 2005 开始,可以使用变量 @@error 来判断sql语句是否执行成功。
-- TRUNCATE TABLE #temp
BEGIN TRAN
INSERT INTO #temp(DTIME)SELECT GETDATE()
IF @@ERROR <> 0 ROLLBACK TRAN
SELECT 0+'A'
IF @@ERROR <> 0 ROLLBACK TRAN
COMMIT TRAN
错误还是一样,整个事务也被回滚了。 @@error 在此并没有用,因为此处错误直接是语句终止。
其实也可以这样写:
-- TRUNCATE TABLE #temp
BEGIN TRAN
INSERT INTO #temp(DTIME)SELECT GETDATE()
IF @@ERROR <> 0 GOTO LABEL
SELECT 0+'A'
IF @@ERROR <> 0 GOTO LABEL
COMMIT TRAN
LABEL:
ROLLBACK TRAN
测试四:
现在这种最常用的标准写法!
BEGIN TRAN
BEGIN TRY
INSERT INTO #temp(DTIME)SELECT GETDATE()
SELECT 0+'A'
COMMIT TRAN
END TRY
BEGIN CATCH
PRINT 'ERROR ' + CONVERT(VARCHAR(50), ERROR_NUMBER()) +
', SEVERITY ' + CONVERT(VARCHAR(5), ERROR_SEVERITY()) +
', STATE ' + CONVERT(VARCHAR(5), ERROR_STATE()) +
', PROCEDURE ' + ISNULL(ERROR_PROCEDURE(), '-') +
', LINE ' + CONVERT(VARCHAR(5), ERROR_LINE())+
CHAR(10) + ERROR_MESSAGE();
ROLLBACK TRAN
END CATCH
PRINT 'DONE'
查询临时表,并没有记录,因为整个事务已经回退了。此外可以自定义输出异常信息,语句并没有终止退出,还可以继续执行下面的语句,不影响程序的执行,但是事务中的操作已经撤销了。
测试五:
SQLserver 2005 还有抛出错误的语句:RAISERROR
测试轻微严重级别:
BEGIN TRAN
BEGIN TRY
INSERT INTO #temp(DTIME)SELECT GETDATE()
SELECT 0+'A'
COMMIT TRAN
END TRY
BEGIN CATCH
RAISERROR ('Error raised in TRY block.',10,1); --0~10
ROLLBACK TRAN
END CATCH
PRINT 'DONE'
RAISERROR 的第二个参数严重级别这里设置为10 ,严重性在 1~10 之间的消息的 @@ERROR 设置为 0,可以指定 0~18 之间的严重级别。 严重级别为10并不严重,只是提示。抛出信息的同时,整个事务因错误也进行了回滚,临时表并没有插入数据。错误也并没有导致查询终止,还可以继续往下进行。
现在设置中等严重级别:
BEGIN TRAN
BEGIN TRY
INSERT INTO #temp(DTIME)SELECT GETDATE()
SELECT 0+'A'
COMMIT TRAN
END TRY
BEGIN CATCH
RAISERROR ('Error raised in TRY block.',18,1); --11~18
ROLLBACK TRAN
END CATCH
PRINT 'DONE'
严重级别在11~18 之间,程序将终止运行,事务也会回滚,临时表没有数据。输出的错误消息为自定义的消息。
现在设置严重级别:严重级别则需写到日志中。
BEGIN TRAN
BEGIN TRY
INSERT INTO #temp(DTIME)SELECT GETDATE()
SELECT 0+'A'
COMMIT TRAN
END TRY
BEGIN CATCH
RAISERROR ('Error raised in TRY block.',25,1) WITH LOG
ROLLBACK TRAN
END CATCH
PRINT 'DONE'
此时异常终止,错误回滚,临时表都会被删除!查看日志,也记录抛出的信息:
exec xp_readerrorlog 0,1,NULL,NULL,'2015-09-10','2016-01-01','desc'
测试六:
与上面 测试五 类似,使用的仍是 RAISERROR ,但是是在 try 模块中使用。
BEGIN TRY
INSERT INTO #temp(DTIME)SELECT GETDATE()
RAISERROR ('Error raised in TRY block.',10, 1 );
-- RAISERROR ('Error raised in TRY block.',18, 1 );
-- RAISERROR ('Error raised in TRY block.',25, 1 )WITH LOG;
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT,@ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, @ErrorSeverity,@ErrorState );
ROLLBACK TRAN
END CATCH;
结果与上面运行结果是一样的,然而在 try 模块中抛出的错误信息,会在catch 模块中获取到。
测试七:使用 XACT_ABORT
查看 xact_abort 是否启用:
SELECT (CASE WHEN (16384 & @@OPTIONS) = 16384 THEN 'ON' ELSE 'OFF' END) AS XACT_ABORT;
SET XACT_ABORT ON
BEGIN TRAN
INSERT INTO #TEMP(DTIME) SELECT GETDATE()
SELECT 0 + 'A'
COMMIT TRAN
SET XACT_ABORT OFF
在设置 xact_abort 为 on 时,若事务内部执行不成功,整个事务将回滚。
测试八:throw
现在为SQLserver 2012 新增加的特性: throw!
BEGIN TRAN
BEGIN TRY
INSERT INTO #TEMP(DTIME) SELECT GETDATE()
SELECT 0 + 'A'
COMMIT TRAN
END TRY
BEGIN CATCH
PRINT 'In catch block.';
THROW --上个语句末尾需加分号";"
ROLLBACK TRAN
END CATCH
此处的 throw 是默认抛出系统错误消息。程序终止运行。
测试九:
使用 throw 抛出自定义消息:
BEGIN TRAN
BEGIN TRY
INSERT INTO #TEMP(DTIME) SELECT GETDATE()
SELECT 0 + 'A'
COMMIT TRAN
END TRY
BEGIN CATCH
THROW 60000, 'In catch block.', 1;
ROLLBACK TRAN;
END CATCH
可以定义错误号范围 50000 到 2147483647 。消息也可以自定义。
可以直接执行:
--直接执行
THROW 50000, 'THROW Test.', 1;
测试十:
格式化系统消息:
--构造消息/格式化消息
SELECT message_id,text FROM sys.messages WHERE language_id = 2052 AND message_id = 20009;
message_id = 20009 有2个参数,怎么调用呢?
使用 FORMATMESSAGE ,将信息错误号和参数都填写即可。
DECLARE @var1 VARCHAR(200);
SELECT @var1 = FORMATMESSAGE(20009, 'First Variable', 'Second Variable');
SELECT @var1;
sys.messages 内部自带有自己的错误编号和消息,但是仍然可以自定义添加的!
查看中文消息:
SELECT * FROM sys.messages WHERE language_id = 2052;
现在手动添加:错误编号,消息
EXEC sp_addmessage 50001, 16, N'the value between 1 and 100';
GO
查看结果:
SELECT * FROM sys.messages WHERE message_id = 50001;
以上是没有参数的消息,现在创建自定义参数的消息,添加自定义参数消息需要确定区域语言。如,查看中文2052的语言名称。
SELECT name FROM sys.syslanguages WHERE lcid = 2052;
添加自定义参数消息:
EXEC sp_addmessage
@msgnum = 50002,
@severity = 16,
@msgtext = N'The item named %s already exists in %s.',
@lang = '简体中文';
EXEC sp_addmessage
@msgnum = 50002,
@severity = 16,
@msgtext = N'The item named %s already exists in %s.',
@lang = 'us_english';
EXEC sp_addmessage
@msgnum = 50002,
@severity = 16,
@msgtext = N'The item named %s already exists in %s.',
@lang = '简体中文';
看看怎么使用:
SELECT * FROM sys.messages WHERE message_id = 50002;
RAISERROR(50001,1,1)
RAISERROR(50002,1,1)
RAISERROR(50002,1,1,'iPhone','手机')
嘿嘿!~这些消息可以拿去用了。要是不用,可以先直接删除。
-- 从系统视图中删除消息
EXEC sp_dropmessage @msgnum = 50001
GO
EXEC sp_dropmessage @msgnum = 50002,@lang = '简体中文'
GO
EXEC sp_dropmessage @msgnum = 50002,@lang = 'us_english'
GO
测试十一:
确认是否有打开的事务:
--是否有活动的用户事务
BEGIN TRAN
SELECT XACT_STATE(),@@TRANCOUNT
BEGIN TRAN
SELECT XACT_STATE(),@@TRANCOUNT
COMMIT TRAN
SELECT XACT_STATE(),@@TRANCOUNT
COMMIT TRAN
SELECT XACT_STATE(),@@TRANCOUNT
@@TRANCOUNT : 不能用于确定事务是否已分类为不可提交的事务
XACT_STATE() : 不能用于确定是否有嵌套事务
OK ! 更多参考官方文档!~
本文原创地址:http://blog.csdn.net/kk185800961/article/details/48347873