Pot ser útil per accedir a informació en forma d’arbre…
CREATE TABLE BI_INT.jerarquia
(
ID INTEGER,
REF_ID INTEGER,
NOM VARCHAR2(50)
);
ALTER TABLE BI_INT.jerarquia ADD (
PRIMARY KEY
(ID));
insert into jerarquia values (1,null,’Avi’);
insert into jerarquia values (2,1,’Pare1′);
insert into jerarquia values (3,1,’Pare2′);
insert into jerarquia values (4,2,’Fill1′);
insert into jerarquia values (5,2,’Fill3′);
insert into jerarquia values (6,3,’Fill2′);
insert into jerarquia values (7,3,’Fill4′);
insert into jerarquia values (8,4,’Net1′);
insert into jerarquia values (9,7,’Net2′);
commit;
select * from jerarquia;
SELECT nom, id, ref_id, LEVEL, SYS_CONNECT_BY_PATH(nom, ‘/’) as Arbre
FROM jerarquia
–where level < 3
START WITH id = 1
CONNECT BY PRIOR id = ref_id
ORDER SIBLINGS BY nom;
——
| NOM | ID | REF_ID | LEVEL | ARBRE |
|---|---|---|---|---|
| Avi |
1
|
1
|
/Avi | |
| Pare1 |
2
|
1
|
2
|
/Avi/Pare1 |
| Fill1 |
4
|
2
|
3
|
/Avi/Pare1/Fill1 |
| Net1 |
8
|
4
|
4
|
/Avi/Pare1/Fill1/Net1 |
| Fill3 |
5
|
2
|
3
|
/Avi/Pare1/Fill3 |
| Pare2 |
3
|
1
|
2
|
/Avi/Pare2 |
| Fill2 |
6
|
3
|
3
|
/Avi/Pare2/Fill2 |
| Fill4 |
7
|
3
|
3
|
/Avi/Pare2/Fill4 |
| Net2 |
9
|
7
|
4
|
/Avi/Pare2/Fill4/Net2 |