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

MySQL 預存程序入門到進階：創建、調用、參數、流程控制、事務與最佳實踐

編輯：MySQL 資料庫相關知識

基礎語法速覽


-- 1. 無參預存程序
CREATE PROCEDURE sp_refresh_stats()
BEGIN
    ANALYZE TABLE orders, users, products;
END;

-- 2. 帶參數 (IN/OUT/INOUT)
CREATE PROCEDURE sp_get_order_stats(
    IN p_user_id BIGINT,
    OUT p_total_orders INT,
    OUT p_total_amount DECIMAL(12,2),
    INOUT p_currency VARCHAR(3)  -- 入參同時可被修改返回
)
BEGIN
    SELECT COUNT(*), COALESCE(SUM(amount),0)
    INTO p_total_orders, p_total_amount
    FROM orders WHERE user_id = p_user_id;

    IF p_currency IS NULL THEN
        SET p_currency = 'CNY';
    END IF;
END;

-- 3. 調用
CALL sp_get_order_stats(1001, @orders, @amount, @curr);
SELECT @orders, @amount, @curr;

流程控制構造

構造	語法範例	適用場景
IF-THEN-ELSE	IF condition THEN ... ELSEIF ... ELSE ... END IF;	分支邏輯
CASE	CASE WHEN cond1 THEN ... WHEN cond2 THEN ... ELSE ... END CASE;	多條件分支
WHILE	WHILE condition DO ... END WHILE;	已知次數/條件循環
REPEAT-UNTIL	REPEAT ... UNTIL condition END REPEAT;	至少執行一次
LOOP + LEAVE	label: LOOP ... IF cond THEN LEAVE label; END IF; END LOOP;	複雜退出邏輯
ITERATE	ITERATE label;	跳過本次循環繼續下一次

區塊與標籤 (命名空間隔離)


outer_block: BEGIN
    DECLARE v_cnt INT DEFAULT 0;
    inner_block: BEGIN
        DECLARE v_cnt INT DEFAULT 100;  -- 同名變量，內層遮蔽外層
        SELECT v_cnt;  -- 100
    END inner_block;
    SELECT v_cnt;  -- 0
END outer_block;

游標處理結果集 (逐行邏輯)


CREATE PROCEDURE sp_process_pending_orders()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_order_id BIGINT;
    DECLARE v_amount DECIMAL(12,2);
    DECLARE cur CURSOR FOR SELECT id, amount FROM orders WHERE status='pending';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_order_id, v_amount;
        IF done THEN LEAVE read_loop; END IF;
        -- 業務邏輯：風控檢查、扣減庫存、發送通知...
        UPDATE orders SET status='processing' WHERE id = v_order_id;
    END LOOP;
    CLOSE cur;
END;

事務與錯誤處理


CREATE PROCEDURE sp_transfer(IN from_id BIGINT, IN to_id BIGINT, IN amt DECIMAL(12,2))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;  -- 重新拋出錯誤給調用者
    END;

    START TRANSACTION;
    UPDATE accounts SET balance = balance - amt WHERE id = from_id AND balance >= amt;
    IF ROW_COUNT() = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient balance';
    END IF;
    UPDATE accounts SET balance = balance + amt WHERE id = to_id;
    COMMIT;
END;

管理與元數據查詢


-- 列出所有預存程序
SHOW PROCEDURE STATUS [WHERE Db='my_db'];

-- 查看具體定義
SHOW CREATE PROCEDURE sp_get_order_stats;

-- 信息庫查詢 (可編寫報表)
SELECT ROUTINE_NAME, ROUTINE_TYPE, DEFINER, CREATED, LAST_ALTERED
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'my_db' AND ROUTINE_TYPE = 'PROCEDURE';

最佳實踐與避坑指南

業務邏輯下沉原則：僅將 **高頻、多表、需事務原子性、計算密集** 的邏輯下沉；單表 CRUD 留在應用層 (ORM 更靈活)。
參數校驗前置**：入口即驗證 IF p_id <= 0 THEN SIGNAL ...，避免髒數據進入事務。
避免動態 SQL**：PREPARE/EXECUTE 雖強大但難調試、易注入、優化器難分析。極少數場景 (分表路由) 才用。
版本控制**：預存程序 DDL 納入 Flyway/Liquibase 遷移腳本，而非手工執行。
權限最小化**：調用者僅需 EXECUTE ON PROCEDURE ...，無需表級權限，實現數據訪問封裝。
除錯技巧**：MySQL 無原生調試器，用 INSERT INTO debug_log(msg) VALUES(...) 記錄關鍵變量，或引入 sys.schema_table_statistics 監控調用性能。