Comando CONNECT BY no Oracle SQL: Explorando a Hierarquia de Dados

O Oracle SQL é uma poderosa linguagem de consulta utilizada para gerenciar e manipular bancos de dados Oracle. Uma das funcionalidades mais interessantes e úteis do Oracle SQL é o comando CONNECT BY, que permite realizar consultas em dados hierárquicos, como árvores ou estruturas de dados em forma de grafo. O comando CONNECT BY permite que você recupere dados hierárquicos em uma única consulta, facilitando a exploração de relacionamentos e estruturas complexas.

Introdução ao comando CONNECT BY

O comando CONNECT BY faz parte da cláusula START WITH ... CONNECT BY em uma instrução SELECT. Ele é usado para percorrer relacionamentos pai-filho em uma tabela hierárquica ou em tabelas relacionadas usando chaves estrangeiras. Isso torna o CONNECT BY ideal para consultas em dados organizados em uma estrutura de árvore, onde cada registro tem uma referência para seu pai ou antecessor.

Para usar o comando CONNECT BY, é importante que a tabela contenha colunas que estabeleçam a relação pai-filho (ou ascendente-descendente) entre os registros. Geralmente, uma coluna específica é usada para esse propósito, como um ID pai (parent_id) que aponta para o ID do registro pai.

Sintaxe do comando CONNECT BY

A sintaxe básica da cláusula START WITH ... CONNECT BY é a seguinte:

SELECT column1, column2, ...
FROM table_name
START WITH condition
CONNECT BY PRIOR column = column;

column1, column2, ...: As colunas que você deseja selecionar na consulta.
table_name: O nome da tabela que contém os dados hierárquicos.
condition: A condição para iniciar a pesquisa hierárquica.
PRIOR column: Especifica a coluna que estabelece a relação pai-filho entre os registros.

Exemplos Práticos

Vamos considerar um exemplo prático usando uma tabela chamada “employees” que armazena dados sobre funcionários em uma empresa. A tabela possui as seguintes colunas relevantes:

employee_id: ID único para cada funcionário.
employee_name: O nome do funcionário.
manager_id: ID do gerente do funcionário (referência ao employee_id do gerente).

Exemplo 1: Recuperando a hierarquia de gerentes de um funcionário específico

Suponha que desejamos encontrar a hierarquia de gerentes de um funcionário específico, identificado pelo employee_id. Vamos considerar o employee_id = 105 como nosso exemplo.

SELECT employee_id, employee_name, manager_id
FROM employees
START WITH employee_id = 105
CONNECT BY PRIOR manager_id = employee_id;

Neste exemplo, iniciamos a busca pelo funcionário com employee_id = 105 usando a cláusula START WITH. O CONNECT BY PRIOR estabelece a relação entre o manager_id e o employee_id, garantindo que estamos percorrendo a hierarquia dos gerentes. A consulta retornará todos os gerentes, começando pelo funcionário com employee_id = 105 e subindo na hierarquia.

Exemplo 2: Recuperando a estrutura de subordinados de um gerente

Agora, vamos inverter o cenário e recuperar todos os subordinados de um gerente específico, identificado pelo employee_id = 210.

SELECT employee_id, employee_name, manager_id
FROM employees
START WITH manager_id = 210
CONNECT BY PRIOR employee_id = manager_id;

Neste exemplo, iniciamos a busca pelo gerente com employee_id = 210 usando a cláusula START WITH. Em seguida, usamos CONNECT BY PRIOR para estabelecer a relação entre o employee_id e o manager_id, percorrendo a estrutura de subordinados.

Outras funcionalidades do comando connect by

Além da cláusula básica do comando connect by, existem outras funcionalidades que podem ser usadas para melhorar as consultas hierárquicas. Algumas delas são:

A função level: essa função retorna o nível hierárquico de cada registro na árvore. O nível raiz é 1, o nível dos filhos diretos é 2 e assim por diante. Podemos usar essa função para filtrar ou ordenar os registros por nível.
A pseudocoluna connect_by_isleaf: essa pseudocoluna retorna 1 se o registro for uma treeleaf (ou seja, não tem nenhum filho) ou 0 caso contrário. Podemos usar essa pseudocoluna para identificar ou excluir as folhas da árvore.
A função sys_connect_by_path: essa função retorna o caminho hierárquico desde a raiz até o registro atual, separado por um caractere especificado. Podemos usar essa função para visualizar ou agrupar os registros por caminho.
A pseudocoluna connect_by_root: essa pseudocoluna retorna o valor de uma coluna do registro raiz para cada registro na árvore. Podemos usar essa pseudocoluna para obter informações do registro raiz em qualquer nível da árvore.

Conclusão

O comando CONNECT BY no Oracle SQL é uma ferramenta poderosa para explorar dados hierárquicos e facilitar consultas em estruturas de árvores ou grafos. Com a capacidade de percorrer relacionamentos pai-filho em uma única consulta, é possível extrair informações valiosas de tabelas hierárquicas complexas. Lembrando que o uso adequado desse comando depende da existência de uma coluna que estabeleça a relação entre os registros.

Ao aplicar o CONNECT BY em suas consultas, você pode simplificar a análise de dados hierárquicos e obter insights mais profundos sobre a estrutura das informações armazenadas em seu banco de dados Oracle.

Fontes

Comando CONNECT BY no Oracle SQL: Explorando a Hierarquia de Dados