mysql - 存储过程 - 个人文章

介绍

MySQL从5.0版本开始支持存储过程和函数。存储过程和函数能够将复杂的 SQL 逻辑封装在一起，应用程序无须关注存储过程和函数内部复杂的 SQL 逻辑，而只需要简单地调用存储过程和函数即可。

一句话总结：存储过程是一组为了完成特定功能的 SQL 语句集合。

关键字：封装、重用

基本语法

基本语法包括存储过程的创建、调用、查看、与删除。

创建存储过程：

CREATE PROCEDURE 存储过程名称 ([ 参数列表 ])
BEGIN
    -- SQL语句
END ;

调用存储过程：

CALL 名称 ([ 参数 ]);

查看存储过程：

1. 查询某个存储过程的定义
SHOW CREATE PROCEDURE 存储过程名称;

2. 查询指定数据库的存储过程及状态信息
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx';

删除存储过程：

DROP PROCEDURE [ IF EXISTS ] 存储过程名称；

简单的例子

前面提到了，存储过程是为了完成特定任务的 SQL 集合，可以只有一条 SQL 语句，也可以有多条。即存储过程可以非常简单也可以非常复杂，下面就通过一个简单的例子来说明存储过程的基本使用。

创建一个查询用户表所有数据的存储过程：

CREATE PROCEDURE `p_user`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '查询用户表的所有数据'
BEGIN
    SELECT * FROM tb_user;
END

调用该存储过程：

CALL p_user();

结果：

查看该存储过程：

SHOW CREATE PROCEDURE p_user;

删除该存储过程

DROP PROCEDURE IF EXISTS p_user;

通过这个简单的例子，我们使用了一遍存储过程的基本语法，但是想要写出更为复杂的存储过程，还需要了解“变量”、“分支”、“循环”、“参数”、“游标”等概念。

更复杂的存储过程

除了“游标”，“变量”、“分支”、“循环”、“参数”等概念与其他后端语言的类似。

变量

在 MySQL 中，变量分为系统变量、用户定义变量和局部变量。因为存储过程中更多的是用局部变量，所以这里只记录局部变量的使用。

变量的声明、赋值与输出：

1. 声明
DECLARE 变量名 变量类型 [DEFAULT ... ] ;

2. 赋值
SET 变量名 := 值 ;    
SELECT 字段名 INTO 变量名 FROM 表名 ... ; # 将表中某个字段的值赋值给变量

3. 输出
SELECT 变量名;

例子，声明一个 name 变量，并将其赋值为申玉非：

CREATE PROCEDURE `p_var`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    DECLARE name VARCHAR(50);
    SET name := "申玉非";
    SELECT name;
END

判断

在 MySQL 中，用于判断的语句有两种：if 和 case。

if 判断：

IF 条件1 THEN
    .....
ELSEIF 条件2 THEN       -- 可选
    .....
ELSE                    -- 可选
    .....
END IF;

例子，根据分数判断分数段，例如 71 分就是良好：

CREATE DEFINER=`root`@`localhost` PROCEDURE `p_if`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '测试 if 语句'
BEGIN
    DECLARE score INT DEFAULT 89;   # 声明 score 变量并赋值为 89
    DECLARE result VARCHAR(50);     # 声明 result 变量，注意这里的数据类型要定义长度。

    if score >= 0 && score < 60 then
        SET result := '不及格';
    ELSEIF score >= 60 && score < 80 then
        SET result := '良好';
    ELSEIF score >= 80 && score < 100 then
        SET result := '优秀';
    ELSEIF score = 100 then
        SET result := '满分';
    ELSEIF score < 0 || score > 100 then
        SET result := '分数不合法';
    end if;

    SELECT result;
END

case 判断：

CASE case_value
    WHEN when_value1 THEN statement_list1
    [ WHEN when_value2 THEN statement_list2] ...
    [ ELSE statement_list ]
END CASE;

含义：

当 case_value 的值为 when_value1 时，执行 statement_list1；
当值为 when_value2 时，执行 statement_list2；
否则就执行 statement_list。

例子，根据分数判断分数段:

CREATE DEFINER=`root`@`localhost` PROCEDURE `p_case`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
    DECLARE score INT DEFAULT 89;
    DECLARE result VARCHAR(50);
    DECLARE my_score INT(11);
    SET my_score := FLOOR(score / 10);

    case 
    when my_score >= 0 && my_score < 6 then
        SET result := '不及格';
    when my_score = 6 then
        SET result := '及格';
    when my_score = 7 then
        SET result := '良好';
    when my_score = 8 then
        SET result := '优秀';
    when my_score = 9 then
        SET result := '非常优秀';
    when my_score = 10 then
        SET result := '满分';
    when my_score < 0 || my_score > 10 then
        SET result := '分数不合法，分数必须在 0~100 之间';
    END case;

    SELECT result;
END

参数

在 MySQL 中参数分为 3 类：

类型	描述
IN	该类参数作为输入，也就是需要调用时传入值
OUT	该类参数作为输出，也就是该参数可以作为返回值
INOUT	既可以作为输入参数，也可以作为输出参数

例子，传入两个数，返回两个数之和：

CREATE PROCEDURE `p_param`(
    IN `x` INT,     # 第一个传入的参数
    IN `y` INT,     # 第二个传入的参数
    OUT `s` INT     # 返回值
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '测试参数'
BEGIN
    SET s := x + y;
END

调用：

-- 定义用户变量 @result来接收返回的数据, 用户变量可以不用声明

CALL p_param(1, 2, @s);
SELECT @s AS s;

# 结果为 3；

循环

在 MySQL 中有三种循环：while、repeat、loop。

while

while 循环是有条件的循环控制语句。满足条件后，再执行循环体中的 SQL 语句，语法如下：

-- 先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
WHILE 条件 DO
    SQL 逻辑...
END WHILE;

例子，从 1 加到 10：

CREATE PROCEDURE `p_sum`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '从 1 加到 10'
BEGIN
    DECLARE n INT DEFAULT 1;
    DECLARE sum INT DEFAULT 0;
    while n <= 10 DO
        SET sum := sum + n;
        SET n := n + 1;
    END while;

    SELECT sum;
END

# 调用
CALL p_sum(); 
# 结果：55

repeat

repeat 是有条件的循环控制语句, 当满足 until 声明的条件的时候，则退出循环，语法如下：

-- 先执行一次逻辑，然后判定UNTIL条件是否满足，如果满足，则退出。如果不满足，则继续下一次循环
REPEAT
    SQL 逻辑...
    UNTIL 条件
END REPEAT;

和 do...while 类似，但又不同：

do...while 是满足条件则循环，否则退出；
repeat 是不满足条件则循环，否则就退出。

例子，从 1 加到 10：

CREATE PROCEDURE `p_sum`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '从 1 加到 10'
BEGIN
    DECLARE n INT DEFAULT 1;
    DECLARE sum INT DEFAULT 0;

    repeat
        SET sum := sum + n;
        SET n := n + 1;
        until n > 10
    END repeat;

    SELECT sum;
END

# 调用
CALL p_sum();
# 结果：55

注意： until语句后面没有“;”。

loop

loop 相对简单，是没有条件的循环。但是配合 label 却可以实现“终止循环” 和 “跳过循环”，语法如下：

[begin_label:] LOOP
    SQL 逻辑...
END LOOP [end_label];

终止循环：LEAVE label;
跳过循环：ITERATE label;

例子：求 10 以内（包括10）的偶数和：

CREATE PROCEDURE `p_sum`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '求 10 以内（包括 10）的偶数和'
BEGIN
    DECLARE n INT DEFAULT 1;
    DECLARE sum INT DEFAULT 0;

    s:loop                # s 是标签名
        if n > 10 then
            leave s;      # 终止循环
        END if;
    
        if n % 2 = 0 then
            SET sum := sum + n;
        END if;
        SET n := n + 1;
    END loop s;
    
    SELECT sum;
END

# 调用
p_sum();
# 结果：30

游标

前面提到，我们可以把数据表中某个字段的值赋值给变量：

SELECT 字段名 INTO 变量名 FROM 表名 ... ;

现在我们想把查询到的结果存到另一张表中，光靠变量是不够的，因为一个变量只能存储一个数据。我们就会想要是在 MySQL 中有数组就好了，我们可以把查出来的数据保存到数组中，然后循环添加数据。很可惜，在 MySQL 中没有数组，但是有一个类似的概念——游标。

游标（CURSOR）是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。

和把大象塞进冰箱一样简单，使用游标分为四步：

声明游标；
打开游标；
读取游标；
关闭游标。

声明游标：

DECLARE 游标名称 CURSOR FOR 查询语句;

打开游标：

OPEN 游标名称;

读取游标：

FETCH 游标名称 INTO 变量 [, 变量 ];

关闭游标：

CLOSE 游标名称;

要实现把查询出来的结果保存到另外的表，光有游标还不够。我们遍历数组的时候，什么时候停止遍历？遍历到数组的最后一个元素的时候。在大多数编程语言中，都有手段来统计数组的个数，把数组的个数作为终止循环的判断条件。但是在 MySQL 中，并没有这样的手段，也就意味着没有终止循环的条件，一旦越界，就会报错。

那这个问题是否是无解的呢？答案是否定的，在 MySQL 中，恰恰就是利用这个报错来终止循环，也就是利用 MySQL 中的“条件处理程序”来打上这个补丁。

条件处理程序（Handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。语法为如下：

DECLARE handler_action HANDLER FOR condition_value [, condition_value] ... statement ;

handler_action 的取值：
    CONTINUE: 继续执行当前程序
    EXIT: 终止执行当前程序

condition_value 的取值：
    SQLSTATE sqlstate_value: 状态码，如 02000
    SQLWARNING: 所有以01开头的SQLSTATE代码的简写
    NOT FOUND: 所有以02开头的SQLSTATE代码的简写
    SQLEXCEPTION: 所有没有被SQLWARNING 或 NOTFOUND捕获的SQLSTATE代码的简写

游标和条件处理程序都介绍完了，下面举一个把性别为女的用户保存到 tb_user_women 表中的例子：

CREATE DEFINER=`root`@`localhost` PROCEDURE `p_cursor`(
    IN `ugender` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    DECLARE uname VARCHAR(50);
    DECLARE uage INT(11);
    DECLARE u_cursor CURSOR FOR SELECT name,age FROM tb_user WHERE gender = ugender; 
    -- 声明条件处理程序 ： 当SQL语句执行抛出的状态码为02000时，将关闭游标u_cursor，并退出
    DECLARE exit handler FOR SQLSTATE   '02000' close u_cursor;

    -- 创建 tb_user_women 表
    DROP TABLE if EXISTS tb_user_women;
    CREATE TABLE if not exists tb_user_women(
        id INT(11) PRIMARY KEY  AUTO_INCREMENT,
        name VARCHAR(50),
        age INT(11)
    ); 

    -- 打开游标
    OPEN u_cursor;

    -- 读取游标
    while TRUE DO
        fetch u_cursor INTO uname, uage;
        INSERT INTO tb_user_women(name,age) VALUES(uname,uage);
    END while;
    -- 关闭游标
    close u_cursor;
END

# 调用
CALL p_cursor(0);

结果：

注意：声明自定义变量要写在声明游标前面。

至于 p_cursor 存储过程中，为什么要把条件处理程序中的 SQLSTATE 的值设置为 02000，可以参考 MySQL 官方文档——错误状态码

优缺点

优点

存储过程可以一次编译多次使用；
存储过程的安全性强；
可以减少网络传输量。

缺点

可移植性差。存储过程不能跨数据库移植，比如在 MySQL、Oracle 和 SQL Server 里编写的存储过程，在换成其他数据库时都需要重新编写。
存储过程的版本管理很困难。比如数据表索引发生变化了，可能会导致存储过程失效。我们在开发软件的时候往往需要进行版本管理，但是存储过程本身没有版本控制，版本迭代更新的时候很麻烦。
它不适合高并发的场景。高并发的场景需要减少数据库的压力，有时数据库会采用分库分表的方式，而且对可扩展性要求很高，在这种情况下，存储过程会变得难以维护，增加数据库的压力，显然就不适用了。

存储过程

介绍

基本语法

简单的例子

更复杂的存储过程

变量

判断

参数

循环

while

repeat

loop

游标

优缺点

优点

缺点

参考

Moonshadow2333

引用和评论

线段树学习记录

在Java程序中监听mysql的binlog

Mysql数据库sql优化案例

MySQL 回收表碎片实践教程

高效工程师的七个习惯

sqlmap注入语句学习，帮助你排查SQL注入漏洞

业务系统大事务导致mysql死锁问题的排查与解决