MySQL 儲存過程實戰教程：建立、參數、變數、流程控制與調用全流程

為什麼用儲存過程？

• 邏輯下沉：跨應用複用、減少網路往返
• 安全性：應用僅呼叫 SP，無直連表權限
• 效能：複雜邏輯在 Server 端完成、減少傳輸
• 事務邊界清晰：SP 內隱式/顯式事務

語法骨架

CREATE PROCEDURE sp_name ([IN|OUT|INOUT] param_name type, ...)
BEGIN
  DECLARE var_name type [DEFAULT value];
  -- 流程控制、SQL 陳述式
END;

• IN：輸入 (預設)、OUT：輸出、INOUT：雙向
• DELIMITER // 變更語句結束符，避免內部 ; 衝突

完整範例：用戶註冊儲存過程

DELIMITER //

CREATE PROCEDURE `sp_user_register` (
  IN p_username VARCHAR(50),
  IN p_password_hash CHAR(64),
  IN p_email VARCHAR(100),
  IN p_mobile VARCHAR(20),
  OUT p_user_id INT UNSIGNED,
  OUT p_result_code TINYINT,
  OUT p_result_msg VARCHAR(100)
)
BEGIN
  DECLARE v_exists INT DEFAULT 0;
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    SET p_result_code = -1;
    SET p_result_msg = '系統異常，請稍後重試';
  END;

  START TRANSACTION;

  -- 檢查用戶名/郵箱/手機唯一性
  SELECT COUNT(*) INTO v_exists FROM `users` WHERE `username`=p_username OR `email`=p_email OR `mobile`=p_mobile;
  IF v_exists > 0 THEN
    SET p_result_code = 1;
    SET p_result_msg = '用戶名/郵箱/手機已存在';
    ROLLBACK;
  ELSE
    INSERT INTO `users` (`username`,`password_hash`,`email`,`mobile`,`created_at`)
    VALUES (p_username, p_password_hash, p_email, p_mobile, NOW());
    SET p_user_id = LAST_INSERT_ID();
    SET p_result_code = 0;
    SET p_result_msg = '註冊成功';
    COMMIT;
  END IF;
END//

DELIMITER ;

調用與測試

CALL sp_user_register('alice', 'hash123', 'alice@ex.com', '13800000000', @uid, @code, @msg);
SELECT @uid, @code, @msg;

流程控制構造

管理指令

• 查看：SHOW PROCEDURE STATUS [LIKE 'pattern']; / SHOW CREATE PROCEDURE sp_name;
• 刪除：DROP PROCEDURE [IF EXISTS] sp_name;
• 權限：GRANT EXECUTE ON PROCEDURE db.sp_name TO 'user'@'host';

避坑指南

1. 避免過度業務邏輯下沉：核心業務規則留應用層，SP 僅做資料層聚合
2. 參數型別嚴格匹配：隱式轉換導致索引失效
3. 大事務拆分：SP 內長事務易鎖死，拆小批次
4. 版本相容：備份/遷移時 mysqldump --routines，目標版本支援語法

結語

儲存過程是資料庫端的「微服務」。輕量、單一職責、參數化、異常處理 四原則，讓 SP 成為效能與安全的加速器，而非維護負擔。