PHP:同时使用INPUT和OUTPUT参数(不是"INOUT")调用MySQL存储过程 [英] PHP: Calling MySQL Stored Procedure with Both INPUT AND OUTPUT Parameters (NOT "INOUT")

问题描述

从PHP我想在MySQL中调用存储过程.该过程需要输入和输入参数-不 "INOUT" 参数.

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

举一个简单的例子，说我在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 ;

现在，从PHP脚本/页面的角度说，我有以下变量(我们将它们称为"proc输入变量")，我想将这些变量作为 input 参数提供给存储过程当我称呼它时:

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";

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

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;

因此，从本质上讲，在PHP脚本/页面方面，我想做的本质上是(我意识到以下代码无效)是...

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);

...以及曾经被调用的以下PHP代码...

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

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

...应产生以下输出:

...should produce the following output:

Sum: 1368
Product: 44253432
Average: 456

一个警告是，如果可能的话，我希望能够使用MySQLi procedural 函数/界面来做到这一点.如果不可能，那么我将使用它.

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.

我已经进行了一段时间的编程，但是PHP语言对我来说是一个相对较新的尝试.我发现了大量关于从PHP调用MySQL存储过程的教程.有些是使用 input 参数调用存储过程的教程，有些是使用 output 参数调用存储过程的教程，有些是使用 inout 参数.我没有找到有关调用存储过程的任何教程或示例，而这些存储过程需要同时使用输入和输入参数，而不是使用"inout"参数.我在弄清楚如何对参数绑定进行编码时遇到麻烦(例如:mysqli_stmt_bind_param和mysqli_stmt_bind_result)，并使它们全部正常工作.

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.

任何帮助将不胜感激，我先感谢您！

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

推荐答案

不幸的是，MySQLi 没有对输出sproc参数有任何本机支持；必须将其输出到MySQL 用户变量，然后使用单独的SELECT语句获取值

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.

使用程序界面:

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

这篇关于PHP:同时使用INPUT和OUTPUT参数(不是"INOUT")调用MySQL存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！