MySQL 預存程序與函數完整開發指南：語法、參數、流程控制、游標、異常處理實戰範例

預存程序（Stored Procedure）與函數（Function）是將業務邏輯下沉到資料庫層的利器，可減少網路往返、封裝複雜邏輯、提升安全性。本文系統整理 MySQL 5.6+ 的完整語法體系與實戰範例。

預存程序核心結構

CREATE PROCEDURE proc_name ([IN|OUT|INOUT] param type, ...)
BEGIN
  -- 區域變數
  DECLARE var_name type [DEFAULT value];
  
  -- 流程控制：IF / CASE / WHILE / REPEAT / LOOP
  -- 游標：DECLARE cur CURSOR FOR SELECT ...; OPEN cur; FETCH cur INTO var; CLOSE cur;
  -- 異常處理：DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
  
  -- SQL 執行
  SELECT ... INTO var FROM ...;
  UPDATE ...;
END;

參數模式對照

關鍵語法速查

游標完整範例：統計學生人數

CREATE PROCEDURE count_students(OUT total INT)
BEGIN
  DECLARE done BOOLEAN DEFAULT FALSE;
  DECLARE sid INT;
  DECLARE cur CURSOR FOR SELECT stu_id FROM student;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  
  SET total = 0;
  OPEN cur;
  FETCH cur INTO sid;
  WHILE NOT done DO
    SET total = total + 1;
    FETCH cur INTO sid;
  END WHILE;
  CLOSE cur;
END;

函數與程序差異

函數範例：依 ID 取得分數

CREATE FUNCTION get_score(p_id INT) RETURNS INT
BEGIN
  RETURN (SELECT score FROM student WHERE id = p_id);
END;

SELECT get_score(1);  -- 直接在 SQL 使用

最佳實務建議

• 複雜報表、批次異動邏輯適合下沉為程序。
• 避免在程序中大量單筆 COMMIT，成批提交。
• 游標處理大量資料時考慮改用集合式 SQL（INSERT ... SELECT）。
• 生產環境嚴禁動態 SQL（PREPARE）除非極必要，防注入與計畫快取失效。