MySQL 預存程序與函數完整教學：語法、參數模式、流程控制、游標實戰

一、預存程序

1.1 基礎語法

CREATE PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body

proc_parameter: [IN | OUT | INOUT] param_name type
characteristic:
  COMMENT 'string'
  LANGUAGE SQL
  DETERMINISTIC | NOT DETERMINISTIC
  CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA
  SQL SECURITY { DEFINER | INVOKER }

routine_body: BEGIN ... END  (單句可省略 BEGIN/END)

1.2 參數模式實戰

IN 範例：條件查詢

DELIMITER //
CREATE PROCEDURE SP_SEARCH(IN p_name CHAR(20))
BEGIN
  IF p_name IS NULL OR p_name = '' THEN
    SELECT * FROM t_user;
  ELSE
    SELECT * FROM t_user WHERE USER_NAME LIKE p_name;
  END IF;
END //
DELIMITER ;

CALL SP_SEARCH('林炳文');

OUT 範例：回傳筆數

CREATE PROCEDURE SP_SEARCH2(IN p_name CHAR(20), OUT p_int INT)
BEGIN
  IF p_name IS NULL OR p_name = '' THEN
    SELECT * FROM t_user;
  ELSE
    SELECT * FROM t_user WHERE USER_NAME LIKE p_name;
  END IF;
  SELECT FOUND_ROWS() INTO p_int;
END;

CALL SP_SEARCH2('林%', @p_num);
SELECT @p_num;  -- 顯示符合條件的筆數

INOUT 範例：數值運算

CREATE PROCEDURE sp_inout(INOUT p_num INT)
BEGIN
  SET p_num = p_num * 10;
END;

SET @p_num = 2;
CALL sp_inout(@p_num);
SELECT @p_num;  -- 20

1.3 流程控制語句

IF / ELSEIF / ELSE

CREATE PROCEDURE SP_SCHOLARSHIP_LEVEL(IN p_level CHAR(1))
BEGIN
  IF p_level = 'A' THEN
    SELECT * FROM t_grade WHERE STU_SCORE >= 90;
  ELSEIF p_level = 'B' THEN
    SELECT * FROM t_grade WHERE STU_SCORE >= 80 AND STU_SCORE < 90;
  ELSEIF p_level = 'C' THEN
    SELECT * FROM t_grade WHERE STU_SCORE >= 70 AND STU_SCORE < 80;
  ELSEIF p_level = 'D' THEN
    SELECT * FROM t_grade WHERE STU_SCORE < 60;
  ELSE
    SELECT * FROM t_grade;
  END IF;
END;

CASE 語句（兩種形式）

-- 形式 1：值比較
CASE case_value
  WHEN when_value THEN stmt_list
  ...
  ELSE stmt_list
END CASE;

-- 形式 2：條件判斷
CASE
  WHEN search_condition THEN stmt_list
  ...
  ELSE stmt_list
END CASE;

迴圈：WHILE / REPEAT / LOOP

-- WHILE：先判斷後執行
WHILE p_num > 1 DO
  SET p_result = p_num * p_result;
  SET p_num = p_num - 1;
END WHILE;

-- REPEAT：先執行後判斷
REPEAT
  SET p_result = p_num * p_result;
  SET p_num = p_num - 1;
UNTIL p_num <= 1
END REPEAT;

-- LOOP：需配合 LEAVE/ITERATE
loop_label: LOOP
  ...
  IF condition THEN LEAVE loop_label; END IF;
END LOOP;

1.4 局部變量與錯誤處理

DECLARE var_name TYPE [DEFAULT value];
SET var_name = expr;
SELECT col INTO var_name FROM table ...;

-- 錯誤處理器
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
-- SQLWARNING / NOT FOUND / SQLEXCEPTION

1.5 管理指令

CALL sp_name([params]);
ALTER PROCEDURE sp_name [characteristic ...];  -- 僅能改特性
DROP PROCEDURE [IF EXISTS] sp_name;

二、預存函數

2.1 語法差異

CREATE FUNCTION fn_name ([params]) RETURNS type
[characteristic ...] routine_body
RETURN value;  -- 函數體必須包含 RETURN

2.2 呼叫方式

SELECT fn_name(params);  -- 在 SQL 表達式中呼叫

2.3 範例：取較大值

CREATE FUNCTION sp_cal_max(p_num1 INT, p_num2 INT) RETURNS INT
BEGIN
  IF p_num1 >= p_num2 THEN RETURN p_num1; ELSE RETURN p_num2; END IF;
END;

SELECT sp_cal_max(2, 34);  -- 34

2.4 函數 vs 預存程序 差異總結

三、游標

3.1 四步驟

1. 宣告： DECLARE cur_name CURSOR FOR SELECT ...;
2. 打開： OPEN cur_name;
3. 讀取： FETCH cur_name INTO var1, var2, ...;
4. 關閉： CLOSE cur_name;

3.2 完整範例：表間資料複製

CREATE PROCEDURE get_cur()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE v_id INT; DECLARE v_type CHAR(11); DECLARE v_order CHAR(11);
  DECLARE mycur CURSOR FOR SELECT * FROM test_cur1;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  OPEN mycur;
  REPEAT
    FETCH mycur INTO v_id, v_type, v_order;
    IF NOT done THEN
      INSERT INTO test_cur2 VALUES (v_id, v_type, v_order);
    END IF;
  UNTIL done END REPEAT;
  CLOSE mycur;
END;

最佳實務建議

• 業務邏輯下沉： 複雜多步驟、需原子性的業務邏輯適合封裝為預存程序，減少網路往返。
• 避免過度使用： 簡單 CRUD 留在應用層（ORM），利於版控、測試、重構。
• 權限最小化： 以 SQL SECURITY DEFINER 建立，授予 EXECUTE 權限而非直接表權限。
• 版本控管： 將 CREATE PROCEDURE/FUNCTION 腳本納入 Git，避免線上手動修改導致版本漂移。
• 效能監控： 開啟 log_slow_queries 監控預存程式執行時間，定期 EXPLAIN 內部 SQL。