如何:多次執行查詢
本主題的範例會示範如何使用 SQL Server Driver for PHP 來多次執行備妥的陳述式。藉由使用 sqlsrv_prepare 和 sqlsrv_execute 的組合,就會在伺服器上編譯陳述式一次,然後使用不同的參數值來多次執行陳述式。
注意
如果是一次性查詢,建議您使用 sqlsrv_query。
當您準備的陳述式會使用變數當做參數時,這些變數會繫結至該陳述式。這表示,如果您更新變數的值,下次當您執行此陳述式時,將會使用更新的參數值來執行它。
範例
下列範例會示範如何準備陳述式,然後以不同的參數值重新執行它。此範例會更新 AdventureWorks 資料庫中 Sales.SalesOrderDetail 資料表的 OrderQty 資料行。在發生更新後,將會查詢資料庫來確認更新都已成功。此範例假設 SQL Server 和 AdventureWorks 資料庫已經安裝在本機電腦上。當從命令列執行此範例時,所有輸出都會寫入主控台。
<?php
/* Connect to the local server using Windows Authentication and
specify the AdventureWorks database as the database in use. */
$serverName = "(local)";
$connectionInfo = array( "Database"=>"AdventureWorks");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false )
{
echo "Could not connect.\n";
die( print_r( sqlsrv_errors(), true));
}
/* Define the parameterized query. */
$tsql = "UPDATE Sales.SalesOrderDetail
SET OrderQty = ?
WHERE SalesOrderDetailID = ?";
/* Initialize parameters and prepare the statement. Variables $qty
and $id are bound to the statement, $stmt1. */
$qty = 0; $id = 0;
$stmt1 = sqlsrv_prepare( $conn, $tsql, array( &$qty, &$id));
if( $stmt1 )
{
echo "Statement 1 prepared.\n";
}
else
{
echo "Error in statement preparation.\n";
die( print_r( sqlsrv_errors(), true));
}
/* Set up the SalesOrderDetailID and OrderQty information. This array
maps the order ID to order quantity in key=>value pairs. */
$orders = array( 1=>10, 2=>20, 3=>30);
/* Execute the statement for each order. */
foreach( $orders as $id => $qty)
{
// Because $id and $qty are bound to $stmt1, their updated
// values are used with each execution of the statement.
if( sqlsrv_execute( $stmt1) === false )
{
echo "Error in statement execution.\n";
die( print_r( sqlsrv_errors(), true));
}
}
echo "Orders updated.\n";
/* Free $stmt1 resources. This allows $id and $qty to be bound to a different statement.*/
sqlsrv_free_stmt( $stmt1);
/* Now verify that the results were successfully written by selecting
the newly inserted rows. */
$tsql = "SELECT OrderQty
FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID = ?";
/* Prepare the statement. Variable $id is bound to $stmt2. */
$stmt2 = sqlsrv_prepare( $conn, $tsql, array( &$id));
if( $stmt2 )
{
echo "Statement 2 prepared.\n";
}
else
{
echo "Error in statement preparation.\n";
die( print_r( sqlsrv_errors(), true));
}
/* Execute the statement for each order. */
foreach( array_keys($orders) as $id)
{
/* Because $id is bound to $stmt2, its updated value
is used with each execution of the statement. */
if( sqlsrv_execute( $stmt2))
{
sqlsrv_fetch( $stmt2);
$quantity = sqlsrv_get_field( $stmt2, 0);
echo "Order $id is for $quantity units.\n";
}
else
{
echo "Error in statement execution.\n";
die( print_r( sqlsrv_errors(), true));
}
}
/* Free $stmt2 and connection resources. */
sqlsrv_free_stmt( $stmt2);
sqlsrv_close( $conn);
?>
如需寫入及讀取大量資訊的替代策略,請參閱<SQL 陳述式的批次>(英文) 和<BULK INSERT>。
另請參閱
工作
其他資源
設計考量
擷取資料
更新資料 (SQL Server Driver for PHP)
API 參考 (SQL Server Driver for PHP)