一、存储过程
含义:一组预先编译好SQL语句的集合,理解成批处理语句
- 提高代码的重用性
- 简化操作
- 减少编译次数,并且减少了和数据服务器的连接次数,提高了效率
1. 创建存储过程
create procedure 存储过程名(参数列表)
begin
存储过程体(一组合法的SQL语句)
end
注意:
- 参数列表包含三部分 : 参数模式、参数名、参数类型
in stuname varchar(20)
参数模式:
in : 该参数可以作为输入,也就是该参数需要调用方传入值
out : 该参数可以作为输出,也就是该参数可以作为返回值
inout : 该参数既可以作为输入又可以作为输出,也就是该参数既需要传入值,又可以返回值
- 如果存储过程体仅仅只有一句话,begin end 可以省略;存储过程体中的每条sql语句的结尾要求必须加分号;存储过程的结尾可以使用 delimiter 重新设置
语法:
delimiter 结束标记
2. 存储过程调用
call 存储过程名(实参列表)
- 插入到admin表中五条记录(空参列表)
delimiter $
create procedure mypl()
begin
insert into admin(username,password) values("张飞","000"),("刘备","111"),("关羽","222"),("赵云","3333"),("马超","444");
end $
//调用
call myp1()$
- 创建存储过程实现 根据女神名,查询对应的男神信息(创建带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("柳岩")$
- 创建存储过程实现用户是否登录成功(创建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")$
- 根据女神名返回对应的男神名(创建带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$
- 根据女神名返回对应的男神名和男神魅力值(创建带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)$
- 传入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 函数名(参数列表);
- 返回公司的员工个数(无参数返回)
create function myf1() returns int
begin
declare c int default 0; #定义局部变量
select count(*) into c #赋值
from employees;
return c; #返回变量
end $
//调用
select myf1() $
- 根据员工名,返回它的工资(有参数有返回)
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") $
- 根据部门名,返回该部门的平均工资(带 参数有返回)
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 函数名;
- 创建函数,实现传入两个 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) $