問題描述

我想從 PHP 調(diào)用 MySQL 中的存儲過程.該過程采用輸入和輸出參數(shù)——不是INOUT"參數(shù).

From PHP I would like to call a stored procedure in MySQL. The procedure takes input and output parameters -- not "INOUT" parameters.

舉個(gè)簡單的例子，假設(shè)我在 MySQL 中有以下存儲過程:

For a simple example, say I have the following stored procedure in MySQL:

DELIMITER $$

DROP PROCEDURE IF EXISTS `test_proc`$$
CREATE PROCEDURE `test_proc`(
    in input_param_1 int,
    in input_param_2 int,
    in input_param_3 int,
    out output_sum int,
    out output_product int,
    out output_average int
)
BEGIN
    set output_sum = input_param_1 + input_param_2 + input_param_3;
    set output_product = input_param_1 * input_param_2 * input_param_3;
    set output_average = (input_param_1 + input_param_2 + input_param_3) / 3;
END$$

DELIMITER ;

現(xiàn)在，從 PHP 腳本/頁面方面，假設(shè)我有以下變量(我們將它們稱為proc 輸入變量")，我想將它們作為 輸入 參數(shù)提供給存儲過程當(dāng)我調(diào)用它時(shí):

Now, from the PHP script/page side, say I have the following variables (we'll call them "proc input variables") that I want to feed to the stored procedure as input parameters when I call it:

$procInput1 = "123";
$procInput2 = "456";
$procInput3 = "789";

假設(shè)在 PHP 腳本/頁面方面，我還有以下變量(我們將它們稱為proc 輸出變量")，我想將它們作為 輸出 參數(shù)提供給存儲過程在我調(diào)用它時(shí)由存儲過程設(shè)置:

Let's say that on the PHP script/page side I also have the following variables (we'll call them "proc output variables") that I want to feed to the stored procedure as output parameters to be set by the stored procedure when I call it:

$procOutput_sum;
$procOutput_product;
$procOutput_average;

所以，本質(zhì)上，在 PHP 腳本/頁面方面，我想要做的本質(zhì)上是(我意識到以下代碼無效)，是...

So, in essence, on the PHP script/page side, what I want to be able to do, in essence (I realize the following code is not valid), is...

call test_proc($procInput1, $procInput2, $procInput3, $procOutput_sum, $procOutput_product, $procOutput_average);

...并且，一旦調(diào)用，以下 PHP 代碼...

...and, once called, the following PHP code...

echo "Sum: ".$procOutput_sum;
echo "Product: ".$procOutput_product;
echo "Average: ".$procOutput_average;

...應(yīng)該產(chǎn)生以下輸出:

...should produce the following output:

Sum: 1368
Product: 44253432
Average: 456

一個(gè)警告是，如果可能的話，我希望能夠使用 MySQLi 過程 函數(shù)/接口來做到這一點(diǎn).如果不可能，那么我可以使用它.

One caveat is that, if at all possible, I would like to be able to do this using the MySQLi procedural functions/interface. If not possible, then however I can get it to work is what I'll use.

我已經(jīng)有一段時(shí)間編程了，但 PHP 語言對我來說是一個(gè)相對較新的嘗試.我找到了大量關(guān)于從 PHP 調(diào)用 MySQL 存儲過程的教程.有些是使用輸入參數(shù)調(diào)用存儲過程的教程，有些是使用輸出參數(shù)調(diào)用存儲過程的教程，還有一些是使用inout<調(diào)用存儲過程的教程/strong> 參數(shù).我還沒有找到任何關(guān)于調(diào)用存儲過程的教程或示例，這些存儲過程同時(shí)輸入和輸出參數(shù)，而特別不使用"inout"參數(shù).我無法弄清楚如何對參數(shù)綁定(例如:mysqli_stmt_bind_param 和 mysqli_stmt_bind_result)進(jìn)行編碼并使其全部正常工作.

$procInput1 = 123;
$procInput2 = 456;
$procInput3 = 789;

$mysqli = mysqli_connect();

$call = mysqli_prepare($mysqli, 'CALL test_proc(?, ?, ?, @sum, @product, @average)');
mysqli_stmt_bind_param($call, 'iii', $procInput1, $procInput2, $procInput3);
mysqli_stmt_execute($call);

$select = mysqli_query($mysqli, 'SELECT @sum, @product, @average');
$result = mysqli_fetch_assoc($select);
$procOutput_sum     = $result['@sum'];
$procOutput_product = $result['@product'];
$procOutput_average = $result['@average'];

$procInput1 = 123;
$procInput2 = 456;
$procInput3 = 789;

$mysqli = new mysqli();

$call = $mysqli->prepare('CALL test_proc(?, ?, ?, @sum, @product, @average)');
$call->bind_param('iii', $procInput1, $procInput2, $procInput3);
$call->execute();

$select = $mysqli->query('SELECT @sum, @product, @average');
$result = $select->fetch_assoc();
$procOutput_sum     = $result['@sum'];
$procOutput_product = $result['@product'];
$procOutput_average = $result['@average'];