学步园

时间有限,就直接上案例了，简洁明了

实现功效：
1、定义一个游标，查询出一个列表赋值给游标
2、循环遍历游标，将游标每行数据插入另外一个表的操作

DELIMITER $$

USE `euniversity`$$

DROP PROCEDURE IF EXISTS `test`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()
BEGIN
 DECLARE STOP INT DEFAULT 0;
 
 DECLARE _sid INT DEFAULT 0;
 DECLARE _user_code VARCHAR(500) DEFAULT '';
 DECLARE _user_name VARCHAR(500) DEFAULT '';
 DECLARE _gender VARCHAR(500) DEFAULT '';
 DECLARE _passwords VARCHAR(500) DEFAULT '';
 DECLARE _phone VARCHAR(10) DEFAULT 'Y';
 
 --  声明用户列表的游标
 DECLARE cursor_test CURSOR FOR SELECT sid,user_code,user_name,gender,PASSWORD,phone FROM info_user WHERE enabled_flag='Y';
 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET STOP=1;
 
 OPEN cursor_test;
      -- fetch cursor_test into _sid, _user_code, _user_name, _gender, _passwords, _phone;
     WHILE STOP <> 1 DO
   FETCH cursor_test INTO _sid, _user_code, _user_name, _gender, _passwords, _phone;
          INSERT INTO temp_user (sid,user_code, user_name, gender, PASSWORD, phone) VALUES(_sid, _user_code, _user_name, _gender, _passwords, _phone);
     END WHILE;
        CLOSE cursor_test;
    END$$

DELIMITER ;