mysql存储过程和函数的学习
目录
- 存储过程
1.1 创建存储过程
1.2 调用存储过程
1.2 删除存储过程
1.3 查看存储过程的信息 - 函数
2.1 函数创建过程
2.2 函数调用
3.2 查看函数
3.3 删除函数
存储过程和函数: 类似于java中的方法
好处:
1、提高代码的重用性
2、简化操作
3、减少编译次数并且减少了和数据库服务器的连接次数,提高了效率
存储过程含义: 一组预先编译号的sql语句的集合,理解成批处理语句
一、 存储过程创建语法
create procedure 存储过程名(参数列表)
begin
存储过程体(一组合法是sql语句)
end
注意:
1、 参数列表包含三部分
参数模式 参数名 参数类型
例如:
in stuname varchar(20)
参数模式:
IN: 该参数可以作为输入,也就是该参数需要调用方传入值
OUT: 该参数可以作为输出,也就是该参数可以作为返回值
INOUT: 该参数既可以作为输入又可以作为输出,也就是该参数既需要传入值,又可以返回值
2、 如果存储过程体仅仅只有一句话,begin end可以省略
(1)存储过程体中的每条sql语句的结尾要求必须要分号。
(2)存储过的结尾可以使用delimiter重新设置
语法:
delimiter 结束标记
案例:
delimiter $
二、存储过程调用语法
call 存储过程名(实参列表);
1、空参列表
案例:插入到admin表中五条记录
delimiter $
create procedure p1()
begin
insert into admin(id,username,password) values(1,'john1',123), (2,'john2',456), (3,'john3',123), (4,'john4',456), (5,'john5',123);
end $
(1)调用
call myp3() $
(2)查询数据
select * from admin $
2、创建带in模式参数的存储过程
案例:创建存储过程实现根据女生名查询对应的男生信息
delimiter $
create procedure p2(in girlName varchar(20))
begin
select b.* from boy b right join girl g on b.id=g.boyId where g. girlName =girlName;
end $
(1)调用
call p3('lili')$
报错出现:
incorrect string value
解决方法:
set names gbk;
案例:创建存储过程实现,用户是否登录成功
create procedure p1(in username varchar(10),in password varchar(10))
begin
declare result int default 0;#声明并初始化
select count(*) into result #赋值
from admin a where a.username=username and a.password =password;
select if(result>0,'登录成功','登录失败');#使用
end $
call p1('john','123')$
3、创建带out模式的存储过程
案例:根据女生名,返回对应的男生名
create procedure m1(in girlName varchar(20),out boyName varchar(20))
begin
select b.boyName into boyName from boy b inner join girl g on b.id=g.boyId where g.girlName=girlName;
end $
#调用
set @bName;#定义一个用户变量接收返回值
call m1(‘lili’,@bName)$
select @bName$
案例:根据女生名,返回对应的男生名和年龄
create procedure m2(in girlName varchar(20),out boyName varchar(20),out age int)
begin
select b.boyName,b.age into boyName,age from boy b inner join girl g on b.id=g.boyId where g.girlName=girlName;
end $
#调用
call m2('lili',@bname,@age)$
select @bname,@age$
4、创建带inout模式参数的存储过程
案例:传入a和b两个值,最终a和b都翻倍并返回
create procedure m3(inout a int,inout b int)
begin
set a=a*2;
set b=b*2;
end$
#定义两个用户变量
set @m=10$
set @n=20
#调用
call m3(@m,@n)
select @m,@n$
练习
1、创建存储过程实现传入用户名和密码,插入到admin表中
create procedure m4(in username varchar(20),in password varchar(20) )
begin
insert into admin (admin.username,admin.password) values(username,password);
end$
call m4('admin','235')$
2、创建存储过程或函数实现传入女生编号,返回女生姓名和女生年龄
create procedure m5(in id int,out girlName varchar(20),out age int)
begin
select g.girlName,g.age into girlName,age from girl g where g.id=id;
end $
#调用
call m5('1',@name,@age)$
select @name,@age$
3、 创建存储过程实现传入两个女生生日,返回大小
create procedure m1(in birth1 datetime,in birth2 datetime,out result int )
begin
select datediff(birth1,birth2) into result;
end $
call m1('1998-3-5','1985-4-3',@res)$
select @res$
三、删除存储过程
语法: drop procedure 存储过程名
drop procedure m1;
四、查看存储过程
show create procedure m1$
案例:创建存储过程或函数实现传入一个日期,格式化成xx年xx月xx日并返回
create procedure test1(in mydate datetime, out strDate varchar(20))
begin
select date_format(mydate,'%y年%m月%d日') into strDate;
end$
#调用
call test1(now(),@str)$
select @str;
案例:创建存储过程或函数实现传入女生名字,返回:女生and男生格式的字符串。例如:传入lili,返回:lili and john
create procedure test2(in name varchar(20),out allName varchar(20))
begin
select concat(girlName,'and',ifnull(boyName,'null')) name into allName from girl g inner join boy b on g.boyId=b.id where g.girlName=name;
end$
#调用
call test2('lili',@name)$
select @name$
案例:创建存储过程或函数,根据传入的起始索引和条目数,查询girl表中的记录
create procedure test2(in num int,in firstIndex int)
begin
select * from girl limit num,firstIndex;
end$
#调用
call test2(1,2)$
函数含义:一组预先编译号的sql语句的集合,理解成批处理语句
好处:
1、提高代码的重用性
2、简化操作
3、减少编译次数并且减少了和数据库服务器的连接次数,提高了效率
区别:
存储过程: 可以有0个返回,也可以有多个返回,适合做批量插入或批量更新
函数: 有且仅有1个返回,适合做处理数据后返回一个结果
一、函数创建语法
create function 函数名(参数列表) returns 返回类型
begin
函数体
end$
注意:
1、参数列表包含两部分;
参数名 参数类型
2、函数体:肯定会有return语句,如果没有会报错
如果return语句没有放在函数体的最后也不报错,但不建议
return 值;
3、 函数体中仅有一句话,则可以省略begin end
4、使用delimiter语句设置结束标记
二、函数调用语法
select 函数名(参数列表)
1、无参有返回
案例:返回女生的个数
create function test3() returns int
begin
declare c int default 0; #定义局部变量
select count(*) into c from girl; #赋值
return c;
end$
#调用
select test3()$
2、有参有返回
案例:根据女生姓名返回她的年龄
create function test4(name varchar(20)) returns int
begin
set @age=0; #定义用户变量
#declare a int default 0; #定义局部变量
select age into @age from girl g where g.girlName=name;
return @age;
end$
#调用
select test4('lili')$
案例:根据部门名,返回该部门的平均工资
create function test5(name varchar(20)) returns double
begin
declare sal double default=0;
select avg(salary) into sal from employees e join departments d on e.departemnt_id=d.department_id where d.department_name=name;
return sal;
end$
select test5('IT')$
三、查看函数
show create function test3;
四、删除函数
drop function test3;
案例:创建函数,实现传入两个float,返回二者之和
create function test1(n float,m float) returns float
begin
declare sum float default 0;
set sum=n+m;
return sum;
end$
select test1(1,2)$