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`()
BEGIN
	DECLARE 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 ;

调用CALL pro_user();

查看tmp_user，发现记录已经同步过去了。