為什麼使用預存程序?
- 封裝業務邏輯:將複雜 SQL 移出應用程式碼,減少網路往返
- 效能優化:預編譯、減少解析開銷、批次處理
- 安全性:授權執行權限而非直接表權限,防 SQL 注入
- 可維護性:集中管理資料邏輯,修改不需部署應用
基本語法結構
CREATE PROCEDURE 程序名 ([IN|OUT|INOUT] 參數名 型別, ...)
BEGIN
-- 宣告區域變數
DECLARE 變數名 型別 [DEFAULT 預設值];
-- 邏輯主體
SQL 陳述式;
流程控制;
-- 結束
END;
完整範例:建立與呼叫
1. 建立簡單加法程序
DROP PROCEDURE IF EXISTS pr_add;
CREATE PROCEDURE pr_add (
IN a INT,
IN b INT
)
BEGIN
DECLARE c INT;
IF a IS NULL THEN SET a = 0; END IF;
IF b IS NULL THEN SET b = 0; END IF;
SET c = a + b;
SELECT c AS sum_result;
-- 不能使用 RETURN,只能用 SELECT 或 OUT 參數回傳
END;
2. 呼叫方式
-- 直接傳值
CALL pr_add(10, 20);
-- 使用使用者變數
SET @a = 10, @b = 20;
CALL pr_add(@a, @b);
3. OUT/INOUT 參數回傳值
CREATE PROCEDURE pr_calc (
IN a INT,
IN b INT,
OUT sum_result INT,
OUT diff_result INT
)
BEGIN
SET sum_result = a + b;
SET diff_result = a - b;
END;
-- 呼叫
CALL pr_calc(10, 5, @sum, @diff);
SELECT @sum, @diff; -- 15, 5
關鍵語法規則(避坑指南)
| 規則 | 說明 |
程序名後必須加 () | 即使無參數:CREATE PROCEDURE p() |
參數名不可加 @ | IN a INT 正確,IN @a INT 錯誤 |
| 不支援參數預設值 | 需在程式內用 IF NULL THEN SET 處理 |
不需 AS 關鍵字 | SQL Server 需要,MySQL 不需要 |
多條語句需 BEGIN ... END | 單條可省略 |
每句結尾必須加分號 ; | 包括 DECLARE、SET、IF 等 |
| 註解風格 | /* 多行 */、-- 單行 (後接空格)、# 單行 |
不能使用 RETURN | 僅限 FUNCTION 使用 |
呼叫必須加 () | CALL pr_name(); |
流程控制結構
條件判斷
IF condition THEN
statements;
ELSEIF condition2 THEN
statements;
ELSE
statements;
END IF;
-- CASE 語法
CASE expr
WHEN val1 THEN statements;
WHEN val2 THEN statements;
ELSE statements;
END CASE;
迴圈
-- WHILE 迴圈
WHILE_LOOP: WHILE i < 10 DO
SET i = i + 1;
IF i = 5 THEN ITERATE WHILE_LOOP; END IF; -- 繼續下一輪
IF i = 8 THEN LEAVE WHILE_LOOP; END IF; -- 跳出迴圈
END WHILE WHILE_LOOP;
-- REPEAT UNTIL
REPEAT
SET i = i + 1;
UNTIL i >= 10 END REPEAT;
-- LOOP (需配合 LEAVE 跳出)
MY_LOOP: LOOP
SET i = i + 1;
IF i >= 10 THEN LEAVE MY_LOOP; END IF;
END LOOP MY_LOOP;
常用內建函數速查(預存程序常用)
字串函數
CONCAT(str1, str2, ...):連接,任一 NULL 結果為 NULL
CONCAT_WS(sep, str1, str2, ...):帶分隔符連接,忽略 NULL
SUBSTRING(str, pos, len):擷取子字串,**位置從 1 開始**
TRIM([BOTH|LEADING|TRAILING] [char] FROM str):去除空白/指定字元
REPLACE(str, from, to)、UPPER()/LOWER()、LENGTH()
數學函數
ROUND(num, decimals):四捨五入,ROUND(1.567,2)=1.57
FLOOR()/CEILING()、ABS()、MOD()、POWER()、RAND()
日期時間函數
NOW()/CURRENT_TIMESTAMP、DATE()/TIME()
DATE_ADD(date, INTERVAL n UNIT)、DATE_SUB()
DATEDIFF(d1, d2)、TIMESTAMPDIFF(UNIT, d1, d2)
DATE_FORMAT(date, '%Y-%m-%d %H:%i:%s')、STR_TO_DATE()
DAY()/MONTH()/YEAR()、DAYNAME()/MONTHNAME()
管理指令
-- 查看所有預存程序
SHOW PROCEDURE STATUS [WHERE Db='dbname'];
-- 查看特定程序定義
SHOW CREATE PROCEDURE pr_add;
-- 刪除程序
DROP PROCEDURE IF EXISTS pr_add;
預存程序 vs 函數 (FUNCTION)
| 特性 | PROCEDURE | FUNCTION |
| 回傳值 | OUT 參數 / 結果集 | 單一值 (RETURN) |
| 呼叫方式 | CALL proc() | SELECT func() |
| 可用於 SQL 表達式 | 否 | 是 |
| 支援動態 SQL | 是 (PREPARE) | 否 |
| 事務控制 | 可 COMMIT/ROLLBACK | 不可 |
最佳實踐建議
- 命名規範:
sp_業務域_動作(如 sp_order_create)
- 參數預設用
IN,明確標示 OUT/INOUT
- 避免過度複雜邏輯,超過 200 行考慮拆分或移至應用層
- 嚴禁在預存程序中做大量迴圈單筆 INSERT/UPDATE,改用集合操作
- 版本控制:將
SHOW CREATE PROCEDURE 輸出納入程式碼倉庫