qidao123.com ToB IT社区-企服评测·应用市场

标题: 在 SQL Server 中使用 Try Catch 处理异常 [打印本页]
作者: 魏晓东    时间: 2022-8-27 00:34
标题: 在 SQL Server 中使用 Try Catch 处理异常
如何在 SQL Server 中使用 Try Catch 处理错误？

从 SQL Server 2005 开始，我们在TRY 和 CATCH块的帮助下提供了结构错误处理机制。使用TRY-CATCH的语法如下所示。
  1. BEGIN TRY
  2. --这里写可能导致错误的语句
  3. END TRY
  4. BEGIN CATCH
  5. --当错误发生后,这里的语句将会被执行
  6. END CATCH
  7. -- 其他语句
复制代码
有可能抛出异常的SQL 语句需要放在BEGIN TRY和END TRY块之间。如果在 TRY 块中发生异常，则控制权立即转移到相应的CATCH块。如果 TRY 块中没有发生异常，则直接跳过CATCH块，执行CATCH块之后的语句。
注意：被 CATCH 捕获的错误不会返回给调用应用程序。如果要将错误信息返回给调用应用程序，则需要将 RAISERROR() 函数显式与 catch 块一起使用。在之前的文章中，我们讨论了如何使用 RAISERROR() 函数显式地引发错误。参考: SQL Server 中的异常处理
示例：了解 SQL Server 中的 Try-Catch 实现。

在下面的示例中，我们使用 SQL Server TRY CATCH 实现和用户定义的错误语句来创建一个用于除以 2 个变量值的存储过程。
  1. IF OBJECT_ID('spDivideTwoNumbers','P') IS NOT NULL
  2.     DROP PROCEDURE spDivideTwoNumbers
  3. GO
  4. CREATE PROCEDURE spDivideTwoNumbers(
  5. @Number1 INT,
  6. @Number2 INT
  7. )
  8. AS
  9. BEGIN
  10.   DECLARE @Result INT
  11.   SET @Result = 0
  12.   BEGIN TRY
  13.     SET @Result = @Number1 / @Number2
  14.     PRINT '结果是: ' + CAST(@Result AS VARCHAR)
  15.   END TRY
  16.   BEGIN CATCH
  17.     PRINT '第二个数字不能为0'
  18.   END CATCH
  19. END
复制代码
让我们来测试一下这个存储过程
  1. exec spDivideTwoNumbers 100,1
复制代码
输出: 结果是: 100
  1. exec spDivideTwoNumbers 100,0
复制代码
输出: 第二个数字不能为0
当我们用正确的值执行上述存储过程时，程序就不会出现错误。这意味着在执行完 try 块中的所有语句后，控件直接跳转到存在于 catch 块之后的语句，而不执行 catch 块。
如果在执行过程中，即在try块中发生任何错误，那么在这种情况下，从发生错误的行开始，控制权直接跳转到catch块。所以 try 块中的其余语句将不会执行，而 catch 块将执行。
注意：在上面的程序中，当错误发生时，我们会显示一条用户自定义的错误消息“第二个数字不能为0”。然而，我们还可以通过调用函数 Error_Message 来显示原始错误消息。为了测试这个重写catch块内的代码如下
  1. ALTER PROCEDURE spDivideTwoNumbers(
  2. @Number1 INT,
  3. @Number2 INT
  4. )
  5. AS
  6. BEGIN
  7.   DECLARE @Result INT
  8.   SET @Result = 0
  9.   BEGIN TRY
  10.     SET @Result = @Number1 / @Number2
  11.     PRINT '结果是: ' + CAST(@Result AS VARCHAR)
  12.   END TRY
  13.   BEGIN CATCH
  14.     PRINT ERROR_MESSAGE()
  15.   END CATCH
  16. END
复制代码
再次执行: exec spDivideTwoNumbers 100,0
输出:   遇到以零作除数错误。
ERROR_MESSAGE 是系统函数, 此函数返回导致 TRY...CATCH 构造的 CATCH 块执行的错误消息文本。
在 SQL Server 中使用 try-catch 的示例

我们将使用下面的 Product 和 ProductSales 表来了解如何使用 RaiseError 和 @ERROR 系统定义函数来处理 SQL Server 中的错误。
Product (产品表)
ProductIDNamePriceQuantity101Laptop15000100102Desktop20000150104Mobile3000200105Tablet4000250ProductSales (产品销售表)
ProductSalesIDProductIDQuantitySold110110210215310430410535请使用以下 SQL 脚本创建并使用示例数据填充 Product 和 ProductSales 表。
  1. IF OBJECT_ID('dbo.Product','U') IS NOT NULL
  2.     DROP TABLE dbo.Product
  3. IF OBJECT_ID('dbo.ProductSales','U') IS NOT NULL
  4.     DROP TABLE dbo.ProductSales
  5. GO
  6. CREATE TABLE Product
  7. (
  8.   ProductID INT PRIMARY KEY,
  9.   Name VARCHAR(40),
  10.   Price INT,
  11.   Quantity INT
  12. )
  13. GO
  14. INSERT INTO Product VALUES(101, 'Laptop', 15000, 100)
  15. INSERT INTO Product VALUES(102, 'Desktop', 20000, 150)
  16. INSERT INTO Product VALUES(103, 'Mobile', 3000, 200)
  17. INSERT INTO Product VALUES(104, 'Tablet', 4000, 250)
  18. GO
  19. CREATE TABLE ProductSales
  20. (
  21.   ProductSalesId INT PRIMARY KEY,
  22.   ProductId INT,
  23.   QuantitySold INT
  24. )
  25. GO
  26. INSERT INTO ProductSales VALUES(1, 101, 10)
  27. INSERT INTO ProductSales VALUES(2, 102, 15)
  28. INSERT INTO ProductSales VALUES(3, 103, 30)
  29. INSERT INTO ProductSales VALUES(4, 104, 35)
  30. GO
复制代码
在 SQL Server 中使用 TRY Catch 实现产品销售的存储过程
  1. CREATE PROCEDURE spSellProduct
  2. @ProductID INT,
  3. @QuantityToSell INT
  4. AS
  5. BEGIN
  7.   -- 首先我们需要检查待销售产品的可用库存
  8.   DECLARE @StockAvailable INT
  9.   SELECT @StockAvailable = QuantityAvailable FROM Product WHERE ProductId = @ProductId
  11.     --如果可用库存小于要销售的数量,抛出错误
  12.   IF(@StockAvailable< @QuantityToSell)
  13.   BEGIN
  14.     Raiserror('可用库存不足',16,1)
  15.   END
  16.   
  17.   -- 如果可用库存充足
  18.   ELSE
  19.   BEGIN
  20.     BEGIN TRY
  21.       -- 我们需要开启一个事务
  22.       BEGIN TRANSACTION
  24.       -- 首先做减库存操作
  25.       UPDATE Product SET Quantity = (Quantity - @QuantityToSell) WHERE ProductID = @ProductID
  27.       -- 计算当前最大的产品销售ID,即 MaxProductSalesId
  28.       DECLARE @MaxProductSalesId INT
  29.       SELECT @MaxProductSalesId = CASE
  30.           WHEN  MAX(ProductSalesId) IS NULL THEN 0
  31.           ELSE MAX(ProductSalesId)
  32.           END
  33.       FROM ProductSales
  35.       -- 把 @MaxProductSalesId 加一, 所以我们会避免主键冲突
  36.       --(解释下,建表的时候,没有设置主键自增,所以需要人工处理自增)
  37.       Set @MaxProductSalesId = @MaxProductSalesId + 1
  39.       -- 把销售的产品数量记录到ProductSales表中
  40.       INSERT INTO ProductSales VALUES (@MaxProductSalesId, @ProductId, @QuantityToSell)
  42.       -- 最后,提交事务
  43.       COMMIT TRANSACTION
  44.     END TRY
  45.     BEGIN CATCH
  46.       -- 如果发生了异常,回滚事务
  47.       ROLLBACK TRANSACTION
  48.       -- 输出错误详情
  49.       SELECT ERROR_NUMBER() as ErrorNumber,
  50.           ERROR_MESSAGE() as ErrorMessage,
  51.           ERROR_PROCEDURE() as ErrorProcedure,
  52.           ERROR_STATE() as ErrorState,
  53.           ERROR_SEVERITY() as ErrorSeverity,
  54.           ERROR_LINE() as ErrorLine
  55.     END CATCH
  56.   End
  57. END
复制代码
在存储过程 spSellProduct 中，Begin Transaction 和 Commit Transaction 语句被包装在 Begin Try 和 End Try 块之间。如果包含在 BEGIN TRY 和 END TRY 块中的代码没有发生错误，则执行 COMMIT TRANSACTION 语句并将更改永久保存到数据库。
如果在 try 块中发生错误，则立即跳转到 CATCH 块，并且在 CATCH 块中，我们正在回滚事务。因此，使用 Try/Catch 构造处理错误比使用 SQL Server 中的 @@Error 系统函数要容易得多。
SQL Server 还提供了一些我们可以在 CATCH 块范围内使用的内置函数，这些函数用于检索有关发生的错误的更多信息，如果这些函数在 CATCH 块范围之外执行，它们将返回 NULL。
注意：我们不能在用户定义的函数中使用 TRY/CATCH

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！



欢迎光临 qidao123.com ToB IT社区-企服评测·应用市场 (https://dis.qidao123.com/) Powered by Discuz! X3.5