MySQL存储过程不适用于SELECT(基本问题) [英] MySQL Stored Procedures not working with SELECT (basic question)
问题描述
我正在使用一个平台(performforms),该平台要求我对大多数查询使用存储过程,并且从未使用过存储过程,所以无法弄清楚自己在做什么错.以下语句执行无错误:
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语句,因此我可以调用它,并返回多个字段的多行.现在,我只是想让任何选择正常工作.
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屋!