您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 存儲過程開發完全指南：從基礎語法到 Java/MyBatis 調用實戰

編輯：MySQL 綜合教學

MySQL 存儲過程開發完全指南：從基礎語法到 Java/MyBatis 調用實戰

存儲過程將業務邏輯下沉至資料庫層，減少網絡交互、封裝複雜事務、提升批量處理效能。本文系統梳理 MySQL 存儲過程語法精髓，涵蓋參數模式、流程控制、循環、動態 SQL，並詳細演示 JDBC 與 MyBatis 兩種主流調用姿勢，助你快速掌握企業級開發技能。

一、存儲過程核心語法

1. 基礎結構與參數模式

DELIMITER $$  -- 客戶端必須修改分隔符，Navicat 等 GUI 工具可忽略

CREATE PROCEDURE proc_name (
    IN  param_in   INT,        -- 輸入參數
    OUT param_out  VARCHAR(30), -- 輸出參數
    INOUT param_io INT          -- 輸入輸出參數
)
BEGIN
    -- 聲明局部變量
    DECLARE var_name INT DEFAULT 0;
    
    -- 業務邏輯
    SELECT COUNT(*) INTO var_name FROM some_table;
    SET param_out = CONCAT('Result: ', var_name);
END $$

DELIMITER ;

2. 條件控制：IF-THEN-ELSE / CASE WHEN

-- IF 結構（注意 THEN 與 END IF）
IF a IS NULL THEN SET a = 0; END IF;
IF b IS NULL THEN SET b = 0; END IF;

-- CASE WHEN 多分支
CASE expression
    WHEN value1 THEN statement_list;
    WHEN value2 THEN statement_list;
    ELSE statement_list;
END CASE;

3. 循環構造：WHILE / LOOP / REPEAT

-- WHILE 循環
WHILE i < 5 DO
    INSERT INTO student(name) VALUES (CONCAT('user_', i));
    SET i = i + 1;
END WHILE;

-- LOOP 循環（配合 LEAVE/ITERATE）
loop_label: LOOP
    IF i = 3 THEN SET i = i + 1; ITERATE loop_label; END IF; -- 相當於 continue
    INSERT INTO student VALUES (NULL, i, i, NOW());
    SET i = i + 1;
    IF i >= 5 THEN LEAVE loop_label; END IF; -- 相當於 break
END LOOP;

4. 動態 SQL 與模糊查詢

CREATE PROCEDURE search_student(IN keyword VARCHAR(30))
BEGIN
    -- CONCAT 拼接通配符，防止注入建議使用 PREPARE/EXECUTE
    SELECT * FROM student WHERE name LIKE CONCAT('%', keyword, '%');
END;

二、 Java 應用調用實戰

1. JDBC CallableStatement 標準調用

public int callAddProc(int a, int b) throws SQLException {
    String sql = "{call add_proc(?, ?, ?)}"; // {call 存儲過程名(?, ?, ?)}
    try (Connection conn = dataSource.getConnection();
         CallableStatement stmt = conn.prepareCall(sql)) {
        
        stmt.setInt(1, a);           // IN 參數
        stmt.setInt(2, b);           // IN 參數
        stmt.registerOutParameter(3, Types.INTEGER); // 註冊 OUT 參數類型
        
        stmt.execute();
        return stmt.getInt(3);       // 獲取 OUT 參數值
    }
}

2. MyBatis Mapper XML 調用

<!-- Mapper 接口 -->
public Map<String, Object> callAddProc(Map<String, Object> params);

<!-- XML 映射 -->
<select id="callAddProc" parameterType="map" statementType="CALLABLE">
    {call add_proc(
        #{firstParam, jdbcType=INTEGER, mode=IN},
        #{secondParam, jdbcType=INTEGER, mode=IN},
        #{outParam, jdbcType=INTEGER, mode=OUT}
    )}
</select>

3. MyBatis 關鍵細節

statementType="CALLABLE"：必須指定，告訴 MyBatis 使用 CallableStatement
mode=IN/OUT/INOUT：精確聲明參數方向
輸出參數自動回填： 調用後 params Map 會自動包含 outParam 鍵值，無需預先放入

// 測試代碼
Map<String, Object> params = new HashMap<>();
params.put("firstParam", 1);
params.put("secondParam", 2);
mapper.callAddProc(params);
System.out.println(params); // {secondParam=2, firstParam=1, outParam=3}

三、進階技巧與避坑指南

場景	建議
複雜業務邏輯	優先考慮 Service 層組合，存儲過程僅用於極高頻、強事務、大批量數據處理
動態 SQL 構建	使用 `PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;` 防注入
異常處理	聲明 `DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN ... END;` 捕獲錯誤
游標處理	`DECLARE cur CURSOR FOR SELECT ...; OPEN cur; FETCH cur INTO ...; CLOSE cur;`
版本管理	存儲過程代碼納入 Git，通過 Flyway/Liquibase 遷移腳本管理
效能調優	避免在循環中執行單條 SQL，改用批量操作或臨時表

四、總結

MySQL 存儲過程是「雙刃劍」：用對場景（核心交易扣款、報表聚合、數據遷移）能極大提升效能與一致性；濫用會導致業務邏輯分散、難以測試、版本控制弱、資料庫耦合度高。建議遵循 「能在應用層解決不下沉、必須下沉時精準下沉、下沉後嚴格治理」 的原則，配合 JDBC/MyBatis 標準化調用模板，構建可維護的企業級數據訪問層。