游标失灵
上一篇 /
下一篇 2011-10-12 15:46:42
/ 个人分类：SQL大全
BEGIN
declare personId varchar(25);
declare i int;
DECLARE days int;
DECLARE count int;
DECLARE cursorName CURSOR FOR select id from person;
OPEN cursorName ;
set i=1;
set count=0;
while i<50 do
FETCH cursorName INTO personId;
BEGIN
SET days=0;
set count=0;
WHILE count<20 do
INSERT INTO person_batch (pid,batch,seq,tag_id,utime) VALUES (personId,DATE_SUB(CURRENT_TIMESTAMP(),INTERVAL CONCAT(days, " 0:0:0" ) DAY_SECOND ),i,15,CONCAT("2011-10-12"));
set days=days+1;
set count=count+1;
end while;
end;
set i=i+1;
end while;
CLOSE cursorName;
COMMIT;
END
i值未增加
找到原因count未清0，应该改成
BEGIN
declare personId varchar(25);
declare i int;
DECLARE days int;
DECLARE count int;
DECLARE cursorName CURSOR FOR select id from person;
OPEN cursorName ;
set i=1;
set count=0;
while i<50 do
FETCH cursorName INTO personId;
BEGIN
SET days=0;
WHILE count<20 do
INSERT INTO person_batch (pid,batch,seq,tag_id,utime) VALUES (personId,DATE_SUB(CURRENT_TIMESTAMP(),INTERVAL CONCAT(days, " 0:0:0" ) DAY_SECOND ),i,15,CONCAT("2011-10-12"));
set days=days+1;
set count=count+1;
end while;
end;
set i=i+1;
end while;
CLOSE cursorName;
COMMIT;
END
收藏
举报
TAG:
