MySQL 学习笔记 1：存储过程

2023年7月10日 1087点热度 0人点赞 0条评论

存储过程可以看做是将一组 SQL打包执行，并返回最终的执行结果。

其优点是：因为存储过程中的 SQL 是同时一次执行，所以没有数据一致性的问题。其次，不需要由客户端多次传输 SQL 命令，而只需要告诉 MySQL 执行某某存储过程，所以性能上比执行同样内容的多条 SQL 是要更好的。最后，使用存储过程，即使某些表结构发生变化，只要存储过程返回的结果和接收参数不发生改变，相应的调用代码也无需改变。

本文介绍如何创建和使用 MySQL 的存储过程。

本文使用可视化数据库连接工具 SQLyog 连接数据库操作，该工具的下载安装可以通过这里。当然，这并非必须。使用其他客户端（比如命令行客户端）都可以完成文中的相关 SQL 执行。

本文用于测试的表结构如下：

CREATE TABLE `student` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `average_score` int NOT NULL,
  `level` enum('FRESH_MAN','JUNIOR','SENIOR','SOPHOMORE') NOT NULL,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_7pb8owoegbhhcrpopw4o1ykcr` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

填充的测试数据类似这样：

mysql> select * from student limit 10;
+-----+---------------+-----------+----------+
| id  | average_score | level     | name     |
+-----+---------------+-----------+----------+
| 190 |             1 | JUNIOR    | Tom      |
| 191 |            14 | FRESH_MAN | icexmoon |
| 192 |             2 | SOPHOMORE | BrusLee  |
| 193 |            28 | SOPHOMORE | Harry    |
| 194 |            44 | JUNIOR    | JackChen |
| 195 |             4 | SENIOR    | Jimmy    |
| 196 |            22 | FRESH_MAN | LiLei    |
| 197 |            22 | JUNIOR    | XiaoMing |
| 198 |            74 | FRESH_MAN | Adam     |
| 199 |            73 | FRESH_MAN | Alex     |
+-----+---------------+-----------+----------+

表结构创建和测试数据填充都由 JPA 和测试用例完成，如果需要使用类似的方式实现，可以参考完整示例代码。

创建

选定要添加存储过程的数据库，右击存储过程，点击创建存储过程。之后需要输入存储过程名称，并确认。

此时会自动打开一个新的查询窗口，内容大概如下：

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `jpa`.`showScoreAvg`()
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
    BEGIN

    END$$

DELIMITER ;

这里的内容实际上是 SQLyog 为我们提供的一个存储过程创建模版，需要我们填写实际的存储过程内容后运行才能真正创建存储过程。

DELIMITER $$语句可以改写 MySQL 的默认 SQL 结束标记（;）为指定字符，这是因为我们在存储过程中往往要编写多条 SQL，而这些 SQL 的结束标识都是;，而;本身又是 SQL 的结束标识，在存储过程创建语句中同样会发挥作用，这就会导致存储过程的创建语句半中被结束，必然会运行失败。

所以存储过程创建语句中通常都会用DELIMITER先指定一个不常见的终止符（比如$$）以能让存储过程中的 SQL 结束符;被正常包含，最后再使用DELIMITER ;语句将终止符替换为正常的;。

之后的CREATE PROCEDURE jpa.showScoreAvg() 表示这是为jpa这个库创建的名为showScoreAvg的存储过程。存储过程本身可以定义多个输入或输出参数，这里没有任何参数，所以参数列表()是空。

存储过程的内容被包含在BEGIN和END$$之间，比如：

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `jpa`.`showScoreAvg`()
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
    BEGIN
        SELECT AVG(average_score)
        FROM student;
    END$$

DELIMITER ;

执行上边的 SQL 后会显示：

1 queries executed, 1 success, 0 errors, 0 warnings

查询：CREATE PROCEDURE `jpa`.`showScoreAvg`() BEGIN select avg(average_score) from student; END

共 0 行受到影响

执行耗时   : 0.010 sec
传送时间   : 1.007 sec
总耗时      : 1.018 sec

这说明存储过程已经创建好了。

查看

也可以通过 SQL 查询数据库中已有的存储过程：

show procedure status;

这条语句会显示所有的存储过程，可以使用 LIKE 对结果进行过滤：

show procedure status like 'show%'

如果要查看存储过程的创建语句，可以使用以下 SQL：

show create procedure showScoreAvg;

执行

执行存储过程：

call showScoreAvg();

也可以带上库名：

call jpa.showScoreAvg();

在我们这个简单示例中，会显示学生总的平均分，但通常存储过程并不会直接输出任何内容，而是通过输入和输出参数与外部交换数据。

修改 & 删除

修改存储过程与创建类似，在菜单中找到要修改的存储过程，右键改变存储过程，会加载类似下面的内容：

DELIMITER $$

USE `jpa`$$

DROP PROCEDURE IF EXISTS `showScoreAvg`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `showScoreAvg`()
BEGIN
        select avg(average_score)
        from student;
    END$$

DELIMITER ;

就像这个模版中展示的，要修改存储过程必须先删除再重新创建，删除存储过程的语句是：

DROP PROCEDURE IF EXISTS `showScoreAvg`;

输出参数

在这里，我们的存储过程输出两个参数，分别是总的平均分和总分：

DELIMITER $$

USE `jpa`$$

DROP PROCEDURE IF EXISTS `showScoreAvg`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `showScoreAvg`(
    out var_avg_score int,
    out var_sum_score int
)
BEGIN
        select avg(average_score)
        from student
        into var_avg_score;
        select sum(average_score)
        from student
        into var_sum_score;
    END$$

DELIMITER ;

这里的out var_avg_score int表示存储过程有一个用于输出的参数var_avg_score，其类型是int。

在存储过程中，out表示存储过程用于向外输出的参数，int表示外部输入存储过程的参数。参数的可选类型与表字段的可选类型一致。

在存储过程中，使用into var_avg_score的方式将单个查询结果（也可以是常量或变量）输入到参数中，这样外部就可以通过参数获取结果。需要注意的是，只能输入单个查询结果，多条行或列数据是无法输入到参数中的。

执行 SQL 以更改存储过程。

执行带输出参数的存储过程：

CALL showScoreAvg(@var_avg_score, @var_sum_score);

并不会有任何输出显示，因为存储过程将结果输出到了参数。需要用查询语句获取结果：

select @var_avg_score,@var_sum_score;

输入参数

我们再看存储过程如何使用输入参数：

DELIMITER $$

USE `jpa`$$

DROP PROCEDURE IF EXISTS `showScoreAvg`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `showScoreAvg`(
    in var_min_id int,
    in var_max_id int,
    out var_avg_score int,
    out var_sum_score int
)
BEGIN
        select avg(average_score)
        from student
        where id >= var_min_id and id <= var_max_id
        into var_avg_score;
        select sum(average_score)
        from student
        where id >= var_min_id and id <= var_max_id
        into var_sum_score;
    END$$

DELIMITER ;

这里定义了两个输入参数var_min_id和var_max_id，并用这两个参数作为查询条件，只输出学生id在这两个参数之间的分数统计结果。

执行修改后的存储过程：

CALL showScoreAvg(145,160,@var_avg_score, @var_sum_score);
SELECT @var_avg_score,@var_sum_score;

可以修改showScoreAvg的前两个输入参数值，明显可以看到结果的变化。

控制流程

在存储过程中同样可以使用流程控制语句，比如：

DELIMITER $$

USE `jpa`$$

DROP PROCEDURE IF EXISTS `showScoreAvg`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `showScoreAvg`(
    in cheat bool,
    in var_min_id int,
    in var_max_id int,
    out var_avg_score int,
    out var_sum_score int
)
BEGIN
        declare student_num int;
        declare min_avg int default 60;
        
        select avg(average_score)
        from student
        where id >= var_min_id and id <= var_max_id
        into var_avg_score;
        select sum(average_score)
        from student
        where id >= var_min_id and id <= var_max_id
        into var_sum_score;
        select count(id)
        from student
        where id >= var_min_id and id <= var_max_id
        into student_num;
        
        if cheat and var_avg_score < min_avg then
            select min_avg into var_avg_score;
            select min_avg*student_num into var_sum_score;
        end if;
    END$$

DELIMITER ;

在这里，新增了一个输入参数cheat，这是一个布尔值，用于表示是否启用“作弊”模式，目的是“美化”输出的结果。这里我们设定一个平均分的下限，如果查询结果低于这个值，就直接使用下限作为输出的平均分，当然相应的总分也得用伪造后的平均分重新计算。

为实现这个目的，除了增加一个输入参数作为作弊“开关”，还在存储过程内部定义了两个变量：

        declare student_num int;
        declare min_avg int default 60;

declare用于定义存储过程中的变量，并且可以用default为其指定一个默认值。

这里的student_num用于记录查询的相关学生数目，以便最终伪造总分时使用。min_avg用于伪造平均分时的触发“门槛”，如果正常查询出的总平均分低于这个值，就进行伪造。

是否伪造平均分，由if...then...end if语句控制：

        if cheat and var_avg_score < min_avg then
            select min_avg into var_avg_score;
            select min_avg*student_num into var_sum_score;
        end if;

在上边这个示例中，如果cheat为true（非0值）且查询到的平均分小于min_avg变量中的值，就重新设置相关的输出变量中的值（使用select ... into ...语句）。

测试：

call showScoreAvg(1,145,160,@var_avg_score, @var_sum_score);
select @var_avg_score,@var_sum_score;

修改第一个参数为0，就会看到“非作弊”下的真实结果。

注释

最后，我们还需要为存储过程加上注释：

-- Name: showScoreAvg
-- Parameters:  cheat = 作弊模式 0关闭1启用
--      var_min_id = 最小的学生id
--      var_max_id = 最大的学生id
--      var_avg_score = 学生总平均分
--      var_sum_score = 学生总分

CREATE DEFINER=`root`@`localhost` PROCEDURE `showScoreAvg`(
    IN cheat BOOL,
    IN var_min_id INT,
    IN var_max_id INT,
    OUT var_avg_score INT,
    OUT var_sum_score INT
) COMMENT '计算id在某个区间的学生的总平均分、总分'
BEGIN
        -- 查询相关的学生数目
        DECLARE student_num INT;
        -- 执行作弊的最低均分
        DECLARE min_avg INT DEFAULT 60;
        
        -- 查询学生的总平均分
        SELECT AVG(average_score)
        FROM student
        WHERE id >= var_min_id AND id <= var_max_id
        INTO var_avg_score;
        -- 查询学生总分
        SELECT SUM(average_score)
        FROM student
        WHERE id >= var_min_id AND id <= var_max_id
        INTO var_sum_score;
        -- 查询相关学生总数
        SELECT COUNT(id)
        FROM student
        WHERE id >= var_min_id AND id <= var_max_id
        INTO student_num;
        
        -- 如果启用作弊且均分低于 min_avg
        IF cheat AND var_avg_score < min_avg THEN
            -- 使用 min_avg 作为输出的平均分
            SELECT min_avg INTO var_avg_score;
            -- 使用 min_avg*student_num 作为输出的总分
            SELECT min_avg*student_num INTO var_sum_score;
        END IF;
    END$$

DELIMITER ;

comment中的注释可以在show procedure status的输出结果中出现。

代码调用

代码如何调用存储过程取决于持久层框架，这里我使用 JPA（Hibernate）演示。

NamedStoredProcedure

首先，在实体类上用@NamedStoredProcedureQuery注解定义一个“命名的存储过程查询”：

@NamedStoredProcedureQuery(name = "showScoreAvg",
        procedureName = "showScoreAvg",
        parameters = {
                @StoredProcedureParameter(
                        mode = ParameterMode.IN,
                        name = "cheat",
                        type = Boolean.class
                ),
                @StoredProcedureParameter(
                        mode = ParameterMode.IN,
                        name = "var_min_id",
                        type = Integer.class
                ),
                @StoredProcedureParameter(
                        mode = ParameterMode.IN,
                        name = "var_max_id",
                        type = Integer.class
                ),
                @StoredProcedureParameter(
                        mode = ParameterMode.OUT,
                        name = "var_avg_score",
                        type = Integer.class
                ),
                @StoredProcedureParameter(
                        mode = ParameterMode.OUT,
                        name = "var_sum_score",
                        type = Integer.class
                )

        }
)
@ToString
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Entity
public class Student {
    // ...
}

利用持久上下文创建命名存储过程查询，添加输入参数后执行查询：

ProcedureCall showScoreAvg = session.createNamedStoredProcedureQuery("showScoreAvg");
showScoreAvg.setParameter("cheat", true);
showScoreAvg.setParameter("var_min_id", 1);
showScoreAvg.setParameter("var_max_id", 300);
showScoreAvg.execute();
int averageScore = (int) showScoreAvg.getOutputParameterValue("var_avg_score");
int sumScore = (int) showScoreAvg.getOutputParameterValue("var_sum_score");
System.out.println("averageScore:%d, sumScore:%d".formatted(averageScore, sumScore));

输出参数由ProcedureCall.getOutputParameterValue方法返回。

StoredProcedure

使用@NamedStoredProcedureQuery并非是必须的，也可以完全通过编程的方式创建一个存储过程调用定义，然后再添加参数值并调用：

@Test
void test2() {
    var result = this.callProcedureShowScoreAvg(true, 1, 300);
    System.out.println("averageScore:%d, sumScore:%d".formatted(result.avgScore(), result.sumScore()));
    result = this.callProcedureShowScoreAvg(false, 1, 300);
    System.out.println("averageScore:%d, sumScore:%d".formatted(result.avgScore(), result.sumScore()));
}

ShowScoreAvgResult callProcedureShowScoreAvg(boolean cheat, int varMinId, int varMaxId) {
    ProcedureCall showScoreAvg = session.createStoredProcedureCall("showScoreAvg");
    showScoreAvg.registerParameter("cheat", Boolean.class, ParameterMode.IN);
    showScoreAvg.registerParameter("var_min_id", Integer.class, ParameterMode.IN);
    showScoreAvg.registerParameter("var_max_id", Integer.class, ParameterMode.IN);
    showScoreAvg.registerParameter("var_avg_score", Integer.class, ParameterMode.OUT);
    showScoreAvg.registerParameter("var_sum_score", Integer.class, ParameterMode.OUT);
    showScoreAvg.setParameter("cheat", cheat);
    showScoreAvg.setParameter("var_min_id", varMinId);
    showScoreAvg.setParameter("var_max_id", varMaxId);
    showScoreAvg.execute();
    int averageScore = (int) showScoreAvg.getOutputParameterValue("var_avg_score");
    int sumScore = (int) showScoreAvg.getOutputParameterValue("var_sum_score");
    return new ShowScoreAvgResult(averageScore, sumScore);
}

public record ShowScoreAvgResult(int avgScore, int sumScore) {
}

在这个示例中，存储过程调用被封装在callProcedureShowScoreAvg方法中，因为返回多个输出参数，所以结果用一个“记录类”ShowScoreAvgResult表示。这里用Session.createStoredProcedureCall创建了一个存储过程调用对象，和之前用NamedStoredProcedure不同的是，这里要先通过ProcedureCall.registerParameter方法添加存储过程的相关参数定义后才能再通过setParameter方法设置参数值。最后的执行存储过程和获取输出参数的过程与之前是一致的。

两种方式差别不大，只不过第一种方式可以重用存储过程调用的参数定义，但相应的注解编写有一些繁杂。

The End，谢谢阅读。

本文的完整示例可以从这里获取。

参考资料

《MySQL 必知必会》
spring data Jpa 调用存储过程返回多个out参数

本作品采用知识共享署名 4.0 国际许可协议进行许可