DROP TABLE IF EXISTS `treenodes`;
CREATE TABLE `treenodes` (
`id` int(11) NOT NULL,
`nodename` varchar(20) DEFAULT NULL,
`pid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=
innodb DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of treenodes
-- ----------------------------
INSERT INTO `treenodes` VALUES ('1', 'A', '0');
INSERT INTO `treenodes` VALUES ('2', 'B', '1');
INSERT INTO `treenodes` VALUES ('3', 'C', '1');
INSERT INTO `treenodes` VALUES ('4', 'D', '2');
INSERT INTO `treenodes` VALUES ('5', 'E', '2');
INSERT INTO `treenodes` VALUES ('6', 'F', '3');
INSERT INTO `treenodes` VALUES ('7', 'G', '6');
CREATE PROCEDURE showChildList (IN rootId INT,IN)
BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst
(sno int primary key auto_increment,
id int,
depth int
);
DELETE FROM tmpLst;
CALL createChildLst(rootId,0);
select tmpLst.*,treeNodes.*
from tmpLst,treeNodes
where tmpLst.id = treeNodes.id
order by tmpLst.sno;
END;
CREATE PROCEDURE createChildLst (IN rootId INT,IN nDepth INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE b INT;
DECLARE cur1 CURSOR FOR SELECT id FROM treeNodes where pid=rootId;
DECLARE
continue HANDLER FOR NOT FOUND SET done = 1;
insert into tmpLst values (null,rootId,nDepth);
OPEN cur1;
FETCH cur1 INTO b;
WHILE done=0 DO
CALL createChildLst(b,nDepth+1);
FETCH cur1 INTO b;
END WHILE;
CLOSE cur1;
END;
mysql> call showChildList(1);
+-----+----+-------+----+----------+-----+
| sno | id | depth | id | nodename | pid |
+-----+----+-------+----+----------+-----+
| 2 | 1 | 0 | 1 | A | 0 |
| 3 | 2 | 1 | 2 | B | 1 |
| 4 | 4 | 2 | 4 | D | 2 |
| 5 | 5 | 2 | 5 | E | 2 |
| 6 | 3 | 1 | 3 | C | 1 |
| 7 | 6 | 2 | 6 | F | 3 |
| 8 | 7 | 3 | 7 | G | 6 |
+-----+----+-------+----+----------+-----+
7 rows in set
Query OK, 0 rows affected
mysql> call showChildList(3);
+-----+----+-------+----+----------+-----+
| sno | id | depth | id | nodename | pid |
+-----+----+-------+----+----------+-----+
| 9 | 3 | 0 | 3 | C | 1 |
| 10 | 6 | 1 | 6 | F | 3 |
| 11 | 7 | 2 | 7 | G | 6 |
+-----+----+-------+----+----------+-----+
3 rows in set
Query OK, 0 rows affected
mysql> call showChildList(5);
+-----+----+-------+----+----------+-----+
| sno | id | depth | id | nodename | pid |
+-----+----+-------+----+----------+-----+
| 12 | 5 | 0 | 5 | E | 2 |
+-----+----+-------+----+----------+-----+
1 row in set
Query OK, 0 rows affected
