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;

——