MySQL 存储过程不适用于 SELECT(基本问题) [英] MySQL Stored Procedures not working with SELECT (basic question)

问题描述

我使用的平台(perfectforms)要求我对大多数查询使用存储过程，并且从未使用过存储过程，我无法弄清楚我做错了什么.以下语句执行没有错误:

I am using a platform (perfectforms) that requires me to use stored procedures for most of my queries, and having never used stored procedures, I can't figure out what I'm doing wrong. The following statement executes without error:

DELIMITER //
DROP PROCEDURE IF EXISTS test_db.test_proc//
CREATE PROCEDURE test_db.test_proc() SELECT 'foo'; //
DELIMITER ;

但是当我尝试使用以下方法调用它时:

But when I try to call it using:

CALL test_proc();

我收到以下错误:

#1312 - PROCEDURE test_db.test_proc can't return a result set in the given context

我在 phpmyadmin 3.2.4、PHP 版本 5.2.12 和 mysql 服务器版本是 5.0.89-community 中执行这些语句.

I am executing these statements from within phpmyadmin 3.2.4, PHP Version 5.2.12 and the mysql server version is 5.0.89-community.

当我编写一个返回参数的存储过程，然后选择它时，一切正常(例如):

When I write a stored procedure that returns a parameter, and then select it, things work fine (e.g.):

DELIMITER //
DROP PROCEDURE IF EXISTS test_db.get_sum//
CREATE PROCEDURE test_db.get_sum(out total int)
BEGIN
SELECT SUM(field1) INTO total FROM test_db.test_table;
END //
DELIMITER ;

工作正常，当我调用它时:

works fine, and when I call it:

CALL get_sum(@t); SELECT @t;

我得到的总和没问题.

最终，我需要做的是在存储过程中包含一个花哨的 SELECT 语句，这样我就可以调用它，并返回多行的多个字段.现在我只是想让 any 选择工作.

Ultimately, what I need to do is have a fancy SELECT statement wrapped up in a stored procedure, so I can call it, and return multiple rows of multiple fields. For now I'm just trying to get any select working.

非常感谢任何帮助.

推荐答案

想通了.这不是 PHP 的错误(尽管它曾经是) - 这是某些版本的 phpmyadmin 中的错误.相同的错误间歇性地重新出现，然后在各种颠覆中得到修复(见上文):

Figured it out. This is not a bug with PHP (though it used to be) - it's a bug in some versions of phpmyadmin. The same bug intermittently reappears and is then fixed in various subversions (see above):

#1312 - PROCEDURE [name] can't return a result set in the given context

此行为似乎仅限于 phpmyadmin 中存储过程中的 SELECT 语句.

This behavior appears limited to SELECT statements within stored procedures inside phpmyadmin.

使用像 MySQL Workbench 这样的客户端可以解决这个问题(或者你可以升级 phpmyadmin，但如果你像我一样在共享服务器上，那会很痛苦).

Using a client like MySQL Workbench works around the problem (or you could upgrade phpmyadmin, but that's a pain if you're on a shared server like I am).

无论如何，感谢大家的帮助.

Anyway, thanks to everyone for your help.

这篇关于MySQL 存储过程不适用于 SELECT(基本问题)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！