start with.....connect by prior oracle中取出树形结构

create table test_connect_by (
  parent     number,
  child      number,
  constraint uq_tcb unique (child)
);

5 = 2+3

insert into test_connect_by values ( 5, 2);
insert into test_connect_by values ( 5, 3);

18 = 11+7

insert into test_connect_by values (18,11);
insert into test_connect_by values (18, 7);

17 = 9+8

insert into test_connect_by values (17, 9);
insert into test_connect_by values (17, 8);

26 = 13+1+12

insert into test_connect_by values (26,13);
insert into test_connect_by values (26, 1);
insert into test_connect_by values (26,12);

15=10+5

insert into test_connect_by values (15,10);
insert into test_connect_by values (15, 5);

38=15+17+6

insert into test_connect_by values (38,15);
insert into test_connect_by values (38,17);
insert into test_connect_by values (38, 6);

38, 26 and 18 have no parents (the parent isnull)

insert into test_connect_by values (null, 38);
insert into test_connect_by values (null, 26);
insert into test_connect_by values (null, 18);

Now, let's select the data hierarchically:

select lpad(' ',2*(level-1)) || to_char(child) s 
  from test_connect_by 
  start with parent is null
  connect by prior child = parent; //这里是从parent=null的行开始查找，找到当前行的child字段等于其他行的parent字段的列

This select statement results in:

38
  15
    10
    5
      2
      3
  17
    9
    8
  6
26
  13
  1
  12
18
  11
  7

其实我们在做系统菜单或者组织机构的时候，这时都是树形结构的，用到这个函数是十分的有用的，在数据库中就得到了树形结构，而不用再做循环。
SIBLINGS 是兄弟姐妹的意思，那么ORDER SIBLINGS BY的意思就是在兄弟姐妹之间的排序,在用递归查询的时候，要对树内的兄弟姐妹排序，用到order siblings by语法。