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

• Documentation officielle MySQL : Stored Programs
• Bonnes pratiques SQL : conventions de nommage et gestion des droits
  • https://dotclear.placeoweb.com/post/R%C3%A8gles-et-conventions-de-nommage-SQL
  • https://fr.ittrip.xyz/sql/sql-naming-conventions|
• Tutoriels professionnels (Zeste de savoir, Percona, MariaDB docs).

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";

DELIMITER //
CREATE FUNCTION nom_fonction(param TYPE, ...)
RETURNS TYPE
DETERMINISTIC
BEGIN
   DECLARE resultat TYPE;
   -- logique
   SET resultat = ...;
   RETURN resultat;
END //
DELIMITER ;

SELECT nom_fonction(valeur);

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

SELECT fn_calcul_tva(100) AS prix_ttc;

On peut intercepter une erreur grâce aux handlers :

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
   -- log ou rollback
   SELECT 'Erreur rencontrée, annulation de la procédure' AS message;
   ROLLBACK;
END;

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;

1. Nommage clair : sp_ pour procédures, fn_ pour fonctions.
2. Commenter chaque procédure (auteur, date, rôle, paramètres).
3. Centraliser la logique métier sensible dans des procédures.
4. Versionner les scripts SQL dans Git.
5. Donner des droits d’exécution sur les procédures plutôt que des droits directs sur les tables.

A06