对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 ;
希望遇到同样问题的你,不犯这样的错误。