9.6 SQL Server异常处理

 

SQL Server异常处理

 

目录

• SQL Server异常处理
  • TRY CATCH
    • 简介
    • CATCH块中的函数
    • 嵌套的TRY CATCH
    • 简单示例
    • 在事务中使用TRY CATCH的示例
  • THROW
    • 简介
    • 示例
      • A) 以下示例使用THROW语句引发异常：
      • B）使用THROW语句重新引发异常
      • C）使用FORMATMESSAGE函数

 

TRY CATCH

简介

TRY CATCH可以很好的处理SQL Server中的异常。要使用TRY CATCH，首先要在BEGIN TRY…END TRY块中放置一组可能导致异常的T-SQL语句，如下所示：

	BEGIN TRY
	-- 可能导致异常的语句
	END TRY

然后在TRY块之后立即使用BEGIN CATCH…END CATCH块：

	BEGIN CATCH
	-- 处理异常的语句
	END CATCH

以下是完整的TRY CATCH结构：

	BEGIN TRY
	-- 可能导致异常的语句
	END TRY
	BEGIN CATCH
	-- 处理异常的语句
	END CATCH

如果TRY块里边的语句完成且没有错误，则CATCH块不会被执行。但是，如果TRY块中有语句导致异常，则将执行CATCH块中的语句。

CATCH块中的函数

在CATCH块中，可以使用以下函数获取发生错误的详细信息：

• ERROR_LINE()返回发生异常的行号。
• ERROR_MESSAGE()返回生成的错误消息的完整内容。
• ERROR_PROCEDURE()返回发生错误的存储过程或触发器的名称。
• ERROR_NUMBER()返回发生的错误的编号。
• ERROR_SEVERITY()返回所发生错误的严重级别。
• ERROR_STATE()返回发生错误的状态号。

注意，这些函数只能在CATCH块中使用，如果在其他地方使用，所有这些函数将返回NULL。

嵌套的TRY CATCH

可以将TRY CATCH构造嵌套在另一个TRY CATCH构造中。但是，TRY块或CATCH块都可以包含嵌套的TRY CATCH，例如：

	BEGIN TRY
	--- 可能导致异常的语句
	END TRY
	BEGIN CATCH
	-- 处理异常的语句
	BEGIN TRY
	--- 嵌套的TRY块
	END TRY
	BEGIN CATCH
	--- 嵌套的CATCH块
	END CATCH
	END CATCH

简单示例

首先，创建一个名为usp_divide的存储过程，将两个数字相除：

	CREATE PROC usp_divide(
	@a decimal,
	@b decimal,
	@c decimal output
	) AS
	BEGIN
	BEGIN TRY
	SET @c = @a / @b;
	END TRY
	BEGIN CATCH
	SELECT
	ERROR_NUMBER() AS ErrorNumber
	,ERROR_SEVERITY() AS ErrorSeverity
	,ERROR_STATE() AS ErrorState
	,ERROR_PROCEDURE() AS ErrorProcedure
	,ERROR_LINE() AS ErrorLine
	,ERROR_MESSAGE() AS ErrorMessage;
	END CATCH
	END;
	GO

在这个存储过程中，我们将公式放在TRY块中，并在CATCH块中调用CATCH块函数ERROR_*。
然后，调用usp_divide存储过程将10除以2：

	DECLARE @r decimal;
	EXEC usp_divide 10, 2, @r output;
	PRINT @r;

输出：

5

因为TRY块中没有发生异常，所以存储过程在TRY块处完成。

尝试通过调用usp_divide存储过程将20除以0试试：

	DECLARE @r2 decimal;
	EXEC usp_divide 10, 0, @r2 output;
	PRINT @r2;

下图显示了输出：

由于公式导致的除零错误，所以报错后不再执行TRY块后续代码，直接执行CATCH块内的语句，该语句返回错误的详细信息。

在事务中使用TRY CATCH的示例

新建两张表，sale.persons(人员)和sales.deals(交易)用于示例：

	CREATE TABLE sales.persons
	(
	person_id INT PRIMARY KEY IDENTITY,
	first_name NVARCHAR(100) NOT NULL,-- 名字
	last_name NVARCHAR(100) NOT NULL-- 性别
	);
	CREATE TABLE sales.deals
	(
	deal_id INT PRIMARY KEY IDENTITY,
	person_id INT NOT NULL, -- 交易人
	deal_note NVARCHAR(100), -- 备注
	FOREIGN KEY(person_id) REFERENCES sales.persons(person_id)
	);
	insert into
	sales.persons(first_name, last_name)
	values
	('John','Doe'),
	('Jane','Doe');
	insert into
	sales.deals(person_id, deal_note)
	values
	(1,'Deal for John Doe');

然后，创建一个名为usp_report_error的新存储过程，该存储过程用于在CATCH块中用于报告错误的详细信息：

	CREATE PROC usp_report_error
	AS
	SELECT
	ERROR_NUMBER() AS ErrorNumber
	,ERROR_SEVERITY() AS ErrorSeverity
	,ERROR_STATE() AS ErrorState
	,ERROR_LINE () AS ErrorLine
	,ERROR_PROCEDURE() AS ErrorProcedure
	,ERROR_MESSAGE() AS ErrorMessage;
	GO

然后，开发一个新的存储过程，从sales.persons表中删除一行：

	CREATE PROC usp_delete_person(
	@person_id INT
	) AS
	BEGIN
	BEGIN TRY
	BEGIN TRANSACTION;
	-- 删除一个人员
	DELETE FROM sales.persons
	WHERE person_id = @person_id;
	-- 如果删除成功，提交事务
	COMMIT TRANSACTION;
	END TRY
	BEGIN CATCH
	-- 如果出了异常则报告错误，展示错误消息
	EXEC usp_report_error;
	-- 测试事务是否不可提交。
	IF (XACT_STATE()) = -1
	BEGIN
	PRINT N'The transaction is in an uncommittable state.' +
	'Rolling back transaction.'
	ROLLBACK TRANSACTION;
	END;
	-- 测试事务是否可提交。
	IF (XACT_STATE()) = 1
	BEGIN
	PRINT N'The transaction is committable.' +
	'Committing transaction.'
	COMMIT TRANSACTION;
	END;
	END CATCH
	END;
	GO

在这个存储过程中，使用XACT_STATE()函数在CATCH块中执行COMMIT TRANSACTION或ROLLBACK TRANSACITION之前检查事务的状态。

然后，调用usp_delete_person存储过程删除id为2的人员：

没有发生异常。

最后，调用存储过程usp_delete_person删除id为1的人员：

EXEC usp_delete_person 1;

发生以下错误(因为人员1被交易表中的记录引用着，所以报错)：

THROW

简介

THROW语句用于抛出异常，并将执行传输到TRY CATCH的CATCH块。
语法：

THROW [error_number,message ,state ];

其中：
error_number

error_number是一个代表异常的整数。error_number必须大于50000且小于或等于2147483647。

message

message是一个描述异常的NVARCHAR(2048)类型的字符串。

state

state是一个TINYINT，其值介于0和255之间。状态表示与消息关联的状态。

如果没有为THROW语句指定任何参数，则必须将THROW声明放在CATCH块中：

	BEGIN TRY
	-- 可能导致异常的语句
	END TRY
	BEGIN CATCH
	-- 处理异常的语句
	THROW;
	END CATCH

在这种情况下，THROW语句将CATCH块捕获的错误再抛出。

请注意，THROW语句之前的语句必须以分号(;)结束

示例

A) 以下示例使用THROW语句引发异常：

THROW 50005, N'An error occurred', 1;

输出：

	Msg 50005, Level 16, State 1, Line 1
	An error occurred

B）使用THROW语句重新引发异常

首先，为演示创建一个新表t1：

	CREATE TABLE t1(
	id int primary key
	);
	GO

然后，使用CATCH块中不带参数的THROW语句重新抛出捕获的错误：

	BEGIN TRY
	INSERT INTO t1(id) VALUES(1);
	-- cause error
	INSERT INTO t1(id) VALUES(1);
	END TRY
	BEGIN CATCH
	PRINT('Raise the caught error again');
	THROW;
	END CATCH

输出：

	(1 row affected)
	(0 rows affected)
	Raise the caught error again
	Msg 2627, Level 14, State 1, Line 10
	Violation of PRIMARY KEY constraint 'PK__t1__3213E83F906A55AA'. Cannot insert duplicate key in object 'dbo.t1'. The duplicate key value is (1).

在这个示例中，第一条INSERT语句成功了。但是，由于主键约束，第二个失败。因此，THROW语句再次引发CATCH块捕获的错误。

C）使用FORMATMESSAGE函数

与RAISERROR语句不同，THROW语句不允许您替换消息文本中的参数。因此，要模拟此功能，可以使用FORMATMESSAGE()函数。

以下语句将自定义消息添加到sys.messages目录视图中：

	EXEC sys.sp_addmessage
	@msgnum = 50010,
	@severity = 16,
	@msgtext =
	N'The order number %s cannot be deleted because it does not exist.',
	@lang = 'us_english';
	GO

此语句使用message_id 50010并将%s占位符替换为订单id '1001'：

	DECLARE @MessageText NVARCHAR(2048);
	SET @MessageText = FORMATMESSAGE(50010, N'1001');
	THROW 50010, @MessageText, 1;

输出：

	Msg 50010, Level 16, State 1, Line 8
	The order number 1001 cannot be deleted because it does not exist.

 

分类: SQL Server

标签: SQL Server , SQL Server高级 , 数据库