PHP：使用 INPUT 和 OUTPUT 参数（不是“INOUT”）调用 MySQL 存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17014531/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PHP: Calling MySQL Stored Procedure with Both INPUT AND OUTPUT Parameters (NOT "INOUT")
提问by John Doe
From PHP I would like to call a stored procedure in MySQL. The procedure takes input andoutput 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 inputparameters 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 outputparameters to be set bythe 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 proceduralfunctions/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 inputparameters, some are tutorials on calling stored procedures with outputparameters, and some are tutorials on calling stored procedures with inoutparameters. I have not found any tutorials or examples on calling stored procedures that take bothinput andoutput parameters at the same time, while specifically notusing "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参数。我还没有找到任何关于调用同时接受输入和输出参数的存储过程的教程或示例，但特别是没有使用“输入输出”参数。我在弄清楚如何对参数绑定（例如：mysqli_stmt_bind_param 和 mysqli_stmt_bind_result）进行编码并使其全部正常工作时遇到了麻烦。
Any help will be greatly appreciated and I give thanks in advance!
任何帮助将不胜感激，我提前表示感谢！
回答by eggyal
Unfortunately, MySQLi does nothave any native support for output sproc parameters; one must instead output into MySQL user variablesand then fetch the values using a separate SELECTstatement.
不幸的是，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'];
