数据库：存储过程和函数

存储过程和函数

#存储过程和函数 
/*
存储过程和函数:类似于java中的方法
好处：
1、提高代码的重用性
2、简化操作
*/
#存储过程
/*
含义:一组预先编译好的SQL语句的集合，理解成批处理语句

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

#一、创建语法
create procedure 存储过程名(参数列表)
begin
			存储过程体(一组合法有效的SQL语句)
end
注意：
1、参数列表包含三部分
参数模式		参数名		 参数类型
举例：
in stuname varchar(20)

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

2、如果存储过程体仅仅只有一句话，begin end可以省略
存储过程体中的每条SQL语句的结尾要求必须加分号。
存储过程的结尾可以使用delimiter重新设置
语法:
delimiter 结束标记
案例：
delimiter $

#二、调用语法
call 存储过程名(实参列表);
#1、空参列表
#案例：插入到admin表中五条记录

select * from admin;

#到命令行中写
delimiter $
create procedure myp1()
begin
				insert into admin(username,password) values('john1','000'),('lily','000'),('rose','000'),('jack','000'),('tom','000');
end $

#调用
call myp1() $;

#2、创建带in模式参数的存储过程
#案例1:创建存储过程实现 根据女神名，查询对应的男神信息
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('柳岩') $;

#案例2:创建存储过程实现，用户是否登陆成功
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 IF(result>0,'成功','失败');#使用
end $
#调用
call myp3('张飞','8888') $

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

#案例1:根据女神名,返回对应的男神名
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 $

#调用
set @bName$
call myp5('柳岩',@bName)$
select @bName

#案例2:根据女神名，返回对应的男神名和男神魅力值
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);

#4.创建带inout模式参数的存储过程
#案例1:传入a和b两个值，最终a和b都翻倍并返回
create procedure myp8(inout a int,inout b int)
begin 
				set a=a*2;
				set b=b*2;
end $

set @m=10$
set @n=20$

call myp8(@m,@n)$
select @m,@n$

#二、存储过程的删除
语法: drop procedure 存储过程名
drop procedure test_pro3;#不能同时删除多个

#三、查看存储过程的信息
desc myp2;#不可以
show create procedure myp2;

#一、创建存储过程实现传入用户名和密码，插入到admin表中
create procedure test_pro1(in username varchar(20),in loginPwd varchar(20))
begin
			insert into admin(admin.username,password) values(username,loginpwd)
end $


#二、创建存储过程或函数实现传入女神编号，返回女神名称和电话
create procedure test_pro2(in id int,out name varchar(20),out phone varchar(20))
begin
			select b.Name,b.phone into Name,phone from beauty b where b.id=id
end $

call test_pro2(1,@n,@p);


#三、创建存储过程或函数实现传入两个女神生日，返回大小
create procedure test_pro3(in birth1 datetime,in birth2 datetime,out result int)
begin
				select datediff(birth1,birth2) into result;
end $
call test_pro3('1998-1-1',now(),@result)$

#四、创建存储过程或函数实现传入一个日期，格式化成xx年xx月xx日并返回
create procedure test_pro4(in mydate datetime,out strDate varchar(50))
begin
				select DATE_FORMAT(mydate,'%y年%m月%d日') into strDate;
end $

call test_pro4(NOW(),@str) $
select @str $

desc beauty;
#五、创建存储过程或函数实现传入女神名称，返回 女神 AND 男神 格式的字符串
create procedure test_pro5(in beautyName varchar(50),out str varchar(50))
begin
				select concat(beautyName,' and ',boyName) into str from boys bo right join beauty b on b.boyfriend_id=bo.id where b.name=beautyName;
end $

call test_pro5('柳岩',@str)$
select @str $
#六、创建存储过程或函数，根据传入的条目数和起始索引，查询beauty表的记录
	create procedure test_pro6(in startIndex int,in size int)
	begin
				select * from beauty limit startIndex,size
	end $

call test_pro6(3,5) $

函数

#函数
/*
含义:一组预先编译好的SQL语句的集合，理解成批处理语句

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

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

#一、创建语法
create function 函数名(参数列表) returns 返回类型
begin
				函数体
end

/*
注意：
1.参数列表 包含两部分：
参数名 参数类型

2.函数体：肯定会有return语句，如果没有会报错
如果return 语句没有放在函数体的最后也不报错，但不建议

return 值;

3.函数体中仅有一句话，则可以省略begin end
4.使用 delimiter语句设置结束标记

*/

#二、调用语法
select 函数名(参数列表) 

#三、查看函数
show create function myf3;

#四、删除函数
drop function myf3;

#案例
#一、创建函数，实现传入两个float，返回两者之和

create function test_fun1(num1 float,num2 float) returns float
begin
			declare sum float default 0;
			set sum=num1+num2;
			return sum;
end $
select test_fun1(1,2)$