所有知识体系文章，GitHub已收录，欢迎Star！再次感谢，愿你早日进入大厂！
GitHub地址： https://github.com/Ziphtracks/JavaLearningmanual
MySQL存储过程
一、存储过程
1.1 什么是存储过程
1.2 数据库存储过程程序
1.3 为什么要使用存储程序
这里我大致解释一下上述几种使用存储程序的优势：
我们要知道在Java语言中，我们使用数据库与Java代码结合持久化存储需要引入JDBC来完成。会想到JDBC，我们是否还能想起SQL注入问题呢？虽然使用PreparedStatement解决SQL注入问题，那就真的是绝对安全吗？不，它不是绝对安全的。
这时候分析一下数据库与Java代码的连接操作流程。在BS结构中，一般都是浏览器访问服务器的，再由服务器发送SQL语句到数据库，在数据库中对SQL语句进行编译运行，最后把结果通过服务器处理再返回浏览器。在此操作过程中，浏览器对服务器每发送一次对数据库操作的请求就会调用对应的SQL语句编译和执行，这是一件十分浪费性能的事情，性能下降 了就说明对数据库的操作效率低 了。
还有一种可能是，在这个过程中进行发送传输的SQL语句是对真实的库表进行操作的SQL语句，如果在发送传输的过程中被拦截了，一些不法分子会根据他所拦截的SQL语句推断出我们数据库中的库表结构，这是一个很大的安全隐患 。
关于可维护性的提高，这里模拟一个场景。通常数据库在公司中是由DBA来管理的，如果管理数据库多年的DBA辞职了，此时数据库会被下一任DBA来管理。这里时候问题来了，数据库中这么多的数据和SQL语句显然对下一任管理者不太友好。就算管理多年的DBA长时间不操作查看数据库也会忘记点什么东西。所以，我们在需要引入存储程序来进行SQL语句的统一编写和编译，为维护提供了便利 。（其实我觉得这个例子并不生动合理，但是为了大家能理解，请体谅！）
讲了很多存储程序的优势演变过程，其核心就是： 需要将编译好的一段或多段SQL语句放置在数据库端的存储程序中，以便解决以上问题并方便开发者直接调用。
二、存储过程的使用步骤
2.1 存储过程的开发思想
2.2 存储过程的优缺点
2.3 MySQL存储过程的官方文档
1https://dev.mysql.com/doc/refman/5.6/en/preface.html
2.3 存储过程的使用语法
1create PROCEDURE 过程名( in|out|inout 参数名 数据类型 , ...)
2begin
3 sql语句;
4end;
5call 过程名(参数值);
1-- 临时定义结束符为"//"
2DELIMITER //
3create PROCEDURE 过程名( in|out 参数名 数据类型 , ...)
4begin
5 sql语句;
6end//
7-- 将结束符重新定义回结束符为";"
8DELIMITER ;
例如： 使用存储过程来查询员工的工资（无参）
注意： 如果在特殊的必要情况下，我们还可以通过delimiter关键字将;结束符声明回来使用，在以下案例中我并没有这样将结束符声明回原来的;，在此请大家注意~
为什么我在这里提供了drop（删除）呢？
是因为我们在使用的时候如果需要修改存储过程中的内容，我们需要先删除现有的存储过程后，再creat重新创建。
1# 声明结束符为//
2delimiter //
3
4# 创建存储过程(函数)
5create procedure se()
6begin
7 select salary from employee;
8end //
9
10# 调用函数
11call se() //
12
13# 删除已存在存储过程——se()函数
14drop procedure if exists se //
三、存储过程的变量和赋值
3.1 局部变量
使用set为参数赋值
1# set赋值
2
3# 声明结束符为//
4delimiter //
5
6# 创建存储过程
7create procedure val_set()
8begin
9 # 声明一个默认值为unknown的val_name局部变量
10 declare val_name varchar(32) default 'unknown';
11 # 为局部变量赋值
12 set val_name = 'Centi';
13 # 查询局部变量
14 select val_name;
15end //
16
17# 调用函数
18call val_set() //
19
使用into接收参数
1delimiter //
2create procedure val_into()
3begin
4 # 定义两个变量存放name和age
5 declare val_name varchar(32) default 'unknown';
6 declare val_age int;
7 # 查询表中id为1的name和age并放在定义的两个变量中
8 select name,age into val_name,val_age from employee where id = 1;
9 # 查询两个变量
10 select val_name,val_age;
11end //
12
13call val_into() //
14
3.2 用户变量
1delimiter //
2create procedure val_user()
3begin
4 # 为用户变量赋值
5 set @val_name = 'Lacy';
6end //
7
8# 调用函数
9call val_user() //
10
11# 查询该用户变量
12select @val_name //
3.3 会话变量
1# 查看所有会话变量
2show session variables;
3# 查看指定的会话变量
4select @@session.val_name;
5# 修改指定的会话变量
6set @@session.val_name = 0;
这里我获取了一下所有的会话变量，大概有500条会话变量的记录。等我们深入学习MySQL后，了解了各个会话变量值的作用，可以根据需求和场景来修改会话变量值。
1delimiter //
2create procedure val_session()
3begin
4 # 查看会话变量
5 show session variables;
6end //
7
8call val_session() //
9
3.4 全局变量
1# 查看全局变量中变量名有char的记录
2show global variables like '%char%' //
3# 查看全局变量character_set_client的值
4select @@global.character_set_client //
3.5 入参出参
出参in
1delimiter //
2create procedure val_in(in val_name varchar(32))
3begin
4 # 使用用户变量出参(为用户变量赋参数值)
5 set @val_name1 = val_name;
6end //
7
8# 调用函数
9call val_in('DK') //
10
11# 查询该用户变量
12select @val_name1 //
入参out
1delimiter //
2# 创建一个入参和出参的存储过程
3create procedure val_out(in val_id int,out val_name varchar(32))
4begin
5 # 传入参数val_id查询员工返回name值(查询出的name值用出参接收并返回)
6 select name into val_name from employee where id = val_id;
7end //
8
9# 调用函数传入参数并声明传入一个用户变量
10call val_out(1, @n) //
11
12# 查询用户变量
13select @n //
入参出参inout
1delimiter //
2create procedure val_inout(in val_name varchar(32), inout val_age int)
3begin
4 # 声明一个a变量
5 declare a int;
6 # 将传入的参数赋值给a变量
7 set a = val_age;
8 # 通过name查询age并返回val_age
9 select age into val_age from employee where name = val_name;
10 # 将传入的a与-和查询age结果字符串做拼接并查询出来(concat——拼接字符串)
11 select concat(a, '-', val_age);
12end //
13
14# 声明一个用户变量并赋予参数为40
15set @ages = '40' //
16# 调用函数并传入参数值
17call val_inout('Ziph', @ages) //
18# 执行结果
19# 40-18
四、存储过程中的流程控制
4.1 if 条件判断（推荐）
语法：
1IF 条件判断 THEN 结果
2 [ELSEIF 条件判断 THEN 结果] ...
3 [ELSE 结果]
4END IF
举例： 传入所查询的id参数查询工资标准（s<=6000为低工资标准；6000
1delimiter //
2create procedure s_sql(in val_id int)
3begin
4 # 声明一个局部变量result存放工资标准结果
5 declare result varchar(32);
6 # 声明一个局部变量存放查询得到的工资
7 declare s double;
8 # 根据入参id查询工资
9 select salary into s from employee where id = val_id;
10 # if判断的使用
11 if s <= 6000 then
12 set result = '低工资标准';
13 elseif s <= 10000 then
14 set result = '中工资标准';
15 elseif s <= 15000 then
16 set result = '中上工资标准';
17 else
18 set result = '高工资标准';
19 end if;
20 # 查询工资标准结果
21 select result;
22end //
23
24# 调用函数，传入参数
25call s_sql(1);
4.2 case条件判断
语法：
1# 语法一
2CASE case_value
3 WHEN when_value THEN 结果
4 [WHEN when_value THEN 结果] ...
5 [ELSE 结果]
6END CASE
7
8# 语法二(推荐语法)
9CASE
10 WHEN 条件判断 THEN 结果
11 [WHEN 条件判断 THEN 结果] ...
12 [ELSE 结果]
13END CASE
举例：
1# 语法一
2delimiter //
3create procedure s_case(in val_id int)
4begin
5 # 声明一个局部变量result存放工资标准结果
6 declare result varchar(32);
7 # 声明一个局部变量存放查询得到的工资
8 declare s double;
9 # 根据入参id查询工资
10 select salary into s from employee where id = val_id;
11 case s
12 when 6000 then set result = '低工资标准';
13 when 10000 then set result = '中工资标准';
14 when 15000 then set result = '中上工资标准';
15 else set result = '高工资标准';
16 end case;
17 select result;
18end //
19
20call s_case(1);
21
22# 语法二(推荐)
23delimiter //
24create procedure s_case(in val_id int)
25begin
26 # 声明一个局部变量result存放工资标准结果
27 declare result varchar(32);
28 # 声明一个局部变量存放查询得到的工资
29 declare s double;
30 # 根据入参id查询工资
31 select salary into s from employee where id = val_id;
32 case
33 when s <= 6000 then set result = '低工资标准';
34 when s <= 10000 then set result = '中工资标准';
35 when s <= 15000 then set result = '中上工资标准';
36 else set result = '高工资标准';
37 end case;
38 select result;
39end //
40
41call s_case(1);
4.3 loop循环
语法：
1[别名:] LOOP
2 循环语句
3END LOOP [别名]
注意：别名和别名控制的是同一个标签。
示例1： 循环打印1~10（leave控制循环的退出）
注意：该loop循环为死循环，我们查的1~10数字是i，在死循环中设置了当大于等于10时停止循环，也就是说先后执行了10次该循环内的内容，结果查询了10次，生成了10个结果（1~10）。
1delimiter //
2create procedure s_loop()
3begin
4 # 声明计数器
5 declare i int default 1;
6 # 开始循环
7 num:
8 loop
9 # 查询计数器记录的值
10 select i;
11 # 判断大于等于停止计数
12 if i >= 10 then
13 leave num;
14 end if;
15 # 计数器自增1
16 set i = i + 1;
17 # 结束循环
18 end loop num;
19end //
20
21call s_loop();
打印结果：
示例2： 循环打印1~10（iterate和leave控制循环）
注意：这里我们使用字符串拼接计数器结果，而条件如果用iterate就必须时 i < 10 了！
1delimiter //
2create procedure s_loop1()
3begin
4 # 声明变量i计数器
5 declare i int default 1;
6 # 声明字符串容器
7 declare str varchar(256) default '1';
8 # 开始循环
9 num:
10 loop
11 # 计数器自增1
12 set i = i + 1;
13 # 字符串容器拼接计数器结果
14 set str = concat(str, '-', i);
15 # 计数器i如果小于10就继续执行
16 if i < 10 then
17 iterate num;
18 end if;
19 # 计数器i如果大于10就停止循环
20 leave num;
21 # 停止循环
22 end loop num;
23 # 查询字符串容器的拼接结果
24 select str;
25end //
26
27call s_loop1();
4.4 repeat循环
语法：
1[别名:] REPEAT
2 循环语句
3UNTIL 条件
4END REPEAT [别名]
示例： 循环打印1~10
1delimiter //
2create procedure s_repeat()
3begin
4 declare i int default 1;
5 declare str varchar(256) default '1';
6 # 开始repeat循环
7 num:
8 repeat
9 set i = i + 1;
10 set str = concat(str, '-', i);
11 # until 结束条件
12 # end repeat 结束num 结束repeat循环
13 until i >= 10 end repeat num;
14 # 查询字符串拼接结果
15 select str;
16end //
17
18call s_repeat();
4.5 while循环
语法：
1[别名] WHILE 条件 DO
2 循环语句
3END WHILE [别名]
示例： 循环打印1~10
1delimiter //
2create procedure s_while()
3begin
4 declare i int default 1;
5 declare str varchar(256) default '1';
6 # 开始while循环
7 num:
8 # 指定while循环结束条件
9 while i < 10 do
10 set i = i + 1;
11 set str = concat(str, '+', i);
12 # while循环结束
13 end while num;
14 # 查询while循环拼接字符串
15 select str;
16end //
17
18call s_while();
4.6 流程控制语句（继续、结束）
leave：与Java中break;相似
1leave 标签;
iterate：与Java中的continue;相似
1iterate 标签;
五、游标与handler
5.1 游标
语法：
1DECLARE 游标名 CURSOR FOR 查询语句
2-- 打开语法
3OPEN 游标名
4-- 取值语法
5FETCH 游标名 INTO var_name [, var_name] ...
6-- 关闭语法
7CLOSE 游标名
了解了游标的语法，我们开始使用游标。如下：
示例： 使用游标查询id、name和salary。
1delimiter //
2create procedure f()
3begin
4 declare val_id int;
5 declare val_name varchar(32);
6 declare val_salary double;
7
8 # 声明游标
9 declare emp_flag cursor for
10 select id, name, salary from employee;
11
12 # 打开
13 open emp_flag;
14
15 # 取值
16 fetch emp_flag into val_id, val_name, val_salary;
17
18 # 关闭
19 close emp_flag;
20
21 select val_id, val_name, val_salary;
22end //
23
24call f();
执行结果：
因为游标逐行操作的特点，导致我们只能使用游标来查询一行记录。怎么改善代码才可以实现查询所有记录呢？聪明的小伙伴想到了使用循环。对，我们试试使用一下循环。
1delimiter //
2create procedure f()
3begin
4 declare val_id int;
5 declare val_name varchar(32);
6 declare val_salary double;
7
8 # 声明游标
9 declare emp_flag cursor for
10 select id, name, salary from employee;
11
12 # 打开
13 open emp_flag;
14
15 # 使用循环取值
16 c:loop
17 # 取值
18 fetch emp_flag into val_id, val_name, val_salary;
19 end loop;
20
21 # 关闭
22 close emp_flag;
23
24 select val_id, val_name, val_salary;
25end //
26
27call f();
我们使用循环之后，发现有一个问题，因为循环是死循环，我们不加结束循环的条件，游标会一直查询记录，当查到没有的记录的时候，就会抛出异常1329：未获取到选择处理的行数。
如果我们想办法指定结束循环的条件该怎么做呢？
这时候可以声明一个boolean类型的标记。如果为true时则查询结果集，为false时则结束循环。
1delimiter //
2create procedure f()
3begin
4 declare val_id int;
5 declare val_name varchar(32);
6 declare val_salary double;
7
8 # 声明flag标记
9 declare flag boolean default true;
10
11 # 声明游标
12 declare emp_flag cursor for
13 select id, name, salary from employee;
14
15 # 打开
16 open emp_flag;
17
18 # 使用循环取值
19 c:loop
20 fetch emp_flag into val_id, val_name, val_salary;
21 # 如果标记为true则查询结果集
22 if flag then
23 select val_id, val_name, val_salary;
24 # 如果标记为false则证明结果集查询完毕，停止死循环
25 else
26 leave c;
27 end if;
28 end loop;
29
30 # 关闭
31 close emp_flag;
32
33 select val_id, val_name, val_salary;
34end //
35
36call f();
上述代码你会发现并没有写完，它留下了一个很严肃的问题。当flag = false时候可以结束循环。但是什么时候才让flag为false啊？
于是，MySQL为我们提供了一个handler句柄。它可以帮我们解决此疑惑。
handler句柄可以用来捕获异常，也就是说在这个场景中当捕获到1329：未获取到选择处理的行数时，就将flag标记的值改为false。这样使用handler句柄就解决了结束循环的难题。让我们来试试吧！
终极版示例： 解决了多行查询以及结束循环问题。
1delimiter //
2create procedure f()
3begin
4 declare val_id int;
5 declare val_name varchar(32);
6 declare val_salary double;
7
8 # 声明flag标记
9 declare flag boolean default true;
10
11 # 声明游标
12 declare emp_flag cursor for
13 select id, name, salary from employee;
14
15 # 使用handler句柄来解决结束循环问题
16 declare continue handler for 1329 set flag = false;
17
18 # 打开
19 open emp_flag;
20
21 # 使用循环取值
22 c:loop
23 fetch emp_flag into val_id, val_name, val_salary;
24 # 如果标记为true则查询结果集
25 if flag then
26 select val_id, val_name, val_salary;
27 # 如果标记为false则证明结果集查询完毕，停止死循环
28 else
29 leave c;
30 end if;
31 end loop;
32
33 # 关闭
34 close emp_flag;
35
36 select val_id, val_name, val_salary;
37end //
38
39call f();
执行结果：
在执行结果中，可以看出查询结果以多次查询的形式，分布显示到了每一个查询结果窗口中。
5.2 handler句柄
语法：
1DECLARE handler操作 HANDLER
2 FOR 情况列表...(比如：异常错误情况)
3 操作语句
注意：异常情况可以写异常错误码、异常别名或SQLSTATE码。
handler操作：
异常情况列表：
注意： MySQL中各种异常情况代码、错误码、别名和SQLSTATEM码可参考官方文档：
https://dev.mysql.com/doc/refman/5.6/en/server-error-reference.html
写法示例：
1 DECLARE exit HANDLER FOR SQLSTATE '3D000' set flag = false;
2 DECLARE continue HANDLER FOR 1050 set flag = false;
3 DECLARE continue HANDLER FOR not found set flag = false;
六、循环创建表
预编译： PREPARE 数据库对象名 FROM 参数名
执行： EXECUTE 数据库对象名 [USING @var_name [, @var_name] ...]
通过数据库对象创建或删除表： {DEALLOCATE | DROP} PREPARE 数据库对象名
关于时间处理的语句：
1-- EXTRACT(unit FROM date) 截取时间的指定位置值
2-- DATE_ADD(date,INTERVAL expr unit) 日期运算
3-- LAST_DAY(date) 获取日期的最后一天
4-- YEAR(date) 返回日期中的年
5-- MONTH(date) 返回日期的月
6-- DAYOFMONTH(date) 返回日
代码：
1-- 思路：循环构建表名 comp_2020_06_01 到 comp_2020_06_30；并执行create语句。
2delimiter //
3create procedure sp_create_table()
4begin
5 # 声明需要拼接表名的下一个月的年、月、日
6 declare next_year int;
7 declare next_month int;
8 declare next_month_day int;
9
10 # 声明下一个月的月和日的字符串
11 declare next_month_str char(2);
12 declare next_month_day_str char(2);
13
14 # 声明需要处理每天的表名
15 declare table_name_str char(10);
16
17 # 声明需要拼接的1
18 declare t_index int default 1;
19 # declare create_table_sql varchar(200);
20
21 # 获取下个月的年份
22 set next_year = year(date_add(now(),INTERVAL 1 month));
23 # 获取下个月是几月
24 set next_month = month(date_add(now(),INTERVAL 1 month));
25 # 下个月最后一天是几号
26 set next_month_day = dayofmonth(LAST_DAY(date_add(now(),INTERVAL 1 month)));
27
28 # 如果下一个月月份小于10，就在月份的前面拼接一个0
29 if next_month < 10
30 then set next_month_str = concat('0',next_month);
31 else
32 # 如果月份大于10，不做任何操作
33 set next_month_str = concat('',next_month);
34 end if;
35
36 # 循环操作(下个月的日大于等于1循环开始循环)
37 while t_index <= next_month_day do
38
39 # 如果t_index小于10就在前面拼接0
40 if (t_index < 10)
41 then set next_month_day_str = concat('0',t_index);
42 else
43 # 如果t_index大于10不做任何操作
44 set next_month_day_str = concat('',t_index);
45 end if;
46
47 # 拼接标命字符串
48 set table_name_str = concat(next_year,'_',next_month_str,'_',next_month_day_str);
49 # 拼接create sql语句
50 set @create_table_sql = concat(
51 'create table comp_',
52 table_name_str,
53 '(`grade` INT(11) NULL,`losal` INT(11) NULL,`hisal` INT(11) NULL) COLLATE=\'utf8_general_ci\' ENGINE=InnoDB');
54 # 预编译
55 # 注意：FROM后面不能使用局部变量！
56 prepare create_table_stmt FROM @create_table_sql;
57 # 执行
58 execute create_table_stmt;
59 # 创建表
60 DEALLOCATE prepare create_table_stmt;
61
62 # t_index自增1
63 set t_index = t_index + 1;
64
65 end while;
66end//
67
68# 调用函数
69call sp_create_table()
七、其他
7.1 characteristic
7.2 死循环处理
1show processlist;
2kill id;
7.3 select语句中书写case
1select
2 case
3 when 条件判断 then 结果
4 when 条件判断 then 结果
5 else 结果
6 end 别名,
7 *
8from 表名;
7.4 复制表和数据
1CREATE TABLE dept SELECT * FROM procedure_demo.dept;
2CREATE TABLE emp SELECT * FROM procedure_demo.emp;
3CREATE TABLE salgrade SELECT * FROM procedure_demo.salgrade;
7.5 临时表
1create temporary table 表名(
2 字段名 类型 [约束],
3 name varchar(20)
4)Engine=InnoDB default charset utf8;
5
6-- 需求：按照部门名称查询员工，通过select查看员工的编号、姓名、薪资。（注意，此处仅仅演示游标用法）
7delimiter $$
8create procedure sp_create_table02(in dept_name varchar(32))
9begin
10 declare emp_no int;
11 declare emp_name varchar(32);
12 declare emp_sal decimal(7,2);
13 declare exit_flag int default 0;
14
15 declare emp_cursor cursor for
16 select e.empno,e.ename,e.sal
17 from emp e inner join dept d on e.deptno = d.deptno where d.dname = dept_name;
18
19 declare continue handler for not found set exit_flag = 1;
20
21 -- 创建临时表收集数据
22 CREATE temporary TABLE `temp_table_emp` (
23 `empno` INT(11) NOT NULL COMMENT '员工编号',
24 `ename` VARCHAR(32) NULL COMMENT '员工姓名' COLLATE 'utf8_general_ci',
25 `sal` DECIMAL(7,2) NOT NULL DEFAULT '0.00' COMMENT '薪资',
26 PRIMARY KEY (`empno`) USING BTREE
27 )
28 COLLATE='utf8_general_ci'
29 ENGINE=InnoDB;
30
31 open emp_cursor;
32
33 c_loop:loop
34 fetch emp_cursor into emp_no,emp_name,emp_sal;
35
36
37 if exit_flag != 1 then
38 insert into temp_table_emp values(emp_no,emp_name,emp_sal);
39 else
40 leave c_loop;
41 end if;
42
43 end loop c_loop;
44
45 select * from temp_table_emp;
46
47 select @sex_res; -- 仅仅是看一下会不会执行到
48 close emp_cursor;
49
50end$$
51
52call sp_create_table02('RESEARCH');