DELIMITER //
-- 無參查詢
CREATE PROCEDURE sp_get_active_users()
BEGIN
SELECT id, username, email, created_at
FROM users
WHERE status = 1
ORDER BY created_at DESC;
END //
-- IN 參數
CREATE PROCEDURE sp_get_user_by_id(IN p_user_id INT)
BEGIN
SELECT * FROM users WHERE id = p_user_id;
END //
-- OUT 參數
CREATE PROCEDURE sp_count_users_by_status(IN p_status TINYINT, OUT p_total INT)
BEGIN
SELECT COUNT(*) INTO p_total FROM users WHERE status = p_status;
END //
-- INOUT 參數
CREATE PROCEDURE sp_increment_counter(INOUT p_counter INT, IN p_step INT)
BEGIN
SET p_counter = p_counter + p_step;
END //
-- 多結果集
CREATE PROCEDURE sp_user_with_orders(IN p_user_id INT)
BEGIN
SELECT * FROM users WHERE id = p_user_id;
SELECT * FROM orders WHERE user_id = p_user_id ORDER BY created_at DESC;
END //
-- 事務內操作
CREATE PROCEDURE sp_transfer_balance(
IN p_from_id INT,
IN p_to_id INT,
IN p_amount DECIMAL(10,2),
OUT p_success BOOL,
OUT p_message VARCHAR(255)
)
BEGIN
DECLARE v_from_balance DECIMAL(10,2);
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SET p_success = FALSE;
SET p_message = '轉賬失敗:數據庫異常';
END;
START TRANSACTION;
SELECT balance INTO v_from_balance FROM accounts WHERE user_id = p_from_id FOR UPDATE;
IF v_from_balance < p_amount THEN
SET p_success = FALSE;
SET p_message = '餘額不足';
ROLLBACK;
ELSE
UPDATE accounts SET balance = balance - p_amount WHERE user_id = p_from_id;
UPDATE accounts SET balance = balance + p_amount WHERE user_id = p_to_id;
INSERT INTO transfers (from_id, to_id, amount, created_at) VALUES (p_from_id, p_to_id, p_amount, NOW());
SET p_success = TRUE;
SET p_message = '轉賬成功';
COMMIT;
END IF;
END //
DELIMITER ;
<?php
declare(strict_types=1);
namespace App\Database;
use mysqli;
use mysqli_result;
use Exception;
class StoredProcedureExecutor
{
private mysqli $mysqli;
public function __construct(mysqli $mysqli)
{
$this->mysqli = $mysqli;
$this->mysqli->set_charset('utf8mb4');
}
/**
* 執行存儲過程,自動處理多結果集
* @return array [結果集1, 結果集2, ...] 每個結果集為關聯數組數組
*/
public function execute(string $procName, array $params = []): array
{
// 構建 CALL 語句
$placeholders = str_repeat('?,', count($params));
$placeholders = rtrim($placeholders, ',');
$sql = "CALL `$procName`($placeholders)";
$stmt = $this->mysqli->prepare($sql);
if (!$stmt) {
throw new Exception("Prepare 失敗: " . $this->mysqli->error);
}
// 綁定參數
if ($params) {
$types = $this->getTypesString($params);
$stmt->bind_param($types, ...$params);
}
if (!$stmt->execute()) {
throw new Exception("執行失敗 [$procName]: " . $stmt->error);
}
// 處理多結果集
$results = [];
do {
$result = $stmt->get_result();
if ($result) {
$rows = [];
while ($row = $result->fetch_assoc()) {
$rows[] = $row;
}
$results[] = $rows;
$result->free();
}
} while ($this->mysqli->more_results() && $stmt->next_result());
$stmt->close();
return $results;
}
/**
* 執行帶 OUT/INOUT 參數的存儲過程
* @return array ['results' => [...], 'out_params' => [...]]
*/
public function executeWithOutParams(string $procName, array &$params): array
{
// 此處需用戶自行管理 OUT 參數變量引用
// 建議:OUT 參數用單獨查詢 SELECT @out_param 獲取
// 或使用 PDO (支援 bindParam by reference)
throw new Exception("建議使用 PDO 處理 OUT 參數,見下方示例");
}
private function getTypesString(array $params): string
{
$types = '';
foreach ($params as $param) {
if (is_int($param)) $types .= 'i';
elseif (is_float($param)) $types .= 'd';
elseif (is_string($param)) $types .= 's';
elseif (is_bool($param)) $types .= 'i';
elseif (is_null($param)) $types .= 's'; // NULL 當字串處理
else $types .= 's';
}
return $types;
}
}
<?php
declare(strict_types=1);
namespace App\Database;
use PDO;
use PDOStatement;
use Exception;
class PdoStoredProcedure
{
private PDO $pdo;
public function __construct(PDO $pdo)
{
$this->pdo = $pdo;
}
public function call(string $procName, array $params = []): array
{
$placeholders = str_repeat('?,', count($params));
$placeholders = rtrim($placeholders, ',');
$sql = "CALL `$procName`($placeholders)";
$stmt = $this->pdo->prepare($sql);
foreach ($params as $i => $param) {
$stmt->bindValue($i + 1, $param, $this->getPdoType($param));
}
$stmt->execute();
$results = [];
do {
if ($stmt->columnCount() > 0) {
$results[] = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
} while ($stmt->nextRowset());
return $results;
}
/**
* 處理 OUT/INOUT 參數
* $params 中 OUT 參數需預先定義變量引用
*/
public function callWithOutParams(string $procName, array &$params): array
{
$placeholders = [];
$bindParams = [];
foreach ($params as $name => &$value) {
$placeholders[] = '?';
$bindParams[$name] = &$value; // 保持引用
}
$sql = "CALL `$procName`(" . implode(',', $placeholders) . ")";
$stmt = $this->pdo->prepare($sql);
foreach ($bindParams as $i => &$value) {
$stmt->bindParam($i + 1, $value, $this->getPdoType($value), 64); // 64 = max length for OUT
}
$stmt->execute();
$results = [];
do {
if ($stmt->columnCount() > 0) {
$results[] = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
} while ($stmt->nextRowset());
return $results;
}
private function getPdoType($value): int
{
if (is_int($value)) return PDO::PARAM_INT;
if (is_bool($value)) return PDO::PARAM_BOOL;
if (is_null($value)) return PDO::PARAM_NULL;
return PDO::PARAM_STR;
}
}
// 使用示例
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', 'user', 'pass', [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$executor = new PdoStoredProcedure($pdo);
// IN 參數
$users = $executor->call('sp_get_user_by_id', [123]);
print_r($users[0] ?? []);
// OUT 參數
$status = 1;
$total = 0; // 將被修改
$executor->callWithOutParams('sp_count_users_by_status', [
'p_status' => $status,
'p_total' => &$total, // 傳引用
]);
echo "總數: $total\n";
// INOUT 參數
$counter = 5;
$executor->callWithOutParams('sp_increment_counter', [
'p_counter' => &$counter,
'p_step' => 3,
]);
echo "計數器: $counter\n"; // 8
⚠️ 存儲過程內部事務與 PHP 端事務不要混用:
- 方案 A:業務邏輯全在存儲過程,PHP 只調用 CALL,不開啟事務
- 方案 B:業務邏輯在 PHP,存儲過程只做單表原子操作,PHP 控制事務
- 方案 C:PHP 開啟事務 → 調用多個存儲過程 → 提交/回滾 (需存儲過程不自行 COMMIT/ROLLBACK)
FOR UPDATE 鎖行、適當索引SHOW PROCESSLIST 發現長時間運行的存儲過程| 錯誤 | 原因 | 解決 |
|---|---|---|
| Commands out of sync | 上一個結果集未完全讀取/釋放 | 循環 next_result() 直到 false,或用 mysqli::multi_query |
| OUT 參數為 NULL | MySQLi 不直接支援 OUT 參數綁定 | 改用 PDO bindParam 引用、或額外 SELECT @out_var |
| PROCEDURE 不存在 | 數據庫、Schema、大小寫不匹配 | 檢查 SHOW PROCEDURE STATUS WHERE Db='dbname' |
| 權限拒絕 | 用戶無 EXECUTE 權限 | GRANT EXECUTE ON PROCEDURE db.sp_name TO 'user'@'host' |