Fonction stockée MySQL : CREATE FUNCTION et RETURNS

Une fonction stockée est une routine qui reçoit des paramètres et renvoie une seule valeur avec RETURN. Elle se crée avec CREATE FUNCTION nom(paramètres) RETURNS type et s'utilise directement dans une requête, comme une fonction native de MySQL (UPPER(), NOW(), DATEDIFF()). Ce chapitre montre comment l'écrire, comment déclarer ses caractéristiques (DETERMINISTIC, READS SQL DATA) et ce qu'elle n'a pas le droit de faire.

Ta première fonction

Le temps passé sur un ticket, c'est la somme des durées de ses interventions. On en a besoin dans plusieurs requêtes : on l'écrit une fois.

DELIMITER //
CREATE FUNCTION duree_totale_ticket(p_id_ticket INT) RETURNS INT
READS SQL DATA
BEGIN
  DECLARE v_total INT;

  SELECT COALESCE(SUM(duree_min), 0) INTO v_total
  FROM intervention
  WHERE id_ticket = p_id_ticket;

  RETURN v_total;
END //
DELIMITER ;

Elle s'utilise partout où une expression est permise : dans la liste du SELECT, dans un WHERE, un ORDER BY, un SET.

SELECT id_ticket, titre, duree_totale_ticket(id_ticket) AS minutes
FROM ticket
WHERE statut = 'resolu' AND duree_totale_ticket(id_ticket) >= 120
ORDER BY minutes DESC;

La syntaxe complète :

CREATE FUNCTION [IF NOT EXISTS] nom_fonction (parametre TYPE, …)
RETURNS type_retour
[DETERMINISTIC | NOT DETERMINISTIC]
[NO SQL | READS SQL DATA | CONTAINS SQL | MODIFIES SQL DATA]
[SQL SECURITY { DEFINER | INVOKER }]
[COMMENT 'texte']
BEGIN
  …
  RETURN expression;
END

Trois différences avec une procédure : les paramètres n'ont pas de mode (ils sont toujours en entrée), la clause RETURNS (avec un S) donne le type renvoyé, et le corps doit exécuter une instruction RETURN (sans S) sur chaque chemin. Une fonction qui atteint son END sans RETURN échoue à l'exécution avec l'erreur 1321, FUNCTION … ended without RETURN.

Procédure ou fonction ?

Règle simple : si tu as besoin de la valeur dans une requête, c'est une fonction ; si tu agis sur les données ou renvoies plusieurs choses, c'est une procédure.

DETERMINISTIC, NO SQL, READS SQL DATA : les caractéristiques

Une fonction déclare deux informations sur elle-même.

Est-elle déterministe ? DETERMINISTIC signifie : « avec les mêmes paramètres, je renvoie toujours le même résultat ». Une fonction qui ne fait qu'un calcul sur ses paramètres l'est ; une fonction qui lit CURDATE(), NOW(), RAND() ou le contenu d'une table ne l'est pas, puisque le résultat change avec le temps ou les données. Par défaut, une routine est NOT DETERMINISTIC.

Que fait-elle des données ?

Pourquoi est-ce important ? Parce que MySQL 8.4 active par défaut le journal binaire (binary log), qui sert à la réplication et à la restauration à un instant donné (cours SQL). Une fonction qui donnerait un résultat différent sur un serveur répliqué rendrait les copies incohérentes. MySQL exige donc qu'une fonction déclare au moins une des caractéristiques DETERMINISTIC, NO SQL ou READS SQL DATA :

DELIMITER //
CREATE FUNCTION f_sans_caracteristique(p_id INT) RETURNS INT
BEGIN
  RETURN p_id * 2;
END //
DELIMITER ;

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its
declaration and binary logging is enabled (you *might* want to use the less safe
log_bin_trust_function_creators variable)

Trois exemples justes :

DELIMITER //
-- Un pur calcul : déterministe, sans SQL. 570 minutes donnent '9 h 30'.
CREATE FUNCTION libelle_duree(p_minutes INT) RETURNS VARCHAR(20)
DETERMINISTIC NO SQL
BEGIN
  IF p_minutes IS NULL THEN
    RETURN NULL;
  END IF;
  RETURN CONCAT(p_minutes DIV 60, ' h ', LPAD(p_minutes MOD 60, 2, '0'));
END //

-- Dépend de la date du jour : non déterministe, mais sans SQL
CREATE FUNCTION anciennete_ans(p_date DATE) RETURNS INT
NOT DETERMINISTIC NO SQL
BEGIN
  RETURN TIMESTAMPDIFF(YEAR, p_date, CURDATE());
END //
DELIMITER ;

-- duree_totale_ticket, plus haut, lit une table : READS SQL DATA

Les fonctions se combinent :

SELECT id_ticket, titre, libelle_duree(duree_totale_ticket(id_ticket)) AS temps_passe
FROM ticket
WHERE id_ticket IN (1, 5, 9);

Une fonction qui modifie des données et le déclare honnêtement (MODIFIES SQL DATA, NOT DETERMINISTIC) ne coche aucune des trois cases exigées : elle est refusée avec l'erreur 1418, sauf à activer la variable log_bin_trust_function_creators. Cette variable lève le contrôle pour tout le serveur, demande un compte administrateur et ne se règle pas chez un hébergeur mutualisé. Le journal binaire impose aussi une condition au créateur : sans le privilège SUPER (ou cette variable à 1), créer une fonction échoue avec l'erreur 1419, You do not have the SUPER privilege and binary logging is enabled. Tu la rencontreras sur un serveur partagé où ton compte n'est pas administrateur. Le refus de l'erreur 1418 est un bon signal : une fonction ne devrait pas modifier les données. Si un calcul doit écrire, écris une procédure.

Des fonctions utiles pour la base parc

Les jours ouvrés d'attente. La procédure du chapitre 3 devient une fonction, utilisable dans une requête :

DELIMITER //
CREATE FUNCTION jours_ouvres(p_debut DATE, p_fin DATE) RETURNS INT
DETERMINISTIC NO SQL
BEGIN
  DECLARE v_jour DATE DEFAULT p_debut;
  DECLARE v_nb   INT DEFAULT 0;

  WHILE v_jour <= p_fin DO
    IF DAYOFWEEK(v_jour) NOT IN (1, 7) THEN   -- ni dimanche (1) ni samedi (7)
      SET v_nb = v_nb + 1;
    END IF;
    SET v_jour = v_jour + INTERVAL 1 DAY;
  END WHILE;

  RETURN v_nb;
END //
DELIMITER ;

SELECT id_ticket, titre, DATE(date_ouverture) AS ouvert_le,
       jours_ouvres(DATE(date_ouverture), '2026-11-06') AS jours_ouvres_attente
FROM ticket
WHERE statut <> 'resolu'
ORDER BY id_ticket;

Elle est déterministe : deux dates données produisent toujours le même nombre. C'est la requête qui fixe la date de fin (ici le vendredi 6 novembre 2026) ; avec CURDATE() à la place, la requête change de résultat chaque jour, mais pas la fonction.

Le prochain numéro d'inventaire. La procédure ajouter_postes_stock du chapitre 3 calculait le numéro suivant ; ce calcul mérite sa fonction, que le formulaire de saisie peut afficher :

DELIMITER //
CREATE FUNCTION prochain_num_inventaire() RETURNS CHAR(7)
READS SQL DATA
BEGIN
  DECLARE v_max INT;
  SELECT MAX(CAST(SUBSTRING(num_inventaire, 4) AS UNSIGNED)) INTO v_max FROM poste;
  RETURN CONCAT('PC-', LPAD(COALESCE(v_max, 0) + 1, 4, '0'));
END //
DELIMITER ;

SELECT prochain_num_inventaire();   -- PC-0016

Ce qu'une fonction n'a pas le droit de faire

La dernière ligne surprend souvent : on ne peut pas écrire ALTER TABLE ticket ADD COLUMN minutes INT AS (duree_totale_ticket(id_ticket)). Une colonne générée ne peut utiliser que des fonctions natives déterministes ; pour stocker un calcul qui dépend d'autres tables, on utilise un trigger qui tient la colonne à jour (chapitre 8).

Fonction et performance

Une fonction est appelée une fois par ligne. SELECT duree_totale_ticket(id_ticket) FROM ticket exécute 14 fois la requête de la fonction ; sur 100 000 tickets, 100 000 fois. Et une condition WHERE ma_fonction(colonne) = valeur empêche d'utiliser un index sur la colonne : toute la table est lue.

La même information s'obtient en une seule requête ensembliste :

SELECT t.id_ticket, t.titre, COALESCE(SUM(i.duree_min), 0) AS minutes
FROM ticket t
LEFT JOIN intervention i ON i.id_ticket = t.id_ticket
GROUP BY t.id_ticket, t.titre;

Réserve donc les fonctions qui lisent des tables aux petits volumes, ou aux calculs que tu veux absolument écrire une seule fois. Les fonctions de pur calcul (libelle_duree, jours_ouvres) ne posent pas ce problème.

Gérer ses fonctions

SHOW FUNCTION STATUS WHERE Db = 'parc';
SHOW CREATE FUNCTION libelle_duree;
DROP FUNCTION IF EXISTS libelle_duree;

SELECT routine_name, is_deterministic, sql_data_access
FROM information_schema.routines
WHERE routine_schema = 'parc' AND routine_type = 'FUNCTION';

Une fonction et une procédure peuvent porter le même nom (ce sont deux espaces de noms distincts), mais évite-le. Évite aussi les noms des fonctions natives (now, count) : MySQL ne te laisserait appeler la tienne qu'avec une espace avant la parenthèse.

Exercices

DELIMITER //
CREATE FUNCTION nb_postes(p_id_employe INT) RETURNS INT
READS SQL DATA
BEGIN
  DECLARE v_nb INT;
  SELECT COUNT(*) INTO v_nb FROM poste WHERE id_employe = p_id_employe;
  RETURN v_nb;
END //
DELIMITER ;

SELECT nom, prenom, nb_postes(id_employe) AS postes
FROM employe
WHERE nb_postes(id_employe) <> 1
ORDER BY nom;

SELECT e.nom, e.prenom, COUNT(p.id_poste) AS postes
FROM employe e
LEFT JOIN poste p ON p.id_employe = e.id_employe
GROUP BY e.id_employe, e.nom, e.prenom
HAVING COUNT(p.id_poste) <> 1
ORDER BY e.nom;

DELIMITER //
CREATE FUNCTION taux_utilisation(p_id_logiciel INT) RETURNS DECIMAL(5,1)
READS SQL DATA
BEGIN
  DECLARE v_licences   INT;
  DECLARE v_installees INT;

  SELECT nb_licences INTO v_licences FROM logiciel WHERE id_logiciel = p_id_logiciel;
  IF v_licences IS NULL OR v_licences = 0 THEN
    RETURN NULL;
  END IF;

  SELECT COUNT(*) INTO v_installees FROM installation WHERE id_logiciel = p_id_logiciel;
  RETURN v_installees * 100 / v_licences;
END //
DELIMITER ;

DELIMITER //
CREATE FUNCTION categorie_prix(p_prix DECIMAL(8,2)) RETURNS VARCHAR(10)
DETERMINISTIC NO SQL
BEGIN
  IF p_prix < 700 THEN
    RETURN 'entrée';
  ELSEIF p_prix < 1150 THEN
    RETURN 'milieu';
  END IF;
END //
DELIMITER ;