MySQL 存储过程实现 id-pid结构的树形数据展示

set max_sp_recursion_depth=255;call showChildLst(56);DROP PROCEDURE IF EXISTS `createChildLst`;delimiter ;;CREATE DEFINER=`root`@`localhost` PROCEDURE `createChildLst`(IN pid INT,IN nDepth INT)    COMMENT '入口过程'BEGINDECLARE done INT DEFAULT 0;DECLARE b INT;DECLARE cur1 CURSOR FOR SELECT id FROM sheet1 where parent_id=pid order by sort;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; ### 这里相当于定义了一种异步事件，当事件发生会设置变量值OPEN cur1;FETCH cur1 INTO b;INSERT INTO tmpLst VALUES (NULL,pid,nDepth,done);WHILE done=0 DO     CALL createChildLst(b,nDepth+1);     FETCH cur1 INTO b;     END WHILE;CLOSE cur1; END ;;delimiter ;-- ------------------------------  Procedure structure for `showChildLst`-- ----------------------------DROP PROCEDURE IF EXISTS `showChildLst`;delimiter ;;CREATE DEFINER=`root`@`localhost` PROCEDURE `showChildLst`(IN pid INT)    COMMENT '递归过程'BEGINCREATE TEMPORARY TABLE IF NOT EXISTS tmpLst(sno int primary key auto_increment,id int,depth int,isLeaf int); DELETE FROM tmpLst; CALL createChildLst(pid,0); select tmpLst.*,sheet1.* from tmpLst,sheet1 where tmpLst.id=sheet1.id order by tmpLst.sno;END ;;delimiter ;