我在php中使用了mysql存储过程,但是我需要从我的sql返回几个值,所以我打算使用mysql函数或带有“out”参数的存储过程。
如果有人能帮助我,那就太好了。
我找到了解决方案
http://blog.ulf-wendel.de/2011/using-mysql-stored-procedures-with-php-mysqli/
$mysqli = new mysqli("localhost", "root", "", "test");
if (!$mysqli->query("DROP PROCEDURE IF EXISTS p") ||
!$mysqli->query('CREATE PROCEDURE p(OUT msg VARCHAR(50)) BEGIN SELECT "Hi!" INTO msg; END;'))
echo "Stored procedure creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
if (!$mysqli->query("SET @msg = ''") ||
!$mysqli->query("CALL p(@msg)"))
echo "CALL failed: (" . $mysqli->errno . ") " . $mysqli->error;
if (!($res = $mysqli->query("SELECT @msg as _p_out")))
echo "Fetch failed: (" . $mysqli->errno . ") " . $mysqli->error;
$row = $res->fetch_assoc();
echo $row['_p_out'];
PDO
扩展,请尝试下面的代码。<?php
$stmt = $dbh->prepare("CALL sp_returns_string(?)");
// assumes that the output is VARCHAR(100)
$stmt->bindParam(1, $return_value, PDO::PARAM_STR, 100);
// call the stored procedure
$stmt->execute();
print "procedure returned $return_value\n";
?>
输出参数
比输入参数稍微复杂一些,因为开发人员必须知道绑定参数时给定参数的大小。如果值比他们建议的大小要大,就会引发错误。