如何在带有 Zend 框架的 MySql 数据库中使用存储过程? [英] How can I use a stored procedure in a MySql database with Zend Framework?

问题描述

我最近学会了使用 Zend Framework.我做了一个简单的 CRUD 应用程序.但是现在我想将现有数据库用于更复杂的应用程序，我想知道如何在模型中调用存储过程，如何发送参数，如何读取结果并将它们存储在 PHP 中的数组中.请.我感谢任何形式的帮助 :)

I'm recently have learned to use Zend Framework. I did a simple CRUD application. But now I want to use a existing database for a more complex application and I want to know how I call a stored procedure in the Model, how to send parameters, how to read the results and store them in an array in PHP. Please. I appreciate any kind of help :)

推荐答案

这并不难.下面是一个带有 IN 参数、OUT 参数和结果集的 MySQL 存储过程示例:

It's not too hard. Here's an example of a MySQL stored procedure with an IN parameter, an OUT parameter, and a result set:

CREATE PROCEDURE MyProc(IN i INTEGER, OUT o INTEGER)
BEGIN
  SELECT i+10 INTO o;
  SELECT i, o;
END

您可以使用 query() 方法调用它，并传递一个参数:

You can call this with the query() method, and pass a parameter:

$stmt = $db->query("CALL MyProc(?, @output)", array(25));
print_r( $stmt->fetchAll() );

诀窍是 MySQL 存储的 proc 可能返回多个结果集(例如，如果 proc 有多个 SELECT 查询).因此，在您可以执行另一个 SQL 查询之前，API 必须遍历所有结果集.否则，您会收到命令不同步"错误.

The trick is that MySQL stored procs might return multiple result sets (if the proc had multiple SELECT queries for instance). So the API must advance through all result sets before you can execute another SQL query. Or else you get the "Commands out of sync" error.

如果您使用 PDO_MySQL 适配器:

If you use the PDO_MySQL adapter:

while ($stmt->nextRowset()) { }

如果你使用 MySQLi 适配器，你会发现 Zend_Db_Statement_Mysqli 没有实现 nextRowset()，所以你必须调用内部的 mysqli 连接对象:

If you use the MySQLi adapter, you'll find that Zend_Db_Statement_Mysqli doesn't implement nextRowset(), so you have to call the internal mysqli connection object:

while ($db->getConnection()->next_result()) { }

清除结果集后，您可以运行后续 SQL 查询，例如获取过程的 OUT 参数的值:

Once you clear the result sets, you can run subsequent SQL queries, for example to fetch the value of the procedure's OUT parameter:

$stmt = $db->query("SELECT @output");
print_r( $stmt->fetchAll() );

这篇关于如何在带有 Zend 框架的 MySql 数据库中使用存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！