mysql inputoutput,PHP：同时使用INPUT和OUTPUT参数（不是“ INOUT”）调用MySQL存储过程...

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

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 ;

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

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;

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

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

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.

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!

解决方案

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.

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