MySQL基础总结(七) 存储过程和函数

---

MySQL基础总结(七)
存储过程和函数

---

存储过程

概念 一组预先编译好的SQL语句集合(有点类似于批处理文件),比较适合做pill插入和更新

作用

1. 提高代码的重用性
2. 简化操作
3. 减少编译次数,提高效率

存储过程的创建

语法

create procedure <存储过程名>(<参数列表>)
begin
	<存储过程体(其实就是一组SQL语句>
end

注意

1. 参数列表包含三部分: <参数模式> <参数名> <参数类型>

2. 如果存储过程只有一句话,那么begin end可以省略

3. 存储过程体中每条SQL语句结束必须加;

4. 存储过程的结尾需要使用 结束标记 ,结束标记需要在定义存储过程之前声明,例如:

delimiter $  #声明结束标记为 "$"
create procedure <存储过程名>(<参数列表>)
begin
	<存储过程体(其实就是一组SQL语句>
end $  #使用结束标记结束

注意

1. 不要写成delimiter $;(后面不有加;),如果在这条语句后面加;,那就成了将$;这两个符号作为结束符,而不是$

2. 在使用delimiter $之后,就已经将当前会话的结束符换成了$,所以接下来的所有SQL语句的结束符都要用$

• 参数模式

  • in

  该参数可以作为输入,即需要传入值

  • out

  该参数可以作为输出,即返回值

  • inout

  该参数可以作为既可以作为输入,也可以作为输出

• 参数名自定义就可以

• 参数类型,即MySQL字段的类型,如int,varchar()等

储过程的调用

call <存储过程>(<实参列表>);

举例 1 (空参列表) 插入到user表中五条记录

delimiter $
create procedure test1()
begin
insert into `user`(`name`,`password`) values('aaa','111'),('bbb','222'),('ccc','333'),('ddd','444'),('eee','555');
end $

调用

call test1()$

举例 2 (带有in模式的参数列表) 根据男生的名字,查询其女朋友的信息

delimiter $
create procedure test2(In boyname varchar(20))
begin
	select girl.*
	from 女生表 girl
	join 男生表 boy
	on girl.姓名=boy.女朋友姓名
	where boy.姓名=boyname;
end $

调用

call test2('小明')$

举例 3 (带有in模式的参数列表) 输入用户名和密码,检测用户是否登录成功

delimiter $
create procedure login(in username varchar(20),in password varchar(20))
begin
	#声明一个result变量,默认值设为""
	declare result int default 0;
	#查询符合用户名和密码的数据,将结果赋给result变量
	select count(*) into result
	from 用户表 u
	where u.用户名=username
	and u.密码=password;
	#输出result结果
	select if(result>0,'登录成功','登录失败');
end $

调用

call login('小明','123456')$

举例 4 (带有out模式的参数列表) 根据男生的名字,查询其女朋友的信息

delimiter $
create procedure test4(in boyname varchar,out gfinfo varchar(20))
begin
	select girl.* into gfinfo
	from 女生表 girl
	join 男生表 boy
	on girl.姓名=boy.女朋友姓名
	where boy.姓名=boyname;
end $

调用

#先定义一个变量接收out 的值
set @gfName
#调用存储结构
call test4('小明',@gfName)$
#查看结果
select @gfName

注意

其实set @gfName这一句可以不写,直接在调用存储结构时,在参数列表里写上@gfName,会自动创建该变量

举例 5 (带有inout模式的参数列表) 传入a和b两个值,返回a和b乘2之后的结果

delimiter $
CREATE PROCEDURE test5 (INOUT a INT， INOUT b INT)
BEGIN
    SET a=a*2;
    SET b=b*2 ;
END $

调用

#先定义2个变量
set @m=10$
set @n=20$
#调用存储结构
call test5(@m,@n)$
#查看结果
select @m,@n$

存储过程的删除

drop procedure <存储过程名称>;

注意

1. 存储过程一次只能删除一个,不能一次性删除多个

2. 存储过程不支持修改,如果想要修改存储过程,必须删掉重建

查看存储过程的信息

show create procedure <存储过程名>;

---

函数

概念 同存储过程,只不过函数只能有一个返回值,且不能没有返回值,比较适合处理数据之后返回一个结果值

作用 跟存储过程差不多

1. 提高代码的重用性
2. 简化操作
3. 减少编译次数,提高效率

函数的创建

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

注意

1. 参数列表包含三部分: <参数名> <参数类型>

2. 函数体一定要有return语句,如果没有会报错,如果return没有放到函数体的最后,也不会报错,但是不建议这么写,因为没有意义

3. 函数体中如果只有一句SQL语句,则可以省略begin end\

4. 跟存储过程一样,使用delimiter语句设置结束标记

函数的调用

select <函数名> (<参数列表>)

举例 1 (无参) 返回公司的员工个数

delimiter $
create function func1() returns int
begin
	#定义一个变量作为返回值
	declare c int default0;
	#执行查询语句
	select count(*) into c
    from 员工表;
    #返回
    return c;	
end

调用

select func1()$

举例 2 (有参) 根据员工名返回他的工资

delimiter $
create function func2(empName varchar(20)) returns double
begin
	#定义一个变量,除了定义局部变量,也可以定义一个用户变量
	set @sal=0;
	#查询
	select salary into @sal
	from 员工表
	where 员工姓名=empName;
	#返回
	return @sal
end $

调用

select func2('小明')$

举例 3 创建函数,实现传入两个float,返回二者之和

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

调用

select func3(1,2);

查看函数

show create function <函数名>;

删除函数

drop function <函数名>;

入两个float,返回二者之和

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

调用

select func3(1,2);

查看函数

show create function <函数名>;

删除函数

drop function <函数名>;