我們可以通過START WITCNECT BY子句來實現SQL的 層次查詢而Oracle g 為其添加許多了新的偽列
我們可以通過START WITH CONNECT BY 子句來實現SQL的 層次查詢而Oracle g 為其添加許多了新的偽列十多年以來Oracle SQL 具有依照層次關系進行查詢的
功能例如你可以指定一個起始條件然後根據一個或多個連接條件來確定孩子行的內容舉例來說現在假設我有一個表裡面記錄了世界上的某些地區其表結構如下
create table hier
(
parent varchar()
child varchar()
);
insert into hier values(nullAsia);
insert into hier values(nullAustralia);
insert into hier values(nullEurope);
insert into hier values(nullNorth America);
insert into hier values(AsiaChina);
insert into hier values(AsiaJapan);
insert into hier values(AustraliaNew South Wales);
insert into hier values(New South WalesSydney);
insert into hier values(CaliforniaRedwood Shores);
insert into hier values(CanadaOntario);
insert into hier values(ChinaBeijing);
insert into hier values(EnglandLondon);
insert into hier values(EuropeUnited Kingdom);
insert into hier values(JapanOsaka);
insert into hier values(JapanTokyo);
insert into hier values(North AmericaCanada);
insert into hier values(North AmericaUSA);
insert into hier values(OntarioOttawa);
insert into hier values(OntarioToronto);
insert into hier values(USACalifornia);
insert into hier values(United KingdomEngland);
那麼我們可以使用START WITH CONNECT BY 從句將父級地區與孩子地區連接起來並將其層次等級顯示出來
column child format a
select levellpad( level*)||child child
from hier
start with parent is null
connect by prior child = parent;
LEVEL CHILD
Asia
China
Beijing
Japan
Osaka
Tokyo
Australia
New South Wales
Sydney
Europe
United Kingdom
England
London
North America
Canada
Ontario
Ottawa
Toronto
USA
California
Redwood Shores
自從Since Oracle i 開始就可以通過 SYS_CONNECT_BY_PATH 函數實現將從父節點到當前行內容以path或者層次元素列表的形式顯示出來 如下例所示
column path format a
select levelsys_connect_by_path(child/) path
from hier
start with parent is null
connect by prior child = parent;
LEVEL PATH
/Asia
/Asia/China
/Asia/China/Beijing
/Asia/Japan
/Asia/Japan/Osaka
/Asia/Japan/Tokyo
/Australia
/Australia/New South Wales
/Australia/New South Wales/Sydney
/Europe
/Europe/United Kingdom
/Europe/United Kingdom/England
/Europe/United Kingdom/England/London
/North America
/North America/Canada
/North America/Canada/Ontario
/North America/Canada/Ontario/Ottawa
/North America/Canada/Ontario/Toronto
/North America/USA
/North America/USA/California
/North America/USA/California/Redwood Shores
在 Oracle
g 中
還有其他更多關於層次查詢的新特性
例如
有的時候用戶更關心的是每個層次分支中等級最低的內容
那麼你就可以利用偽列函數CONNECT_BY_ISLEAF來判斷當前行是不是葉子
如果是葉子就會在偽列中顯示
如果不是葉子而是一個分支（例如當前內容是其他行的父親）就顯示
下給出了一個關於這個函數使用的例子
select connect_by_isleafsys_connect_by_path(child/) path
from hier
start with parent is null
connect by prior child = parent;
CONNECT_BY_ISLEAF PATH
/Asia
/Asia/China
/Asia/China/Beijing
/Asia/Japan
/Asia/Japan/Osaka
/Asia/Japan/Tokyo
/Australia
/Australia/New South Wales
/Australia/New South Wales/Sydney
/Europe
/Europe/United Kingdom
/Europe/United Kingdom/England
/Europe/United Kingdom/England/London
/North America
/North America/Canada
/North America/Canada/Ontario
/North America/Canada/Ontario/Ottawa
/North America/Canada/Ontario/Toronto
/North America/USA
/North America/USA/California
/North America/USA/California/Redwood Shores
自從Since Oracle
i 開始
就可以通過 SYS_CONNECT_BY_PATH 函數實現將從父節點到當前行內容以
path
或者層次元素列表的形式顯示出來
如下例所示
column path format a
select levelsys_connect_by_path(child/) path
from hier
start with parent is null
connect by prior child = parent;
LEVEL PATH
/Asia
/Asia/China
/Asia/China/Beijing
/Asia/Japan
/Asia/Japan/Osaka
/Asia/Japan/Tokyo
/Australia
/Australia/New South Wales
/Australia/New South Wales/Sydney
/Europe
/Europe/United Kingdom
/Europe/United Kingdom/England
/Europe/United Kingdom/England/London
/North America
/North America/Canada
/North America/Canada/Ontario
/North America/Canada/Ontario/Ottawa
/North America/Canada/Ontario/Toronto
/North America/USA
/North America/USA/California
/North America/USA/California/Redwood Shores
在 Oracle g 中還有其他更多關於層次查詢的新特性 例如有的時候用戶更關心的是每個層次分支中等級最低的內容那麼你就可以利用偽列函數CONNECT_BY_ISLEAF來判斷當前行是不是葉子如果是葉子就會在偽列中顯示如果不是葉子而是一個分支（例如當前內容是其他行的父親）就顯示下給出了一個關於這個函數使用的例子
select connect_by_isleafsys_connect_by_path(child/) path
from hier
start with parent is null
connect by prior child = parent;
CONNECT_BY_ISLEAF PATH
/Asia
/Asia/China
/Asia/China/Beijing
/Asia/Japan
/Asia/Japan/Osaka
/Asia/Japan/Tokyo
/Australia
/Australia/New South Wales
/Australia/New South Wales/Sydney
/Europe
/Europe/United Kingdom
/Europe/United Kingdom/England
/Europe/United Kingdom/England/London
/North America
/North America/Canada
/North America/Canada/Ontario
/North America/Canada/Ontario/Ottawa
/North America/Canada/Ontario/Toronto
/North America/USA
/North America/USA/California
/North America/USA/California/Redwood Shores
在Oracle g 中還有一個新操作——CONNECT_BY_ROOT 它用在列名之前用於返回當前層的根節點如下面的例子我可以顯示出層次結構表中當前行數據所對應的最高等級節點的內容
select connect_by_root childsys_connect_by_path(child/) path
from hier
start with parent is null
connect by prior child = parent;
CONNECT_BY_ROOT PATH
Asia /Asia
Asia /Asia/China
Asia /Asia/China/Beijing
Asia /Asia/Japan
Asia /Asia/Japan/Osaka
Asia /Asia/Japan/Tokyo
Australia /Australia
Australia /Australia/New South Wales
Australia /Australia/New South Wales/Sydney
Europe /Europe
Europe /Europe/United Kingdom
Europe /Europe/United Kingdom/England
Europe /Europe/United Kingdom/England/London
North America /North America
North America /North America/Canada
North America /North America/Canada/Ontario
North America /North America/Canada/Ontario/Ottawa
North America /North America/Canada/Ontario/Toronto
North America /North America/USA
North America /North America/USA/California
North America /North America/USA/California/Redwood Shores
在Oracle g 之前的版本中如果在你的樹中出現了環狀循環（如一個孩子節點引用一個父親節點）Oracle 就會報出一個錯誤提示 ORA: CONNECT BY loop in user data如果不刪掉對父親的引用就無法執行查詢操作而在 Oracle g 中只要指定NOCYCLE就可以進行任意的查詢操作與這個關鍵字相關的還有一個偽列——CONNECT_BY_ISCYCLE 如果在當前行中引用了某個父親節點的內容並在樹中出現了循環那麼該行的偽列中就會顯示否則就顯示如下例所示
create table hier
(
parent number
child number
);
insert into hier values(null);
insert into hier values();
insert into hier values();
insert into hier values();
select connect_by_iscyclesys_connect_by_path(child/) path
from hier
start with parent is null
connect by nocycle prior child = parent;
CONNECT_BY_ISCYCLE PATH
/
//
///
From:http://tw.wingwit.com/Article/program/Oracle/201311/16716.html
