mysql游标案例

mysql游标案例

直接po代码

DELIMITER $
CREATE PROCEDURE test_cursor()
BEGIN
 DECLARE  no_more_record INT DEFAULT 0;
 
 DECLARE  id INT;
 DECLARE  username VARCHAR(50);
 DECLARE  `password` VARCHAR(50);
 
 DECLARE  cur_record CURSOR FOR SELECT admin.id, admin.username, admin.`password` FROM admin;  /*首先这里对游标进行定义*/
 #DECLARE  CONTINUE HANDLER FOR NOT FOUND SET no_more_record = 1; /*这个是个条件处理,针对NOT FOUND的条件,当没有记录时赋值为1*/
 DECLARE  CONTINUE HANDLER FOR SQLSTATE '02000' SET  no_more_record = 1;
 
 OPEN  cur_record; /*接着使用OPEN打开游标*/
 FETCH  cur_record INTO id, username, `password`; /*把第一行数据写入变量中,游标也随之指向了记录的第一行*/
 
 
 tag:WHILE no_more_record != 1 DO
 IF no_more_record = 1 THEN LEAVE tag;
 END IF;
 INSERT  INTO admin3(admin3.`id`, admin3.username, admin3.`password`)
 VALUES  (id, username, `password`);
 FETCH  cur_record INTO id, username, `password`;
 
 END WHILE tag;
 CLOSE  cur_record;  /*用完后记得用CLOSE把资源释放掉*/
END$
#
SELECT * FROM admin3$
#
CALL test_cursor()$
#
TRUNCATE TABLE admin3$