MySQL 預存程序與觸發器完整教學：語法、參數模式、流程控制、變數作用域實戰

MySQL 預存程序與觸發器完整教學：語法、參數模式、流程控制、變數作用域實戰

預存程序封裝複雜業務邏輯，減少網路往返；觸發器自動響應表變更。本文涵蓋建立/呼叫/刪除程序、IN/OUT/INOUT 參數、會話/全域/區域變數、條件判斷、迴圈、查詢結果賦值、觸發器三類事件實戰。

一、預存程序

基礎語法

DELIMITER $$
CREATE PROCEDURE proc_name ([IN|OUT|INOUT] param_name type, ...)
BEGIN
  -- SQL 語句 + 流程控制
END $$
DELIMITER ;

• DELIMITER 變更結束符，避免程序體內 ; 衝突。
• IN：輸入參數；OUT：輸出參數；INOUT：雙向。

1. 僅輸入參數

DELIMITER $$
CREATE PROCEDURE pro_findById(IN eid INT)
BEGIN
  SELECT * FROM employee WHERE id = eid;
END $$
DELIMITER ;

CALL pro_findById(4);

2. 僅輸出參數（需會話變數接收）

DELIMITER $$
CREATE PROCEDURE pro_testOut(OUT str VARCHAR(20))
BEGIN
  SET str = 'hellojava';
END $$
DELIMITER ;

SET @name = '';
CALL pro_testOut(@name);
SELECT @name;  -- 顯示 hellojava

3. 輸入輸出參數

DELIMITER $$
CREATE PROCEDURE pro_testInOut(INOUT n INT)
BEGIN
  SELECT n;   -- 顯示輸入值
  SET n = 500;
END $$
DELIMITER ;

SET @n = 10;
CALL pro_testInOut(@n);
SELECT @n;  -- 顯示 500

4. 條件判斷 (IF/CASE)

DELIMITER $$
CREATE PROCEDURE pro_testIf(IN num INT, OUT str VARCHAR(20))
BEGIN
  IF num = 1 THEN SET str = '星期一';
  ELSEIF num = 2 THEN SET str = '星期二';
  ELSEIF num = 3 THEN SET str = '星期三';
  ELSE SET str = '輸入錯誤';
  END IF;
END $$
DELIMITER ;

CALL pro_testIf(4, @str);
SELECT @str;

5. 迴圈

DELIMITER $$
CREATE PROCEDURE pro_testWhile(IN num INT, OUT result INT)
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE vsum INT DEFAULT 0;
  WHILE i <= num DO
    SET vsum = vsum + i;
    SET i = i + 1;
  END WHILE;
  SET result = vsum;
END $$
DELIMITER ;

CALL pro_testWhile(100, @result);
SELECT @result;  -- 5050

6. 查詢結果直接賦值 (INTO)

DELIMITER $$
CREATE PROCEDURE pro_findById2(IN eid INT, OUT vname VARCHAR(20))
BEGIN
  SELECT empName INTO vname FROM employee WHERE id = eid;
END $$
DELIMITER ;

CALL pro_findById2(1, @NAME);
SELECT @NAME;

MySQL 變數三大類

二、觸發器

建立語法

CREATE TRIGGER trigger_name
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON table_name
FOR EACH ROW
BEGIN
  -- 觸發邏輯
END;

實戰：新增員工自動寫入日誌

CREATE TRIGGER tri_empAdd AFTER INSERT ON employee
FOR EACH ROW
INSERT INTO test_log(content) VALUES('員工表插入了一條記錄');

CREATE TRIGGER tri_empUpd AFTER UPDATE ON employee
FOR EACH ROW
INSERT INTO test_log(content) VALUES('員工表修改了一條記錄');

CREATE TRIGGER tri_empDel AFTER DELETE ON employee
FOR EACH ROW
INSERT INTO test_log(content) VALUES('員工表刪除了一條記錄');

查看/刪除觸發器

SHOW TRIGGERS \G;
DROP TRIGGER tri_empAdd;

三、最佳實踐與注意事項

• DELIMITER 只在建立程序/函數/觸發器時需要，建立完成後立即恢復 ;。
• 預存程序適合固定、高頻、多步驟的業務邏輯；避免將所有邏輯下推資料庫，導致難以版控與除錯。
• 觸發器邏輯務必輕量，避免觸發器鏈式調用、外部呼叫、大量資料處理。
• 生產環境謹慎使用 OUT/INOUT 參數，優先考慮回傳結果集或會話變數。
• 權限：建立程序需 CREATE ROUTINE，執行需 EXECUTE，定義者/調用者權限模型由 SQL SECURITY 控制。