mysql-游标运用

mysql-游标使用
mysql存储过程好几年没用了，最近看文档，就写了个小例子实践下，增强记忆。
例子主要实现的是从一张表更新记录到另外一张表去。

 CREATE TABLE `user_info` (                           `user_id` int(11) default NULL,                    `user_name` varchar(20) default NULL             ) ENGINE=InnoDB DEFAULT CHARSET=utf8     CREATE TABLE `tmp_user` (                          `tmp_user_id` int(11) default NULL,              `tmp_name` varchar(20) default NULL            ) ENGINE=InnoDB DEFAULT CHARSET=utf8   insert into `user_info` (`user_id`, `user_name`) values('20','aa');insert into `user_info` (`user_id`, `user_name`) values('21','bb');insert into `user_info` (`user_id`, `user_name`) values('22','cc');insert into `user_info` (`user_id`, `user_name`) values('23','dd');insert into `tmp_user` (`tmp_user_id`, `tmp_name`) values('20','');insert into `tmp_user` (`tmp_user_id`, `tmp_name`) values('22','');insert into `tmp_user` (`tmp_user_id`, `tmp_name`) values('23','');

DELIMITER $$DROP PROCEDURE IF EXISTS `test`.`pro_user`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_user`()BEGINDECLARE total INT DEFAULT 0;DECLARE tmpCount INT DEFAULT 0;DECLARE userId INT DEFAULT 0;DECLARE userName varchar(50);DECLARE cur1 CURSOR FOR SELECT user_id,user_name FROM user_info;SELECT count(*) INTO total  FROM user_info;OPEN cur1;  REPEAT FETCH cur1 INTO userId, userName;SET  tmpCount = tmpCount + 1; UPDATE tmp_user set tmp_name=userName where tmp_user_id=userId;  UNTIL  tmpCount >=total END REPEAT;CLOSE cur1; select total ;END$$DELIMITER ;