DROP TABLE IF EXISTS channel;   
CREATE TABLE channel (   
  id INT(11) NOT NULL AUTO_INCREMENT,     
  cname VARCHAR(200) DEFAULT NULL,   
  parent_id INT(11) DEFAULT NULL,   
  PRIMARY KEY (id)   
) ENGINE=INNODB DEFAULT CHARSET=utf8;   
INSERT  INTO channel(id,cname,parent_id)    
VALUES (13,'首頁',-1),   
       (14,'TV580',-1),   
       (15,'生活580',-1),   
       (16,'左上幻燈片',13),   
       (17,'幫忙',14),   
       (18,'欄目簡介',17);  

CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_cre_childlist`(IN rootId INT,IN nDepth INT)
BEGIN   
      DECLARE done INT DEFAULT 0;   
      DECLARE b INT;   
      DECLARE cur1 CURSOR FOR SELECT id FROM channel WHERE parent_id=rootId;   
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;   
      SET max_sp_recursion_depth=12;   
       
      INSERT INTO tmpLst VALUES (NULL,rootId,nDepth);   
       
      OPEN cur1;   
       
      FETCH cur1 INTO b;   
      WHILE done=0 DO   
              CALL pro_cre_childlist(b,nDepth+1);   
              FETCH cur1 INTO b;   
      END WHILE;   
       
      CLOSE cur1;   
END

CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_cre_parentlist`(IN rootId INT,IN nDepth INT)
BEGIN   
      DECLARE done INT DEFAULT 0;   
      DECLARE b INT;   
      DECLARE cur1 CURSOR FOR SELECT parent_id FROM channel WHERE id=rootId;   
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;   
      SET max_sp_recursion_depth=12;   
       
      INSERT INTO tmpLst VALUES (NULL,rootId,nDepth);   
       
      OPEN cur1;   
       
      FETCH cur1 INTO b;   
      WHILE done=0 DO   
              CALL pro_cre_parentlist(b,nDepth+1);   
              FETCH cur1 INTO b;   
      END WHILE;   
       
      CLOSE cur1;   
END

CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_cre_pathlist`(IN nid INT,IN delimit VARCHAR(10),INOUT pathstr VARCHAR(1000))
BEGIN                     
      DECLARE done INT DEFAULT 0;   
      DECLARE parentid INT DEFAULT 0;         
      DECLARE cur1 CURSOR FOR    
      SELECT t.parent_id,CONCAT(CAST(t.parent_id AS CHAR),delimit,pathstr)   
        FROM channel AS t WHERE t.id = nid;   
           
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;   
      SET max_sp_recursion_depth=12;                     
       
      OPEN cur1;   
       
      FETCH cur1 INTO parentid,pathstr;   
      WHILE done=0 DO              
              CALL pro_cre_pathlist(parentid,delimit,pathstr);   
              FETCH cur1 INTO parentid,pathstr;   
      END WHILE;   
            
      CLOSE cur1;    
END

CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_cre_pnlist`(IN nid INT,IN delimit VARCHAR(10),INOUT pathstr VARCHAR(1000))
BEGIN                     
      DECLARE done INT DEFAULT 0;   
      DECLARE parentid INT DEFAULT 0;         
      DECLARE cur1 CURSOR FOR    
      SELECT t.parent_id,CONCAT(t.cname,delimit,pathstr)   
        FROM channel AS t WHERE t.id = nid;   
           
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;   
      SET max_sp_recursion_depth=12;                     
       
      OPEN cur1;   
       
      FETCH cur1 INTO parentid,pathstr;   
      WHILE done=0 DO              
              CALL pro_cre_pnlist(parentid,delimit,pathstr);   
              FETCH cur1 INTO parentid,pathstr;   
      END WHILE;   
            
      CLOSE cur1;    
END

CREATE DEFINER=`root`@`localhost` FUNCTION `fn_tree_path`(nid INT,delimit VARCHAR(10)) RETURNS varchar(2000) CHARSET utf8
BEGIN     
  DECLARE pathid VARCHAR(1000);   
     
  SET @pathid=CAST(nid AS CHAR);   
  CALL pro_cre_pathlist(nid,delimit,@pathid);   
     
  RETURN @pathid;   
END

CREATE DEFINER=`root`@`localhost` FUNCTION `fn_tree_pathname`(nid INT,delimit VARCHAR(10)) RETURNS varchar(2000) CHARSET utf8
BEGIN     
  DECLARE pathid VARCHAR(1000);   
  SET @pathid='';       
  CALL pro_cre_pnlist(nid,delimit,@pathid);   
  RETURN @pathid;   
END

CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_show_childLst`(IN rootId INT)
BEGIN   
      DROP TEMPORARY TABLE IF EXISTS tmpLst;   
      CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst    
       (sno INT PRIMARY KEY AUTO_INCREMENT,id INT,depth INT);         
       
      CALL pro_cre_childlist(rootId,0);   
       
      SELECT channel.id
	   , CONCAT(SPACE(tmpLst.depth*2),'--',channel.cname) NAME
	   , channel.parent_id
	   , tmpLst.depth
	   , fn_tree_path(channel.id,'/') path
	   , fn_tree_pathname(channel.id,'/') pathname   
      FROM tmpLst,channel WHERE tmpLst.id=channel.id ORDER BY tmpLst.sno;   
END

CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_show_parentLst`(IN rootId INT)
BEGIN   
      DROP TEMPORARY TABLE IF EXISTS tmpLst;   
      CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst    
       (sno INT PRIMARY KEY AUTO_INCREMENT,id INT,depth INT);         
       
      CALL pro_cre_parentlist(rootId,0);   
      SELECT channel.id
            ,CONCAT(SPACE(tmpLst.depth*2),'--',channel.cname) NAME
            ,channel.parent_id,tmpLst.depth,fn_tree_path(channel.id,'/') path
            ,fn_tree_pathname(channel.id,'/') pathname   
      FROM tmpLst,channel 
      WHERE tmpLst.id=channel.id 
      ORDER BY tmpLst.sno;   
END