mysql存储过程中遇到的问题

野性酷女 2022-07-28 06:25 324阅读 0赞

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

DROP PROCEDURE IF EXISTS \`PROC\_SUMMARY\_STAGE\`;  
DELIMITER ;;  
CREATE [DEFINER=\`mysql\`@\`%][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 ;

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

[DEFINER_mysql]: mailto:DEFINER=`mysql`@`%