Em bancos de dados relacionais, é comum encontrar dados organizados de maneira hierárquica, como árvores genealógicas, estruturas de pastas, organogramas e outros cenários semelhantes. No Oracle, uma forma eficiente de navegar por essas estruturas hierárquicas é utilizando a cláusula CONNECT BY. Essa funcionalidade facilita a execução de consultas recursivas que podem percorrer dados relacionados em níveis hierárquicos, retornando uma estrutura organizada.

Neste post, vamos explorar como a cláusula CONNECT BY funciona, quando e como utilizá-la para consultas recursivas no Oracle.

O que é CONNECT BY? O CONNECT BY é uma extensão da SQL no Oracle que permite criar consultas recursivas para percorrer relações hierárquicas em tabelas. Ele utiliza as relações de “pai-filho” para construir árvores a partir de uma linha raiz e se estende recursivamente para encontrar todos os descendentes.

Por exemplo, em uma tabela de funcionários que contém uma coluna manager_id, você pode usar o CONNECT BY para encontrar todos os subordinados de um gerente específico.

Exemplo Prático: Vamos supor que você tem uma tabela de funcionários chamada employees que tem a seguinte estrutura:

CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
employee_name VARCHAR2(100), manager_id NUMBER
);

Essa tabela armazena o ID do funcionário e seu respectivo gerente (manager_id). Nosso objetivo é realizar uma consulta que mostre a hierarquia completa de funcionários, começando pelo CEO (que não tem gerente).

Para realizar essa consulta, utilizamos a cláusula CONNECT BY da seguinte maneira:

SELECT employee_id,
             employee_name,
LEVEL AS hierarchy_level
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

Explicando a Consulta:

  1. START WITH manager_id IS NULL:
    • Esta cláusula define o ponto inicial da consulta recursiva. Neste caso, estamos começando pelo CEO, que não tem um gerente (ou seja, manager_id IS NULL).
  2. CONNECT BY PRIOR employee_id = manager_id:
    • Esta cláusula estabelece a relação de hierarquia. Aqui, estamos dizendo ao Oracle para conectar a linha atual (employee_id) à linha anterior, onde employee_id da linha anterior é igual ao manager_id da linha atual.
  3. LEVEL:
    • LEVEL é uma pseudo-coluna fornecida pelo Oracle que indica a profundidade da linha na hierarquia. O nível 1 seria o ponto inicial (CEO), o nível 2 seriam os subordinados diretos do CEO, e assim por diante.

Resultado: O resultado desta consulta irá retornar todos os funcionários em uma estrutura hierárquica, incluindo o nível hierárquico de cada um. Isso é particularmente útil em relatórios de organograma, navegação de categorias de produtos e muito mais.

Por exemplo:

employee_id employee_name hierarchy_level
1 John Doe 1
2 Jane Smith 2
3 Michael White 2
4 Sarah Black 3

Filtrando Hierarquias: Você também pode adicionar filtros para limitar a hierarquia que está sendo exibida. Por exemplo, se você quiser ver apenas os subordinados diretos de um gerente específico, pode utilizar a cláusula WHERE em conjunto com CONNECT BY:

SELECT employee_id,
              employee_name,
LEVEL AS hierarchy_level
FROM employees
START WITH employee_id = 2-- Começando pelo gerente com ID 2
CONNECT BY PRIOR employee_id = manager_id;

Uso de ORDER SIBLINGS BY: Para garantir que os filhos de um nó hierárquico específico sejam ordenados de forma correta, você pode usar a cláusula ORDER SIBLINGS BY. Isso permite ordenar os “irmãos” (filhos do mesmo pai) de maneira específica.

SELECT employee_id,
             employee_name,
LEVEL AS hierarchy_level
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY employee_name;

Conclusão: O CONNECT BY no Oracle é uma ferramenta poderosa para lidar com dados hierárquicos em seus bancos de dados. Ele permite navegar facilmente por relações pai-filho, seja para construir árvores, organogramas ou outras estruturas hierárquicas complexas.

Utilizar o CONNECT BY de maneira eficaz pode facilitar bastante o trabalho com dados relacionados hierarquicamente e melhorar a clareza dos seus relatórios. Certifique-se de dominar essa técnica para tirar o máximo proveito da organização de dados no Oracle.