Zona J

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
------- -------------- ------------
1       João           NULL
2       Pedro          1
3       Ricardo        1
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 num identificador, todos os resultados hierarquicamente - neste caso, ao perguntar quais os supervisionados hierárquicos do João, teríamos o 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 deve conhecer isto, mas para mim foi uma novidade e foi-me imensamente útil.
Com esta directiva, consigo fazer o seguinte:

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

O resultado desta query seria aproximado a:

NOME           LEVEL
-------------- -------
João           1
Pedro          2
Ricardo        2
José           3

O que se fez foi dizer que queríamos começam com o registo 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 registo em registo iterativamente, sendo que na primeira iteração o PRIOR ID é o campo do registo raiz e estamos a seleccionar 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á a ser usada é uma pseudo-coluna que indica o nível em que está o registo em relação à raiz (sendo a raiz o nível 1).

Podem encontrar uma explicação mais completa aqui.