MySQL 存儲過程入門到精通：創建、參數模式、調用、刪除與依賴管理全解析

什麼是存儲過程？

將一組 SQL 語句封裝為服務端可複用的程序單元，通過 CALL 調用。優點：減少網路往返、邏輯集中維護、權限控制細粒度。

快速上手：批量插入 10 萬條測試數據

CREATE TABLE proced (
    id INT(5) PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    type VARCHAR(50)
);

DELIMITER //
CREATE PROCEDURE adddata()
BEGIN
    DECLARE n INT DEFAULT 0;
    WHILE n < 100000 DO
        INSERT INTO proced(name, type) VALUES('跡憶博客', 'onmpw');
        SET n = n + 1;
    END WHILE;
END //
DELIMITER ;

CALL adddata();

核心語法詳解

1. DELIMITER 重定義結束符

存儲過程體內含多條 ;，若不改結束符，客戶端會誤判語句結束。

DELIMITER //  -- 改為 //
... 過程體 ...
END //
DELIMITER ;   -- 恢復 ;

2. 參數模式：IN / OUT / INOUT

IN 範例

CREATE PROCEDURE in_proced(IN param VARCHAR(100))
BEGIN
    INSERT INTO proced(name, type) VALUES(param, 'onmpw');
END;
CALL in_proced('onmpw.com');

OUT 範例

CREATE PROCEDURE out_proced(OUT param INT)
BEGIN
    SELECT COUNT(*) INTO param FROM proced;
END;
CALL out_proced(@a);
SELECT @a;  -- 返回行數

INOUT 範例

CREATE PROCEDURE inout_proced(INOUT param INT)
BEGIN
    SELECT COUNT(*) INTO param FROM proced WHERE id > param;
END;
SET @a = 3;
CALL inout_proced(@a);
SELECT @a;  -- 變為 id>3 的行數

存儲過程生命週期管理

刪除

DROP PROCEDURE IF EXISTS proc_name;

修改（僅屬性，不可改邏輯）

ALTER PROCEDURE proc_name
    [COMMENT '...']
    [LANGUAGE SQL]
    [{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}]
    [SQL SECURITY {DEFINER | INVOKER}];

依賴管理鐵律

被其他過程引用的存儲過程，不可直接刪除或修改簽名！
必須先解除依賴關係（修改調用方），否則運行時拋出 ERROR 1305 (42000): PROCEDURE ... does not exist

反面教材

CREATE PROCEDURE A(IN p1 INT, OUT p2 INT) ...;
CREATE PROCEDURE B(INOUT p INT)
BEGIN
    DECLARE v INT;
    CALL A(p, v);
    SET p = v;
END;

DROP PROCEDURE A;  -- 刪除被 B 依賴的 A
CALL B(@x);        -- 爆錯：PROCEDURE test.A does not exist

最佳實踐建議

• 參數名加前綴（p_、v_）區分變量/欄位
• 複雜邏輯拆分為多個小過程，單一職責
• 事務控制在應用層，存儲過程避免跨事務調用
• 版本控管：SHOW CREATE PROCEDURE 導出存檔
• 監控執行時間：performance_schema.events_statements_summary_by_digest