PHP：使用INPUT和OUTPUT参数调用MySQL存储过程（不是“INOUT”）

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

从PHP我想在MySQL中调用存储过程。该过程采用输入和输出参数 - 而不是“INOUT”参数。

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

举个简单的例子,假设我在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 ;

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:

现在,从PHP脚本/页面方面,说我有以下变量(我们称之为“proc输入变量”),当我调用它时,我想将其作为输入参数提供给存储过程:

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

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:

让我们说在PHP脚本/页面方面我还有以下变量(我们将它们称为“proc输出变量”),我想将它作为输出参数提供给存储过程,当我调用时由存储过程设置它:

$procOutput_sum;
$procOutput_product;
$procOutput_average;

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...

所以,从本质上讲,在PHP脚本/页面方面,我希望能够做到的,实质上(我意识到以下代码无效),是...

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

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

......以及,一旦调用,以下PHP代码......

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

...should produce the following output:

......应该产生以下输出:

Sum: 1368
Product: 44253432
Average: 456

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.

需要注意的是,如果可能的话,我希望能够使用MySQLi程序函数/接口来实现这一点。如果不可能,那么我可以让它工作就是我将要使用的。

I have been programming for quite some time, but the PHP language is a relatively new endeavor for me. I have found tons of tutorials on calling MySQL stored procedures from PHP. Some are tutorials on calling stored procedures with input parameters, some are tutorials on calling stored procedures with output parameters, and some are tutorials on calling stored procedures with inout parameters. I have not found any tutorials or examples on calling stored procedures that take both input and output parameters at the same time, while specifically not using "inout" parameters. I'm having trouble figuring out how to code the parameter bindings (e.g.: mysqli_stmt_bind_param and mysqli_stmt_bind_result) and getting it all to work properly.

我已经编程了很长一段时间,但PHP语言对我来说是一个相对较新的努力。我已经找到了大量关于从PHP调用MySQL存储过程的教程。一些是使用输入参数调用存储过程的教程,一些是使用输出参数调用存储过程的教程,另一些是使用inout参数调用存储过程的教程。我没有找到任何关于调用同时接受输入和输出参数的存储过程的教程或示例,而没有使用“inout”参数。我无法弄清楚如何编写参数绑定的代码(例如:mysqli_stmt_bind_param和mysqli_stmt_bind_result)并让它全部正常工作。

Any help will be greatly appreciated and I give thanks in advance!

任何帮助将不胜感激,我提前表示感谢!

2 个解决方案

#1

Unfortunately, MySQLi does not have any native support for output sproc parameters; one must instead output into MySQL user variables and then fetch the values using a separate SELECT statement.

不幸的是,MySQLi对输出sproc参数没有任何原生支持;必须输入MySQL用户变量,然后使用单独的SELECT语句获取值。

Using the procedural interface:

使用程序界面:

$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'];

Or, using the object-oriented interface:

或者,使用面向对象的界面:

$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'];

#2

-1

Here is a variation of the above answer, PHP code to call your MySQL stored procedure:

以下是上述答案的变体,用于调用MySQL存储过程的PHP代码:

require("/home/course/username/database/login.php"); //path to your login information file

$link = mysqli_connect($host, $user, $pass);
if (!$link) die("Couldn't connect to MySQL");

mysqli_select_db($link, $db)
or die("Couldn't open $db: ".mysqli_error($link));

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

//Call stored procedure and insert query execution
$sql = "CALL test_proc($procInput1, $procInput2, $procInput3, @output_sum, @output_product, @output_average)";
if(mysqli_query($link, $sql)){
echo "Procedure test_proc records added/updated successfully.";
} else{
echo "ERROR: Couldn't execute $sql. " . mysqli_error($link);
}

$select = $mysqli->query('SELECT @output_sum, @output_product, @output_average');
$result = $select->fetch_assoc();
$procOutput_sum     = $result['@output_sum'];
$procOutput_product = $result['@output_product'];
$procOutput_average = $result['@output_average'];

#1