MySql存储过程循环使用的方法(mysql,开发技术)

场景描述

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

解决方案

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

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

案例

建表语句：

--学生信息表DROPTABLEIFEXISTSt_student;CREATETABLE`t_student`(`id`BIGINT(12)NOTNULLAUTO_INCREMENTCOMMENT'主键',`code`VARCHAR(10)NOTNULLCOMMENT'学号',`name`VARCHAR(20)NOTNULLCOMMENT'姓名',`age`INT(2)NOTNULLCOMMENT'年龄',`gender`CHAR(1)NOTNULLCOMMENT'性别（M：男，F：女）',PRIMARYKEY(`id`),UNIQUEKEYUK_STUDENT(`code`))CHARSET=utf8mb4COLLATE=utf8mb4_general_ci;

--学生成绩表DROPTABLEIFEXISTSt_achievement;CREATETABLE`t_achievement`(`id`BIGINT(12)NOTNULLAUTO_INCREMENTCOMMENT'主键',`year`INT(4)NOTNULLCOMMENT'学年',`subject`CHAR(2)NOTNULLCOMMENT'科目（01：语文，02：数学，03：英语）',`score`INT(3)NOTNULLCOMMENT'得分',`student_id`BIGINT(12)NOTNULLCOMMENT'所属学生id',PRIMARYKEY(`id`))CHARSET=utf8mb4COLLATE=utf8mb4_general_ci;

--成绩汇总表DROPTABLEIFEXISTSt_achievement_report;CREATETABLE`t_achievement_report`(`id`BIGINT(12)NOTNULLAUTO_INCREMENTCOMMENT'主键',`student_id`BIGINT(12)NOTNULLCOMMENT'学生id',`year`INT(4)NOTNULLCOMMENT'学年',`total_score`INT(4)NOTNULLCOMMENT'总分',`avg_score`DECIMAL(4,2)NOTNULLCOMMENT'平均分',PRIMARYKEY(`id`))CHARSET=utf8mb4COLLATE=utf8mb4_general_ci;

初始化数据：

INSERTINTOt_student(id,CODE,NAME,age,gender)VALUES(1,'2022010101','小张',18,'M'),(2,'2022010102','小李',18,'F'),(3,'2022010103','小明',18,'M');INSERTINTOt_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);

存储过程：

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

--如果存储过程存在，先删除存储过程DROPPROCEDUREIFEXISTSstatistics_achievement;DELIMITER$$--定义存储过程CREATEPROCEDUREstatistics_achievement()BEGIN--定义变量记录循环处理是否完成 DECLAREdoneBOOLEANDEFAULTFALSE;--定义变量传递学生id DECLAREstudentidBIGINT(12); --定义游标 DECLAREcursor_studentCURSORFORSELECTidFROMt_student; --定义CONTINUEHANDLER，当循环结束时done=true DECLARECONTINUEHANDLERFORSQLSTATE'02000'SETdone=TRUE; --打开游标 OPENcursor_student; --重复遍历 REPEAT --每次读取一次游标 FETCHcursor_studentINTOstudentid;--计算总分、平均分插入汇总表 INSERTINTOt_achievement_report(student_id,`YEAR`,total_score,avg_score) SELECTstudentid,`YEAR`,SUM(score),ROUND(SUM(score)/3,2)FROMt_achievementt1WHEREstudent_id=studentidANDNOTEXISTS( SELECT1FROMt_achievement_reportt2WHEREstudent_id=studentidANDt1.year=t2.year )GROUPBY`YEAR`; --结束循环，意思是等到done=true时，结束循环REPEAT UNTILdoneENDREPEAT; --查询结果，仅会展示查出的最后一条 SELECTstudentid; --关闭游标 CLOSEcursor_student;END$$DELIMITER;

--执行存储过程CALLstatistics_achievement();

• 执行结果，返回查询结果3，即最后一条学生记录id