create procedure sp_select_probe_port()
begin
declare probeid int;
declare portid int;
declare done1 int default 0;
declare done2 int default 0;
declare cur_1 cursor for select id from t_probes;
declare cur_2 cursor for select id from t_ports where probe_id=probeid;
declare continue handler for not found set done1=1;
declare continue handler for not found set done2=1;
declare v_id varchar(30);
declare v_parentid varchar(30);
declare v_name varchar(50);drop table if exists temp_probe_port;
create temporary table temp_probe_port(
id varchar(30) default 0,
parent_id varchar(30) default 0,
name varchar(50) default 'N/A'
);
open cur_1;
repeat
fetch cur_1 into probeid;
if not done1 then
set v_id=concat("1",probe_id);
set v_parentid='0';
select name into @v_name from t_probes where id=probeid;
set v_name=@v_name;
insert into temp_probe_port values(v_id,v_parentid,v_name);
open cur_2;
repeat
fetch cur_2 into portid;
if not done2 then
set v_id=concat("2",portid);
set v_parentid=concat("1",probe_id);
select name into @v_name from t_ports where id=portid;
set v_name=@v_name;
insert into temp_probe_port values(v_id,v_parentid,v_name);
end if;
until done2 end repeat;
close cur_2;
set done2=0;
end if;
until done1 end repeat;
close cur_1;
set done1=0;
select * from temp_probe_port;
drop table if exists temp_probe_port;
end创建不成功,哪位给修改下,谢谢!
declare continue handler for not found set done2=1;只要一个。
然后简单描述一下你的功能。这样可以进行调试了。
DROP TABLE IF EXISTS `t_probes`;
CREATE TABLE `t_probes` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`model` varchar(30) NOT NULL DEFAULT 'N/A',
`sn` varchar(30) NOT NULL DEFAULT 'N/A',
`port_count` int(11) NOT NULL DEFAULT '0',
`name` varchar(50) NOT NULL DEFAULT 'N/A',
`version` varchar(30) NOT NULL DEFAULT 'N/A',
`gateway` char(16) NOT NULL DEFAULT 'N/A',
`up_link` varchar(30) NOT NULL DEFAULT 'N/A',
`startup_mode` int(11) NOT NULL DEFAULT '0' COMMENT '????????',
`mode` int(11) NOT NULL DEFAULT '0' COMMENT '??????',
`setup_mode` int(11) unsigned DEFAULT '0' COMMENT '?????????',
`memo` varchar(100) NOT NULL DEFAULT 'N/A',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;INSERT INTO `t_probes` VALUES (1,'model_3','192.168.0.6',1,'probe_test','null','192.168.0.6','tcp_client;127.0.0.1:5000',2,2,2,'probe_for_test');DROP TABLE IF EXISTS `t_ports`;
CREATE TABLE `t_ports` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`port_num` int(11) NOT NULL DEFAULT '0',
`vlan_id` int(11) NOT NULL DEFAULT '0',
`mode` int(11) NOT NULL DEFAULT '0',
`name` varchar(30) NOT NULL DEFAULT 'N/A',
`memo` varchar(100) NOT NULL DEFAULT 'N/A',
`probe_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `probe_id` (`probe_id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;INSERT INTO `t_ports` VALUES (1,0,10,0,'port_1','port-1',1);
INSERT INTO `t_ports` VALUES (2,0,0,2,'port_0','port-0',1);
begin
declare probeid int;
declare portid int;
declare done1 int default 0;
declare done2 int default 0;
declare v_id varchar(30);
declare v_parentid varchar(30);
declare v_name varchar(50);declare cur_1 cursor for select id from t_probes;
declare continue handler for not found set done1=1;
drop table if exists temp_probe_port;
create temporary table temp_probe_port(
id varchar(30) default 0,
parent_id varchar(30) default 0,
name varchar(50) default 'N/A'
);
open cur_1;
repeat
fetch cur_1 into probeid;
if not done1 then
set v_id=concat("1",probe_id);
set v_parentid='0';
select name into @v_name from t_probes where id=probeid;
set v_name=@v_name;
insert into temp_probe_port values(v_id,v_parentid,v_name); begin
declare cur_2 cursor for select id from t_ports where probe_id=probeid;
declare continue handler for not found set done2=1;
open cur_2;
repeat
fetch cur_2 into portid;
if not done2 then
set v_id=concat("2",portid);
set v_parentid=concat("1",probe_id);
select name into @v_name from t_ports where id=portid;
set v_name=@v_name;
insert into temp_probe_port values(v_id,v_parentid,v_name);
end if;
until done2 end repeat;
close cur_2;
set done2=0;
end;
end if;
until done1 end repeat;
close cur_1;
set done1=0;
select * from temp_probe_port;
drop table if exists temp_probe_port;
end;前段时间刚搞错给忘了,晕啊。。