d4:c05 [SIO Wiki]

Cette page est en lecture seule. Vous pouvez afficher le texte source, mais ne pourrez pas le modifier. Contactez votre administrateur si vous pensez qu'il s'agit d'une erreur.
====== D4-C05 : Procédures stockées et fonctions avec MariaDB ======

===== Ressources =====

  * Documentation officielle [[https://dev.mysql.com/doc/dev/mysql-server/latest/stored_programs.html|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).
===== 1. Introduction =====
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**.

===== Avantages en milieu professionnel =====
  * 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.

===== 2. Procédures stockées =====
==== Syntaxe générale ====
<code>
DELIMITER //
CREATE PROCEDURE nom_procedure(
    [IN | OUT | INOUT] param_nom TYPE, ...
)
BEGIN
   -- instructions SQL
END //
DELIMITER ;
</code>

==== Appel ====
<code>CALL nom_procedure(valeurs_param);</code>

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

Appel et affichage du résultat :
<code>
CALL sp_nb_logements_vacants(@result);
SELECT @result AS "Nombre de logements vacants";
</code>

===== 3. Fonctions stockées =====
==== Syntaxe générale ====
<code>
DELIMITER //
CREATE FUNCTION nom_fonction(param TYPE, ...)
RETURNS TYPE
DETERMINISTIC
BEGIN
   DECLARE resultat TYPE;
   -- logique
   SET resultat = ...;
   RETURN resultat;
END //
DELIMITER ;
</code>

==== Appel : ====
<code>SELECT nom_fonction(valeur);</code>

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

==== Utilisation : ====
<code>SELECT fn_calcul_tva(100) AS prix_ttc;</code>

===== 4. Gestion des erreurs =====
On peut intercepter une erreur grâce aux handlers :
<code>
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
   -- log ou rollback
   SELECT 'Erreur rencontrée, annulation de la procédure' AS message;
   ROLLBACK;
END;
</code>
===== 5. Curseurs =====
Quand une requête retourne plusieurs lignes, on peut les parcourir :
<code>
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;
</code>
===== 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.

[[d4:A06]]