oracle 根据父子编码生产新编码

merge into a5_org a
using (select path, lPad(row_number()over(partition by P_departmentID order by dep_order),2,'0') rn
from a5_org b where b.d_level=4) b on( a.path =b.path)
when matched then
update set a.serial_num=b.rn--生产每一级的编码

从d_level 2-5  

 

select path, lPad(row_number()over(partition by P_departmentID order by dep_order),2,'0') rn
from a5_org b where b.d_level=3

 

关联更新
merge into a5_org a using(
select path,RPAD(replace(sys_connect_by_path(serial_num,'/') ,'/',''),8,'0')as rn --编码合并
from a5_org
start with P_departmentID is null
connect by prior departmentID= P_departmentID
) b on (a.path =b.path)
when matched then
update set a.serial_num=b.rn --更新目标