MySQL存储过程

一、存储过程

1.1 什么是存储过程

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升

1.2 数据库存储过程程序

当我们了了解存储过程是什么之后，就需要了解数据库中存在的这三种类型的数据库存储类型程序，如下：

• 存储过程： 存储过程是最常见的存储程序，存储过程是能够接受输入和输出参数并且能够在请求时被执行的程序单元。
• 存储函数： 存储函数和存储过程很相像，但是它的执行结果会返回一个值。最重要的是存储函数可以被用来充当标准的 SQL 语句，允许程序员有效的扩展 SQL 语言的能力。
• 触发器： 触发器是用来响应激活或者触发数据库行为事件的存储程序。通常，触发器用来作为数据库操作语言的响应而被调用，触发器可以被用来作为数据校验和自动反向格式化。

注意： 其他的数据库提供了别的数据存储程序，包括包和类。目前MySQL不提供这种结构。

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 存储过程的开发思想

存储过程时数据库的一个重要的对象，可以封装SQL语句集，可以用来完成一些较复杂的业务逻辑，并且可以入参（传参）、出参（返回参数），这里与Java中封装方式十分相似。

而且创建时会预先编译后保存，开发者后续的调用都不需要再次编译。

2.2 存储过程的优缺点

存储过程使用的优缺点其实在1.3中的优势中说到了。这里我简单罗列一下存储过程的优点与缺点。

• 优点：
• 在生产环境下，可以通过直接修改存储过程的方式修改业务逻辑或bug，而不用重启服务器。
• 执行速度快，存储过程经过编译之后会比单独一条一条编译执行要快很多。
• 减少网络传输流量。
• 便于开发者或DBA使用和维护。
• 在相同数据库语法的情况下，改善了可移植性。
• 缺点：
• 过程化编程，复杂业务处理的维护成本高。
• 调试不便。
• 因为不同数据库语法不一致，不同数据库之间可移植性差。

2.3 MySQL存储过程的官方文档

英语好或者有能力的小伙伴可以去参考一下官方文档。如果不参考官方文档，没关系，我在下面也会详细讲述MySQL存储过程的各个知识点。

1https://dev.mysql.com/doc/refman/5.6/en/preface.html

代码100分

2.3 存储过程的使用语法

代码100分1create PROCEDURE 过程名( in|out|inout 参数名 数据类型 , ...)
2begin
3    sql语句;
4end;
5call 过程名(参数值);

in是定义传入参数的关键字。out是定义出参的关键字。inout是定义一个出入参数都可以的参数。如果括号内什么都不定义，就说明该存储过程时一个无参的函数。在后面会有详细的案例分析。

注意： SQL语句默认的结束符为;，所以在使用以上存储过程时，会报1064的语法错误。我们可以使用DELIMITER关键字临时声明修改SQL语句的结束符为//，如下：

1-- 临时定义结束符为"//"
2DELIMITER //
3create PROCEDURE 过程名( in|out 参数名 数据类型 , ...)
4begin
5    sql语句;
6end//
7-- 将结束符重新定义回结束符为";"
8DELIMITER ;

例如： 使用存储过程来查询员工的工资（无参）

注意： 如果在特殊的必要情况下，我们还可以通过delimiter关键字将;结束符声明回来使用，在以下案例中我并没有这样将结束符声明回原来的;，在此请大家注意~

为什么我在这里提供了drop（删除）呢？

是因为我们在使用的时候如果需要修改存储过程中的内容，我们需要先删除现有的存储过程后，再creat重新创建。

代码100分 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 局部变量

声明局部变量语法： declare var_name type [default var_value];

赋值语法：

注意： 局部变量的定义，在begin/end块中有效。

使用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 用户变量

用户自定义用户变量，当前会话（连接）有效。与Java中的成员变量相似。

• 语法： @val_name
• 注意： 该用户变量不需要提前声明，使用即为声明。

 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 会话变量

会话变量是由系统提供的，只在当前会话（连接）中有效。

语法： @@session.val_name

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 全局变量

全局变量由系统提供，整个MySQL服务器内有效。

语法： @@global.val_name

1# 查看全局变量中变量名有char的记录
2show global variables like "%char%" //
3# 查看全局变量character_set_client的值
4select @@global.character_set_client //

3.5 入参出参

入参出参的语法我们在文章开头已经提过了，但是没有演示，在这里我将演示一下入参出参的使用。

语法： in|out|inout 参数名 数据类型 , ...

in定义出参；out定义入参；inout定义出参和入参。

出参in

使用出参in时，就是需要我们传入参数，在这里可以对参入的参数加以改变。简单来说in只负责传入参数到存储过程中，类似Java中的形参。

 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

在使用out时，需要传入一个参数。而这个参数相当于是返回值，可以通过调用、接收来获取这个参数的内容。简单来说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

inout关键字，就是把in和out合并成了一个关键字使用。被关键字修饰的参数既可以出参也可以入参。

 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 条件判断（推荐）

扩展： timestampdiff(unit, exp1, exp2)为exp2 – exp1得到的差值，而单位是unit。（常用于日期）

扩展例子： select timestampdiff(year,’2020-6-6‘,now()) from emp e where id = 1;

解释扩展例子： 查询员工表中id为1员工的年龄，exp2就可以为该员工的出生年月日，并以年为单位计算。

语法：

1IF 条件判断 THEN 结果
2    [ELSEIF 条件判断 THEN 结果] ...
3    [ELSE 结果]
4END IF

举例： 传入所查询的id参数查询工资标准（s<=6000为低工资标准；6000

=15000为高工资标准）

 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条件判断

关于case语句，不仅仅在存储过程中可以使用，MySQL基础查询语句中也有用到过。相当于是Java中的switch语句。

语法：

 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循环

loop为死循环，需要手动退出循环，我们可以使用leave来退出循环

可以把leave看成Java中的break；与之对应的，就有iterate（继续循环）也可以看成Java的continue

语法：

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循环

repeat循环类似Java中的do while循环，直到条件不满足才会结束循环。

语法：

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循环

while循环就与Java中的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句柄语法： declare continue handler for 异常 set flag = false;

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();

执行结果：

在执行结果中，可以看出查询结果以多次查询的形式，分布显示到了每一个查询结果窗口中。

注意： 在语法中，变量声明、游标声明、handler声明是必须按照先后顺序书写的，否则创建存储过程出错。

5.2 handler句柄

语法：

1DECLARE handler操作 HANDLER
2    FOR 情况列表...(比如：异常错误情况)
3    操作语句

注意：异常情况可以写异常错误码、异常别名或SQLSTATE码。

handler操作：

• CONTINUE： 继续
• EXIT： 退出
• UNDO： 撤销

异常情况列表：

• mysql_error_code
• SQLSTATE [VALUE] sqlstate_value
• condition_name
• SQLWARNING
• NOT FOUND
• SQLEXCEPTION

注意： 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;

六、循环创建表

需求： 创建下个月的每天对应的表，创建的表格式为：comp_2020_06_01、comp_2020_06_02、...

描述： 我们需要用某个表记录很多数据，比如记录某某用户的搜索、购买行为（注意，此处是假设用数据库保存），当每天记录较多时，如果把所有数据都记录到一张表中太庞大，需要分表，我们的要求是，每天一张表，存当天的统计数据，就要求提前生产这些表——每月月底创建下一个月每天的表！

预编译： 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

在MySQL存储过程中，如果没有显示的定义characteristic，它会隐式的定义一系列特性的默认值来创建存储过程。

• LANGUAGE SQL

• 存储过程语言，默认是sql，说明存储过程中使用的是sql语言编写的，暂时只支持sql，后续可能会支持其他语言

• NOT DETERMINISTIC

• 是否确定性的输入就是确定性的输出，默认是NOT DETERMINISTIC，只对于同样的输入，输出也是一样的，当前这个值还没有使用

• CONTAINS SQL

• 提供子程序使用数据的内在信息，这些特征值目前提供给服务器，并没有根据这些特征值来约束过程实际使用数据的情况。有以下选择：

  • CONTAINS SQL表示子程序不包含读或者写数据的语句
  • NO SQL 表示子程序不包含sql
  • READS SQL DATA 表示子程序包含读数据的语句，但是不包含写数据的语句
  • MODIFIES SQL DATA 表示子程序包含写数据的语句。

• SQL SECURITY DEFINER

• MySQL存储过程是通过指定SQL SECURITY子句指定执行存储过程的实际用户。所以次值用来指定存储过程是使用创建者的许可来执行，还是执行者的许可来执行，默认值是DEFINER

  • DEFINER 创建者的身份来调用，对于当前用户来说：如果执行存储过程的权限，且创建者有访问表的权限，当前用户可以成功执行过程的调用的
  • INVOKER 调用者的身份来执行，对于当前用户来说：如果执行存储过程的权限，以当前身份去访问表，如果当前身份没有访问表的权限，即便是有执行过程的权限，仍然是无法成功执行过程的调用的。

• COMMENT “”

• 存储过程的注释性信息写在COMMENT里面，这里只能是单行文本，多行文本会被移除到回车换行等

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");