存儲過程將業務邏輯下沉到資料庫層，減少網絡往返、利用服務端算力、統一權限控制。本文以「會員體系」為例，展示 MySQL 存儲過程創建、PHP PDO 調用、參數綁定、異常處理的完整最佳實踐。

1. 存儲過程定義規範

DELIMITER //

-- 通用異常處理模板
CREATE PROCEDURE `sp_handle_error`()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    GET DIAGNOSTICS CONDITION 1
      @sqlstate = RETURNED_SQLSTATE,
      @errno = MYSQL_ERRNO,
      @text = MESSAGE_TEXT;
    SELECT CONCAT('ERROR ', @errno, ' (', @sqlstate, '): ', @text) AS error_msg;
    ROLLBACK;
  END;
END//

-- 會員表
CREATE TABLE `members` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(50) NOT NULL UNIQUE,
  `password_hash` CHAR(60) NOT NULL,  -- bcrypt hash
  `email` VARCHAR(100) NOT NULL UNIQUE,
  `status` TINYINT NOT NULL DEFAULT 1 COMMENT '1=正常,0=禁用',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4//

-- 1. 註冊（輸入：username, password, email；輸出：new_id）
CREATE PROCEDURE `sp_member_register`(
  IN p_username VARCHAR(50),
  IN p_password VARCHAR(100),
  IN p_email VARCHAR(100),
  OUT p_new_id BIGINT UNSIGNED
)
BEGIN
  DECLARE v_hash CHAR(60);
  SET v_hash = SHA2(CONCAT(p_password, 'salt_'), 256); -- 生產請用 PHP password_hash()
  INSERT INTO `members` (`username`, `password_hash`, `email`)
  VALUES (p_username, v_hash, p_email);
  SET p_new_id = LAST_INSERT_ID();
END//

-- 2. 登錄驗證（輸入：username；輸出：password_hash, id, status）
CREATE PROCEDURE `sp_member_validate`(
  IN p_username VARCHAR(50),
  OUT p_id BIGINT UNSIGNED,
  OUT p_hash CHAR(60),
  OUT p_status TINYINT
)
BEGIN
  SELECT `id`, `password_hash`, `status`
  INTO p_id, p_hash, p_status
  FROM `members` WHERE `username` = p_username AND `status` = 1;
END//

-- 3. 修改密碼（輸入：username, old_password, new_password；輸出：affected_rows）
CREATE PROCEDURE `sp_member_change_pwd`(
  IN p_username VARCHAR(50),
  IN p_old_password VARCHAR(100),
  IN p_new_password VARCHAR(100),
  OUT p_affected INT
)
BEGIN
  DECLARE v_hash CHAR(60);
  SELECT `password_hash` INTO v_hash FROM `members` WHERE `username` = p_username;
  IF v_hash = SHA2(CONCAT(p_old_password, 'salt_'), 256) THEN
    UPDATE `members` SET `password_hash` = SHA2(CONCAT(p_new_password, 'salt_'), 256)
    WHERE `username` = p_username;
    SET p_affected = ROW_COUNT();
  ELSE
    SET p_affected = -1; -- 密碼錯誤
  END IF;
END//

-- 4. 刪除會員（軟刪除/硬刪除可選）
CREATE PROCEDURE `sp_member_delete`(
  IN p_username VARCHAR(50),
  IN p_hard_delete BOOLEAN,
  OUT p_affected INT
)
BEGIN
  IF p_hard_delete THEN
    DELETE FROM `members` WHERE `username` = p_username;
  ELSE
    UPDATE `members` SET `status` = 0 WHERE `username` = p_username;
  END IF;
  SET p_affected = ROW_COUNT();
END//

DELIMITER ;

2. PHP PDO 封裝調用類

<?php
class MemberRepository {
  private PDO $pdo;

  public function __construct(PDO $pdo) {
    $this->pdo = $pdo;
    $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  }

  // 註冊
  public function register(string $username, string $password, string $email): int {
    $stmt = $this->pdo->prepare("CALL sp_member_register(?, ?, ?, @new_id)");
    $stmt->bindParam(1, $username, PDO::PARAM_STR);
    $stmt->bindParam(2, $password, PDO::PARAM_STR);
    $stmt->bindParam(3, $email, PDO::PARAM_STR);
    $stmt->execute();
    $stmt->closeCursor();
    $row = $this->pdo->query("SELECT @new_id AS id")->fetch(PDO::FETCH_ASSOC);
    return (int)$row['id'];
  }

  // 登錄驗證
  public function validate(string $username, string $password): ?array {
    $stmt = $this->pdo->prepare("CALL sp_member_validate(?, @id, @hash, @status)");
    $stmt->bindParam(1, $username, PDO::PARAM_STR);
    $stmt->execute();
    $stmt->closeCursor();
    $row = $this->pdo->query("SELECT @id AS id, @hash AS hash, @status AS status")->fetch(PDO::FETCH_ASSOC);
    if (!$row || !$row['id']) return null;
    // PHP 端驗證密碼（生產用 password_verify）
    if (hash_equals($row['hash'], hash('sha256', $password . 'salt_'))) {
      return ['id' => (int)$row['id'], 'status' => (int)$row['status']];
    }
    return null;
  }

  // 修改密碼
  public function changePassword(string $username, string $oldPwd, string $newPwd): bool {
    $stmt = $this->pdo->prepare("CALL sp_member_change_pwd(?, ?, ?, @affected)");
    $stmt->bindParam(1, $username, PDO::PARAM_STR);
    $stmt->bindParam(2, $oldPwd, PDO::PARAM_STR);
    $stmt->bindParam(3, $newPwd, PDO::PARAM_STR);
    $stmt->execute();
    $stmt->closeCursor();
    $row = $this->pdo->query("SELECT @affected AS n")->fetch(PDO::FETCH_ASSOC);
    return (int)$row['n'] > 0;
  }

  // 刪除
  public function delete(string $username, bool $hard = false): bool {
    $stmt = $this->pdo->prepare("CALL sp_member_delete(?, ?, @affected)");
    $stmt->bindParam(1, $username, PDO::PARAM_STR);
    $stmt->bindValue(2, $hard, PDO::PARAM_BOOL);
    $stmt->execute();
    $stmt->closeCursor();
    $row = $this->pdo->query("SELECT @affected AS n")->fetch(PDO::FETCH_ASSOC);
    return (int)$row['n'] > 0;
  }
}

// 使用示例
$pdo = new PDO('mysql:host=127.0.0.1;dbname=app_db;charset=utf8mb4', 'app_user', 'AppPass#2024');
$repo = new MemberRepository($pdo);

$uid = $repo->register('john_doe', 'Secret123!', 'john@example.com');
echo "Registered ID: $uid\n";

$user = $repo->validate('john_doe', 'Secret123!');
var_dump($user);

$repo->changePassword('john_doe', 'Secret123!', 'NewSecret456!');
$repo->delete('john_doe');

3. 關鍵最佳實踐

1. 參數化綁定： 所有輸入用 ? 佔位符 + bindParam，徹底防注入。
2. OUT 參數獲取： MySQL 用戶變量 @var 接收 OUT，調用後再 SELECT @var 讀取。
3. 密碼處理： 存儲過程僅演示邏輯，**生產環境密碼哈希必須在 PHP 端用 password_hash()/password_verify()**，資料庫只存儲哈希值。
4. 事務邊界： 單一存儲過程內隱式事務；跨過程事務請在 PHP 端 $pdo->beginTransaction() 控制。
5. 權限最小化： 應用帳號僅擁有 EXECUTE 權限於特定存儲過程，無直接表操作權限。
6. 異常處理： PDO::ERRMODE_EXCEPTION + try-catch 統一捕獲，記錄日誌、返回友好錯誤碼。

4. 存儲過程優缺點理性評估

建議： 核心高頻、邏輯固化、極度敏感權限的操作（如資金轉賬、庫存扣減）用存儲過程；常規 CRUD、複雜業務流程留在應用層（Service Layer + ORM）。