MySql（二十五）存储过程

存储过程

引入：存储过程和函数类似于java中的方法

Java中方法的好处：

1. 提高了代码的重用性
2. 简化操作

存储过程定义：是一组预先编译好的sql语句的集合，理解成批处理语句

存储过程的好处：

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

创建语法

创建语法：

Create procedure 存储过程名(参数列表)

​ Begin

​ 存储过程体（一组合法的sql语句）

​ End

注意事项1：

参数列表包含三部分：参数模式 参数名 参数类型

参数模式：

1. In ：该参数可以作为输入也就是说该参数需要调用方传入值
2. Out ：该参数可以作为输出也就是说该参数可以作为返回值
3. Inout ：该参数既可以作为输入也可以作为输出，即该参数既需要传入值，又可以返回值

举个栗子： in stuname varchar(20);

注意事项2：

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

语法：delimiter 结束标记

Eg:delimiter $;//即这里的$就代表结束标记

在本章中的存储过程的结尾这里就均使用$符号做为结束标记，下面就不在赘述了…

调用语法

​ 调用语法：

Call 存储过程名(实参列表);

案例一：空参列表

1． 插入到admin表中五条记录

Delimiter $  /**设置结束标记(后面结束时都将使用这个$)**/
/**创建**/
Create procedure myp1()
Begin
	Insert into admin(username,pwd)
	Values
	('join1','000'),
	('join1','000'),
	('join1','000'),
	('join1','000'),
	('join1','000');
End $
/**调用**/
Call myp1()$

案例二：创建带in模式参数的存储过程

表beauty

id	name	sex	phone	borndate	boyfriend
1	柳岩	女	15090833772	2018-1-2	1
2	AngleBaby	女	19037399002	2012-3-2	3
3	赵丽颖	女	15909877112	1998-2-1	4
4	赵敏	女	15980291854	1998-2-3	2
5	周芷若	女	15892041335	1993-12-4	2
6	金星	女	18390833220	1992-1-2	9

表boy

id	boyname	usercp(魅力值)
1	大鹏	100
2	张无忌	339
3	黄晓明	99
4	冯绍峰	400
5	男神·	500

1. 根据女神名，查询对应的男神信息

   /**创建**/
   Create procedure myp2(IN beautyName varchar(4))
   Begin
   	Select b.*,bea.name from boy b
   	Right join beauty bea
   	On b.id = bea.boyfriend_id
   	Where bea.name = beautyName;
   End $
   /**调用**/
   Call myp2(‘赵敏’)$
   

案例三：创建带out模式参数的存储过程

1. 根据女神名返回男神名

   /**创建带一个参数的存储过程**/
   Create procedure myp3(in beautyName varchar()4,out boyName varchar(4))
   Begin
   	select b.boyName into boyName
   	from boys b
   	inner join beauty bea
   	on b.id = bea.boyfriendid
   	where beautyName = bea.name; 
   End $
   /**调用**/
   /**定义变量，用于存储从表中查询到的数据**/
   set @bName$
   Call myp3('柳岩',bName)$
   /**查询**/
   select @bName$
   

2. 根据女神名，返回对应的男神名和魅力值

   /**创年**/
   create procedure myp4(in beautyName varchar(4), out boyName varchar(4),out usercp int)
   begin
   	select b.boyName,b.usercp into boyName,usercp
   	from boys b
   	inner join beauty bea 
   	in b.id = bea.boyfriend
   	where baeutyName = bea.name;
   end $
   /**调用这里的“@bName和@ucp是直接创建的变量，用于存储从表中查询到满足条件的数据”**/
   call myp4("柳岩",@bName,@ucp)$
   /**查看**/
   select @bName,@ucp$
   

   案例四：创建带inout模式参数的存储过程

3. 传入a和b的值，最后a和b都翻倍并返回

   /**创建存储过程**/
   create procedure myp4(inout a int, inout b int)
   begin
   	set a = a*1;
   	set b = b*2;
   end $
   /**调用(创建用户变量用于存取数值)**/
   set @num1 = 2$
   set @num2 = 2$
   call myp4(@num1,@num2)$
   /**查看**/
   select @num1,@num2$
   

   案例讲解

   案例一：创建存储过程实现传入用户名和密码，插入到admin表中

   /**创建存储过程**/
   create procedure myp(in userName varchar(4), in pwd varchar(6))
   begin
   	insert into admin(admin.userName, admin.pwd)
   	values(userName,pwd);
   end $
   

   案例二：创建存储过程或函数实现传入女神编号，返回女神名称和电话

   表beauty

   id	name	sex	phone	borndate	boyfriend
   1	柳岩	女	15090833772	2018-1-2	1
   2	AngleBaby	女	19037399002	2012-3-2	3
   3	赵丽颖	女	15909877112	1998-2-1	4
   4	赵敏	女	15980291854	1998-2-3	2
   5	周芷若	女	15892041335	1993-12-4	2
   6	金星	女	18390833220	1992-1-2	9

   /**创建存储过程**/
   create procedure myp1(in beauty_id int ,out beautyName varchar(4), out phone varchar(11))
   begin
   	select bea.beautyName,bea.phone into beautyName,phone
   	from beauty
   	where beauty_id = bea.beauty_id;
   end$
   /**调用(这里的“@n和@p是直接创建的用户变量用于存储从表中查找的数据”)**/
   call myp1(1,@n,@p)$
   /**查看**/
   select @n,@p$	
   

   案例三：创建存储过程或函数实现传入一个日期，格式化成xx年xx月xx日并返回

   /**创建存储过程**/
   create procedure myp3(in mydt datetime,out rdt varchar(10) )
   begin
   	select  date_format(mydt,'%Y年%c月%d日') into rdt;
   end$
   /**调用**/
   call mp3(now(),@returnDt)$
   /**查看**/
   select @returnDt $
   

   案例四：创建存储过程或函数实现传入一个女神名，返回：“女神 and 男神” 格式的字符串

   表beauty

   id	name	sex	phone	borndate	boyfriend
   1	柳岩	女	15090833772	2018-1-2	1
   2	AngleBaby	女	19037399002	2012-3-2	3
   3	赵丽颖	女	15909877112	1998-2-1	4
   4	赵敏	女	15980291854	1998-2-3	2
   5	周芷若	女	15892041335	1993-12-4	2
   6	金星	女	18390833220	1992-1-2	9

   表boy

   id	boyname	usercp(魅力值)
   1	大鹏	100
   2	张无忌	339
   3	黄晓明	99
   4	冯绍峰	400
   5	男神·	500

   /**创建存储过程**/
   create procedure myp4(in beautyName varchar(4),out couple varchar(10))
   begin
    select concat(beautyName,' and ',ifnull(b.boyName ,'null'))  into couple
    from boy b into couple
    right join beauty beau
    in beau.'boyfriend' = b.'id'
    where beautyName = beau.name;
   end $
   /**调用**/
   call myp4('AngleBaby',@str) $
   /**查看**/
   select @str$
   

案例五：创建存储过程或函数：根据传入的条目数和起始索引，查询beauty表的记录

/**创建存储过程**/
create procedure myp5(in startindex int, in size int)
begin
	select * from beauty 
	limit startindex,size;
end$
/**调用：显示beauty表中索引从3开始的共5条数据**/
call myp5(3,5)$

存储过程的删除

语法：

drop procedure 存储过程名;

注意：存储过程的删除一次只能删除一个

存储过程的信息查看

语法：

show create procedure 存储过程名;