MySQL 游标遍历

游标 储存过程中的游标遍历

CREATE PROCEDURE cusorProcedure()
BEGIN
declare rid int(11);
declare rbank_code varchar(10);
declare rbank_name varchar(20);
DECLARE done INT default FALSE;
#所有变量后面定义 不然会报错
DECLARE cur CURSOR SCROLL FOR SELECT id,bank_code,bank_name FROM bank_info; 

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;

#FETCH NEXT FROM cur INTO rid,rbank_code,rbank_name;
#SELECT "next",rid,rbank_code,rbank_name;

out_loop:LOOP
FETCH cur INTO rid,rbank_code,rbank_name;
IF done THEN
     LEAVE out_loop;
    END IF;
SELECT "success" as `status`;
insert into test1 (id,bank_code,bank_name) values(rid,rbank_code,rbank_name);
END LOOP out_loop;

#REPEAT
# FETCH cur INTO rid,rbank_code,rbank_name;
# insert into test1 (id,bank_code,bank_name) values(rid,rbank_code,rbank_name);
#UNTIL done END REPEAT;

CLOSE cur;
END


drop procedure cusorProcedure
call cusorProcedure
SELECT * FROM bank_info
SELECT * FROM test1
show procedure status


truncate test1
delete from test1