mysql存储过程循环只执行一次

mysql存储过程

下面是存储过程主体，问题是无论怎么跑，貌似里面的两个循环都只会执行一次。好困惑。请sql大神解救

BEGIN
DECLARE stop_flag INT DEFAULT 0;
DECLARE captain VARCHAR(500);
DECLARE captain1 VARCHAR(500);
DECLARE captain2 VARCHAR(500);
declare captainstaffno1 varchar(32);
declare captainstaffno2 varchar(32);
declare crwPilotInf varchar(500);
DECLARE leftSeat VARCHAR(200);
DECLARE rightSeat VARCHAR(200);
DECLARE controller VARCHAR(200);
DECLARE ti_code varchar(32);
declare flightNo VARCHAR(32);
DECLARE flightDate datetime;
declare to_airport varchar(32);
declare ld_airport varchar(32);
declare observers varchar(500) DEFAULT '';
declare observerIds varchar(500) default '';
declare observerText varchar(500);
declare observerNames varchar(100);
declare observerId varchar(100);
DECLARE leftSeat_SID VARCHAR(200);
DECLARE rightSeat_SID VARCHAR(200);
DECLARE controller_SID VARCHAR(200);
#定义游标
DECLARE cur1 CURSOR FOR select tiCode, FLIGHT_NO, datop, tiAirDromeBegin,
tiAirDromeEnd, tiLeftSeat, tiRightSeat, tiControl
from ODS.T_FLIGHT_SEGMENTS_REPORT WHERE datop between '2014-02-01' and '2014-02-28';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET stop_flag=1;
​
open cur1;
WHILE stop_flag = 0 DO
FETCH cur1 INTO ti_code,flightNo,flightDate,to_airport,
ld_airport,leftSeat,rightSeat,controller;
SET @sqlexec:=concat('select CRWPILOTINF,CAPTAIN1,CAPTAINSTAFFNO1,CAPTAIN2,CAPTAINSTAFFNO2 into @crwPilotInf, @captain1, @captainstaffno1, @captain2, @captainstaffno2 from ODS.T_FLIGHT_REPORT_SPL where FLTIDS=\'',flightNo,'\' and DATE_FORMAT(FLTDATE,\'%Y-%m-%d\')=\'',flightDate,'\' and TO_AIRPORT=\'',to_airport,'\' and LD_AIRPORT=\'',ld_airport,'\';');
prepare stmt from @sqlexec ;
execute stmt;
deallocate prepare stmt ;
set crwPilotInf = @crwPilotInf;
set captain1 = @captain1;
set captainstaffno1 = @captainstaffno1;
set captain2 = @captain2;
set captainstaffno2 = @captainstaffno2;
SET @i = 1;
SET @count=CHAR_LENGTH(crwPilotInf)-CHAR_LENGTH(REPLACE(crwPilotInf,'；','')) + 1;
IF IFNULL(@count,0) > 0 THEN
WHILE @i set observerText = SUBSTRING_INDEX(SUBSTRING_INDEX(crwPilotInf,'；',@i),'；',-1);
set observerNames = SUBSTRING_INDEX(SUBSTRING_INDEX(observerText,':',1),':',-1);
set observerId = SUBSTRING_INDEX(SUBSTRING_INDEX(observerText,'+',2),'+',-1);
select @i,observerText,observerNames,observerId;
if (observerText REGEXP captain1) > 0 then
select (observerText REGEXP captain1);
ELSEIF (observerText REGEXP captain2) > 0 then
select (observerText REGEXP captain2);
ELSEIF (observerText REGEXP leftSeat) > 0 then
update ODS.T_FLIGHT_SEGMENTS_REPORT a
INNER JOIN STG.IF_TASKINFOAIRCREWS_TMP b
on a.tiCode=b.TICODE set a.LEFTSEAT_PILOT_ID=b.SID
where b.TICODE=ti_code
and b.MNAME=leftSeat;
ELSEIF (observerText REGEXP rightSeat) > 0 then
update ODS.T_FLIGHT_SEGMENTS_REPORT a
INNER JOIN STG.IF_TASKINFOAIRCREWS_TMP b
on a.tiCode=b.TICODE set a.RIGHTSEAT_PILOT_ID=b.SID
where b.TICODE=ti_code
and b.MNAME=rightSeat;
ELSEIF (observerText REGEXP controller) > 0 then
update ODS.T_FLIGHT_SEGMENTS_REPORT a
INNER JOIN STG.IF_TASKINFOAIRCREWS_TMP b
on a.tiCode=b.TICODE set a.CONTROL_PILOT_ID=b.SID
where b.TICODE=ti_code
and b.MNAME=controller;
ELSE
SET observers = concat(observers, observerNames,',');
SET observerIds = concat(observerIds, observerId,',');
END IF;
SET @i=@i+1;
END WHILE;
END IF;
UPDATE ODS.T_FLIGHT_SEGMENTS_REPORT SET OBSERVER_PILOT_ID=observerIds, OBSERVER_NAME=observers
where FLIGHT_NO=flightNo and datop=flightDate and tiAirDromeBegin=to_airport and tiAirDromeEnd = ld_airport;
SET observers='';
SET observerIds='';
COMMIT;
END WHILE;
CLOSE cur1;
END

麻烦大神们都帮忙看看，问题出在哪里。怎么解决？
ps:没有c币了。各位慷慨一下