Procédures et fonctions

Interruption de l'exécution d'une procedure

-- Avec l'utilisation d'une étiquette
CREATE PROCEDURE <nom de la procédure>()
this_proc:BEGIN
    IF <condition> THEN
        LEAVE this_proc;
    END IF;
    ...
END;
 
-- Avec l'utilisation d'un bloc IF
CREATE PROCEDURE <nom de la procédure>()
BEGIN
    IF <condition> THEN
        ...
    END IF;
END;

La première méthode est plus intéressante car elle permet de garder un code propre, même quand le nombre de d'interruption augmente.
Avec la seconde méthode, il sera nécessaire d'imbriquer de multiples blocs IF, le rendant moins lisible.

Définition d'une procédure

Utilisateur de création

S'il n'est pas explicitement mentionné, l'utilisateur utilisé pour la définition de la procédure sera utilisé.
Il est possible de spécifier l'utilisateur à utiliser :

CREATE DEFINER = `<utilisateur>`@`<hôte>` PROCEDURE <nom de la procédure>()
BEGIN
   ...
END

Note : il n'est pas nécessaire que l'utilisateur existe sur la base de données.

Type de sécurité appliquée

Il existe 2 types de sécurité pour l'exécution des procédures :

DEFINER : utilise les droits de l'utilisateur utiliser pour la définition de la procédure,
INVOKER : utilise les droits de l'utilisateur appelant la procédure.

S'il n'est pas explicitement mentionné, le type de sécurité appliqué sera DEFINER.
Lors de la création d'une procédure, il est possible de définir le type de sécurité à appliquer.

CREATE PROCEDURE <nom de la procédure>() SQL SECURITY INVOKER
BEGIN
   ...
END

Note : si l'utilisateur n'existe pas et que le type de sécurité appliquée est DEFINER, l'erreur suivante sera générée:

ERROR 1449 (HY000): The user specified as a definer ('<utilisateur>'@'%') does not exist

Commentaires

CREATE PROCEDURE <nom de la procédure>()
COMMENT '<commentaire>'
BEGIN
    ...
END

Fonctions utiles

Ajout d'une durée normalisée à une date

Selon la norme ISO 8601, on peut normaliser les durées utilisées pour le calcul de dates. MySQL permet d'ajouter des durées à des dates mais en utilisant une syntaxe n'utilisant pas les durées normalisées. La fonction ¹⁾qui suit permet de faire ça mais uniquement pour les jours, les semaines, les mois et les années.

CREATE FUNCTION ADD_ISO_DURATION(StartDate DATE, Duration VARCHAR(45))
RETURNS DATE
BEGIN
  DECLARE Pos INTEGER;
 
  IF StartDate IS NULL OR Duration IS NULL OR LENGTH(Duration) = 0 THEN
    RETURN StartDate;
  END IF;
 
  IF Duration REGEXP '^P[0-9]+W$' THEN
    SET StartDate = DATE_ADD(StartDate, INTERVAL SUBSTR(Duration, 2, LENGTH(Duration) - 2) WEEK);
    RETURN StartDate;
  END IF;
 
  IF Duration NOT REGEXP '^P([0-9]+Y)?([0-9]+M)?([0-9]+D)?$' THEN
    RETURN NULL;
  END IF;
 
  SET Duration = SUBSTR(Duration, 2);
 
  SET Pos = LOCATE('Y', Duration);
  IF Pos <> 0 THEN
    SET StartDate = DATE_ADD(StartDate, INTERVAL SUBSTR(Duration, 1, Pos - 1) YEAR);
    SET Duration = SUBSTR(Duration, Pos + 1);
  END IF;
 
  SET Pos = LOCATE('M', Duration);
  IF Pos <> 0 THEN
    SET StartDate = DATE_ADD(StartDate, INTERVAL SUBSTR(Duration, 1, Pos - 1) MONTH);
    SET Duration = SUBSTR(Duration, Pos + 1);
  END IF;
 
  SET Pos = LOCATE('D', Duration);
  IF Pos <> 0 THEN
    SET StartDate = DATE_ADD(StartDate, INTERVAL SUBSTR(Duration, 1, Pos - 1) DAY);
  END IF;
 
  RETURN StartDate;
END

¹⁾

Largement inspirée de iso8601 - MySQL - Convert ISO-8601 Duration to Interval for DATE_ADD - Stack Overflow