MySQL错误代码：END END附近的1064存储过程语法错误

I am Fairly new at the MYSQL programming, I need to create a Stored Procedure that adds an animal to the Animal Table, But it needs to check that the Foreign keys exist in the Catagory table (catID) and the Area Table (areaID).

我在MYSQL编程方面相当新,我需要创建一个将动物添加到Animal Table的存储过程,但它需要检查Catagory表(catID)和Area Table(areaID)中是否存在Foreign键。

This is what I have so far, but getting an error message that says there is a syntax error near END END, I have tried putting brackets but then there are just more errors.

这是我到目前为止,但收到一条错误消息,说END END附近有一个语法错误,我试过放括号,但后面只有更多的错误。

If any one can help that would be great!

如果任何人可以帮助那将是伟大的!

-- checking if the SP already exists and deletes it
DROP PROCEDURE IF EXISTS sp_AddAnimal;

-- creates the stored procedure to add animals to the Animal Table
DELIMITER $$
CREATE PROCEDURE sp_AddAnimal
        (
        IN  p_animalType                    VARCHAR(30), 
        IN  p_weight                        VARCHAR(6) , 
        IN  p_gender                        VARCHAR(7) , 
        IN  p_age                           VARCHAR(4) , 
        IN  p_stock                         INT        , 
        IN  p_catID                         INT        ,
        IN  p_areaID                        INT      
     )
BEGIN 

    IF EXISTS (SELECT catID FROM Catagory WHERE Catagory.catID = p_catID) THEN
    BEGIN
        IF EXISTS (SELECT areaID FROM Area WHERE Area.areaID = p_areaID) THEN
            BEGIN
                INSERT INTO Animal
                    (
                        animalType                    , 
                        weight                        , 
                        gender                        , 
                        age                           , 
                        stock                         , 
                        catID                         ,
                        areaID                        
                    )
                VALUES 
                    ( 
                        p_animalType                    , 
                        p_weight                        , 
                        p_gender                        , 
                        p_age                           , 
                        p_stock                         , 
                        p_catID                         , 
                        p_areaID                    
                    ) ; 
            END
    END   
END 

DELIMITER;

1 个解决方案

#1

The syntax for IF ... END IF blocks is:

IF ... END IF块的语法是:

IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF

From the link in the comments.

You need to use END IF instead of just END to close your

从评论中的链接。您需要使用END IF而不是END来关闭您的

IF EXISTS (SELECT catID FROM Catagory WHERE Catagory.catID = p_catID) THEN

and your

IF EXISTS (SELECT areaID FROM Area WHERE Area.areaID = p_areaID) THEN

#1