Lição 1: Construindo consultas recursivas com CTE

junho 1, 2011

 

O conceito de CTE é muito similar a tabelas derivadas, porém com CTEs você pode cruzar com outros resultados para resolver um ou mais problemas, executando consultas recursivas.

 

Expressão de Tabela Comum (Common Table Expressions)

 

Sintaxe:

WITH – Cláusula onde contém o comando válido de select.

Select – Consulta que será realizada utilizando a CTE.

 

WITH EmpTitle AS

(SELECT JobTitle, COUNT(*) NumTitles

FROM HumanResources.Employee

GROUP BY JobTitle)

 

SELECT b.BusinessEntityID, b.JobTitle, a.NumTitles

FROM EmpTitle a INNER JOIN HumanResources.Employee b ON b.JobTitle = a.JobTitle

GO

 

Uma CTE recursiva expande a definição de uma expressão de tabela e consiste em duas partes:

– Uma consulta âncora, que é a fonte da recursividade, juntamente com a declaração “UNION ALL” e uma segunda consulta.

– Uma consulta externa que referencia a rotina e especifica o numero de níveis de recursão.

Por exemplo, a consulta abaixo permite que você especifique um employee e retorna  os niveis da gerencia acima desse empregado na organização, com um limite de 25 niveis de organização.

 

DECLARE @EmployeeToGetOrgFor INT=126;

WITH EMP_cte(BusinessEntityID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel)

AS (SELECT E.BusinessEntityID, E.OrganizationNode, P.FirstName, P.LastName, E.JobTitle, 0

       FROM HumanResources.Employee e INNER JOIN Person.Person AS p

           ON P.BusinessEntityID = E.BusinessEntityID

       WHERE E.BusinessEntityID = @EmployeeToGetOrgFor

  UNION ALL

      SELECT E.BusinessEntityID, E.OrganizationNode, P.FirstName, P.LastName,     E.JobTitle, RecursionLevel+1

    FROM HumanResources.Employee E INNER JOIN EMP_cte

           ON E.OrganizationNode = EMP_cte.OrganizationNode.GetAncestor(1)

       INNER JOIN Person.Person P ON P.BusinessEntityID = E.BusinessEntityID)

 

SELECT EMP_cte.RecursionLevel, EMP_cte.BusinessEntityID, EMP_cte.FirstName, EMP_cte.LastName,   EMP_cte.OrganizationNode.ToString() AS OrganizationNode,

P.FirstName AS ‘ManagerFirstName’, P.LastName AS ‘ManagerLastName’

FROM EMP_cte INNER JOIN HumanResources.Employee E

                         ON EMP_cte.OrganizationNode.GetAncestor(1) = E.OrganizationNode

                      INNER JOIN Person.Person P ON P.BusinessEntityID = E.BusinessEntityID

ORDER BY RecursionLevel,EMP_cte.OrganizationNode.ToString()

OPTION (MAXRECURSION 25);

 

image

 

A primeira consulta com a cláusula WITH define o resultado ancora. A segunda consulta executada recursivamente até o nível máximo de recursão contra a consulta âncora.

A recursividade é realizada pelo INNER JOIN na CTE:

INNER JOIN EMP_cte

A consulta externa é então utilizada para retornar o resultado da operação recursiva enquanto dados adicionais são necessários. A cláusula “OPTION” especifica o número máximo de níveis de recursão serão permitidos.

Dica para o exame:

Caso haver um bloco de comando antes da palavra chave “WITH” utilizar “;” para finalizar o bloco de comando anterior.

 

[]’s

Andressa Alves Martins

Twitter: Dre_Martins Skype: Dre.alvesm

Blog: http://sqlgo.wordpress.com