MySQL 5.0 存儲過程進階教程（四）：異常處理、游標、權限模型與安全上下文

一、異常處理：DECLARE HANDLER

場景：外鍵約束違規記錄錯誤日誌

CREATE TABLE t2 (s1 INT PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE t3 (s1 INT, KEY(s1), FOREIGN KEY(s1) REFERENCES t2(s1)) ENGINE=InnoDB;
CREATE TABLE error_log (msg CHAR(80));

CREATE PROCEDURE p22 (p1 INT)
BEGIN
    DECLARE EXIT HANDLER FOR 1216
        INSERT INTO error_log VALUES (CONCAT('Time: ', CURRENT_DATE, '. FK Fail: ', p1));
    INSERT INTO t3 VALUES (p1);
END;

呼叫 `CALL p22(5)` 時，5 不在 t2 中，觸發 1216 錯誤，EXIT HANDLER 寫日誌並退出複合語句，t3 無資料寫入。

HANDLER 類型對比

CONTINUE 範例：主鍵衝突不中斷流程

CREATE PROCEDURE p23 ()
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
    SET @x = 1; INSERT INTO t4 VALUES (1);  -- 成功
    SET @x = 2; INSERT INTO t4 VALUES (1);  -- 重複主鍵，觸發 HANDLER，@x2=1
    SET @x = 3;                             -- 繼續執行
END;

執行後 `@x=3, @x2=1`，證明流程未中斷。

命名條件：DECLARE CONDITION

CREATE PROCEDURE p24 ()
BEGIN
    DECLARE `Constraint Violation` CONDITION FOR SQLSTATE '23000';
    DECLARE EXIT HANDLER FOR `Constraint Violation` ROLLBACK;
    START TRANSACTION;
    INSERT INTO t2 VALUES (1);
    INSERT INTO t2 VALUES (1);  -- 觸發約束錯誤，整筆事務回滾
    COMMIT;
END;

預定義條件

• NOT FOUND (SQLSTATE 02000) — 游標無資料
• SQLEXCEPTION — 所有錯誤
• SQLWARNING — 警告

二、游標：逐行處理結果集

CREATE PROCEDURE p25 (OUT ret INT)
BEGIN
    DECLARE a, b INT;
    DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;
    OPEN cur_1;
    REPEAT
        FETCH cur_1 INTO a;
    UNTIL b = 1 END REPEAT;
    CLOSE cur_1;
    SET ret = a;  -- 返回最後一行值
END;

關鍵順序：變量 → 條件 → 游標 → HANDLER，否則報錯。

游標三大特性

• READ ONLY：不可 `UPDATE ... WHERE CURRENT OF cursor`
• NOT SCROLLABLE：僅 `FETCH NEXT`，不支援 PRIOR/ABSOLUTE
• ASENSITIVE：對底表變更敏感，開啟期間勿並發 UPDATE

三、權限與安全上下文

SQL SECURITY：DEFINER vs INVOKER

CREATE PROCEDURE p26 () SQL SECURITY INVOKER SELECT COUNT(*) FROM t;
CREATE PROCEDURE p27 () SQL SECURITY DEFINER SELECT COUNT(*) FROM t;
GRANT INSERT ON db5.* TO peter;

• INVOKER：以調用者權限執行。peter 無 SELECT 權限 → 呼叫 p26 失敗
• DEFINER：以建立者權限執行。root 有 SELECT → peter 呼叫 p27 成功

善用 DEFINER 可實現「最小權限原則」：應用帳號僅需 EXECUTE 權限，核心表權限收歸定義者。