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:

  1. 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.

  2. 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.

  3. 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 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:

  1. Dividere l'espressione CTE in membri non ricorsivi e ricorsivi.

  2. Eseguire il membro o i membri non ricorsivi creando la prima chiamata o set di risultati di base (T0).

  3. Eseguire il membro o i membri non ricorsivi con Ti come input e Ti+1 come output.

  4. Ripetere il passaggio 3 fino a quando viene restituito un set vuoto.

  5. 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

  1. La CTE ricorsiva, DirectReports, definisce un membro non ricorsivo e un membro ricorsivo.

  2. 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                 0
    
  3. Il 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 (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           1
    
  4. Il membro ricorsivo viene attivato ripetutamente. La seconda iterazione del membro ricorsivo utilizza il set di risultati con una sola riga del passaggio 3 (contenente EmployeeID12) come valore di input e restituisce il set di risultati seguente:

    ManagerID EmployeeID Title                                   Level
    --------- ---------- --------------------------------------- ------
    12        3          Engineering Manager                     2
    

    La terza iterazione del membro ricorsivo utilizza il set di risultati con una sola riga riportato sopra (contenente EmployeeID3)) 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                         3
    

    La quarta iterazione del membro ricorsivo utilizza il set di righe precedente per i valori EmployeeID4, 9, 11, 158, 263, 267 e 270 come valore di input.

    Il processo viene ripetuto fino a quando un membro ricorsivo restituisce un set di risultati vuoto.

  5. 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)