萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 存儲過程異常處理完整指南:DECLARE HANDLER、條件命名與實戰案例

異常處理核心語法

DECLARE handler_action HANDLER
FOR condition_value [, condition_value] ...
statement;

handler_action: CONTINUE | EXIT | UNDO  -- UNDO 暫不支持

condition_value:
    mysql_error_code              -- 數字錯誤碼,如 1062
  | SQLSTATE [VALUE] sqlstate_value  -- 5 字符標準碼,如 '23000'
  | condition_name                -- 自定義條件名
  | SQLWARNING                    -- SQLSTATE 以 '01' 開頭
  | NOT FOUND                     -- SQLSTATE 以 '02' 開頭
  | SQLEXCEPTION                  -- 除 '00','01','02' 外的所有錯誤

三大處理動作語義

動作執行完 statement 後默認適用場景
CONTINUE繼續執行後續語句SQLWARNING、NOT FOUND
EXIT退出當前 BEGIN...END 塊SQLEXCEPTION
UNDO回滾並退出(暫不支持)—

條件分類與默認行為

  • 精確匹配:error code / SQLSTATE / condition_name —— 針對特定錯誤
  • 類別匹配:
    • SQLWARNING:警告類(如截斷、死鎖檢測),默認 忽略繼續
    • NOT FOUND:游標無數據、SELECT...INTO 無結果,默認 忽略繼續
    • SQLEXCEPTION:真正錯誤,默認 終止存儲過程

自定義條件名:提升可讀性

-- 原始寫法
DECLARE CONTINUE HANDLER FOR 1216 SET @msg = 'FK error';

-- 推薦寫法
DECLARE foreign_key_error CONDITION FOR 1216;
DECLARE CONTINUE HANDLER FOR foreign_key_error SET @msg = 'FK error';

實戰案例:主鍵衝突優雅處理

CREATE PROCEDURE sp_add_location (
    IN  in_location VARCHAR(30),
    IN  in_address1 VARCHAR(30),
    IN  in_address2 VARCHAR(30),
    IN  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, in_zipcode);
END;

插入重複主鍵時,不拋錯中斷,而是設置輸出參數繼續執行,調用方可根據 out_status 判斷結果。

存儲過程常用語法速查

1. 條件分支與變量

CREATE PROCEDURE P_GET_CLASS_NAME(IN cid INT, OUT cname VARCHAR(50))
BEGIN
    IF cid = 1 THEN SET cname = '一班';
    ELSEIF cid = 2 THEN SET cname = '二班';
    ELSE SET cname = '未知';
    END IF;
END;

2. 動態 SQL:PREPARE / EXECUTE / DEALLOCATE

CREATE PROCEDURE P_INSERT_STUDENT(
    IN sid INT, IN sname VARCHAR(10), IN classno INT, IN birth DATETIME
)
BEGIN
    DECLARE classname VARCHAR(50) DEFAULT NULL;
    CALL P_GET_CLASS_NAME(classno, classname);

    SET @sql = 'INSERT INTO TBL_STUDENT VALUES(?,?,?,?)';
    PREPARE stmt FROM @sql;
    EXECUTE stmt USING sid, sname, classname, birth;
    DEALLOCATE PREPARE stmt;
END;

3. 游標與 NOT FOUND 處理模式

DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT id FROM t;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;
read_loop: LOOP
    FETCH cur INTO @id;
    IF done THEN LEAVE read_loop; END IF;
    -- 處理 @id
END LOOP;
CLOSE cur;

最佳實踐總結

  1. 盡量使用 SQLSTATE:標準化、跨版本穩定,避免依賴 MySQL 專有 error code
  2. 定義 condition_name:業務語義化,代碼自文檔化
  3. 精確捕獲 + 類別兜底:已知錯誤精確處理,未知錯誤用 SQLEXCEPTION 統一退出並記錄日誌
  4. 避免吞掉異常:CONTINUE 必須伴隨明確的業務狀態記錄(輸出參數、日誌表),否則難以排查
  5. 事務邊界清晰:異常處理中若需回滾,需顯式 ROLLBACK,因 UNDO 不支持
copyright © 萬盛學電腦網 all rights reserved