SQL Server 存儲過程開發手冊（更新版）

根據要求，重新整理并加入了事務控制、異常日志記錄和返回狀態碼的設計。以下是詳細說明：

---

1. 總則

1.1 目標

本手冊旨在為 SQL Server 存儲過程的編寫提供一套完整的規范，確保系統的事務控制、異常處理、日志記錄和狀態碼返回清晰明確，優化數據一致性、系統穩定性和性能。

---

2. 事務控制與層次化設計

2.1 事務控制原則

根據業務的需求和事務的粒度，存儲過程應分為不同的層次，每個層次的事務控制和異常處理策略不同，具體如下表所示：

層次	控制事務	記錄異常日志	返回狀態碼
主流程 (Main)	?	?	?
子事務 (SubTx)	?	?	?
功能性操作 (Func)	?	?	可選

• 主流程 (Main)：

  • 不控制事務，負責協調和調用子事務或功能性操作。

  • 記錄異常日志，捕獲并輸出錯誤信息。

  • 返回狀態碼，以通知調用方執行狀態（成功或失敗）。

• 子事務 (SubTx)：

  • 控制事務，開始和提交或回滾事務。

  • 記錄異常日志，在發生異常時捕獲并輸出錯誤信息。

  • 返回狀態碼，標識事務是否成功完成。

• 功能性操作 (Func)：

  • 不控制事務，通常是讀取操作或不涉及數據修改的輔助功能。

  • 不記錄異常日志（除非必要），通常這些操作在主流程中捕獲異常。

  • 返回狀態碼可選，根據具體業務需要決定是否返回。

---

3. 存儲過程命名規范

存儲過程命名應該遵循以下結構，便于識別和維護：

proc_領域_子領域_類型

• 領域 (Domain)：表示存儲過程所屬的業務領域，如訂單、用戶、商品等。

• 子領域 (SubDomain)：表示存儲過程在領域中的具體細分模塊，如發貨、支付、庫存等。

• 類型 (Type)：表示存儲過程的類型，分為三種類型：

  • Main：主流程，負責協調其他子事務和功能性操作。

  • SubTx：子事務，負責數據更新和事務控制。

  • Func：功能性操作，通常是查詢或計算操作。

3.1 示例命名

• proc_Order_Shipment_SubTx：表示“訂單”領域中的“發貨”子領域的子事務，涉及事務控制。

• proc_Order_Payment_Main：表示“訂單”領域中的“支付”子領域的主流程，負責調用其他子事務。

• proc_Inventory_Check_Func：表示“庫存”領域中的“檢查”功能，通常用于查詢操作，不涉及事務控制。

---

4. 存儲過程設計與事務控制

4.1 主流程存儲過程

主流程存儲過程不涉及事務控制，它的職責是調用子事務和功能性操作，并記錄異常日志和返回執行狀態碼。

CREATE PROCEDURE proc_Order_Shipment_Main@OrderId INT,@code INT output,@msg varchar(500) output
AS
BEGINBEGIN TRY-- 調用子事務，子事務控制事務EXEC proc_Order_Shipment_SubTx @OrderId, @code,@msg  ;if(@code!=200){-- 記錄失敗原因，處理失敗帶來的影響return}-- 調用功能性操作（如果需要）EXEC proc_Inventory_Check_Func @OrderId, @code,@msg ;if(@code!=200){-- 記錄失敗原因，處理失敗帶來的影響return}-- 返回成功狀態碼set @code=200END TRYBEGIN CATCH-- 捕獲并記錄異常日志PRINT 'Error in proc_Order_Shipment_Main: ' + ERROR_MESSAGE();-- 返回失敗狀態碼RETURN 1; -- 狀態碼：1 表示失敗END CATCH
END;

4.2 子事務存儲過程

子事務存儲過程負責控制事務，它需要開始、提交和回滾事務，并在發生異常時記錄日志和返回狀態碼。

CREATE PROCEDURE proc_Order_Shipment_SubTx@OrderId INT,@code INT output,@msg varchar(500) output
AS
BEGIN-- 開始事務BEGIN TRANSACTION;BEGIN TRY-- 訂單發貨相關的數據庫操作UPDATE dbo.OrderSET ShipmentStatus = 'Shipped'WHERE OrderId = @OrderId;-- 提交事務COMMIT TRANSACTION;-- 返回成功狀態碼RETURN 0; -- 狀態碼：0 表示成功END TRYBEGIN CATCH-- 發生錯誤時回滾事務ROLLBACK TRANSACTION;-- 記錄異常日志PRINT 'Error in proc_Order_Shipment_SubTx: ' + ERROR_MESSAGE();-- 返回失敗狀態碼RETURN 1; -- 狀態碼：1 表示失敗END CATCH
END;

4.3 功能性操作存儲過程

功能性操作通常是只執行查詢或計算，不涉及數據修改，因此不控制事務。日志記錄和狀態碼返回可選，具體業務需求決定。

CREATE PROCEDURE proc_Inventory_Check_Func@OrderId INT,@code INT output,@msg varchar(500) output
AS
BEGIN-- 查詢操作，檢查庫存SELECT ProductId, AvailableStockFROM dbo.InventoryWHERE ProductId IN (SELECT ProductId FROM dbo.OrderItems WHERE OrderId = @OrderId);
END;

---

5. 錯誤處理與日志記錄

5.1 錯誤處理

• 主流程：主流程存儲過程捕獲所有子事務和功能性操作中的異常，但不控制事務回滾。它應當記錄異常日志，并返回相應的狀態碼（通常是 0 或 1）。

• 子事務：子事務存儲過程必須通過 BEGIN TRY 和 BEGIN CATCH 語句來捕獲異常，并在異常發生時回滾事務。所有異常都應該記錄日志，并返回狀態碼，標識事務是否成功。

• 功能性操作：功能性操作通常不捕獲異常，也不記錄日志，異常處理依賴于主流程。

5.2 異常日志記錄

所有的異常都應通過日志記錄下來，方便后期排查和優化。可以使用 PRINT 或 RAISEERROR 語句來記錄錯誤消息。

BEGIN CATCHPRINT 'Error: ' + ERROR_MESSAGE();-- 或者使用 RAISEERROR 記錄更詳細的日志-- RAISEERROR('Error in procedure: %s', 16, 1, ERROR_MESSAGE());THROW;
END CATCH

---

6. 返回狀態碼

每個存儲過程在執行完畢后應返回一個狀態碼，狀態碼通常為：

• 0：表示操作成功。

• 1：表示操作失敗（可根據需要擴展更多狀態碼，表示不同類型的錯誤）。

返回狀態碼用于調用方判斷存儲過程執行是否成功，并做相應的處理。

---

7. 性能優化與鎖爭用

• 避免長事務：事務內只包含必要的數據庫操作，避免外部接口或延時任務的調用。

• 批量處理：對于需要更新大量數據的操作，盡量使用批量處理，減少事務鎖的持有時間。

• 索引優化：確保數據庫表有適當的索引，以加速查詢和數據操作。

---

8. 審查與監控

8.1 代碼審查標準

• 確保每個存儲過程的事務控制符合規范。

• 確保異常處理機制完備，并且日志記錄充分。

• 確保存儲過程有清晰的返回狀態碼，便于系統監控。

8.2 監控與日志

• 監控長時間運行的事務，避免事務阻塞。

• 配置合適的告警機制，及時捕獲死鎖或其他異常。

---

總結：

• 通過分層設計，每個存儲過程的責任清晰，主流程不控制事務，子事務控制事務，功能性操作不涉及事務管理。

• 存儲過程命名規則統一，便于后續維護和擴展。

• 錯誤處理和日志記錄是保證系統穩定性的關鍵，每個存儲過程都應有明確的異常處理和日志記錄機制。