mysql一直光标闪_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

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

0

顶

0

踩

分享到：

2012-04-18 15:46

浏览 11525

分类:数据库

评论