22.存储过程和函数

声明：此MySQL基础学习源自尚硅谷。（推荐）b站官方链接：https://www.bilibili.com/video/BV1xW411u7ax?p=1

存储过程和函数

存储过程和函数：类似java中的方法
好处：
1.提高代码的重用性
2.简化操作

存储过程

含义:
一组预先编译好的SQL语句的集合，理解成批处理语句。
好处：
1.提高代码的重用性
2.简化操作
3.减少了编译次数，并且减少了和数据库服务器连接次数，提高了效率。

procedure /prəˈ siː dʒə(r)/ 过程，步骤

1.创建语法

create procedure 存储过程名(参数列表)
begin
	存储过程体 (一组合法的SQL语句)
end

注意：

1.参数列表包含三部分
参数模式、参数名、参数类型
 
举例：
in stuname varchar(20) # 默认是in

-- 参数模式
in：该参数可以作为输入，也就是该参数需要调用方传入值
out：该参数可以作为输出，也就是该参数可以作为返回值
inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

2.如果存储过程体仅仅只有一句话，begin end可以省略
3.存储过程体中每条SQL语句的结尾要求必须加分号
4.存储过程的结尾可以使用DELIMITER重新设置
	语法：
	DELIMITER 结束标记 
	eg:	DELIMITER $ # 不要写分号结束

DELIMITER设置结束标记后，其他语句都要用这个设置的结束标记

2.调用语法

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

1.空参列表

案例：插入到admin表中五条记录

-- 设置结束标记，注意：设置结束标记后面不要写分号，设置结束标记是所有语句都要用设置的这个结束标记，并不是只设置给存储过程使用的，其他语句执行也要用这个结束标记，才能结束语句。
DELIMITER $
CREATE PROCEDURE myp1()
begin
	insert into admin(`username`,`password`) values
	('zhangsan','001'),
	('lisi','002'),
	('wangwu','003'),
	('zhaoliu','004'),
	('taoqi','005');
	
end $ # $表示存储过程结束
 
-- 不能在数据库管理工具中执行，需要cmd连接数据库执行。

2.创建带in模式参数的存储过程

案例1：创建存储过程实现 根据女神名，查询对应的男神信息

delimiter $
create 	procedure myp2(in beautyName varchar(20))
begin 
	select * from boys bo right join beauty b on bo.id = b.boyfriend_id 
	where b.name = beautyName;
end $

call myp2("苍老师")$

案例2：创建存储过程实现，用户是否登录成功

create procedure myp3(in username varchar(20),in `password` varchar(20))
begin 
	declare result int default 0 ; # 声明变量并初始化
	
	select count(*) into result # 变量赋值
	from admin
	where admin.username = username # 参数名与表字段同名，也遵循就近原则，可以用 表名.字段名 区别
	and admin.`password` = `password`;
	
	select if(result>0,'成功','失败'); # 变量使用
end $

3.创建带out模式的存储过程

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

create procedure myp5(in beautyName varchar(20), out boyName varchar(20))
begin
	select b.boyName into boyName # 查询得到结果赋值给返回参数boyName
	from beauty be right join boys b on be.boyfriend_id = b.id 
	where be.name = beautyName;
end $

call myp5('小昭',@bName)$
select @bName$

案例2：根据女神名，返回对应男神名和男神魅力值

create procedure myp6(in beautyName varchar(20),out boyName varchar(20),out userCP int)
begin
	select b.boyName,b.userCP into boyName,userCP 
	from beauty be right join boys b on b.id = be.boyfriend_id
	where be.name = beautyName;
end $

4.创建带inout模式参数的存储过程

案例：传入a和b两个值，最终a和b的值都翻倍并返回

create procedure myp7(inout a int,inout b int)
begin
	set a=a*2;
	set b=b*2;
end $

set @m=10$
set @n=20$
 
call myp7(@m,@n)$
select @m,@n$

3.存储过程删除

语法：
	drop procedure 存储过程名;
 
-- 不支持一次删除多个存储过程
drop procedure 存储过程1,存储过程2; # 执行错误

4.查看存储过程的信息

show create procedure 存储过程名;
 
-- 不能修改存储过程内的逻辑语句
-- 可以删除存储过程重新创建

函数

函数与存储过程的区别：
存储过程：可以有0个返回，也可以有多个返回，适合做批量插入，批量更新。
函数：有且仅有1个返回，适合做处理数据后返回一个结果

1.创建语法

create function 函数名(参数列表) returns 返回类型
begin 
	函数体
end

注意：
1.参数列表包含两部分
参数名 参数类型
 
2.函数体：肯定会有return语句，如果没有会报错。
如果return语句没有放在函数体的最后也不报错，但不建议。
return 值;
 
3.函数体中仅有一句话，则可以省略begin end
 
4.使用delimiter语句设置结束标记

2.调用语法

select 函数名(参数列表)

1.无参有返回

案例：返回公司的员工个数

delimiter $
create function myf1() returns int
begin
	-- 定义一个变量返回
	declare c int default 0; # 定义局部变量
	select count(*) into c # 变量赋值
	from employees;
	retrun c; # 返回
end $

select myf1() $

2.有参有返回

案例1：根据员工名，返回他的工资

create function myf2(empName varchar(20)) returns double
begin
	set @sal=0; # 定义用户变量
	select salary into @sal # 赋值
	from employees
	where last_name = empName;
	
	return @sal;
end $

select myf2('kochhar') $

案例2：根据部门名，返回该部门的平均工资

create function myf3(deptName varchar(20)) returns double
begin
	declare sal double;
	select avg(salary) into sal
	from employees e join departments d
	on e.department_id = d.department_id
	where d.department_name = deptName;
	
	return sal;
end $

select myf3('IT') $

3.查看函数

show create function myf2;

在数据库的mysql库中的proc表可以看到定义的存储过程和函数

4.删除函数

drop function myf2;

案例：创建函数，实现传入两个float，返回二者之和

create function myf3(num1 float,num2 float) return float
begin
	declare sum float default 0;
	set sum=num1+num2;
	
	return sum;
end $

select myf3(1.3,4.5)$