熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle10g中層次查詢簡介

2013-11-13 15:26:41  來源: Oracle 

  我們可以通過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
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.