Les fonctions stockées (Stored function)

Une fonction est une petite routine qui retourne une valeur unique et qu'on peut réutiliser. MySQL comporte déjà plusieurs fonctions (NOW(), CONCAT()) mais il est aussi possible d'en créer de nouvelle.

Création d'une fonction

Voici la syntaxe de création d'une fonction

DELIMITER $$

CREATE FUNCTION [IF NOT EXISTS] nom_fonction(
    paramètre1,
    paramètre2,…
)
RETURNS type_de_donnée
[NOT] DETERMINISTIC
BEGIN
 -- Le traitement
END $$

DELIMITER ;

paramètres

Les paramètres sont définis par un nom, un type de données et un indicateur IN, OUT ou INOUT.
IN indique que le paramêtre est en entrée, OUT en sortie et INOUT pour les deux. La valeur d'un paramêtre IN est "passée" dans la fonction et utilisée dans celle-ci. Pour un paramêtre OUT on peut récupérer sa valeur à la sortie de la fonction. Ce n'est pas la même valeur que le retour de la fonction, on peut donc s'en servir pour retourner plus d'une valeur à la fonction. INOUT permet les deux. Par défaut et si on ne l'indique pas le paramêtre sera IN.
Chaque paramêtre est séparé par une virgule.

Type de retour

Après le mot-clé RETURNS on indique le type de données de la valeur de retour de la fonction. Ça peut être n'importe quel type MySQL valide (INTEGER, VARCHAR(), TINYINT(1))

DETERMINISTIC vs NOT DETERMINISTIC

Une fonction DETERMINISTIC va toujours retourner la même valeur si on lui donne les mêmes paramètres. Une fonction NOT DETERMINISTIC peut retourner un résultat différent même si on lui donne les mêmes paramètres. C'est une valeur "indicative" et MySQL fait confiance au développeur quand à la véracité de l'affimation. Par contre pour une question d'optimisation et de performance il est préférable d'indiquer le bon type. Par défaut la fonction sera NOT DETERMINISTIC.

Note

On peut se donner comme trucs qu'une fonction qui va retourner un résultat en fonction d'un ou plusieurs champs d'une table sera NOT DETERMINISTIC

Le traitement

Habituellement dans le traitment on va déclarer une variavble au tout début qui sera utilisée pour contenir le résultat de retour de la fonction.
Le traitement doit absolument contenir un instruction RETURN qui marque la fin de la fonction et retourne la valeur.
On peut utiliser des HANDLERs dans une fonction pour controller les erreurs et le traitement.

Exemple d'une fonction qui reçoit en paramètre une note et qui retourne un commentaire selon la note.

DELIMITER $$

CREATE FUNCTION note_commentaire(resultat INTEGER)
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    DECLARE commentaire VARCHAR(20);

    IF resultat >= 60 THEN
        SET commentaire = "Réussite";
    ELSE
        SET commentaire = "Échec";
    END IF;

    RETURN commentaire;

END$$

DELIMITER ;

Utilisation d'une fonction

On peut utiliser la fonction dans n'importe quel instruction MySQL et dans une procédure stockée.

SELECT note_commentaire(r.note)
FROM resultat r
WHERE r.etudiant_id = 1234;

Modification d'une fonction

On ne peut pas modifier le traitement d'une fonction. Si on doit faire des modifications, on devra la supprimer et la créer à nouveau.

Suppression d'une fonction

Syntaxe

DROP FUNCTION [IF EXISTS] function_name;