【数据库六】存储过程

1.存储过程概述

1.1 存储过程定义

存储过程是一组为了完成特定功能的SQL语句集合。

存储过程在使用过程中是将常用或者复杂的工作，预先使用SQL语句写好并用一个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时，只需要调用它即可。存储过程在执行上比传统SQL速度更快、执行效率更高。

1.2 存储过程优点

（1）执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率
（2）SQL语句加上控制语句的集合，灵活性高
（3）在服务器端存储，客户端调用时，降低网络负载==
（4）可多次重复被调用，可随时修改，不影响客户端调用
（5）可完成所有的数据库操作，也可控制数据库的信息访问权限

1.3 创建存储过程

delimiter $$							#将语句的结束符号从分号;临时改为两个$$(可以是自定义)
create procedure proc()					#创建存储过程，过程名为proc，不带参数
-> begin								#过程体以关键字begin开始
-> select * from Store_Info;			#过程体语句
-> end $$								#过程体以关键字end结束
delimiter ;								#将语句的结束符号恢复为分号

##调用存储过程##
call proc;

##查看存储过程##
show create procedure [数据库.]存储过程名;		#查看某个存储过程的具体信息
show create procedure proc;
show procedure status [like '%proc%'] \G

创建存储过程的步骤：

(1) 先使用delimiter临时修改自定义SQL结束符
delimiter $$

(2) 使用create procedure [库名].存储过程名() 创建存储过程
create procedure [库名].存储过程名()

(3) 再用delimiter把SQL语句结束符改回分号
delimiter ;

(4) 使用call [库名].存储过程名调用存储过程
call 存储过程名

(5) 查看存储过程

show create procedure [数据库.]存储过程名;		#查看某个存储过程的具体信息

举例：创建存储过程

mysql -uroot -p111
create database dzg;
use dzg;

###创建proc1的存储过程
delimiter $$
create procedure proc1()
begin
create table dzg.ky28 (id int,name varchar(10),age int,hobby varchar(50));
insert into dzg.ky28  values (1,'校长',39,'唱歌');
insert into dzg.ky28  values (2,'老师',28,'跳舞');
insert into dzg.ky28 values (3,'主任',47,'打架');
select * from dzg.ky28;
end $$
delimiter ;

###调用存储过程
call proc1;
desc ky28;
select * from ky28;

###查看存储过程的创建过程
show create procedure dzg.proc1\G
show procedure status%proc%'\G

2. 存储过程参数

in 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
out 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
inout 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

2.1 输入参数

输入参数举例一：

use dzg;
delimiter $$
create procedure proc2(in input_name varchar(10))
begin
select * from ky28 where name=input_name;
end $$
delimiter ;

call proc2('校长');
call proc2('主任');

show create procedure dzg.proc2\G

输入参数举例二（向数据表中添加多条数据记录）：

use dzg;
delimiter $$
create procedure proc6(in input_id int,in input_name varchar(10),in input_age int,in input_hobby varchar(50))
begin
insert into ky28 values (input_id,input_name,input_age,input_hobby);
end $$
delimiter ;

###调用存储过程，传入要输入的数据记录
call proc6(4,'司机',27,'开车');
call proc6(5,'厨师',40,'做饭');

select * from ky28;

2.2 输出参数

输出参数举例：

use dzg;
delimiter $$
create procedure proc3(in input_name varchar(10),out output_age int)
begin
select age into output_age from ky28 where name=input_name;
end $$
delimiter ;

###调用存储过程，输入的第一个参数为输入参数，第二个参数为输出参数
call proc3('老师',@out_age);
select @out_age;

call proc3('校长',@out_age);
select @out_age;

2.3 输入输出参数

输入输出参数举例一：（务必保证输入和输出的参数，为同一种数据类型）

use dzg;
delimiter $$
create procedure proc4(inout myvar int)
begin
select age into myvar from ky28 where id=myvar;
end $$
delimiter ;

###自定义初始值，调用存储过程，查看变量保存的值
set @inout_var=2;
call proc4(@inout_var);
select @inout_var;

set @inout_var=3;
call proc4(@inout_var);
select @inout_var;

输入输出参数举例二：（务必保证输入和输出的参数，为同一种数据类型）

use dzg;
delimiter $$
create procedure proc5(inout myname_hobby varchar(50))
begin
select hobby into myname_hobby from ky28 where name=myname_hobby;
end $$
delimiter ;

set @inout_var='校长';
call proc5(@inout_var);
select @inout_var;

set @inout_var='主任';
call proc5(@inout_var);
select @inout_var;

2.4 存储过程参数总结

• in 传入参数
• out 传出参数
• inout 传入传出参数

###输入/输出参数
create procedure [库名].存储过程名(in 输入参数名 参数数据类型, out  输入参数名 参数数据类型)
begin
   select  字段 into  输出参数名  from  字段  where 字段 = 输入参数名;
end$$

call (参数值, 变量名);        #传出的参数值只能用变量获取

###输入输出参数
create procedure [库名.]存储过程名(inout  参数名 参数数据类型)
begin
   select  字段 into  参数名  from  字段  where 字段 = 参数名;
end$$

set 变量名 = 值;
call (变量名);               #传入传出参数值只能用变量

3. 删除存储过程

存储过程内容的修改方法是通过删除原有存储过程，之后再以相同的名称创建新的存储过程。如果要修改存储过程的名称，可以先删除原存储过程，再以不同的命名创建新的存储过程。

drop procedure if exists proc;	#仅当存在时删除，不添加if exists时，如果指定的过程不存在，则产生一个错误

举例：

drop procedure proc2;
drop procedure proc2;  #由于第一遍已经删除存储过程proc2,如果没有if exists，第二遍在删除的时候会报错
drop procedure if exists proc2;  #无论proc2是否存在，都不会报错

4.存储过程的控制语句

4.1 条件语句if --else

举例一：

###if-then-else ···· end if 
use dzg;
delimiter $$
create procedure proc7(in input_age int)
begin
if input_age > 40 then
update ky28 set hobby='金钱' where id=3 or id=2;
else
update ky28 set hobby = '玩耍' where id=4 or id=1;
end if;
end $$
delimiter ;

call proc7(42);
select * from ky28;

call proc7(28);
select * from ky28;

注意： if中的条件表达式与SQL语句中,实际执行的where条件表达式没有任何关系;SQL语句真正执行的条件，是满足符合where条件表达式执行后的结果。

举例二：（根据性别，自动判别属于'男子组'还是'女子组'）

use dzg;
create table game(name varchar(10),sex char(2),team char(3));
desc game;
delimiter $$
create procedure proc8(in input_name varchar(10),in in_sex char(2))
begin 
if in_sex='男' then
insert into game values(input_name,in_sex,'男子组');
else
insert into game values(input_name,in_sex,'女子组');
end if;
end $$
delimiter ;

call proc8('小红','女');
call proc8('小伟','男');
call proc8('小丽','女');

select * from game;

4.2 循环语句

举例：（调用存储过程，一次性添加20条数据记录）

###while ···· end while
use dzg;
delimiter $$
create procedure proc9()
begin
declare i int;
set i=1;
create table class28(id int,name varchar(20));
while i<=20
do insert into class28 values(i,concat('student',i));
set i=i+1;
end while;
end $$
delimiter ;

call proc9;
show tables;
select * from class28;
select count(id) from class28;

4.3 存储过程控制语句总结

###条件语句
if 条件表达式 then
  SQL语句序列
[else]
  SQL语句序列
end if;

###循环语句
while 条件表达式 do
  SQL语句序列
  set 条件迭代方式
end while;