????2.6?????ú??????name·??
????-- fn_tree_pathname
????-- ???ú??????name·??
????DELIMITER
????DROP FUNCTION IF EXISTS csdn.fn_tree_pathname
????CREATE FUNCTION csdn.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
????DELIMITER ;
????2.7?????ù??????????
????-- pro_show_childLst
????DELIMITER
????-- ???ù??????????
????DROP PROCEDURE IF EXISTS pro_show_childLst
????CREATE 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
????2.8?????ù???????????
????-- pro_show_parentLst
????DELIMITER
????-- ???ù???????????
????DROP PROCEDURE IF EXISTS `pro_show_parentLst`
????CREATE 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
????3??????????
????3.1????????????????????????
????mysql> CALL pro_show_childLst(-1);
????+----+-----------------------+-----------+-------+-------------+----------------------------+
????| id | NAME                  | parent_id | depth | path        | pathname                   |
????+----+-----------------------+-----------+-------+-------------+----------------------------+
????| 13 |   --???              |        -1 |     1 | -1/13       | ???/                      |
????| 16 |     --???????      |        13 |     2 | -1/13/16    | ???/???????/           |
????| 14 |   --TV580             |        -1 |     1 | -1/14       | TV580/                     |
????| 17 |     --???            |        14 |     2 | -1/14/17    | TV580/???/                |
????| 18 |       --??????      |        17 |     3 | -1/14/17/18 | TV580/???/??????/       |
????| 15 |   --????580           |        -1 |     1 | -1/15       | ????580/                   |
????+----+-----------------------+-----------+-------+-------------+----------------------------+
????6 rows in set (0.05 sec)
????Query OK?? 0 rows affected (0.05 sec)
????3.2?????????????????
????CALL pro_show_childLst(13);
????mysql> CALL pro_show_childLst(13);
????+----+---------------------+-----------+-------+----------+-------------------------+
????| id | NAME                | parent_id | depth | path     | pathname                |
????+----+---------------------+-----------+-------+----------+-------------------------+
????| 13 | --???              |        -1 |     0 | -1/13    | ???/                   |
????| 16 |   --???????      |        13 |     1 | -1/13/16 | ???/???????/        |
????+----+---------------------+-----------+-------+----------+-------------------------+
????2 rows in set (0.02 sec)
????Query OK?? 0 rows affected (0.02 sec)
????mysql>
????3.3?????TV580?????????????
????CALL pro_show_childLst(14);
????mysql> CALL pro_show_childLst(14);
????+----+--------------------+-----------+-------+-------------+----------------------------+
????| id | NAME               | parent_id | depth | path        | pathname                   |
????+----+--------------------+-----------+-------+-------------+----------------------------+
????| 14 | --TV580            |        -1 |     0 | -1/14       | TV580/                     |
????| 17 |   --???           |        14 |     1 | -1/14/17    | TV580/???/                |
????| 18 |     --??????     |        17 |     2 | -1/14/17/18 | TV580/???/??????/       |
????+----+--------------------+-----------+-------+-------------+----------------------------+
????3 rows in set (0.02 sec)
????Query OK?? 0 rows affected (0.02 sec)
????mysql>
????3.4??“???”?????????????????????
????CALL pro_show_childLst(17);
????mysql> CALL pro_show_childLst(17);
????+----+------------------+-----------+-------+-------------+----------------------------+
????| id | NAME             | parent_id | depth | path        | pathname                   |
????+----+------------------+-----------+-------+-------------+----------------------------+
????| 17 | --???           |        14 |     0 | -1/14/17    | TV580/???/                |
????| 18 |   --??????     |        17 |     1 | -1/14/17/18 | TV580/???/??????/       |
????+----+------------------+-----------+-------+-------------+----------------------------+
????2 rows in set (0.03 sec)
????Query OK?? 0 rows affected (0.03 sec)
????mysql>
????3.5??“??????”???????????????????
????mysql> CALL pro_show_childLst(18);
????+----+----------------+-----------+-------+-------------+----------------------------+
????| id | NAME           | parent_id | depth | path        | pathname                   |
????+----+----------------+-----------+-------+-------------+----------------------------+
????| 18 | --??????     |        17 |     0 | -1/14/17/18 | TV580/???/??????/       |
????+----+----------------+-----------+-------+-------------+----------------------------+
????1 row in set (0.36 sec)
????Query OK?? 0 rows affected (0.36 sec)
????mysql>
????3.6???????????????
????CALL pro_show_parentLst(-1);
????mysql> CALL pro_show_parentLst(-1);
????Empty set (0.01 sec)
????Query OK?? 0 rows affected (0.01 sec)
????mysql>
????3.7?????“???”??????
????CALL pro_show_parentLst(13);
????mysql> CALL pro_show_parentLst(13);
????+----+----------+-----------+-------+-------+----------+
????| id | NAME     | parent_id | depth | path  | pathname |
????+----+----------+-----------+-------+-------+----------+
????| 13 | --???   |        -1 |     0 | -1/13 | ???/    |
????+----+----------+-----------+-------+-------+----------+
????1 row in set (0.02 sec)
????Query OK?? 0 rows affected (0.02 sec)
????mysql>
????3.8?????“TV580”??????parent_id?-1
????CALL pro_show_parentLst(14);
????mysql> CALL pro_show_parentLst(14);
????+----+---------+-----------+-------+-------+----------+
????| id | NAME    | parent_id | depth | path  | pathname |
????+----+---------+-----------+-------+-------+----------+
????| 14 | --TV580 |        -1 |     0 | -1/14 | TV580/   |
????+----+---------+-----------+-------+-------+----------+
????1 row in set (0.02 sec)
????Query OK?? 0 rows affected (0.02 sec)
????3.9?????“???”????????
????mysql>
????CALL pro_show_parentLst(17);
????mysql> CALL pro_show_parentLst(17);
????+----+-----------+-----------+-------+----------+---------------+
????| id | NAME      | parent_id | depth | path     | pathname      |
????+----+-----------+-----------+-------+----------+---------------+
????| 17 | --???    |        14 |     0 | -1/14/17 | TV580/???/   |
????| 14 |   --TV580 |        -1 |     1 | -1/14    | TV580/        |
????+----+-----------+-----------+-------+----------+---------------+
????2 rows in set (0.02 sec)
????Query OK?? 0 rows affected (0.02 sec)
????mysql>
????3.10??????????“??????”??????
????CALL pro_show_parentLst(18);
????mysql> CALL pro_show_parentLst(18);
????+----+----------------+-----------+-------+-------------+----------------------------+
????| id | NAME           | parent_id | depth | path        | pathname                   |
????+----+----------------+-----------+-------+-------------+----------------------------+
????| 18 | --??????     |        17 |     0 | -1/14/17/18 | TV580/???/??????/       |
????| 17 |   --???       |        14 |     1 | -1/14/17    | TV580/???/                |
????| 14 |     --TV580    |        -1 |     2 | -1/14       | TV580/                     |
????+----+----------------+-----------+-------+-------------+----------------------------+
????3 rows in set (0.02 sec)
????Query OK?? 0 rows affected (0.02 sec)
????mysql>