您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 預存程序參數與異常處理完整教學：HANDLER 機制詳解

編輯：MySQL 綜合教學

MySQL 預存程序參數與異常處理完整教學

MySQL 預存程序支援輸入（IN）、輸出（OUT）、輸入輸出（INOUT）三種參數模式，並提供 DECLARE HANDLER 機制處理執行時錯誤。本文詳細說明參數語法、Handler 類型、條件值定義與完整實作範例。

一、參數模式語法

CREATE PROCEDURE proc_name (
    IN  in_param   INT,      -- 僅輸入
    OUT out_param  VARCHAR(50), -- 僅輸出
    INOUT io_param INT       -- 輸入輸出雙向
)
BEGIN
    -- 程序體
END;

IN：呼叫端傳入，程序內可讀不可寫回呼叫端
OUT：程序內賦值，呼叫端可取得結果
INOUT：雙向傳遞，呼叫端傳入初值，程序可修改並回傳

二、DECLARE HANDLER 異常處理機制

語法結構

DECLARE handler_type HANDLER FOR condition_value[, ...] statement

handler_type: CONTINUE | EXIT
condition_value: 
    SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING | NOT FOUND | SQLEXCEPTION
  | mysql_error_code

Handler 類型差異

類型	行為
CONTINUE	發生錯誤後繼續執行後續陳述式
EXIT	發生錯誤後離開當前 BEGIN...END 區塊

條件值優先順序

MySQL 錯誤代碼 > SQLSTATE 代碼 > 命名條件

SQLWARNING：所有 01 開頭的 SQLSTATE
NOT FOUND：所有 02 開頭的 SQLSTATE（如游標無資料）
SQLEXCEPTION：未被上述兩者捕獲的所有錯誤
MySQL Error Code：如 1062（重複鍵）、1216（外鍵違反）

三、命名條件（CONDITION）提升可讀性

-- 宣告命名條件
DECLARE duplicate_key CONDITION FOR 1062;
DECLARE foreign_key_violated CONDITION FOR 1216;

-- 使用命名條件
DECLARE CONTINUE HANDLER FOR duplicate_key
BEGIN
    SET out_status = 'Duplicate Entry';
END;

DECLARE CONTINUE HANDLER FOR foreign_key_violated
BEGIN
    SET out_status = 'Foreign key violated';
END;

四、完整實作範例

範例 1：重複鍵處理

CREATE PROCEDURE sp_add_location (
    IN  in_location VARCHAR(30),
    IN  in_address1 VARCHAR(30),
    IN  in_address2 VARCHAR(30),
    IN  zipcode     VARCHAR(10),
    OUT out_status  VARCHAR(30)
)
BEGIN
    DECLARE CONTINUE HANDLER FOR 1062  -- Duplicate entry
        SET out_status = 'Duplicate Entry';
    
    SET out_status = 'OK';
    INSERT INTO locations (location, address1, address2, zipcode)
    VALUES (in_location, in_address1, in_address2, zipcode);
END;

範例 2：游標 NOT FOUND 處理

CREATE PROCEDURE sp_not_found()
READS SQL DATA
BEGIN
    DECLARE l_last_row INT DEFAULT 0;
    DECLARE l_dept_id INT;
    DECLARE c_dept CURSOR FOR SELECT department_id FROM departments;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_row = 1;
    
    OPEN c_dept;
    dept_loop: LOOP
        FETCH c_dept INTO l_dept_id;
        IF l_last_row = 1 THEN
            LEAVE dept_loop;
        END IF;
    END LOOP dept_loop;
    CLOSE c_dept;
END;

範例 3：綜合業務邏輯（含多種 Handler）

CREATE PROCEDURE sp_add_department (
    IN  p_department_name   VARCHAR(30),
    IN  p_manager_surname   VARCHAR(30),
    IN  p_manager_firstname VARCHAR(30),
    IN  p_location          VARCHAR(30),
    OUT p_sqlcode           INT,
    OUT p_status_message    VARCHAR(100)
)
BEGIN
    /* 宣告命名條件 */
    DECLARE duplicate_key CONDITION FOR 1062;
    DECLARE foreign_key_violated CONDITION FOR 1216;
    
    /* 宣告變數與游標 */
    DECLARE l_manager_id INT;
    DECLARE csr_mgr_id CURSOR FOR
        SELECT employee_id FROM employees
        WHERE surname = UPPER(p_manager_surname)
          AND firstname = UPPER(p_manager_firstname);
    
    /* 宣告異常處理器 */
    DECLARE CONTINUE HANDLER FOR duplicate_key
    BEGIN
        SET p_sqlcode = 1062;
        SET p_status_message = 'Duplicate key error';
    END;
    
    DECLARE CONTINUE HANDLER FOR foreign_key_violated
    BEGIN
        SET p_sqlcode = 1216;
        SET p_status_message = 'Foreign key violated';
    END;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND
    BEGIN
        SET p_sqlcode = 1329;
        SET p_status_message = 'No record found';
    END;
    
    /* 執行邏輯 */
    SET p_sqlcode = 0;
    OPEN csr_mgr_id;
    FETCH csr_mgr_id INTO l_manager_id;
    
    IF p_sqlcode <> 0 THEN
        SET p_status_message = CONCAT(p_status_message, ' when fetching manager id');
    ELSE
        INSERT INTO departments (department_name, manager_id, location)
        VALUES (UPPER(p_department_name), l_manager_id, UPPER(p_location));
        IF p_sqlcode <> 0 THEN
            SET p_status_message = CONCAT(p_status_message, ' when inserting new department');
        END IF;
    END IF;
    
    CLOSE csr_mgr_id;
END;