MySQL 預存過程異常處理機制：DECLARE HANDLER 完整實戰

MySQL 預存過程異常處理機制：DECLARE HANDLER 完整實戰

MySQL 5.0+ 引入 DECLARE ... HANDLER，可在預存過程中捕獲特定錯誤碼或 SQLSTATE，實現類似 Try-Catch 的結構化錯誤處理。

核心組件

1. 條件聲明：DECLARE condition_name CONDITION FOR error_code [OR SQLSTATE 'xxxxx'];
2. 處理器聲明：DECLARE {CONTINUE|EXIT} HANDLER FOR condition_name [, ...] BEGIN ... END;
   • CONTINUE：執行處理器後繼續後續語句。
   • EXIT：執行處理器後離開當前 BEGIN...END 塊。
3. 預定義條件：NOT FOUND（游標無數據）、SQLEXCEPTION（所有錯誤）、SQLWARNING（所有警告）。

完整範例：新增員工並處理重複鍵、外鍵違規、查無記錄

DELIMITER $$

CREATE PROCEDURE add_employee(
    IN p_first_name VARCHAR(30),
    IN p_last_name  VARCHAR(30),
    IN p_city       VARCHAR(30),
    IN p_desc       VARCHAR(30),
    OUT p_sqlcode   INT,
    OUT p_msg       VARCHAR(100)
)
BEGIN
    /* 1. 聲明條件 */
    DECLARE duplicate_key     CONDITION FOR 1062;
    DECLARE fk_violated       CONDITION FOR 1216;

    /* 2. 聲明變數與游標 */
    DECLARE l_mgr_id INT;
    DECLARE cur_mgr CURSOR FOR
        SELECT id FROM employee WHERE first_name=p_first_name AND last_name=p_last_name;

    /* 3. 聲明異常處理器 */
    DECLARE CONTINUE HANDLER FOR duplicate_key
    BEGIN
        SET p_sqlcode = 1062;
        SET p_msg = 'Duplicate key error';
    END;

    DECLARE CONTINUE HANDLER FOR fk_violated
    BEGIN
        SET p_sqlcode = 1216;
        SET p_msg = 'Foreign key violated';
    END;

    DECLARE CONTINUE HANDLER FOR NOT FOUND
    BEGIN
        SET p_sqlcode = 1329;
        SET p_msg = 'No record found';
    END;

    /* 4. 業務邏輯 */
    SET p_sqlcode = 0;
    OPEN cur_mgr;
    FETCH cur_mgr INTO l_mgr_id;

    IF p_sqlcode <> 0 THEN
        SET p_msg = CONCAT(p_msg, ' when fetching manager id');
    ELSE
        INSERT INTO employee (first_name, id, city) VALUES (p_first_name, l_mgr_id, p_city);
        IF p_sqlcode <> 0 THEN
            SET p_msg = CONCAT(p_msg, ' when inserting new employee');
        END IF;
    END IF;

    CLOSE cur_mgr;
END$$

DELIMITER ;

/* 測試調用 */
SET @code = 0, @msg = '';
CALL add_employee('Jason', 'Martin', 'New City', 'New Desc', @code, @msg);
SELECT @code, @msg;

關鍵點解析

• 錯誤碼 1062（ER_DUP_ENTRY）、1216（ER_NO_REFERENCED_ROW_2）為常見完整性約束錯誤。
• NOT FOUND 捕獲游標 FETCH 無數據時的狀態（SQLSTATE '02000'）。
• 輸出參數 OUT 傳回錯誤碼與訊息，供應用層判斷。
• CONTINUE 讓流程能在多個操作點累積錯誤資訊。

最佳實踐

1. 盡量使用具體錯誤碼條件，避免捕獲過廣的 SQLEXCEPTION 導致掩蓋真實問題。
2. 處理器中記錄錯誤上下文（如操作步驟、參數值）便於排查。
3. 重要業務建議在應用層再次封裝事務邊界，資料庫層僅做細顆粒錯誤轉換。