单表查询树形结构的SQL语句

CREATE TABLE TBL_TEST
(
ID    NUMBER,
NAME  VARCHAR2(100 BYTE),
PID   NUMBER                                  DEFAULT 0
);
 
#插入测试数据：
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES(''''1'''',''''10'''',''''0'''');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES(''''2'''',''''11'''',''''1'''');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES(''''3'''',''''20'''',''''0'''');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES(''''4'''',''''12'''',''''1'''');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES(''''5'''',''''121'''',''''2'''');
 
#从Root往树末梢递归
SELECT * FROM TBL_TEST
start WITH id=1
connect BY prior id = pid
 
#从末梢往树ROOT递归
SELECT * FROM TBL_TEST
start WITH id=5
connect BY prior pid = id