BEGIN
DECLARE done INT DEFAULT 0;
DECLARE scoreNumber INT DEFAULT 0;
DECLARE DefstudentId INT DEFAULT 0;
DECLARE backupScrore double DEFAULT 0;
DECLARE cur1 CURSOR FOR select sum(scoreNum) as sumNum,passivityId as studentId from Score group by passivityId ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
UPDATE student d set d.badge = null; OPEN cur1; REPEAT
FETCH cur1 into scoreNumber,DefstudentId;
IF done = 0 THEN SELECT score INTO backupScrore from `backup` WHERE studentId = DefstudentId ORDER BY backupdate desc ,backupId DESC LIMIT 1; IF backupScrore is not NULL THEN
UPDATE student d set d.growScore= scoreNumber- backupScrore where d.studentId= DefstudentId;
END IF;
END IF;
UNTIL done = 1
END REPEAT; CLOSE cur1;
END本来想 在MySQL游标指针为空的时候跳出循环 .结果 在 中间一个判断 is not Null 那里就跳出来了.在这种情况下怎么保证他还能执行游标为空的时候跳出循环?
DECLARE done INT DEFAULT 0;
DECLARE scoreNumber INT DEFAULT 0;
DECLARE DefstudentId INT DEFAULT 0;
DECLARE backupScrore double DEFAULT 0;
DECLARE cur1 CURSOR FOR select sum(scoreNum) as sumNum,passivityId as studentId from Score group by passivityId ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
UPDATE student d set d.badge = null; OPEN cur1; REPEAT
FETCH cur1 into scoreNumber,DefstudentId;
IF done = 0 THEN SELECT score INTO backupScrore from `backup` WHERE studentId = DefstudentId ORDER BY backupdate desc ,backupId DESC LIMIT 1; IF backupScrore is not NULL THEN
UPDATE student d set d.growScore= scoreNumber- backupScrore where d.studentId= DefstudentId;
END IF;
END IF;
UNTIL done = 1
END REPEAT; CLOSE cur1;
END本来想 在MySQL游标指针为空的时候跳出循环 .结果 在 中间一个判断 is not Null 那里就跳出来了.在这种情况下怎么保证他还能执行游标为空的时候跳出循环?
from (
select sum(scoreNum) as sumNum,passivityId as studentId from Score group by passivityId ;
)T
where T.sumNum is not null or studentid is not null;
不是这样.我说的是 .
IF backupScrore is not NULL THEN
UPDATE student d set d.growScore= scoreNumber- backupScrore where d.studentId= DefstudentId;
END IF;
这一句 抛出了 NULL 就是 这个 backupScore is null 这一句 让他跳出了循环 .
SELECT score INTO backupScrore from `backup` WHERE studentId = DefstudentId ORDER BY backupdate desc ,backupId DESC LIMIT 1;
是否有结果,对done赋值
SELECT score INTO backupScrore from `backup` WHERE studentId = DefstudentId ORDER BY backupdate desc ,backupId DESC LIMIT 1;
set done=0;