在 SQL Server 中使用 Try Catch 處理異常

Broadm發表於2022-07-14
SQLServer

如何在 SQL Server 中使用 Try Catch 處理錯誤？

從 SQL Server 2005 開始，我們在TRY 和 CATCH塊的幫助下提供了結構錯誤處理機制。使用TRY-CATCH的語法如下所示。

BEGIN TRY
--這裡寫可能導致錯誤的語句
END TRY
BEGIN CATCH
--當錯誤發生後,這裡的語句將會被執行
END CATCH
-- 其他語句

有可能丟擲異常的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 個變數值的儲存過程。

IF OBJECT_ID('spDivideTwoNumbers','P') IS NOT NULL
    DROP PROCEDURE spDivideTwoNumbers
GO
CREATE PROCEDURE spDivideTwoNumbers(
@Number1 INT, 
@Number2 INT
)
AS
BEGIN
  DECLARE @Result INT
  SET @Result = 0
  BEGIN TRY
    SET @Result = @Number1 / @Number2
    PRINT '結果是: ' + CAST(@Result AS VARCHAR)
  END TRY
  BEGIN CATCH
    PRINT '第二個數字不能為0'
  END CATCH
END

讓我們來測試一下這個儲存過程

exec spDivideTwoNumbers 100,1

輸出: 結果是: 100

exec spDivideTwoNumbers 100,0

輸出: 第二個數字不能為0

當我們用正確的值執行上述儲存過程時，程式就不會出現錯誤。這意味著在執行完 try 塊中的所有語句後，控制元件直接跳轉到存在於 catch 塊之後的語句，而不執行 catch 塊。

如果在執行過程中，即在try塊中發生任何錯誤，那麼在這種情況下，從發生錯誤的行開始，控制權直接跳轉到catch塊。所以 try 塊中的其餘語句將不會執行，而 catch 塊將執行。

注意：在上面的程式中，當錯誤發生時，我們會顯示一條使用者自定義的錯誤訊息“第二個數字不能為0”。然而，我們還可以通過呼叫函式 Error_Message 來顯示原始錯誤訊息。為了測試這個重寫catch塊內的程式碼如下

ALTER PROCEDURE spDivideTwoNumbers(
@Number1 INT, 
@Number2 INT
)
AS
BEGIN
  DECLARE @Result INT
  SET @Result = 0
  BEGIN TRY
    SET @Result = @Number1 / @Number2
    PRINT '結果是: ' + CAST(@Result AS VARCHAR)
  END TRY
  BEGIN CATCH
    PRINT ERROR_MESSAGE()
  END CATCH
END

再次執行: exec spDivideTwoNumbers 100,0
輸出: 遇到以零作除數錯誤。

ERROR_MESSAGE 是系統函式, 此函式返回導致 TRY...CATCH 構造的 CATCH 塊執行的錯誤訊息文字。

在 SQL Server 中使用 try-catch 的示例

我們將使用下面的 Product 和 ProductSales 表來了解如何使用 RaiseError 和 @ERROR 系統定義函式來處理 SQL Server 中的錯誤。

Product (產品表)

ProductID Name Price Quantity
101 Laptop 15000 100
102 Desktop 20000 150
104 Mobile 3000 200
105 Tablet 4000 250

ProductSales (產品銷售表)

ProductSalesID ProductID QuantitySold
1 101 10
2 102 15
3 104 30
4 105 35

請使用以下 SQL 指令碼建立並使用示例資料填充 Product 和 ProductSales 表。

IF OBJECT_ID('dbo.Product','U') IS NOT NULL
    DROP TABLE dbo.Product
IF OBJECT_ID('dbo.ProductSales','U') IS NOT NULL
    DROP TABLE dbo.ProductSales
GO
CREATE TABLE Product
(
  ProductID INT PRIMARY KEY, 
  Name VARCHAR(40), 
  Price INT,
  Quantity INT
 )
GO
INSERT INTO Product VALUES(101, 'Laptop', 15000, 100)
INSERT INTO Product VALUES(102, 'Desktop', 20000, 150)
INSERT INTO Product VALUES(103, 'Mobile', 3000, 200)
INSERT INTO Product VALUES(104, 'Tablet', 4000, 250)
GO
CREATE TABLE ProductSales
(
  ProductSalesId INT PRIMARY KEY,
  ProductId INT,
  QuantitySold INT
) 
GO
INSERT INTO ProductSales VALUES(1, 101, 10)
INSERT INTO ProductSales VALUES(2, 102, 15)
INSERT INTO ProductSales VALUES(3, 103, 30)
INSERT INTO ProductSales VALUES(4, 104, 35)
GO

在 SQL Server 中使用 TRY Catch 實現產品銷售的儲存過程

CREATE PROCEDURE spSellProduct
@ProductID INT,
@QuantityToSell INT
AS
BEGIN

  -- 首先我們需要檢查待銷售產品的可用庫存
  DECLARE @StockAvailable INT
  SELECT @StockAvailable = QuantityAvailable FROM Product WHERE ProductId = @ProductId

    --如果可用庫存小於要銷售的數量,丟擲錯誤
  IF(@StockAvailable< @QuantityToSell)
  BEGIN
    Raiserror('可用庫存不足',16,1)
  END
  
  -- 如果可用庫存充足
  ELSE
  BEGIN
    BEGIN TRY
      -- 我們需要開啟一個事務
      BEGIN TRANSACTION

      -- 首先做減庫存操作
      UPDATE Product SET Quantity = (Quantity - @QuantityToSell) WHERE ProductID = @ProductID

      -- 計算當前最大的產品銷售ID,即 MaxProductSalesId
      DECLARE @MaxProductSalesId INT
      SELECT @MaxProductSalesId = CASE 
          WHEN  MAX(ProductSalesId) IS NULL THEN 0 
          ELSE MAX(ProductSalesId) 
          END 
      FROM ProductSales

      -- 把 @MaxProductSalesId 加一, 所以我們會避免主鍵衝突 
      --(解釋下,建表的時候,沒有設定主鍵自增,所以需要人工處理自增)
      Set @MaxProductSalesId = @MaxProductSalesId + 1

      -- 把銷售的產品數量記錄到ProductSales表中
      INSERT INTO ProductSales VALUES (@MaxProductSalesId, @ProductId, @QuantityToSell)

      -- 最後,提交事務
      COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
      -- 如果發生了異常,回滾事務
      ROLLBACK TRANSACTION
      -- 輸出錯誤詳情
      SELECT ERROR_NUMBER() as ErrorNumber,
          ERROR_MESSAGE() as ErrorMessage,
          ERROR_PROCEDURE() as ErrorProcedure,
          ERROR_STATE() as ErrorState,
          ERROR_SEVERITY() as ErrorSeverity,
          ERROR_LINE() as ErrorLine
    END CATCH
  End
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

相關文章