DROP TABLE IF EXISTS `city`;
CREATE TABLE `city` (
`i_id` int(11) NOT NULL AUTO_INCREMENT,
`p_id` int(11) DEFAULT NULL,
`c_name` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`status` char(1) COLLATE utf8_bin DEFAULT '0',
PRIMARY KEY (`i_id`)
) ENGINE=InnoDB AUTO_INCREMENT=247 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*Data for the table `city` */
insert into `city`(`i_id`,`p_id`,`c_name`,`status`) values
(1,0,'北京','0'),
(2,1,'朝阳区','0'),
(3,1,'海淀区','0'),
(4,1,'石景山区','0'),
(5,4,'永乐小区','0'),
(6,4,'N号楼','0'),
(7,5,'朝阳公园','0'),
(8,2,'幸福溪里','0'),
(9,2,'国贸中心','0');
创建存储过程
/*!50003 DROP PROCEDURE IF EXISTS `getAllNode` */;
DELIMITER $$
/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `getAllNode`(in id int)
BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst(id int primary key auto_increment,i_id int,p_id int,c_name varchar(50));
DELETE FROM tmpLst;
CALL getChildList(id,0);
select tmpLst.*,city.* from tmpLst,city where tmpLst.i_id=city.i_id ;
END */$$
DELIMITER ;
/* Procedure structure for procedure `getChildList` */
/*!50003 DROP PROCEDURE IF EXISTS `getChildList` */;
DELIMITER $$
/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `getChildList`(IN id INT,IN parent_id INT)
BEGIN
DECLARE b INT;
-- 遍历数据结束标志
DECLARE done INT DEFAULT true;
-- 定义一个游标
DECLARE cur1 CURSOR FOR SELECT i_id FROM city WHERE p_id=id;
-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = false;
SET @@max_sp_recursion_depth = 10;
insert into tmpLst (i_id,p_id)VALUES(id,parent_id);
OPEN cur1;
FETCH cur1 INTO b;
WHILE done DO
CALL getChildList(b,id);
FETCH cur1 INTO b;
END WHILE;
CLOSE cur1;
END */$$
DELIMITER ;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
元数据
运行CALL getAllNode(2);