MySQL 5.0+ 引入預存程序,PHP 透過 MySQLi 擴展可完整支援。本文說明如何正確呼叫預存程序、處理回傳結果、解決多重呼叫問題與最佳實務。
已棄用/移除: `mysql_query()` 僅支援無回傳結果的預存程序。若程序有輸出參數或結果集,會拋出錯誤:
<?php
// ❌ 舊版 mysql 擴展 - 有回傳值會報錯
$rs = mysql_query("CALL func('param')", $conn);
// 錯誤:PROCEDURE func can't return a result set in the given context
?>
<?php
$mysqli = new mysqli('localhost', 'user', 'password', 'database');
if ($mysqli->connect_errno) {
die("連線失敗: " . $mysqli->connect_error);
}
// 呼叫預存程序
$procName = 'get_user_info';
$param = 'john_doe';
if ($result = $mysqli->query("CALL {$procName}(?)", [$param])) {
// 處理結果集
while ($row = $result->fetch_assoc()) {
print_r($row);
}
$result->free();
} else {
echo "呼叫失敗: " . $mysqli->error;
}
// 關閉連線
$mysqli->close();
?>
-- MySQL 預存程序範例
DELIMITER //
CREATE PROCEDURE get_user_info(IN p_username VARCHAR(50))
BEGIN
SELECT id, username, email, created_at
FROM users
WHERE username = p_username;
END //
DELIMITER ;
<?php
$mysqli = new mysqli('localhost', 'user', 'pass', 'db');
// 預存程序有 OUT 參數
$stmt = $mysqli->prepare("CALL calculate_total(?, ?, @total)");
$orderId = 123;
$taxRate = 0.05;
$stmt->bind_param('id', $orderId, $taxRate);
$stmt->execute();
$stmt->close();
// 取得 OUT 參數值
$result = $mysqli->query("SELECT @total AS total_amount");
$row = $result->fetch_assoc();
echo "總金額: {$row['total_amount']}";
$result->free();
?>
同一連線連續呼叫多個預存程序會出錯:
<?php
// ❌ 會失敗:Commands out of sync; you can't run this command now
$mysqli->query("CALL proc1()");
$mysqli->query("CALL proc2()"); // 錯誤!
?>
<?php
// 正確處理多重呼叫:使用 multi_query 或逐一處理結果集
$mysqli->multi_query("CALL proc1(); CALL proc2();");
do {
if ($result = $mysqli->store_result()) {
// 處理第一個結果集
while ($row = $result->fetch_assoc()) {
// ...
}
$result->free();
}
} while ($mysqli->more_results() && $mysqli->next_result());
?>
<?php
function callProc(mysqli $mysqli, string $procSql): ?mysqli_result {
$result = $mysqli->query($procSql);
if ($mysqli->errno) {
// 若發生 out of sync,重新連線重試一次
if ($mysqli->errno === 2014) { // CR_COMMANDS_OUT_OF_SYNC
$mysqli->close();
$mysqli = new mysqli('localhost', 'user', 'pass', 'db');
return $mysqli->query($procSql);
}
return null;
}
return $result;
}
// 使用
$result1 = callProc($mysqli, "CALL proc1()");
$result2 = callProc($mysqli, "CALL proc2()");
?>
<?php
$pdo = new PDO('mysql:host=localhost;dbname=test', 'user', 'pass');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// PDO 可直接處理多重呼叫
$stmt1 = $pdo->query("CALL proc1()");
$data1 = $stmt1->fetchAll(PDO::FETCH_ASSOC);
$stmt1->closeCursor();
$stmt2 = $pdo->query("CALL proc2()");
$data2 = $stmt2->fetchAll(PDO::FETCH_ASSOC);
$stmt2->closeCursor();
?>
<?php
class StoredProcedureCaller {
private mysqli $mysqli;
public function __construct(mysqli $mysqli) {
$this->mysqli = $mysqli;
}
/**
* 呼叫預存程序並回傳所有結果集
* @return array> 多個結果集的陣列
*/
public function call(string $procName, array $params = []): array {
$placeholders = implode(',', array_fill(0, count($params), '?'));
$sql = "CALL {$procName}({$placeholders})";
$stmt = $this->mysqli->prepare($sql);
if (!$stmt) {
throw new RuntimeException("Prepare 失敗: {$this->mysqli->error}");
}
if ($params) {
$types = str_repeat('s', count($params)); // 簡化:全視為字串
$stmt->bind_param($types, ...$params);
}
$stmt->execute();
$results = [];
do {
if ($result = $stmt->get_result()) {
$results[] = $result->fetch_all(MYSQLI_ASSOC);
$result->free();
}
} while ($this->mysqli->more_results() && $this->mysqli->next_result());
$stmt->close();
return $results;
}
}
// 使用
$caller = new StoredProcedureCaller($mysqli);
$results = $caller->call('get_user_stats', ['john_doe']);
print_r($results[0]); // 第一個結果集
?>