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.
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 arrestata 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 Hint per la query (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'esempio è seguito da una descrizione dettagliata dell'esecuzione del codice.
-- 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
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 1 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 Employee e la CTE DirectReports. Il riferimento alla CTE stessa stabilisce la chiamata ricorsiva. In base al dipendente nella CTE DirectReports utilizzato come input (Ti), il join (MyEmployees.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 --------- ---------- ----------------------------- ------ 1 273 Vice President of Sales 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 EmployeeID273) come valore di input e restituisce il set di risultati seguente:
ManagerID EmployeeID Title Level --------- ---------- ----------------------------- ------ 273 16 Marketing Manager 2 273 274 North American Sales Manager 2 273 285 Pacific Sales Manager 2La terza iterazione del membro ricorsivo utilizza il set di risultati riportato sopra come valore di input e restituisce il set di risultati seguente:
ManagerID EmployeeID Title Level --------- ---------- ----------------------------- ------ 16 23 Marketing Specialist 3 274 275 Sales Representative 3 274 276 Sales Representative 3 285 286 Sales Representative 3Il 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 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