MySQL—— 存储过程和函数

存储过程和函数：类似于Java中的方法

好处：

（1）提高代码的重用性。
（2）简化操作。

存储过程：一组预先编译好的SQL查询语句的集合。减少了编译次数，并且减少了和数据库服务器连接次数，提高了效率。

语法:

create procedure 存储过程名（参数列表）
begin
	sql 语句(存储过程体) 
end ;

注释：

参数列表包含三样东西：

参数模式      参数名     参数类型

举例：

IN          stuname    varchar(20)

参数模式有三种：

（1）IN :该参数可以作为输入，需要在调用的时候传入值。
（2）OUT:该参数可以作为输出（返回值）。
（3）INOUT ： 该参数即可做输入，也可以做输出。

调用存储过程：call 存储过程名 （实参列表）；

一，创建带IN模式参数的存储过程

案例1：根据输入的女神名，查询出相关男神资料

create procedure myp1(IN beautyName varchar(20))   

// procedure 代表这是存储体,myp1 存储体名字,IN 参数模式 （输入），beautyName 自定义参数  

begin 	 //存储体的开始

	select bo.*
	from boys bo
	right  join beauty b on bo.id=b.boyfriend_id
	where b.name = beautyName ;
	
end ;	//存储体的结束

调用： call myp1 ('迪丽热巴')；
注释：因为这个myp1存储体有一个输入参数，所以调用的时候需要携带一个参数值进去。

案例2：一次性传入多个参数，判断传入的账号密码是否正确

create procedure myp2(IN userName varchar(20),IN passWord varchar(20))
begin
	declare result varchar(20) default  ' ' ;     #声明局部变量，并初始化
		//declare 定义局部变量 ，result 变量名， default 默认值 ，''表示默认值为空
		//每次声明局部变量的时候就要用 declare 来修饰
	select count(*) into result    #赋值
		//count(*) 符合下面的where里面的条件则获取符合条件的个数，并赋值给变量result 
	from admin # 表名
	where admin.username= userName  and admin.password =passWord ;
	select result ;
		//这里的select就像Java里面的print 一样，输出结果。
end ; 

调用：call myp2('张飞','8888');
注释：当存储体的参数列表里的参数名跟表的列名一样的时候，在调用表的列名是可以用 表名·列名 的形式来区分列名和参数名，不然都会默认为参数名

案例3：一次性传入多个参数，判断传入的账号密码是否正确

create procedure myp3(IN userName varchar(20),IN passWord varchar(20))
begin
	declare result int default 0 ; #定义局部变量 result
	select count(*) into result  #赋值
	from admin
	where admin.username=userName and admin.password=passWord;
	select if(result>0,'成功','失败');
	//select 表示查询，当result 值大于0（有符合添加的）则成功，否则失败
end ;

调用：call myp3('张飞','8888')

二，创建带out 模式（带返回结果）的存储过程

案例1：根据女神名返回男神名

create  procedure myp5(IN beautyName varchar(20),out boyName varchar(20))
	//这里的in 是输入，调用的时候输入值，out是输出
begin
	select bo.boyName INTO boyName  #这是牛逼之处，直接把结果赋值给输出参数上
	from boys bo
	inner join beauty b on bo.id=b.boyfriend_id
	where b.name=beautyName;
end ;

调用：

set @bName ; #定义用户变量，用来接收返回出的结果

call myp5('张三'，@bName)

注释：因为这个存储体有两个参数（一个输入，一个输出），所以调用的时候需要带两个参数，因为有一个返回结果（out），所以为了在存储体外（可以理解为方法外）能接收返回的结果，就需要定义一个用户变量@bName（可以在存储体外和存储体内使用）

查看存储体运行之后的结果：select @bName ;

案例2： (一个输入两个输出) 根据输入的女神名输出对应的男神名和男神编号

create procedure myp6(In beautyName varchar(20),out boyName varchar(20),out boyId Int)
begin
	select bo.boyname,bo.boyid INTO boyName ,boyId 
	# 因为有两个输出，所以把要输出的东西一个个对应起来赋值给输出变量
	from boys bo
	inner join  beauty b on bo.id=b.boyfried_Id 
	where b.name=beautyName ;
	#这里的beautyName 就是in 输入的变量的值
end ;

调用：

#自定义用户变量，来接受调用出来的结果
set @bname;
set @bid;
call myp6('张三',@bname,@bid);

查看调用之后的结果：select @bname,@bid ;

三，创建带 inout 模式参数的存储过程

案例：传入a,b的值，最终都翻倍返回。

create procedure myp8(inout a INT , inout b INT)
begin
	set a=a*2;
	set b=b*2;
end ;

调用：

#为了接收输出的结果，自定义两个用户变量
set @m=10;
set @n=20;
call myp8(@m,@n);

查看存储体运行之后的结果：select @m,@n;

函数：

好处：

（1）提高了代码的重用性。
（2）简化操作。

函数与存储体的区别：

存储过程：可以有0个或多个返回，调用时用call 。
函数：有且仅有1个返回，return，一定要有return语句，没有return语句会报错。调用时使用select 。

语法：

create function  函数名 （参数列表）returns  返回类型
begin 
	函数体
end ;

查看函数结构：show create function 函数名；
删除函数：drop function 函数名；

函数体中仅有一句话时则可以忽略begin ··· end
使用 delimiter 语句来设置结束标志，比如： delimiter $ （这样设置完后就可以不用 ；而是用$ 来做结束符）

案例1：无参有返回 返回公司员工个数

create function myf1() returns INT
begin
	declare c int default 0;  #定义局部变量，并赋值为0
	select count(*) into c # 给c赋值（count是求总共数量的函数）
	from employees;
	return c;
end ;

案例2： 有参有返回 根据员工名返回工资

create function myf2(name varchar(20)) returns double
begin
	set @sal=0; #定义用户变量 ，用户变量一定要加@
	select salary INTO @sal 
	from employees
	where last_name = name;
	return @sal;   #这个语句绝对不能忘记
end ;

调用：select myf2('小张')；

案例3：传入两个值，求和

create function f1 (num1 float,num2 float) returns float
begin
	declare sum float default 0; #定义局部变量并赋值为0
	set sum=num1+num2;
	return sum ;
end ;

查询函数结果：select f1 (1,2);
结果为：3 ；