MySQL 存儲過程與函數深度解析：從語法到實戰的完整指南

MySQL 存儲過程與函數深度解析：從語法到實戰的完整指南

在資料庫開發中，將業務邏輯下沉至存儲過程與函數，能有效減少網路往返、封裝複雜 SQL、提升維護性。本文系統梳理 MySQL 存儲程式的創建、參數機制、流程控制、游標操作及管理維護全流程，並附帶實戰優化建議。

一、核心概念與優勢

存儲過程是預編譯的 SQL 集合，駐留於 Server 端；函數則強調返回值特性，可嵌入 SQL 表達式。兩者共同優勢在於：

• 減少客戶端與伺服器數據傳輸
• 避免重複編寫 SQL，實現邏輯復用
• 利用資料庫引擎優勢執行複雜運算
• 通過權限控制強化資料安全

二、存儲過程創建詳解

CREATE PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic...] routine_body

參數模式：IN（輸入）、OUT（輸出）、INOUT（雙向）。每個參數由「模式 名稱 類型」組成，類型支援所有 MySQL 資料型別。

特性子句：

• LANGUAGE SQL：聲明主體語言
• DETERMINISTIC / NOT DETERMINISTIC：結果確定性，影響查詢快取
• CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA：SQL 存取限制
• SQL SECURITY DEFINER | INVOKER：執行權限上下文
• COMMENT 'string'：文檔註釋

三、存儲函數創建要點

CREATE FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic...] routine_body

關鍵差異：強制要求 RETURNS type 聲明返回類型；參數列表無 IN/OUT 模式，預設為輸入；函數體內必須包含 RETURN 語句。

四、變量與游標實戰

變量聲明：DECLARE var_name type [DEFAULT value];

賦值方式：SET var = expr; 或 SELECT col INTO var FROM table WHERE ...;

游標四步法：

1. DECLARE cur CURSOR FOR select_stmt;（必須在變量聲明之後、處理程式之前）
2. OPEN cur;
3. FETCH cur INTO var_list; 循環讀取
4. CLOSE cur; 釋放資源

提示：游標適用於逐行處理邏輯，大批量操作優先考慮集合式 SQL。

五、流程控制語句速查表

六、調用、查看與維護管理

• 調用過程：CALL sp_name([params]);
• 調用函數：SELECT func_name([params]); 或嵌入 SQL 表達式
• 狀態查看：SHOW PROCEDURE/FUNCTION STATUS [LIKE 'pattern'];
• 定義查看：SHOW CREATE PROCEDURE/FUNCTION sp_name;
• 元數據查詢：SELECT * FROM information_schema.ROUTINES;
• 修改特性：ALTER PROCEDURE/FUNCTION sp_name [characteristic...];
• 刪除物件：DROP PROCEDURE/FUNCTION [IF EXISTS] sp_name;

七、實戰優化建議

1. 業務邏輯過於複雜時，評估是否適合下沉，避免「資料庫變業務層」
2. 合理使用 DETERMINISTIC 開啟查詢快取
3. 游標循環中避免高耗操作，必要時改用臨時表或集合式寫法
4. 建立命名規範：sp_ 前綴標識過程，fn_ 標識函數
5. 版本控管：將 DDL 腳本納入 Git 管理，實現 Schema 變更可追溯

掌握存儲程式開發，能讓你在資料庫層面構建高效、安全、可維護的業務邏輯閉環，是資深 DBA 與後端工程師的必修技能。