Queries hierárquicas em Oracle

Um caso que ocorre com frequência é ter estruturas hierárquicas em
tabelas. Um exemplo típico será uma tabela empregado, que pode ter e/ou
ser um supervisor. Ou seja, temos uma chave estrangeira que aponta para
a chave da própria tabela, algo tipo

ID_EMP  NOME           SUPERVISOR<br />------- -------------- ------------<br />1       João           NULL<br />2       Pedro          1<br />3       Ricardo        1<br />4       José           2

Em que o João é supervisor do Pedro e do Ricardo e o Pedro é supervisor do José.

Por vezes, temos uma estrutura deste tipo mais ou menos complexa,
com mais ou menos níveis de profundidade e queremos saber, com base em um
identificador, todos os resultados hierarquicamente – neste caso, ao
perguntar quais os supervisionados hierárquicos do João, teríamos Pedro, Ricardo e José.

Tipicamente, até agora fazia uma função em pl/sql que fizesse isto,
pois não conhecia algumas das facilidades que o Oracle fornece para
isto.

Introducing CONNECT BY

DBAs e pessoal com alguma experiência em Oracle devem conhecer isto, mas para mim foi uma novidade e foi-me imensamente útil.
Com esta diretiva, consigo fazer o seguinte:

SELECT<br />  NOME, LEVEL<br />FROM<br />  EMPREGADO<br />START WITH<br />  NOME='João'<br />CONNECT BY NOCYCLE<br />  PRIOR ID = SUPERVISOR

O resultado desta query seria aproximado a:

NOME           LEVEL<br />-------------- -------<br />João           1<br />Pedro          2<br />Ricardo        2<br />José           3

O que se fez foi dizer que queríamos começar com o registro com o
nome João e “ligá-lo” aos seus filhos. Isto é feito com a cláusula
CONNECT BY e usando PRIOR num dos campos. Podemos pensar seguindo da
raiz, de registro em registro iterativamente, sendo que na primeira
iteração o PRIOR ID é o campo do registro raiz e estamos selecionando,
como próximos campos, todos os que tenham SUPERVISOR = PRIOR ID.

A palavra NOCYCLE é para não se entrarem em ciclos, e a coluna LEVEL que
está sendo usada é uma pseudo-coluna que indica o nível em que está o
registro em relação à raiz (sendo a raiz o nível 1).

Podem encontrar uma explicação mais completa aqui.