图源:
存储过程可以看做是将一组 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
注解定义一个“命名的存储过程查询”:
name = "showScoreAvg",
( procedureName = "showScoreAvg",
parameters = {
( mode = ParameterMode.IN,
name = "cheat",
type = Boolean.class
),
( mode = ParameterMode.IN,
name = "var_min_id",
type = Integer.class
),
( mode = ParameterMode.IN,
name = "var_max_id",
type = Integer.class
),
( mode = ParameterMode.OUT,
name = "var_avg_score",
type = Integer.class
),
( mode = ParameterMode.OUT,
name = "var_sum_score",
type = Integer.class
)
}
)
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
并非是必须的,也可以完全通过编程的方式创建一个存储过程调用定义,然后再添加参数值并调用:
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 必知必会》
-
文章评论