Query hierárquica no Oracle

Com o Oracle é possível escrever queries que consultam dados armazenados em formato hieráquico. Tabelas que caracterizam este formato são as que possuem um campo com informações que remetem a um identificador pai. Por exemplo:

1
2
3
4
5

CREATE TABLE CATEGORIA(
   CATEGORIA_ID NUMBER PRIMARY KEY,
   NOME VARCHAR2(40),
   CATEGORIA_ID_PAI NUMBER
);

No exemplo acima, o campo CATEGORIA_ID_PAI referencia o campo CATEGORIA_ID.

O oracle introduz os seguintes comandos para trabalhar com queries hierárquicas:

• START WITH (cláusula SQL) - usado para indicar qual registro será o nó raiz do retorno.
  
• CONNECT BY (cláusula SQL) - usado para indicar a relação entre registro pai e registro filho.
  
• ORDER SIBLINGS BY (cláusula SQL) - usado para ordenar o resultado dentro de suas respectivas hierarquias.
  
• LEVEL (pseudocolumn) - pseudocoluna, com valor numérico começando em 1, que indica o nível do nó em relação ao resultado.
  
• SYS_CONNECT_BY_PATH (função) - função usada para formatar uma string concatenando, com separador, todos os nós que estão acima do nó atual.
  
• PRIOR (operador) - este operador permite recuperar o valor de um campo do registro pai e em conjunto com CONNECT BY permite indicar quem é o pai na relação pai/filho.
  
• CONNECT_BY_ROOT (operador) - este operador permite recuperar o valor de um campo do nó raiz da árvore gerada no resultado da query.

Exemplo de query hierárquica:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

SELECT
   LEVEL AS NIVEL, 
   CATEGORIA_ID, 
   NOME,
 
   -- Nome com tabulação de dois níveis
   LPAD(' ', LEVEL * 2) || NOME AS NOME_TABULADO,
 
   -- Exemplo do operador PRIOR
   PRIOR NOME AS NOME_DO_PAI,
 
   -- Exemplo da função SYS_CONNECT_BY_PATH
   SYS_CONNECT_BY_PATH(NOME, '/') AS NOME_FORMATO_DIRETORIO,
 
   -- Exemplo do operador CONNECT_BY_ROOT 
   CONNECT_BY_ROOT NOME AS NOME_RAIZ
FROM
   CATEGORIA
START WITH 
   CATEGORIA_ID = 1
CONNECT BY 
   CATEGORIA_ID_PAI = PRIOR CATEGORIA_ID
ORDER BY SIBLINGS 
   NOME
;

Deve-se tomar cuidado ao usar ORDER BY, pois o resultado obtido poderá não estar ordenado de acordo com a árvore hierárquica. Para que o resultado esteja ordenado de acordo com a árvore, ORDER BY SIBLINGS deve ser usado ao invés.

A cláusula SQL CONNECT BY pode ser usada também para restringir ramos inteiros de uma árvore. Se por exemplo, na query acima tivéssemos adicionado AND CATEGORIA_ID_PAI <> 2 à cláusula CONNECT BY, estaríamos descartando do resultado todos os nós abaixo do nó com categoria igual a 2 (inclusive).

As cláusulas START WITH e CONNECT BY podem aparecer em qualquer ordem dentro de um comando SELECT, porém, devem ser declaradas após a cláusula WHERE e antes de ORDER BY, caso estas existam.