疑难杂症：oracle SQL查询子节点并用树型显示子节点

Question

现有一张表（id，NAME   ,PID）， 
    如:有几条记录                                   ID     NAME  PID(id,名称，父节点id) 
                                                    1,     a,     0；       
                                                    2，    b，    1；       
                                                    5，    c，    2；       
                                                    6，    d，    0；       
                                                    7,     e,     2；       
                                                    3，    f，    1；       
                                                    8，    g，    3；       
                                                    9，    h，    3；
                                                   10,     i,     0； 
现需要查出每父节点及子节点[*父节点为0表示根节点]：如下样式
                                                    ID            NAME    
                                                     1              a
                                                     --2            b
                                                     ----5          c
                                                     ----7          e
                                                     --3            f
                                                     ----8          g
                                                     ----9          h
                                                     6              d
                                                     10             i根节点没有"--"，一级子节点用"--"，二级子节点用“----”
疑难杂症，请教各位有经验的同行。我会加倍感激。。感激无穷。

Answer 1

try
select lpad(id,length(id)+level,'-'),name 
from mytab
connect by prior id=pid
start with pid=0

Answer 2

  select case when pid=1 then '--'||id 
                           when pid=2 then '---'||id
                           when pid=3 then '----'||id end pp,name   from nodeN 

Answer 3

http://topic.csdn.net/u/20090101/13/690d6377-9f50-46ee-8de8-16a0227070f6.html

Answer 4

http://topic.csdn.net/u/20090101/13/690d6377-9f50-46ee-8de8-16a0227070f6.html

Answer 5

。。感激无穷。 create or replace view v_funtiontree as
select level as vLevel,
       t.fmid as vFMID,
       t.pfmid as vPFMID,
       lpad('--',8*level-1)||t.fmname||' ' as vFMName
from SYS_FUNCTION t
start with t.pfmid is null
connect by nocycle  prior t.fmid= t.pfmid
order by t.fmid,level;