MySQL 預存程序與函式完整教學：建立、變數、游標、條件處理、修改刪除

MySQL 預存程序與函式完整教學：建立、變數、游標、條件處理、修改刪除

預存程序與函式將多條 SQL 封裝為單一可呼叫單位，提升重用性、效能與安全性。本文系統說明建立語法、參數模式、流程控制、游標操作與管理指令。

一、預存程序 vs 函式差異

二、建立預存程序

CREATE PROCEDURE proc_name ([IN|OUT|INOUT] param_name type, ...)
[characteristic ...] routine_body

特性參數

• LANGUAGE SQL：主體為 SQL (預設)
• DETERMINISTIC：相同輸入必得相同輸出 (預設)
• CONTAINS SQL / NO SQL / READS SQL DATA / MODIFIES SQL DATA
• SQL SECURITY DEFINER | INVOKER：定義者/呼叫者權限 (預設 DEFINER)
• COMMENT 'string'

範例：查詢所有員工薪資

DELIMITER $$
CREATE PROCEDURE proc_employee_sal()
COMMENT '查詢所有員工薪資'
BEGIN
    SELECT sal FROM t_employee;
END $$
DELIMITER ;

三、建立函式

CREATE FUNCTION func_name ([param_name type, ...])
RETURNS type
[characteristic ...] routine_body

範例：查詢指定員工薪資

DELIMITER $$
CREATE FUNCTION func_employee_sal(empno INT) RETURNS DECIMAL(10,2)
COMMENT '查詢指定員工薪資'
BEGIN
    RETURN (SELECT sal FROM t_employee WHERE empno = empno);
END $$
DELIMITER ;

四、程式體核心元素

1. 變數宣告與賦值

DECLARE var_name TYPE [DEFAULT value];
SET var_name = expr;
SELECT col_name INTO var_name FROM table WHERE condition;  -- 單行結果

2. 條件與處理器

-- 定義條件名稱
DECLARE condition_name CONDITION FOR SQLSTATE '45000';

-- 定義處理器
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @err = 1;
DECLARE EXIT HANDLER FOR NOT FOUND SET @done = 1;

3. 游標操作

DECLARE cur_name CURSOR FOR SELECT ...;
OPEN cur_name;
FETCH cur_name INTO var1, var2;
CLOSE cur_name;

• 游標指向結果集，逐行處理
• 效能較低，>1萬行建議改用集合操作
• 避免游標迴圈中 JOIN 表

五、修改與刪除

-- 修改特性
ALTER PROCEDURE proc_name [characteristic ...];
ALTER FUNCTION func_name [characteristic ...];

-- 刪除
DROP PROCEDURE proc_name;
DROP FUNCTION func_name;

六、最佳實務

• 命名規範：proc_ 前綴程序，func_ 前綴函式
• 複雜邏輯拆分為多個小程序/函式
• 參數驗證放在程式體開頭
• 使用 DELIMITER 避免分號衝突
• 權限控管：GRANT EXECUTE ON proc TO user