CREATEPROCEDURE myp2(IN beautyName VARCHAR(20))BEGINSELECT bo.*FROM boys bo
RIGHTJOIN beauty b ON bo.id = b.boyfriend_id
WHERE b.name=beautyName;END $
#调用CALL myp2('柳岩')$
创建存储过程实现,用户是否登录成功
CREATEPROCEDURE myp4(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))BEGINDECLARE result INTDEFAULT0;#声明并初始化SELECTCOUNT(*)INTO result#赋值FROM admin
WHERE admin.username = username
AND admin.password = PASSWORD;SELECTIF(result>0,'成功','失败');#使用END $
#调用CALL myp3('张飞','8888')$
创建out 模式参数的存储过程
根据输入的女神名,返回对应的男神名
CREATEPROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))BEGINSELECT bo.boyname INTO boyname
FROM boys bo
RIGHTJOIN
beauty b ON b.boyfriend_id = bo.id
WHERE b.name=beautyName ;END $
根据输入的女神名,返回对应的男神名和魅力值
CREATEPROCEDURE myp7(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT usercp INT)BEGINSELECT boys.boyname ,boys.usercp INTO boyname,usercp
FROM boys
RIGHTJOIN
beauty b ON b.boyfriend_id = boys.id
WHERE b.name=beautyName ;END $
#调用CALL myp7('小昭',@name,@cp)$
SELECT@name,@cp$
创建带inout模式参数的存储过程
传入a和b两个值,最终a和b都翻倍并返回
CREATEPROCEDURE myp8(INOUT a INT,INOUT b INT)BEGINSET a=a*2;SET b=b*2;END $
#调用SET@m=10$
SET@n=20$
CALL myp8(@m,@n)$
SELECT@m,@n$