Banco de Dados - SQL Server

Utilizando queries recursivas no SQL Server 2005

Neste artigo, o objetivo é explorar a utilização de CTE’s para buscas em estruturas hierárquicas, baseadas em tabelas com auto-referenciamento, do tipo Id/ParentId.

por Half Scheidl



Introdução

Entre os novos recursos do SQL Server 2005, estão as Common Table Expressions (CTE), uma forma de definir uma view, válida apenas no escopo do batch atual. Sua sintaxe resumida é a seguinte:

WITH [name] AS (

[instrução SELECT]

)

Com este recurso, é possível simplificar queries mais complexas e torná-las mais legíveis, eliminando sub-queries, por exemplo, ou ainda solucionar problemas de maneira modular, isto é, iniciando pelas partes mais simples. No caso de mais de uma CTE’s por consulta, a segunda pode incluir a primeira em sua cláusula FROM, e assim por diante.

Neste artigo, o objetivo é explorar a utilização de CTE’s para buscas em estruturas hierárquicas, baseadas em tabelas com auto-referenciamento, do tipo Id/ParentId.

Recursividade

Recursão é a chamada de uma função por ela mesma. O exemplo clássico de recursão é o cálculo do fatorial de um número natural:

Código 1 – Pseudo-código: cálculo do fatorial de um número n

int Fatorial (int n) {

if (n==1)

return 1;

else

return n * Fatorial(n-1);

}

Dado um número n, a rotina calcula n * (n – 1) * (n-2) * ... 1. Como exemplo, o fatorial de 4, cuja notação matemática é 4!, é dado por 4 * 3 * 2 * 1 = 24. Vale notar que esta solução para o cálculo de fatorial não é a melhor em termos computacionais, mas este assunto fica para depois...

Consultas recursivas

No SQL Server 2005, é possível escrever CTE’s que fazem referência a si próprias, incluindo seu nome numa cláusula FROM. Uma das aplicações é a construção de consultas simples para estruturas relacionais hierárquicas, como uma tabela de funcionários com um auto-relacionamento definindo os subordinados. A aplicação em que se baseia este artigo é a hierarquia de produtos de uma empresa de semi-condutores.

Para utilização de uma query recursiva, a CTE deve possuir duas intruções SQL, separadas obrigatoriamente por UNION ALL, sendo a primeira chamada de “âncora”, isto é, a primeira consulta define o ponto de entrada na hierarquia. A segunda consulta inclui o nome da CTE em sua cláusula FROM. Tal estrutura está exemplificada no Código 2.

Código 2 – Estrutura básica de uma query recursiva

WITH Filhos AS (

-- Membro âncora

SELECT IdHierarquia, IdHierarquiaPai, Descricao

FROM [Tabela de Hierarquia]

WHERE Descricao = "NONONO"

UNION ALL

-- Filhos

SELECT h.IdHierarquia, h.IdHierarquiaPai, h.Descricao

FROM [Filhos] t1

INNER JOIN [Tabela de Hierarquia] t2 ON t1.IdHierarquiaPai = t2.IdHierarquia

)

SELECT * FROM Filhos

É possível limitar o número de recursões na query, evitando loops infinitos, através do parâmetro option (maxrecursion n), incluído após a última instrução. Quando não informado, o valor padrão para o limite de recursões é 100. Caso uma query exceda esse limite, é retornado o erro abaixo, substituindo [n] pelo número definido com o parâmetro maxrecursion.

The statement terminated. The maximum recursion [n] has been exhausted before statement completion.


O Código 3 pode ser utilizado para criar as tabelas utilizadas.

Código 3 – Instrução DDL para criação das tabelas

CREATE DATABASE DbArtigo;

GO

USE DbArtigo;

CREATE TABLE HierProdutos (

IdHierarquia INT PRIMARY KEY,

IdHierarquiaPai INT CONSTRAINT fk_HierProdutos FOREIGN KEY REFERENCES HierProdutos (IdHierarquia),

Descricao VARCHAR(100)

)

CREATE TABLE Produtos (

IdProduto INT PRIMARY KEY,

IdHierarquia INT CONSTRAINT fk_Produtos_HierProdutos FOREIGN KEY REFERENCES HierProdutos (IdHierarquia),

CodProduto VARCHAR(100),

DescricaoProd VARCHAR(100)

)

As tabelas abaixo apresentam uma lista de hierarquias e produtos, para exemplificar o contexto de negócio. Nos itens seguintes, apresentam-se alguns problemas para o modelo de dados proposto.


Tabela 1 – Exemplo para a tabela de Produtos

IdProduto

IdHierarquia

CodProduto

DescProduto

1

9

P101

Processador 101

2

9

P102

Processador 102

3

10

P201

Processador 201

4

10

P202

Processador 202

5

17

P402

Processador 401

6

20

R001

Roteador 001

7

14

M001

Memória 1GB

8

8

M0012

Memória 512MB

9

8

M0013

Memória 512MB DDR2

Tabela 2 – Exemplo para a tabela Hierarquia de Produtos

IdHierarquia

IdHierarquiaPai

Descricao

1

NULL

Desktop

2

NULL

Servidores

3

NULL

Equipamentos de Rede

4

1

Placa-mãe

5

1

Placa de rede

6

1

Processador

7

1

Placa de Vídeo

8

1

Memória

9

6

Processador núcleo simples

10

6

Processador núcleo duplo

11

2

Placa-mãe

12

2

Placa de rede

13

2

Processador

14

2

Memória

15

13

Processador núcleo simples

16

13

Processador núcleo duplo

17

13

Processador de quatro núcleos

18

3

Roteador

19

3

Switch

Problemas

Para o modelo de dados descrito, procura-se resposta às seguintes perguntas:

1) Quais os produtos e seus respetivos níveis, abaixo de Desktop?

2) Qual a profundidade de cada nível da hierarquia?


WITH Niveis AS (

-- Membro âncora

SELECT IdHierarquia, IdHierarquiaPai, Descricao

FROM HierProdutos

WHERE Descricao = "Desktop"

UNION ALL

-- Filhos

SELECT h.IdHierarquia, h.IdHierarquiaPai, h.Descricao

FROM HierProdutos h

INNER JOIN Niveis ON h.IdHierarquiaPai = Niveis.IdHierarquia

)

SELECT t1.Descricao AS [Classificação], t2.CodProduto [Código do Produto], t2.DescricaoProd AS [Descrição do Produto]

FROM Niveis t1 INNER JOIN Produtos t2 ON

t1.IdHierarquia = t2.IdHierarquia

O resultado se encontra na figura a seguir.

Para o Problema 2, basta definirmos um número para o nível 0 (registros em que IdHierarquiaPai é nulo) e incrementá-lo a cada recursão (Código 5).

Código 5 – Obter a profundidade de cada classificação da hierarquia

WITH Niveis AS (

-- Membro âncora

SELECT IdHierarquia, IdHierarquiaPai, Descricao,

0 AS n -- nível 0

FROM HierProdutos

WHERE IdHierarquiaPai IS NULL

UNION ALL

-- Filhos

SELECT h.IdHierarquia, h.IdHierarquiaPai, h.Descricao,

n+1 -- demais níveis

FROM HierProdutos h

INNER JOIN Niveis ON h.IdHierarquiaPai = Niveis.IdHierarquia

)

SELECT * FROM Niveis t1

O resultado dessa consulta encontra-se na figura a seguir.

Variações no código 5 permitem exibir os resultados de maneira diversificada . Um exemplo é concatenar espaços, em quantidade igual ao nível do produto na hierarquia. No Código 6 utilizamos a função REPLICATE, que replica uma string tantas vezes quanto desejado.

Código 6 – Retornar todos os produtos abaixo de um certo nível

WITH Niveis AS (

-- Membro âncora

SELECT IdHierarquia, IdHierarquiaPai, Descricao,

0 AS Nivel -- nível 0

FROM HierProdutos

WHERE IdHierarquiaPai IS NULL

UNION ALL

-- Filhos

SELECT h.IdHierarquia, h.IdHierarquiaPai, h.Descricao,

Nivel+1

FROM HierProdutos h

INNER JOIN Niveis ON h.IdHierarquiaPai = Niveis.IdHierarquia

)

SELECT IdHierarquia, IdHierarquiaPai, REPLICATE(" ",Nivel) + Descricao AS Descricao

FROM Niveis t1

Analisando o resultado abaixo, pode-se observar um detalhe: os niveis não são exibidos como esperado, isto é, os filhos não vêm logo após os respectivos pais.

Fica como desafio então alterar a query para exibir o resultado esperado, ou seja, os filhos logo após os respectivos pais.

Considerações finais

O novo recurso Common Table Expressions do SQL Server 2005 permite a solução de problemas de maneira modular, assim como a construção de queries recursivas de maneira rápida e objetiva.

Entretanto, o esforço de aprendizado inicial e sua lógica diferenciada podem inibir sua aplicação em hierarquias pai e filho.

Em relação ao desafio proposto, acredito haver mais de uma solução, entretanto, para os que quiserem seguir o meu caminho, antecipo que utilizei as funções POWER e ROW_NUMBER. O resultado obtido encontra-se abaixo.

Hierarquia de produtos devidamente ordenada

Desktop

Placa-mãe

Placa de rede

Processador

Processador núcleo simples

Processador núcleo duplo

Placa de Vídeo

Memória

Servidores

Placa-mãe

Placa de rede

Processador

Processador núcleo simples

Processador núcleo duplo

Processador de quatro núcleos

Memória

Equipamentos de Rede

Roteador

Switch

A propósito, a útil e poderosa ROW_NUMBER, juntamente com as demais funções de ranking do SQL Server 2005, serão o assunto do próximo artigo. Aguardo seus comentários e até a próxima!

Anexo 1 – Dados de Teste

INSERT INTO HierProdutos VALUES (1,NULL,"Desktop");

INSERT INTO HierProdutos VALUES (2,NULL,"Servidores");

INSERT INTO HierProdutos VALUES (3,NULL,"Equipamentos de Rede");

INSERT INTO HierProdutos VALUES (4,1,"Placa-mãe")

INSERT INTO HierProdutos VALUES (5,1,"Placa de rede")

INSERT INTO HierProdutos VALUES (6,1,"Processador")

INSERT INTO HierProdutos VALUES (7,1,"Placa de Vídeo")

INSERT INTO HierProdutos VALUES (8,1,"Memória")

INSERT INTO HierProdutos VALUES (9,6,"Processador núcleo simples")

INSERT INTO HierProdutos VALUES (10,6,"Processador núcleo duplo")

INSERT INTO HierProdutos VALUES (11,2,"Placa-mãe")

INSERT INTO HierProdutos VALUES (12,2,"Placa de rede")

INSERT INTO HierProdutos VALUES (13,2,"Processador")

INSERT INTO HierProdutos VALUES (14,2,"Memória")

INSERT INTO HierProdutos VALUES (15,13,"Processador núcleo simples")

INSERT INTO HierProdutos VALUES (16,13,"Processador núcleo duplo")

INSERT INTO HierProdutos VALUES (17,13,"Processador de quatro núcleos")

INSERT INTO HierProdutos VALUES (18,3,"Placas de Rede")

INSERT INTO HierProdutos VALUES (19,3,"Roteador")

INSERT INTO HierProdutos VALUES (20,3,"Switch")

INSERT INTO Produtos VALUES (1,9,"P101","Processador 101")

INSERT INTO Produtos VALUES (2,9,"P102","Processador 102")

INSERT INTO Produtos VALUES (3,10,"P201","Processador 201")

INSERT INTO Produtos VALUES (4,10,"P202","Processador 202")

INSERT INTO Produtos VALUES (5,17,"P402","Processador 401")

INSERT INTO Produtos VALUES (6,20,"R001","Roteador 001")

INSERT INTO Produtos VALUES (7,14,"M001","Memória 1GB")

INSERT INTO Produtos VALUES (8,8,"M0012","Memória 512MB")

INSERT INTO Produtos VALUES (9,8,"M0013","Memória 512MB DDR2")

Half Scheidl

Half Scheidl - Engenheiro da Computação pela Escola Politécnica da USP, trabalha como consultor de Business Intelligence na Arbit (www.arbit.com.br).