MySQL 5 存儲過程與函數編寫實戰指南

前言

MySQL 5.0 版本起正式支援存儲過程與函數，近期有空深入研究，整理這份實戰筆記供參考。

基本語法結構

存儲過程

CREATE PROCEDURE 過程名稱 ([過程參數[,...]]) [特性 ...] 過程體

函數

CREATE FUNCTION 函數名稱 ([函數參數[,...]]) RETURNS 返回類型 [特性 ...] 函數體

參數與特性說明

過程參數模式

• IN：輸入參數（預設）
• OUT：輸出參數
• INOUT：輸入輸出參數

格式：[IN | OUT | INOUT] 參數名 參數類型

函數參數

格式：參數名 參數類型（函數參數預設為 IN 模式）

返回類型

任何有效的 MySQL 資料類型皆可。

特性子句

LANGUAGE SQL 
| [NOT] DETERMINISTIC 
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } 
| SQL SECURITY { DEFINER | INVOKER } 
| COMMENT 'string'

本文不深入探討 MySQL 專有特性，因其與 SQL 標準相容性較低。

開發實戰注意事項

1. 註釋語法

MySQL 支援兩種註釋方式：

• --：單行註釋
• /* ... */：多行區塊註釋

2. 變數宣告

• 區域變數必須使用 DECLARE 宣告
• 使用者變數（臨時變數）可直接以 @ 前綴使用，如 @myVar

3. 分隔符處理（關鍵）

在 MySQL Administrator 等圖形工具中直接編輯時無需特殊處理；但若在腳本檔中批量匯入存儲過程/函數，因 MySQL 預設以 ; 為分隔符，會導致過程體內的每條語句被誤判為結束而報錯。

解決方案：使用 DELIMITER 關鍵字暫時變更分隔符，結束後再改回。

DELIMITER $$
-- 這裡編寫完整的 CREATE PROCEDURE / FUNCTION 陳述式
-- 過程體內的 ; 不會被視為結束符
DELIMITER ;

4. 內建函數差異

MySQL 支援大量內建函數，部分與 Oracle、Informix、Sybase 等商用資料庫一致，但也存在：

• 功能相同但名稱不同
• 名稱相同但功能不同

從其他資料庫遷移的 DBA 需特別留意此差異。

5. 除錯技巧

目前尚未深入研究 MySQL 官方除錯工具。編譯錯誤相對容易定位；業務邏輯除錯可採用「建立除錯表」的笨辦法：在過程體關鍵流程點插入記錄，觀察執行軌跡。雖然原始但相當實用。