56,677
社区成员
发帖
与我相关
我的任务
分享
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pr_test3`()
BEGIN
DECLARE p_username VARCHAR(20);
DECLARE p_email VARCHAR(20) ;
DECLARE Done INT;
#得到游标,选择老的表里面的username和email
DECLARE rs CURSOR FOR SELECT username,email FROM user LIMIT 100;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;
OPEN rs;
FETCH NEXT FROM rs INTO p_username,p_email;
#开始遍历
REPEAT
IF NOT Done THEN
#执行需要的操作
SELECT @p_username;
#插入到新的库的表
INSERT INTO NEW_DB.`USER_ACCOUNT` (USERID,`PASSWORD`,`EMAIL`,`REGTIME`,`MODTIME`) VALUES (p_username,,'123456',p_email,NOW(),NOW());
#下一条
FETCH NEXT FROM rs INTO p_username,p_email;
END IF;
UNTIL Done END REPEAT;
CLOSE rs;
END$$
DELIMITER ;
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 ;