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;
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)
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.
