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 '入口过程'
BEGIN
DECLARE 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 '递归过程'
BEGIN
CREATE 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 ;