I have a good working pl/sql solution to my problem and am just curious to see if there is a sql one
I have a tree structure e.g.:
a
b c
d e f
g h i j k
create table tree
(id number(6) primary key
,parent_id number(6)
,root_id number(6) not null
--,lft number(4) not null
--,rgt number(4) not null
);
create table tree_update
(id number(10) primary key
,lft number(10) not null
,rgt number(10)
);
insert into tree values ('a',null,'a');
insert into tree values ('b','a' ,'a');
insert into tree values ('c','a' ,'a');
insert into tree values ('d','b' ,'a');
insert into tree values ('e','c' ,'a');
insert into tree values ('f','c' ,'a');
insert into tree values ('g','d' ,'a');
insert into tree values ('h','d' ,'a');
insert into tree values ('i','e' ,'a');
insert into tree values ('j','f' ,'a');
insert into tree values ('k','f' ,'a');
The product that is using this also uses "left" and "right" value to traverse the tree. This way a node's descendants can be derived using
select *
from tree
where lft > :node_left
and rgt < :node_rgt;
see http://en.wikipedia.org/wiki/Nested_set_model for details
Unfortunately the lft and rgt value can get corrupted, especially during concurrent updates to rows in the tree. So I need to apply periodic fixes
I did this using recursive pl/sql. This is my pl/sql:
create or replace procedure setLeftRight
(ParentId in integer
,CurrLeft in out integer
)
as
nLeft integer := CurrLeft +1;
begin
for r in
(select id
from tree
where parent_id = ParentId
or (parent_id is null and ParentId is null)
order by id
)
loop
insert into tree_update
(id
,lft
)
values
(r.id
,nLeft
);
setLeftRight
(r.id
,nLeft
);
end loop;
update tree_update
set rgt = nLeft
where id = ParentId
returning rgt+1 into CurrLeft;
end setLeftRight;
/
sho err
and it is called with:
var rgt number
exec :rgt := 0
exec setLeftRight(null,:rgt)
leaving correct answers as
id lft rgt
------- --- ---
a 1 22
b 2 9
d 3 8
g 4 5
h 6 7
c 10 21
e 11 14
i 12 13
f 15 20
j 16 19
k 17 18
So again I'm just curious to see if there is a sql solution. I can assign a sequential value to each row using recursive subquery factoring and search depth first, in either direction. But doing this, then joining, then re-evaluating the lft and rgt values seems cumbersome.
You should also note that rgt-lft = 2 x <number of descendants> + 1, if they are assigned sequentially (which isn't actually necessary as only order is important). I use this to check my values before commiting.
So is there any elegant sql solution, in any db version, but I'm currently on 11.2.0.3.
Thanks,
Ben