本文介绍了MySQL存储过程的概念、优点，详细讲解了如何创建、查看、执行、修改和删除存储过程，以及如何处理输入和输出参数。文中还通过实例展示了在SQLyog中操作存储过程的步骤，并探讨了在JPA中调用存储过程的方法。此外，文章提到了存储过程中的控制流程、注释和参考资料。
MySQL 学习笔记 1：存储过程
图源：ubiq.co
存储过程可以看做是将一组 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参数
扫一扫
821
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
