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 auto-referenciar, 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 unir 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 pára 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 Adventure Works Cycles. A instrução que executa a CTE limita o conjunto de resultados aos funcionários no Grupo de Pesquisa e Desenvolvimento. Um acompanhamento passo a passo da execução do código segue-se ao exemplo.
USE AdventureWorks;
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 HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID 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 HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, Level
FROM DirectReports
INNER JOIN HumanResources.Department AS dp
ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Research and Development' 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 109 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 (Employee.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 --------- ---------- --------------------------------------- ------ 109 12 Vice President of Engineering 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 EmployeeID12) como valor de entrada, e retorna o seguinte conjunto de resultados:
ManagerID EmployeeID Title Level --------- ---------- --------------------------------------- ------ 12 3 Engineering Manager 2A terceira iteração do membro recursivo usa o conjunto de resultados de linha única acima (que contém EmployeeID3) como valor de entrada, e retorna este conjunto de resultados:
ManagerID EmployeeID Title Level --------- ---------- --------------------------------------- ------ 3 4 Senior Tool Designer 3 3 9 Design Engineer 3 3 11 Design Engineer 3 3 158 Research and Development Manager 3 3 263 Senior Tool Designer 3 3 267 Senior Design Engineer 3 3 270 Design Engineer 3A quarta iteração de membro recursivo usa o conjunto de linhas anterior para os valores EmployeeID4, 9, 11, 158, 263, 267 e 270 como valores de entrada.
Esse processo se repete até que o membro recursivo retorne um conjunto de resultados vazio.
O 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 109 Chief Executive Officer 0 109 12 Vice President of Engineering 1 12 3 Engineering Manager 2 3 4 Senior Tool Designer 3 3 9 Design Engineer 3 3 11 Design Engineer 3 3 158 Research and Development Manager 3 3 263 Senior Tool Designer 3 3 267 Senior Design Engineer 3 3 270 Design Engineer 3 263 5 Tool Designer 4 263 265 Tool Designer 4 158 79 Research and Development Engineer 4 158 114 Research and Development Engineer 4 158 217 Research and Development Manager 4 (15 row(s) affected)