MySQL SIGNAL 與 RESIGNAL 實戰：存儲過程中精準拋出與重拋異常

MySQL SIGNAL 與 RESIGNAL 實戰：存儲過程中精準拋出與重拋異常

MySQL 5.5 引入 SIGNAL 與 RESIGNAL，賦予存儲過程、觸發器、函數、事件主動拋出錯誤或重拋異常的能力，極大增強了業務邏輯的可控性。本文透過實例深入講解兩者語法、差異與應用場景。

一、SIGNAL：主動拋出錯誤

語法

SIGNAL SQLSTATE 'state' | condition_name
  SET condition_information_item = value, ...;

-- condition_information_item 可用項：
-- MESSAGE_TEXT (錯誤訊息)、MYSQL_ERRNO (錯誤碼)、CURSOR_NAME 等

核心規則

• 必須指定 SQLSTATE (5 字元) 或已聲明的 condition_name。
• 45000 為通用用戶自定義異常狀態碼。
• 可在存儲程序任何位置使用 (不限於 HANDLER 內)。

實例：訂單明細新增前驗證訂單存在性

DELIMITER $$
CREATE PROCEDURE AddOrderItem(
  IN p_order_no INT,
  IN p_product_code VARCHAR(45),
  IN p_qty INT,
  IN p_price DECIMAL(10,2),
  IN p_line_no INT
)
BEGIN
  DECLARE v_cnt INT;

  SELECT COUNT(*) INTO v_cnt FROM orders WHERE orderNumber = p_order_no;

  IF v_cnt != 1 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = '訂單號不存在', MYSQL_ERRNO = 1002;
  END IF;

  -- 正常業務邏輯
  INSERT INTO orderdetails (orderNumber, productCode, quantityOrdered, priceEach, orderLineNumber)
  VALUES (p_order_no, p_product_code, p_qty, p_price, p_line_no);
END$$
DELIMITER ;

調用測試：

CALL AddOrderItem(999, 'S10_1678', 1, 95.7, 1);
-- 錯誤輸出：ERROR 1002 (45000): 訂單號不存在

二、RESIGNAL：在處理器中重拋/轉換異常

語法

RESIGNAL [SQLSTATE 'state' | condition_name]
  [SET condition_information_item = value, ...];

關鍵差異

實例：捕獲除零錯誤並轉換為友好訊息

DELIMITER $$
CREATE PROCEDURE Divide(
  IN p_num INT,
  IN p_denom INT,
  OUT p_result DECIMAL(10,2)
)
BEGIN
  DECLARE div_by_zero CONDITION FOR SQLSTATE '22012';

  DECLARE CONTINUE HANDLER FOR div_by_zero
    RESIGNAL SET MESSAGE_TEXT = '業務錯誤：分母不能為零';

  IF p_denom = 0 THEN
    SIGNAL div_by_zero;  -- 主動觸發
  ELSE
    SET p_result = p_num / p_denom;
  END IF;
END$$
DELIMITER ;

調用測試：

CALL Divide(10, 0, @res);
-- 錯誤輸出：ERROR 1644 (22012): 業務錯誤：分母不能為零

三、進階模式：異常轉換鏈

CREATE PROCEDURE ProcessOrder(IN p_order_id INT)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    -- 記錄原始錯誤
    INSERT INTO error_log (proc_name, sqlstate, msg, created_at)
    VALUES ('ProcessOrder', @sqlstate, @msg, NOW());
    -- 轉換為統一業務異常
    RESIGNAL SET MESSAGE_TEXT = '訂單處理失敗，請聯繫管理員', MYSQL_ERRNO = 9999;
  END;

  -- 業務邏輯...
  UPDATE orders SET status = 'PROCESSING' WHERE id = p_order_id;
  -- 可能拋出 1205 (Lock wait timeout) 等
  CALL DeductInventory(p_order_id);
  CALL CreateShipment(p_order_id);
  UPDATE orders SET status = 'COMPLETED' WHERE id = p_order_id;
END;

四、最佳實踐建議

1. 統一錯誤碼體系：預留 45000 系列作業業務異常，應用層統一捕獲轉換為 HTTP 狀態碼/業務異常類。
2. 錯誤訊息國際化：MESSAGE_TEXT 存鍵值 (如 ERR_ORDER_NOT_FOUND)，應用層按語言包解析。
3. RESIGNAL 保留堆疊：在 HANDLER 中 RESIGNAL 不加參數可完整保留原始 SQLSTATE/錯誤碼，便於根因分析。
4. 觸發器中應用：BEFORE INSERT/UPDATE 觸發器中 SIGNAL 可阻止非法寫入，替代舊版 SIGNAL SQLSTATE '45000' 技巧。
5. 避免濫用：僅在真正的業務規則違反時使用，正常流程控制用 IF/ELSE 即可。

掌握 SIGNAL/RESIGNAL，能讓資料庫層成為業務完整性的最後一道防線，同時輸出結構化、可程式化處理的錯誤資訊，顯著提升系統健壯性與可維護性。