START WITH and CONNECT BY用法

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/