O que devemos conhecer sobre SQL/Oracle Parte 16: Recuperação hierárquica

Interpretando o conceito de consulta hierárquica

Consultas hierárquicas vão além do conceito relacional pai/filho, onde temos uma tabela que é referenciada por outra, onde para cada registro de uma podemos ter vários em outra. 

A estrutura de dados hierárquicos é diferente, ao invés dos dados estarem em tabelas diferentes eles ficam na mesma tabela (ou view), onde uma coluna do registro faz referencia a outra coluna de um outro registro, dessa forma teoricamente existe a capacidade de se ter infinitas gerações.

Para embasar nossos próximos exemplos construiremos uma tabela nesses moldes, onde teremos informações para relatórios de funcionários:

CREATE TABLE RELATORIO_CARGOS(CODIGO NUMBER(10) PRIMARY KEY, CARGO VARCHAR2(50), RESPONDE_PARA NUMBER(10));

ALTER TABLE RELATORIO_CARGOS ADD CONSTRAINT RELATORIO_CARGOS_FK FOREIGN KEY(RESPONDE_PARA) REFERENCES RELATORIO_CARGOS(CODIGO);

Resumindo:

• Uma consulta hierárquica estende o relacionamento pai-filho em uma estrutura multigerencial, com múltiplos níveis de relacionamento que podem ser adicionados a uma tabela, permitindo a criação de uma estrutura relacional sem fim;
• Consultas hierárquicas são baseadas em self-join;
• Todos os registros de uma consulta hierárquica representam um nó (node);
• O ponto inicial de uma consulta hierárquica é considerado o nó raiz (root);
• Qualquer nó com dois ou mais filhos é considerado um garfo (fork);
• Qualquer nó que termina sem filhos é considerado folha (leaf);

Criando e formatando dados hierárquicos

Utilizando a estrutura definida anteriormente podemos partir para a consulta, onde veremos duas novas cláusulas, START WITH, que identifica o nosso nó raiz, aqui é uma boa prática identificar uma coluna que seja chave primaria, mas isso não é obrigatório, qualquer expressão que identifique um registro de forma única pode ser usado, e também usaremos a cláusula CONNECT BY e PRIOR, responsável por realizar o SELF-JOIN, é onde indicamos as colunas que relacionam os registros, e mais importante, qual será o sentido dessa busca, sendo que o PRIOR deve ser indicado antes de uma das colunas utilizadas no SELF-JOIN:

SELECT LEVEL, CODIGO, CARGO, RESPONDE_PARA FROM RELATORIO_CARGOS START WITH CODIGO = 1 CONNECT BY RESPONDE_PARA = PRIOR CODIGO;

Na consulta acima indicamos uma pseudocoluna chamada LEVEL, ela irá mostrar o nível de hierarquia de cada registro, sendo que ele é gerado a cada consulta, em nosso caso o nível um será o registro com código um, se na cláusula START WITH tivéssemos indicado outro código esse passaria a ser o nível um.

Um uso comum é a construção de estruturas em arvore, onde podemos formatar uma das colunas com espaços, por exemplo, para facilitar a visualização de nossa estrutura:

SELECT LEVEL, CODIGO, (LPAD('   ', LEVEL*3) || CARGO) CARGO FROM RELATORIO_CARGOS START WITH CODIGO = 1 CONNECT BY RESPONDE_PARA = PRIOR CODIGO;

Como mencionado antes à direção do resultado da consulta é definida pela cláusula PRIOR, em nosso exemplo o resultado inicia na raiz de nossa estrutura e vai descendo, mas se colocarmos o PRIOR antes da coluna RESPONDE_PARA o sentido será o oposto, alterando apenas isso termos apenas um registro como resultado, considerando que o código um é o raiz, logo para um sentido maior usaremos um código maior:

SELECT LEVEL, CODIGO, (LPAD('   ', LEVEL*3) || CARGO) CARGO FROM RELATORIO_CARGOS START WITH CODIGO = 15 CONNECT BY PRIOR RESPONDE_PARA =  CODIGO;

Ao indicarmos o código quinze nosso resultado passa a contemplar apenas os registros que estão diretamente ligados a ele, sendo que agora ele passa a ser o nível um.

Para a ordenação deve-se tomar cuidado, o resultado é sempre formado nível a nível, sendo que os registros são posicionados logo abaixo de quem estão diretamente relacionados, uma ordenação faria com que esses registros se misturassem, perdendo assim o sentido da apresentação. Por isso para ordenar utilizamos uma nova cláusula ORDER SIBLINGS BY, que realiza a ordenação sem misturar os níveis:

SELECT LEVEL, CODIGO, (LPAD('   ', LEVEL*3) || CARGO) CARGOF FROM RELATORIO_CARGOS START WITH CODIGO = 1 CONNECT BY RESPONDE_PARA = PRIOR CODIGO ORDER SIBLINGS BY CARGO;

É possível ainda listar toda a hierarquia em cada registro, utilizando a função SYS_CONNECT_BY_PATH:

SELECT LEVEL, CODIGO, SYS_CONNECT_BY_PATH(CARGO, '/') CARGO FROM RELATORIO_CARGOS START WITH CODIGO = 1 CONNECT BY RESPONDE_PARA = PRIOR CODIGO;

Outra função interessante é o CONNECT_BY_ROOT, capaz de mostrar qualquer informação do nó raiz da consulta:

SELECT LEVEL, CODIGO, CARGO, CONNECT_BY_ROOT CARGO AS CARGO_ROOT FROM RELATORIO_CARGOS START WITH CODIGO = 4 CONNECT BY RESPONDE_PARA = PRIOR CODIGO;

Resumindo:

• As cláusulas START WITH e CONNECT BY são usadas para formar uma consulta hierárquica;
• A cláusula START WITH identifica o nó raiz (root);
• A cláusula CONNECT BY define o relacionamento self-join;
• Deve existir ao menos uma cláusula PRIOR junto ao CONNECT BY;
• O PRIOR determina a direção da consulta hierárquica;
• A pseudocoluna LEVEL identifica o nível de geração do nó;
• A cláusula ORDER SIBLINGS BY ordena registros pelo nível hierárquico sem comprometer o resultado;
• A função SYS_CONNECT_BY_PATH mostra o caminho completo dos nós;
• O operador SYS_CONNECT_BY_ROOT faz referencia ao nó raiz (root) de qualquer nó;
• A ordem das cláusulas, se usadas, devem ser SELECT, FROM, WHERE, START WITH, CONNECT BY, e ORDER BY;

Excluindo ramos da estrutura em arvore

Até agora em nossos exemplos não removemos nenhum registro do resultado, mas isso é possível, poderíamos utilizar a cláusula WHERE para limitar algum cargo, por exemplo, mas isso não necessariamente removeria um ramo de nossa estrutura, não que isso não possa ser feito, mas existe uma forma melhor para tal, é incluir essa remoção na cláusula CONNECT BY:

SELECT LEVEL, CODIGO, (LPAD('   ', LEVEL*3) || CARGO) CARGOF FROM RELATORIO_CARGOS START WITH CODIGO = 1 CONNECT BY RESPONDE_PARA = PRIOR CODIGO AND CODIGO != 4;

No exemplo acima o nó de código quatro e todos os seus descendentes foram removidos do resultado.

Resumindo:

• A cláusula CONNECT BY pode ser usada para excluir ramos inteiros da arvore;
• A cláusula WHERE pode excluir registros individualmente, mas não irá excluir os ramos de forma automática;