Oracle 10g中新型層次查詢選項簡介

    我們可以通過START WITH . . . CONNECT BY . . .子句來實現SQL的 層次查詢，而Oracle 10g 為其添加許多了新的偽列。十多年以來，Oracle SQL 具有依照層次關系進行查詢的功能。例如，你可以指定一個起始條件，然后根據一個或多個連接條件來確定孩子行的內容。舉例來說，現在假設我有一個表，里面記錄了世界上的某些地區，其表結構如下：

create table hier

(

parent varchar2(30),

child varchar2(30)

);

insert into hier values(null,'Asia');

insert into hier values(null,'Australia');

insert into hier values(null,'Europe');

insert into hier values(null,'North America');

insert into hier values('Asia','China');

insert into hier values('Asia','Japan');

insert into hier values('Australia','New South Wales');

insert into hier values('New South Wales','Sydney');

insert into hier values('California','Redwood Shores');

insert into hier values('Canada','Ontario');

insert into hier values('China','Beijing');

insert into hier values('England','London');

insert into hier values('Europe','United Kingdom');

insert into hier values('Japan','Osaka');

insert into hier values('Japan','Tokyo');

insert into hier values('North America','Canada');

insert into hier values('North America','USA');

insert into hier values('Ontario','Ottawa');

insert into hier values('Ontario','Toronto');

insert into hier values('USA','California');

insert into hier values('United Kingdom','England'); 

    那么我們可以使用START WITH . . . CONNECT BY . . .從句將父級地區與孩子地區連接起來，并將其層次等級顯示出來。

column child format a40

select level,lpad(' ',level*3)||child child

from hier

start with parent is null

connect by prior child = parent;

LEVEL CHILD

---------- --------------------------

1 Asia

2 China

3 Beijing

2 Japan

3 Osaka

3 Tokyo

1 Australia

2 New South Wales

3 Sydney

1 Europe

2 United Kingdom

3 England

4 London

1 North America

2 Canada

3 Ontario

4 Ottawa

4 Toronto

2 USA

3 California

4 Redwood Shores

    自從Since Oracle 9i 開始，就可以通過 SYS_CONNECT_BY_PATH 函數實現將從父節點到當前行內容以“path”或者層次元素列表的形式顯示出來。 如下例所示：

column path format a50

select level,sys_connect_by_path(child,'/') path

from hier

start with parent is null

connect by prior child = parent;

LEVEL PATH

-------- --------------------------------------------

1 /Asia

2 /Asia/China

3 /Asia/China/Beijing

2 /Asia/Japan

3 /Asia/Japan/Osaka

3 /Asia/Japan/Tokyo

1 /Australia

2 /Australia/New South Wales

3 /Australia/New South Wales/Sydney

1 /Europe

2 /Europe/United Kingdom

3 /Europe/United Kingdom/England

4 /Europe/United Kingdom/England/London

1 /North America

2 /North America/Canada

3 /North America/Canada/Ontario

4 /North America/Canada/Ontario/Ottawa

4 /North America/Canada/Ontario/Toronto

2 /North America/USA

3 /North America/USA/California

4 /North America/USA/California/Redwood Shores

在 Oracle 10g 中，還有其他更多關于層次查詢的新特性 。例如，有的時候用戶更關心的是每個層次分支中等級最低的內容。那么你就可以利用偽列函數CONNECT_BY_ISLEAF來判斷當前行是不是葉子。如果是葉子就會在偽列中顯示“1”，如果不是葉子而是一個分支（例如當前內容是其他行的父親）就顯示“0”。下給出了一個關于這個函數使用的例子：

select connect_by_isleaf,sys_connect_by_path(child,'/') path

from hier

start with parent is null

connect by prior child = parent;

CONNECT_BY_ISLEAF PATH

---------------------------------- 

0 /Asia

0 /Asia/China

1 /Asia/China/Beijing

0 /Asia/Japan

1 /Asia/Japan/Osaka

1 /Asia/Japan/Tokyo

0 /Australia

0 /Australia/New South Wales

1 /Australia/New South Wales/Sydney

0 /Europe

0 /Europe/United Kingdom

0 /Europe/United Kingdom/England

1 /Europe/United Kingdom/England/London

0 /North America

0 /North America/Canada

0 /North America/Canada/Ontario

1 /North America/Canada/Ontario/Ottawa

1 /North America/Canada/Ontario/Toronto

0 /North America/USA

0 /North America/USA/California

1 /North America/USA/California/Redwood Shores 

[1] [2] 下一頁  

原文轉自：http://www.kjueaiud.com