???
注：prior pid = id 句說(shuō)明 pid是id的父節(jié)點(diǎn)，通過(guò)pid查詢id
?
四、sys_connect_by_path函數(shù)講解：
??? sys_connect_by_path函數(shù)主要作用是可以把一個(gè)父節(jié)點(diǎn)下的所有子節(jié)點(diǎn)通過(guò)某個(gè)字符進(jìn)行區(qū)分，在一個(gè)格中顯示
??? select
level
,sys_connect_by_path(
id
,
'/'
)
from
TBL_TEST
??? start
with
pid=
1
??? connect
by
prior
pid =
id
;
?
??? select
level
,sys_connect_by_path(pid,
'/'
)
from
TBL_TEST
??? start
with
pid=
1
??? connect
by
prior
pid =
id
;
?
??? 可以比較這兩段代碼的運(yùn)行結(jié)果與code example1的結(jié)果之間的差異，即可理解此函數(shù)用法
??? 或具體看下面的例子中對(duì)sys_connect_by_path的使用
?
?
?
?
==============================================================================================
附網(wǎng)上的一些資料，轉(zhuǎn)自
yangtingkun
start with ... connect by用法簡(jiǎn)介 sql有向圖問(wèn)題期待新解決方案
/*******************************************************************************
通過(guò)START WITH . . . CONNECT BY . . .子句來(lái)實(shí)現(xiàn)SQL的層次查詢.
自從Oracle 9i開始，可以通過(guò) SYS_CONNECT_BY_PATH 函數(shù)實(shí)現(xiàn)將父節(jié)點(diǎn)到當(dāng)前行內(nèi)容以“path”或者層次元素列表的形式顯示出來(lái)。
自從Oracle 10g 中，還有其他更多關(guān)于層次查詢的新特性 。例如，有的時(shí)候用戶更關(guān)心的是每個(gè)層次分支中等級(jí)最低的內(nèi)容。
那么你就可以利用偽列函數(shù)CONNECT_BY_ISLEAF來(lái)判斷當(dāng)前行是不是葉子。如果是葉子就會(huì)在偽列中顯示“1”，
如果不是葉子而是一個(gè)分支（例如當(dāng)前內(nèi)容是其他行的父親）就顯示“0”。
在Oracle 10g 之前的版本中，如果在你的樹中出現(xiàn)了環(huán)狀循環(huán)（如一個(gè)孩子節(jié)點(diǎn)引用一個(gè)父親節(jié)點(diǎn)），
Oracle 就會(huì)報(bào)出一個(gè)錯(cuò)誤提示：“ ORA-01436: CONNECT BY loop in user data”。如果不刪掉對(duì)父親的引用就無(wú)法執(zhí)行查詢操作。
而在 Oracle 10g 中，只要指定“NOCYCLE”就可以進(jìn)行任意的查詢操作。與這個(gè)關(guān)鍵字相關(guān)的還有一個(gè)偽列——CONNECT_BY_ISCYCLE，
如果在當(dāng)前行中引用了某個(gè)父親節(jié)點(diǎn)的內(nèi)容并在樹中出現(xiàn)了循環(huán)，那么該行的偽列中就會(huì)顯示“1”，否則就顯示“0”。
The start with .. connect by clause can be used to select data that has a hierarchical relationship
(usually some sort of parent->child, boss->employee or thing->parts).
It is also being used when an sql execution plan is explained.
syntax:
select ... [start with initial-condition] connect by [nocycle] recurse-condition
level
With level it is possible to show the level in the hierarchical relation of all the data.
--oracle 9i
sys_connect_by_path
With sys_connect_by_path it is possible to show the entire path from the top level down to the 'actual' child.
--oracle 10g
connect_by_root
connect_by_root is a new operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries.??
connect_by_is_leaf
connect_by_isleaf is a new operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries.
connect_by_iscycle
connect_by_is_cycle is a new operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries.
--start with ... connect by ... 的處理機(jī)制
How must a start with ... connect by select statement be read and interpreted?
If Oracle encounters such an SQL statement, it proceeds as described in the following pseude code.
for rec in (select * from some_table) loop
??if FULLFILLS_START_WITH_CONDITION(rec) then
? ? RECURSE(rec, rec.child);
??end if;
end loop;
procedure RECURSE (rec in MATCHES_SELECT_STMT, new_parent IN field_type) is
??begin
??APPEND_RESULT_LIST(rec);? ???
??for rec_recurse in (select * from some_table) loop
? ? if FULLFILLS_CONNECT_BY_CONDITION(rec_recurse.child, new_parent) then
? ?? ?RECURSE(rec_recurse,rec_recurse.child);
? ? end if;
??end loop;
end procedure RECURSE;
created by zhouwf0726 2006.
*******************************************************************************/
--創(chuàng)建測(cè)試表，增加測(cè)試數(shù)據(jù)
create table test(superid varchar2(20),id varchar2(20));
insert into test values('0','1');
insert into test values('0','2');
insert into test values('1','11');
insert into test values('1','12');
insert into test values('2','21');
insert into test values('2','22');
insert into test values('11','111');
insert into test values('11','112');
insert into test values('12','121');
insert into test values('12','122');
insert into test values('21','211');
insert into test values('21','212');
insert into test values('22','221');
insert into test values('22','222');
commit;
--層次查詢示例
select level||'層',lpad(' ',level*5)||id id
from test
start with superid = '0' connect by prior id=superid;
select level||'層',connect_by_isleaf,lpad(' ',level*5)||id id
from test
start with superid = '0' connect by prior id=superid;
--給出兩個(gè)以前在"數(shù)據(jù)庫(kù)字符串分組相加之四"中的例子來(lái)理解start with ... connect by ...
--功能：實(shí)現(xiàn)按照superid分組,把id用";"連接起來(lái)
--實(shí)現(xiàn)：以下兩個(gè)例子都是通過(guò)構(gòu)造2個(gè)偽列來(lái)實(shí)現(xiàn)connect by連接的。
/*------method one------*/
select superid,ltrim(max(sys_connect_by_path(id,';')),';') from(
select superid,id,row_number() over(partition by superid order by superid) id1,
row_number() over(order by superid) + dense_rank() over(order by superid) id2
from test
)
start with id1=1 connect by prior id2 = id2 -1
group by superid order by superid;
/*------method two------*/
select distinct superid,ltrim(first_value(id) over(partition by superid order by l desc),';')
from(
select superid,level l,sys_connect_by_path(id,';') id
from(
select superid,id,superid||rownum parent_rn,superid||to_char(rownum-1) rn
from test
)
connect by prior parent_rn = rn
);
--下面的例子實(shí)現(xiàn)把一個(gè)整數(shù)的各個(gè)位上的數(shù)字相加,通過(guò)這個(gè)例子我們?cè)俅卫斫鈉onnect by.
create or replace function f_digit_add(innum integer) return number
is
outnum integer;
begin
? ? ? ? if innum<0 then
? ? ? ? ? ? ? ? return 0;
? ? ? ? end if;
? ? ? ? select sum(nm) into outnum from(
? ? ? ? ? ? ? ? select substr(innum,rownum,1) nm from dual connect by rownum<length(innum)
? ? ? ? );
? ? ? ? return outnum;
end f_digit_add;
/
select f_digit_add(123456) from dual;
/**********************************************************************************
***********************************************************************************
下面是關(guān)于SQL解決有向圖問(wèn)題，在這個(gè)例子中作者提到的錯(cuò)誤
select * from fares connect by prior arrive = depart start with depart = 'LHR';
ERROR:
ORA-01436: CONNECT BY loop in user data
在oracle10g以上版本可以利用connect by的nocycle參數(shù)來(lái)解。有興趣的朋友研究用一條sql實(shí)現(xiàn)有向圖問(wèn)題!
***********************************************************************************
**********************************************************************************/
一個(gè)常見的高級(jí)計(jì)算機(jī)科學(xué)問(wèn)題可以在“有向圖”的范疇之下描述。有向圖是由一組向量和邊所連接的一組有限的節(jié)點(diǎn)。
例如，一個(gè)節(jié)點(diǎn)可以想象為一座“城市”，而每個(gè)向量可以想象為兩座城市間的一個(gè)“航線”。
有很多算法和論文講到如何解決每種可能路線的遍歷問(wèn)題以及尋找最短路徑或者最小代價(jià)路徑的問(wèn)題。
這些算法中大部分都是過(guò)程化的，或者是使用遞歸方面來(lái)解決的。然而 SQL 的聲明性語(yǔ)言使得解決復(fù)雜的有向圖問(wèn)題更加容易，
而且不需要很多代碼。
讓我們以兩座城市之間的航線為例子，創(chuàng)建一個(gè)表保存一些假想數(shù)據(jù)：
create table airports
(
? ? code char(3) constraint airports_pk primary key,
? ? description varchar2(200)
);
insert into airports values ('LHR','London Heathrow, UK');
insert into airports values ('JFK','New York-Kennedy, USA');
insert into airports values ('GRU','Sao Paulo, Brazil');
create table fares
(
? ? depart char(3),
? ? arrive char(3),
? ? price number,
? ? constraint fares_pk primary key (depart,arrive),
? ? constraint fares_depart_fk foreign key (depart) references airports,
? ? constraint fares_arrive_fk foreign key (arrive) references airports
);
insert into fares values('LHR','JFK',700);
insert into fares values('JFK','GRU',600);
insert into fares values('LHR','GRU',1500);
insert into fares values('GRU','LHR',1600);
不能使用CONNECT BY 語(yǔ)法來(lái)解決如何從倫敦到圣保羅，因?yàn)樵趫D中有數(shù)據(jù)產(chǎn)生一個(gè)環(huán)（從圣保羅飛回）：
select * from fares connect by prior arrive = depart start with depart = 'LHR';
ERROR:
ORA-01436: CONNECT BY loop in user data
要解決有向圖問(wèn)題，我們需要?jiǎng)?chuàng)建一個(gè)臨時(shí)表來(lái)保存兩個(gè)節(jié)點(diǎn)之間所有可能的路徑。我們必須注意不復(fù)制已經(jīng)處理過(guò)的路徑，
而且在這種情況下，我們不想路徑走回開始處的同一個(gè)地點(diǎn)。我還希望跟蹤到達(dá)目的地所需航程的數(shù)目，以及所走路線的描述。
臨時(shí)表使用以下腳本創(chuàng)建：
create global temporary table faretemp
(
? ? depart? ?? ?char(3),
? ? arrive? ?? ?char(3),
? ? hops? ?? ???integer,
? ? route? ?? ? varchar2(30),
? ? price? ?? ? number,
? ? constraint faretemp_pk primary key (depart,arrive)
);
一個(gè)簡(jiǎn)單的視圖可以在稍微簡(jiǎn)化這個(gè)例子中使用的代碼。視圖可以根據(jù) fares 表中的單個(gè)航程計(jì)算從 faretemp 表中的一個(gè)路徑
到達(dá)一下一個(gè)航程的數(shù)據(jù)：
create or replace view nexthop
as
? ? select src.depart,
? ?? ?? ???dst.arrive,
? ?? ?? ???src.hops+1 hops,
? ?? ?? ???src.route||','||dst.arrive route,
? ?? ?? ???src.price + dst.price price
? ?? ?from faretemp src,fares dst
? ???where src.arrive = dst.depart
? ?? ? and dst.arrive != src.depart;
/
show errors;
這個(gè)算法相當(dāng)簡(jiǎn)單。首先，使用 fares 表中的數(shù)據(jù)填充 faretemp 表，作為初始的航程。然后，取到我們剛才插入的所有數(shù)據(jù)，
使用它們建立所有可能的二航程（two-hop）路徑。重復(fù)這一過(guò)程，直至在兩個(gè)節(jié)點(diǎn)之間創(chuàng)建了新路徑。
循環(huán)過(guò)程將在節(jié)點(diǎn)間所有可能的路徑都被描述之后退出。如果我們只對(duì)某個(gè)開始條件感興趣，
那么我們還可以限制第一次的插入從而減少裝載數(shù)據(jù)的量。下面是發(fā)現(xiàn)路徑的代碼：
truncate table faretemp;
begin
? ? -- initial connections
? ? insert into faretemp
? ???select depart,arrive,1,depart||','||arrive,price from fares;
? ? while sql%rowcount > 0 loop
? ?? ???insert into faretemp
? ?? ?? ?? ?select depart,arrive,hops,route,price from nexthop
? ?? ?? ?? ? where (depart,arrive)
? ?? ?? ?? ?? ?? ? not in (select depart,arrive from faretemp);
? ? end loop;
end;
/
show errors;
select * from faretemp order by depart,arrive;
可以在表 A 中查看輸出。
前面的數(shù)據(jù)有一個(gè)小問(wèn)題。數(shù)據(jù)是點(diǎn)之間最短路徑（最小航程數(shù)）的集合。然而，從倫敦到圣保羅的航程卻不是最便宜的一個(gè)。
要解決最便宜的費(fèi)用問(wèn)題，需要對(duì)我們的循環(huán)做一個(gè)改進(jìn)，當(dāng)在一個(gè)航程中發(fā)現(xiàn)一個(gè)更便宜的路線時(shí)使用這個(gè)路線代替原來(lái)的路線。
修改后的代碼如下：
truncate table faretemp;
declare
? ? l_count integer;
begin
? ? -- initial connections
? ? insert into faretemp
? ?? ???select depart,arrive,1,depart||','||arrive,price from fares;
? ? l_count := sql%rowcount;
? ? while l_count > 0 loop
? ?? ???update faretemp
? ?? ?? ???set (hops,route,price) =
? ?? ?? ?? ???(select hops,route,price from nexthop
? ?? ?? ?? ?? ? where depart = faretemp.depart
? ?? ?? ?? ?? ?? ?and arrive = faretemp.arrive)
? ?? ?? ?where (depart,arrive) in
? ?? ?? ?? ? (select depart,arrive from nexthop
? ?? ?? ?? ?? ?where price < faretemp.price);
? ?? ???l_count := sql%rowcount;
? ?? ???insert into faretemp
? ?? ?? ?? ?select depart,arrive,hops,route,price from nexthop
? ?? ?? ?? ? where (depart,arrive)
? ?? ?? ?? ?? ?not in (select depart,arrive from faretemp);
? ?? ???l_count := l_count + sql%rowcount;
? ? end loop;
end;
/
show errors;
select * from faretemp order by depart,arrive;
可能在表 B中查看輸出。
算法發(fā)現(xiàn)LHR、JFK、GRU 路線比 LHR、GRU 路線便宜，所以用前者代替了后者。循環(huán)將在沒(méi)有更便宜的費(fèi)用，
并且沒(méi)有其它可能路線時(shí)退出。
?
------------------------------------------------------------------------------------------
?
SYS_CONNECT_BY_PATH 函數(shù)
自從Since Oracle 9i 開始，就可以通過(guò) SYS_CONNECT_BY_PATH 函數(shù)實(shí)現(xiàn)將從父節(jié)點(diǎn)到當(dāng)前行內(nèi)容以“path”或者層次元素列表的形式顯示出來(lái)。 如下例所示：
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
CONNECT_BY_ISLEAF偽列
在 Oracle 10g 中，還有其他更多關(guān)于層次查詢的新特性 。例如，有的時(shí)候用戶更關(guān)心的是每個(gè)層次分支中等級(jí)最低的內(nèi)容。那么你就可以利用偽列函數(shù)CONNECT_BY_ISLEAF來(lái)判斷當(dāng)前行是不是葉子。如果是葉子就會(huì)在偽列中顯示“1”，如果不是葉子而是一個(gè)分支（例如當(dāng)前內(nèi)容是其他行的父親）就顯示“0”。下給出了一個(gè)關(guān)于這個(gè)函數(shù)使用的例子：
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
?CONNECT_BY_ROOT偽列
在Oracle 10g 中還有一個(gè)新操作——CONNECT_BY_ROOT。 它用在列名之前用于返回當(dāng)前層的根節(jié)點(diǎn)。如下面的例子，我可以顯示出層次結(jié)構(gòu)表中當(dāng)前行數(shù)據(jù)所對(duì)應(yīng)的最高等級(jí)節(jié)點(diǎn)的內(nèi)容。
select connect_by_root ,sys_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
CONNECT_BY_ISCYCLE偽列
在Oracle 10g 之前的版本中，如果在你的樹中出現(xiàn)了環(huán)狀循環(huán)（如一個(gè)孩子節(jié)點(diǎn)引用一個(gè)父親節(jié)點(diǎn)），Oracle 就會(huì)報(bào)出一個(gè)錯(cuò)誤提示：“ ORA-01436: CONNECT BY loop in user data”。如果不刪掉對(duì)父親的引用就無(wú)法執(zhí)行查詢操作。而在 Oracle 10g 中，只要指定“NOCYCLE”就可以進(jìn)行任意的查詢操作。與這個(gè)關(guān)鍵字相關(guān)的還有一個(gè)偽列——CONNECT_BY_ISCYCLE， 如果在當(dāng)前行中引用了某個(gè)父親節(jié)點(diǎn)的內(nèi)容并在樹中出現(xiàn)了循環(huán)，那么該行的偽列中就會(huì)顯示“1”，否則就顯示“0”。如下例所示：
create table hier2
(
parent number,
child number
);
insert into hier2 values(null,1);
insert into hier2 values(1,2);
insert into hier2 values(2,3);
insert into hier2 values(3,1);
select connect_by_iscycle,sys_connect_by_path(child,"/") path
from hier2
start with parent is null
connect by nocycle prior child = parent;
CONNECT_BY_ISCYCLE PATH
------------------ -------
0 /1
0 /1/2
1 /1/2/3 ?