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 '入口过程'
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 ;
set max_sp_recursion_depth=255;
call showChi