使用oracle 9i 裡的層級查詢（Hierarchical Queries）實現樹狀表結構的遞迴資料查詢

先來看2張圖：

這個圖展示了ORACLE查詢一個層級樹時的返回順序,從Root節點開始返回該Root的一個子節點,
然後再返回該子節點的子節點,一直到葉結點.
在下一個有子節點的節點被找到並且該節點不是葉結idan以前,層級會逐漸增加,並且Oracle返回該節點
當Root節點的第一個節點的所有的"子孫(descendants)"節點被返回後,Oracle才會去尋找下一個子節點,這個過程將一直持續.(深度優先)
(This process continues, with Oracle returning to the root row for the next child
only when all of the "descendants" of the first child row have been returned.)

這個圖說明了 Hierarchical Queries 的文法
1.以指定的層級根不開始
2.由指定的層級中父子關係串連,在一個層級查詢中,條件運算式必須使用PRIOR運算子限定查取父級資料.
比喻:
... PRIOR expr = expr
or
... expr = PRIOR expr
如果CONNECT BY是符合條件,那麼只有一個條件限定PRIOR運算子.（例子將在下面給出）
先建表,插入一些資料:

Create Table TreeDemo (
    NodeID  NVARCHAR2(50) Default '' Not Null,
    Parent_NodeID  NVARCHAR2(50) Default '' Not Null,
    NodeName  NVARCHAR2(100) Default '' Not Null
, Constraint PK_TreeDemo Primary Key (NodeID)
);
insert into TreeDemo values('A',0,'A_1');
insert into TreeDemo values('A01','A','A_01');
insert into TreeDemo values('A02','A','A_02');
insert into TreeDemo values('A03','A01','A_03');
insert into TreeDemo values('A04','A01','A_02');

用這LPAD和SYS_CONNECT_BY_PATH函數把樹型結構構造出來
LPAD 函數將左邊的字串填充特定的字元空格
SYS_CONNECT_BY_PATH ORACLE樹結構專用函數

SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(NodeName, '/') "TREE"
FROM TreeDemo
start with NodeID = 'A04' 
connect by prior NodeID = Parent_NodeID ;

結果為:

TREE
---------------------
 /A_02
   /A_02/A_01
     /A_02/A_01/A_1

在使用 start with ....connect by prio 查詢前需要注意一點:
如果 prio 後面的關係弄反了查出來的結果是不一樣的.
看看下面2條查詢語句的結果:

1.這種查詢將把當前合格節點作為根節點遍曆查詢其所屬子節點:

select * from TreeDemo
start with NodeID = 'A01' 
connect by prior NodeID = Parent_NodeID ;NODEID    PARENT_NODEID   NODENAME
--------- -------------  --------- 
A01      A          A_01
A03       A01          A_03
A04      A01          A_03    

2.這種查詢將由葉結點遍曆查詢到根節點:start with NodeID = 'A04' 
connect by prior Parent_NodeID = NodeID ;NODEID    PARENT_NODEID   NODENAME
--------- -------------  --------- 
A04      A01          A_02
A01       A          A_01
A         0          A_1

3.帶條件過濾:

select * from TreeDemo
start with NodeID = 'A04' 
connect by PARENT_NODEID <>'0' and prior Parent_NodeID = NodeID ;NODEID    PARENT_NODEID   NODENAME
--------- -------------  --------- 
A04      A01          A_02
A01       A          A_01

也可在查詢裡加上排序條件,可以看出在ORACLE裡處理這種樹型資料表是很方便的,在MS SQL 裡可能要寫函數處理.