MySQL 預存程序 (Stored Procedure) 建立、呼叫與管理實戰

前言

預存程序將商業邏輯封裝在資料庫層，減少網路傳輸、提升安全性與維護性。本文說明 MySQL 預存程序的語法、定界符處理、建立/呼叫/刪除操作與參數模式。

語法結構

CREATE PROCEDURE proc_name ([IN | OUT | INOUT] param_name data_type)
BEGIN
    statement_list
END

關鍵：定界符變更

MySQL 預設以分號 ; 結束語句，但預存程序內部包含多條語句，需暫時變更定界符：

DELIMITER //

建立完成後記得改回 DELIMITER ;

完整範例：Hello World

DELIMITER //

CREATE PROCEDURE hello()
BEGIN
    SELECT 'Hello World!';
END //

DELIMITER ;

-- 呼叫
CALL hello();

-- 結果
+-----------------+
| Hello World!    |
+-----------------+
| Hello World!    |
+-----------------+

參數模式詳解

帶參數範例：查詢用戶訂單總額

DELIMITER //

CREATE PROCEDURE get_user_order_total (
    IN p_user_id INT,
    OUT p_total DECIMAL(10,2)
)
BEGIN
    SELECT COALESCE(SUM(amount), 0) INTO p_total
    FROM orders
    WHERE user_id = p_user_id;
END //

DELIMITER ;

-- 呼叫
SET @total = 0;
CALL get_user_order_total(123, @total);
SELECT @total;  -- 顯示結果

管理指令

-- 查看預存程序
SHOW PROCEDURE STATUS WHERE Db = 'mydb';
SHOW CREATE PROCEDURE get_user_order_total;

-- 刪除
DROP PROCEDURE IF EXISTS hello;

重要限制與注意事項

• 無法在預存程序中刪除另一個預存程序 (原文提及)
• 支援呼叫其他預存程序 (巢狀呼叫)
• MySQL 5.0+ 才支援預存程序
• 除錯困難，建議複雜邏輯在應用層實現，資料庫層僅封裝核心資料操作
• 避免在高併發核心路徑使用複雜預存程序，增加資料庫 CPU 壓力

總結

掌握 DELIMITER 切換、IN/OUT/INOUT 參數模式、CALL 呼叫與 DROP 管理，即可在 MySQL 中落地預存程序。權衡封裝效益與除錯維護成本，適度使用。