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 鼓楼 3540102 1
2 福州 35401 2
3 福建 35101 3
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 福州 35401 1
2 台江  3540101 2
3 鼓楼  3540102 2
4 鼓楼分局本部    354010201 3
5 洪山    354010202 3
6 仓山  3540103 2
7 仓山分局本部    354010301 3
8 金山    354010302 3
9 螺城    354010303 3
10 盖山    354010304 3

--正树从根结点到子结点,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'

1 org /福州 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 /福州/仓山/盖山    354010304 3

小技巧:

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

生成1000之内的数据