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

-END-