MySQL stored procedure introduction, creation, case, delete, view "recommended collection"

create procedure	存储过程的名字(参数列表)


BEGIN

		存储过程体（一组合法有效的sql语句）

END

	参数模式	参数名	参数类型
	举例：
	IN stauname varchar(20)

参数模式：
IN ：该参数可以作为输入,也就是该参数需要调用方 传入值
OUT	：该参数可以作为输出,也就是该参数可以作为返回值
INOUT：该参数既可以作为输入也可以作为输出,也就是该参数既需要传入值,又可以返回值

	语法：
		delimiter	结束标记
	案例：
		delimiter $

CALL 存储过程名(实参列表);

案例：
	要求：向boys表中插入数据
		select * from boys;
		
	a.创建存储过程：
delimiter $
create procedure mypro1()
begin 
INSERT into boys	VALUES(5,'张三',1223);
INSERT into boys	VALUES(6,'张6',1233);
INSERT into boys	VALUES(7,'张7',1243);
INSERT into boys	VALUES(8,'张8',1253);
END $

b.调用存储过程：
call mypro1();

案例：
	要求：创建存储过程实现,根据女生名,Find the corresponding male information

	a.创建存储过程：
	delimiter $
	create procedure mypro3 (IN girlname varchar(20) )
BEGIN
	select * from boys b right join beauty g
	ON b.id=g.boyfrind_id
	where g.name = girlname;
END $

	b.调用存储过程：
	call mypro3('刘岩')
	call mypro3('小昭')

a. 创建存储过程
delimiter $#Defines the symbol for the end of the stored procedure
create procedure mypro4(IN girlname varchar(20),OUT mingzi varchar(20))	
BEGIN
select b.boyname INTO mingzi#The result of the query is given to the variablemingzi进行赋值
FROM 
beauty g JOIN boys b 
ON g.boyfrind_id=b.id
where g.NAME=girlname;#Make conditional girls namesg.NAMEEqual to the value passed in when calling the stored procedure
END $

b.调用存储过程
#@bNameis the set accepting variable,用来放out模式的mingzi返回的值
CALL mypro4('小昭',@bName);
#Query variables directly,便可以得到结果,这是8.0版本的写法,5.Several versions need to end with a defined terminator
select @bName

delimiter $
create PROCEDURE mypr05(inout a int,INOUT b int)
BEGIN	
SET	a=a*2;
SET	b=b*2;
end $

#这里不能直接调用,否则返回的a,bThe value of the variable has nowhere to store,
#So two variables need to be set,赋初值,用c,dvariable to pass in the initial value,Then receive the new value returned

#First define two user variables,to store the returneda,b变量值
SET @c=2;
SET @d=10;

call mypr05(@c,@d)

#查看c,d的值,That is, after the call,a,b的返回值
select @c,@d

drop procedure 储存过程名 

show create procedure 存储过程名