分享方式:


使用資料表值參數 (PHP)

下載 PHP 驅動程式

適用於

  • 適用於 SQL Server 的 Microsoft Drivers 5.10.0 for PHP

簡介

您可以使用資料表值參數,將多個資料列傳送到 Transact-SQL 陳述式或預存程序。 您不需要建立暫存資料表。 如此頁面的範例所示,若要搭配 PHP 驅動程式使用資料表值參數,請宣告具有名稱的使用者定義資料表類型。

透過預存程序使用資料表值參數

下列範例假設下列資料表、資料表類型和預存程序存在:

CREATE TABLE TVPOrd(
    OrdNo INTEGER IDENTITY(1,1),
    OrdDate DATETIME,
    CustID VARCHAR(10))


CREATE TABLE TVPItem(
    OrdNo INTEGER,
    ItemNo INTEGER IDENTITY(1,1),
    ProductCode CHAR(10),
    OrderQty INTEGER,
    SalesDate DATE,
    Label NVARCHAR(30),
    Price DECIMAL(5,2),
    Photo VARBINARY(MAX))


--Create TABLE type for use as a TVP
CREATE TYPE TVPParam AS TABLE(
                ProductCode CHAR(10),
                OrderQty INTEGER,
                SalesDate DATE,
                Label NVARCHAR(30),
                Price DECIMAL(5,2),
                Photo VARBINARY(MAX))


--Create procedure with TVP parameters
CREATE PROCEDURE TVPOrderEntry(
        @CustID VARCHAR(10),
        @Items TVPParam READONLY,
        @OrdNo INTEGER OUTPUT,
        @OrdDate DATETIME OUTPUT)
AS
BEGIN
    SET @OrdDate = GETDATE(); SET NOCOUNT ON;
    INSERT INTO TVPOrd (OrdDate, CustID) VALUES (@OrdDate, @CustID);
    SELECT @OrdNo = SCOPE_IDENTITY();
    INSERT INTO TVPItem (OrdNo, ProductCode, OrderQty, SalesDate, Label, Price, Photo)
    SELECT @OrdNo, ProductCode, OrderQty, SalesDate, Label, Price, Photo
    FROM @Items
END

PHP 驅動程式會針對資料表值參數 (TVP) 使用資料列繫結,而且您必須提供類型名稱作為非空白字串。 在這個範例中,名稱為 TVPParam。 TVP 輸入基本上是索引鍵/值組,其中 TVP 類型名稱作為索引鍵,而輸入資料則作為巢狀陣列。 例如:

$image1 = fopen($pic1, 'rb');
$image2 = fopen($pic2, 'rb');
$image3 = fopen($pic3, 'rb');

$items = [
    ['0062836700', 367, "2009-03-12", 'AWC Tee Male Shirt', '20.75', $image1],
    ['1250153272', 256, "2017-11-07", 'Superlight Black Bicycle', '998.45', $image2],
    ['1328781505', 260, "2010-03-03", 'Silver Chain for Bikes', '88.98', $image3],
];

// Create a TVP input array
$tvpType = 'TVPParam';
$tvpInput = array($tvpType => $items);

// To execute the stored procedure, either execute a direct query or prepare this query:
$callTVPOrderEntry = "{call TVPOrderEntry(?, ?, ?, ?)}";

使用 SQLSRV 驅動程式

您可以使用 sqlsrv_execute 呼叫 sqlsrv_querysqlsrv_prepare。 下列範例顯示先前的使用案例:

$custCode = 'SRV_123';
$ordNo = 0;
$ordDate = null;
$params = array($custCode,
                array($tvpInput, SQLSRV_PARAM_IN, SQLSRV_PHPTYPE_TABLE, SQLSRV_SQLTYPE_TABLE), // or simply array($tvpInput),
                array(&$ordNo, SQLSRV_PARAM_OUT),
                array(&$ordDate, SQLSRV_PARAM_OUT, SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_CHAR)));
$stmt = sqlsrv_query($conn, $callTVPOrderEntry, $params);
if (!$stmt) {
    print_r(sqlsrv_errors());
}
sqlsrv_next_result($stmt);

此外,您可以使用 sqlsrv_send_stream_data 來傳送 TVP 執行後資料。 例如:

$options = array("SendStreamParamsAtExec" => 0);
$stmt = sqlsrv_prepare($conn, $callTVPOrderEntry, $params, $options);
if (!$stmt) {
    print_r(sqlsrv_errors());
}
$res = sqlsrv_execute($stmt);
if (!$res) {
    print_r(sqlsrv_errors());
}

// Now call sqlsrv_send_stream_data in a loop
while (sqlsrv_send_stream_data($stmt)) {
}
sqlsrv_next_result($stmt);

使用 PDO_SQLSRV 驅動程式

使用 PDO_SQLSRV 驅動程式時,這是相等的範例。 您可以使用 prepare/execute 搭配 bindParam,並將 TVP 輸入指定為 PDO::PARAM_LOB。 如果沒有,您將會收到此錯誤:Operand type clash: nvarchar is incompatible with …

try {
    $stmt = $conn->prepare($callTVPOrderEntry);
    $stmt->bindParam(1, $custCode);
    $stmt->bindParam(2, $tvpInput, PDO::PARAM_LOB);
    // 3 - OrdNo output
    $stmt->bindParam(3, $ordNo, PDO::PARAM_INT, 10);
    // 4 - OrdDate output
    $stmt->bindParam(4, $ordDate, PDO::PARAM_STR, 20);
    $stmt->execute();
} catch (PDOException $e) {
    ...
}

如果預存程序只接受輸入參數,您可以使用 bindValue 而非 bindParam

使用預設 dbo 結構描述以外的結構描述

如果您未使用預設 dbo 結構描述,則應該提供結構描述名稱。 即使結構描述名稱包含空白字元,也不要使用 [] 等分隔符號。

    $inputs = [
        ['ABC', 12345, null],
        ['DEF', 6789, 'This is a test']
    ];
    $schema = 'Sales DB';
    $tvpType = 'TestTVP';

    // i.e. the TVP type name is "[Sales DB].[TestTVP]"
    $tvpInput = array($tvpType => $inputs, $schema);

不透過預存程序使用資料表值參數

您可以不透過預存程序使用資料表值參數。 請考慮下列範例:

CREATE TYPE id_table_type AS TABLE(id INT PRIMARY KEY)

CREATE TABLE test_table (id INT PRIMARY KEY)

使用 SQLSRV 驅動程式

這是使用使用者定義結構描述時的範例:

$schema = 'my schema';
$tvpName = 'id_table_type';

$tsql = "INSERT INTO [$schema].[test_table] SELECT * FROM ?";
$params = [
[[$tvpname => [[1], [2], [3]], $schema]],
];

$stmt = sqlsrv_query($conn, $tsql, $params);
if (!$stmt) {
    print_r(sqlsrv_errors());
}
sqlsrv_free_stmt($stmt);

使用 PDO_SQLSRV 驅動程式

這是使用預設 dbo 結構描述時的範例:

$tsql = "INSERT INTO test_table SELECT * FROM ?";
$tvpInput = array('id_table_type' => [[1], [2], [3]]);

$stmt = $conn->prepare($tsql);
$stmt->bindParam(1, $tvpInput, PDO::PARAM_LOB);
$result = $stmt->execute();

另請參閱