MySQL 存儲函數與觸發器完全指南：語法、範例與最佳實踐

前言

在 MySQL 資料庫開發中，存儲函數與觸發器是兩項強大且常被忽視的進階功能。它們能將業務邏輯封裝在資料庫層，提升效能、確保資料完整性，並減少應用程式與資料庫的往返通訊。本文將系統整理存儲函數與觸發器的建立、呼叫、修改、刪除語法，並搭配實戰範例，助你快速掌握核心用法。

一、存儲函數

1.1 存儲函數 vs 存儲過程：關鍵差異

• 無輸出參數：函數本身即為返回值，故不支援 OUT/INOUT 參數。
• 呼叫方式不同：不可用 CALL，必須在 SQL 表達式中以 SELECT func_name() 調用。
• 強制 RETURN：函數主體必須包含 RETURN value，否則會報錯。

1.2 建立存儲函數語法

CREATE FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body

• sp_name：函數名稱，不可與存儲過程重名。
• func_parameter：僅含參數名與型別，無 IN/OUT/INOUT。
• RETURNS type：宣告返回值型別。
• routine_body：函數主體，允許流程控制、游標等，但必須有 RETURN。

1.3 實戰範例

範例 1：統計學生人數

DELIMITER $$
CREATE FUNCTION NUM_OF_XS() RETURNS INTEGER
BEGIN
  RETURN (SELECT COUNT(*) FROM XS);
END$$
DELIMITER ;

範例 2：清理孤立學號

DELIMITER $$
CREATE FUNCTION DELETE_STU(XH CHAR(6)) RETURNS BOOLEAN
BEGIN
  DECLARE STU CHAR(6);
  SELECT 姓名 INTO STU FROM XS WHERE 學號 = XH;
  IF STU IS NULL THEN
    DELETE FROM XS_KC WHERE 學號 = XH;
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;
END$$
DELIMITER ;

範例 3：巢狀呼叫函數

DELIMITER $$
CREATE FUNCTION IS_STU(XH CHAR(6)) RETURNS CHAR(10)
BEGIN
  DECLARE NAME CHAR(8);
  SELECT NAME_OF_STU(XH) INTO NAME;
  IF NAME = '王林' THEN
    RETURN (SELECT 出生日期 FROM XS WHERE 學號 = XH);
  ELSE
    RETURN 'FALSE';
  END IF;
END$$
DELIMITER ;

1.4 呼叫、修改與刪除

• 呼叫：SELECT NUM_OF_XS();
• 刪除：DROP FUNCTION IF EXISTS NUM_OF_XS;
• 修改特性：ALTER FUNCTION sp_name [characteristic ...]（修改主體需先刪除再建立）

二、觸發器

2.1 建立觸發器語法

CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt

• trigger_time：BEFORE 或 AFTER。
• trigger_event：INSERT、UPDATE、DELETE。
• FOR EACH ROW：對每一受影響列執行。
• trigger_stmt：執行語句，多語句用 BEGIN ... END 包裹。

注意：觸發器不可返回結果給客戶端，故定義中勿使用 SELECT 或呼叫返回結果的存儲過程。

2.2 NEW 與 OLD 列引用

2.3 實戰範例：級聯刪除

DELIMITER $$
CREATE TRIGGER XS_DELETE AFTER DELETE ON XS FOR EACH ROW
BEGIN
  DELETE FROM XS_KC WHERE 學號 = OLD.學號;
END$$
DELIMITER ;

驗證：DELETE FROM XS WHERE 學號='081101'; 再查詢 XS_KC 確認已級聯刪除。

2.4 刪除觸發器

DROP TRIGGER [schema_name.]trigger_name;

範例：DROP TRIGGER XS_DELETE;

三、總結與建議

• 善用存儲函數封裝可重用的計算邏輯，減少應用層代碼。
• 觸發器適合強制資料完整性、審計日誌、級聯操作等場景。
• 避免在觸發器中執行耗時操作，影響 DML 效能。
• 開發階段務必完整測試觸發器遞歸與巢狀呼叫，防止死循環。