MySQL 存儲過程優化實戰：從臃腫 SQL 到規範、健壯、可追蹤

原始存儲過程問題

CREATE PROCEDURE pr_dealtestnum (IN p_boxnumber VARCHAR(30))
BEGIN
  INSERT INTO tb_testnum SELECT boxnumber, usertype
  FROM tb_testnum_tmp WHERE boxnumber = p_boxnumber;
END;

問題：INSERT ... SELECT 混寫、無存在性判斷、無返回狀態、未列出目標列、無錯誤檢測。

五輪優化演進

優化一：拆分查詢與插入

DECLARE p_usertype INT;
SELECT usertype INTO p_usertype FROM tb_testnum_tmp WHERE boxnumber = p_boxnumber;
INSERT INTO tb_testnum VALUES (p_boxnumber, p_usertype);

優化二：雙向存在性校驗

• 源表無數據 → 返回錯誤碼 1。
• 目標表已存在 → 返回錯誤碼 2，不重複插入。

優化三：引入 OUT 參數作為執行狀態碼

CREATE PROCEDURE pr_dealtestnum (
  IN p_boxnumber VARCHAR(30),
  OUT p_result INT  -- 0 成功，1 源無數據，2 目標已存在
)

優化四：顯式列出目標列

INSERT INTO tb_testnum (boxnumber, usertype) VALUES (p_boxnumber, p_usertype);

防止未來表結構變更導致列順序錯位報錯。

優化五：檢測 @error_count 判斷插入成敗

INSERT INTO tb_testnum (boxnumber, usertype) VALUES (p_boxnumber, p_usertype);
IF @error_count <> 0 THEN SET p_result = 3; ELSE SET p_result = 0; END IF;

最終版核心特性

• 參數化輸入/輸出，狀態碼語義清晰。
• 源/目標雙重存在性校驗，冪等安全。
• 顯式列名，結構變更兼容。
• 錯誤碼覆蓋：業務邏輯錯誤（1/2）+ 執行層錯誤（3）。

總結

存儲過程優化不只是 SQL 改寫，更是「防禦性編程」：校驗前置、狀態碼輸出、顯式列名、錯誤感知。養成規範習慣，系統壽命更長、維護更易。