mysql自学笔记十（Navicat Premium 15）

#存储过程和函数
/*
存储过程和函数：类似于java中方法
好处：
1、提高代码的重用性
2、简化操作

*/

#存储过程
/*

含义：一组预先编译好的sql语句的集合，理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数,提高了效率

*/

#一、创建语法
create procedure 存储过程名(参数列表)
begin

				存储过程体(一组合法的sql语句)

end

注意：
1、参数列表包含三部分
参数模式 参数名 参数类型
举例：
in stuname varchar(20)

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

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

语法：delimter 结束标记
delimter $


#二、调用语法
call 存储过程名(实参列表);

#1、空参列表
#插入到admin表中五条记录
select * from admin;

delimiter $
create PROCEDURE myp1()
begin
		insert into admin(username,`password`) VALUES('john1','0000'),('lili','0000'),('rose','0000'),('jack','0000'),('tom','0000');
end $

#调用
call myp1()$

#2、创建带in模式参数的存储过程
#①、创建存储过程实现 根据女神名，查询对应的男神信息
delimiter $
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('热巴')$

#②、创建存储过程实现，用户是否登录成功
delimiter $
create procedure myp3(in username VARCHAR(20),in password varchar(20))
begin
		declare result int default 0;
		select count(*) into result from admin where admin.username=username and admin.password=password;
		select if(result>0,'登录成功','登录失败');#使用变量
end $

#调用
call myp3('张飞','8888')$


#3、创建带out模式的存储过程
#①、根据女神名，返回对应的男神名
delimiter $
create procedure myp4(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$ #在begin end外定义变量，用户变量，这一行也可以省略
call myp4('小昭',@bName)$
select @bName$

#②、根据女神名，返回对应的男神名和男生魅力值
delimiter $
create procedure myp5(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 myp5('小昭',@bName,@usercp)$
select @bName,@usercp$

#4、创建带inout
#传入a和b两个值，最终a和b都翻倍并返回
delimiter $
create PROCEDURE myp6(inout a int,inout b int)
begin
		set a=a*2;
		set b=b*2;
end $
 
#调用
set @m=10$
set @n=20$
call myp6(@m,@n)$
select @m,@n$

#二、删除存储过程
#语法：drop procedure 存储过程名
drop procedure myp1;
drop procedure myp1,myp2;#错误！！！

#三、查看存储过程的信息
DESC myp1;#错误！！！
show create procedure myp1; 

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

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

#2、创建存储过程或函数实现传入女神名，返回：女神 and 男神 格式的字符串
-- 如 传入：小昭
-- 返回 小昭 and 张无忌
delimiter $
create procedure test_pro2(in beautyName VARCHAR(20), out str VARCHAR(50))
begin
 select concat(beautyName,'and',IFNULL(boyName,'null')) into str from boys bo right join beauty b on b.boyfriend_id=bo.id where b.name=beautyName;
end $

call test_pro2('小昭',@str)$
select @str $

#3、创建存储过程或函数，根据传入的条目数和起始索引，查询beauty表的记录
delimiter $
create procedure test_pro3(in startIndex int,in size int)
begin
select * from beauty limit startIndex,size;
end $

call test_pro3(2,3)$


#函数
/*
含义：一组预先编译好的sql语句的集合，理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数,提高了效率

区别：

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

*/

#一、创建语法：
create function 函数名(参数列表) returns 返回类型
BEGIN
		函数体
end
/*
注意：
1、参数列表 包含两个部分：
参数名 参数类型

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

return 值;

3、函数体中仅有一句话，则可以省略begin end

4、使用delimiter语句设置结束标记
*/

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

#举例
#1、无参有返回
#返回公司的员工的个数
delimiter $
create function myf1() returns int
begin
		declare c int default 0; #定义局部变量
	 	select count(*) into c from employees;
		return c;
end $

select myf1()$

#2、有参有返回
#根据员工名返回她的工资
delimiter $
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('Lex') $
 
 #根据部门名，返回该部门的平均工资
delimiter $
 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') $

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

#四、删除函数
drop function myf3;

#创建两个函数，实现传入两个float，返回二者之和
delimiter $
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);