mysql 存储过程使用游标多执行一条记录

今天在写数据迁移的存储过程发现总会多执行一条记录修改后记录如下：

DECLARE notfoundroute INT DEFAULT 0;
declare route_cursor CURSOR FOR
select uid,	uteamid,	iday,	ctitle,	ctrip,	ctraffic,	cdinner,	chotel,	cmemo,	uuserid,	dupdate,	istop,	cdepartcity,	carrivecity,	cboardtime,	cofftime,	iadddays,	clineno,	ccarriername,	usupplierid,	cport,	ctransit from t_team_route
where uTeamID = vuid ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET notfoundroute = 1;
OPEN route_cursor;
flag_line2:LOOP
FETCH route_cursor into
vrouid,	vrouteamid,	vroiday,	vroctitle,	vroctrip,	vroctraffic,	vrocdinner,	vrochotel,	vrocmemo,	vrouuserid,	vrodupdate,	vroistop,	vrocdepartcity,	vrocarrivecity,	vrocboardtime,	vrocofftime,	vroiadddays,	vroclineno,	vroccarriername,	vrousupplierid,	vrocport,	vroctransit;
IF notfoundroute = 1 THEN LEAVE flag_line2; END IF;
IF notfoundroute =0 THEN
insert into GW_ROUTE( OBJ_TYPE,	OBJ_ID,	IDAY,	TITLE,	DEPART_CITY,	ARRIVAL_CITY,	DEPART_TIME,	ARRIVAL_TIME,	CARRIER_NAME,	SITE,	NIGHT_DAYS,	SHIFT_NO,	TRIP,	TRAFFIC,	DINNER,	HOTEL,	INSTRUCTION,	STATUS,	CREATE_TIME,	UPDATE_TIME,	CREATOR,	OPERATOR)
values ('O',offer_id,vroiDay,vrocTitle,vrocDepartCity,vrocarrivecity,vrocBoardTime,vrocOffTime,vrocCarrierName,vroiStop,vroiAddDays,vrocLineNo,vrocTrip,vroctraffic,vrocdinner,vrochotel,vrocmemo,'E',@timenow,@timenow,4,4);
END IF;
END LOOP;
CLOSE route_cursor;

主要是在遍历游标的时候要先FETCH 在判断notfoundroute的值，这样就不会多执行一条了。

最后

以上就是冷艳中心最近收集整理的关于mysql 存储过程使用游标多执行一条记录的全部内容，更多相关mysql内容请搜索靠谱客的其他文章。