mysql中施用光标的demo

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; 

欢迎大家阅读《mysql中施用光标的demo》,跪求各位点评，by 搞代码

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)