Utilizzo di APPLY
Data aggiornamento: 14 aprile 2006
L'operatore APPLY consente di richiamare una funzione valutata a livello di tabella per ogni riga restituita da un'espressione di tabella esterna di una query. La funzione valutata a livello di tabella opera come input destro, mentre l'espressione di tabella esterna opera come input sinistro. L'input destro viene valutato per ogni riga dell'input sinistro e le righe prodotte vengono combinate per l'output finale. L'elenco di colonne prodotto dall'operatore APPLY corrisponde al set di colonne nell'input sinistro seguito dall'elenco di colonne restituito dall'input destro.
[!NOTA] Per l'utilizzo di APPLY il livello di compatibilità del database deve essere 90.
L'operatore APPLY è disponibile in due forme: CROSS APPLY e OUTER APPLY. L'operatore CROSS APPLY restituisce solo le righe della tabella esterna che producono un set di risultati dalla funzione valutata a livello di tabella. L'operatore OUTER APPLY restituisce le righe che producono un set di risultati e quelle che non producono alcun set di risultati, con valori NULL nelle colonne prodotte dalla funzione valutata a livello di tabella.
Si considerino, come esempio, le tabelle Employees e Departments seguenti:
--Create Employees table and insert values.
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees PRIMARY KEY(empid),
)
GO
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO
--Create Departments table and insert values.
CREATE TABLE Departments
(
deptid INT NOT NULL PRIMARY KEY,
deptname VARCHAR(25) NOT NULL,
deptmgrid INT NULL REFERENCES Employees
)
GO
INSERT INTO Departments VALUES(1, 'HR', 2)
INSERT INTO Departments VALUES(2, 'Marketing', 7)
INSERT INTO Departments VALUES(3, 'Finance', 8)
INSERT INTO Departments VALUES(4, 'R&D', 9)
INSERT INTO Departments VALUES(5, 'Training', 4)
INSERT INTO Departments VALUES(6, 'Gardening', NULL)
Per la maggior parte dei reparti inclusi nella tabella Departments è disponibile un ID responsabile che corrisponde a un dipendente incluso nella tabella Employees. La funzione valutata a livello di tabella seguente accetta un ID dipendente come argomento e restituisce il dipendente e tutti i relativi subalterni.
CREATE FUNCTION dbo.fn_getsubtree(@empid AS INT) RETURNS @TREE TABLE
(
empid INT NOT NULL,
empname VARCHAR(25) NOT NULL,
mgrid INT NULL,
lvl INT NOT NULL
)
AS
BEGIN
WITH Employees_Subtree(empid, empname, mgrid, lvl)
AS
(
-- Anchor Member (AM)
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = @empid
UNION all
-- Recursive Member (RM)
SELECT e.empid, e.empname, e.mgrid, es.lvl+1
FROM Employees AS e
JOIN Employees_Subtree AS es
ON e.mgrid = es.empid
)
INSERT INTO @TREE
SELECT * FROM Employees_Subtree
RETURN
END
GO
Per restituire tutti i subalterni in tutti i livelli per il responsabile di ogni reparto, utilizzare la query seguente.
SELECT *
FROM Departments AS D
CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST
Set di risultati:
deptid deptname deptmgrid empid empname mgrid lvl
----------- ---------- ----------- ----------- ---------- ----------- ---
1 HR 2 2 Andrew 1 0
1 HR 2 5 Steven 2 1
1 HR 2 6 Michael 2 1
2 Marketing 7 7 Robert 3 0
2 Marketing 7 11 David 7 1
2 Marketing 7 12 Ron 7 1
2 Marketing 7 13 Dan 7 1
2 Marketing 7 14 James 11 2
3 Finance 8 8 Laura 3 0
4 R&D 9 9 Ann 3 0
5 Training 4 4 Margaret 1 0
5 Training 4 10 Ina 4 1
Si noti che ogni riga della tabella Departments è duplicata tante volte quante sono le righe restituite da fn_getsubtree per il responsabile del reparto.
Il reparto Gardening, inoltre, non è incluso nei risultati. Poiché per questo reparto non esiste un responsabile, fn_getsubtree ha restituito un set vuoto. Se si utilizza l'operatore OUTER APPLY, nel set di risultati verrà visualizzato anche il reparto Gardening, con valori NULL nel campo deptmgrid, nonché nei campi restituiti da fn_getsubtree.