Recursividade com CTE

Posted on junho 14, 2011

1


As Common Table Expressions ou mais conhecidas como (CTE’s), são capazes de realizar recursividade, ou seja elas podem ser referenciadas por elas mesmos.

Para realizar a recursidade de uma CTE, podemos usar:

 

1. UNION ALL, UNION, EXCEPT e INTERSECT eles são chamados de membros de ancoragem (anchor members).

 

2. A chamada da recursividade terá que incluir os operadores citados acima.

 

3. Para finalizar a recursividade ela deve conter um  check (Verificação) na CTE, o mesmo terá que ser implícito, senão a CTE entrará em loop infinito.

 

Para melhor visualização, segue figura retirada do BOL.

 

 

image

 

 

1. A divisão do membro de ancoragem (anchor member) e o membro da recursividade terá que obedecer essa ordem mostrada na figura acima.

2. Faça uma consulta criando o anchor member ou podemos chamar tambêm de base result set que será a base que precisaremos para chamá-lo na recursividade (T0)

3.  Chame o anchor member para montar a sua recursividade (TI) que será a entrada, e (TI+1) para a saída ou seja o output, faça isso até que seja retornado um valor NULL na expressão criada.

4. O result set será o UNION ALL de T0 (Result Set) e Tn (Recursive Member)

 

Veja o seguinte exemplo,

 

USE tempdb

GO

 

IF OBJECT_ID (‘dbo.Empregados’) IS NOT NULL

      DROP TABLE dbo.Empregados

GO

 

IF OBJECT_ID (‘dbo.HistoricoDepartamentoFuncionario’) IS NOT NULL

      DROP TABLE dbo.HistoricoDepartamentoFuncionario

GO

 

–Criação da Tabela

CREATE TABLE dbo.Empregados

(

      ID SMALLINT NOT NULL,

      PrimeiroNome NVARCHAR(30)  NOT NULL,

      UltimoNome NVARCHAR(40) NOT NULL,

      Titulo NVARCHAR(50) NOT NULL,

      CodigoDepartamento SMALLINT NOT NULL,

      IDGerente INT NULL,

);

GO

ALTER TABLE Empregados

ADD CONSTRAINT PK_Empregados_ID

PRIMARY KEY (ID)

 

 

–Criação da Tabela

CREATE TABLE HistoricoDepartamentoFuncionario

(

      ID INT NOT NULL,

      Codigo SMALLINT NOT NULL,

      DataInicio DATE NOT NULL,

      Expediente VARCHAR(30) NOT NULL,

      DataFim     DATE NULL,

      DataModificacao DATE NULL

)

 

 

–Inserindo Dados na Tabela

INSERT INTO dbo.Empregados VALUES

 (1, N’Luan Moreno’, N’M. Maciel’, N’DBA’,16,NULL)

,(273, N’Luciano Caixeta’, N’Moreira’, N’Diretor’,3,1)

,(274, N’Gilberto’, N’Uchôa’, N’Diretor’,3,273)

,(275, N’Vitor’, N’Meriat’, N’Programador’,3,274)

,(276, N’Socorro’, N’Vieira’, N’DBA’,3,274)

,(285, N’André’, N’Anselmo’, N’Programador/DBA’,3,273)

,(286, N’Thiago’, N’Stuckert’, N’Penetration Tester’,3,285)

,(16,  N’Fabrício’,N’Braz’, N’Diretor’, 4, 273)

,(23,  N’Antônio’, N’Pádua’, N’DBA’, 4, 16);

 

 INSERT INTO HistoricoDepartamentoFuncionario (ID, Codigo, Expediente, DataInicio, DataFim, DataModificacao)

SELECT H.BusinessEntityID, H.DepartmentID, S.Name, H.StartDate, H.EndDate, H.ModifiedDate

FROM AdventureWorks2008.HumanResources.EmployeeDepartmentHistory AS H

INNER JOIN AdventureWorks2008.HumanResources.Shift AS S

ON H.ShiftID = S.ShiftID

 

Visualizando os registros na tabela:

 

image

 

image

 

Agora iremos fazer uma recursividade em cima de empregados, para visualizar o nível de cada empregado na empresa.

USE tempdb

GO

WITH RelatorioDiretores (IDGerente, IDEmpregado, Titulo, IDDepartamento, Nivel)

AS

(

      –Anchor Member

      SELECT E.IDGerente, E.ID AS IDEmpregado, Titulo, E.CodigoDepartamento AS IDDepartamento, 0 AS Nivel

      FROM Empregados AS E

      INNER JOIN HistoricoDepartamentoFuncionario AS HPF

      ON E.ID = HPF.ID

            AND hpf.DataFim IS NULL

      WHERE E.IDGerente IS NULL

      UNION ALL

      –Recursive Member

      SELECT  E.IDGerente, E.ID AS IDEmpregado, E.Titulo, E.CodigoDepartamento AS IDDepartamento, Nivel + 1

      FROM Empregados AS E

      INNER JOIN HistoricoDepartamentoFuncionario AS HPF

      ON E.ID = HPF.ID

            AND hpf.DataFim IS NULL

      INNER JOIN RelatorioDiretores AS D

      ON E.IDGerente = D.IDEmpregado

)

SELECT *

FROM RelatorioDiretores AS D

image

 

 

Assim conseguimos visualizar a qual Gerente um funcionário está vínculado, sendo assim a recursividade se da pelos níveis na busca das informações.

 

Nessa outra consulta estamos fazendo uma recursividade com data

 

WITH GeradorDatas AS

(

      SELECT CAST (‘2008-01-01’ AS DATETIME) Date –Data de Início

      UNION ALL

      SELECT Date + 1

      FROM GeradorDatas

      WHERE Date + 1 < = ‘2015-01-01’ –Data de Fim

)

SELECT ROW_NUMBER() OVER (ORDER BY Date) AS ID

      ,Date AS Data

      ,DATEPART(DAY, Date) AS Dia

      ,DATENAME(dw, Date) as Nome_Dia

      ,DATEPART(WEEK, Date) AS Semana

      ,MONTH (Date) AS Mes

      ,DATENAME(mm, Date) AS Nome_Mes

      ,DATEPART(QUARTER, Date) AS Trimestre

      ,DATEPART(YEAR, Date) AS Ano

FROM GeradorDatas

 

Agora se tentarmos executar essa CTE no qual a recursividade é maior que 100 vezes, por padrão teremos o seguinte problema.

 

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

 

Isso acontece porque temos que especificar um check (Validação) para que a recursividade não entre em loop infinito, aqui especifico WHERE Date + 1 < = ‘2015-01-01’ –Data de Fim  porém aqui temos mais de 100 registros retornados, para que esse erro não ocorra adicione na consulta a HINT  

OPTION (MAXRECURSION 0), isso fará com que a consulta seja executada sem limites.

 

No final temos o seguinte:

 

WITH GeradorDatas AS

(

      SELECT CAST (‘2008-01-01’ AS DATETIME) Date –Data de Início

      UNION ALL

      SELECT Date + 1

      FROM GeradorDatas

      WHERE Date + 1 < = ‘2015-01-01’ –Data de Fim

)

SELECT ROW_NUMBER() OVER (ORDER BY Date) AS ID

      ,Date AS Data

      ,DATEPART(DAY, Date) AS Dia

      ,DATENAME(dw, Date) as Nome_Dia

      ,DATEPART(WEEK, Date) AS Semana

      ,MONTH (Date) AS Mes

      ,DATENAME(mm, Date) AS Nome_Mes

      ,DATEPART(QUARTER, Date) AS Trimestre

      ,DATEPART(YEAR, Date) AS Ano

FROM GeradorDatas

OPTION (MAXRECURSION 0)

image

 

Agora não termos mais esse tipo de problema durante a execução de uma CTE recursiva.

 

Gostou do post, deixe seu comentário..

 

Abs

Posted in: Script's