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

109 阅读 0 评论 72 点赞

我是靠谱客的博主冷艳中心，这篇文章主要介绍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;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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;