oracle父子结构转分级码结构

第一步增加列用于临时处理

ALTER TABLE GSPUSERDOMAIN ADD fid varchar2(36)  
ALTER TABLE GSPUSERDOMAIN ADD pathtemp varchar2(36)  

第二步插入父节点

insert into GSPUSERDOMAIN(id,code,name,PATH,LAYER,isdetail,fid,pathtemp)  
select id,danweidh,mingcheng,ID,level,connect_by_isleaf,shangji,'' from JIGOU   
  start with id in(select id from JIGOU where shangji IS NULL)  
  connect by shangji=prior id order by shangji  

第三步处理路径

declare  
   vInit VARCHAR2(36):=0;  
begin  
  for i in 1..10 loop  
   declare CURSOR  emp_cur IS  select * From GSPUSERDOMAIN  where LAYER=i FOR UPDATE;  
    BEGIN    
    FOR emp_row IN emp_cur  
    LOOP  
      select(case when max(pathtemp)is null then '0' else max(pathtemp) end)+1 into vInit From GSPUSERDOMAIN where nvl(trim(GSPUSERDOMAIN.fid),' ')=nvl(trim(emp_row.fid),' ');  
      vInit:=LPAD(vInit,4,'0');  
    UPDATE GSPUSERDOMAIN SET pathtemp=(select pathtemp from GSPUSERDOMAIN aa where aa.id=GSPUSERDOMAIN.fid)||vInit WHERE CURRENT OF emp_cur;  
    END LOOP;  
    end;  
  end loop;  
end;

第四步骤修改path

UPDATE GSPUSERDOMAIN SET PATH = pathtemp  

最后一步，删除临时列

alter table GSPUSERDOMAIN drop column fid;  
alter table GSPUSERDOMAIN drop column pathtemp;