BEGIN
declare p_id INT;
declare p_Code INT default 0;
declare p_Logo INT default 0;
declare p_Name varchar(255) default '';
declare p_Address varchar(755) default '';
declare p_Phone varchar(755) default '';
declare p_Logo2 varchar(755) default '';
declare p_Logo3 varchar(755) default '';
declare p_Paid varchar(755) default '';
declare p_IsDelete varchar(755) default '';
declare done INT DEFAULT 0;
declare cur CURSOR FOR SELECT ID, Code,Logo,Name,Address,Phone,Logo2,Logo3,Paid,IsDelete FROM hospital_copy;
declare CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN cur;
REPEAT
FETCH cur INTO p_id, p_Code,p_Logo,p_Name,p_Address,p_Phone,p_Logo2,p_Logo3,p_Paid,p_IsDelete;
IF done=0 THEN
INSERT INTO `hospital` (
`ID` ,
`Code` ,
`Name` ,
`Country_Code` ,
`Phone` ,
`Address1` ,
`Address2` ,
`Address3` ,
`County` ,
`Country` ,
`Postcode` ,
`Logo` ,
`Logo2` ,
`Logo3` ,
`Paid` ,
`IsDeleted`,
`Add_time`,
`Delete_time`,
`timeInterval`
)
VALUES (
p_id, p_Code,p_Name,'0001','13971056776',p_Address,p_Address, p_Address, p_Address, p_Address, '442200', p_Logo,p_Logo2,p_Logo3,p_Paid,p_IsDelete,'2011-1-1','','30');
END IF;
UNTIL done=1
END REPEAT;
CLOSE cur;
END
加深红色部分解决方法,最后的重复的原因是FETCH cur INTO ……;的时候,当第一次到达最后一条记录时,取出记录,把值给变量,这时一切正常,没有错误。然后UNTIL UNTIL done=1 循环到REPEAT重新开始,再FETCH一条时,已经没有记录,done会被 SET done=1;,但此刻没有进行任何判断,程序会继续执行INSERT INTO ……语句,而由于FETCH没有取到记录,则没有对变量进行赋值,所以变量仍是原值。当到UNTIL done 时程序退出REPEAT,从而最后的记录会被重复。
转载于:https://www.cnblogs.com/zbl3033/p/3958320.html