您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 預存程序完整技術指南：從入門到進階的語法、權限、游標與異常處理

編輯：MySQL 資料庫相關知識

核心概念與優勢

特性	預存程序	使用者定義函數 (UDF)
返回值	0-N 個 OUT/INOUT 參數	單一標量值或 TABLE
調用方式	CALL sp_name() 獨立語句	嵌入 SQL 表達式 (SELECT func())
事務控制	支援 START/COMMIT/ROLLBACK	不支援 (隱含只讀)
動態 SQL	支援 PREPARE/EXECUTE	不支援
臨時表	可建立/操作	僅表變量
權限模型	DEFINER/INVOKER 權限切換	同預存程序
適用場景	複雜業務邏輯、批次處理、ETL、權限封裝	計算、轉換、查詢片段復用

基礎語法速查

建立與刪除

-- 刪除舊版本
DROP PROCEDURE IF EXISTS `sp_name`;

-- 建立 (分隔符變更避免衝突)
DELIMITER //

CREATE PROCEDURE `sp_name` (
    IN  p_in_param   INT,           -- 輸入參數
    OUT p_out_param  VARCHAR(100),  -- 輸出參數
    INOUT p_io_param DATETIME       -- 輸入輸出參數
)
[CHARACTERISTICS ...]  -- DETERMINISTIC, NO SQL, READS SQL DATA, MODIFIES SQL DATA, SQL SECURITY DEFINER/INVOKER
BEGIN
    -- 宣告區
    DECLARE v_local_var INT DEFAULT 0;
    DECLARE v_cursor CURSOR FOR SELECT col FROM tbl WHERE id = p_in_param;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;

    -- 執行區
    SET v_local_var = p_in_param * 2;
    SELECT name INTO p_out_param FROM users WHERE id = v_local_var;
    SET p_io_param = NOW();
END //

DELIMITER ;

調用與檢視

-- 調用
CALL sp_name(10, @out_val, @io_val);
SELECT @out_val, @io_val;

-- 檢視定義
SHOW CREATE PROCEDURE sp_name;
SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_NAME = 'sp_name';

變數、流程控制與動態 SQL

變數宣告與賦值

DECLARE v_int INT DEFAULT 0;
DECLARE v_str VARCHAR(100) DEFAULT 'init';
DECLARE v_dt DATETIME DEFAULT CURRENT_TIMESTAMP;

-- SET 賦值 (多變數同時)
SET v_int = 10, v_str = 'hello', v_dt = DATE_ADD(NOW(), INTERVAL 1 DAY);

-- SELECT ... INTO (單行結果)
SELECT COUNT(*), MAX(created_at) INTO v_int, v_dt FROM orders WHERE user_id = 100;

-- 使用者變數 (@var) 跨會話/語句持久
SET @total = (SELECT COUNT(*) FROM big_table);
SELECT @total;

流程控制結構

-- IF-ELSEIF-ELSE
IF v_score >= 90 THEN
    SET v_grade = 'A';
ELSEIF v_score >= 80 THEN
    SET v_grade = 'B';
ELSE
    SET v_grade = 'C';
END IF;

-- CASE (簡單/搜尋)
CASE v_status
    WHEN 1 THEN SET v_desc = 'Active';
    WHEN 2 THEN SET v_desc = 'Inactive';
    ELSE SET v_desc = 'Unknown';
END CASE;

CASE
    WHEN v_amt > 10000 THEN SET v_level = 'VIP';
    WHEN v_amt > 1000 THEN SET v_level = 'Gold';
    ELSE SET v_level = 'Normal';
END CASE;

-- 迴圈
-- WHILE (先判斷)
WHILE v_i < 10 DO
    INSERT INTO log VALUES (v_i);
    SET v_i = v_i + 1;
END WHILE;

-- REPEAT (後判斷，至少執行一次)
REPEAT
    FETCH cur INTO v_val;
    IF NOT v_done THEN
        INSERT INTO result VALUES (v_val);
    END IF;
UNTIL v_done END REPEAT;

-- LOOP + LEAVE/ITERATE (無條件迴圈)
read_loop: LOOP
    FETCH cur INTO v_val;
    IF v_done THEN LEAVE read_loop; END IF;
    IF v_val < 0 THEN ITERATE read_loop; END IF;  -- 跳過負值
    INSERT INTO positive VALUES (v_val);
END LOOP;

動態 SQL (PREPARE/EXECUTE)

SET @sql = CONCAT('SELECT * FROM ', @table_name, ' WHERE id = ?');
PREPARE stmt FROM @sql;
SET @id = 100;
EXECUTE stmt USING @id;
DEALLOCATE PREPARE stmt;

-- 注意：動態 SQL 無法直接使用常規變數，需用使用者變數 (@) 或參數標記 (?)

游標完整用法

特性限制

READ ONLY：不可更新游標指向的行
NON-SCROLLABLE：僅支援順向 FETCH，無法 BACK/RELATIVE
ASENSITIVE：對底表變更敏感度未定義 (MySQL 實作為 INSENSITIVE，即建立臨時表複本)

標準模板

CREATE PROCEDURE process_orders()
BEGIN
    DECLARE v_done INT DEFAULT 0;
    DECLARE v_order_id BIGINT;
    DECLARE v_amount DECIMAL(12,2);
    DECLARE v_user_id INT;

    -- 1. 宣告游標 (SELECT 不能有 INTO)
    DECLARE cur CURSOR FOR
        SELECT id, amount, user_id FROM orders
        WHERE status = 'PENDING' AND created_at > DATE_SUB(NOW(), INTERVAL 1 DAY)
        ORDER BY created_at;

    -- 2. 宣告 NOT FOUND 處理器 (必要)
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;

    -- 3. 開啟游標
    OPEN cur;

    -- 4. 迴圈讀取
    read_loop: LOOP
        FETCH cur INTO v_order_id, v_amount, v_user_id;
        IF v_done THEN LEAVE read_loop; END IF;

        -- 業務邏輯
        UPDATE accounts SET balance = balance - v_amount WHERE user_id = v_user_id;
        UPDATE orders SET status = 'PROCESSED' WHERE id = v_order_id;
    END LOOP;

    -- 5. 關閉游標
    CLOSE cur;
END;

異常處理與條件處理器

處理器類型

類型	行為	適用場景
CONTINUE	執行處理器語句後，繼續執行後續邏輯	記錄錯誤、設定旗標、嘗試替代邏輯
EXIT	執行處理器語句後，離開當前 BEGIN...END 區塊	嚴重錯誤終止、回滾事務
UNDO	尚未實作	—

條件值分類

-- 1. SQLSTATE 代碼 (標準、可攜)
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @dup_key = 1;  -- 完整性約束違反
DECLARE CONTINUE HANDLER FOR SQLSTATE '42000' SET @syntax_err = 1; -- 語法/權限錯誤

-- 2. 簡寫類別
DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN ... END;      -- 01 開頭
DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN ... END;         -- 02 開頭 (游標結束、SELECT 無結果)
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN ... END;      -- 其他所有錯誤

-- 3. 自訂命名條件
DECLARE deadlock CONDITION FOR SQLSTATE '40001';
DECLARE CONTINUE HANDLER FOR deadlock
BEGIN
    SET @retry_count = @retry_count + 1;
    IF @retry_count <= 3 THEN
        -- 重試邏輯 (需在外層迴圈)
    END IF;
END;

完整異常處理範例

CREATE PROCEDURE transfer_funds(
    IN p_from INT, IN p_to INT, IN p_amt DECIMAL(12,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;  -- 8.0+ 重新拋出原始錯誤給調用者
    END;

    DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
    BEGIN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient balance or account locked';
    END;

    START TRANSACTION;
    
    -- 悲觀鎖檢查餘額
    SELECT balance INTO @bal FROM accounts WHERE user_id = p_from FOR UPDATE;
    IF @bal < p_amt THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient balance';
    END IF;

    UPDATE accounts SET balance = balance - p_amt WHERE user_id = p_from;
    UPDATE accounts SET balance = balance + p_amt WHERE user_id = p_to;
    INSERT INTO transfers(from_id, to_id, amount, created_at) VALUES (p_from, p_to, p_amt, NOW());

    COMMIT;
END;

權限模型：DEFINER vs INVOKER

-- 1. DEFINER (預設) - 以建立者權限執行
CREATE PROCEDURE sensitive_op() SQL SECURITY DEFINER
BEGIN
    -- 可存取建立者有權限的表，即使調用者無權限
    DELETE FROM audit_log WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);
END;

-- 2. INVOKER - 以調用者權限執行
CREATE PROCEDURE user_report() SQL SECURITY INVOKER
BEGIN
    -- 僅能存取調用者有權限的表
    SELECT * FROM user_data WHERE owner = CURRENT_USER();
END;

-- 3. 查看/修改 DEFINER
SHOW CREATE PROCEDURE sensitive_op;
-- ALTER PROCEDURE sensitive_op SQL SECURITY INVOKER;  -- 8.0+ 支援修改

效能優化與最佳實踐

避免在循環中執行單條 SQL → 批次處理、臨時表、游標配合多行 INSERT
使用 DETERMINISTIC/NO SQL/READS SQL DATA 特性宣告 → 優化器可緩存結果、跳過不必要鎖
參數化動態 SQL → PREPARE 重用執行計畫、防注入
限制結果集大小 → 分頁、LIMIT、避免全表掃描回傳
適度使用事務 → 過大事務導致鎖爭用、Undo Log 膨脹、回滾耗時
定期 ANALYZE PROCEDURE (8.0+) → 更新統計資訊、重新優化執行計畫

除錯技巧

-- 1. 選擇性日誌 (資料表)
CREATE TABLE sp_debug_log (id INT AUTO_INCREMENT PRIMARY KEY, proc VARCHAR(64), msg TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
INSERT INTO sp_debug_log(proc, msg) VALUES ('sp_name', CONCAT('var=', v_var));

-- 2. SELECT ... INTO @var 觀察中間值
SELECT @debug_var := v_var;

-- 3. MySQL 8.0+ 追蹤
SET SESSION debug = '+d,sp_name';  -- 需編譯 WITH_DEBUG=ON
-- 或使用 Performance Schema
SELECT * FROM performance_schema.events_statements_history WHERE sql_text LIKE '%sp_name%';