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