Mysql存储过程和函数

一、存储过程

含义：一组预先编译好SQL语句的集合，理解成批处理语句

1. 提高代码的重用性
2. 简化操作
3. 减少编译次数，并且减少了和数据服务器的连接次数，提高了效率

1. 创建存储过程

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

注意:

1. 参数列表包含三部分 ： 参数模式、参数名、参数类型

in stuname varchar(20)

参数模式：

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

2. 如果存储过程体仅仅只有一句话，begin end 可以省略；存储过程体中的每条sql语句的结尾要求必须加分号；存储过程的结尾可以使用 delimiter 重新设置

语法：
delimiter 结束标记

2. 存储过程调用

call 存储过程名（实参列表）

1. 插入到admin表中五条记录（空参列表）

delimiter $
create procedure mypl()
begin
	insert into admin(username,password) values("张飞","000"),("刘备","111"),("关羽","222"),("赵云","3333"),("马超","444");
end $
//调用
call myp1()$

2. 创建存储过程实现 根据女神名，查询对应的男神信息（创建带in模式参数的存储过程）

create procedure myp2(in beautyName varchar(20))
begin
	select bo.* from boys bo right join beauty b on bo.id = b.boyfriend_id
	where b.name = beautyName;
end $
//调用
call myp2("柳岩")$

3. 创建存储过程实现用户是否登录成功（创建in模式参数的存储过程）

create procedure myp3(in username varchar(20),in password varchar(20))
begin
	declare result varchar(20) default '';	#声明并初始化
	select count(*) into result #赋值
	from admin where admin.username = username and admin.password = password;
	select result;	#使用
end $
//调用
call myp3("张飞","8888")$

4. 根据女神名返回对应的男神名（创建带out模式的存储过程）

create procedure myp5(in beautyName varchar(20),out boyName varchar(20))
begin
	select bo.boyName into boyName
	from boys bo 
	inner join beauty b on bo.id = b.boyfriend_id
	where b.name = beautyName;
end $
//调用
call myp5("小昭",@bName)$
select @bName$

5. 根据女神名返回对应的男神名和男神魅力值（创建带out模式的存储过程）

create procedure myp6(in beautyName varchar(20),out boyName varchar(20),out userCP int)
begin
	select bo.boyName ,bo.userCP into boyName,userCP
	from boys bo 
	inner join beauty b on bo.id = b.boyfriend_id
	where b.name = beautyName;
end $
//调用
call myp6("小昭",@bName,@userCP)$

6. 传入a和b两个值，最终a和b都翻倍，并返回（创建带inout模式参数的存储过程）

create procedure myp7(inout a int,inout b int)
begin
	set a = a * 2;
	set b = b * 2;
end $
//调用
set @m = 20 $
set @n = 10 $
call myp7(@m,@n) $
select @m,@n $

3. 存储过程的删除

drop procedure 存储过程名;	//每次只能删除一个

4. 查看存储过程的信息

show create procedure 存储过程名;

二、函数

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

1. 创建函数

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

参数列表 包含两部分：参数名 参数类型
函数体：肯定有return语句，如果没有肯定会报错
当函数体中仅有一句话，则可以省略 begin end
使用 delimiter 语句设置结束标记

2. 函数调用

select 函数名(参数列表);

1. 返回公司的员工个数（无参数返回）

create function myf1() returns int
begin
	declare c int default 0;	#定义局部变量
	select count(*) into c #赋值
	from employees;
	return c;	#返回变量
end $
//调用
select myf1() $

2. 根据员工名，返回它的工资(有参数有返回)

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("k_ing") $

3. 根据部门名，返回该部门的平均工资（带 参数有返回）

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 函数名;

4. 删除函数

drop function 函数名;

1. 创建函数，实现传入两个 float ,返回二者之和

create function test(num1 float,num2 float) returns float
begin
	declare sum float default 0;
	set sum = num1 + num2;
	return sum;
end $
//调用
select test(1.25,25.2) $