BEGIN
#Routine body goes here...
DECLARE no_more_record INT DEFAULT 0;
DECLARE TEST_ID INT(20);
DECLARE TEST_USERNAME VARCHAR(20);
DECLARE TEST_SEX INT(2);
DECLARE TEST_ADDRESS VARCHAR(20);
DECLARE cur_record CURSOR FOR SELECT id, username,sex,address from `user`; /*首先这里对游标进行定义*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_record = 1; /*这个是个条件处理,针对NOT FOUND的条件,当没有记录时赋值为1*/
OPEN cur_record; /*接着使用OPEN打开游标*/
FETCH cur_record INTO TEST_ID, TEST_USERNAME,TEST_SEX,TEST_ADDRESS; /*把第一行数据写入变量中,游标也随之指向了记录的第一行*/
WHILE no_more_record != 1 DO
INSERT INTO tempdata(id,username,birthday,sex,address)
VALUES (TEST_ID, TEST_USERNAME,NOW(),TEST_SEX,TEST_ADDRESS);
FETCH cur_record INTO TEST_ID,TEST_USERNAME,TEST_SEX,TEST_ADDRESS;
END WHILE;
CLOSE cur_record; /*用完后记得用CLOSE把资源释放掉*/
END