mysql 游标 ,嵌套游标

DELIMITER $$
USE `fdtdata`$$
DROP PROCEDURE IF EXISTS `fn_ev_PerformaceMessage02`$$
CREATE DEFINER=`root`@`%` PROCEDURE `fn_ev_PerformaceMessage02`(p_time DATETIME,p_AgentId VARCHAR(64))
BEGIN
-- p_time 必须是一个年月日的时间
 /*局部变量的定义 declare*/ 
         DECLARE tempStudentId VARCHAR(64) DEFAULT NULL ;
         DECLARE tempSubjectId INT;
         DECLARE tempAve DECIMAL(18,2);
         DECLARE tempTime DATETIME;
         DECLARE tempLowMe INT;		-- 同一个科目下，比我低的学生个数
         DECLARE tempAllStudent INT;    -- 同一个科目下，所有的学生的个数 
         DECLARE tempPercent DECIMAL(18,2);   -- tempLowMe/tempAllStudent
         DECLARE ConstGood FLOAT DEFAULT 0.90; -- 设置比多少优秀高？

DECLARE returnStr TEXT;
DECLARE cur1 CURSOR FOR  SELECT c.`StudentId`,c.`SubjectId` ,CAST(AVG(p.`AveScore`) AS DECIMAL(18,2)) AS ave 
FROM `tb_fdt_courseitem` c INNER JOIN `tb_fdt_performance` p
ON c.`CourseItemId`=p.`CourseItemId`
WHERE c.`StartDate`>= p_time AND c.`EndDate` <DATE_ADD(p_time,INTERVAL 1 DAY)  AND c.`AgentId`=p_AgentId
GROUP BY studentid,subjectid
ORDER BY studentid;
         /*    mysql 用异常来判断是不是已经跳出了循环，把 游标 异常后 捕捉 ，并设置 循环使用 变量 tmpname 为 null 跳出循环。          */
DECLARE CONTINUE HANDLER FOR  NOT FOUND SET tempStudentId = NULL;
  
DROP TEMPORARY TABLE IF EXISTS tmp_dingshi_PerformaceMessage;
CREATE TEMPORARY TABLE tmp_dingshi_PerformaceMessage
-- 获得某一天，学生的id和科目的id,已经自己在这个科目的平均分
SELECT c.`StudentId`,c.`SubjectId` ,CAST(AVG(p.`AveScore`) AS DECIMAL(18,2)) AS ave 
FROM `tb_fdt_courseitem` c INNER JOIN `tb_fdt_performance` p
ON c.`CourseItemId`=p.`CourseItemId`
WHERE c.`StartDate`>= p_time AND c.`EndDate` <DATE_ADD(p_time,INTERVAL 1 DAY)  AND c.`AgentId`=p_AgentId
GROUP BY studentid,subjectid
ORDER BY studentid;

    /*开游标*/ 
     OPEN cur1;
         /*游标向下走一步*/ 
         FETCH cur1 INTO tempStudentId,tempSubjectId,tempAve;
         /* 循环体 这很明显 把游标查询出的 name 都加起并用 ; 号隔开 */
      WHILE ( tempStudentId IS NOT NULL) DO
          -- select tempStudentId,tempSubjectId,tempAve;
           SET tempLowMe= (SELECT COUNT(*) FROM tmp_dingshi_PerformaceMessage  WHERE ave<(tempAve) AND subjectid=tempSubjectId);
           SET tempAllStudent=(SELECT COUNT(*) FROM tmp_dingshi_PerformaceMessage WHERE subjectid=tempSubjectId);
           
           SET tempPercent=tempLowMe/tempAllStudent;
           
           SELECT tempStudentId,tempSubjectId,tempAve,tempLowMe,tempAllStudent,tempPercent;
           
           IF(tempPercent>=ConstGood) THEN 	-- 如果比自己差的有19个人，一共20个人，那么19/20=95% 就是比95%的要高
		CALL fn_ev_PerformaceMessage03(tempStudentId,tempSubjectId);
           END IF;
        /*游标向下走一步*/ 

         FETCH cur1 INTO tempStudentId,tempSubjectId,tempAve;
      END WHILE;
      CLOSE cur1;
SELECT returnStr;
    END$$
DELIMITER ;