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;

历史上的今天:

本文地址：https://www.lisen.me/oracle-parent-child-structure-to-hierarchical-code-structure.html
版权声明：本文为原创文章，版权归木子网所有，欢迎分享本文，转载请保留出处！