- --第一步增加列用于临时处理
- 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
版权声明:本文为原创文章,版权归 木子网 所有,欢迎分享本文,转载请保留出处!
版权声明:本文为原创文章,版权归 木子网 所有,欢迎分享本文,转载请保留出处!