Fonctions stockées

4.1 Difference entre procedure et fonction

Regle simple : si on a besoin d'une valeur calculee utilisable dans un SELECT, c'est une fonction. Sinon, c'est une procedure.

4.2 CREATE FUNCTION : syntaxe

DELIMITER //

CREATE FUNCTION nom_fonction (param1 TYPE, param2 TYPE, ...)
RETURNS type_retour
DETERMINISTIC    -- ou NOT DETERMINISTIC
BEGIN
    DECLARE v_resultat type_retour;
    -- calculs
    RETURN v_resultat;
END //

DELIMITER ;

Differences avec CREATE PROCEDURE :

• Les parametres sont toujours en entree (pas de IN/OUT/INOUT).
• RETURNS type_retour est obligatoire (indique le type de la valeur retournee).
• Le corps doit contenir au moins un RETURN.
• DETERMINISTIC ou NOT DETERMINISTIC est obligatoire en MySQL (sinon erreur si log_bin_trust_function_creators est desactive).

4.3 DETERMINISTIC vs NOT DETERMINISTIC

• DETERMINISTIC : pour les memes parametres en entree, la fonction retourne toujours le meme resultat. Exemple : calcul de TVA.
• NOT DETERMINISTIC : le resultat peut varier meme avec les memes parametres. Exemple : une fonction qui utilise NOW() ou qui lit des donnees en base.

En cas de doute, utiliser DETERMINISTIC si la fonction ne fait que des calculs mathematiques, NOT DETERMINISTIC si elle lit la base de donnees.

4.4 Exemple : prix TTC a partir du HT

Besoin : calculer le prix TTC (taux de TVA par defaut : 20%).

DELIMITER //

CREATE FUNCTION prix_ttc(p_prix_ht DECIMAL(10,2), p_taux_tva DECIMAL(5,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN ROUND(p_prix_ht * (1 + p_taux_tva / 100), 2);
END //

DELIMITER ;

Utilisation :

SELECT designation, prix_ht, prix_ttc(prix_ht, 20) AS prix_ttc
FROM produit;

La fonction s'utilise exactement comme ROUND() ou CONCAT() : directement dans un SELECT.

4.5 Exemple : age a partir de la date de naissance

DELIMITER //

CREATE FUNCTION calculer_age(p_date_naissance DATE)
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE v_age INT;

    SET v_age = TIMESTAMPDIFF(YEAR, p_date_naissance, CURDATE());

    RETURN v_age;
END //

DELIMITER ;

Note : cette fonction utilise CURDATE() qui change chaque jour. On pourrait argumenter qu'elle est NOT DETERMINISTIC. En pratique, pour l'examen BTS SIO, les deux sont acceptes. L'important est de justifier son choix.

Utilisation :

SELECT nom, prenom, date_naissance, calculer_age(date_naissance) AS age
FROM client
ORDER BY age DESC;

4.6 DROP FUNCTION

DROP FUNCTION IF EXISTS prix_ttc;
DROP FUNCTION IF EXISTS calculer_age;