Procédure stockée
Une procédure stockée (stored procedure) est un bloc de code SQL enregistré
dans la base de données, exécutable par appel. Elle permet d’encapsuler logique,
réutiliser du code, améliorer la maintenance et parfois les performances.
1. Pourquoi utiliser des procédures ?
• Réutilisabilité : centraliser la logique côté serveur.
• Performance : plan d’exécution précompilé (selon SGBD).
• Sécurité : contrôle d’accès (donner droit d’exécution sans donner accès
direct aux tables).
• Atomicité : exécuter plusieurs opérations dans une seule unité logique
(transactions).
• Maintenance : modifier la logique sans changer les clients.
2. Concepts clés
• Création : CREATE PROCEDURE ...
• Appel : CALL proc_name(...) (MySQL) / EXEC proc_name (SQL Server)
• Paramètres : IN, OUT, INOUT (MySQL / PostgreSQL concept similaire), ou
seulement @param en SQL Server (avec OUTPUT).
• Variables locales : déclarées dans la procédure.
• Gestion des exceptions / erreurs : diffère par SGBD (MySQL HANDLER, SQL
Server TRY...CATCH, Oracle EXCEPTION).
• Transactions : START TRANSACTION / COMMIT / ROLLBACK (attention au
comportement selon SGBD et niveau d’autocommit).
3. Paramètres : IN / OUT / INOUT (MySQL) — équivalents SQL Server
• IN : paramètre d’entrée (valeur fournie par l’appelant). Ne renvoie pas de
modification.
• OUT : paramètre de sortie ; la procédure écrit dedans pour renvoyer une
valeur.
• INOUT : à la fois entrée et sortie (valeur fournie, peut être modifiée et
renvoyée).
4. Syntaxes et exemples (MySQL / WAMP) — points importants
a) Rappel : DELIMITER
MySQL utilise ; par défaut. Pour définir une procédure contenant plusieurs ;, on
change temporairement le délimiteur :
1
Paramètre IN
Définition
• Permet d’envoyer une valeur à la procédure stockée.
• La procédure peut utiliser cette valeur mais ne peut pas la modifier pour
la renvoyer.
• C’est un paramètre d’entrée.
Ici :
• p_id est donné par l’utilisateur.
• La procédure l’utilise pour faire un SELECT.
2. Paramètre OUT
Définition
• Permet à la procédure de renvoyer une valeur au programme appelant.
• C’est un paramètre de sortie.
• Généralement, la procédure modifie la variable passée.
Ici :
• La variable total ne vient pas de l’utilisateur.
• La procédure remplit cette variable et la renvoie.
3. Combinaison IN et OUT
b) Exemple complet (MySQL) avec IN
1. Creation de la base de données et d’une table avec quelques enregistrements
CREATE DATABASE IF NOT EXISTS test_procedures;
CREATE TABLE employe (
id INT PRIMARY KEY AUTO_INCREMENT,
nom VARCHAR(50),
salaire DECIMAL(10,2),
dept INT
);
INSERT INTO employe (nom, salaire, dept) VALUES
('Ali', 5000, 1),
('Sara', 6200, 1),
('Mohamed', 4500, 2),
('Imane', 7000, 2);
2. Procédure avec paramètre IN
DELIMITER $$
CREATE PROCEDURE get_employes_by_dept(IN p_dept INT)
BEGIN
SELECT * FROM employe
WHERE dept = p_dept;
END $$
DELIMITER ;
3. Appel de la procédure (IN)
CALL get_employes_by_dept(1);
c) Exemple complet (MySQL) avec OUT
1. Création de la procédure OUT stockée
DELIMITER $$
CREATE PROCEDURE get_total_salaire(
IN p_dept INT,
OUT total DECIMAL(10,2)
)
BEGIN
SELECT SUM(salaire)
INTO total
FROM employe
WHERE dept = p_dept;
END $$
DELIMITER ;
2. Appel de la procédure avec paramètre OUT
Étape 1 : Appeler la procédure
CALL get_total_salaire(2, @tot);
Étape 2 : Lire la valeur OUT
SELECT @tot AS total_salaire_dept_2;
5. Structures de contrôle (communes)
• IF ... THEN ... ELSEIF ... ELSE ... END IF (MySQL/Oracle)
• CASE (SQL standard)
• LOOPS :
o WHILE ... DO ... END WHILE
o LOOP ... LEAVE ... END LOOP (MySQL)
o REPEAT ... UNTIL ... END REPEAT
• Cursors pour parcourir des lignes (utile pour logique ligne-par-ligne).
Exemple WHILE (MySQL) :
6. Gestion d’erreurs
• MySQL : DECLARE ... HANDLER FOR condition
o DECLARE EXIT HANDLER FOR SQLEXCEPTION pour capturer erreurs
et faire ROLLBACK, message, etc.
Remarque :
• SQL Server : BEGIN TRY ... END TRY BEGIN CATCH ... END CATCH avec
ERROR_MESSAGE() etc.
• Oracle : bloc EXCEPTION avec WHEN OTHERS THEN ....
7. Transactions et effets
• Les procédures peuvent démarrer et contrôler des transactions.
• Choisir soigneusement : démarrer un TRANSACTION dans une procédure
appelée depuis une transaction existante peut compliquer les choses.
• Toujours gérer erreurs et faire ROLLBACK si besoin.
C’est quoi une transaction dans SQL :
Une transaction en SQL est un ensemble d’opérations (INSERT, UPDATE, DELETE…)
qui sont exécutées comme une seule unité logique de travail.
L’objectif est de garantir que toutes les opérations réussissent ensemble, ou
aucune si quelque chose échoue.
Objectif principal
Assurer l’intégrité et la cohérence des données.
Propriétés d’une transaction : ACID
Une transaction doit respecter les 4 propriétés ACID :
• Atomicité (A)
Tout ou rien :
• Si toutes les opérations réussissent → COMMIT
• Si une seule échoue → ROLLBACK
• Cohérence (C)
La base reste dans un état valide avant et après la transaction.
• Isolation (I)
Les transactions en parallèle ne doivent pas interférer entre elles.
• Durabilité (D)
Une fois validée (COMMIT), la transaction reste enregistrée même en cas de
panne.
Exemple simple en SQL (MySQL)
COMMIT : C’est commande SQL pour Valider définitivement toutes les
modifications
ROLLBACK : C’est une commande SQL pour Annuler toutes les modifications
depuis le début de la transaction
Ici, on transfère de l’argent :
• si les deux UPDATE réussissent alors on exécute la commande COMMIT
• sinon on execute la commande ROLLBACK
Résumé :
Pourquoi utiliser une transaction ?
• Pour les opérations critiques (paiement, réservation…)
• Pour éviter les données incohérentes
• Pour regrouper plusieurs requêtes en un seul bloc sûr
8. Sécurité et droits
• Droits d’exécution (GRANT EXECUTE ON PROCEDURE ... TO user;).
• Avantage : donner EXECUTE sans accorder SELECT/INSERT sur les tables
internes.
• Attention aux injections SQL si vous construisez dynamiquement des
requêtes (PREPARE / EXECUTE) — valider/échapper les entrées.