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).
SQL> create table test_connect(
2 parent varchar2(10),
3 child varchar2(10),
4 constraint uq_tcb unique(child)
5 );
Table created.
SQL> insert into test_connect values('a','ab');
1 row created.
SQL> c/ab/ac
1* insert into test_connect values('a','ac')
SQL> /
1 row created.
SQL> c/ac/ad
1* insert into test_connect values('a','ad')
SQL> /
1 row created.
SQL> select * from test_connect;
PARENT CHILD
---------- ----------
a ab
a ac
a ad
SQL> insert into test_connect values('ab','aba');
1 row created.
SQL> c/aba/abb
1* insert into test_connect values('ab','abb')
SQL> /
1 row created.
SQL> c/abb/abc
1* insert into test_connect values('ab','abc')
SQL> /
1 row created.
SQL> select * from test_connect;
PARENT CHILD
---------- ----------
a ab
a ac
a ad
ab aba
ab abb
ab abc
6 rows selected.
SQL> insert into test_connect values('b','ba');
1 row created.
SQL> c/ba/bb
1* insert into test_connect values('b','bb')
SQL> /
1 row created.
SQL> c/bb/bc
1* insert into test_connect values('b','bc')
SQL> /
1 row created.
SQL> c/bc/bd
1* insert into test_connect values('b','bd')
SQL> /
1 row created.
SQL> select * from test_connect;
PARENT CHILD
---------- ----------
a ab
a ac
a ad
ab aba
ab abb
ab abc
b ba
b bb
b bc
b bd
10 rows selected.
SQL> insert into test_connect values('aba','abaa');
1 row created.
SQL> c/abaa/abab
1* insert into test_connect values('aba','abab')
SQL> /
1 row created.
SQL> c/abab/abac
1* insert into test_connect values('aba','abac')
SQL> /
1 row created.
SQL> c/abac/abad
1* insert into test_connect values('aba','abad')
SQL> /
1 row created.
SQL> select * from test_connect;
PARENT CHILD
---------- ----------
a ab
a ac
a ad
ab aba
ab abb
ab abc
b ba
b bb
b bc
b bd
aba abaa
PARENT CHILD
---------- ----------
aba abab
aba abac
aba abad
14 rows selected.
SQL> insert into test_connect values('c','cc');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test_connect;
PARENT CHILD
---------- ----------
a ab
a ac
a ad
ab aba
ab abb
ab abc
b ba
b bb
b bc
b bd
aba abaa
PARENT CHILD
---------- ----------
aba abab
aba abac
aba abad
c cc
15 rows selected.
SQL> insert into test_connect values('null','a');
1 row created.
SQL> c/a/b
1* insert into test_connect vblues('null','a')
SQL> /
insert into test_connect vblues('null','a')
*
ERROR at line 1:
ORA-00928: missing SELECT keyword
SQL> insert into test_connect values('null','b');
1 row created.
SQL> c/b/c
1* insert into test_connect values('null','c')
SQL> /
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test_connect;
PARENT CHILD
---------- ----------
a ab
a ac
a ad
ab aba
ab abb
ab abc
b ba
b bb
b bc
b bd
aba abaa
PARENT CHILD
---------- ----------
aba abab
aba abac
aba abad
c cc
null a
null b
null c
18 rows selected.
SQL> select lpad(' ',2*(level-1)) || to_char(child) s
from test_connect
start with parent is null
connect by prior child = parent;
2 3 4
no rows selected
SQL> select * from test_connect start with parent is null
2 connect by prior child=parent;
no rows selected
SQL> update test_child set parent=null where child in('a','b','c');
update test_child set parent=null where child in('a','b','c')
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> c/test_child/test_connect
1* update test_connect set parent=null where child in('a','b','c')
SQL> /
3 rows updated.
SQL> commit;
Commit complete.
SQL> select lpad(' ',2*(level-1)) || to_char(child) s
from test_connect
start with parent is null
connect by prior child = parent;
2 3 4
S
--------------------------------------------------------------------------------
a
ab
aba
abaa
abab
abac
abad
abb
abc
ac
ad
S
--------------------------------------------------------------------------------
b
ba
bb
bc
bd
c
cc
18 rows selected.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10972173/viewspace-438992/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10972173/viewspace-438992/