【数据库六】存储过程

大家好，欢迎来到IT知识分享网。

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;