MySQL 存儲過程 IN/OUT 參數實戰：從簡單加法到帶事務的序號生成器

基礎語法

CREATE PROCEDURE sp_name(IN param1 TYPE, OUT param2 TYPE, INOUT param3 TYPE)
BEGIN
    -- 邏輯
    SET param2 = ...;
END;

• IN：輸入，預設。
• OUT：輸出，需用變數接收 CALL sp(@out_var); SELECT @out_var;。
• INOUT：雙向。

案例一：簡單加法

DELIMITER $$
CREATE PROCEDURE sp_add(a INT, b INT, OUT c INT)
BEGIN
    SET c = a + b;
END$$
DELIMITER ;

CALL sp_add(1, 2, @c);
SELECT @c;  -- 3

案例二：帶事務的單據號生成器（防併發）

-- 表 t_BillNo(bmh VARCHAR(20) PK, SaleNo BIGINT)

DELIMITER $$
CREATE PROCEDURE sp_GetMaxNumber(IN v_bmh VARCHAR(6), OUT v_MaxNo INT)
BEGIN
    DECLARE exit handler for sqlexception
    BEGIN
        ROLLBACK;
        SET v_MaxNo = 0;
    END;

    START TRANSACTION;
    UPDATE t_BillNo SET SaleNo = IFNULL(SaleNo, 0) + 1 WHERE bmh = v_bmh;
    SELECT SaleNo INTO v_MaxNo FROM t_BillNo WHERE bmh = v_bmh;
    COMMIT;
END$$
DELIMITER ;

CALL sp_GetMaxNumber('8', @v_MaxNo);
SELECT @v_MaxNo;  -- 返回遞增後的單號

關鍵點解析

• DECLARE ... HANDLER：異常捕獲，自動回滾並設輸出 0。
• IFNULL(SaleNo,0)+1：初始化兼容。
• 事務包裹 UPDATE + SELECT，確保原子性、避免臟讀。
• 高併發下仍有間隙，極致效能可用 SELECT ... FOR UPDATE 或 Redis/數據庫序列替代。

調試與查看

SHOW CREATE PROCEDURE sp_GetMaxNumber;
SHOW PROCEDURE STATUS WHERE Db='test';
DROP PROCEDURE IF EXISTS sp_GetMaxNumber;