MySQL 預存程序與函數深度解析：建立、變數、條件處理與最佳實務

MySQL 5.0+ 支援預存程序與函數，將商業邏輯封裝於資料庫層，減少網路往返、提升效能與安全性。本文系統說明建立語法、參數模式、特性選項、變數操作、條件與錯誤處理器。

1. 建立預存程序

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

-- 範例：統計部門員工數
CREATE PROCEDURE num_from_employee (IN emp_id INT, OUT count_num INT)
READS SQL DATA
BEGIN
  SELECT COUNT(*) INTO count_num FROM employee WHERE d_id = emp_id;
END

參數模式：IN（輸入）、OUT（輸出）、INOUT（雙向）。
特性選項：LANGUAGE SQL、DETERMINISTIC、READS SQL DATA/MODIFIES SQL DATA、SQL SECURITY DEFINER|INVOKER、COMMENT。
分隔符：建立時先 DELIMITER //，結束後 DELIMITER ; 恢復。

2. 建立預存函數

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

-- 範例：依員工編號取姓名
CREATE FUNCTION name_from_employee (emp_id INT)
RETURNS VARCHAR(20)
BEGIN
  RETURN (SELECT name FROM employee WHERE num = emp_id);
END

函數參數無 IN/OUT，僅有名稱與型別；必須指定 RETURNS 型別；函數體內需 RETURN 值。

3. 變數宣告與賦值

-- 宣告
DECLARE var_name type [DEFAULT value];
DECLARE my_sql INT DEFAULT 10;

-- SET 賦值
SET var_name = expr [, var_name = expr] ...;
SET my_sql = 30;

-- SELECT INTO 賦值
SELECT col_name INTO var_name FROM table_name WHERE condition;
SELECT d_id INTO my_sql FROM employee WHERE id = 2;

4. 條件與錯誤處理器

定義條件

DECLARE condition_name CONDITION FOR SQLSTATE '42S02';  -- 或 mysql_error_code 1146

定義處理器

DECLARE handler_type HANDLER FOR condition_value [...] sp_statement
handler_type: CONTINUE | EXIT | UNDO(暫不支援)
condition_value: SQLSTATE | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION | mysql_error_code

六種常見寫法

-- 1. 捕獲 SQLSTATE
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'CAN NOT FIND';
-- 2. 捕獲錯誤碼
DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'CAN NOT FIND';
-- 3. 先定義條件再引用
DECLARE can_not_find CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR can_not_find SET @info = 'CAN NOT FIND';
-- 4. SQLWARNING (01 開頭)
DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR';
-- 5. NOT FOUND (02 開頭)
DECLARE EXIT HANDLER FOR NOT FOUND SET @info = 'CAN NOT FIND';
-- 6. SQLEXCEPTION (其他)
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR';

建議：預期可處理的錯誤用 CONTINUE；嚴重錯誤用 EXIT 終止；MySQL 暫不支援 UNDO。

最佳實務：
• 明確指定 READS SQL DATA 或 MODIFIES SQL DATA 讓優化器更精準。
• 善用 COMMENT 記錄用途與版本。
• 錯誤處理器盡量具體捕獲，避免吞沒非預期異常。
• 複雜邏輯拆解為多個程序/函數，單一職責。