大致寫法：select * from some_table [where 條件1] connect by [條件2] start with [條件3];其中 connect by 與 start with 語(yǔ)句擺放的先后順序不影響查詢的結(jié)果，[where 條件1]可以不需要。

另外一種寫法：select * from some_table connect by [條件2][條件1]start with [條件3][條件1];

[where 條件1]、[條件2]、[條件3]各自作用的范圍都不相同：

[where 條件1]是在根據(jù)“connect by [條件2] start with [條件3]”選擇出來(lái)的記錄中進(jìn)行過(guò)濾，是針對(duì)單條記錄的過(guò)濾， 不會(huì)考慮樹的結(jié)構(gòu)；

[條件2]指定構(gòu)造樹的條件，以及對(duì)樹分支的過(guò)濾條件，在這里執(zhí)行的過(guò)濾會(huì)把符合條件的記錄及其下的所有子節(jié)點(diǎn)都過(guò)濾掉；

[條件3]限定作為搜索起始點(diǎn)的條件，如果是自上而下的搜索則是限定作為根節(jié)點(diǎn)的條件，如果是自下而上的搜索則是限定作為葉子節(jié)點(diǎn)的條件；

示例：

假如有如下結(jié)構(gòu)的表：some_table(id,p_id,name)，其中p_id保存父記錄的id。

select * from some_table t where t.id!=123 connect by prior t.p_id=t.id and t.p_id!=321 start with t.p_id=33 or t.p_id=66;

對(duì)prior的說(shuō)明：

prior存在于[條件2]中，可以不要，不要的時(shí)候只能查找到符合“start with [條件3]”的記錄，不會(huì)在尋找這些記錄的子節(jié)點(diǎn)。要的時(shí)候有兩種寫法：connect by prior t.p_id=t.id或connect by t.p_id=prior t.id，前一種寫法表示采用自上而下的搜索方式(先找父節(jié)點(diǎn)然后找子節(jié)點(diǎn))，后一種寫法表示采用自下而上的搜索方式(先找葉子節(jié)點(diǎn)然后找父節(jié)點(diǎn))。

自從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”。

在Oracle10g 之前的版本中，如果在你的樹中出現(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í)行查詢操作。而在Oracle10g 中，只要指定“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”。

--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.

--創(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);

return outnum;

end f_digit_add;

/

select f_digit_add(123456) from dual;

創(chuàng)作挑戰(zhàn)賽新人創(chuàng)作獎(jiǎng)勵(lì)來(lái)咯，堅(jiān)持創(chuàng)作打卡瓜分現(xiàn)金大獎(jiǎng)

總結(jié)

以上是生活随笔為你收集整理的oracle中 start with,Oracle中connect by...start with...的使用的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。