| 特性 | 存儲過程 | 存儲函數 |
|---|---|---|
| 創建語法 | CREATE PROCEDURE | CREATE FUNCTION |
| 調用方式 | CALL proc_name() | SELECT func_name() (在 SQL 中) |
| 返回值 | 無直接返回，通過 OUT/INOUT 參數 | 必須 返回單一值 (RETURNS) |
| 事務控制 | 可執行 COMMIT/ROLLBACK | **不可** 顯式事務控制 |
| 調用限制 | 可調用函數、其他過程 | 不可調用過程，可調用其他函數 |
| 適用場景 | 複雜業務邏輯、批量處理、DDL、事務管理 | 計算、轉換、單值查詢、SQL 表達式複用 |
統稱「存儲例程」。MySQL 5.0+ 正式支持。
CREATE PROCEDURE proc_name ([IN|OUT|INOUT] param_name type, ...)
[特性 ...]
BEGIN
-- SQL 語句，每句以 ; 結尾
ENDCREATE FUNCTION func_name ([param_name type, ...])
RETURNS return_type
[特性 ...]
BEGIN
-- 必須包含 RETURN 值
RETURN expression;
END@var 接收**@var-- IN：值傳遞，外部不變
CREATE PROCEDURE p_in(IN id INT)
BEGIN
SET id = id + 1;
SELECT id AS inner_id;
END;
SET @x = 10; CALL p_in(@x); SELECT @x AS outer_x; -- inner=11, outer=10
-- OUT：單向輸出，外部初始值被忽略
CREATE PROCEDURE p_out(OUT id INT)
BEGIN
SELECT id AS init_id; -- NULL
SET id = 100;
SELECT id AS final_id; -- 100
END;
SET @y = 999; CALL p_out(@y); SELECT @y AS outer_y; -- outer=100
-- INOUT：雙向，引用傳遞語義
CREATE PROCEDURE p_inout(INOUT id INT)
BEGIN
SELECT id AS init_id; -- 10
SET id = id * 2;
SELECT id AS final_id; -- 20
END;
SET @z = 10; CALL p_inout(@z); SELECT @z AS outer_z; -- outer=20-- 局部變量：僅在 BEGIN...END 塊內有效，必須在塊首聲明
DECLARE var_name type [DEFAULT value];
DECLARE a INT DEFAULT 0;
DECLARE b VARCHAR(50);
-- 賦值方式
SET a = 100;
SET b = 'hello';
SELECT COUNT(*) INTO a FROM users; -- 查詢結果賦值
SELECT id, name INTO a, b FROM users LIMIT 1; -- 多列賦值-- IF...ELSEIF...ELSE
IF condition1 THEN
stmt1;
ELSEIF condition2 THEN
stmt2;
ELSE
stmt3;
END IF;
-- CASE...WHEN...THEN...END CASE
CASE
WHEN condition1 THEN stmt1;
WHEN condition2 THEN stmt2;
ELSE stmt3;
END CASE;
-- 簡單 CASE（值比較）
CASE expr
WHEN val1 THEN stmt1;
WHEN val2 THEN stmt2;
ELSE stmt3;
END CASE;-- WHILE：先判斷後執行
WHILE condition DO
stmt_list;
END WHILE;
-- REPEAT：先執行後判斷（至少一次），類似 do-while
REPEAT
stmt_list;
UNTIL condition
END REPEAT;
-- LOOP：無條件無限循環，需配合 LEAVE/ITERATE 退出
[label:] LOOP
stmt_list;
IF exit_cond THEN LEAVE label; END IF;
IF skip_cond THEN ITERATE label; END IF;
END LOOP [label];LEAVE label：跳出指定標籤的循環或 BEGIN...END 塊ITERATE label：跳過本次循環剩餘語句，直接進入下一次迭代DELIMITER $$
DROP PROCEDURE IF EXISTS `pro_prime` $$
CREATE PROCEDURE `pro_prime`(IN max_num INT)
BEGIN
DECLARE i INT DEFAULT 2;
DECLARE j INT;
DECLARE is_prime TINYINT;
-- 結果表（臨時表避免殘留）
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_primes (prime INT PRIMARY KEY);
TRUNCATE TABLE tmp_primes;
outer_loop: WHILE i <= max_num DO
SET is_prime = 1;
SET j = 2;
inner_loop: WHILE j * j <= i DO
IF i % j = 0 THEN
SET is_prime = 0;
LEAVE inner_loop;
END IF;
SET j = j + 1;
END WHILE inner_loop;
IF is_prime = 1 THEN
INSERT INTO tmp_primes VALUES (i);
END IF;
SET i = i + 1;
END WHILE outer_loop;
SELECT * FROM tmp_primes;
END $$
DELIMITER ;
-- 調用
CALL pro_prime(100); -- 返回 100 以內所有質數DELIMITER //
CREATE PROCEDURE p1() BEGIN SELECT 'Step 1'; END//
CREATE PROCEDURE p2() BEGIN SELECT 'Step 2'; END//
CREATE PROCEDURE p3()
BEGIN
CALL p1();
CALL p2();
SELECT 'All done';
END//
DELIMITER ;
CALL p3();MySQL 默認分隔符 ;。存儲過程體內含多條語句，直接執行腳本會被過早截斷。
-- 腳本文件標準模板
DELIMITER $$
DROP PROCEDURE IF EXISTS `my_proc` $$
CREATE PROCEDURE `my_proc`()
BEGIN
-- 過程體
SELECT 1;
SELECT 2;
END $$
DELIMITER ; -- 恢復默認分隔符圖形化工具通常自動處理分隔符，手工執行腳本**必須**手動管理。
| 操作 | 語句 |
|---|---|
| 刪除過程 | DROP PROCEDURE [IF EXISTS] proc_name; |
| 刪除函數 | DROP FUNCTION [IF EXISTS] func_name; |
| 查看過程狀態 | SHOW PROCEDURE STATUS [LIKE 'pattern'];\G |
| 查看函數狀態 | SHOW FUNCTION STATUS [LIKE 'pattern'];\G |
| 查看創建語法 | SHOW CREATE PROCEDURE db_name.proc_name;\G |
| 查看函數創建語法 | SHOW CREATE FUNCTION db_name.func_name;\G |
| 查看定義信息 | SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA='db' AND ROUTINE_NAME='proc';\G |
CREATE PROCEDURE p()
[特性列表]
BEGIN ... END
特性列表（可組合）：
LANGUAGE SQL -- 唯一支持的語言
| [NOT] DETERMINISTIC -- 確定性：相同輸入必產生相同輸出（影響複製、優化器）
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } -- SQL 訪問級別
| SQL SECURITY { DEFINER | INVOKER } -- 執行權限上下文：定義者/調用者
| COMMENT 'string' -- 註釋UUID(), RAND(), NOW() 等**必須標記 NOT DETERMINISTIC**EXECUTE 權限。INVOKER：以調用者權限執行，需調用者擁有底層表權限-- 表結構
CREATE TABLE scores (
stu_id INT PRIMARY KEY,
name VARCHAR(20),
english INT,
math INT,
physics INT
);
DELIMITER $$
DROP PROCEDURE IF EXISTS calc_stat $$
CREATE PROCEDURE calc_stat(IN total_students INT)
BEGIN
DECLARE v_id INT DEFAULT 1;
DECLARE v_name VARCHAR(20);
DECLARE v_e, v_m, v_p INT;
DECLARE v_total INT;
DECLARE v_avg DECIMAL(5,2);
-- 結果表（臨時表，會話結束自動清理）
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_stat (
stu_id INT, name VARCHAR(20), english INT, math INT, physics INT,
total INT, average DECIMAL(5,2)
);
TRUNCATE TABLE tmp_stat;
REPEAT
SELECT english, math, physics, name
INTO v_e, v_m, v_p, v_name
FROM scores WHERE stu_id = v_id;
SET v_total = v_e + v_m + v_p;
SET v_avg = v_total / 3;
INSERT INTO tmp_stat VALUES (v_id, v_name, v_e, v_m, v_p, v_total, v_avg);
SET v_id = v_id + 1;
UNTIL v_id > total_students
END REPEAT;
SELECT * FROM tmp_stat ORDER BY average DESC;
-- 臨時表會話結束自動銷毀，無需手動 DROP
END $$
DELIMITER ;
-- 調用（假設 10 位學生）
CALL calc_stat(10);SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='...' 拋錯INSERT ... SELECT, UPDATE ... JOIN）替代CREATE TEMPORARY TABLE，會話隔離、自動清理EXECUTE 權限，不授予底層表 DML 權限MySQL 存儲過程/函數提供了強大的服務端程序化能力，但**並非銀彈**。掌握語法、參數模式、流程控制、分隔符管理是基礎；理解執行上下文、權限模型、複製影響是進階；**「業務邏輯應用層，數據密集操作存儲層」** 是架構取捨的黃金法則。合理使用，能解決批量處理、跨表原子操作、減少網絡往返等棘手問題；濫用則會成為維護噩夢。
