php 使用 PDO 调用带有 Out 参数的存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13382922/
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
Calling stored procedure with Out parameter using PDO
提问by TheMethod
I've been using PDO for awhile now and am refactoring a project so that it uses stored procs instead of inline SQL. I am getting an error that I can't explain.I am using PHP version 5.3.5 and MySQL version 5.0.7.
我已经使用 PDO 一段时间了，我正在重构一个项目，以便它使用存储过程而不是内联 SQL。我收到一个无法解释的错误。我使用的是 PHP 5.3.5 版和 MySQL 5.0.7 版。
I'm just trying to get a basic stored proc with an output to work. Here is the stored proc:
我只是想让一个带有输出的基本存储过程工作。这是存储的过程：
DELIMITER //
CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100))
BEGIN
SET var1 = 'This is a test';
END //
Here is the code I am using to call the proc, $db is an instance of PDO:
这是我用来调用 proc 的代码，$db 是 PDO 的一个实例：
$stmt = $db->prepare("CALL proc_OUT(?)");
$stmt->bindParam(1, $return_value, PDO::PARAM_STR, 4000);
// call the stored procedure
$stmt->execute();
echo $returnvalue;
Simple right? However, it results in the following error:
简单吧？但是，它会导致以下错误：
exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1414 OUT or INOUT argument 1 for routine mydb.proc_OUT is not a variable or NEW pseudo-variable in BEFORE trigger
If I call the proc directly like so:
如果我像这样直接调用 proc：
CALL proc_OUT(@res);
SELECT @res;
it works as expected which leads me to believe that there is a problem with how it is being called with PHP, however I can't seem to find what the issue is. I am following the instructions in the manualbut am still getting this error. Could anyone suggest what I could be doing wrong? Any advice would be very much appreciated. Thanks much!
它按预期工作，这使我相信 PHP 调用它的方式存在问题，但是我似乎无法找到问题所在。我正在按照手册中的说明进行操作，但仍然出现此错误。谁能建议我可能做错了什么？任何建议将不胜感激。非常感谢！
采纳答案by Matteo Tassinari
It would seem that there is a bug at work here, best solution I've found is this:
这里似乎存在一个错误，我发现的最佳解决方案是：
http://www.php.net/manual/en/pdo.prepared-statements.php#101993
http://www.php.net/manual/en/pdo.prepared-statements.php#101993
From the comment at the link above:
从上面链接的评论：
$dbh->query("CALL SomeStoredProcedure($someInParameter1, $someInParameter2, @someOutParameter)"); $dbh->query("SELECT @someOutParameter"); // OR, if you want very much to use PDO.Prepare(), // insert "SELECT @someOutParameter" in your stored procedure and then use: $stmt = $dbh->prepare("CALL SomeStoredProcedure(?, ?)"); $stmt ->execute(array($someInParameter1, $someInParameter2));
$dbh->query("CALL SomeStoredProcedure($someInParameter1, $someInParameter2, @someOutParameter)"); $dbh->query("SELECT @someOutParameter"); // OR, if you want very much to use PDO.Prepare(), // insert "SELECT @someOutParameter" in your stored procedure and then use: $stmt = $dbh->prepare("CALL SomeStoredProcedure(?, ?)"); $stmt ->execute(array($someInParameter1, $someInParameter2));
See also this: https://stackoverflow.com/a/4502524/815386
回答by sdespont
You need to specify that your parameter is IN/OUT style like PHP web site example :
您需要指定您的参数为 IN/OUT 样式，如 PHP 网站示例：
http://php.net/manual/en/pdo.prepared-statements.phpexample #5
http://php.net/manual/en/pdo.prepared-statements.php示例 #5
<?php
$stmt = $dbh->prepare("CALL sp_takes_string_returns_string(?)");
$value = 'hello';
$stmt->bindParam(1, $value, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000);
// call the stored procedure
$stmt->execute();
print "procedure returned $value\n";
回答by Marco Marsala
Got it! Just add a
知道了！只需添加一个
SELECT @outputparam;
at the end of the stored procedure, where @outputparam is the name used for the param in the stored procedure definition. If you cannot edit the stored procedure, you should do a second query, for SELECT @outputparam, with PHP PDO to get the output param value.
在存储过程的末尾，其中@outputparam 是用于存储过程定义中的参数的名称。如果您无法编辑存储过程，您应该使用 PHP PDO 对 SELECT @outputparam 进行第二次查询以获取输出参数值。
Tip: If you're using the deprecated DBLib to connect to SQL Server and you modified the stored procedure as suggested, you'll also need to tweak your syntax to get the output param value in the calling PHP script:
提示：如果您使用已弃用的 DBLib 连接到 SQL Server 并按照建议修改了存储过程，则还需要调整语法以获取调用 PHP 脚本中的输出参数值：
$out = 0;
$sth = $db->prepare("DECLARE @myout INT; EXECUTE mysp :firstparam, :secondparam, @myout OUTPUT;"); // the DECLARE trick is needed with DBLib
$sth->bindParam(':firstparam', $firstparam, PDO::PARAM_INT);
$sth->execute();
$sth->bindColumn(1, $out, PDO::PARAM_INT);
$sth->fetch(PDO::FETCH_BOUND);
var_dump($out); // works
