-- 生成tree 第一种
-- 1. 第一次不会生成 根序号 手动初始
update B001 set B001003 = '001',B001002 = '-1' where id = '101';
commit;
-- 2. 循环拼接已准备好的层次所属序号 B001050(所属层级),B001002(父id),B001003(treeId)
declare
type arr is table of B001%rowtype index by binary_integer; -- 可变数组
v_arr1 arr;
v_arr2 arr;
begin
for z in 1..8 loop
-- 从数据库提取
select * bulk collect into v_arr1 from B001 order by B001050,B001002; -- bulk collect 将数据一起赋值给 v_arr1 ,加快速度,这里不用会提示错误
for i in 1..v_arr1.count loop
select * bulk collect into v_arr2 from B001 where B001002 = v_arr1(i).id order by B001050,B001002; -- bulk collect 将数据一起赋值给 v_arr1 ,加快速度,这里不用会提示错误
for j in 1..v_arr2.count loop
update B001 set B001003 = v_arr1(i).B001003 || replace(lpad(j,3),' ','0') where id = v_arr2(j).id;
end loop;
commit;
end loop;
end loop;
end;
-- 生成tree 第二种
-- 1. 第一次不会生成 根序号 手动初始
update B001 set B001003 = '001',B001002 = '-1' where id = '101';
-- 2. 生成 第一层级 tree 第二层级 length(B001003) = 6 第三层级 length(B001003) = 9 。。。。
merge into B001 b1 using
(select a.id,
(
select b.B001003 from B001 b where a.B001002 = b.id
) || trim(to_char(row_number() over(partition by a.B001002 order by a.B001002),'000')) as tree_code from B001 a where exists
(
select * from B001 c where a.B001002 = c.id and length(B001003) = 3
)
) b2 on b1.id = b2.id when matched then update set b1.B001003 = b2.tree_code;
commit;
-- for 循环 多少层级 循环多少次 length(B001003) = z*3
/*
-- 2. 生成 第一层级 tree
declare
begin
for z in 1..8 loop
merge into B001 b1 using
(select a.id,
(
select b.B001003 from B001 b where a.B001002 = b.id
) || trim(to_char(row_number() over(partition by a.B001002 order by a.B001002),'000')) as tree_code from B001 a where exists
(
select * from B001 c where a.B001002 = c.id and length(B001003) = z*3
)
) b2 on b1.id = b2.id when matched then update set b1.B001003 = b2.tree_code;
commit;
end loop;
end;
*/