萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 預存程序入門完整指南:建立、呼叫、參數、流程控制與常用函數

為什麼使用預存程序?

  • 封裝業務邏輯:將複雜 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)

特性PROCEDUREFUNCTION
回傳值OUT 參數 / 結果集單一值 (RETURN)
呼叫方式CALL proc()SELECT func()
可用於 SQL 表達式否是
支援動態 SQL是 (PREPARE)否
事務控制可 COMMIT/ROLLBACK不可

最佳實踐建議

  • 命名規範:sp_業務域_動作(如 sp_order_create)
  • 參數預設用 IN,明確標示 OUT/INOUT
  • 避免過度複雜邏輯,超過 200 行考慮拆分或移至應用層
  • 嚴禁在預存程序中做大量迴圈單筆 INSERT/UPDATE,改用集合操作
  • 版本控制:將 SHOW CREATE PROCEDURE 輸出納入程式碼倉庫
copyright © 萬盛學電腦網 all rights reserved