Consultas Hierárquicas em SQL: Navegando em Estruturas de Árvore com CONNECT BY e START WITH

Navegando em Relações de Pai e Filho em Bancos Relacionais

Em sistemas corporativos, é extremamente comum nos depararmos com dados que possuem uma estrutura de relacionamento hierárquico (parent-child). Exemplos clássicos incluem organogramas empresariais (onde um funcionário se reporta a um gerente, que por sua vez se reporta a um diretor), estruturas de árvore de categorias de produtos, listas de materiais (*Bill of Materials - BOM*) na indústria ou mapas de rotas logísticas.

Consultar essas estruturas utilizando apenas junções comuns (JOIN) pode se tornar um desafio complexo e engessado, especialmente quando a profundidade da árvore de dados é desconhecida ou variável. Para solucionar esse problema com máxima elegância e performance, o banco de dados Oracle disponibiliza as cláusulas nativas START WITH e CONNECT BY.

O Conceito de Hierarquia Relacional

Uma tabela armazena dados hierárquicos quando possui uma coluna de chave estrangeira que aponta para a chave primária da própria tabela (uma relação reflexiva ou auto-relacionamento). Por exemplo, em uma tabela de employees, podemos ter as colunas:

• employee_id (Chave primária do colaborador)
• full_name (Nome do colaborador)
• manager_id (Chave estrangeira apontando para o employee_id do seu gestor direto)

A Sintaxe Fundamental do CONNECT BY

A estrutura de uma consulta hierárquica no padrão Oracle utiliza operadores direcionais especiais para percorrer os galhos da árvore de dados:

• START WITH: Define a condição de raiz (o ponto de partida absoluto da árvore, como o CEO da empresa ou o topo de uma categoria).
• CONNECT BY: Define a regra de relacionamento que conecta o registro pai ao registro filho utilizando o operador PRIOR.
• PRIOR: Especifica qual lado da relação pertence ao nível superior (pai).

Exemplo prático de organograma corporativo:

SELECT    LEVEL AS nivel_hierarquico,    LPAD(' ', 2 * (LEVEL - 1)) || full_name AS organograma_visual,    job_title,    employee_id,    manager_id FROM employees START WITH manager_id IS NULL -- Começa pelo topo (ex: o presidente que não possui chefe) CONNECT BY PRIOR employee_id = manager_id;

Funções Auxiliares para Análise Hierárquica

O Oracle fornece pseudocolunas e funções embutidas extremamente úteis para enriquecer a navegação em árvores de dados:

• LEVEL: Retorna um número inteiro indicando a profundidade do nó na árvore. O nível raiz recebe 1, seus subordinados diretos recebem 2, e assim sucessivamente.
• SYS_CONNECT_BY_PATH(coluna, separador): Constrói uma string concatenando todo o caminho percorrido desde a raiz até o nó atual (ex: /Diretoria/Financeiro/Gerencia/Analista).
• CONNECT_BY_ISCYCLE: Retorna 1 se o registro atual criar um loop infinito de dados corrompidos na hierarquia, ou 0 caso contrário.

Exemplo prático utilizando SYS_CONNECT_BY_PATH:

SELECT    employee_id,    full_name,    SYS_CONNECT_BY_PATH(full_name, ' -> ') AS caminho_hierarquico FROM employees START WITH manager_id IS NULL CONNECT BY PRIOR employee_id = manager_id;

Evitando Loops Infinitos com NOCYCLE

Em bases de dados legadas ou mal mantidas, inconsistências de dados podem fazer com que um funcionário aponte para si mesmo como gestor ou crie um ciclo fechado (onde o subordinado é chefe do próprio chefe). Quando isso ocorre, uma consulta hierárquica comum falhará com o famoso erro ORA-01436: CONNECT BY loop in user data.

Para proteger sua aplicação contra esse cenário, utilize sempre a cláusula CONNECT BY NOCYCLE combinada com a verificação de segurança:

SELECT employee_id, full_name FROM employees START WITH manager_id IS NULL CONNECT BY NOCYCLE PRIOR employee_id = manager_id;

Conclusão

Dominar consultas hierárquicas com START WITH e CONNECT BY capacita o desenvolvedor a extrair informações complexas de estruturas em árvore de forma limpa, rápida e estruturada diretamente no banco de dados.