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 存储过程使用游标多执行一条记录所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。