Usando Expressões de Tabela (CTEs) Recursivas no DB2

O DB2 oferece suporte às expressões de tabela (CTEs) desde 2004, mas poucos desenvolvedores conhecem as CTEs recursivas. Este artigo mostra como criar CTEs recursivas e também apresenta exemplos práticos para pesquisar o organograma de uma empresa, apresentando a hierarquia de funcionários subordinados a qualquer um dos gerentes cadastrados no banco de dados.

Wagner Crivelini, DBA DB2 & SQL SERVER, IBM

Wagner CriveliniProfissional certificado IBM IT Specialist Level 2 – Expert, com mais de 15 anos de experiência com tecnologia de bancos de dados, é autor de mais de 50 artigos sobre este tópico, além de participar como colaborador de diversas comunidades, como DB2 on Campus, BigDataUniversity.com, Revista SQL MAGAZINE Brasil, SQLServerCentral.com, podcast DatabaseCast e o blog Banco de Dados & BI. Perfil My Developer Works.



11/Set/2012

Introdução

Alguns recursos do DB2 estão disponíveis há muito tempo, mas pouca gente usa. Isto acontece com uma funcionalidade das expressões de tabela (as CTEs, da sigla em inglês Common Table Expressions), que é suportada pelo DB2 desde 2004. Muita gente conhece e usa as CTEs, mas poucos desenvolvedores sabem que podem incluir recursividade em suas CTEs.

As CTEs recursivas oferecem grande versatilidade para nossas declarações SQL e o DB2 também as suporta há muito tempo. Estranhamente, este é um recurso pouco conhecido.

Para mostrar como usá-las, este artigo mostra um caso clássico: a pesquisa do organograma de uma empresa

Os exemplos apresentados são válidos para qualquer organograma de hierarquia simples (isto é, cada funcionário responde a apenas um chefe), independente do número de funcionários e número de níveis hierárquicos.


Situando o Exemplo

Neste artigo foi usado o DB2 Express-C v10.1 for WINDOWS (http://www-01.ibm.com/software/data/db2/express/download.html), porém o leitor pode executar as declarações SQL apresentadas aqui em qualquer versão ou edição do DB2.

O banco de dados considerado é uma das bases demo do DB2 chamada SAMPLE. Para instalá-la no WINDOWS, basta rodar o executável db2sampl.exe disponível no diretório diretório_de_instalacao\BIN.

A base considera um modelo de dados peculiar em relação à estrutura de funcionários. Ela usa uma associação entre as tabelas de funcionários (EMPLOYEE) e departamentos (DEPARTAMENT) para reconhecer três relações:

  1. Em qual departamento trabalha cada funcionário
  2. Quem é o gerente de cada departamento
  3. Qual a hierarquia entre os departamentos

Ao contrário da maioria dos modelos de dados, a base não informa diretamente qual é a hierarquia de funcionários, informando apenas a hierarquia dos departamentos. A Figura 1 mostra um diagrama detalhando as relações entre estas duas tabelas.

Figura 1. Modelo do banco de dados SAMPLE.


Entendendo as CTEs Recursivas

CTEs recursivas incluem todos os requisitos das CTEs comuns e mais algumas outras restrições. Elas são temporárias como qualquer CTE e também usam a mesma sintaxe para sua definição.

Porém existem algumas peculiaridades específicas das CTEs recursivas:

  1. Precisam conter duas declarações SELECT unidas pelo operador UNION ALL
  2. A primeira declaração seleciona o registro que será usado como ponto de partida
  3. A segunda declaração faz referência à tabela que contém os dados recursivos E também referencia a própria CTE
  4. Além disso, a segunda declaração deve obrigatoriamente conter um critério de parada, que irá definir o momento de encerramento do processo recursivo
  5. Não é permitido o uso de junções de tabelas (JOINs) ou cláusulas de agrupamento (GROUP BY, OVER, etc.) na CTE recursiva.

É fundamental que o desenvolvedor preste atenção na definição do critério de parada, do contrário a CTE entrará num laço infinito.

A seguir, temos um exemplo da sintaxe das CTEs recursivas. Na base SAMPLE, a hierarquia de departamentos está representada na tabela DEPARTMENT através de um autorrelacionamento: cada departamento (campo DEPTNO) é subordinado a um departamento pai (campo ADMRDEPT). Veja novamente a Figura 1.

A Tabela 1 mostra os dados da tabela DEPARTMENT incluindo o código do gerente do departamento (MGRNO) que será útil quando tratarmos os dados de funcionários.

ADMRDEPTDEPTNOMGRNO
A00 A00 10
A00 B01 20
A00 C01 30
A00 D01 NULL
D01 D11 60
D01 D21 70
A00 E01 50
E01 E11 90
E01 E21 100
E01 F22 NULL
E01 G22 NULL
E01 H22 NULL
E01 I22 NULL
E01 J22 NULL

A última coisa que falta é definir o ponto de partida da CTE recursiva. Neste exemplo, vamos considerar que iniciaremos o processo no registro com código ‘A00’, que é o departamento principal da empresa. Veja que este registro tem o código do departamento igual ao código do departamento pai.

Juntando todas estas informações, temos na Listagem 1 a CTE recursiva que mostra a hierarquia de departamentos. Obviamente, os campos PAI e FILHO representam esta relação.

Listagem 1. Exemplo de CTE recursiva
WITH cteRECURSIVA (PAI, FILHO, NIVEL) AS (
 --ponto de partida
 SELECT ADMRDEPT AS PAI, DEPTNO AS FILHO, 1 AS NIVEL FROM DEPARTMENT WHERE DEPTNO = 'A00'
 UNION ALL
 --declaracao recursiva
 SELECT T.ADMRDEPT AS PAI, T.DEPTNO AS FILHO, C.NIVEL + 1 AS NIVEL
 FROM DEPARTMENT T, cteRECURSIVA C
 WHERE T.ADMRDEPT = C.FILHO
 --criterio de parada
 AND COALESCE(T.ADMRDEPT, '0') NOT IN ('0' , T.DEPTNO)
)
-- consulta ao resultado da CTE
SELECT * FROM cteRECURSIVA ORDER BY PAI, FILHO

O leitor que executar a consulta acima possivelmente se fará a pergunta: se o resultado obtido é idêntico à listagem da tabela DEPARTMENT, qual é a vantagem da consulta recursiva?

De fato, quando escolhemos o departamento principal da empresa como ponto de partida, o resultado da CTE recursiva é realmente idêntico ao da listagem da tabela DEPARTMENT.

Porém devemos lembrar que a consulta recursiva retorna a hierarquia completa abaixo de qualquer um dos departamentos, coisa que não seria obtida com facilidade através de uma declaração de seleção. A consulta recursiva é o meio mais simples e versátil de conseguir esta informação.

Um aspecto importante a ser considerado é que o exemplo da Listagem 1 mostra o caso da CTE recursiva baseada em uma tabela com autorrelacionamento. Em grande parte dos modelos de dados que envolvem informações de funcionários, este autorrelacionamento existe na própria tabela de funcionários. Nesta situação, uma declaração semelhante a esta já seria suficiente para pesquisa em um organograma.


O Caso da Base SAMPLE

Como já foi mencionado, a base SAMPLE usa duas tabelas para representar a hierarquia de funcionários. Isso exige uma preparação, pois as CTEs recursivas não suportam junções de tabela, como se viu anteriormente.

Esta questão pode ser tratada de diversas maneiras. O leitor mais experiente poderia definir uma CTE comum que seria referenciada dentro da CTE recursiva. Esta abordagem tem diversas vantagens para as situações práticas, mas lamentavelmente ela não é didática.

Por esta razão, usamos outra estratégia: criamos uma visão (vwFuncionario) com os campos necessários. Esta visão considera quatro situações distintas:

  1. Funcionários subordinados ao gerente do departamento
  2. Funcionários que são gerentes de departamento e que estão subordinados ao gerente do departamento pai
  3. Funcionários alocados em departamentos sem gerente e que estão subordinados ao gerente do departamento pai
  4. Funcionários que não estão subordinados a ninguém (o presidente da empresa) Estas quatro condições são atendidas com a declaração apresentada na Listagem 2. O primeiro SELECT cobre o primeiro caso. Na segunda declaração são usadas funções CASE para atender as condições 2, 3 e 4, respectivamente.
Listagem 2. Criação da visão com hierarquia de funcionários
CREATE VIEW vwFuncionario AS
SELECT D.ADMRDEPT, D.DEPTNO, D.MGRNO, E.EMPNO
FROM EMPLOYEE E
	INNER JOIN DEPARTMENT D ON E.WORKDEPT = D.DEPTNO
WHERE D.MGRNO <> E.EMPNO
UNION ALL
SELECT D.ADMRDEPT, D.DEPTNO,
	CASE WHEN D.ADMRDEPT < D.DEPTNO
	THEN (
		SELECT
	                       CASE WHEN COALESCE(X.MGRNO,0) > 0
		       THEN X.MGRNO
		       ELSE (SELECT Y.MGRNO FROM DEPARTMENT Y WHERE Y.DEPTNO = X.ADMRDEPT)
			END MGRNO
		FROM DEPARTMENT X WHERE D.ADMRDEPT = X.DEPTNO
		)
	ELSE D.MGRNO
	END AS MGRNO, 	
	E.EMPNO
FROM EMPLOYEE E
	INNER JOIN DEPARTMENT D ON E.WORKDEPT = D.DEPTNO
WHERE D.MGRNO = E.EMPNO

Agora temos de fato um objeto com a hierarquia de funcionários e podemos usar esta visão para definição da CTE recursiva. Para demonstrar a versatilidade desta consulta, assumimos que o ponto de partida é o funcionário de código ‘50’. A Listagem 3 apresenta esta consulta.

Listagem 3. CTE recursiva para a hierarquia de funcionários
WITH cteHIERARQUIA (PAI, FILHO, DEPARTAMENTO, NIVEL) AS (
SELECT MGRNO AS PAI, EMPNO AS FILHO, DEPTNO AS DEPARTAMENTO, 1 AS NIVEL
FROM vwFuncionario
WHERE EMPNO = 50
UNION ALL
SELECT V.MGRNO AS PAI, V.EMPNO AS FILHO, V.DEPTNO AS DEPARTAMENTO, C.NIVEL + 1 AS NIVEL
FROM cteHIERARQUIA C, vwFuncionario V
WHERE V.MGRNO = C.FILHO
	AND COALESCE(V.MGRNO, 0) NOT IN (0 , V.EMPNO)
)

SELECT PAI, FILHO, DEPARTAMENTO, NIVEL
FROM cteHIERARQUIA CTE
ORDER BY PAI, FILHO

A Tabela 2 mostra o resultado da consulta da Listagem 3. São istados apenas os funcionários que estão direta ou indiretamente subordinados ao funcionário ‘50’, além do chefe imediato do próprio funcionário ‘50’.

Tabela 2. Hierarquia dos funcionários subordinados a ‘50’

PAIFILHODEPARTAMENTONIVEL
10 50 E01 1
50 90 E11 2
50 100 E21 2
90 280 E11 3
90 290 E11 3
90 300 E11 3
90 310 E11 3
90 200280 E11 3
90 200310 E11 3
100 320 E21 3
100 330 E21 3
100 340 E21 3
100 200330 E21 3
100 200340 E21 3

O processo da CTE recursiva é simples e depende fundamentalmente do ponto de partida. Além disso, ele é bastante versátil: neste exemplo, a hierarquia a partir do funcionário ‘50’ tinha três níveis hierárquicos, o que quer dizer que a CTE foi repetida mais duas vezes após a definição do ponto de partida.

Para atender uma hierarquia mais complexa (com 10 níveis hierárquicos, por exemplo), não seria necessário mudar absolutamente nada na CTE. Os pontos-chave são o ponto de partida e o critério de parada.

O processo é o mesmo independente da quantidade de funcionários ou do número de níveis hierárquicos, afetando apenas o tempo de execução das consultas.

Como há uma relação direta entre o número de níveis hierárquicos e o número de repetições do processo recursivo, fica evidente que o processo também independe do número de repetições da recursividade.

A única condição necessária para usar a solução apresentada aqui é que o organograma tenha uma hierarquia simples, ou seja, quando cada funcionário tem apenas um chefe, que caracteriza o que é conhecido como relação pai-filho.

Nos casos em que existem hierarquias múltiplas no organograma é necessário adotar uma abordagem diferente, como mostra o artigo de Torsten Steinbach, citado nas Referências.


Conclusão

Este artigo mostra como definir CTEs recursivas e como usá-las para pesquisar os organogramas que se baseiam em hierarquias simples (quando existe uma relação do tipo pai-filho).

CTEs recursivas são simples e extremamente versáteis. O desenvolvedor deve atentar exclusivamente ao ponto de partida e o critério de parada, que são pontos-chave da operação.

Número de indivíduos listados no organograma e a quantidade de níveis de recursividade não afetam o processo, a não ser em relação ao tempo de execução das consultas.


Referências

Venigalla, Srini. Expanding Recursive Opportunities with SQL UDFs in DB2 v7.2. IBM DEVELOPERWORKS. 01/Mar/2002.
http://www.ibm.com/developerworks/data/library/techarticle/0203venigalla/0203venigalla.html

Steinbach, Torsten. Migrating Recursive SQL from Oracle to DB2 UDB. IBM DEVELOPERWORKS. 17/Jul/2003.
http://www.ibm.com/developerworks/data/library/techarticle/0307steinbach/0307steinbach.html

Gennick, Jonathan. Understanding the WITH Clause. JONATHAN GENNICK's website. 01/Jul/2003. http://www.gennick.com/with.html

Vanroose, Peter. CTEs in DB2 for z/OS: More Powerful Than You Can Imagine. IDUG 2007 EUROPE. 06/Nov/2007.
http://www.abis.be/resources/presentations/idug20071106ctedb2.pdf

Comentários

developerWorks: Conecte-se

Los campos obligatorios están marcados con un asterisco (*).


Precisa de um ID IBM?
Esqueceu seu ID IBM?


Esqueceu sua senha?
Alterar sua senha

Ao clicar em Enviar, você concorda com os termos e condições do developerWorks.

 


A primeira vez que você entrar no developerWorks, um perfil é criado para você. Informações no seu perfil (seu nome, país / região, e nome da empresa) é apresentado ao público e vai acompanhar qualquer conteúdo que você postar, a menos que você opte por esconder o nome da empresa. Você pode atualizar sua conta IBM a qualquer momento.

Todas as informações enviadas são seguras.

Elija su nombre para mostrar



Ao se conectar ao developerWorks pela primeira vez, é criado um perfil para você e é necessário selecionar um nome de exibição. O nome de exibição acompanhará o conteúdo que você postar no developerWorks.

Escolha um nome de exibição de 3 - 31 caracteres. Seu nome de exibição deve ser exclusivo na comunidade do developerWorks e não deve ser o seu endereço de email por motivo de privacidade.

Los campos obligatorios están marcados con un asterisco (*).

(Escolha um nome de exibição de 3 - 31 caracteres.)

Ao clicar em Enviar, você concorda com os termos e condições do developerWorks.

 


Todas as informações enviadas são seguras.


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=80
Zone=Information Management
ArticleID=834422
ArticleTitle=Usando Expressões de Tabela (CTEs) Recursivas no DB2
publish-date=09112012