我们可以通过START WITH . . . CONNECT BY . . .子句来实现SQL的 层次查询，而Oracle 10g 为其添加许多了新的伪列。十多年以来，Oracle SQL 具有依照层次关系进行查询的功能。例如，你可以指定一个起始条件，然后根据一个或多个连接条件来确定孩子行的内容。举例来说，现在假设我有一个表，里面记录了世界上的某些地区，其表结构如下： `x*=jkT\(
z)]>gR>
create table hier <H8k`S=sN
( Z[& ]Tuw
parent varchar2(30), S,A8!Up:
child varchar2(30) Lu[]i R
); HWyY
`_nt. A
insert into hier values(null,'Asia'); Z>sI1)<
insert into hier values(null,'Australia'); /. PYrW
insert into hier values(null,'Europe'); a(n!^X5 z
insert into hier values(null,'North America'); ^PoXM:"W-
insert into hier values('Asia','China'); 'DwxfbK/\
insert into hier values('Asia','Japan'); sA dn|Q~
insert into hier values('Australia','New South Wales'); 3ztD)Q
insert into hier values('New South Wales','Sydney'); V_oH|k*
insert into hier values('California','Redwood Shores'); `X.p,t9U
insert into hier values('Canada','Ontario'); < %1 [ EH.
insert into hier values('China','Beijing'); Dj8{_u@B
insert into hier values('England','London'); NGN_'.
insert into hier values('Europe','United Kingdom'); ^3fJpD
insert into hier values('Japan','Osaka'); gOqUnVN /
insert into hier values('Japan','Tokyo'); "O^Ah\ }
insert into hier values('North America','Canada'); E^RzQ W8l#
insert into hier values('North America','USA'); ][pV&jwx
insert into hier values('Ontario','Ottawa'); K^:KDei0
insert into hier values('Ontario','Toronto'); L[ !n__}%
insert into hier values('USA','California'); KYR&_ 8
insert into hier values('United Kingdom','England'); _JL<F6 `g
2eG@6
那么我们可以使用START WITH . . . CONNECT BY . . .从句将父级地区与孩子地区连接起来，并将其层次等级显示出来。 T \9 i,;
Okll;
column child format a40 /CnfI9`aL
select level,lpad(' ',level*3)||child child G:xCB
from hier ^o4 p oc
start with parent is null $I5oVYArD
connect by prior child = parent; #V Z@ 4V4
?o_+Cwwi||
LEVEL CHILD uDWj k
---------- -------------------------- m %1=?G
1 Asia ^;,bsmo8
2 China o d xhm_+
3 Beijing ~K(oL"
2 Japan c+ y^{.Pq`
3 Osaka ]cwe+`4di
3 Tokyo J# a6YGZT
1 Australia )*!e!
2 New South Wales rjjG/Xo#xh
3 Sydney M:iW`/Z@y
1 Europe Z =hYacRU
2 United Kingdom _X<Gt29=
3 England r`Q U6-
4 London kV?>olC
1 North America -\dE-C1
2 Canada Y9N_/"S
3 Ontario TRLt8:
4 Ottawa Ln8eD!]
4 Toronto B>| P 8M*
2 USA @oEp X) UQ
3 California c4^ >hpF;
4 Redwood Shores 7[g bw
z)]>gR>
create table hier <H8k`S=sN
( Z[& ]Tuw
parent varchar2(30), S,A8!Up:
child varchar2(30) Lu[]i R
); HWyY
`_nt. A
insert into hier values(null,'Asia'); Z>sI1)<
insert into hier values(null,'Australia'); /. PYrW
insert into hier values(null,'Europe'); a(n!^X5 z
insert into hier values(null,'North America'); ^PoXM:"W-
insert into hier values('Asia','China'); 'DwxfbK/\
insert into hier values('Asia','Japan'); sA dn|Q~
insert into hier values('Australia','New South Wales'); 3ztD)Q
insert into hier values('New South Wales','Sydney'); V_oH|k*
insert into hier values('California','Redwood Shores'); `X.p,t9U
insert into hier values('Canada','Ontario'); < %1 [ EH.
insert into hier values('China','Beijing'); Dj8{_u@B
insert into hier values('England','London'); NGN_'.
insert into hier values('Europe','United Kingdom'); ^3fJpD
insert into hier values('Japan','Osaka'); gOqUnVN /
insert into hier values('Japan','Tokyo'); "O^Ah\ }
insert into hier values('North America','Canada'); E^RzQ W8l#
insert into hier values('North America','USA'); ][pV&jwx
insert into hier values('Ontario','Ottawa'); K^:KDei0
insert into hier values('Ontario','Toronto'); L[ !n__}%
insert into hier values('USA','California'); KYR&_ 8
insert into hier values('United Kingdom','England'); _JL<F6 `g
2eG@6
那么我们可以使用START WITH . . . CONNECT BY . . .从句将父级地区与孩子地区连接起来，并将其层次等级显示出来。 T \9 i,;
Okll;
column child format a40 /CnfI9`aL
select level,lpad(' ',level*3)||child child G:xCB
from hier ^o4 p oc
start with parent is null $I5oVYArD
connect by prior child = parent; #V Z@ 4V4
?o_+Cwwi||
LEVEL CHILD uDWj k
---------- -------------------------- m %1=?G
1 Asia ^;,bsmo8
2 China o d xhm_+
3 Beijing ~K(oL"
2 Japan c+ y^{.Pq`
3 Osaka ]cwe+`4di
3 Tokyo J# a6YGZT
1 Australia )*!e!
2 New South Wales rjjG/Xo#xh
3 Sydney M:iW`/Z@y
1 Europe Z =hYacRU
2 United Kingdom _X<Gt29=
3 England r`Q U6-
4 London kV?>olC
1 North America -\dE-C1
2 Canada Y9N_/"S
3 Ontario TRLt8:
4 Ottawa Ln8eD!]
4 Toronto B>| P 8M*
2 USA @oEp X) UQ
3 California c4^ >hpF;
4 Redwood Shores 7[g bw
自从Since Oracle 9i 开始，就可以通过 SYS_CONNECT_BY_PATH 函数实现将从父节点到当前行内容以“path”或者层次元素列表的形式显示出来。 如下例所示： Ef.k@Y0
LM^7t } '
column path format a50 SS> ISBv
select level,sys_connect_by_path(child,'/') path B;~& =80
from hier ek< }cX
start with parent is null r}@>3 Nwe
connect by prior child = parent; - 9j(e<
zMPiQ@Mo
LEVEL PATH fn< jHS!{!
-------- -------------------------------------------- '5k!3i0U
1 /Asia u"VSV{[
2 /Asia/China =_*P~ K s
3 /Asia/China/Beijing `>yAx)fZ
2 /Asia/Japan 6/Fyz u`
3 /Asia/Japan/Osaka 6[\^8Ll
3 /Asia/Japan/Tokyo [N}G %n
1 /Australia o/_'{a!G+N
2 /Australia/New South Wales | K4M1 7s
3 /Australia/New South Wales/Sydney {#"R0 fn
1 /Europe w4H$ O5ZJo
2 /Europe/United Kingdom S-iy9K~`-x
3 /Europe/United Kingdom/England $I"MW6J ~
4 /Europe/United Kingdom/England/London k.{\|NS
1 /North America y"i \P
2 /North America/Canada \V\CWIBj
3 /North America/Canada/Ontario Hcxhk`
4 /North America/Canada/Ontario/Ottawa +3j;
4 /North America/Canada/Ontario/Toronto *(U?GQ (a
2 /North America/USA 9)Y :[Gx
3 /North America/USA/California CQ CkX!O/o
4 /North America/USA/California/Redwood Shores 5I r1)IFt
>{!o B 7
在 Oracle 10g 中，还有其他更多关于层次查询的新特性 。例如，有的时候用户更关心的是每个层次分支中等级最低的内容。那么你就可以利用伪列函数CONNECT_BY_ISLEAF来判断当前行是不是叶子。如果是叶子就会在伪列中显示“1”，如果不是叶子而是一个分支（例如当前内容是其他行的父亲）就显示“0”。下给出了一个关于这个函数使用的例子： |,+72aI<
:}JX -F#
select connect_by_isleaf,sys_connect_by_path(child,'/') path R0e#s_9l?4
from hier {9YWSd%i
start with parent is null Ixjje/+
connect by prior child = parent; @:qhbe|m
21nT":f\F
CONNECT_BY_ISLEAF PATH lT;"chlp
---------------------------------- ------------ yn|R5-$i D
0 /Asia ?7'2Z~n#W
0 /Asia/China I Okj:j
1 /Asia/China/Beijing zgT <T\
0 /Asia/Japan w"#oA|itU%
1 /Asia/Japan/Osaka #0HpvO#QL
1 /Asia/Japan/Tokyo fq*R'>T`#
0 /Australia &aWNUFT[BN
0 /Australia/New South Wales \t/4>yS|A
1 /Australia/New South Wales/Sydney @-\y"
0 /Europe $T H"g.
0 /Europe/United Kingdom eVOz%f=bW
0 /Europe/United Kingdom/England 8v< 7OR
1 /Europe/United Kingdom/England/London $p;a = .
0 /North America &rKdnVe4#
0 /North America/Canada z<0SA@=/
0 /North America/Canada/Ontario Jv',.Ivc[
1 /North America/Canada/Ontario/Ottawa ' _ nr' k
1 /North America/Canada/Ontario/Toronto NR]X,@-afb
0 /North America/USA &]x|e
0 /North America/USA/California s|'c2z8-v
1 /North America/USA/California/Redwood Shores
LM^7t } '
column path format a50 SS> ISBv
select level,sys_connect_by_path(child,'/') path B;~& =80
from hier ek< }cX
start with parent is null r}@>3 Nwe
connect by prior child = parent; - 9j(e<
zMPiQ@Mo
LEVEL PATH fn< jHS!{!
-------- -------------------------------------------- '5k!3i0U
1 /Asia u"VSV{[
2 /Asia/China =_*P~ K s
3 /Asia/China/Beijing `>yAx)fZ
2 /Asia/Japan 6/Fyz u`
3 /Asia/Japan/Osaka 6[\^8Ll
3 /Asia/Japan/Tokyo [N}G %n
1 /Australia o/_'{a!G+N
2 /Australia/New South Wales | K4M1 7s
3 /Australia/New South Wales/Sydney {#"R0 fn
1 /Europe w4H$ O5ZJo
2 /Europe/United Kingdom S-iy9K~`-x
3 /Europe/United Kingdom/England $I"MW6J ~
4 /Europe/United Kingdom/England/London k.{\|NS
1 /North America y"i \P
2 /North America/Canada \V\CWIBj
3 /North America/Canada/Ontario Hcxhk`
4 /North America/Canada/Ontario/Ottawa +3j;
4 /North America/Canada/Ontario/Toronto *(U?GQ (a
2 /North America/USA 9)Y :[Gx
3 /North America/USA/California CQ CkX!O/o
4 /North America/USA/California/Redwood Shores 5I r1)IFt
>{!o B 7
在 Oracle 10g 中，还有其他更多关于层次查询的新特性 。例如，有的时候用户更关心的是每个层次分支中等级最低的内容。那么你就可以利用伪列函数CONNECT_BY_ISLEAF来判断当前行是不是叶子。如果是叶子就会在伪列中显示“1”，如果不是叶子而是一个分支（例如当前内容是其他行的父亲）就显示“0”。下给出了一个关于这个函数使用的例子： |,+72aI<
:}JX -F#
select connect_by_isleaf,sys_connect_by_path(child,'/') path R0e#s_9l?4
from hier {9YWSd%i
start with parent is null Ixjje/+
connect by prior child = parent; @:qhbe|m
21nT":f\F
CONNECT_BY_ISLEAF PATH lT;"chlp
---------------------------------- ------------ yn|R5-$i D
0 /Asia ?7'2Z~n#W
0 /Asia/China I Okj:j
1 /Asia/China/Beijing zgT <T\
0 /Asia/Japan w"#oA|itU%
1 /Asia/Japan/Osaka #0HpvO#QL
1 /Asia/Japan/Tokyo fq*R'>T`#
0 /Australia &aWNUFT[BN
0 /Australia/New South Wales \t/4>yS|A
1 /Australia/New South Wales/Sydney @-\y"
0 /Europe $T H"g.
0 /Europe/United Kingdom eVOz%f=bW
0 /Europe/United Kingdom/England 8v< 7OR
1 /Europe/United Kingdom/England/London $p;a = .
0 /North America &rKdnVe4#
0 /North America/Canada z<0SA@=/
0 /North America/Canada/Ontario Jv',.Ivc[
1 /North America/Canada/Ontario/Ottawa ' _ nr' k
1 /North America/Canada/Ontario/Toronto NR]X,@-afb
0 /North America/USA &]x|e
0 /North America/USA/California s|'c2z8-v
1 /North America/USA/California/Redwood Shores
在Oracle 10g 中还有一个新操作――CONNECT_BY_ROOT。 它用在列名之前用于返回当前层的根节点。如下面的例子，我可以显示出层次结构表中当前行数据所对应的最高等级节点的内容。 > Jr SY6|~
b90Mi<5
select connect_by_root child,sys_connect_by_path(child,'/') path [,\tN2w
from hier E,t^^YM1-
start with parent is null 4i& !KG?
connect by prior child = parent; ~5sHg &e
8O@F$P
CONNECT_BY_ROOT PATH =/4q-
------------------------------ -------- QN,q.l!cE
Asia /Asia UbV~,;]9
Asia /Asia/China A@| 6N:~f
Asia /Asia/China/Beijing n UIp=S|j
Asia /Asia/Japan }^5,J|1l
Asia /Asia/Japan/Osaka /{QB2h_B
Asia /Asia/Japan/Tokyo Fg 5ZJyu+
Australia /Australia t4{;/x4S
Australia /Australia/New South Wales 6y& QgS[
Australia /Australia/New South Wales/Sydney $U89z O
Europe /Europe E qOhJVo
Europe /Europe/United Kingdom +N>:QD5z
Europe /Europe/United Kingdom/England w\Ar&xO (
Europe /Europe/United Kingdom/England/London 5v{)ir&1
North America /North America m Q]GsHD
North America /North America/Canada 4E4 (F
North America /North America/Canada/Ontario 4B7otaF #l
North America /North America/Canada/Ontario/Ottawa kh{]\;m
North America /North America/Canada/Ontario/Toronto K,xTn+y
North America /North America/USA l~}7~7#69
North America /North America/USA/California c 7p{Xr
North America /North America/USA/California/Redwood Shores !ix7rr-z
J' P3ID
在Oracle 10g 之前的版本中，如果在你的树中出现了环状循环（如一个孩子节点引用一个父亲节点），Oracle 就会报出一个错误提示：“ ORA-01436: CONNECT BY loop in user data”。如果不删掉对父亲的引用就无法执行查询操作。而在 Oracle 10g 中，只要指定“NOCYCLE”就可以进行任意的查询操作。与这个关键字相关的还有一个伪列――CONNECT_BY_ISCYCLE， 如果在当前行中引用了某个父亲节点的内容并在树中出现了循环，那么该行的伪列中就会显示“1”，否则就显示“0”。如下例所示： 8k Y #;M
TO.d6+Yu
create table hier2 |-MYK-kV]
( s"4~7"$7
parent number, T:TOU;:
child number <<u V?[ACE
); /nH,q^.
3#R\ pn
insert into hier2 values(null,1); O-q 'C,
insert into hier2 values(1,2); 4*( dwvI
insert into hier2 values(2,3); 3Ab ;XJ"
insert into hier2 values(3,1); nm?U^@43
K;~E@q
select connect_by_iscycle,sys_connect_by_path(child,'/') path `WPS =Nan
from hier2 V44 R~'
start with parent is null 5WYX :!
connect by nocycle prior child = parent; _B3R9Ry2
/fKF%ygV
CONNECT_BY_ISCYCLE PATH ; *j?'T|`
------------------ ------- 0=L 8=sL
0 /1 Q$%3O{8 O
0 /1/2 U|wqWk
1 /1/2/3
b90Mi<5
select connect_by_root child,sys_connect_by_path(child,'/') path [,\tN2w
from hier E,t^^YM1-
start with parent is null 4i& !KG?
connect by prior child = parent; ~5sHg &e
8O@F$P
CONNECT_BY_ROOT PATH =/4q-
------------------------------ -------- QN,q.l!cE
Asia /Asia UbV~,;]9
Asia /Asia/China A@| 6N:~f
Asia /Asia/China/Beijing n UIp=S|j
Asia /Asia/Japan }^5,J|1l
Asia /Asia/Japan/Osaka /{QB2h_B
Asia /Asia/Japan/Tokyo Fg 5ZJyu+
Australia /Australia t4{;/x4S
Australia /Australia/New South Wales 6y& QgS[
Australia /Australia/New South Wales/Sydney $U89z O
Europe /Europe E qOhJVo
Europe /Europe/United Kingdom +N>:QD5z
Europe /Europe/United Kingdom/England w\Ar&xO (
Europe /Europe/United Kingdom/England/London 5v{)ir&1
North America /North America m Q]GsHD
North America /North America/Canada 4E4 (F
North America /North America/Canada/Ontario 4B7otaF #l
North America /North America/Canada/Ontario/Ottawa kh{]\;m
North America /North America/Canada/Ontario/Toronto K,xTn+y
North America /North America/USA l~}7~7#69
North America /North America/USA/California c 7p{Xr
North America /North America/USA/California/Redwood Shores !ix7rr-z
J' P3ID
在Oracle 10g 之前的版本中，如果在你的树中出现了环状循环（如一个孩子节点引用一个父亲节点），Oracle 就会报出一个错误提示：“ ORA-01436: CONNECT BY loop in user data”。如果不删掉对父亲的引用就无法执行查询操作。而在 Oracle 10g 中，只要指定“NOCYCLE”就可以进行任意的查询操作。与这个关键字相关的还有一个伪列――CONNECT_BY_ISCYCLE， 如果在当前行中引用了某个父亲节点的内容并在树中出现了循环，那么该行的伪列中就会显示“1”，否则就显示“0”。如下例所示： 8k Y #;M
TO.d6+Yu
create table hier2 |-MYK-kV]
( s"4~7"$7
parent number, T:TOU;:
child number <<u V?[ACE
); /nH,q^.
3#R\ pn
insert into hier2 values(null,1); O-q 'C,
insert into hier2 values(1,2); 4*( dwvI
insert into hier2 values(2,3); 3Ab ;XJ"
insert into hier2 values(3,1); nm?U^@43
K;~E@q
select connect_by_iscycle,sys_connect_by_path(child,'/') path `WPS =Nan
from hier2 V44 R~'
start with parent is null 5WYX :!
connect by nocycle prior child = parent; _B3R9Ry2
/fKF%ygV
CONNECT_BY_ISCYCLE PATH ; *j?'T|`
------------------ ------- 0=L 8=sL
0 /1 Q$%3O{8 O
0 /1/2 U|wqWk
1 /1/2/3
闽公网安备 35060202000074号