Les fonctions dans SQL Server sont les objets de base de données qui contiennent un ensemble d'instructions SQL pour effectuer une tâche spécifique . Une fonction accepte les paramètres d’entrée, effectue des actions, puis renvoie le résultat. Il convient de noter que les fonctions renvoient toujours soit une valeur unique, soit un tableau. L’objectif principal des fonctions est de reproduire facilement la tâche courante. Nous pouvons créer des fonctions une seule fois et les utiliser à plusieurs endroits en fonction de nos besoins. SQL Server ne permet pas d'utiliser les fonctions d'insertion, de suppression ou de mise à jour d'enregistrements dans les tables de la base de données.
théorie des arbres et des graphes
Voici les règles de création de fonctions SQL Server :
- Une fonction doit avoir un nom et celui-ci ne peut pas commencer par un caractère spécial tel que @, $, # ou d'autres caractères similaires.
- Les instructions SELECT sont les seules qui fonctionnent avec des fonctions.
- Nous pouvons utiliser une fonction n'importe où telle que AVG, COUNT, SUM, MIN, DATE et d'autres fonctions avec la requête SELECT en SQL.
- Chaque fois qu'une fonction est appelée, elle se compile.
- Les fonctions doivent renvoyer une valeur ou un résultat.
- Les fonctions utilisent uniquement des paramètres d'entrée.
- Nous ne pouvons pas utiliser les instructions TRY et CATCH dans les fonctions.
Types de fonctions
SQL Server classe les fonctions en deux types :
- Fonctions du système
- Fonctions définies par l'utilisateur
Décrivons les deux types en détail.
Fonctions du système
Les fonctions définies par le système sont appelées fonctions système. Autrement dit, tous les fonctions intégrées prises en charge par le serveur sont appelées fonctions système. Les fonctions intégrées nous font gagner du temps lors de l'exécution d'une tâche spécifique. Ces types de fonctions fonctionnent généralement avec l'instruction SQL SELECT pour calculer des valeurs et manipuler des données.
Voici la liste de quelques fonctions système utilisées dans SQL Server :
- Fonctions de chaîne (LEN, SUBSTRING, REPLACE, CONCAT, TRIM)
- Fonctions de date et d'heure (datetime, datetime2, smalldatetime)
- Fonctions d'agrégation (COUNT, MAX, MIN, SUM, AVG)
- Fonctions mathématiques (ABS, POWER, PI, EXP, LOG)
- Fonctions de classement (RANK, DENSE_RANK, ROW_NUMBER, NTILE)
L'image suivante montre toutes les fonctions de base de données intégrées utilisées dans SQL Server :
Fonctions définies par l'utilisateur
Des fonctions qui sont créé par l'utilisateur dans la base de données système ou dans une base de données définie par l'utilisateur sont appelées fonctions définies par l'utilisateur. Les fonctions UDF acceptent les paramètres, effectuent des actions et renvoient le résultat. Ces fonctions nous aident à simplifier notre développement en encapsulant une logique métier complexe et en la rendant disponible pour une réutilisation n'importe où en fonction des besoins. Les fonctions définies par l'utilisateur facilitent grandement l'écriture du code nécessaire à l'interrogation des données. Ils améliorent également la lisibilité et la fonctionnalité des requêtes, et permettent à d'autres utilisateurs de reproduire les mêmes procédures.
SQL Server classe les fonctions définies par l'utilisateur principalement en deux types :
- Fonctions scalaires
- Fonctions table
Voici les descriptions de ces fonctions UDF.
Fonctions scalaires
Fonction scalaire dans SQL Server toujours accepte les paramètres, simples ou multiples et renvoie une valeur unique . Les fonctions scalaires sont utiles dans la simplification de notre code. Supposons que nous puissions avoir un calcul complexe qui apparaît dans un certain nombre de requêtes. Dans un tel cas, nous pouvons créer une fonction scalaire qui encapsule la formule et l'utilise dans chaque requête plutôt que dans chaque requête.
Voici la syntaxe illustrant la création d'une fonction scalaire dans SQL Server :
CREATE FUNCTION schema_name.function_name (parameter_list) RETURNS data_type AS BEGIN statements RETURN value END
Les paramètres de syntaxe ci-dessus sont décrits ci-dessous :
Nous définirons d'abord le nom de la fonction après le CRÉER UNE FONCTION mots clés. Le nom du schéma est facultatif. Si nous ne définissons pas le nom du schéma, SQL Server utilise le schéma par défaut dbo . Ensuite, nous définirons la liste des paramètres entre parenthèses. Troisièmement, nous écrirons les instructions de la fonction puis, dans le RETOUR instruction, définissez le type de données de la valeur de retour. Enfin, nous avons ajouté l'instruction RETURN pour renvoyer une valeur dans le corps de la fonction.
Exemple
Cet exemple créera une fonction pour calculer les ventes nettes en fonction de la quantité, du prix et de la valeur de la remise :
CREATE FUNCTION udfNet_Sales( @quantity INT, @price DEC(10,2), @discount DEC(3,2) ) RETURNS DEC(10,2) AS BEGIN RETURN @quantity * @price * (1 - @discount); END;
Désormais, nous pouvons utiliser cette fonction pour calculer les ventes nettes de toute commande client dans le tableau défini.
L'image suivante s'affiche où l'on peut trouver les fonctions scalaires :
Nous pouvons appeler les fonctions scalaires de la même manière que la fonction intégrée dans SQL Server. Par exemple, nous pouvons appeler la fonction udfNet_Sales ci-dessus comme ci-dessous :
SELECT dbo.udfNet_Sales(25, 500, 0.2) AS net_sales;
L'exécution de cette fonction renverra les ventes nettes :
SQL Server nous permet également pour modifier la fonction scalaire en utilisant ALTER mot-clé. Voici la syntaxe pour faire cela :
ALTER FUNCTION schema_name.function_name (parameter_list) RETURNS data_type AS BEGIN statements RETURN value END
Nous pouvons utiliser l'instruction ci-dessous pour supprimer la fonction scalaire de la base de données SQL Server :
DROP FUNCTION [schema_name.]function_name;
Fonctions table
Les fonctions table dans SQL Server sont les fonction définie par l'utilisateur qui renvoie des données d'un type de table. Puisque cette fonction est le type de retour est une table , nous pouvons l'utiliser de la même manière que nous utilisons une table.
Nous pouvons classer la fonction table en deux types :
1. Fonctions de valeurs de table en ligne
Cette fonction UDF renvoie une variable de table basée sur l'action effectuée par la fonction. Une seule instruction SELECT doit être utilisée pour déterminer la valeur de la variable de table.
Exemple
L'exemple ci-dessous créera une fonction de valeurs de table et récupérera les données de la table des employés :
--It creates a table-valued function to get employees CREATE FUNCTION fudf_GetEmployee() RETURNS TABLE AS RETURN (SELECT * FROM Employee)
Dans cette syntaxe, le TABLEAU DES RETOURS spécifie que la fonction renverra une table. Puisqu'il n'y a pas DÉBUT...FIN instruction, il interroge simplement les données de la table des employés. De plus, s’il ne possède aucun paramètre, il sera appelé directement.
Nous pouvons appeler les fonctions table en utilisant le DEPUIS clause du SÉLECTIONNER requête. Par exemple , nous pouvons appeler ce qui précède udf_GetEmployee fonctionner comme ci-dessous :
convertir un caractère en chaîne
SELECT * FROM udf_GetEmployee();
L'exécution de cette fonction renverra le résultat ci-dessous :
SQL Server nous permet également de modifier les fonctions table en utilisant le mot-clé ALTER au lieu du mot-clé CREATE. Le reste du scénario est le même.
Fonctions table multi-instructions (MSTVF)
Cette fonction UDF renvoie une variable de table en fonction de l'action effectuée par la fonction. Il peut contenir une ou plusieurs instructions pour produire le résultat, et c'est également une fonction qui renvoie le résultat de plusieurs instructions sous forme de tableau. C'est utile car nous pouvons exécuter plusieurs instructions dans cette fonction et obtenir des résultats agrégés dans la table renvoyée. Nous pouvons définir cette fonction en utilisant une variable de table comme valeur de retour. À l'intérieur de la fonction, nous exécutons plusieurs requêtes et insérons des données dans cette variable de table.
L'exemple suivant crée un nom de fonction « MULTIVALUÉ » qui renvoie le ' @Employé' tableau. Il contient trois champs nommés id, emp_name et salaire du 'Employé' table à l’aide de l’instruction INSERT, puis utilise l’instruction UPDATE pour mettre à jour le nom de l’employé.
CREATE FUNCTION MULTIVALUED() RETURNS @Employee TABLE (id INT, emp_name VARCHAR(50), salary INT) AS BEGIN INSERT INTO @Employee SELECT E.id, E.emp_name, E.salary FROM Employee E; UPDATE @Employee SET emp_name = 'Graeme Smith' WHERE id = 3; RETURN END
Nous pouvons appeler les fonctions table à instructions multiples en utilisant la clause FROM de la requête SELECT. Par exemple , nous pouvons appeler la fonction ci-dessus comme ci-dessous :
SELECT * FROM MULTIVALUED();
L'exécution de cette fonction renverra le résultat ci-dessous :
Quand faut-il utiliser les fonctions table ?
Les fonctions table sont couramment utilisées comme vues paramétrées. Les fonctions table sont plus flexibles que les procédures stockées car elles peuvent être utilisées partout où des tables sont utilisées.
Conclusion
Cet article explique un aperçu complet des fonctions utilisées dans SQL Server. Ici, nous avons appris principalement deux types de fonctions utilisées dans SQL Server : les fonctions système et définies par l'utilisateur.