MySQL存储过程不可以定义动态的游标(cursor),但是可以使用变量。open -> fetch...into -> close。
1、声明一个游标:
-- define userId
DECLARE userId nvarchar(50) default 0;
-- define CURSOR
DECLARE mycur CURSOR FOR select id from user;
-- declare cur stop flag (sql error -> stopCur=1)
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET stopCur=1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopCur=1;
2、open,执行 fetch 循环
-- open CURSOR
OPEN cur;
-- Execute only once
FETCH cur INTO userId;
WHILE stopCur<>1 DO
BLOCKA:BEGIN
set insertsql='';
set insertsql=concat('insert into newUser(id,name,age) select id,name,age from user where id="'+userId+'"');
set @sqlinsert=insertSql;
prepare stmtinsert from @sqlinsert;
execute stmtinsert;
END BLOCKA;
-- Execute i-1 times
fetch cur into userId;
END WHILE
CLOSE cur;