MySQL 預存程序與函數完整教學：建立、變數、流程控制與權限管理

前言

本篇詳細解析 MySQL 預存程序與函數的建立語法、參數模式、變數作用域、條件處理、游標操作、流程控制結構，以及調用、查看、修改與刪除的完整生命週期管理。

預存程序建立語法

CREATE PROCEDURE sp_name ([proc_parameter]) [characteristics] routine_body

參數模式

• IN：輸入參數，呼叫時必須提供，內部修改不回傳（預設）
• OUT：輸出參數，內部修改可回傳給呼叫端
• INOUT：雙向參數，呼叫時提供，內部修改可回傳

特性選項

• LANGUAGE SQL：主體為 SQL 語句（目前唯一值）
• DETERMINISTIC：相同輸入必產生相同輸出（優化器提示）
• CONTAINS SQL / NO SQL / READS SQL DATA / MODIFIES SQL DATA：SQL 資料存取等級
• SQL SECURITY DEFINER | INVOKER：執行權限檢查模式
• COMMENT 'string'：說明文字

關鍵技巧：DELIMITER 變更

DELIMITER //
CREATE PROCEDURE Proc() BEGIN SELECT * FROM t3; END //
DELIMITER ;
CALL Proc();

必須將結束符號從 ; 改為 //，避免程序內部分號提前終止建立語句。

預存函數建立語法

CREATE FUNCTION func_name([func_parameter]) RETURNS type [characteristics] routine_body

關鍵差異：

• 必須有 RETURNS 宣告回傳型別
• 函數體需包含 RETURN value 語句
• 參數預設皆為 IN 模式，不支援 OUT/INOUT
• RETURNS 有 s，RETURN 無 s，寫錯會語法錯誤

函數範例

DELIMITER //
CREATE FUNCTION NameByT() RETURNS CHAR(50)
RETURN (SELECT NAME FROM t3 WHERE id = 2); //
DELIMITER ;

SELECT NameByT();  -- 調用方式同內建函數

變數宣告與賦值

區域變數

DECLARE var_name [, var_name]... type [DEFAULT value];
SET var_name = expr [, var_name = expr]...;
SELECT col_name INTO var_name FROM table WHERE ...;

使用者變數 (@var)

連線層級作用域，跨程序可存取，連線結束自動釋放。

條件與錯誤處理

宣告條件

DECLARE condition_name CONDITION FOR SQLSTATE '42000';
-- 或
DECLARE condition_name CONDITION FOR 1148;

宣告處理程序

DECLARE handler_type HANDLER FOR condition_value sp_statement

• CONTINUE：記錄錯誤繼續執行
• EXIT：離開當前 BEGIN...END 區塊
• UNDO：回滾（MySQL 暫不支援）

條件值：SQLSTATE、mysql_error_code、condition_name、SQLWARNING (01*)、NOT FOUND (02*)、SQLEXCEPTION (其他)。

實作範例

CREATE PROCEDURE handlerdemo()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
  SET @x = 1; INSERT INTO t8 VALUES(1);
  SET @x = 2; INSERT INTO t8 VALUES(1);  -- 重複主鍵觸發處理程序
  SET @x = 3;
END;

執行後 @x = 3 證明程序未中斷繼續執行至結尾。

游標操作

僅能在預存程序/函數中使用，須在處理程序宣告前定義。

1. DECLARE cursor_name CURSOR FOR select_statement;
2. OPEN cursor_name;
3. FETCH cursor_name INTO var_name [, var_name]...;
4. CLOSE cursor_name;

流程控制結構

調用與權限

• 程序：CALL sp_name([params])
• 函數：SELECT func_name([params])（同內建函數）
• 需具備 EXECUTE 權限，資訊存於 information_schema.USER_PRIVILEGES

查看、修改與刪除

查看狀態

SHOW PROCEDURE STATUS LIKE 'pattern';
SHOW FUNCTION STATUS LIKE 'pattern';

查看定義

SHOW CREATE PROCEDURE sp_name;
SHOW CREATE FUNCTION func_name;

查詢 information_schema.Routines

SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME = 'name';

修改（僅特性，不含主體）

ALTER PROCEDURE sp_name [characteristics];
ALTER FUNCTION func_name [characteristics];

若需修改程式碼，必須 DROP 後重建。

刪除

DROP PROCEDURE sp_name;
DROP FUNCTION func_name;

實用提示

• 程序可巢狀呼叫其他程序
• 參數名避免與資料表欄位同名
• 支援中文參數：CHARACTER SET gbk
• DECLARE 必須寫在程序內部