SQLSERVER-异常处理机制（附2012新增throw功能）

对于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() : 不能用于确定是否有嵌套事务  

转载于:https://blog.51cto.com/zhouhuo/1980581