D4-C05 : Procédures stockées et fonctions avec MariaDB

MariaDB (comme MySQL) permet d’écrire du code SQL réutilisable grâce aux procédures stockées et aux fonctions stockées.

Ces programmes sont stockés côté serveur et exécutés par le SGBD.

• Performance : moins d’échanges entre l’application et la base.
• Sécurité : on limite l’accès aux tables brutes, les applications n’appellent que des procédures.
• Maintenance : la logique métier est centralisée dans la base.
• Réutilisabilité : une règle métier est codée une fois, utilisée partout.

DELIMITER //
CREATE PROCEDURE nom_procedure(
    [IN | OUT | INOUT] param_nom TYPE, ...
)
BEGIN
   -- instructions SQL
END //
DELIMITER ;

CALL nom_procedure(valeurs_param);

DELIMITER //
CREATE PROCEDURE sp_nb_logements_vacants(OUT nb INT)
BEGIN
   SELECT COUNT(*) INTO nb
   FROM logement
   WHERE etat = 'VACANT';
END //
DELIMITER ;

Appel et affichage du résultat :

CALL sp_nb_logements_vacants(@result);
SELECT @result AS "Nombre de logements vacants";

Syntaxe générale DELIMITER CREATE FUNCTION nom_fonction(param TYPE, …) RETURNS TYPE DETERMINISTIC BEGIN DECLARE resultat TYPE; – logique SET resultat = …; RETURN resultat; END DELIMITER ;

👉 Appel :

SELECT nom_fonction(valeur);

Exemple simple DELIMITER CREATE FUNCTION fn_calcul_tva(prix_ht DECIMAL(10,2)) RETURNS DECIMAL(10,2) DETERMINISTIC BEGIN RETURN prix_ht * 1.20; END DELIMITER ;

Utilisation :

SELECT fn_calcul_tva(100) AS prix_ttc;

4. Gestion des erreurs

On peut intercepter une erreur grâce aux handlers :

DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN

1. - log ou rollback

SELECT 'Erreur rencontrée, annulation de la procédure' AS message;

 ROLLBACK;

END;

5. Curseurs

Quand une requête retourne plusieurs lignes, on peut les parcourir :

DECLARE c CURSOR FOR SELECT nom, prenom FROM locataire; DECLARE CONTINUE HANDLER FOR NOT FOUND SET fini = 1;

OPEN c; boucle: LOOP

  FETCH c INTO v_nom, v_prenom;
  IF fini = 1 THEN
      LEAVE boucle;
  END IF;
  -- traitement ligne par ligne

END LOOP; CLOSE c;

6. Bonnes pratiques professionnelles

Nommage clair : sp_ pour procédures, fn_ pour fonctions.

Commenter chaque procédure (auteur, date, rôle, paramètres).

Centraliser la logique métier sensible dans des procédures.

Versionner les scripts SQL dans Git.

Donner des droits d’exécution sur les procédures plutôt que des droits directs sur les tables.