MySQL 存儲過程實戰心得：變量、異常處理與 DELIMITER 陷阱

案例代碼：主鍵衝突異常捕獲

USE test;
DROP TABLE IF EXISTS t8;
CREATE TABLE t8(s1 INT, PRIMARY KEY(s1));

DROP PROCEDURE IF EXISTS handlerdemo;
DELIMITER $$
CREATE PROCEDURE handlerdemo()
BEGIN
    DECLARE xx INT DEFAULT 4;
    -- 定義條件：SQLSTATE 23000 = 完整性約束違反
    DECLARE oh_no CONDITION FOR SQLSTATE '23000';
    -- 退出型處理器：遇到條件設置變量並退出 BEGIN...END
    DECLARE EXIT HANDLER FOR oh_no SET @info = '違反主鍵約束';
    
    SET @X = 1;
    INSERT INTO t8 VALUES(1);  -- 成功
    SET @X = 2;
    INSERT INTO t8 VALUES(1);  -- 觸發主鍵衝突，跳轉到 EXIT HANDLER
    SET @X = 3;                -- 不會執行
END $$
DELIMITER ;

-- 調用與驗證
CALL handlerdemo();
SELECT @X, @info;
-- 結果：@X=2, @info='違反主鍵約束'

核心心得總結

1. DELIMITER 陷阱與最佳實踐

• 存儲過程視為「單條語句」，內部 ; 需被屏蔽，故重定義分隔符
• 模式 A（推薦）：定義前 DELIMITER //，結束後 DELIMITER ; 恢復，後續可繼續寫別的語句
• 模式 B：定義前 DELIMITER $$，結束用 $$，但之後不能再寫其他語句，否則解析錯誤

-- 1. 定義條件別名
DECLARE oh_no CONDITION FOR SQLSTATE '23000';
-- 或直接用錯誤碼
DECLARE dup_key CONDITION FOR 1062;

-- 2. 綁定處理器
DECLARE EXIT HANDLER FOR oh_no SET @msg = '錯誤';
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @retry = 1;