Utilisation de APPLY
Mis à jour : 14 avril 2006
L'opérateur APPLY vous permet d'appeler une fonction table pour chaque ligne retournée par l'expression de table externe d'une requête. La fonction table agit en tant qu'entrée droite et l'expression de table externe en tant qu'entrée gauche. L'entrée droite est évaluée pour chaque ligne de l'entrée gauche, les lignes produites étant combinées dans l'entrée finale. La liste des colonnes produite par l'opérateur APPLY correspond au jeu de colonnes de l'entrée gauche suivi de la liste des colonnes retournées par l'entrée droite.
.gif "ms175156.note(fr-fr,SQL.90).gif") Remarque : |
|---|
| Pour utiliser APPLY, le niveau de compatibilité de la base de données doit être 90. |
Il existe deux formes d'opérateur APPLY : CROSS APPLY et OUTER APPLY. L'opérateur CROSS APPLY retourne uniquement les lignes de la table externe produisant un ensemble de résultats à partir de la fonction table. L'opérateur OUTER APPLY retourne les lignes produisant un ensemble de résultats ainsi que les lignes ne produisant pas un tel ensemble, des valeurs NULL étant indiquées dans les colonnes produites à partir de la fonction table.
Par exemple, prenons les deux tables suivantes : Employees et Departments.
--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)
La plupart des services de la table Departments possèdent un ID de responsable correspondant à un employé de la table Employees. La fonction table suivante accepte l'ID d'employé en tant qu'argument et retourne cet employé ainsi que tous ses subordonnés.
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
Pour retourner tous les subordonnés du responsable de chaque service, à quelque niveau que ce soit, utilisez la requête suivante.
SELECT *
FROM Departments AS D
CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST
Voici l'ensemble des résultats.
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
Comme vous pouvez le constater, chaque ligne de la table Departments est dupliquée autant de fois qu'il y a de lignes retournées à l'aide de fn_getsubtree pour chaque responsable de service.
Par ailleurs, le service Gardening ne figure pas dans les résultats. Étant donné que ce service est dépourvu de responsable, fn_getsubtree a retourné un ensemble vide. En revanche, si vous utilisez l'opérateur OUTER APPLY, le service Gardening apparaît dans l'ensemble de résultats avec des valeurs nulles dans le champ deptmgrid ainsi que dans les champs retournés par fn_getsubtree.