Consultas recursivas que usam expressões de tabelas comuns
Uma CTE (expressão de tabela comum) fornece a significativa vantagem de ser capaz de se autorreferenciar, criando, portanto uma CTE recursiva. Uma CTE recursiva é aquela em que uma CTE inicial é executada repetidamente para retornar subconjuntos de dados até que o resultado completo seja obtido.
Uma consulta é denominada consulta recursiva quando faz referência a uma CTE recursiva. Retornar dados hierárquicos é de uso comum em consultas recursivas. Por exemplo: exibir funcionários em um organograma ou dados em um cenário de lista de materiais em que um produto pai tenha um ou mais componentes, podendo esses componentes, por outro lado, ter subcomponentes ou componentes de outros pais.
Uma CTE recursiva pode simplificar muitíssimo um código necessário à execução de uma consulta recursiva dentro de instruções SELECT, INSERT, UPDATE, DELETE ou CREATE VIEW. Em versões anteriores do SQL Server, uma consulta recursiva, em geral, requer o uso de tabelas temporárias, cursores e lógica para controlar o fluxo das etapas recursivas. Para obter mais informações sobre expressões de tabelas comuns, consulte Usando expressões de tabela comuns.
Estrutura de uma CTE recursiva
A estrutura da CTE recursiva em Transact-SQL é semelhante a rotinas recursivas em outras linguagens de programação. Embora uma rotina recursiva em outras linguagens retorne um valor escalar, uma CTE recursiva pode retornar várias linhas.
Uma CTE recursiva consiste em três elementos:
Invocação da rotina.
A primeira invocação da CTE recursiva consiste em um ou mais CTE_query_definitions associadas pelos operadores UNION ALL, UNION, EXCEPT ou INTERSECT. Como essas definições de consultas constituem o conjunto de resultados base da estrutura da CTE, são citados como membros de ancoragem.
CTE_query_definitions são considerados membros de ancoragem, exceto se fizerem referência à própria CTE. Todas as definições de consulta de membro de ancoragem devem ser posicionadas antes que a primeira definição de membro recursivo e o operador UNION ALL precisem ser usados para associar o último membro de ancoragem ao primeiro membro recursivo.
Invocação recursiva da rotina.
A invocação recursiva inclui uma ou mais CTE_query_definitions associadas por operadores UNION ALL que fazem referência à própria CTE. Essas definições de consulta estão denominadas membros recursivos.
Verificação de término.
A verificação de término é implícita; a recursão para quando nenhuma linha é retornada da invocação anterior.
.gif "Observação") Observação |
|---|
Uma CTE recursiva incorretamente composta pode causar um loop infinito. Por exemplo, se a definição de consulta do membro recursivo retornar os mesmos valores para as colunas pai e filho, um loop infinito será criado. Ao testar os resultados de uma consulta recursiva, é possível limitar o número de níveis de recursão permitidos para uma instrução específica, usando a dica MAXRECURSION e um valor entre 0 e 32.767 na cláusula OPTION da instrução INSERT, UPDATE, DELETE ou SELECT. Para obter mais informações, consulte dicas de consulta (Transact-SQL) e WITH common_table_expression (Transact-SQL). |
Pseudocódigo e semântica
A estrutura de uma CTE recursiva deve conter no mínimo um membro de ancoragem e um membro recursivo. O pseudocódigo a seguir mostra os componentes de uma CTE recursiva simples que contém um único membro de ancoragem e um único membro recursivo.
WITH cte_name ( column_name [,...n] )
AS
(
CTE_query_definition –- Anchor member is defined.
UNION ALL
CTE_query_definition –- Recursive member is defined referencing cte_name.
)
-- Statement using the CTE
SELECT *
FROM cte_name
A semântica da execução recursiva é descrita a seguir:
Divida a expressão da CTE em membros de ancoragem e membros recursivos.
Execute os membros de ancoragem que criam a primeira invocação ou conjunto de resultados base (T0).
Execute os membros recursivos com Ti como uma entrada e Ti+1 como uma saída.
Repita a Etapa 3 até que um conjunto vazio seja retornado.
Retorne o conjunto de resultados. Trata-se de um UNION ALL de T0 a Tn.
Exemplo
O exemplo a seguir mostra a semântica da estrutura da CTE recursiva retornando uma lista hierárquica de funcionários, começando com o funcionário de mais alto nível na empresa Ciclos da Adventure Works. Um acompanhamento passo a passo da execução do código segue-se ao exemplo.
-- Create an Employee table.
CREATE TABLE dbo.MyEmployees
(
EmployeeID smallint NOT NULL,
FirstName nvarchar(30) NOT NULL,
LastName nvarchar(40) NOT NULL,
Title nvarchar(50) NOT NULL,
DeptID smallint NOT NULL,
ManagerID int NULL,
CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC)
);
-- Populate the table with values.
INSERT INTO dbo.MyEmployees VALUES
(1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)
,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)
,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)
,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)
,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)
,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)
,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)
,(16, N'David',N'Bradley', N'Marketing Manager', 4, 273)
,(23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);
USE AdventureWorks2008R2;
GO
WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
AS
(
-- Anchor member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
0 AS Level
FROM dbo.MyEmployees AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL
WHERE ManagerID IS NULL
UNION ALL
-- Recursive member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
Level + 1
FROM dbo.MyEmployees AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, DeptID, Level
FROM DirectReports
INNER JOIN HumanResources.Department AS dp
ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Sales and Marketing' OR Level = 0;
GO
Passo a passo do código de exemplo
A CTE recursiva DirectReports define um membro de ancoragem e um membro recursivo.
O membro de ancoragem retorna o conjunto de resultados base T0. Trata-se do mais alto funcionário da empresa, ou seja, um funcionário que não se reporta a um gerente.
Este é o conjunto de resultados retornado pelo membro de ancoragem:
ManagerID EmployeeID Title Level --------- ---------- ----------------------------- ------ NULL 1 Chief Executive Officer 0O membro recursivo retorna os subordinados diretos do funcionário do conjunto de resultados de membro de ancoragem. Isto é alcançado por uma operação de junção entre a Employee tabela e a CTE DirectReports. Essa referência ao próprio CTE é que estabelece a invocação recursiva. Com base no funcionário da CTE DirectReports como entrada (Ti), a junção (MyEmployees.ManagerID = DirectReports.EmployeeID) retorna como saída (Ti+1), os funcionários que têm (Ti) como gerente. Portanto, a primeira iteração do membro recursivo retorna esse conjunto de resultados:
ManagerID EmployeeID Title Level --------- ---------- ----------------------------- ------ 1 273 Vice President of Sales 1O membro recursivo é ativado repetidamente. A segunda iteração do membro recursivo usa o conjunto de resultados de linha única na etapa 3 (que contém EmployeeID273) como valor de entrada, e retorna o seguinte conjunto de resultados:
ManagerID EmployeeID Title Level --------- ---------- ----------------------------- ------ 273 16 Marketing Manager 2 273 274 North American Sales Manager 2 273 285 Pacific Sales Manager 2A terceira iteração do membro recursivo usa o conjunto de resultados acima como o valor de entrada, e retorna este conjunto de resultados:
ManagerID EmployeeID Title Level --------- ---------- ----------------------------- ------ 16 23 Marketing Specialist 3 274 275 Sales Representative 3 274 276 Sales Representative 3 285 286 Sales Representative 3O conjunto de resultados final retornado pela consulta em execução é a união de todos os conjuntos de resultados gerados pelos membros de ancoragem e membros recursivos.
Este é o conjunto de resultados completo retornado pelo exemplo:
ManagerID EmployeeID Title Level --------- ---------- ----------------------------- ------ NULL 1 Chief Executive Officer 0 1 273 Vice President of Sales 1 273 16 Marketing Manager 2 273 274 North American Sales Manager 2 273 285 Pacific Sales Manager 2 16 23 Marketing Specialist 3 274 275 Sales Representative 3 274 276 Sales Representative 3 285 286 Sales Representative 3