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:
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).
- 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,
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, ondeemployee_idda linha anterior é igual aomanager_idda linha atual.
- Esta cláusula estabelece a relação de hierarquia. Aqui, estamos dizendo ao Oracle para conectar a linha atual (
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.