【MySQL的存储过程】

---

一、存储过程的概述

1、存储过程的定义

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

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

2、存储过程的优点

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

二、存储过程的步骤（面试题）

1、创建存储过程

1）先用delimiter临时修改SQL语句结束符(由于存储过程中使用的语句均为；结尾，不能让这些语句切断了整个存储过程语句)

delimiter $$

2）使用 create procedure 创建存储过程

create database dznb$$
use dznb$$
create procedure proc1()
  begin
  create table dznb.dznb01 (id int ,name varchar(10),age int,hobby varchar(50));
  insert into dznb.dznb01 values (1,'zhangsan',20,'dancing');
  insert into dznb.dznb01 values (2,'lisi',22,'singing');
  select * from dznb.dznb01;
  end$$

创建存储的时候会自动生成表结构和内容

3）再用delimiter把SQL语句结束符改回分号

delimiter ;

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

call proc1

创建存储过程

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

查看某个存储过程的具体信息

show create procedure [数据库.]存储过程名;

以列的方式显示存储过程执行的命令

show procedure status [like '%proc%'] \G

2、存储过程的参数

IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）

OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）

INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

in 参数的使用

create procedure proc2(in input_name varchar(10))
    -> begin
    -> select * from dznb01 where name = input_name;
    -> end$$

 delimiter ;

call proc2('zhangsan')

out参数的使用

delimiter $$

create procedure proc3(in input_name varchar(10), out output_age int)

begin

select age into output_age from dznb01 where name = input_name;

end$$

delimiter ;

call proc3('lisi',@out_age);

select @out_age;

call proc3('zhangsan',@out_age);

select @out_age;

inout参数的使用

语法
 
create procedure [库名.]存储过程名(inout  参数名 参数数据类型)
begin
   select  字段 into  参数名  from  字段  where 字段 = 参数名;
end$$
 
/*调用*/
set 变量名 = 值;
call (变量名);               #传入传出参数值只能用变量

delimiter $$
#使用inout 定义的参数必须为同一类型，如二者都为int或varchar
create procedure proc4(inout myvar int)
begin
select age into myvar from test where id=myvar;
end$$

delimiter ;


#调用
set @inout_var = 1;

call proc4( @out_myvar);

select @out_myvar;

三、删除存储过程

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

四、存储过程的控制语句

利用传入参数向dznb01表中插入数据
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 dznb01 values (input_id, input_name, input_age, input_hobby);
end$$

delimiter ;
call proc6(3,'qian yang',100,'pig');
call proc6(4,'qian',50,'dog');

1. 条件语句if-then-else ···· end if

delimiter $$
create procedure proc7(in input_age int)
begin
if input_age > 25 then
update dznb01 set hobby = 'money' where id = 1 or id = 2;
else
update dznb01 set hobby = 'play' where id = 3 or id = 4;
end if;
end$$

delimiter ;

call proc7(50);
select * from dznb01;

call proc7(20);
select * from dznb01;

2. 循环语句while ···· end while

#使用循环语句向表中插入数据
delimiter $$

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

delimiter ;
call proc9;
select * from class28;