一 前言
本篇内容是关于mysql储存过程的知识，学习本篇的基础是知识追寻者之前发过的文章（公众号读者看专辑）
《SQL-你真的了解什么SQL么？》
《SQL-小白最佳入门sql查询一》
《SQL-小白最佳入门sql查询二》
《SQL- 多年开发人员都不懂的插入与更新删除操作注意点》
《SQL-SQL事物操作》
《SQL-Mysql数据类型》
《SQL-mysql视图的前世今生》
公众号：知识追寻者
知识追寻者(Inheriting the spirit of open source, Spreading technology knowledge;)
二 储存过程
2.1 储存过程的概念
我们经常使用的SQL查询语句都是单条语句，如果要使用多条语句达到一个目的就显得力不从心了，储存过程就是使用多条语句完成业务的操作，你可以理解为linux脚本编程类似，window的批处理文件那样；简单的定义储存过程就是多条SQL的集合；
我们使用储存过程能够简化复杂的单条SQL，相比于单条复杂的SQL极大提高了性能；如果表结构发生变化只需要改变储存过程使用到SQL语句的表名，如果业务逻辑发生变化，只需要跳转储存过程即可，具有很强的灵活性；建立一次储存过程，即可使用，不用反复建立，保证开发人员使用到都是相同的储存过程，保证数据可靠性；总之使用储存过程，简单，灵活，安全可靠，性能好；
2.2 存储过程语法
创建存储过程
Create PROCEDURE 储存过程名称 （参数列表）
begin
过程体
end;
1
2
3
4
?
参数列表
IN 表示输入; 示例 IN var1 Decimal(6,2)
OUT 表示输出;示例 IN var2 Decimal(6,2)
INOUT 表示输入输出；示例 IN var3 Decimal(6,2)
1
2
3
4
5
变量
declare 变量名称 变量类型 [default value]
1
执行存储过程
call 储存过程名称
1
删除储存过程
DROP PROCEDURE 储存过程名称
1
赋值
使用 set 和 select into 语句为变量赋值。
set @var := 20
select sum(price) into total from table_name
1
2
3
if 语句
f 条件 then
表达式
[elseif 条件 then
表达式]
...
[else
表达式]
end if;
1
2
3
4
5
6
7
8
case语句
CASE 值 WHEN 匹配值 THEN 结果
[WHEN 匹配值 THEN 结果]
......
[ELSE 结果]
END
1
2
3
4
5
while语句
[开始标签:]while 条件 do
循环体
[结尾标签]
end while ;
1
2
3
4
loop 语句
[开始标签:] loop
语句体
[结尾标签]
end loop;
1
2
3
4
iterate/ leave语句
其是通过标签可以实现；iterate 表示迭代， leave表示离开
1
repeat语句
repeat
--循环体
until 循环条件
end repeat;
1
2
3
4
tip : 如果你是用命令行学习，在写多行SQL的时候 使用 // 可以实现换行哟！
2.3 储存过程示例
我们准备表如下，一个订单明细表；
CREATE TABLE `oder_detail` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘主键‘,
`detail_name` varchar(255) DEFAULT NULL COMMENT ‘订单明细‘,
`price` decimal(10,2) DEFAULT NULL COMMENT ‘价格‘,
`oid` int(11) DEFAULT NULL COMMENT ‘订单id‘,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT=‘订单明细表‘;
1
2
3
4
5
6
7
准备数据如下
INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (1, ‘毛巾‘, 20.00, 1);
INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (2, ‘牙膏‘, 15.00, 1);
INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (3, ‘杯子‘, 5.00, 1);
INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (4, ‘毛巾‘, 15.00, 2);
INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (5, ‘杯子‘, 15.00, 2);
1
2
3
4
5
6
无参储存过程
查看订单明细的所有订单名称,示例如下，跟普通的查询语句没区别；
create procedure slelect_detail()
begin
select detail_name from oder_detail;
end;
1
2
3
4
我们再调用储存过程
call slelect_detail();
1
此时就会打印内容如下
毛巾
牙膏
杯子
毛巾
杯子
1
2
3
4
5
删除储存过程
drop procedure slelect_detail;
1
带入参储存过程示例
现在我们需要查询oid为动态的所有订单明细名称，由于考虑到oid为动态，就是需要用户自己输入，故将oid作为入参;
create procedure slelect_detail(IN order_id INT)
begin
select detail_name from oder_detail where oid = order_id;
end;
1
2
3
4
调用储存过程，只查询oid为1的用户的订单明细名称
call slelect_detail(1);
1
打印内容
毛巾
牙膏
杯子
1
2
3
删除储存过程
drop procedure slelect_detail;
1
带入参和出参的存储过程示例
查询任意用户的订单明细的所有金额；定义入参订单id 为 order_id ， 输出总金额为 total；
create procedure slelect_toatal_money(IN order_id INT, OUT total DECIMAL(8,2))
begin
select sum(price) into total from oder_detail where oid = order_id;
end;
1
2
3
4
调用储存过程示例
call slelect_toatal_money(1,@total);
1
查询 order_id 为1 总金额示例
SELECT @total;
1
输出为 40；
删除储存过程
drop procedure slelect_toatal_money;
1
2.4 if 语句示例
上节的储存过程都是单条SQL，这次我们开始使用控制流程，实现复杂的储存过程；
知识追寻者对输入的 order_id 自动加5 ，然后判断 var 是否 小于7 ，如果是就查询订单明细价格，否则查询订单明细价格总和；
create procedure slelect_toatal_money(IN order_id INT)
begin
-- 定义变量
declare var int;
-- 赋值
set var= order_id+5;
-- if 判断
if var<7 then
select price from oder_detail where oid = order_id;
else
select sum(price) from oder_detail where oid = order_id;
end if;
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
调用
call slelect_toatal_money(1);
1
输出
price
20
15
5
1
2
3
4
调用
call slelect_toatal_money(2);
1
输出
sum(price)
30
1
2
2.6 while 语句示例
对 变量 var 进行判断，如果 var <7 就执行 查询价格语句，并且var 进行自增；
create procedure slelect_toatal_money(IN order_id INT)
begin
-- 定义变量
declare var int;
-- 赋值
set var= order_id+5;
-- while
while var<7 do
select price from oder_detail where oid = order_id;
set var = var + 1;
end while;
end;
1
2
3
4
5
6
7
8
9
10
11
12
调用示例
call slelect_toatal_money(1);
1
输出
price
20
15
5
1
2
3
4
2.7 case语句示例
如下语句实现的效果与上面if语句实现效果一致；
create procedure slelect_toatal_money(IN order_id INT)
begin
-- 定义变量
declare var int;
-- 赋值
set var:= order_id;
-- case 判匹配
case var
when 1 then
select price from oder_detail where oid = order_id;
when 2 then
select sum(price) from oder_detail where oid = order_id;
end case;
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
调用示例
call slelect_toatal_money(2);
1
输出
sum(price)
30
1
2
将参数改为1试试结果
2.8 loop语句
如果 var 小于3 就 计算 价格 + var 的值；
create procedure slelect_toatal_money(IN order_id INT)
begin
-- 定义变量
declare var int;
-- 赋值
set var:= order_id;
-- loop
select_loop : loop
select price+var from oder_detail where oid = order_id;
set var = var +1;
-- 跳出循环
if var > 3 then
leave select_loop;
end if;
end loop;
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
调用示例
call slelect_toatal_money(1);
1
会输出三组结果
2.7 repeat
repeat 与 while 的不同之处就是 ，while 在 执行之前检查条件，其实执行之后检查条件；
create procedure slelect_toatal_money(IN order_id INT)
begin
-- 定义变量
declare var int;
-- 赋值
set var= order_id+5;
-- repeat循环
repeat
select price from oder_detail where oid = order_id;
set var = var + 1;
until var>7
end repeat;
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
调用示例
call slelect_toatal_money(1);
1
此时会输出2组相同结果；
price
20
15
5
1
2
3
4
tip: loop, while , repeat , iterate 都是循环，loop,while, repeat 功能几乎相同；iterate可以通过标签的形式调用 循环，与 leave 语句使用方式一样；