mysql存储过程中遇到的问题

对mysql不熟悉，之前也没写过存储过程，因此写这个费了不少时间，下面这是我写好的存储过程，用navicat 中‘工具’--——>'数据传输' 导出的sql文件，

DROP PROCEDURE IF EXISTS `PROC_SUMMARY_STAGE`;
DELIMITER ;;
CREATE DEFINER=`mysql`@`%` PROCEDURE `PROC_SUMMARY_STAGE`()
BEGIN
 DECLARE personPlaneId BIGINT;
 DECLARE startTime datetime;            #开始时间
 DECLARE endTime datetime;             #结束时间
 DECLARE sumStudyTime INT;             #当前学时
 DECLARE targetStudyTime INT;           #目标学时
 DECLARE id1_count INT DEFAULT 0;         #t_stage_info 第一阶段条数
 DECLARE id2_count INT DEFAULT 0;         #t_stage_info 第二阶段条数
 DECLARE id3_count INT DEFAULT 0;         #t_stage_info 第三阶段条数
 DECLARE firstEndTime datetime;           #第一阶段结束时间
 DECLARE secEndTime datetime;            #第二阶段结束时间
 DECLARE thirdEndTime datetime;          #第三阶段结束时间

 DECLARE done INT DEFAULT 0;
    
  #声明光标
   DECLARE cur1 CURSOR FOR
    SELECT p1.id,
    p1.start_learn_time start_time,
    p1.last_date end_time,
    p1.sum_study_time,
    p1.target_study_time
    FROM t_person_plan p1
    WHERE p1.license_type = 7 AND p1.dept_id = 320500
    AND p1.last_date >= CONCAT(date_sub(curDate(),interval 1 day),' 00:00:00')
    AND p1.last_date < CONCAT(curDate(),' 00:00:00');
 
  #当游标到达尾部时，mysql自动设置done=1
   declare continue handler for SQLSTATE '02000' SET done = 1; 
  
  OPEN cur1;  
   REPEAT
     #移动游标并赋值 
        fetch cur1 into personPlaneId,startTime,endTime,sumStudyTime,targetStudyTime;
    IF NOT done  THEN 

       #获取第一阶段结束时间
       SET firstEndTime = (SELECT add_time FROM t_person_plan_record WHERE person_plan_id=personPlaneId AND chapter_id=29611 LIMIT 1);
       #获取第二阶段结束时间
       SET secEndTime = (SELECT add_time FROM t_person_plan_record WHERE person_plan_id=personPlaneId AND chapter_id=29625 LIMIT 1);
       #获取第三阶段结束时间
       SET thirdEndTime = (SELECT add_time FROM t_person_plan_record WHERE person_plan_id=personPlaneId AND chapter_id=29734 LIMIT 1);
       #获取count
       SELECT COUNT(1) INTO id1_count from t_stage_info where person_plan_id=personPlaneId AND stage=1;
       SELECT COUNT(1) INTO id2_count from t_stage_info where person_plan_id=personPlaneId AND stage=2;
       SELECT COUNT(1) INTO id3_count from t_stage_info where person_plan_id=personPlaneId AND stage=3;

      IF sumStudyTime<=12 THEN

        IF id1_count>0  THEN
        UPDATE t_stage_info SET end_time=endTime,sum_study_time=sumStudyTime WHERE person_plan_id=personPlaneId AND stage=1;
       ELSE
        INSERT INTO t_stage_info(person_plan_id,start_time,end_time,sum_study_time,target_study_time,stage)
         VALUES(personPlaneId,startTime,endTime,sumStudyTime,targetStudyTime,1);
       END IF;
       
      ELSEIF (sumStudyTime>12) AND (sumStudyTime<=14) THEN
       
       #更新第一阶段数据
       IF id1_count>0  THEN
        UPDATE t_stage_info SET end_time=firstEndTime,sum_study_time=sumStudyTime WHERE person_plan_id=personPlaneId AND stage=1;
       ELSE
        INSERT INTO t_stage_info(person_plan_id,start_time,end_time,sum_study_time,target_study_time,stage)
         VALUES(personPlaneId,startTime,firstEndTime,sumStudyTime,targetStudyTime,1);
       END IF;
       #更新第二阶段数据
       IF id2_count > 0  THEN
        UPDATE t_stage_info SET end_time=endTime,sum_study_time=sumStudyTime WHERE person_plan_id=personPlaneId AND stage=2;
       ELSE
        INSERT INTO t_stage_info(person_plan_id,start_time,end_time,sum_study_time,target_study_time,stage)
         VALUES(personPlaneId,firstEndTime,endTime,sumStudyTime,targetStudyTime,2);
       END IF;

      #当学到第三阶段时
      ELSEIF sumStudyTime>14 THEN
       
       #如果thirdEndTime为空则取最后学习的时间

      #此处开始是用的case。。when  then ，后来保存直接报错，可能是mysql中if ...else   不能嵌套 case..when  then 的缘故，因此换掉了，
       #SET thirdEndTime = CASE WHEN thirdEndTime IS NULL THEN endTime ELSE thirdEndTime END;

 

       IF thirdEndTime IS NULL THEN
        SET thirdEndTime = endTime;
       END IF;

       #更新第一阶段数据
       IF id1_count>0 THEN
        UPDATE t_stage_info SET end_time=firstEndTime,sum_study_time=sumStudyTime WHERE person_plan_id=personPlaneId AND stage=1;
       ELSE
        INSERT INTO t_stage_info(person_plan_id,start_time,end_time,sum_study_time,target_study_time,stage)
         VALUES(personPlaneId,startTime,firstEndTime,sumStudyTime,targetStudyTime,1);
       END IF;
       #更新第二阶段数据
       IF id2_count>0 THEN
        UPDATE t_stage_info SET end_time=secEndTime,sum_study_time=sumStudyTime WHERE person_plan_id=personPlaneId AND stage=2;
       ELSE
        INSERT INTO t_stage_info(person_plan_id,start_time,end_time,sum_study_time,target_study_time,stage)
         VALUES(personPlaneId,firstEndTime,secEndTime,sumStudyTime,targetStudyTime,2);
       END IF;
       #更新第三阶段数据
       IF  id3_count>0 THEN
        UPDATE t_stage_info SET end_time=thirdEndTime,sum_study_time=sumStudyTime WHERE person_plan_id=personPlaneId AND stage=3;
       ELSE
        INSERT INTO t_stage_info(person_plan_id,start_time,end_time,sum_study_time,target_study_time,stage)
         VALUES(personPlaneId,secEndTime,thirdEndTime,sumStudyTime,targetStudyTime,3);
       END IF;
       
      END IF;
       END IF;
   UNTIL done END REPEAT; 
  close cur1; 

END
;;
DELIMITER ;

 

希望遇到同样问题的你，不犯这样的错误。