项目需求：对表进行重新构建，这个用java的缺点是数据的传送以及遍历的话会消耗更多的资源，因此使用mysql的存储过程进行构建。
具体要求：跳过原本设置的假期和课程本身的假期对数据进行重排。
1.游标从创建到关闭的过程： 注意class_id1 的属性刚开始由declare设置的默认是空，但在打开游标之前只要给它重新赋值就不为空了。
DECLARE cc_2 CURSOR FOR SELECT id FROM pms_teach_example_day where class_id=class_id1 AND id>=(SELECT id FROM pms_teach_example_day WHERE class_id=class_id1 AND date is NULL ORDER BY id limit 1);
2.定义结束条件：02000是sql的状态码，意思是这条sql语句执行到最后了，当然在最开始的时候要定义done这个属性，默认值设为0，如果这个done属性还用在其他游标里，每次关闭游标前把done设置为0（重点），以下是定义结束条件的两种写法。
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000‘ SET done = 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
3.打开游标进行循环：循环也可以有两种：一种就是repeat 另一种就是loop，loop的好处是可以控制循环何时跳出，记得打开loop要跟着跳出loop和关闭loop的结束语句，另外，在这里边虽然没有写done 的值但是它默认结束后就会给你返回1，因此在关闭游标前依旧要把done的值设为0，这个很坑，因为mysql的变量都是全局变量，一处修改下次就不能用了，所以一定要重置，cc_2可以简单理解为查询的每一行。
写法一：
OPEN cc_2;
tloop:LOOP
FETCH cc_2 INTO id2;
SET date2 = (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(date3,‘,‘,j+1),‘,‘,-1));
IF i>j THEN
update pms_teach_example_day SET date = date2 WHERE id = id2 ;
SET j=j+1;
END IF;
IF i=j THEN
LEAVE tloop;
END if;
END LOOP tloop;
SET j =0;
SET done =0;
CLOSE cc_2;
写法二：
OPEN t_index;
REPEAT
FETCH cc_2 INTO id2;
IF done!=1 THEN
//操作语句
END IF;
UNTIL DONE END REPEAT;
CLOSE t_index;
5.嵌套循环：嵌套循环最好用游标嵌套，然后两个loop之类的，这里为了方便用了while-游标嵌套，b:BEGIN是写了一个外部的标签，因为我要循环365次，但是总不会真的要循环这么多次，当条件满足后就可以通过这个标签来进行跳出循环，跳出的语句跟loop一样：LEAVE b; 嵌套循环实现跟java一样，外部执行一次，里边要完整的循环一次，注意END LOOP 之后这个内循环就已经关闭了，END LOOP 和CLOSE cc_2之间已经不算在循环里了，所以说标红的之一段代码是外循环里的，我的外循环是用jj来进行控制，内循环的话就是自然执行完，特别注意 j 这个属性，这个属性在内循环中一直使用，因此每次外循环调用的时候要重新给它赋值，这样的话才能保证每次内循环 j 开始的值是固定的。
b:BEGIN
WHILE jj< 365 do
OPEN cc_5;
SET j=0;
t:LOOP
FETCH cc_5 INTO hstartDate,hendDate;
if done !=1 THEN
IF (ADDDATE(lastDate,jj+1)< hstartDate OR ADDDATE(lastDate,jj+1)>hendDate) THEN
SET j = j+1;
END IF;
ELSE
LEAVE t;
END IF;
END LOOP t;
CLOSE cc_5;
IF (SELECT LOCATE((WEEKDAY(ADDDATE(lastDate,jj+1))+1),classDay))>0 THEN
SET j=j+1;
END IF;
SET done = 0;
IF j = h THEN
UPDATE pms_teach_example_day SET date =(ADDDATE(lastDate,jj+1)) WHERE class_id= cid AND date is NULL LIMIT 1 ;
SELECT jj;
END if;
if(SELECT count(0) FROM pms_teach_example_day WHERE class_id = cid AND date is NULL)=0 THEN
LEAVE b;
END IF;
SET jj =jj+1;
END WHILE;
END b;
6.最后最后，一定要开始事务，将需要保持原子性的代码都放在一个事务里，不然你有的执行成功有的执行不成功会造成数据混乱。
7.完整代码：有点乱，以后再改吧。
BEGIN
DECLARE cid int DEFAULT 0;
DECLARE count int(100);
DECLARE id2 int(200);
DECLARE h int(100);
DECLARE outId int(100) ;
DECLARE day_num int(100);
DECLARE bdate VARCHAR(255);
DECLARE edate VARCHAR(255);
DECLARE hstartDate VARCHAR(255);
DECLARE hendDate VARCHAR(255);
DECLARE classDay VARCHAR(255);
DECLARE date2 VARCHAR(255);
DECLARE date3 VARCHAR(1000);
DECLARE lastDate VARCHAR(1000);
DECLARE longth int(100);
DECLARE hlength int(100);
DECLARE i int(100);
DECLARE jj int(100) DEFAULT 0;
DECLARE j int(100) DEFAULT 0;
DECLARE done int(100) DEFAULT 0;
DECLARE t_error int(100) DEFAULT 0;
DECLARE startLocate int(100);
DECLARE cc CURSOR FOR SELECT start_date,end_date FROM pms_holiday_info where FIND_IN_SET(cid,outline_id_array);
DECLARE cc_3 CURSOR FOR SELECT date FROM pms_teach_example_day WHERE class_id =class_id1 AND date > end_date ;
DECLARE cc_2 CURSOR FOR SELECT id FROM pms_teach_example_day where class_id=class_id1 AND id>=(SELECT id FROM pms_teach_example_day WHERE class_id=class_id1 AND date is NULL ORDER BY id limit 1);
DECLARE cc_5 CURSOR FOR SELECT start_date,end_date FROM pms_holiday_info WHERE FIND_IN_SET(outId,outline_id_array);
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000‘ SET done = 1;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;
START TRANSACTION;
SET bdate=begin_date;
SET edate=end_date;
SET cid =class_id1;
UPDATE pms_teach_example_day SET date=null WHERE class_id = cid AND date>=bdate and date<=edate;
SET count =( SELECT ROW_COUNT());
OPEN cc_3;
l:LOOP
FETCH cc_3 INTO date2;
IF done !=1 THEN
set date3 = (select CONCAT_WS(‘,‘,date3,date2));
END if;
IF done =1 THEN
LEAVE l;
END IF;
END LOOP l;
SET done = 0;
CLOSE cc_3;
SET i = ((SELECT count(0) FROM pms_teach_example_day WHERE class_id=cid));
SET i = i- count;
set classDay =(SELECT REPLACE(class_day,‘,‘,‘‘) FROM pms_class_info WHERE id =cid);
set longth =( SELECT CHAR_LENGTH(classDay));
set startLocate = (SELECT LOCATE(day_num,classDay));
SET hlength = ((SELECT CHAR_LENGTH(date3))-(SELECT CHAR_LENGTH(REPLACE(date3,‘,‘,‘‘)))+1);
OPEN cc_2;
tloop:LOOP
FETCH cc_2 INTO id2;
SET date2 = (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(date3,‘,‘,j+1),‘,‘,-1));
IF i>j THEN
update pms_teach_example_day SET date = date2 WHERE id = id2 ;
SET j=j+1;
END IF;
IF i=j THEN
LEAVE tloop;
END if;
END LOOP tloop;
SET j =0;
SET done =0;
CLOSE cc_2;
UPDATE pms_teach_example_day set date = NULL WHERE class_id=cid ORDER BY id DESC LIMIT count;
set outId =(SELECT outline_id FROM pms_class_info WHERE id =cid);
SET h =(SELECT count(0) FROM pms_holiday_info WHERE FIND_IN_SET(outId,outline_id_array))+1;
set lastDate = (SELECT MAX(date) FROM pms_teach_example_day WHERE class_id =cid );
b:BEGIN
WHILE jj< 365 do
OPEN cc_5;
SET j=0;
t:LOOP
FETCH cc_5 INTO hstartDate,hendDate;
if done !=1 THEN
IF (ADDDATE(lastDate,jj+1)< hstartDate OR ADDDATE(lastDate,jj+1)>hendDate) THEN
SET j = j+1;
END IF;
ELSE
LEAVE t;
END IF;
END LOOP t;
CLOSE cc_5;
IF (SELECT LOCATE((WEEKDAY(ADDDATE(lastDate,jj+1))+1),classDay))>0 THEN
SET j=j+1;
END IF;
SET done = 0;
IF j = h THEN
UPDATE pms_teach_example_day SET date =(ADDDATE(lastDate,jj+1)) WHERE class_id= cid AND date is NULL LIMIT 1 ;
SELECT jj;
END if;
if(SELECT count(0) FROM pms_teach_example_day WHERE class_id = cid AND date is NULL)=0 THEN
LEAVE b;
END IF;
SET jj =jj+1;
END WHILE;
END b;
IF t_error = 1 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
END