CREATE PROCEDURE adoption_deux_ou_rien(p_client_id INT,
p_animal_id_1 INT, p_animal_id_2 INT)
BEGIN
DECLARE v_prix DECIMAL(7,2);
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK; -- Gestionnaire
qui annule la transaction et termine la procédure
START TRANSACTION;
SELECT COALESCE(Race.prix, Espece.prix) INTO v_prix
FROM Animal
INNER JOIN Espece ON Espece.id = Animal.espece_id
LEFT JOIN Race ON Race.id = Animal.race_id
WHERE Animal.id = p_animal_id_1;
INSERT INTO Adoption (animal_id, client_id, date_reservation,
date_adoption, prix, paye)
VALUES (p_animal_id_1, p_client_id, CURRENT_DATE(),
CURRENT_DATE(), v_prix, TRUE);
SELECT 'Adoption animal 1 réussie' AS message;
SELECT COALESCE(Race.prix, Espece.prix) INTO v_prix
FROM Animal
INNER JOIN Espece ON Espece.id = Animal.espece_id
LEFT JOIN Race ON Race.id = Animal.race_id
WHERE Animal.id = p_animal_id_2;
INSERT INTO Adoption (animal_id, client_id, date_reservation,
date_adoption, prix, paye)
VALUES (p_animal_id_2, p_client_id, CURRENT_DATE(),
CURRENT_DATE(), v_prix, TRUE);
SELECT 'Adoption animal 2 réussie' AS message;
COMMIT;
END|
DELIMITER ;
CALL adoption_deux_ou_rien(2, 43, 55); -- L'animal 55 a déjà été
adopté
La procédure s'interrompt, puisque la seconde insertion échoue. On n'exécute donc pas le second SELECT. Ici, grâce à la
transaction et au ROLLBACK du gestionnaire, la première insertion a été annulée.
On ne peut pas utiliser de transactions dans un trigger.
Préparer une requête dans un bloc d'instructions
Pour finir, on peut créer et exécuter une requête préparée dans un bloc d'instructions. Ceci permet de créer des requêtes
dynamiques, puisqu'on prépare une requête à partir d'une chaîne de caractères.
Exemple : la procédure suivante ajoute la ou les clauses que l'on veut à une simple requête SELECT :
Code : SQL
DELIMITER |
CREATE PROCEDURE select_race_dynamique(p_clause VARCHAR(255))
BEGIN
SET @sql = CONCAT('SELECT nom, description FROM Race ',
p_clause);
PREPARE requete FROM @sql;
Partie 5 : Sécuriser et automatiser ses actions
308/414
www.openclassrooms.com