Pr.
Zakaria Boulouard
Fonctions
Stockées
Objectifs
• Dans ce chapitre, on va :
• Distinguer entre fonctions et procédures stockées
• Apprendre à manipuler des fonctions sous MySQL Workbench
2
Introduction
• Dans un système d’information, plusieurs applications peuvent avoir
besoin d’effectuer les mêmes calculs ou les mêmes opérations sur les
données.
• Lorsque ces traitements sont codés séparément dans chaque
application, cela peut conduire à des duplications de code et à des
divergences dans la manière dont les règles métier sont appliquées.
3
Introduction
• Les « fonctions stockées » répondent à ce besoin en permettant
d’exécuter certains calculs directement au sein du SGBD.
• Une fonction stockée est un bloc SQL enregistré dans la base de
données et destiné à retourner une valeur unique, généralement
utilisée dans une requête.
4
Introduction
• Elle encapsule un calcul ou une règle métier afin qu’elle soit définie
une seule fois et réutilisée partout.
• L’exécution d’une fonction stockée au niveau du serveur présente
également un intérêt en termes de performances.
5
Introduction
• Le traitement est effectué au plus près des données, ce qui évite de
transférer inutilement des informations vers l’application cliente et
réduit les échanges entre celle-ci et la base.
• Enfin, les fonctions stockées contribuent à l’homogénéité et à la
cohérence du système d’information.
6
Introduction
• En centralisant certains calculs dans la base de données, elles
garantissent que la même règle est appliquée de manière uniforme par
toutes les applications qui l’utilisent.
7
Définition
• Une « fonction stockée » est un bloc d’instructions SQL enregistré dans
la base de données et destiné à produire une valeur unique en sortie.
• Elle est définie dans le SGBD, compilée et conservée sous forme
d’objet de la base, au même titre qu’une table, une vue ou une
procédure.
8
Définition
• Contrairement à une procédure stockée, la fonction a pour objectif
principal d’effectuer un calcul, une transformation ou une vérification,
puis de retourner un résultat au moyen de l’instruction « RETURN ».
• Cette valeur peut ensuite être utilisée directement dans une requête
SQL, par exemple dans une clause « SELECT », « WHERE » ou « ORDER
BY ».
9
Définition
• L’intérêt d’une fonction stockée réside dans la centralisation d’une
règle de calcul au niveau de la base de données.
• Au lieu de répéter le même traitement dans plusieurs applications ou
requêtes, celui-ci est défini une seule fois dans la fonction, ce qui
favorise la réutilisation, la cohérence et la maintenance du code.
10
Définition
• Une fonction stockée s’inscrit ainsi comme un composant logique du
système d’information, dédié aux traitements déterministes et aux
opérations de calcul effectuées au plus près des données.
• Le tableau suivant synthétise les différences entre une fonction stockée
et une procédure stockée :
11
Critère Procédure Fonction
Retour de valeur via OUT / INOUT via RETURN
Dans SELECT, WHERE,
Appel CALL
SET…
Usage principal Traitements Calculs / Transformations
Transaction Possible Fortement Déconseillée
Résultat Éventuellement plusieurs Une seule valeur
12
Syntaxe Générale
• La définition d’une fonction stockée en MySQL suit une structure
formelle qui précise son nom, ses paramètres éventuels, le type de
valeur retournée, le comportement, et le bloc d’instructions exécuté
lors de son appel.
• Comme pour les procédures stockées, la modification du délimiteur
est nécessaire afin de permettre l’écriture d’instructions multiples à
l’intérieur du corps de la fonction.
13
Syntaxe Générale
• La syntaxe générale d’une fonction stockée est la suivante :
14
Fonction
15
Syntaxe Générale
• Le mot‐clé « RETURNS » indique le type de la valeur renvoyée par la
fonction (entier, décimal, chaîne, etc…).
• La présence d’une instruction « RETURN » est obligatoire et marque la
valeur produite par la fonction.
16
Syntaxe Générale
• Le bloc « BEGIN … END » regroupe l’ensemble des instructions
nécessaires au calcul, qu’il s’agisse de requêtes SQL, d’affectations ou
de conditions.
17
Syntaxe Générale
• La fonction ainsi créée devient un objet persistant du SGBD et peut
être utilisée directement dans une requête SQL, au même titre qu’une
expression ou qu’une colonne calculée.
• Cette structure garantit une définition claire, centralisée et réutilisable
du traitement qu’elle encapsule.
18
Comportement
• Lors de la création d’une fonction stockée, MySQL peut exiger la
précision de certaines propriétés liées à son comportement et à son
interaction avec les données.
• Ces options servent principalement au moteur d’optimisation et à la
gestion de la réplication.
19
Comportement
• Les plus courantes sont les suivantes :
• « DETERMINISTIC » : Cette clause indique qu’à paramètres identiques,
la fonction produit toujours la même valeur. Elle est utilisée pour les
fonctions purement calculatoires et permet au SGBD d’optimiser leur
exécution.
20
Comportement
DELIMITER //
CREATE FUNCTION carre(x INT)
RETURNS INT
DETERMINISTIC
• Exemple : BEGIN
RETURN x * x;
END //
DELIMITER ;
21
Comportement
• La fonction dans l’exemple précédent est appelée comme suit :
SELECT carre(5);
22
Comportement
• Les plus courantes sont les suivantes :
• « NOT DETERMINISTIC » : Cette clause indique que la fonction peut
produire des résultats différents pour les mêmes paramètres. Cela
concerne les fonctions utilisant des valeurs temporelles, aléatoires ou
dépendant du contexte système.
23
Comportement
DELIMITER //
CREATE FUNCTION heure_actuelle()
RETURNS DATETIME
• Exemple : NOT DETERMINISTIC
NO SQL
RETURN NOW(); //
DELIMITER ;
24
Comportement
• La fonction dans l’exemple précédent est appelée comme suit :
SELECT heure_actuelle();
25
Comportement
• Les plus courantes sont les suivantes :
• « NO SQL » : signifie que la fonction n’exécute aucune instruction SQL
interne (pas de « SELECT », « INSERT », « UPDATE », etc…). Elle se limite
à des calculs ou à de la manipulation de chaînes. Cette option renseigne
MySQL sur le fait que la fonction n’interagit pas avec les données
stockées et qu’elle ne peut donc pas altérer l’état de la base.
26
Comportement
DELIMITER //
CREATE FUNCTION double_val(x INT)
RETURNS INT
• Exemple : DETERMINISTIC
NO SQL
RETURN x * 2; //
DELIMITER ;
27
Comportement
• La fonction dans l’exemple précédent est appelée comme suit :
SELECT double_val(5);
28
Comportement
• Les plus courantes sont les suivantes :
• « READS SQL DATA » : Cette clause indique que la fonction lit des
données dans la base (par exemple via un « SELECT ») mais ne les
modifie pas. Elle est utilisée pour encapsuler des requêtes de
consultation, tout en garantissant que la fonction reste sans effet sur
l’intégrité des données. C’est l’option la plus courante pour les fonctions
accédant aux tables.
29
Comportement
• Exemple :
30
31
Comportement
• La fonction dans l’exemple précédent est appelée comme suit :
SELECT prix_chambre(1, 6);
32
Comportement
• Les plus courantes sont les suivantes :
• « CONTAINS SQL » : signifie que la fonction contient du SQL interne,
mais sans lecture explicite de tables ni modification directe des données.
Elle est principalement déclarative et rarement utilisée en pratique dans
MySQL, mais elle respecte la syntaxe du standard SQL. Elle sert à
indiquer que le corps de la fonction peut comporter des instructions SQL
non liées aux données.
33
Comportement
DELIMITER //
CREATE FUNCTION message_bienvenue()
RETURNS VARCHAR(50)
• Exemple : DETERMINISTIC
CONTAINS SQL
RETURN 'Bienvenue dans MySQL'; //
DELIMITER ;
34
Comportement
• La fonction dans l’exemple précédent est appelée comme suit :
SELECT message_bienvenue();
35
Fonctions et Valeurs de Retour
• Une fonction stockée a pour vocation principale de produire une
valeur en sortie.
• Contrairement à une procédure stockée, elle ne s’appuie pas sur des
paramètres « OUT » ou « INOUT », mais renvoie son résultat
exclusivement au moyen de l’instruction « RETURN ».
36
Fonctions et Valeurs de Retour
• Cette valeur constitue l’issue du traitement et représente le résultat du
calcul ou de la transformation effectuée par la fonction.
• La présence d’au moins une instruction « RETURN » dans le corps de la
fonction est obligatoire.
37
Fonctions et Valeurs de Retour
• Elle marque la fin de l’exécution et transmet la valeur produite au
contexte appelant.
• La fonction ne peut retourner qu’une seule valeur, mais celle-ci peut
être de tout type supporté par MySQL (entier, nombre décimal, chaîne
de caractères, valeur booléenne, date, etc…).
38
Fonctions et Valeurs de Retour
• La valeur retournée par la fonction peut être utilisée directement dans
une requête SQL, au même titre qu’une expression ou qu’une colonne
calculée.
• Elle peut intervenir dans une projection « SELECT », une condition
« WHERE », un tri « ORDER BY » ou toute autre expression
arithmétique ou logique.
39
Fonctions et Valeurs de Retour
• Cette capacité d’intégration confère aux fonctions stockées un rôle
central dans la factorisation des calculs et l’enrichissement des
requêtes.
• En ce sens, la valeur de retour constitue l’élément distinctif
fondamental entre la fonction stockée et la procédure stockée, la
première étant orientée vers le calcul et l’évaluation d’un résultat
unique, tandis que la seconde relève davantage de l’exécution de
traitements ou de séquences d’opérations.
40
Fonctions et Transactions
• Les fonctions stockées en MySQL ne sont pas conçues pour gérer des
transactions ou pour modifier l’état de la base de données.
• Leur rôle principal est orienté vers le calcul et la production d’une
valeur, et non vers l’exécution d’opérations d’écriture.
41
Fonctions et Transactions
• Pour cette raison, l’utilisation d’instructions telles que « START
TRANSACTION », « COMMIT » ou « ROLLBACK » n’est pas
recommandée au sein d’une fonction.
• Une fonction stockée peut éventuellement lire des données lorsqu’elle
est déclarée avec l’option « READS SQL DATA », mais elle ne doit pas
modifier les tables ni déclencher d’effets de bord.
42
Fonctions et Transactions
• Les opérations d’insertion, de mise à jour ou de suppression sont donc
à proscrire dans ce contexte, car elles peuvent perturber la cohérence
transactionnelle et empêcher certaines optimisations du moteur.
• La gestion des transactions doit être réservée aux procédures stockées
ou aux couches applicatives qui orchestrent les traitements.
43
Fonctions et Transactions
• Celles-ci sont mieux adaptées aux enchaînements d’opérations
dépendantes et aux mécanismes de contrôle d’erreurs associés.
• La fonction stockée, quant à elle, reste limitée à un rôle calculatoire,
déterministe et indépendant du cycle transactionnel.
44
Fonctions et Transactions
• Ainsi, dans une architecture cohérente, les fonctions stockées sont
utilisées pour encapsuler des calculs ou des validations, tandis que les
procédures et les applications assurent la gestion des transactions et
des modifications de données.
45