MySQL服务器存储过程语法错误

I am having some trouble with creating a Stored Procedure for a MySQL database.

我在为MySQL数据库创建存储过程时遇到了一些麻烦。

Here is a Select statement that works:

这是一个有效的Select语句:

use canningi_db_person_cdtest;
SELECT *
FROM pet
WHERE name = 'Puffball';

Here is my Stored Procedure that does not work:

这是我的存储过程不起作用:

use canningi_db_person_cdtest;
CREATE PROCEDURE GetAllPets() 
BEGIN 
SELECT *
FROM pet
WHERE name = 'Puffball'; 
END

I am getting the following error:

我收到以下错误:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5

#1064 - 您的SQL语法有错误;检查与MySQL服务器版本对应的手册,以便在第5行的''附近使用正确的语法

How can I get this working?

我怎样才能使这个工作?

EDIT

How do I call this Stored Procedure? I have tried this with no result:

如何调用此存储过程?我试过没有结果:

use canningi_db_person_cdtest;
CALL GetAllPets();

This is the error:

这是错误:

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

2 个解决方案

#1

Add a delimiter to end your procedure. Example:

添加分隔符以结束您的过程。例:

use canningi_db_person_cdtest;

DELIMITER //

CREATE PROCEDURE GetAllPets() 
BEGIN 
    SELECT *
    FROM pet
    WHERE name = 'Puffball'; 
END//

If you need more information, please refer to http://dev.mysql.com/doc/refman/5.1/en/stored-programs-defining.html

如果您需要更多信息,请参阅http://dev.mysql.com/doc/refman/5.1/en/stored-programs-defining.html

#2

In this particular case, since you have only one statement in your procedure, you don't need to change DELIMITER nor use BEGIN...END block.

在这种特殊情况下,由于程序中只有一个语句,因此无需更改DELIMITER,也不需要使用BEGIN ... END块。

You procedure might look like this

你的程序可能看起来像这样

CREATE PROCEDURE GetAllPets() 
  SELECT *
    FROM pet
   WHERE name = 'Puffball';

And use it like this

并像这样使用它

CALL GetAllPets();

Here is SQLFiddle demo

这是SQLFiddle演示

#1