改成这个再试 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
加入这个还是死循环,我干脆把 IF NOT Done THEN END; 这两条语句去掉了
找个像dbForge工具调试看看吧。不过有一个地方看着都别扭: DECLARE rs CURSOR FOR SELECT username,email FROM user LIMIT 100; 获取的数据是期望的吗?只从代码上看不出为什么出现死循环。
DECLARE Done INT default 0
我这样做更本就没有得到数据 select @p_username 都是null
这个也做了 你是发开ecshop的吗
最后干脆用insert into DB1.USER select XXX from DB2.user 来解决了
你的存储过程结构好像不太标准 ,循环取值时,只需要出现一次(是否存在下一条记录判断之前)即可,我整理后的脚本如下 : DELIMITER $$ CREATE PROCEDURE `pr_test3`() BEGIN DECLARE done1 INT DEFAULT 0; DECLARE p_username VARCHAR(20); DECLARE p_email VARCHAR(20) ; DECLARE cur1 cursor for SELECT username,email FROM user LIMIT 100; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = 1; open cur1; repeat FETCH cur1 INTO p_username,p_email; #开始遍历 if not done1 then INSERT INTO NEW_DB.`USER_ACCOUNT` (USERID,`PASSWORD`,`EMAIL`,`REGTIME`,`MODTIME`) VALUES (p_username,,'123456',p_email,NOW(),NOW()); end IF; until done1 end REPEAT; CLOSE cur1; commit; END$$ DELIMITER ;
虽然得到的了结果 但是还是会出现 Error Code : 1329 No data - zero rows fetched, selected, or processed 已经有 DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done = 1; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
加入这个还是死循环,我干脆把
IF NOT Done THEN
END;
这两条语句去掉了
DECLARE rs CURSOR FOR SELECT username,email FROM user LIMIT 100;
获取的数据是期望的吗?只从代码上看不出为什么出现死循环。
你是发开ecshop的吗
DELIMITER $$
CREATE PROCEDURE `pr_test3`()
BEGIN
DECLARE done1 INT DEFAULT 0;
DECLARE p_username VARCHAR(20);
DECLARE p_email VARCHAR(20) ;
DECLARE cur1 cursor for SELECT username,email FROM user LIMIT 100;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = 1;
open cur1;
repeat
FETCH cur1 INTO p_username,p_email; #开始遍历
if not done1 then
INSERT INTO NEW_DB.`USER_ACCOUNT` (USERID,`PASSWORD`,`EMAIL`,`REGTIME`,`MODTIME`) VALUES (p_username,,'123456',p_email,NOW(),NOW());
end IF;
until done1 end
REPEAT;
CLOSE cur1;
commit;
END$$ DELIMITER ;
虽然得到的了结果 但是还是会出现
Error Code : 1329
No data - zero rows fetched, selected, or processed
已经有
DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done = 1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;