MySQL 5.0 建立儲存過程完整指南：DELIMITER 變更、參數傳遞、流程控制與觸發器

MySQL 5.0 引入儲存過程，但解析器遇 ; 即終止敘述，導致包含多條 SQL 的過程建立失敗。本文詳解 DELIMITER 變更技巧、完整語法、參數模式、流程控制與觸發器實戰。

核心問題與解法

預設分隔符 ; 會在 INSERT ...; 處截斷。解法：暫時變更分隔符：

DELIMITER //
CREATE PROCEDURE addStudent()
BEGIN
  INSERT INTO student (stu_name, age) VALUES ('22', '2');
END
//
DELIMITER ;

一、建立儲存過程

基本語法

CREATE PROCEDURE sp_name([IN|OUT|INOUT] param type, ...)
[LANGUAGE SQL] [NOT] DETERMINISTIC
[{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}]
[SQL SECURITY {DEFINER|INVOKER}]
[COMMENT 'string']
routine_body

參數模式

• IN：輸入參數（預設），呼叫端傳入
• OUT：輸出參數，過程內賦值返回呼叫端
• INOUT：雙向，呼叫端傳入並接收修改後值

範例：帶參數插入學生

DELIMITER //
CREATE PROCEDURE addStudent(IN p_name VARCHAR(15), IN p_age VARCHAR(3))
BEGIN
  INSERT INTO student (stu_name, age) VALUES (p_name, p_age);
END
//
DELIMITER ;

二、呼叫與刪除

CALL addStudent('Kevin', '23');  -- 必須加 ()
DROP PROCEDURE addStudent;

三、流程控制結構

變數宣告與賦值

DECLARE var_name type [DEFAULT value];
SET var_name = expr;
SELECT col INTO var_name FROM tbl WHERE ...;

範例：批次插入 100 筆

DELIMITER //
CREATE PROCEDURE batchInsert()
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < 100 DO
    INSERT INTO student (stu_name, age) VALUES ('kevin', '23');
    SET i = i + 1;
  END WHILE;
END
//
DELIMITER ;

四、常用檢視指令

SHOW PROCEDURE STATUS;                    -- 列出所有過程基本資訊
SHOW CREATE PROCEDURE sp_name;            -- 顯示建立 DDL
SELECT * FROM INFORMATION_SCHEMA.ROUTINES; -- 系統表查詢

五、函數（FUNCTION）與儲存過程差異

• 必須 RETURNS type 指定回傳型別
• 主體內必須 RETURN value
• 參數僅支援 IN 模式
• 呼叫方式：SELECT func_name(args);（可用於 SQL 表達式）

函數範例

CREATE FUNCTION cust_status(in_status CHAR(1))
RETURNS VARCHAR(20)
BEGIN
  DECLARE long_status VARCHAR(20);
  IF in_status = 'O' THEN SET long_status = 'Overdue';
  ELSEIF in_status = 'U' THEN SET long_status = 'Up to date';
  ELSEIF in_status = 'N' THEN SET long_status = 'New';
  END IF;
  RETURN long_status;
END;

六、觸發器

CREATE TRIGGER trigger_name
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON table_name
FOR EACH ROW
trigger_body

範例：更新後檢查餘額不得為負

CREATE TRIGGER account_balance_au
AFTER UPDATE ON account_balance FOR EACH ROW
BEGIN
  IF NEW.balance < 0 THEN
    SET NEW.balance = NULL;
  END IF;
END;

NEW.col 為新值，OLD.col 為舊值。BEFORE 觸發器可修改 NEW；AFTER 僅能讀取。

七、查看過程/函數/觸發器狀態

SHOW FUNCTION STATUS;
SHOW TRIGGERS;

掌握 DELIMITER 技巧與完整語法，即可在 MySQL 5.0+ 高效開發儲存邏輯。