Olá pessoal,

Hoje vou falar sobre consultas hierárquicas no banco de dados Oracle. Esse tipo de estrutura é muito útil para várias situações desde criação de itens de um menu até uma simulação de informação.

Bom...

Uma consulta hierárquica relaciona registros com base em uma relação de parentesco. Para que seja possível realizar uma consulta de forma hierárquica de um conjunto de dados, é necessário que exista uma relação de lógica entre os registros (pai e filho).

Vou dar um exemplo mais prático: criação de itens de menu. Digamos que queiramos criar um menu da seguinte forma:

1.Item 1
--1.1.Item 1, parte 1
--1.2.Item 1, parte 2
----1.2.1.Item 1, parte 2, seção 1
----1.2.2.Item 1, parte 2, seção 2
2.Item 2
--2.1.Item 2, parte 1
--2.2.Item 2, parte 2

Vou criar uma tabela chamada MENU para ajudar no entendimento:

CREATE TABLE menu
(
   cod      NUMBER (4),
   codpai   NUMBER (4),
   campo    VARCHAR (30),
   CONSTRAINT PK_MENU PRIMARY KEY (COD),
   CONSTRAINT FK_MENU_CODPAI FOREIGN KEY (CODPAI) REFERENCES DIARIO.MENU (COD)
);

Agora vou popular a tabela com registros de mesmo valor do citado no exemplo literal acima:

INSERT INTO MENU VALUES (1, NULL, 'Item 1');
INSERT INTO MENU VALUES (2,1,'Item 1,parte 1');
INSERT INTO MENU VALUES (3,1,'Item 1,parte 2');
INSERT INTO MENU VALUES (4,3,'Item 1,parte 2, seção 1');
INSERT INTO MENU VALUES (5,3,'Item 1,parte 2, seção 2');
INSERT INTO MENU VALUES (6,NULL,'Item 2');
INSERT INTO MENU VALUES (7,6,'Item 2,parte 1');
INSERT INTO MENU VALUES (8,6,'Item 2,parte 2');
COMMIT;

Verificando de forma normal os registros executando a consulta abaixo temos o resultado da Figura 2:

SELECT cod, codpai, campo FROM MENU ORDER BY 1 ASC, 2 ASC;

 Figura 1 - Resultado da consulta do exemplo 1. (Fonte: autoria própria)

Agora vamos ver como realizar uma consulta hierárquica contra essa tabela. Para isso, vamos entender um pouco do conceito.

A estrutura utilizada para realizar consultas dessa natureza é: START WITH ... CONNECT BY, e sua sintaxe pode ser vista na Figura 2.
Figura 2 - Sintaxe para consultas hierárquicas. (Fonte: Oracle Corporation)

O comando START WITH:
Determina quais são os registros “raiz” que devem ser utilizados para iniciar a consulta. No nosso caso, serão os registros com “codpai” nulo.

O comando CONNECT BY PRIOR:
Este comando especifica a relação entre registros pai e filho. No nosso caso, “cod” deverá ter um correspondente em “codpai”. É importante ressaltar que se a igualdade for invertida, ou seja, “codpai” foi igual a “cod” o resultado será totalmente diferente, pois o Oracle considera o argumento da esquerda como pai (ou raiz) e o da direita da igualdade como filho.

A consulta hierárquica para atender a solução é a seguinte:

SELECT cod,
           codpai,
           LEVEL,
           LPAD (' ',(LEVEL - 1) * 3, '-') || campo AS "Descrição"
      FROM menu
START WITH codpai IS NULL
CONNECT BY PRIOR cod = codpai
ORDER BY cod ASC, codpai ASC, campo ASC;

A pseudo-coluna LEVEL sempre está disponível para consultas hierárquicas e serve para indicar o nível em que o registro se encontra em relação à raiz.

O resultado da consulta acima pode ser conferido na Figura 3.

Figura 3 - Resultado da consulta do exemplo 1. (Fonte: autoria própria)

Consultas hierárquicas podem ter muitas utilidades, e os comandos connect by e start with podem nos ajudar de várias outras formas.

Por exemplo, uma simulação de valores de 1 a 10:

SELECT LEVEL "Número" FROM DUAL CONNECT BY LEVEL <= 10 ORDER BY 1 ASC;

O resultado da consulta acima pode ser conferido na Figura 4.
Figura 4 - Simulação de contagem de 1 a 10. (Fonte: autoria própria)

Recentemente passei por uma situação e usei connect by e start with para resolver. A situação foi a seguinte, precisava saber qual o primeiro domingo do mês corrente.

Alguém se habilita com uma solução?

Bom...

A que usei foi a seguinte: simulei dados para os 7 primeiros dias do mês (e com certeza o primeiro domingo estará entre eles), e numa consulta mais externa verifiquei qual das datas correspondia ao primeiro dia da semana, no caso o domingo. A consulta foi a seguinte:

SELECT dia
  FROM (SELECT TRUNC (SYSDATE, 'MM') + b.simulado dia FROM DUAL a,
                ( SELECT LEVEL - 1 simulado FROM DUAL CONNECT BY LEVEL <= 7) b
             )
 WHERE TO_CHAR (dia, 'D') = '1';

O resultado para a consulta pode ser conferida na Figura 5.
Figura 5 – Consulta para saber o primeiro domingo do mês corrente. (Fonte: autoria própria)

Essa foi uma forma simples e prática para a solução do meu problema.

Por hoje é isso, pessoal!

Espero ter conseguido passar o entendimento de forma clara e objetiva.

Até a próxima!

Referências:
Oracle Corporation – Hierarchical Queries. Disponível em: http://docs.oracle.com/cd/B28359_01/server.111/b28286/queries003.htm. Acessado: 18/03/2013.

Lusa, Diego Antonio - Uso do CONNECT BY no Oracle. Disponível em: http://www.devmedia.com.br/uso-do-connect-by-no-oracle/23647. Acessado:18/03/2013.