MySql存儲過程循環使用的方法

今天小編給大家分享一下MySql存儲過程循環使用的方法的相關知識點，內容詳細，邏輯清晰，相信大部分人都還太了解這方面的知識，所以分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后有所收獲，下面我們一起來了解一下吧。

場景描述

我們舉一個簡單的場景，首先我們可能會有這樣一種情況，考試成績表(t_achievement)有一堆的sql腳本處理，需要依賴另一個學生表(t_student)數據對部分學生做考試成績匯總記錄到成績匯總表（t_achievement_report）。

解決方案

• 有一種方式就是通過代碼優先將要匯總的學生表數據獲取出來，然后按成績匯總流程逐個將學生信息數據傳遞到成績匯總業務代碼進行處理。

• 另一種方式也是我們今天的主題，那就是通過存儲過程的方式去做。

案例

建表語句：

-- 學生信息表
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存儲過程循環使用的方法”這篇文章的所有內容，感謝各位的閱讀！相信大家閱讀完這篇文章都有很大的收獲，小編每天都會為大家更新不同的知識，如果還想學習更多的知識，請關注億速云行業資訊頻道。