oracle SYS_CONNECT_BY_PATH,connect by 实例

prior p_dim_value=dim_value，如果prior在前，则树是倒树还是正树就从后往前读，dim_value是子，p_dim_value是父，即此树是正树

prior dim_value = p_dim_value,如果prior在前，则树是倒树还是正树就从后往前读，p_dim_value是父，dim_value是子，即此树是倒树

如果prior省略不写的话，就不进行深层次的递归

--倒树从子结点到根结点,无prior
select dim_name,dim_value,level
  from K_DIM_VALUE
 START WITH DIM_VALUE = '3540102' and dim_code='org'
CONNECT BY NOCYCLE  P_DIM_VALUE   =  DIM_VALUE
and dim_code='org';

鼓楼3540102 1

--倒树从子结点到根结点

select dim_name,dim_value,level
  from K_DIM_VALUE
 START WITH DIM_VALUE = '3540102' and dim_code='org'
CONNECT BY NOCYCLE PRIOR P_DIM_VALUE   =  DIM_VALUE and dim_code='org';

1鼓楼 35401021
2 福州 354012
3 福建 351013
4 国网公司 00000 4

--正树从根结点到子结点
select dim_name,lpad(' ',2*level-2 )||dim_value,level
  from K_DIM_VALUE 
 START WITH DIM_VALUE = '35401' and dim_code='org'  
CONNECT BY  PRIOR DIM_VALUE  = P_DIM_VALUE
and dim_code='org';

1福州 354011
2 台江  35401012
3 鼓楼  35401022
4 鼓楼分局本部    354010201 3
5 洪山    3540102023
6 仓山  35401032
7 仓山分局本部    354010301 3
8 金山    3540103023
9 螺城    3540103033
10 盖山    3540103043

--正树从根结点到子结点,SYS_CONNECT_BY_PATH加上父级名称
select dim_code,SYS_CONNECT_BY_PATH(dim_name,'/'),lpad(' ',2*level-2 )||dim_value,level
  from K_DIM_VALUE 
 START WITH   DIM_VALUE = '35401'  and dim_code='org'
CONNECT BY  PRIOR DIM_VALUE  = P_DIM_VALUE
and  dim_code='org'

1org /福州35401 1
2 org /福州/台江 3540101 2
3 org /福州/鼓楼 3540102 2
4 org /福州/鼓楼/鼓楼分局本部   354010201 3
5 org /福州/鼓楼/洪山   354010202 3
6 org /福州/仓山 3540103 2
7 org /福州/仓山/仓山分局本部   354010301 3
8 org /福州/仓山/金山   354010302 3
9 org /福州/仓山/螺城   354010303 3
10 org /福州/仓山/盖山    3540103043

小技巧:

select rownum a from dual connect by rownum<=10000; 

生成1000之内的数据