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.