您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 5.0 儲存程序完整指南：建立、呼叫、流程控制與常用運算子

編輯：MySQL 綜合教學

前言

MySQL 5.0 引入儲存程序，將商務邏輯封裝在資料庫層，減少網路往返、提升安全性與效能。本文系統整理儲存程序建立、參數傳遞、流程控制、運算子與管理指令。

一、建立儲存程序

基本語法

CREATE PROCEDURE sp_name ([param_list])
BEGIN
    -- 程序主體
    SQL_statements;
END;

分隔符變更 (關鍵)

DELIMITER //

CREATE PROCEDURE sp_get_user(IN p_id INT, OUT p_name VARCHAR(50))
BEGIN
    SELECT name INTO p_name FROM users WHERE id = p_id;
END //

DELIMITER ;

DELIMITER //：將語句結束符從 ; 改為 //，避免程序內部 ; 過早結束
建立完成後必須恢復 DELIMITER ;

參數模式

模式	關鍵字	說明	範例
輸入	IN	呼叫者傳入，程序內可讀不可改 (預設)	IN p_id INT
輸出	OUT	程序內設定，呼叫者可讀	OUT p_name VARCHAR(50)
輸入輸出	INOUT	呼叫者傳入，程序可修改，呼叫者可讀新值	INOUT p_count INT

完整範例

DELIMITER //

CREATE PROCEDURE sp_transfer(
    IN p_from_id INT,
    IN p_to_id INT,
    IN p_amount DECIMAL(10,2),
    OUT p_result VARCHAR(100)
)
BEGIN
    DECLARE v_from_balance DECIMAL(10,2);
    DECLARE v_to_balance DECIMAL(10,2);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_result = 'FAILED: SQL Exception';
    END;
    
    START TRANSACTION;
    
    SELECT balance INTO v_from_balance FROM accounts WHERE id = p_from_id FOR UPDATE;
    SELECT balance INTO v_to_balance FROM accounts WHERE id = p_to_id FOR UPDATE;
    
    IF v_from_balance >= p_amount THEN
        UPDATE accounts SET balance = balance - p_amount WHERE id = p_from_id;
        UPDATE accounts SET balance = balance + p_amount WHERE id = p_to_id;
        COMMIT;
        SET p_result = 'SUCCESS';
    ELSE
        ROLLBACK;
        SET p_result = 'FAILED: Insufficient balance';
    END IF;
END //

DELIMITER ;

二、呼叫儲存程序

-- 基本呼叫 (需帶括號，即使無參數)
CALL sp_name();

-- 帶參數呼叫
CALL sp_get_user(1, @name);
SELECT @name;  -- 查看 OUT 參數

CALL sp_transfer(1, 2, 100.00, @result);
SELECT @result;

三、刪除與查看

-- 刪除
DROP PROCEDURE IF EXISTS sp_name;

-- 查看所有程序狀態
SHOW PROCEDURE STATUS [WHERE Db = 'mydb'];

-- 查看特定程序定義
SHOW CREATE PROCEDURE sp_name;

⚠️ 儲存程序不能在另一個儲存程序中刪除，只能呼叫。

四、流程控制結構

1. 區塊與標籤

BEGIN
    -- 陳述式
END;

-- 帶標籤 (可用於 LEAVE 跳出)
my_label: BEGIN
    SELECT 1;
    LEAVE my_label;  -- 跳出區塊
    SELECT 2;  -- 不會執行
END my_label;

2. 條件判斷 (IF / CASE)

-- IF
IF condition THEN
    statements;
ELSEIF condition THEN
    statements;
ELSE
    statements;
END IF;

-- CASE (兩種形式)
CASE expr
    WHEN value1 THEN statements;
    WHEN value2 THEN statements;
    ELSE statements;
END CASE;

CASE
    WHEN condition1 THEN statements;
    WHEN condition2 THEN statements;
    ELSE statements;
END CASE;

3. 迴圈

-- WHILE
while_label: WHILE condition DO
    statements;
    IF leave_condition THEN LEAVE while_label; END IF;
END WHILE while_label;

-- LOOP (需自行 LEAVE)
loop_label: LOOP
    statements;
    IF leave_condition THEN LEAVE loop_label; END IF;
END LOOP loop_label;

-- REPEAT UNTIL (至少執行一次)
repeat_label: REPEAT
    statements;
UNTIL condition
END REPEAT repeat_label;

4. 迴圈控制

LEAVE label：跳出指定標籤區塊/迴圈 (相當 break)
ITERATE label：跳過本次迭代，進入下一次 (相當 continue)

五、變數宣告與使用

-- 區域變數 (僅在 BEGIN...END 內有效)
DECLARE v_count INT DEFAULT 0;
DECLARE v_name VARCHAR(50) DEFAULT 'Unknown';

-- 使用者變數 (Session 級，跨程序可用)
SET @total = 100;
SELECT @total;

-- 系統變數
SET @@session.sql_mode = 'STRICT_TRANS_TABLES';

六、常用運算子完整表

算術運算子

+  -  *  /  DIV (整除)  % (取模)
SET @a = 10 + 5;   -- 15
SET @b = 10 DIV 3; -- 3
SET @c = 10 % 3;   -- 1

比較運算子

=  <=> (NULL 安全等於)  <> !=  <  <=  >  >=
BETWEEN ... AND ...
IN (...)  NOT IN (...)
IS NULL  IS NOT NULL
LIKE  NOT LIKE
REGEXP  NOT REGEXP  (正則表達式)

<=> 特殊：NULL <=> NULL 為 TRUE，NULL = NULL 為 NULL。

邏輯運算子

AND  OR  XOR  NOT
-- 真值表 (TRUE=1, FALSE=0, NULL=NULL)
-- AND: TRUE+TRUE=TRUE, 其他含 FALSE 為 FALSE, 含 NULL 為 NULL
-- OR:  含 TRUE 為 TRUE, FALSE+FALSE=FALSE, 含 NULL 為 NULL
-- XOR: TRUE+FALSE=TRUE, 相同為 FALSE, 含 NULL 為 NULL

位元運算子

| (OR)  & (AND)  << (左移)  >> (右移)  ~ (NOT)
SET @flags = 1 | 2;  -- 3 (二進位 01 | 10 = 11)

七、錯誤處理與游標

條件處理器

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    ROLLBACK;
    SET @error_msg = 'SQL Error';
END;

DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'  -- Duplicate key
BEGIN
    SET @dup_key = 1;
END;

DECLARE CONTINUE HANDLER FOR NOT FOUND  -- 游標結束
BEGIN
    SET @done = 1;
END;

游標

DECLARE cur CURSOR FOR SELECT id, name FROM users WHERE status = 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET @done = 1;

OPEN cur;
read_loop: LOOP
    FETCH cur INTO v_id, v_name;
    IF @done THEN LEAVE read_loop; END IF;
    -- 處理邏輯
END LOOP;
CLOSE cur;

八、權限與安全性

GRANT EXECUTE ON PROCEDURE mydb.sp_name TO 'user'@'host';
DEFINER vs INVOKER (SQL SECURITY 特性)：定義者權限/呼叫者權限
預設 DEFINER：以建立者權限執行，呼叫者只需 EXECUTE 權限

九、最佳實踐與限制

建議	說明
業務邏輯下沉	複雜交易、批次處理、資料驗證適合放入 SP
避免過度使用	簡單 CRUD 用應用層，SP 偵錯困難、版控弱
參數驗證前置	程序開頭驗證參數合法性
交易控制	明確 START/COMMIT/ROLLBACK，避免隱式提交
避免動態 SQL	`PREPARE/EXECUTE` 效能差、注入風險
限制	不支援觸發程序內呼叫 SP、遞迴深度有限、無套件概念