聊聊MySQL存储过程

一、存储过程

1.1 什么是存储过程？

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

1.2 数据库存储过程程序

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

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

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

1.3 为什么要使用存储程序？

虽然目前的开发中存储过程我们使用的并不是很多，但是不一定就否认它。其实存储程序会为我们使用和管理数据库带来了很多优势：

• 使用存储程序更加安全；
• 存储程序提供了一种数据访问的抽象机制，它能够极大的改善你的代码在底层数据结构演化过程中的易维护性；
• 存储程序可以降低网络拥阻，因为属于数据库服务器的内部数据，这相比在网上传输数据要快的多；
• 存储程序可以替多种使用不同架构的外围应用实现共享的访问例程，这样可以为程序员带来更高、更为独特的数据库编程体验；
• 在某些情况下，使用存储程序可以改善应用程序的可移植性

很多存储程序的优势演变过程，其核心就是：需要将编译好的一段或多段SQL语句放置在数据库端的存储程序中，以便解决以上问题并方便开发者直接调用

二、存储过程的使用步骤

2.1 存储过程的开发思想

存储过程时数据库的一个重要的对象，可以封装SQL语句集，可以用来完成一些较为复杂的业务逻辑，并且可以入参，出参，这里与Java中封装方式十分相似。
而且创建时会预先编译后保存，开发者后续的调用都不需要再次编译。

2.2 存储过程的优缺点

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

2.3 存储过程的使用语法

create procedure 过程名(in|out|inout 参数名 数据类型)
begin
	sql语句;
end;
call 过程名(参数值);
1
2
3
4
5

• in: 是定义传入参数的关键字；
• out: 是定义出参的关键字；
• inout: 是定义一个出入参数都可以的参数；
• 如果括号内什么都不定义，就说明该存储过程时一个无参的函数。
• 我们可以使用 delimiter 关键字临时声明修改SQL语句的结束符为//， 如下：

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

三、存储过程的变量和赋值

3.1 局部变量

声明局部变量语法：decclare var_name type [default var_value];
赋值语法：
注意：局部变量的定义，在begin/end块中有效。

• 使用set为参数赋值

# 声明结束符为//
delimiter //
# 创建存储过程
create procedure val_set()
begin
    # 声明一个默认值为unknown的val_name局部变量
    declare val_name varchar(32) default 'unknown';
    # 为局部变量赋值
    set val_name = 'Centi';
    # 查询局部变量
    select val_name;
end //
# 调用函数
call val_set();
1
2
3
4
5
6
7
8
9
10
11
12
13
14

• 使用into接收参数

delimiter //
create procedure val_into()
begin
    # 定义两个变量存放name和age
    declare val_name varchar(32) default 'unknow';
    declare val_age int;
    # 查询表中id为1的name和age并放在定义的两个变量中
    select id, description into val_age, val_name from myweb_dbbackup.dbbackup_job_exe_detail where id = 10;
    # 查询两个变量
    select val_name, val_age;
end //
call val_into();
1
2
3
4
5
6
7
8
9
10
11
12

3.2 用户变量

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

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

delimiter //
create procedure val_user()
begin
    # 为用户变量赋值
    set @val_name = 'Lacy';
end //
# 调用函数
call val_user();
# 查询该用户变量
select @val_name;
1
2
3
4
5
6
7
8
9
10

3.3 会话变量

会话变量是由系统提供的，只在当前会话（连接）中有效。
语法：@@session.val_name

# 查看所有会话变量
show session variables;
# 查看指定的会话变量
select @@session.val_name;
# 修改指定的会话变量
set @@session.val_name = 0;
1
2
3
4
5
6

3.4 全局变量

全局变量由系统提供，整个MySQL服务器内有效。
语法：@@global.val_name

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

3.5 入参出参

入参出参的语法我们在文章开头已经提过了，但是没有演示，在这里我将演示一下入参出参的使用。
语法：in|out|inout 参数名 数据类型

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

• 入参in

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

delimiter //
create procedure val_in(in val_name varchar(32))
begin
    # 使用用户变量出参（为用户变量赋参数值）
    set @val_name = val_name;
end //
# 调用函数
call val_in('DK');
# 查询该用户变量
select @val_name;
1
2
3
4
5
6
7
8
9
10

• 出参out

在使用out时，需要传入一个参数。而这个参数相当于是返回值，可以通过调用、接收来获取这个参数的内容。简单来说out只负责作返回值

delimiter //
# 创建一个入参和出参的存储过程
create procedure val_out(in val_id int, out val_name varchar(32))
begin
    # 传入参数val_id查询用户返回username值（查询出的username值用出参接收并返回）
    select username into val_name from myweb_dbbackup.dbbackup_admin_user where id = 1;
end //
# 调用函数传入参数并声明传入一个用户变量
call val_out(1, @n);
# 查询用户变量
select @n;
1
2
3
4
5
6
7
8
9
10
11

• 入参出参inout

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

delimiter //
create procedure val_inout(in val_name varchar(32), inout val_id int)
begin
    # 声明一个a变量
    declare a int;
    # 将传入的参数赋值给a变量
    set a = val_id;
    # 通过name查询age并返回val_age
    select id into val_id from myweb_dbbackup.dbbackup_admin_user where username = val_name;
    # 将传入的a与-和查询id结果字符串做拼接并查询出来（concat--拼接字符串）
    select concat(a, '-', val_id);
end //
# 声明一个用户变量并赋予参数为40
set @age = '40';
# 调用函数并传入参数值
call val_inout('fangruichuan', @age);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

四、存储过程的流程控制

4.1 if条件判断

# if条件判断
delimiter //
create procedure s_sql(in val_id int)
begin
    # 声明一个局部变量result存放工资标准结果
    declare result varchar(32);
    # 声明一个局部变量存放查询得到的工资
    declare s double;
    # 根据入参id查询金额
    select flowMoney into s from flow_of_funds where id = val_id;
    # if判断的使用
    if s <= 3000 then
        set result = '低工资';
    elseif s <= 10000 then
        set result = '中工资';
    elseif s <= 15000 then
        set result = '中上工资';
    else
        set result = '高工资';
    end if;
    # 查询工资
    select result;
end //
# 调用函数
call s_sql(6);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

4.2 case条件判断

delimiter //
create procedure s_case(in val_id int)
begin
    # 声明一个局部变量result存放金额标准
    declare result varchar(32);
    # 声明一个局部变量查询得到的金额
    declare s double;
    # 根据入参id查询金额
    select flowMoney into s from flow_of_funds where id = val_id;
    # case判断使用
    case
        when s <= 3000 then set result = '低工资';
        when s <= 10000 then set result = '中工资';
        when s <= 15000 then set result = '中上工资';
        else set result = '高工资';
    end case;
    select result;
end //
call s_case(6);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

4.3 loop循环

loop为死循环，需要手动退出循环，我们可以使用leave来退出循环
可以把leave看成java中的break；与之对应的，就有iterate（继续循环）也可以看成Java中的continue

• 循环打印1~10（leave控制循环的退出）

# 循环打印1~10（leave控制循环的退出）
delimiter //
create procedure s_loop()
begin
    # 声明计数器
    declare i int default 1;
    # 开始循环
    num:
    loop
        # 查询计数器记录的值
        select i;
        # 判断大于等于停止计数
        if i >= 10 then
            leave num;
        end if;
        # 计数器自增1
        set i = i + 1;
    # 结束循环
    end loop num;
end //
call s_loop();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

• 循环打印1~10（iterate和leave控制循环）

注意：这里我们使用字符串拼接计数器结果，而条件如果用iterate就必须是i < 10了

delimiter //
create procedure s_loop1()
begin
    # 声明变量i计数器
    declare i int default 1;
    # 声明字符串容器
    declare str varchar(256) default '1';
    # 开始循环
    num:
    loop
        # 计数器自增1
        set i = i + 1;
        # 字符串容器拼接计数器结果
        set str = concat(str, '-', i);
        # 计数器i如果<10就继续执行
        if i < 10 then
            iterate num;
        end if;
        # 计数器i如果大于10就停止循环
        leave num;
    end loop num;
    # 查询字符串容器的拼接结果
    select str;
end //
call s_loop1();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

4.4 repeat循环

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

# repeat循环
delimiter //
create procedure s_repeat()
begin
    declare i int default 1;
    declare str varchar(256) default '1';
    # 开始repeat循环
    num:
    repeat
        set i = i + 1;
        set str = concat(str, '-', i);
    # until结束条件
    # end repeat 结束num 结束repeat循环
    until i >= 10 end repeat num;
    # 查询字符串拼接结果
    select str;
end //
call s_repeat();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

4.5 while循环

delimiter //
create procedure s_while()
begin
    declare i int default 1;
    declare str varchar(256) default '1';
    # 开始while循环
    num:
    # 指定while循环结束条件
    while i < 10 do
        set i = i + 1;
        set str = concat(str, '+', i);
    # while循环结束
    end while num;
    # 查询while循环拼接字符串
    select str;
end //
call s_while();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

五、游标和handler

5.1 游标

游标是可以得到某一个结果集并逐行处理数据。游标的逐行操作，导致了游标很少被使用

delimiter //
create procedure f()
begin
    declare val_id int;
    declare val_name varchar(32);
    declare val_pwd varchar(255);

    # 声明游标
    declare user_flag cursor for
    select id, username, password from myweb_dbbackup.dbbackup_admin_user;

    # 打开
    open user_flag;

    # 取值
    fetch user_flag into val_id, val_name, val_pwd;

    # 关闭
    close user_flag;

    select val_id, val_name, val_pwd;
end //
call f();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

5.2 句柄

handler句柄语法：declare continue handler for 异常 set flag = false;

handler句柄可以用来捕获异常，就将flag标记的值改为false。这样使用handler句柄就解决了结束循环的难题。让我们来试试把！

上例游标改进

delimiter //
create procedure f3()
begin
    declare val_id int;
    declare val_username varchar(32);
    declare val_pwd varchar(255);
    # 声明标记
    declare flag boolean default true;
    # 声明游标
    declare user_flag cursor for
    select id, username, password from myweb_dbbackup.dbbackup_admin_user;
    # 使用handle句柄来解决结束循环问题
    declare continue handler for 1329 set flag = false;
    # 打开
    open user_flag;
    # 使用循环取值
    c:loop
        fetch user_flag into val_id, val_username, val_pwd;
        # 如果标记为true则查询结果集
        if flag then
            select val_id, val_username, val_pwd;
        # 如果标记为false则证明结果集查询完毕，停止死循环
        else
            leave c;
        end if;
    end loop;
    # 关闭
    close user_flag;
    select val_id, val_username, val_pwd;
end //
call f3();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

表中数据：

可以看到当表中没有数据了直接就停止循环了

六、其他

6.1 characteristic

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

• LANGUAGE SQL
  • 存储过程语言，默认是sql，说明存储过程中使用的是sql语言编写的，暂时只支持sql，后续可能会支持其他语言
• NOT DETERMINISTIC
  • 是否确定性的输入就是确定性的输出，默认是NOT DETERMINISTIC，只对于同样的输入，输出也是一样的，当前这个值还没有使用

…

6.2 临时表

delimiter //
create procedure sp_create_table02(in dept_name varchar(32))
begin
    declare emp_no int;
    declare emp_name varchar(32);
    declare emp_sal decimal(7, 2);
    declare exit_flag int default 0;

    declare emp_cursor cursor for
    select m.id, m.name, m.invesMoney from finance.change_money m;

    declare continue handler for not found set exit_flag = 1;

    -- 创建临时表收集数据
    create temporary table `temp_table_emp`(
        `emp_no` int(1) not null comment '员工编号',
        `ename` varchar(32) null comment '员工姓名',
        `sal` decimal(7, 2) not null default '0.00' comment '薪资',
        primary key (`emp_no`) using BTREE
    )ENGINE = INNODB default charset=utf8;

    open emp_cursor;

    c_loop:loop
        fetch emp_cursor into emp_no, emp_name, emp_sal;
        if exit_flag != 1 then
            insert into temp_table_emp values(emp_no, emp_name, emp_sal);
        else
            leave c_loop;
        end if;
    end loop c_loop;

    select * from temp_table_emp;

    # 仅仅是看一下会不会执行到
    select @sex_res;
    close emp_cursor;
end;

call sp_create_table02('RESEARCH');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40