在 PHP 中使用 PDO 检索 MySQL 存储过程变量
从 MySQL 存储过程检索 LAST_INSERT_ID() 的值可能是一项艰巨的任务。尽管网上有充足的文档,但这个过程对许多开发人员来说往往难以捉摸。本文旨在揭开这个问题的神秘面纱,提供使用 PHP PDO 检索变量值的分步指南。
带有“IN”、“INOUT”和“OUT”的 PHP 过程参数参数
检索变量的关键是理解所涉及的两阶段过程。首先,必须使用必要的输入参数和存储结果的变量来执行该过程。随后,执行单独的查询以检索指定变量中的值。
使用 PDO 检索 MySQL 存储过程的两阶段过程
为了演示此过程,让我们考虑以下场景:
SQL 过程:
CREATE PROCEDURE `demoSpInOutSqlVars`(IN pInput_Param INT, INOUT pInOut_Param INT, OUT pOut_Param INT) BEGIN /* * Pass the full names of SQL User Variable for these parameters. e.g. '@varInOutParam' * These 'SQL user variables names' are the variables that Mysql will use for: * 1) finding values * 2) storing results * * It is similar to 'variable variables' in PHP. */ SET pInOut_Param := ABS(pInput_Param) + ABS(pInOut_Param); /* always positive sum */ SET pOut_Param := ABS(pInput_Param) * -3; /* always negative * 3 */ END
PHP代码:
// DB Connection $db = appDIC('getDbConnection', 'default'); // get the default db connection $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, true); $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); // Set PHP Variables $phpInParam = 5; $phpInOutParam = 404; // PHP InOut variable $phpOutParam = null; // PHP Out variable // Define and Prepare the SQL procedure call $sql = "call demoSpInOut(:phpInParam, @varInOutParam, /* mysql variable name will be read and updated */ @varOutParam)"; /* mysql variable name that will be written to */ $stmt = $db->prepare($sql); // Bind PHP Variables and Set SQL Variables $stmt->bindParam(':phpInParam', $phpInParam, PDO::PARAM_INT); $db->exec("SET @varInOutParam = $phpInOutParam"); // This is safe as it just sets the value into the MySql variable. // Execute the procedure $allOk = $stmt->execute(); // Get the SQL Variables into the PHP variables $sql = "SELECT @varInOutParam AS phpInOutParam, @varOutParam AS phpOutParam FROM dual"; $results = current($db->query($sql)->fetchAll()); $phpInOutParam = $results['phpInOutParam']; $phpOutParam = $results['phpOutParam']; // Display the PHP variables echo "phpInParam = " . $phpInParam . "\n"; echo "phpInOutParam = " . $phpInOutParam . "\n"; echo "phpOutParam = " . $phpOutParam . "\n";
附加说明:
结论:
在 PHP PDO 中检索 MySQL 存储过程变量是一个多方面的过程,涉及执行过程并从 SQL 用户变量中检索值。通过遵循上述步骤,开发人员可以使用 PDO 高效地从 MySQL 存储过程访问所需的变量。
以上是如何在 PHP 中使用 PDO 检索 MySQL 存储过程变量?的详细内容。更多信息请关注PHP中文网其他相关文章!