Définition :
Les fonctions utilisateur (UDF- User Define Function ou Function Définie par l’Utilisateur) sont des outils importants dans le langage transact sql.
Une fonction que le concepteur de la base écrit pour des besoins de traitement au sein des requêtes et du code des procédures stockées ou des triggers. Elle fait donc partie intégrante de la base où elle est considérée comme un objet au même titre qu’une table, une vue, un utilisateur ou une procédure stockée. Il existe deux grands types de fonctions : celles renvoyant une valeur et celles renvoyant un jeu de données (table).
1- tsql udf : Fonctions scalaire
Les fonctions scalaires sont les fonctions utilisateurs (udf) qui renvoient une valeur comme valeur de retour. On peut avoir besoin de ce type de fonctions utilisateurs dans la plus part des projets de gestion de bases de données relationnelles.
Syntaxe :
CREATE FUNCTION function_name ( @nomparametre type [=default] ,… ) RETURNS type_valeur_retour [ AS ] BEGIN function_body RETURN valeur END [ ; ]
|
Remarque :
Tous les types de données peuvent être retournés par une fonction scalaire hors mis timestamp, table, cursor, text, ntext et image
Exemple 1 :
CREATE FUNCTION f1 (@f int)
RETURNS int
AS
BEGIN
return (select count(*) from stagiaire where n_fil=@f)
END
Exemple 2 : fonction qui retourne la factorielle d’un nombre passé en paramètre
CREATE FUNCTION Factoriel (@n int) RETURNS bigint
AS
BEGIN
declare @f bigint, @i int
select @f=1, @i=1
while (@i<=@n)
begin
select @f=@f*@i, @i=@i+1
end
RETURN @f
END
Select dbo.Factoriel(6) as Factoriel;
ou
Declare @r bigint
Set @r=dbo.Factoriel(15)
Print cast(@r as varchar)
Remarque 2 : Le dbo est un utilisateur qui a les autorisations pour exercer toutes les activités dans la base de données. Tout objet créé par un membre du rôle serveur fixe sysadmin appartient à dbo automatiquement.
2- tsql udf : Inline Table-valued Functions
Ce type de fonctions d’utilisateur (udf en tsql) décrit les fonctions définit par l’utilisateur qui retournent comme résultat une table. Ctte table résultatnte est le résultat d’une instruction SELECT.
Syntaxe :
CREATE FUNCTION function_name ( @nomparametre type [=default] ,… ) RETURNS TABLE [ AS ] RETURN [ ( ] select_stmt [ ) ] [ ; ]
|
Exemple :
CREATE FUNCTION recmd_stock (@Id int, @seuil int) RETURNS TABLE
AS
RETURN (SELECT * FROM Stock WHERE Id_Stock = @Id AND Quantite < @Seuil)
3- tsql udf : Fonctions table multi-instructions
Ce type dit en francais : Fonctions table multi-instructions, décrit les fonctions utilisateur (udf) qui retournent (renvoient en valeur de retour) une table on multi-instruction. Autrement dit, on définit une variable de type table pour stocker le résultat et le renvoyer après un traitement précis dans le corps de la fonction utilisateur
Syntaxe :
CREATE FUNCTION function_name ( @nomparametre type [=default] ,… ) RETURNS @return_variable TABLE < table_type_definition > [ AS ] BEGIN function_body RETURN END [ ; ]
|
Exemple 1 :
CREATE FUNCTION table_multi (@Id int)
RETURNS @variable TABLE (Id_Stock int, Quantite int, Nom_Entrepos varchar(25))
AS
BEGIN
SELECT @variable = (SELECT Id_Stock, Quantite, Nom_Entrepos
FROM Entrepos E INNER JOIN Stock S
ON E.Id_Entrepos = S.Id_Entrepos
WHERE S.Id_Stock = @Id)
RETURN
END
Exemple 2 : une fonction table à instructions multiples
Create FUNCTION NoteStg2 (@idstg int)
RETURNS @Resultat Table (Matiere varchar(50), Note decimal(4,2))
AS begin
insert into @Resultat Select ‘Nom : ‘ + nom,Null from stagiaire where idstg=@idstg
insert into @Resultat Select libelle, avg(note)
From Matiere inner join note on Matiere.idmat=note.idmat
Where idstg=@idstg Group by Matiere.idmat, libelle
insert into @Resultat select ‘Moyenne :’, Moyenne from stagiaire where idstg=@idstg
RETURN
end
Select * from dbo.NoteStg2(1);
Remarque : Les fonctions table et table multi-instructions sont différentes dans le sens où le format de retour est différent. En effet, la fonction table retourne la solution d’une requête SELECT, alors que la fonction table multi-instruction, retournera une variable de type table, contenant l’instruction SELECT opérée par la fonction.
Remarque :
– Modifier une fonction : Il suffit de remplacer le mot clé CREATE FUNCTION par le mot clé ALTER FUNCTION.
– Suppression d’une fonction : On utilise la commande drop comme suit : Drop Function nom_fonction
Vidéo Résumée :
Et voilà, vous etes pret pour faire le TP :
TP4-Tsql : Fonctions Utilisateur
Vous pouvez suivre la liste des vidéos du cours base de données (Merise + Sql + Transact sql) sur notre chaine youtube : Vidéos Bases de données.