Query ricorsive tramite espressioni di tabella comuni
Un'espressione di tabella comune (CTE, Common Table Expression) offre il significativo vantaggio di poter fare riferimento a se stessa, creando pertanto un'espressione di tabella comune ricorsiva. Una CTE ricorsiva è un'espressione in cui una CTE iniziale viene eseguita ripetutamente per restituire subset di dati fino a quando viene raggiunto il risultato completo.
In SQL Server 2005 una query viene detta ricorsiva quando fa riferimento a una CTE ricorsiva. Le query ricorsive restituiscono solitamente dati gerarchici, ad esempio nel caso di una visualizzazione dei dipendenti in un organigramma o dei dati in una distinta base in uno scenario in cui un prodotto padre include uno o più componenti e tali componenti possono, a loro volta, includere sottocomponenti o essere componenti di altri componenti padre.
Una CTE ricorsiva consente di semplificare notevolmente il codice necessario per eseguire una query ricorsiva in un'istruzione SELECT, INSERT, UPDATE, DELETE o CREATE VIEW. Nelle versioni precedenti di SQL Server una query ricorsiva richiede solitamente l'utilizzo di tabelle, cursori e logica temporanei per controllare il flusso dei passaggi ricorsivi. Per ulteriori informazioni sulle espressioni di tabella comuni, vedere Utilizzo delle espressioni di tabella comuni.
Struttura di una CTE ricorsiva
La struttura di una CTE ricorsiva in Transact-SQL è simile alle routine ricorsive in altri linguaggi di programmazione. Sebbene una routine ricorsiva negli altri linguaggi restituisca un valore scalare, una CTE ricorsiva può restituire più righe.
Una CTE ricorsiva è costituita da tre elementi:
- Chiamata della routine.
La prima chiamata della CTE ricorsiva è costituita da uno o più elementi CTE_query_definitions uniti in join dagli operatori UNION ALL, UNION, EXCEPT o INTERSECT. Poiché queste definizioni di query formano il set di risultati di base della struttura della CTE, vengono dette membri non ricorsivi.
Gli elementi CTE_query_definitions vengono considerati membri non ricorsivi a meno che non facciano riferimento alla CTE stessa. Tutte le definizioni di query dei membri non ricorsivi devono essere posizionate prima della prima definizione del membro ricorsivo ed è necessario utilizzare un operatore UNION ALL per unire in join l'ultimo membro non ricorsivo con il primo membro ricorsivo. - Chiamata ricorsiva della routine.
La chiamata ricorsiva include uno o più elementi CTE_query_definitions uniti in join tramite operatori UNION ALL che fanno riferimento alla CTE stessa. Queste definizioni di query sono dette membri ricorsivi. - Controllo della chiusura.
Il controllo della chiusura è implicito. La ricorsione viene interrotta quando dalla chiamata precedente non vengono restituite righe.
[!NOTA] Una CTE ricorsiva composta in modo non corretto può provocare un ciclo infinito. Se, ad esempio, la definizione di query del membro ricorsivo restituisce gli stessi valori sia per le colonne padre che per le colonne figlio, viene creato un ciclo infinito. Quando si esegue il test dei risultati di una query ricorsiva, è possibile limitare il numero di livelli di ricorsione per un'istruzione specifica utilizzando l'hint MAXRECURSION e un valore compreso tra 0 e 32.767 nella clausola OPTION dell'istruzione INSERT, UPDATE, DELETE o SELECT. Per ulteriori informazioni, vedere query_hint (Transact-SQL) e WITH common_table_expression (Transact-SQL).
Pseudocodice e semantica
La struttura di una CTE ricorsiva deve contenere almeno un membro non ricorsivo e un membro ricorsivo. Nello pseudocodice seguente vengono illustrati i componenti di una CTE ricorsiva semplice contenente un unico membro non ricorsivo e un unico membro ricorsivo.
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
La semantica dell'esecuzione ricorsiva è la seguente:
- Dividere l'espressione CTE in membri non ricorsivi e ricorsivi.
- Eseguire il membro o i membri non ricorsivi creando la prima chiamata o set di risultati di base (T0).
- Eseguire il membro o i membri non ricorsivi con Ti come input e Ti+1 come output.
- Ripetere il passaggio 3 fino a quando viene restituito un set vuoto.
- Restituire il set di risultati. Si tratta di un'operazione UNION ALL di T0 a Tn.
Esempio
Nell'esempio seguente viene illustrata la semantica della struttura della CTE ricorsiva restituendo un elenco gerarchico di dipendenti, a partire da quello con rango maggiore, nella società Adventure Works Cycles. L'istruzione che esegue la CTE limita il set di risultati ai dipendenti del gruppo di ricerca e sviluppo (Research and Development). L'esempio è seguito da una descrizione dettagliata dell'esecuzione del codice.
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
Descrizione dettagliata del codice di esempio
La CTE ricorsiva,
DirectReports, definisce un membro non ricorsivo e un membro ricorsivo.Il membro non ricorsivo restituisce il set di risultati di base T0. Si tratta del dipendente della società con rango più alto, ovvero il dipendente che non fa riferimento ad alcun responsabile.
Di seguito è riportato il set di risultati restituito dal membro non ricorsivo:ManagerID EmployeeID Title Level --------- ---------- --------------------------------------- ------ NULL 109 Chief Executive Officer 0Il membro ricorsivo restituisce il subalterno o i subalterni diretti del dipendente nel set di risultati del membro non ricorsivo. Il risultato è ottenuto da un'operazione di join tra la tabella
Employeee la CTEDirectReports. Il riferimento alla CTE stessa stabilisce la chiamata ricorsiva. In base al dipendente nella CTEDirectReportsutilizzato come input (Ti), il join(Employee.ManagerID = DirectReports.EmployeeID) restituisce come output (Ti+1) i dipendenti il cui responsabile è (Ti). La prima iterazione del membro ricorsivo restituisce pertanto il set di risultati seguente:ManagerID EmployeeID Title Level --------- ---------- --------------------------------------- ------ 109 12 Vice President of Engineering 1Il membro ricorsivo viene attivato ripetutamente. La seconda iterazione del membro ricorsivo utilizza il set di risultati con una sola riga del passaggio 3 (contenente
EmployeeID``12) come valore di input e restituisce il set di risultati seguente:ManagerID EmployeeID Title Level --------- ---------- --------------------------------------- ------ 12 3 Engineering Manager 2La terza iterazione del membro ricorsivo utilizza il set di risultati con una sola riga riportato sopra (contenente
EmployeeID``3)) come valore di input e restituisce il set di risultati seguente: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 3La quarta iterazione del membro ricorsivo utilizza il set di righe precedente per i valori
EmployeeID``4,9,11,158,263,267e270come valore di input.
Il processo viene ripetuto fino a quando un membro ricorsivo restituisce un set di risultati vuoto.Il set di risultati finale restituito eseguendo la query è costituito dall'unione di tutti i set di risultati generati dai membri non ricorsivi e ricorsivi.
Di seguito è riportato il set di risultati completo restituito dall'esempio: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)
Vedere anche
Concetti
Utilizzo delle espressioni di tabella comuni
Altre risorse
WITH common_table_expression (Transact-SQL)
query_hint (Transact-SQL)
INSERT (Transact-SQL)
UPDATE (Transact-SQL)
DELETE (Transact-SQL)
EXCEPT e INTERSECT (Transact-SQL)