Uso do CONNECT BY no Oracle

Consultas Hierárquicas

Uma consulta hierárquica relaciona um conjunto de tuplas (registros) com base em uma relação de parentesco, normalmente expressa através de ligações do tipo pai/filho estabelecidas entre os dados de uma tabela.

Para que seja possível consultar de forma hierárquica um conjunto de dados é necessário atender dois importantes requisitos: relacionar os registros logicamente e dispor de um mecanismo de consulta adequado.

O primeiro, refere-se a criação de um atributo para guardar qual é o registro “pai” da tupla. Nele será armazenado o valor da chave primária do seu ancestral direto. Caso seja o primeiro da linhagem, ou seja, “o pai de todos”, o atributo deve ficar com valor nulo.

Para que a relação estabelecida entre os registros tenha utilidade, devemos dispor de alguma ferramenta de consulta capaz de processar a relação na forma que ela foi definida, retornando os registros hierarquicamente ordenados. Contudo, sabemos de antemão que um comando SELECT convencional não é adequado para essa necessidade. Precisamos, portanto, de uma ferramenta de consulta especializada.

Pensando justamente em oferecer uma solução adequada para este problema, o banco de dados Oracle disponibiliza uma cláusula chamada connect by, muito Poderosa na hora de realizar consultas que necessitam retornar conjuntos de dados numa ordem hierárquica preestabelecida.

CONNECT BY na prática

Para compreender como o connect by funciona em termos práticos, vamos usar o bom e velho artifício do exemplo.

Imaginemos um simples sistema que permita criar uma nova mensagem ou comentar mensagens já existentes, sem qualquer restrição de nível. Ou seja, será possível comentar um comentário de um comentário e assim sucessivamente, conforme demonstrado na Listagem 1.

            Usuário 1: Alguém sabe o que é equinócio?
 ---|Resp. Usuário 2: Sei lá. Acho q tem relação com criação de equinos!
 ----------|Resp. Usuário 3: Concordo com o que o Usuário 2 comentou.
 --------------------|Resp. Usuário 2: Alguém concordou comigo uma vez. Fato histórico :D!
 ---------------------------|Resp. Usuário 4: Pessoal, equinócio não tem nada a ver com equinos. Na verdade, ele refere-se ao período do ano onde os dias tem a mesma duração das noites.
            

Nosso sistema terá apenas uma tabela para armazenar as mensagem. A estrutura dela é mostrada abaixo. Observe que o atributo PARENT_ID é chave estrangeira e aponta para a ancestral direto da mensagem. É através do PARENT_ID que será estabelecida a relação hierárquica entre os registros da tabela. O DDL é apresentado na Listagem 2.

            CREATE TABLE
         MENSAGEM (ID_MENSAGEM NUMBER(10) NOT NULL,
         DATA_ENVIO DATE DEFAULT SYSDATE NOT NULL,
         TEXTO CLOB DEFAULT '' NOT NULL,
         LOGIN VARCHAR(200) NOT NULL,
         PARENT_ID NUMBER(10),
           PRIMARY KEY( ID_MENSAGEM),
           FOREIGN KEY ( PARENT_ID) REFERENCES MENSAGEM(ID_MENSAGEM)
)
            

Faremos uma carga inicial de dados na tabela MENSAGEM, de modo que ao final ela contenha os registros apresentados pela Tabela 1:

Para retornar os dados da tabela MENSAGEM em ordem hierárquica executaremos a seguinte consulta no banco:

            select level as nivel,
decode(m.parent_id, null,m.login|| ': ',lpad(' ',(level - 1) *3,'-')|| '> Resp. de ' ||m.login ||': ' ) || m.texto as mensagens
from mensagem m
start with m.id_mensagem in (select j.id_mensagem from mensagem j where j.parent_id is null)
connect by prior m.id_mensagem = m.parent_id

            

Analisando os resultados apresentados na tabela 2 podemos observar que:

• A coluna NIVEL indica o nível hierárquico da mensagem em relação a raiz.
• Todos os registros de nível 1 são “raízes”, pois não apresentam nenhum ancestral direto.
• Na tabela MENSAGEM, os registros “raiz” são aqueles que possuem o atributo PARENT_ID nulo.

Vamos agora entender como funciona o operador connect by, utilizando a nossa query (Listagem 3) como base para a explicação. Para fins didáticos, cada conceito será apresentado em um tópico separado.

• Pseudo-coluna LEVEL

Toda a consulta hierárquica tem disponível uma pseudo-coluna chamada level que contém a profundidade ou nível do registro corrente em relação a sua raiz.

• Comando START WITH.

Determina quais são os registros “raiz” que devem ser utilizados para iniciar a consulta. Na nossa query utilizamos um subselect que retorna a chave primária de todas as mensagens cujo parent_id é nulo. Desta forma, a consulta sempre irá iniciar onde o id_mensagem é igual a um dos registros “raiz”.

• Comando CONNECT BY PRIOR.

Este comando é o que de fato especifica a relação entre registros pai e filho. Em nossa consulta utilizamos a igualdade m.id_mensagem = m.parent_id para indicar a relação. É importante ressaltar que se a igualdade for invertida, ou seja, m.parent_id = m.id_mensagem 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.

Conclusão

Relacionamentos do tipo pai/filho são comuns e estão presentes nos mais diversos tipos de sistemas de informação. Contudo, trabalhar com estas relações hierárquicas em consultas nem sempre representam uma atividade simples quando as ferramentas adequadas não estão disponíveis ou não são usadas.

Conforme podemos observar, o Oracle, através da cláusula CONNECT BY oferece um dispositivo funcional e prático para trabalhar com consultas hierárquicas. Juntamente com ele, podemos utilizar outros comandos muito úteis como, por exemplo, SYS_CONNECT_BY_PATH e CONNECT_BY_ROOT. Se desejar saber mais sobre eles, sugiro que você acesse o manual da Oracle sobre consultas hierárquicas.

Espero que este artigo tenha sido útil a você!

Um abraço e até a próxima oportunidade.