mysql中使用光标的demo

    第一次用游标，写了个demo，此游标蛋疼的实现了从test1表逐条复制数据到同样的表结构的test2，等价于insert into test2 select * from test1;

DROP TABLE IF EXISTS `test1`;
CREATE TABLE `test1` (
  `id` int(11) NOT NULL auto_increment,
  `type` int(11) default NULL,
  `order1` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of test1
-- ----------------------------
INSERT INTO `test1` VALUES ('1', '1', '1');
INSERT INTO `test1` VALUES ('2', '1', '1');
INSERT INTO `test1` VALUES ('3', '1', '1');
INSERT INTO `test1` VALUES ('4', '1', '1');


DROP TABLE IF EXISTS `test2`;
CREATE TABLE `test2` (
  `id` int(11) NOT NULL auto_increment,
  `type` int(11) default NULL,
  `order1` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

create procedure curdemo()
begin
               declare stop_flag int DEFAULT 0;//声明一个标记，当游标状态为最后一条记录时，修改该变量
	declare id int default 0;
	declare type int default 0;
	declare order1 int default 0;
	declare cur1 cursor for select * from test1;
                declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop_flag=1;  
	open cur1;//打开游标

                fetch cur1 into id,type,order1;读取数据到游标
                while stop_flag<>1 DO//若游标有下一条记录，循环
	insert into test2 values(id,type,order1);
                 fetch cur1 into id,type,order1;
                 end while;
                close cur1;//关闭游标
end

 这个测试相当蛋疼.......