MySQL 5.0 引入完整程序式 SQL 能力。本文彙整 DDL/DML、儲存過程、函數、觸發器、流程控制、游標、動態 SQL 核心語法，供開發快速查閱。
| 操作 | 語法 |
|---|---|
| 建立過程 | CREATE PROCEDURE sp_name([IN|OUT|INOUT] param type...) routine_body |
| 建立函數 | CREATE FUNCTION fn_name(params) RETURNS type routine_body |
| 修改過程 | ALTER PROCEDURE sp_name [characteristics...] |
| 修改函數 | ALTER FUNCTION fn_name [characteristics...] |
| 刪除過程 | DROP PROCEDURE [IF EXISTS] sp_name |
| 刪除函數 | DROP FUNCTION [IF EXISTS] fn_name |
| 查看建立碼 | SHOW CREATE PROCEDURE/FUNCTION sp_name |
| 查看狀態 | SHOW PROCEDURE/FUNCTION STATUS [LIKE 'pattern'] |
| 呼叫過程 | CALL sp_name(args) (必須加括號) |
| 結構 | 語法 |
|---|---|
| 區塊 | BEGIN ... END / label: BEGIN ... END label; |
| 變數宣告 | DECLARE var type [DEFAULT val]; |
| 賦值 | SET var = expr; / SELECT col INTO var FROM tbl WHERE ...; |
| IF | IF cond THEN ... ELSEIF ... ELSE ... END IF; |
| CASE | CASE WHEN cond THEN ... ELSE ... END CASE; 或 CASE expr WHEN val THEN ... END CASE; |
| WHILE | [label:] WHILE expr DO ... END WHILE [label]; |
| LOOP | [label:] LOOP ... END LOOP [label]; (需 LEAVE) |
| REPEAT | [label:] REPEAT ... UNTIL expr END REPEAT [label]; |
| 跳出 | LEAVE label; |
| 繼續 | ITERATE label; |
| 返回 | RETURN expr; (僅函數) |
DECLARE cur CURSOR FOR SELECT col FROM tbl WHERE ...;
OPEN cur;
FETCH cur INTO var1, var2...;
CLOSE cur;需宣告 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; 處理結束。
CREATE TRIGGER trigger_name
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON table_name
FOR EACH ROW
trigger_bodyNEW.col：新值 (INSERT/UPDATE 可用，BEFORE 可修改)OLD.col：舊值 (UPDATE/DELETE 可用)AFTER UPDATE ON account FOR EACH ROW IF NEW.balance<0 THEN SET NEW.balance=NULL; END IF;PREPARE stmt FROM 'SELECT * FROM tbl WHERE id=?';
SET @id = 1;
EXECUTE stmt USING @id;
DEALLOCATE PREPARE stmt;SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='db';
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA='db';DELIMITER //
CREATE PROCEDURE batchInsert()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < 100 DO
INSERT INTO student (name, age) VALUES ('kevin', '23');
SET i = i + 1;
END WHILE;
END
//
DELIMITER ;掌握核心語法，即可在 MySQL 內層實現複雜業務邏輯，減少應用層往返。
