mysql 数据库 简单存储过程游标使用

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