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 <= @count DO

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币了。各位慷慨一下