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();