lovebosom 于 2016.03.25 10:28 提问

t_stage_info`(`id`bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',`person_plan_id`bigint(20) NOT NULL COMMENT 'person_plan_id',`start_time`datetime DEFAULT NULL COMMENT '开始学习时间',`end_time`datetime DEFAULT NULL COMMENT '结束学习时间',`sum_study_time`int(11) DEFAULT NULL COMMENT '当前学时',`target_study_time`int(11) DEFAULT NULL COMMENT '目标学时',`stage```int(10) DEFAULT NULL COMMENT '阶段信息:1第一阶段 2第二阶段 3第三阶段', PRIMARY KEY (```id`)
}

SET firstEndTime = (SELECT add_time FROM t_person_plan_record WHERE person_plan_id=personPlaneId AND chapter_id=29611);

BEGIN
#Routine body goes here...
DECLARE personPlaneId BIGINT;
DECLARE starttime datetime; #开始时间
DECLARE endtime datetime; #结束时间
DECLARE sumStudyTime INT; #当前学时
DECLARE targetStudyTime INT; #目标学时
DECLARE stage INT; #阶段信息:1第一阶段 2第二阶段 3第三阶段
DECLARE id1 BIGINT ; #t_stage_info 第一阶段主键
DECLARE id2 BIGINT ; #t_stage_info 第二阶段主键
DECLARE id3 BIGINT ; #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,
case when p1.sum_study_time >12 AND p1.sum_study_time<14 THEN 1
when p1.sum_study_time >14 AND p1.sum_study_time<24 THEN 2
when p1.sum_study_time >24 THEN 3 END  AS stage
FROM t_person_plan p1 LEFT JOIN t_person_plan_record p2
ON p1.id = p2.person_plan_id WHERE p1.license_type = 7  #AND p2.chapter_id IN (chapterId)
AND DATE_FORMAT(p1.last_date,'%Y-%m-%d') <= DATE_FORMAT(NOW(),'%Y-%m-%d');

#当游标到达尾部时，mysql自动设置done=1
declare continue handler for SQLSTATE '02000' SET done = 1;

OPEN cur1;
REPEAT
/* 移动游标并赋值 */
fetch cur1 into personPlaneId,startTime,endTime,sumStudyTime,targetStudyTime,stage;
if NOT done  then

#当学到第三阶段时
IF sumStudyTime>14 THEN

#获取第一阶段结束时间
SET firstEndTime = (SELECT add_time FROM t_person_plan_record WHERE person_plan_id=personPlaneId AND chapter_id=29611);
#获取第二阶段结束时间
SET secEndTime = (SELECT add_time FROM t_person_plan_record WHERE person_plan_id=personPlaneId AND chapter_id=29625);
#获取第三阶段结束时间
SET thirdEndTime = (SELECT add_time FROM t_person_plan_record WHERE person_plan_id=personPlaneId AND chapter_id=29734);

SET id1 = (SELECT id from t_stage_info where person_plan_id=personPlaneId AND stage=1);
SET id2 = (SELECT id from t_stage_info where person_plan_id=personPlaneId AND stage=2);
SET id3 = (SELECT id from t_stage_info where person_plan_id=personPlaneId AND stage=3);
#更新第一阶段数据
IF id1 IS NULL THEN
INSERT INTO t_stage_info(id,person_plan_id,start_time,end_time,sum_study_time,target_study_time,stage)
VALUES(default,personPlaneId,startTime,firstEndTime,sumStudyTime,targetStudyTime,1);
ELSE
UPDATE t_stage_info SET end_time=firstEndTime,sum_study_time=sumStudyTime WHERE person_plan_id=personPlaneId AND stage=1;
END IF;
#更新第二阶段数据
IF id2 IS NULL THEN
INSERT INTO t_stage_info(id,person_plan_id,start_time,end_time,sum_study_time,target_study_time,stage)
VALUES(default,personPlaneId,firstEndTime,secEndTime,sumStudyTime,targetStudyTime,2);
ELSE
UPDATE t_stage_info SET end_time=secEndTime,sum_study_time=sumStudyTime WHERE person_plan_id=personPlaneId AND stage=2;
END IF;
#更新第三阶段数据
IF id3 IS NULL THEN
INSERT INTO t_stage_info(id,person_plan_id,start_time,end_time,sum_study_time,target_study_time,stage)
VALUES(default,personPlaneId,secEndTime,thirdEndTime,sumStudyTime,targetStudyTime,3);
ELSE
UPDATE t_stage_info SET end_time=thirdEndTime,sum_study_time=sumStudyTime WHERE person_plan_id=personPlaneId AND stage=3;
END IF;

END IF;
SET done = 0;
end if;
UNTIL done END REPEAT;
close cur1;
``````

END

2个回答

cyxevil   2016.03.28 16:05

lovebosom SELECT add_time FROM t_person WHERE pid=pidno AND page = 1 ; 这个建索引是（pid，page）的组合索引吗？add_time不用建索引吧？

lovebosom SELECT add_time FROM t_person WHERE pid=pidno AND page = 1 ;

CSDNXIAOD   2016.03.30 11:23

SQL存储过程事务和优化方法

----------------------biu~biu~biu~~~在下问答机器人小D，这是我依靠自己的聪明才智给出的答案，如果不正确，你来咬我啊！