PHP:使用 INPUT 和 OUTPUT 参数(非“INOUT")调用 MySQL 存储过程

2021-12-20 00:00:00 php mysql stored-procedures

我想从 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 输入变量")，我想将它们作为 输入 参数提供给存储过程当我调用它时:

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 输出变量")，我想将它们作为 输出 参数提供给存储过程在我调用它时由存储过程设置:

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 过程 函数/接口来做到这一点.如果不可能，那么我可以使用它.

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 存储过程的教程.有些是使用输入参数调用存储过程的教程，有些是使用输出参数调用存储过程的教程，还有一些是使用inout<调用存储过程的教程/strong> 参数.我还没有找到任何关于调用存储过程的教程或示例，这些存储过程同时输入和输出参数，而特别不使用"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'];

相关文章