Créer des fonctions valorisées en table
Les fonctions retournant des tables vous permettent d’encapsuler la logique complexe de requête dans des composants réutilisables qui retournent des jeux de résultats. Vous pouvez appeler ces fonctions directement dans des requêtes, comme des tables ou des vues, ce qui rend votre code plus modulaire et plus facile à gérer.
Lorsque vous générez des applications de base de données, vous devez souvent récupérer des jeux de données filtrés ou calculés en fonction des paramètres d’entrée. Les fonctions tables résolvent ce problème en intégrant la logique de requête dans des fonctions qui acceptent des paramètres et renvoient des tables. Contrairement aux procédures stockées, vous pouvez utiliser des fonctions de type table dans les clauses JOIN et les instructions SELECT, ce qui vous donne la possibilité de traiter les résultats de fonction comme des sources de données.
Comprendre les types de fonctions à valeur de table
SQL Server fournit deux types de fonctions tables, chacune adaptée à différents scénarios.
Fonction table incluse
Contient une instruction unique SELECT et retourne directement les résultats. Avec les fonctions inline, vous ne définissez pas la structure de table , SQL Server l’déduit de votre SELECT instruction. L'optimiseur de requête traite les fonctions tables valorisées en ligne comme des vues avec paramètres, produisant souvent de meilleurs plans d'exécution.
Fonction table multi-instructions
Utilise un BEGIN...END bloc et déclare explicitement la structure de la table retournée. Ce type vous donne plus de contrôle lorsque vous devez exécuter plusieurs instructions, effectuer des calculs complexes ou générer le jeu de résultats de manière itérative. Toutefois, cette flexibilité est fournie avec un compromis des performances, car l’optimiseur traite ces fonctions différemment.
Le choix entre ces fonctions dépend de vos besoins spécifiques. Pour les requêtes simples avec des paramètres, les fonctions inline offrent de meilleures performances. Lorsque vous avez besoin d’une logique procédurale ou de plusieurs étapes pour générer votre jeu de résultats, les fonctions multi-instructions deviennent nécessaires.
Créer des fonctions table incluses
Les fonctions tables en ligne offrent un moyen concis de paramétrer les requêtes. Vous les définissez avec une seule instruction RETURN suivie d’une requête SELECT.
L’exemple suivant montre une fonction inline qui récupère les commandes d’un client spécifique :
CREATE FUNCTION dbo.GetCustomerOrders
(
@CustomerID INT
)
RETURNS TABLE
AS
RETURN
(
SELECT
OrderID,
OrderDate,
TotalAmount,
Status
FROM Sales.Orders
WHERE CustomerID = @CustomerID
);
Vous pouvez maintenant utiliser cette fonction dans les requêtes comme une table :
SELECT OrderID, OrderDate, TotalAmount
FROM dbo.GetCustomerOrders(1001)
WHERE OrderDate >= '2024-01-01';
La fonction accepte le paramètre ID client et retourne uniquement les commandes du client. Vous pouvez filtrer davantage, JOINou agréger les résultats en fonction des besoins. Cette approche permet de nettoyer votre requête principale tout encapsulant la logique de filtrage du client.
Créer des fonctions tables multi-instructions
Les fonctions tables multi-instructions offrent une plus grande flexibilité lorsque vous devez effectuer plusieurs opérations pour constituer votre jeu de résultats.
Considérez une fonction qui calcule les résumés des ventes de produits avec plusieurs agrégations :
CREATE FUNCTION dbo.GetProductSalesSummary
(
@StartDate DATE,
@EndDate DATE
)
RETURNS @SalesSummary TABLE
(
ProductID INT,
ProductName NVARCHAR(100),
TotalQuantity INT,
TotalRevenue DECIMAL(18,2),
AveragePrice DECIMAL(18,2)
)
AS
BEGIN
INSERT INTO @SalesSummary
SELECT
p.ProductID,
p.ProductName,
SUM(od.Quantity) AS TotalQuantity,
SUM(od.Quantity * od.UnitPrice) AS TotalRevenue,
AVG(od.UnitPrice) AS AveragePrice
FROM Production.Products p
INNER JOIN Sales.OrderDetails od ON p.ProductID = od.ProductID
INNER JOIN Sales.Orders o ON od.OrderID = o.OrderID
WHERE o.OrderDate BETWEEN @StartDate AND @EndDate
GROUP BY p.ProductID, p.ProductName;
RETURN;
END;
Notez comment vous déclarez explicitement la variable @SalesSummary de table avec des colonnes et des types de données spécifiques. Le corps de la fonction insère des données dans cette variable de table, puis le retourne. Cette structure vous permet d’ajouter une logique de traitement supplémentaire, une gestion des erreurs ou des instructions conditionnelles si nécessaire.
Utiliser des fonctions de type table dans les requêtes
Les fonctions de valeur de table s’intègrent en toute transparence dans vos requêtes, permettant des modèles puissants de récupération de données.
Vous pouvez joindre des résultats de fonction avec d’autres tables :
SELECT
c.CustomerName,
s.ProductName,
s.TotalRevenue
FROM Customers c
CROSS APPLY dbo.GetProductSalesSummary('2024-01-01', '2024-12-31') s
WHERE s.TotalRevenue > 10000
ORDER BY s.TotalRevenue DESC;
L’opérateur CROSS APPLY appelle la fonction pour chaque ligne de la table Customers, bien que dans cet exemple, les paramètres de fonction soient des constantes. Lorsque vous transmettez des valeurs de colonne en tant que paramètres, CROSS APPLY devient particulièrement utile :
SELECT
c.CustomerName,
o.OrderID,
o.TotalAmount
FROM Customers c
CROSS APPLY dbo.GetCustomerOrders(c.CustomerID) o
WHERE o.Status = 'Completed';
Cette requête récupère toutes les commandes terminées pour chaque client, ce qui montre comment les fonctions table activent le traitement ligne par ligne au sein de vos requêtes. La fonction agit comme une sous-requête corrélée, mais avec une meilleure lisibilité et une meilleure réutilisation.
Pour les fonctions à valeur de table inline qui ne nécessitent pas d’évaluation de ligne par ligne, vous pouvez également utiliser la syntaxe INNER JOIN.
SELECT
c.CustomerName,
o.OrderDate,
o.TotalAmount
FROM Customers c
INNER JOIN dbo.GetCustomerOrders(c.CustomerID) o ON 1=1
WHERE YEAR(o.OrderDate) = 2024;
Avec ces techniques, vous pouvez créer des requêtes complexes à partir de composants de fonction plus simples et testés, ce qui améliore la facilité de maintenance du code et l’efficacité du développement.