CREATE DEFINER=`root`@`%` PROCEDURE `sp_select_planstate`(
pid int
)
COMMENT '计划状态查询'
begin
declare done int default 0;
declare childid int;
declare planid int;
declare cur_5 cursor for select id from t_plan where parent_id=pid;
declare cur_6 cursor for select plan_id from t_usecase where plan_id=pid;
declare continue handler for not found set done = 1;
drop table if exists state_temp;
CREATE TEMPORARY TABLE state_temp(
state int
);
set max_sp_recursion_depth = 5;
insert into state_temp(state) select plan_state from t_plan where id=pid;
open cur_5;
fetch cur_5 into childid;
while done=0 do
call sp_select_planstate(childid);
fetch cur_5 into childid;
end while;
close cur_5;
set done=0;
open cur_6;
fetch cur_6 into planid;
while done=0 do
insert into state_temp(state) select usecase_state from t_usecase where plan_id=planid;
fetch cur_6 into planid;
end while;
close cur_6;
select * from state_temp;
end;需求效果:查询出特定id的计划的状态放入临时表,继续查询这个计划的子计划的状态,也放入临时表,依次类推,最后查询计划包含的用例的状态,也放入临时表,最终取出临时表的状态.
上面的存储过程存在的问题是在临时表中它只存储用例和用例对应的计划的状态,计划的父计划的状态被删除了(因为递归的原因),哪位大个给该下,能够保存所有的状态,或者给点别的思路也可以,谢谢!
CREATE TEMPORARY TABLE state_temp(
state int
);
-->CREATE TEMPORARY TABLE if not exists state_temp(
state int
);
DROP TABLE IF EXISTS `t_plan`;
CREATE TABLE `t_plan` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sn` int(11) NOT NULL DEFAULT '0',
`plan_name` varchar(100) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,
`plan_state` int(11) DEFAULT NULL,
`plan_type` int(11) DEFAULT NULL,
`start_time` varchar(100) NOT NULL,
`end_time` varchar(100) NOT NULL,
`cycle_type` varchar(20) DEFAULT '0',
`cycle_minutes` int(11) DEFAULT '0',
`cycle_oclock` int(11) DEFAULT '0',
`cycle_day` int(11) DEFAULT '0',
`cycle_date` int(11) DEFAULT '0',
`long_time` double DEFAULT '0',
`num` int(11) DEFAULT '0',
`execute_type` int(11) DEFAULT '0',
`dtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`url` varchar(300) DEFAULT NULL,
`direct` varchar(300) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `parent_id` (`parent_id`)
) ENGINE=MyISAM AUTO_INCREMENT=62 DEFAULT CHARSET=utf8;#
# Dumping data for table t_plan
#
LOCK TABLES `t_plan` WRITE;
/*!40000 ALTER TABLE `t_plan` DISABLE KEYS */;INSERT INTO `t_plan` VALUES (2,0,'fzo',0,1,3,'2009-06-30 1:04:05','2009-09-30 17:59:05','',0,0,0,0,0,0,1,'2009-06-30 16:27:22','/IMTS/TestMg/a.jsp','/IMTS/TestMg/c.jsp');
INSERT INTO `t_plan` VALUES (28,214,'66660',0,1,3,'2009-06-30 1:04:05','2009-09-30 17:59:05','',0,0,0,0,0,0,1,'2009-07-07 16:25:11','/IMTS/TestMg/a.jsp','/IMTS/TestMg/c.jsp');
INSERT INTO `t_plan` VALUES (32,0,'Spring',0,1,3,'2009-06-30 00:00:00','2009-06-30 20:00:00','',0,0,0,0,0,0,2,'2009-07-03 09:39:20','/IMTS/TestMg/a.jsp','/IMTS/TestMg/c.jsp');
INSERT INTO `t_plan` VALUES (38,0,'计划测试',0,1,3,'2009-06-29 00:00:00','2009-06-30 00:00:00','',0,0,0,0,0,0,3,'2009-07-08 10:02:20','/IMTS/TestMg/a.jsp','/IMTS/TestMg/c.jsp');
INSERT INTO `t_plan` VALUES (39,0,'继续测试',38,1,3,'2009-06-22 00:00:00','2009-06-30 00:00:00','',0,0,0,0,0,0,3,'2009-07-08 09:11:44','/IMTS/TestMg/a.jsp','/IMTS/TestMg/c.jsp');
INSERT INTO `t_plan` VALUES (40,0,'乱码测试',0,0,3,'2009-06-29 00:00:00','2009-06-30 00:00:00','',0,0,0,0,0,0,1,'2009-07-02 09:24:04','/IMTS/TestMg/a.jsp','/IMTS/TestMg/c.jsp');
INSERT INTO `t_plan` VALUES (41,0,'香港回归测试',0,0,3,'2009-07-01 00:00:00','2009-07-01 00:10:00','',0,0,0,0,0,0,2,'2009-07-01 13:56:54','/IMTS/TestMg/a.jsp','/IMTS/TestMg/c.jsp');
INSERT INTO `t_plan` VALUES (44,0,'ewan',0,1,3,'2009-07-01 00:00:00','2009-07-01 17:22:20','',0,0,0,0,0,0,1,'2009-07-01 19:04:11','/IMTS/TestMg/a.jsp','/IMTS/TestMg/c.jsp');
INSERT INTO `t_plan` VALUES (49,0,'qqqq',0,0,3,'2009-07-01 00:00:00','2009-07-03 00:00:00','',0,0,0,0,0,0,3,'2009-07-03 15:46:27','/IMTS/TestMg/a.jsp','/IMTS/TestMg/c.jsp');
INSERT INTO `t_plan` VALUES (50,0,'测试44',0,0,3,'2009-07-14 00:00:00','2009-07-30 00:00:00','',0,0,0,0,0,0,1,'2009-07-03 15:20:28','/IMTS/TestMg/a.jsp','/IMTS/TestMg/c.jsp');
DROP TABLE IF EXISTS `t_usecase`;
CREATE TABLE `t_usecase` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sn` int(11) DEFAULT '0',
`name` varchar(100) NOT NULL DEFAULT '0',
`plan_id` int(11) NOT NULL,
`dtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`usecase_type` int(11) DEFAULT '0',
`usecase_state` int(11) DEFAULT '0',
`ms_times` int(11) DEFAULT NULL,
`ms_interval` int(11) DEFAULT NULL,
`url` varchar(300) DEFAULT NULL,
`direct` varchar(300) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `plan_id` (`plan_id`)
) ENGINE=MyISAM AUTO_INCREMENT=153 DEFAULT CHARSET=utf8;#
# Dumping data for table t_usecase
#
LOCK TABLES `t_usecase` WRITE;
/*!40000 ALTER TABLE `t_usecase` DISABLE KEYS */;INSERT INTO `t_usecase` VALUES (129,36,'音频用例测试',28,'2009-07-07 16:29:36',2,1,1,1,'/IMTS/TestMg/b.jsp','/IMTS/TestMg/d.jsp');
INSERT INTO `t_usecase` VALUES (130,19,'video',28,'2009-07-07 16:29:36',1,1,1,1,'/IMTS/TestMg/b.jsp','/IMTS/TestMg/d.jsp');
INSERT INTO `t_usecase` VALUES (131,19,'ping',28,'2009-07-07 16:29:36',6,1,2,2,'/IMTS/TestMg/b.jsp','/IMTS/TestMg/d.jsp');
INSERT INTO `t_usecase` VALUES (132,18,'trace',28,'2009-07-07 16:29:36',5,1,2,2,'/IMTS/TestMg/b.jsp','/IMTS/TestMg/d.jsp');
INSERT INTO `t_usecase` VALUES (133,12,'dns',28,'2009-07-07 16:29:36',3,1,2,2,'/IMTS/TestMg/b.jsp','/IMTS/TestMg/d.jsp');
INSERT INTO `t_usecase` VALUES (134,12,'AudioLoop',28,'2009-07-07 16:29:36',4,1,1,2,'/IMTS/TestMg/b.jsp','/IMTS/TestMg/d.jsp');
INSERT INTO `t_usecase` VALUES (136,0,'fzocase',2,'2009-06-30 16:28:17',5,0,0,1,'/IMTS/TestMg/b.jsp','/IMTS/TestMg/d.jsp');
INSERT INTO `t_usecase` VALUES (137,0,'Audio',32,'2009-06-30 16:37:38',2,0,1,1,'/IMTS/TestMg/b.jsp','/IMTS/TestMg/d.jsp');
INSERT INTO `t_usecase` VALUES (138,0,'再次测试',39,'2009-07-08 10:15:23',2,0,10,10,'/IMTS/TestMg/b.jsp','/IMTS/TestMg/d.jsp');
INSERT INTO `t_usecase` VALUES (139,0,'ping乱码测试',40,'2009-06-30 19:30:53',6,0,2,1,'/IMTS/TestMg/b.jsp','/IMTS/TestMg/d.jsp');
INSERT INTO `t_usecase` VALUES (143,0,'九龙测试',41,'2009-07-01 13:58:02',1,0,1,1,'/IMTS/TestMg/b.jsp','/IMTS/TestMg/d.jsp');
INSERT INTO `t_usecase` VALUES (144,0,'单独测试',0,'2009-07-07 11:19:02',2,0,20,10,'/IMTS/TestMg/e.jsp','/IMTS/TestMg/f.jsp');
INSERT INTO `t_usecase` VALUES (150,0,'ping',44,'2009-07-03 14:35:49',6,0,7,2,'/IMTS/TestMg/b.jsp','/IMTS/realtime/audioIndex.jsp');
INSERT INTO `t_usecase` VALUES (152,0,'aaadd',0,'2009-07-08 11:33:10',6,1,1,1,'/IMTS/TestMg/e.jsp','/IMTS/TestMg/f.jsp');
你每次都把 drop table if exists state_temp; 这个表删除了。参考一下下面这个贴子中的
方法二：利用临时表和过程递归http://blog.csdn.net/ACMAIN_CHM/archive/2009/05/02/4142971.aspx
MySQL中进行树状所有子节点的查询