MySql 저장 프로시저 루프를 사용하는 방법

시나리오 설명

먼저 이런 상황이 있을 수 있다는 간단한 시나리오를 보겠습니다(t_achievement)有一堆的sql脚本处理，需要依赖另一个学生表(t_student)数据对部分学生做考试成绩汇总记录到成绩汇总表（t_achievement_report).

Solution

• 한 가지 방법은 코드 우선순위를 통해 요약할 학생 테이블 데이터를 얻은 다음, 학생 정보 데이터를 성적 요약 비즈니스 코드로 하나씩 전송하여 성적 요약 프로세스에 따라 처리하는 것입니다.

• 또 다른 방법은 오늘 우리의 주제인데, 저장 프로시저를 통해 수행하는 것입니다.

Case

Creation 테이블 문:

-- 学生信息表
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 저장 프로시저 루프를 사용하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!