MySQL 存儲過程深度解析：從入門到進階實戰

MySQL 存儲過程深度解析：從入門到進階實戰

存儲過程是預編譯 SQL 語句集，存儲於數據庫服務端，通過名稱調用。相較於客戶端發送單條 SQL，它能封裝業務邏輯、減少網絡交互、提升執行效率，是數據庫端編程的核心技能。

一、核心優勢

1. 增強 SQL 表達力：支持流程控制，完成複雜判斷與運算。
2. 模塊化復用：創建一次，多處調用；修改無需動應用代碼。
3. 執行速度快：首次運行生成執行計劃緩存，避免重複編譯優化。
4. 降低網絡流量：只傳遞調用語句，而非大量 SQL 文本。
5. 安全控制：通過授權 EXECUTE 權限，間接實現數據訪問控制。

二、創建語法與參數模式

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

• IN：輸入參數，調用時必須提供，內部修改不返回。
• OUT：輸出參數，內部賦值並返回調用者。
• INOUT：雙向參數，調用時傳入，內部可修改並返回。

三、實戰範例

1. IN 參數演示

DELIMITER $$
CREATE PROCEDURE proc_in(IN p_in INT)
BEGIN
    SELECT p_in;      -- 輸出 1
    SET p_in = 2;
    SELECT p_in;      -- 輸出 2
END$$
DELIMITER ;

SET @p_in = 1;
CALL proc_in(@p_in);
SELECT @p_in;         -- 仍為 1，原變量未受影響

2. OUT 參數演示

DELIMITER $$
CREATE PROCEDURE proc_out(OUT p_out INT)
BEGIN
    SELECT p_out;     -- 輸出 NULL
    SET p_out = 2;
    SELECT p_out;     -- 輸出 2
END$$
DELIMITER ;

SET @p_out = 1;
CALL proc_out(@p_out);
SELECT @p_out;        -- 變為 2，外部變量被修改

3. INOUT 參數

結合上述特性，調用前賦值，調用後獲取新值，適用於累加、狀態流轉等場景。

四、流程控制語句

1. 條件分支

-- IF-THEN-ELSE
IF condition THEN ... ELSEIF ... ELSE ... END IF;

-- CASE 語句
CASE var
    WHEN 0 THEN INSERT INTO t VALUES(17);
    WHEN 1 THEN INSERT INTO t VALUES(18);
    ELSE INSERT INTO t VALUES(19);
END CASE;

2. 循環結構

• WHILE：先判斷後執行，適合已知次數或條件循環。
• REPEAT：先執行後判斷，至少執行一次。
• LOOP：無前置條件，配合 LEAVE label 跳出。
• ITERATE：跳過本次循環剩餘邏輯，直接進入下一輪（類似 continue）。

-- WHILE 示例
WHILE v < 5 DO
    INSERT INTO t VALUES(v);
    SET v = v + 1;
END WHILE;

-- REPEAT 示例
REPEAT
    INSERT INTO t VALUES(v);
    SET v = v + 1;
UNTIL v >= 5
END REPEAT;

-- LOOP + LEAVE + ITERATE
loop_label: LOOP
    IF v = 3 THEN
        SET v = v + 1;
        ITERATE loop_label;  -- 跳過 insert
    END IF;
    INSERT INTO t VALUES(v);
    SET v = v + 1;
    IF v >= 5 THEN LEAVE loop_label; END IF;
END LOOP;

五、管理操作

六、最佳實踐建議

• 使用 DECLARE 定義局部變量，避免污染會話變量（@var）。
• 業務邏輯過於複雜時，考慮拆分為多個過程或移至應用層。
• 避免在高併發核心鏈路中大量使用，防止數據庫 CPU 瓶頸。
• 定期 ANALYZE PROCEDURE 更新統計信息（MySQL 8.0+ 支持）。