mysql 树展现 id pid_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 ;