MySql存储过程循环使用的方法

场景描述

我们举一个简单的场景,首先我们可能会有这样一种情况,考试成绩表(t_achievement)有一堆的sql脚本处理,需要依赖另一个学生表(t_student)数据对部分学生做考试成绩汇总记录到成绩汇总表(t_achievement_report)。

解决方案
  • 有一种方式就是通过代码优先将要汇总的学生表数据获取出来,然后按成绩汇总流程逐个将学生信息数据传递到成绩汇总业务代码进行处理。

  • 另一种方式也是我们今天的主题,那就是通过存储过程的方式去做。

案例

MySql存储过程循环使用的方法

建表语句:

-- 学生信息表
DROP TABLE IF EXISTS t_student;

CREATE TABLE `t_student` (
`id` BIGINT(12) NOT NULL AUTO_INCREMENT COMMENT '
主键'
,
`code` VARCHAR(10) NOT NULL COMMENT '
学号'
,
`name` VARCHAR(20) NOT NULL COMMENT '
姓名'
,
`age` INT(2) NOT NULL COMMENT '
年龄'
,
`gender` CHAR(1) NOT NULL COMMENT '
性别(M:男,F:女)'
,
PRIMARY KEY (`id`),
UNIQUE KEY UK_STUDENT (`code`)
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- 学生成绩表
DROP TABLE IF EXISTS t_achievement;

CREATE TABLE `t_achievement` (
`id` BIGINT(12) NOT NULL AUTO_INCREMENT COMMENT '
主键'
,
`year` INT(4) NOT NULL COMMENT '
学年'
,
`subject` CHAR(2) NOT NULL COMMENT '
科目(01:语文,02:数学,03:英语)'
,
`score` INT(3) NOT NULL COMMENT '
得分'
,
`student_id` BIGINT(12) NOT NULL COMMENT '
所属学生id'
,
PRIMARY KEY (`id`)
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- 成绩汇总表
DROP TABLE IF EXISTS t_achievement_report;

CREATE TABLE `t_achievement_report` (
`id` BIGINT(12) NOT NULL AUTO_INCREMENT COMMENT '
主键'
,
`student_id` BIGINT(12) NOT NULL COMMENT '
学生id'
,
`year` INT(4) NOT NULL COMMENT '
学年'
,
`total_score` INT(4) NOT NULL COMMENT '
总分'
,
`avg_score` DECIMAL(4,2) NOT NULL COMMENT '
平均分'
,
PRIMARY KEY (`id`)
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

初始化数据:

INSERT INTO t_student(id, CODE, NAME, age, gender) VALUES
(1, '
2022010101'
, '
小张'
, 18, '
M'
),
(2, '
2022010102'
, '
小李'
, 18, '
F'
),
(3, '
2022010103'
, '
小明'
, 18, '
M'
);

INSERT INTO t_achievement(YEAR, SUBJECT, score, student_id) VALUES
(2022, '
01'
, 80, 1),
(2022, '
02'
, 85, 1),
(2022, '
03'
, 90, 1),
(2022, '
01'
, 60, 2),
(2022, '
02'
, 90, 2),
(2022, '
03'
, 98, 2),
(2022, '
01'
, 75, 3),
(2022, '
02'
, 100, 3),
(2022, '
03'
, 85, 3);

存储过程:

在这里主要以上面的场景为例,使用存储过程循环去处理数据。写一个存储过程,将以上数据每个学生的成绩进行汇总。

-- 如果存储过程存在,先删除存储过程
DROP PROCEDURE IF EXISTS statistics_achievement;

DELIMITER $$
-- 定义存储过程
CREATE PROCEDURE statistics_achievement()
BEGIN
-- 定义变量记录循环处理是否完成
DECLARE done BOOLEAN DEFAULT FALSE;

-- 定义变量传递学生id
DECLARE studentid BIGINT(12);

-- 定义游标
DECLARE cursor_student CURSOR FOR SELECT id FROM t_student;

-- 定义CONTINUE HANDLER,当循环结束时 done=true
DECLARE CONTINUE HANDLER FOR SQLSTATE '
02000'
SET done=TRUE;

-- 打开游标
OPEN cursor_student;

-- 重复遍历
REPEAT
-- 每次读取一次游标
FETCH cursor_student INTO studentid;

-- 计算总分、平均分插入汇总表
INSERT INTO t_achievement_report(student_id, `YEAR`, total_score, avg_score)
SELECT studentid, `YEAR`, SUM(score), ROUND(SUM(score) / 3, 2) FROM t_achievement t1 WHERE student_id = studentid AND NOT EXISTS(
SELECT 1 FROM t_achievement_report t2 WHERE student_id = studentid AND t1.year = t2.year
) GROUP BY `YEAR`;

-- 结束循环,意思是等到done=true时,结束循环REPEAT
UNTIL done END REPEAT;

-- 查询结果,仅会展示查出的最后一条
SELECT studentid;

-- 关闭游标
CLOSE cursor_student;

END$$
DELIMITER ;
-- 执行存储过程
CALL statistics_achievement();
  • 执行结果,返回查询结果3,即最后一条学生记录id



循环执行语句是任何编程过程中最常用的方法之一,MySql存储过程更是经常需要使用循环语句。在MySql存储过程中使用循环来实现一些重复性的操作,可以更高效、快速地完成数据处理和计算。那么,怎样使用循环语句来提高MySql存储过程的效率呢?下面就为大家介绍一下。
1. 使用while循环语句
while循环语句是MySql中最常用的循环语句之一。通常情况下,我们会在存储过程中使用while循环语句来进行数据的处理和计算。while循环的结构非常简单,基本格式如下:
```
while(condition)
begin
--执行语句
end
```
其中,condition是我们要判断的循环条件,当这个条件为true时,就会一直循环执行语句,直到condition为false时才会退出循环。
2. 使用for循环语句
除了while循环语句,MySql还支持for循环语句,它可以更加清晰地表达循环执行的次数。for循环的基本格式如下:
```
for i in m..n loop
--执行语句
end loop;
```
其中,i代表循环的索引变量,m和n代表循环执行的起始值和结束值。当循环执行到结束值n时,for循环就会退出。
3. 使用游标循环
除了while和for循环语句,MySql还支持游标循环,这种循环方式通常用于处理复杂的数据。使用游标循环的基本步骤如下:
```
DECLARE cursor_name CURSOR FOR SELECT * FROM table_name;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
LOOP
FETCH cursor_name INTO ...
IF done THEN LEAVE LOOP;
...
END LOOP;
```
其中,游标(cursor)就是一个数据的读取和处理机制。我们可以使用游标语句来读取数据,对读取的数据进行处理并输出结果。使用游标循环时,我们需要先声明一个游标,然后在循环中使用fetch语句读取数据,再进行相应的处理。
总的来说,循环语句是MySql存储过程中非常常见的语句之一。使用循环语句可以大大提高数据处理的效率和速度,让我们的程序更加高效、简洁和易于维护。因此,在编写存储过程时,我们一定要掌握好MySql中的循环语句,才能更好地完成数据的处理和分析。