MySQL的存储过程

1.存储过程介绍

1.1 SQL指令执行过程

从SQL执⾏执⾏的流程中我们分析存在的问题：

1. 如果我们需要重复多次执⾏相同的SQL， SQL执⾏都需要通过连接传递到MySQL，并且
   需要经过编译和执⾏的步骤；
2. 如果我们需要连续执⾏多个SQL指令，并且第⼆个SQL指令需要使⽤第⼀个SQL指令执⾏
   的结果作为参数；

1.2 存储过程的介绍

存储过程：
将能够完成特定功能的SQL指令进⾏封装（SQL指令集），编译之后存储在数据库服务
器上，并且为之取⼀个名字，客户端可以通过名字直接调⽤这个SQL指令集，获取执⾏
结果。

1.3 存储过程优缺点分析

存储过程优点：

1. SQL指令⽆需客户端编写，通过⽹络传送，可以节省⽹络开销，同时避免SQL指令在⽹络
   传输过程中被恶意篡改保证安全性；
2. 存储过程经过编译创建并保存在数据库中的，执⾏过程⽆需重复的进⾏编译操作，对SQL
   指令的执⾏过程进⾏了性能提升；
3. 存储过程中多个SQL指令之间存在逻辑关系，⽀持流程控制语句（分⽀、循环），可以实
   现更为复杂的业务;

存储过程的缺点：

1. 存储过程是根据不同的数据库进⾏编译、创建并存储在数据库中；当我们需要切换到其
   他的数据库产品时，需要重写编写针对于新数据库的存储过程；
2. 存储过程受限于数据库产品，如果需要⾼性能的优化会成为⼀个问题；
3. 在互联⽹项⽬中，如果需要数据库的⾼（连接）并发访问，使⽤存储过程会增加数据库
   的连接执⾏时间（因为我们将复杂的业务交给了数据库进⾏处理）

2.创建存储过程

2.1 存储过程创建语法

-- 语法:
create procedure <proc_name>([IN/OUT args])
begin
-- SQL
end;

2.1 示例

-- 创建⼀个存储过程实现加法运算: Java语法中，⽅法是有参数和返回值的
-- 存储过程中，是有输⼊参数 和 输出参数的
create procedure proc_test1(IN a int,IN b int,OUT c int)
begin
SET c = a+b;
end;

3.调用存储过程

-- 调⽤存储过程
-- 定义变量@m
set @m = 0;
-- 调⽤存储过程，将3传递给a，将2传递给b，将@m传递给c
call proc_test1(3,2,@m);
-- 显示变量值
select @m from dual;

4. 存储过程中变量的使用

存储过程中变量分为两种：局部变量和用户变量

4.1 定义局部变量

局部变量：定义在存储过程中的变量，只能在存储过程内部使⽤
局部变量定义语法

-- 局部变量要定义在存储过程中，⽽且必须定义在存储过程开始
declare <attr_name> <type> [default value];

局部变量定义示例：

create procedure proc_test2(IN a int,OUT r int)
begin
declare x int default 0; -- 定义x int类型，默认值为0
declare y int default 1; -- 定义y
set x = a*a;
set y = a/2;
set r = x+y;
end;

4.2 定义用户变量

⽤户变量：相当于全局变量，定义的⽤户变量可以通过 select @attrName from dual 进⾏
查询

-- ⽤户变量会存储在mysql数据库的数据字典中（dual）
-- ⽤户变量定义使⽤set关键字直接定义，变量名要以@开头
set @n=1;

4.3 给变量设置值

无论是局部变量还是用户变量，都是使用set 关键字修改值。

set @n=1;
call proc_test2(6,@n);
select @n from dual;

4.4 将查询结果赋值给变量

存储过程中使用select…into… 给变量赋值

-- 查询学⽣数量
create procedure proc_test3(OUT c int)
begin
select count(stu_num) INTO c from students; -- 将查询到学⽣数量赋值给c
end;
-- 调⽤存储过程
call proc_test3(@n);
select @n from dual;

4.5 用户变量使用注意事项

因为⽤户变量相当于全局变量，可以在SQL指令以及多个存储过程中共享，在开发中建
议尽量少使⽤⽤户变量，⽤户变量过多会导致程序不易理解、难以维护。

5.存储过程的参数

MySQL 存储过程的参数一共有 三种：IN/OUT/INOUT

5.1 IN 输入参数

输⼊参数——在调⽤存储过程中传递数据给存储过程的参数（在调⽤的过程必须为具有实际
值的变量 或者 字⾯值）

-- 创建存储过程：添加学⽣信息
create procedure proc_test4(IN snum char(8),IN sname varchar(20), IN
gender char(2), IN age int, IN cid int, IN remark varchar(255))
begin
insert into students(stu_num,stu_name,stu_gender,stu_age,cid,remark)
values(snum,sname,gender,age,cid,remark);
end;
call proc_test4('20210108','⼩丽','⼥',20,1,'aaa');

5.2 OUT 输出参数

create procedure proc_test5(IN snum char(8),OUT sname varchar(20))
begin
select stu_name INTO sname from students where stu_num=snum;
end;
set @name='';
call proc_test5('20210108',@name);
select @name from dual;

5.3 INOUT 输入输出参数

create procedure proc_test6(INOUT str varchar(20))
begin
select stu_name INTO str from students where stu_num=str;
end;
set @name='20210108';
call proc_test6(@name);
select @name from dual;

6.存储过程中流程控制

在存储过程中⽀持流程控制语句⽤于实现逻辑的控制

6.1 分支语句

1.if-then-else

-- 单分⽀：如果条件成⽴，则执⾏SQL
if conditions then
-- SQL
end if;
-- 如果参数a的值为1，则添加⼀条班级信息
create procedure proc_test7(IN a int)
begin
if a=1 then
insert into classes(class_name,remark) values('Java2109','test');
end if;
end;
-- 双分⽀：如果条件成⽴则执⾏SQL1，否则执⾏SQL2
if conditions then
-- SQL1
else
-- SQL2
end if;
-- 如果参数a的值为1，则添加⼀条班级信息；否则添加⼀条学⽣信息
create procedure proc_test7(IN a int)；
begin
if a=1 then
insert into classes(class_name,remark) values('Java2109','test');
else
insert into
students(stu_num,stu_name,stu_gender,stu_age,cid,remark)
values('20210110','⼩花','⼥',19,1,'...');
end if;
end;

2.case

-- case
create procedure proc_test8(IN a int)
begin
case a
when 1 then
-- SQL1 如果a的值为1 则执⾏SQL1
insert into classes(class_name,remark) values('Java2110','wahaha');
when 2 then
-- SQL2 如果a的值为2 则执⾏SQL2
insert into
students(stu_num,stu_name,stu_gender,stu_age,cid,remark)
values('20210111','⼩刚','男',21,2,'...');
else
-- SQL (如果变量的值和所有when的值都不匹配，则执⾏else中的这个SQL)
update students set stu_age=18 where stu_num='20210110';
end case;
end;

6.2 循环语句

6.2.1 while

-- while
create procedure proc_test9(IN num int)
begin
declare i int;
set i = 0;
while i<num do
-- SQL
insert into classes(class_name,remark) values( CONCAT('Java',i)
,'....');
set i = i+1;
end while;
end;
call proc_test9(4);

6.2.2 repeat

create procedure proc_test7(IN num int)
begin
	declare i int;
	set i=1;
	repeat
		insert into classes(class_name,class_remark)  values(concat('python',i),'...');
		set i=i+1;
	until i>num end repeat;
end;
call proc_test7(4);

6.2.3 loop

-- loop
create procedure proc_test11(IN num int)
begin
declare i int ;
set i =0;
myloop:loop
-- SQL
insert into classes(class_name,remark) values( CONCAT('HTML',i)
,'....');
set i = i+1;
if i=num then
leave myloop;
end if;
end loop;
end;
call proc_test11(5);

7.存储过程管理

7.1 查询存储过程

存储过程是属于某个数据库的，也就是说当我们将存储过程创建在某个数据库之后，只
能在当前数据库中调⽤此存储过程。
查询存储过程：查询某个数据库中有哪些存储过程

-- 根据数据库名，查询当前数据库中的存储过程
show procedure status where db='db_test2';
-- 查询存储过程的创建细节
show create procedure db_test2.proc_test1;

7.2 修改存储过程

修改存储过程指的是修改存储过程的特征/特性

alter procedure <proc_name> 特征1 [特征2 特征3 ....]

存储过程的特征参数：
1.CONTAINS SQL 表示⼦程序包含 SQL 语句，但不包含读或写数据的语句
2.NO SQL 表示⼦程序中不包含 SQL 语句
3.READS SQL DATA 表示⼦程序中包含读数据的语句
4.MODIFIES SQL DATA 表示⼦程序中包含写数据的语句
5.SQL SECURITY { DEFINER |INVOKER } 指明谁有权限来执⾏
5.1 DEFINER 表示只有定义者⾃⼰才能够执⾏
5.2 INVOKER 表示调⽤者可以执⾏
6. COMMENT ‘string’ 表示注释信息

alter procedure proc_test1 READS SQL DATA;

7.3 删除存储过程

-- 删除存储过程
-- drop 删除数据库中的对象 数据库、数据表、列、存储过程、视图、触发器、索引....
-- delete 删除数据表中的数据
drop procedure proc_test1;

8.存储过程练习案例

使⽤存储过程解决企业项⽬开发过程中的问题
案例：使⽤存储过程完成借书操作

8.1 数据准备

-- 创建数据库
create database db_test3;
-- 使⽤数据库
use db_test3;
-- 创建图书信息表：
create table books(
	book_id int primary key auto_increment,
		book_name varchar(50) not null,
		book_author varchar(20) not null,
		book_price decimal(10,2) not null,
		book_stock int not null,
		book_desc varchar(200)
);
-- 添加图书信息
insert into books(book_name,book_author,book_price,book_stock,book_desc)
values('Java程序设计','亮亮',38.80,12,'亮亮老师带你学Java');
insert into books(book_name,book_author,book_price,book_stock,book_desc)
values('Java王者之路','威哥',44.40,9,'千锋威哥， Java王者领路人');
-- 创建学生信息表
create table students(
stu_num char(4) primary key,
stu_name varchar(20) not null,
stu_gender char(2) not null,
stu_age int not null
);
-- 添加学⽣信息
insert into students(stu_num,stu_name,stu_gender,stu_age)
values('1001','张三','男',20);
insert into students(stu_num,stu_name,stu_gender,stu_age)
values('1002','李四','⼥',20);
insert into students(stu_num,stu_name,stu_gender,stu_age)
values('1003','王五','男',20);

8.1.1 业务分析

哪个学⽣借哪本书，借了多少本？
操作：
保存借书记录
修改图书库存
条件：
判断学⽣是否存在？
判断图书是否存在、库存是否充⾜？

8.1.2 创建借书记录表

-- 借书记录表：
create table records(
rid int primary key auto_increment,
snum char(4) not null,
bid int not null,
borrow_num int not null,
is_return int not null, -- 0表示为归还 1 表示已经归还
borrow_date date not null,
constraint FK_RECORDS_STUDENTS foreign key(snum) references
students(stu_num),
constraint FK_RECORDS_BOOKS foreign key(bid) REFERENCES
books(book_id)
);

8.2 创建储存过程实现借书业务

-- 实现借书业务：
-- 参数1： a 输⼊参数 学号
-- 参数2： b 输⼊参数 图书编号
-- 参数3： m 输⼊参数 借书的数量
-- 参数4： state 输出参数 借书的状态（1 借书成功， 2 学号不存在， 3 图书不存在， 4
库存不⾜）
create procedure proc_borrow_book(IN a char(4),IN b int, IN m int,OUT
state int)
begin
declare stu_count int default 0;
declare book_count int default 0;
declare stock int default 0;
-- 判断学号是否存在：根据参数 a 到学⽣信息表查询是否有stu_num=a的记录
select count(stu_num) INTO stu_count from students where stu_num=a;
if stu_count>0 then
-- 学号存在
-- 判断图书ID是否存在：根据参数b 查询图书记录总数
select count(book_id) INTO book_count from books where book_id=b;
if book_count >0 then
-- 图书存在
-- 判断图书库存是否充⾜：查询当前图书库存，然后和参数m进⾏⽐较
select book_stock INTO stock from books where book_id=b;
if stock >= m then
-- 执⾏借书
-- 操作1：在借书记录表中添加记录
insert into
records(snum,bid,borrow_num,is_return,borrow_date)
values(a,b,m,0,sysdate());
-- 操作2：修改图书库存
update books set book_stock=stock-m where book_id=b;
-- 借书成功
set state=1;
else
-- 库存不⾜
set state=4;
end if;
else
-- 图书不存在
set state = 3;
end if;
else
-- 不存在
set state = 2;
end if;
end;
-- 调⽤存储过程借书
set @state=0;
call proc_borrow_book('1001',1,2,@state);
select @state from dual;

8.3 创建存储过程实现还书业务

9.游标

问题：如果我们要创建⼀个存储 过程，需要返回查询语句查询到的多条数据，该如何实
现呢？

9.1 游标的概念

游标可以⽤来依次取出查询结果集中的每⼀条数据——逐条读取查询结果集中的记录

9.2 游标的使用步骤

9.2.1 声明游标

1. 声明游标语法

DECLARE cursor_nanme CURSOR FOR select_statement;

2. 实例

declare mycursor cursor for select book_name,book_author,book_price from
books;

9.2.2 打开游标

语法

open mycursor;

9.2.3 使用游标

使⽤游标：提取游标当前指向的记录（提取之后，游标⾃动下移）

FETCH mycursor INTO bname,bauthor,bprice;

9.2.4 关闭游标

CLOSE mycursor;

9.3 游标使用案例

-- 游标使⽤案例
create procedure proc_test2(OUT result varchar(200))
begin
declare bname varchar(20);
declare bauthor varchar(20);
declare bprice decimal(10,2);
declare num int;
declare i int;
declare str varchar(50);
-- 此查询语句执⾏之后返回的是⼀个结果集（多条记录），使⽤游标可以来遍历查询结果集
declare mycursor cursor for select book_name,book_author,book_price
from books;
select count(1) INTO num from books;
-- 打开游标
open mycursor;
-- 使⽤游标要结合循环语句
set i=0;
while i<num do
-- 使⽤游标：提取游标当前指向的记录（提取之后，游标⾃动下移）
FETCH mycursor INTO bname,bauthor,bprice;
set i=i+1;
-- set str=concat_ws('~',bname,bauthor,bprice);
select concat_ws('~',bname,bauthor,bprice) INTO str;
set result = concat_ws(',',result,str);
end while;
-- 关闭游标
close mycursor;
end;
-- 案例测试
set @r = '';
call proc_test2(@r);
select @r from dual;