代码之家 › 专栏 › 技术社区 › Sean Cortez

用一个in和int参数创建mysql存储过程

stored-procedures mysql

Sean Cortez · 技术社区 · 6 年前

    CREATE PROCEDURE SelectPetDetails
(
IN PetId int
) 
BEGIN
SELECT *  FROM pet p JOIN animal a on p.int_AnimalId=a.int_AnimalId JOIN breed b on a.int_BreedId=b.int_BreedId JOIN colorpattern c ON a.int_ColorPatternId=c.int_ColorPatternId WHERE int_PetOwnerId= PetId
END

我创建存储过程有什么问题?

1 回复 | 直到 6 年前

Ivan Cachicatari 6 年前

要创建存储过程,必须更改默认分隔符 ; 其他字符,如“$$”或“//”或任何您想要的字符。

-- must change ; to $$ because stored procedure can have ; multiple times
DELIMITER $$  
drop procedure if exists SelectPetDetails$$  -- if you plan execute this many times
CREATE PROCEDURE SelectPetDetails (IN PetId int) 
BEGIN
    SELECT *  FROM pet p 
        JOIN animal a on p.int_AnimalId=a.int_AnimalId 
        JOIN breed b on a.int_BreedId=b.int_BreedId 
        JOIN colorpattern c ON a.int_ColorPatternId=c.int_ColorPatternId 
    WHERE int_PetOwnerId = PetId;
-- mark as end of stored procedure body 
END $$ 

DELIMITER ; 
-- return to regular delimiter