start with connect by

1.create table  test (id number,p_id number,name varchar2(10));

2.insert into  test values (5,0,'zhangda');
   ```````
   commit;
 

3.select * from test;

        ID       P_ID NAME
---------- ---------- ----------
         1          5 zhangsan
         2          5 zhangsi
         3          1 zxiaosan
         4          3 zxxsan
         5          0 zhangda

4.What I want to get?

col name format a15
--All
select lpad(' ',level*2,' ')||name name,id, p_id
from test t
start  with t.P_ID=0
connect by prior t.id=t.p_id;

NAME                     ID       P_ID
---------------      ----------   ----------
  zhangda                 5          0
    zhangsan             1          5
      zxiaosan             3          1
        zxxsan               4           3
    zhangsi                 2           5



--The zhangsan' children
select lpad(' ',level*2,' ')||name name,id, p_id
from test t
start  with t.ID=1
connect by prior t.id=t.p_id;

NAME                     ID       P_ID
---------------       ---------- ----------
  zhangsan               1          5
    zxiaosan               3          1
      zxxsan                 4          3