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.

Modelo de dados

O modelo de dados assumido para este artigo foi baseado no caso real citado, considerando apenas os dados relevantes ao artigo.

A estrutura de produtos é armazenada em duas tabelas, Produtos e HierProdutos. A primeira lista todos os produtos, com suas descrições físicas, e a segunda agrupa estes produtos em hierarquias.

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

Tabela 2 – Exemplo para a tabela Hierarquia de Produtos