1.存储过程介绍
1.1 SQL指令执行过程
从SQL执⾏执⾏的流程中我们分析存在的问题:
- 如果我们需要重复多次执⾏相同的SQL, SQL执⾏都需要通过连接传递到MySQL,并且
需要经过编译和执⾏的步骤; - 如果我们需要连续执⾏多个SQL指令,并且第⼆个SQL指令需要使⽤第⼀个SQL指令执⾏
的结果作为参数;
1.2 存储过程的介绍
存储过程:
将能够完成特定功能的SQL指令进⾏封装(SQL指令集),编译之后存储在数据库服务
器上,并且为之取⼀个名字,客户端可以通过名字直接调⽤这个SQL指令集,获取执⾏
结果。
1.3 存储过程优缺点分析
存储过程优点:
- SQL指令⽆需客户端编写,通过⽹络传送,可以节省⽹络开销,同时避免SQL指令在⽹络
传输过程中被恶意篡改保证安全性; - 存储过程经过编译创建并保存在数据库中的,执⾏过程⽆需重复的进⾏编译操作,对SQL
指令的执⾏过程进⾏了性能提升; - 存储过程中多个SQL指令之间存在逻辑关系,⽀持流程控制语句(分⽀、循环),可以实
现更为复杂的业务;
存储过程的缺点:
- 存储过程是根据不同的数据库进⾏编译、创建并存储在数据库中;当我们需要切换到其
他的数据库产品时,需要重写编写针对于新数据库的存储过程; - 存储过程受限于数据库产品,如果需要⾼性能的优化会成为⼀个问题;
- 在互联⽹项⽬中,如果需要数据库的⾼(连接)并发访问,使⽤存储过程会增加数据库
的连接执⾏时间(因为我们将复杂的业务交给了数据库进⾏处理)
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 声明游标
- 声明游标语法
DECLARE cursor_nanme CURSOR FOR select_statement;
- 实例
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;