Compte Rendu TP 6 - SQL Avancé
Base de données “Parc” - Procédures, Fonctions, Curseurs, Transactions et
Déclencheurs
AMDA Mohamed AMINE
Partie I: Procédures, Fonctions stockées et Exceptions
1. Procédure affichant les détails de la dernière installation de logiciel
DELIMITER //
CREATE PROCEDURE afficher_derniere_installation()
BEGIN
DECLARE v_nSalle VARCHAR(7);
DECLARE v_nomSalle VARCHAR(20);
DECLARE v_nPoste VARCHAR(7);
DECLARE v_nomPoste VARCHAR(20);
DECLARE v_nLog VARCHAR(5);
DECLARE v_nomLog VARCHAR(20);
DECLARE v_dateIns DATE;
DECLARE no_installation CONDITION FOR SQLSTATE '02000';
DECLARE CONTINUE HANDLER FOR no_installation
BEGIN
SELECT 'Aucune installation trouvée' AS message;
END;
DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
SELECT 'Poste ou logiciel inconnu' AS message;
END;
SELECT
[Link], [Link],
[Link], [Link],
[Link], [Link],
[Link]
INTO
v_nSalle, v_nomSalle,
v_nPoste, v_nomPoste,
v_nLog, v_nomLog,
v_dateIns
FROM Installer I
JOIN Poste P ON [Link] = [Link]
JOIN Salle S ON [Link] = [Link]
JOIN Logiciel L ON [Link] = [Link]
ORDER BY [Link] DESC
LIMIT 1;
SELECT CONCAT('La dernière installation a eu lieu dans la salle ',
v_nSalle, ' (', v_nomSalle, ') sur le poste ',
v_nPoste, ' (', v_nomPoste, '). Le logiciel installé
est ',
v_nLog, ' (', v_nomLog, ') le ',
DATE_FORMAT(v_dateIns, '%d/%m/%Y')) AS Resultat;
END //
DELIMITER ;
CALL afficher_derniere_installation();
2. Fonction comptant les postes d’un type spécifique dans une salle donnée
Cette fonction prend en paramètre le numéro d’une salle et le type d’un poste et retourne le
nombre de postes correspondants.
DELIMITER //
CREATE FUNCTION compter_postes_par_type(p_nSalle VARCHAR(7),
p_typePoste VARCHAR(9))
RETURNS INT
BEGIN
DECLARE v_count INT;
DECLARE v_salle_exists INT DEFAULT 0;
DECLARE v_type_exists INT DEFAULT 0;
-- Vérification de l'existence de la salle
SELECT COUNT(*) INTO v_salle_exists
FROM Salle
WHERE nSalle = p_nSalle;
IF v_salle_exists = 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Salle inconnue';
END IF;
-- Vérification de l'existence du type de poste
SELECT COUNT(*) INTO v_type_exists
FROM Types
WHERE typeLP = p_typePoste;
IF v_type_exists = 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Type de poste inconnu';
END IF;
-- Comptage des postes du type spécifié dans la salle donnée
SELECT COUNT(*) INTO v_count
FROM Poste
WHERE nSalle = p_nSalle AND typePoste = p_typePoste;
IF v_count = 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Aucun poste trouvé';
END IF;
RETURN v_count;
END //
DELIMITER ;
-- Configuration des variables de session et appel de la fonction
SET @vs_nSalle = 'S01';
SET @vs_typePoste = 'TX';
SELECT CONCAT('Il y a ', compter_postes_par_type(@vs_nSalle,
@vs_typePoste),
' postes de type ', @vs_typePoste, ' dans la salle ',
@vs_nSalle) AS Resultat;
3. Transformation de la fonction en procédure
Cette procédure retourne le nombre de postes et d’installations de logiciels correspondants
pour un type de poste dans une salle donnée.
DELIMITER //
CREATE PROCEDURE compter_postes_et_installations(IN p_nSalle
VARCHAR(7), IN p_typePoste VARCHAR(9))
BEGIN
DECLARE v_salle_exists INT DEFAULT 0;
DECLARE v_type_exists INT DEFAULT 0;
-- Vérification de l'existence de la salle
SELECT COUNT(*) INTO v_salle_exists
FROM Salle
WHERE nSalle = p_nSalle;
IF v_salle_exists = 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Salle inconnue';
END IF;
-- Vérification de l'existence du type de poste
SELECT COUNT(*) INTO v_type_exists
FROM Types
WHERE typeLP = p_typePoste;
IF v_type_exists = 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Type de poste inconnu';
END IF;
-- Comptage des postes du type spécifié dans la salle donnée
SELECT COUNT(*) INTO @vs_nbPoste
FROM Poste
WHERE nSalle = p_nSalle AND typePoste = p_typePoste;
-- Comptage des installations de logiciels sur ces postes
SELECT COUNT(*) INTO @vs_nbInstall
FROM Installer I
JOIN Poste P ON [Link] = [Link]
WHERE [Link] = p_nSalle AND [Link] = p_typePoste;
-- Affichage des résultats
SELECT CONCAT('Il y a ', @vs_nbPoste, ' postes de type ',
p_typePoste, ' dans la salle ',
p_nSalle, ' avec un total de ', @vs_nbInstall, '
installations de logiciels.') AS Resultat;
END //
DELIMITER ;
-- Appel de la procédure
SET @vs_nSalle = 'S01';
SET @vs_typePoste = 'TX';
CALL compter_postes_et_installations(@vs_nSalle, @vs_typePoste);
Partie II: Curseurs, Transactions, Exceptions, Déclencheurs
1. Curseur pour calculer le délai d’installation
Création de la table de traçage:
CREATE DATABASE IF NOT EXISTS test;
USE test;
CREATE TABLE IF NOT EXISTS Trace (
message VARCHAR(80)
);
Procédure utilisant un curseur pour calculer le délai entre l’achat et l’installation des
logiciels:
DELIMITER //
CREATE PROCEDURE calculTemps()
BEGIN
DECLARE v_nPoste VARCHAR(7);
DECLARE v_nLog VARCHAR(5);
DECLARE v_dateIns DATE;
DECLARE v_dateAch DATE;
DECLARE v_delai INT;
DECLARE v_delai_decimal DECIMAL(10,1);
DECLARE v_hours INT;
DECLARE v_minutes INT;
DECLARE v_seconds INT;
DECLARE v_done INT DEFAULT FALSE;
-- Définition du curseur
DECLARE cur CURSOR FOR
SELECT [Link], [Link], [Link], [Link]
FROM Installer I
JOIN Logiciel L ON [Link] = [Link];
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
-- Vidage de la table de traçage
TRUNCATE TABLE [Link];
-- Ouverture du curseur
OPEN cur;
read_loop: LOOP
FETCH cur INTO v_nPoste, v_nLog, v_dateIns, v_dateAch;
IF v_done THEN
LEAVE read_loop;
END IF;
-- Vérification des incohérences
IF v_dateIns IS NULL THEN
INSERT INTO [Link] VALUES (CONCAT('Installation ',
v_nPoste, '-', v_nLog, ': date d''installation inconnue'));
ITERATE read_loop;
END IF;
IF v_dateAch IS NULL THEN
INSERT INTO [Link] VALUES (CONCAT('Installation ',
v_nPoste, '-', v_nLog, ': date d''achat inconnue'));
ITERATE read_loop;
END IF;
IF v_dateIns < v_dateAch THEN
INSERT INTO [Link] VALUES (CONCAT('Installation ',
v_nPoste, '-', v_nLog, ': date d''installation antérieure à la date
d''achat'));
ITERATE read_loop;
END IF;
-- Calcul du délai en jours
SET v_delai = DATEDIFF(v_dateIns, v_dateAch);
-- Mise à jour de la colonne délai dans la table Installer
UPDATE Installer SET delai = v_delai WHERE nPoste = v_nPoste
AND nLog = v_nLog;
-- Enregistrement du nombre entier de jours
INSERT INTO [Link] VALUES (CONCAT('Installation ',
v_nPoste, '-', v_nLog, ': ', v_delai, ' jours'));
-- Calcul des jours décimaux (ajout d'une composante
temporelle pour l'exemple)
SET v_delai_decimal = v_delai + 0.5; -- Exemple, en réalité on
calculerait en fonction du temps
SET v_hours = FLOOR((v_delai_decimal - FLOOR(v_delai_decimal))
* 24);
SET v_minutes = FLOOR(((v_delai_decimal -
FLOOR(v_delai_decimal)) * 24 - v_hours) * 60);
SET v_seconds = FLOOR((((v_delai_decimal -
FLOOR(v_delai_decimal)) * 24 - v_hours) * 60 - v_minutes) * 60);
-- Enregistrement des jours décimaux au format TIME étendu
INSERT INTO [Link] VALUES (
CONCAT('Installation ', v_nPoste, '-', v_nLog, ': ',
FLOOR(v_delai_decimal), 'j ',
LPAD(v_hours, 2, '0'), ':',
LPAD(v_minutes, 2, '0'), ':',
LPAD(v_seconds, 2, '0'))
);
END LOOP;
-- Fermeture du curseur
CLOSE cur;
-- Affichage de la table de traçage
SELECT * FROM [Link];
END //
DELIMITER ;
-- Appel de la procédure
CALL calculTemps();
2. Transaction pour l’installation groupée de logiciels
Cette procédure permet d’effectuer une installation groupée d’un nouveau logiciel sur tous
les postes d’un même segment.
DELIMITER //
CREATE PROCEDURE installLogSeg(
IN p_nLog VARCHAR(5),
IN p_nomLog VARCHAR(20),
IN p_version VARCHAR(7),
IN p_typeLog VARCHAR(9),
IN p_prix DECIMAL(6,2),
IN p_indSeg VARCHAR(5)
)
BEGIN
DECLARE v_dateAch DATE;
DECLARE v_numIns INT;
DECLARE v_done INT DEFAULT FALSE;
DECLARE v_nPoste VARCHAR(7);
-- Définition du curseur pour sélectionner les postes du segment
du type correspondant
DECLARE curPostes CURSOR FOR
SELECT [Link]
FROM Poste P
JOIN Salle S ON [Link] = [Link]
WHERE [Link] = p_indSeg AND [Link] = p_typeLog;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
-- Date d'achat = aujourd'hui
SET v_dateAch = CURDATE();
-- Vider la table de traçage
TRUNCATE TABLE [Link];
-- Début de la transaction
START TRANSACTION;
-- Insertion du nouveau logiciel
INSERT INTO Logiciel (nLog, nomLog, dateAch, version, typeLog,
prix)
VALUES (p_nLog, p_nomLog, v_dateAch, p_version, p_typeLog,
p_prix);
INSERT INTO [Link] VALUES (CONCAT('Insertion logiciel: ',
p_nLog, ' - ', p_nomLog));
-- Récupération du plus grand numIns
SELECT IFNULL(MAX(numIns), 0) + 1 INTO v_numIns FROM Installer;
-- Ouverture du curseur
OPEN curPostes;
read_loop: LOOP
FETCH curPostes INTO v_nPoste;
IF v_done THEN
LEAVE read_loop;
END IF;
-- Insertion dans la table Installer
INSERT INTO Installer (nPoste, nLog, numIns, dateIns)
VALUES (v_nPoste, p_nLog, v_numIns, CURDATE());
-- Mise à jour du délai (0 car achat et installation le même
jour)
UPDATE Installer SET delai = 0 WHERE nPoste = v_nPoste AND
nLog = p_nLog;
INSERT INTO [Link] VALUES (CONCAT('Installation sur poste:
', v_nPoste));
SET v_numIns = v_numIns + 1;
END LOOP;
-- Fermeture du curseur
CLOSE curPostes;
-- Validation de la transaction (commentée pour les tests)
-- COMMIT;
-- Affichage de la table de traçage
SELECT * FROM [Link];
END //
DELIMITER ;
-- Appel de la procédure
CALL installLogSeg('log9', 'Oracle 10g', '10.0', 'UNIX', 6000, 'S1');
3. Gestion des exceptions dans la procédure d’installation groupée
DELIMITER //
CREATE PROCEDURE installLogSeg(
IN p_nLog VARCHAR(5),
IN p_nomLog VARCHAR(20),
IN p_version VARCHAR(7),
IN p_typeLog VARCHAR(9),
IN p_prix DECIMAL(6,2),
IN p_indSeg VARCHAR(5)
)
BEGIN
DECLARE v_dateAch DATE;
DECLARE v_numIns INT;
DECLARE v_done INT DEFAULT FALSE;
DECLARE v_nPoste VARCHAR(7);
DECLARE v_segment_exists INT DEFAULT 0;
DECLARE v_type_exists INT DEFAULT 0;
DECLARE v_installations INT DEFAULT 0;
-- Gestionnaires d'exceptions
DECLARE CONTINUE HANDLER FOR 1062 -- Duplicate entry
BEGIN
INSERT INTO [Link] VALUES (CONCAT('ERREUR: Logiciel ',
p_nLog, ' déjà existant'));
ROLLBACK;
END;
DECLARE CONTINUE HANDLER FOR 1452 -- Cannot add or update a child
row
BEGIN
INSERT INTO [Link] VALUES (CONCAT('ERREUR: Type de
logiciel ', p_typeLog, ' inconnu'));
ROLLBACK;
END;
-- Définition du curseur pour sélectionner les postes du segment
du type correspondant
DECLARE curPostes CURSOR FOR
SELECT [Link]
FROM Poste P
JOIN Salle S ON [Link] = [Link]
WHERE [Link] = p_indSeg AND [Link] = p_typeLog;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
-- Vider la table de traçage
TRUNCATE TABLE [Link];
-- Vérification de l'existence du segment
SELECT COUNT(*) INTO v_segment_exists FROM Segment WHERE indSeg =
p_indSeg;
IF v_segment_exists = 0 THEN
INSERT INTO [Link] VALUES (CONCAT('ERREUR: Segment ',
p_indSeg, ' inconnu'));
SELECT * FROM [Link];
SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'Segment inconnu';
LEAVE installLogSeg;
END IF;
-- Vérification de l'existence du type de logiciel
SELECT COUNT(*) INTO v_type_exists FROM Types WHERE typeLP =
p_typeLog;
IF v_type_exists = 0 THEN
INSERT INTO [Link] VALUES (CONCAT('ERREUR: Type de
logiciel ', p_typeLog, ' inconnu'));
SELECT * FROM [Link];
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Type de logiciel
inconnu';
LEAVE installLogSeg;
END IF;
-- Date d'achat = aujourd'hui
SET v_dateAch = CURDATE();
-- Début de la transaction
START TRANSACTION;
-- Insertion du nouveau logiciel
INSERT INTO Logiciel (nLog, nomLog, dateAch, version, typeLog,
prix)
VALUES (p_nLog, p_nomLog, v_dateAch, p_version, p_typeLog,
p_prix);
INSERT INTO [Link] VALUES (CONCAT('Insertion logiciel: ',
p_nLog, ' - ', p_nomLog));
-- Récupération du plus grand numIns
SELECT IFNULL(MAX(numIns), 0) + 1 INTO v_numIns FROM Installer;
-- Ouverture du curseur
OPEN curPostes;
read_loop: LOOP
FETCH curPostes INTO v_nPoste;
IF v_done THEN
LEAVE read_loop;
END IF;
-- Insertion dans la table Installer
INSERT INTO Installer (nPoste, nLog, numIns, dateIns)
VALUES (v_nPoste, p_nLog, v_numIns, CURDATE());
-- Mise à jour du délai (0 car achat et installation le même
jour)
UPDATE Installer SET delai = 0 WHERE nPoste = v_nPoste AND
nLog = p_nLog;
INSERT INTO [Link] VALUES (CONCAT('Installation sur poste:
', v_nPoste));
SET v_numIns = v_numIns + 1;
SET v_installations = v_installations + 1;
END LOOP;
-- Fermeture du curseur
CLOSE curPostes;
-- Si aucune installation n'a été réalisée
IF v_installations = 0 THEN
INSERT INTO [Link] VALUES ('ERREUR: Aucune installation
possible, pas de poste de ce type');
ROLLBACK;
SELECT * FROM [Link];
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Pas
d''installation possible';
LEAVE installLogSeg;
END IF;
-- Validation de la transaction
COMMIT;
-- Affichage de la table de traçage
SELECT * FROM [Link];
END //
DELIMITER ;
-- Appel de la procédure avec des paramètres valides
CALL installLogSeg('log9', 'Oracle 10g', '10.0', 'UNIX', 6000, 'S1');
4. Déclencheurs (Triggers)
Déclencheurs pour la mise à jour des colonnes nbLog et nbInstall
-- Ajout des colonnes nécessaires si elles n'existent pas déjà
ALTER TABLE Poste ADD COLUMN IF NOT EXISTS nbLog INT DEFAULT 0;
ALTER TABLE Logiciel ADD COLUMN IF NOT EXISTS nbInstall INT DEFAULT 0;
ALTER TABLE Salle ADD COLUMN IF NOT EXISTS nbPoste INT DEFAULT 0;
ALTER TABLE Segment ADD COLUMN IF NOT EXISTS nbPoste INT DEFAULT 0;
-- Déclencheur après suppression d'une installation
DELIMITER //
CREATE TRIGGER Trig_AD_Installer AFTER DELETE ON Installer
FOR EACH ROW
BEGIN
-- Mise à jour du nombre de logiciels installés sur le poste
UPDATE Poste SET nbLog = nbLog - 1 WHERE nPoste = [Link];
-- Mise à jour du nombre d'installations du logiciel
UPDATE Logiciel SET nbInstall = nbInstall - 1 WHERE nLog =
[Link];
END //
DELIMITER ;
-- Déclencheur après insertion d'une installation
DELIMITER //
CREATE TRIGGER Trig_AI_Installer AFTER INSERT ON Installer
FOR EACH ROW
BEGIN
-- Mise à jour du nombre de logiciels installés sur le poste
UPDATE Poste SET nbLog = nbLog + 1 WHERE nPoste = [Link];
-- Mise à jour du nombre d'installations du logiciel
UPDATE Logiciel SET nbInstall = nbInstall + 1 WHERE nLog =
[Link];
END //
DELIMITER ;
Déclencheurs pour la mise à jour de la colonne nbPoste dans Salle
-- Déclencheur après insertion d'un poste
DELIMITER //
CREATE TRIGGER Trig_AI_Poste AFTER INSERT ON Poste
FOR EACH ROW
BEGIN
-- Mise à jour du nombre de postes dans la salle
UPDATE Salle SET nbPoste = nbPoste + 1 WHERE nSalle = [Link];
END //
DELIMITER ;
-- Déclencheur après suppression d'un poste
DELIMITER //
CREATE TRIGGER Trig_AD_Poste AFTER DELETE ON Poste
FOR EACH ROW
BEGIN
-- Mise à jour du nombre de postes dans la salle
UPDATE Salle SET nbPoste = nbPoste - 1 WHERE nSalle = [Link];
END //
DELIMITER ;
Déclencheur pour la mise à jour de la colonne nbPoste dans Segment
-- Déclencheur après mise à jour de la colonne nbPoste dans Salle
DELIMITER //
CREATE TRIGGER Trig_AU_Salle AFTER UPDATE ON Salle
FOR EACH ROW
BEGIN
IF [Link] <> [Link] THEN
-- Mise à jour du nombre de postes dans le segment
UPDATE Segment SET nbPoste = (
SELECT SUM(nbPoste)
FROM Salle
WHERE indSeg = [Link]
)
WHERE indSeg = [Link];
END IF;
END //
DELIMITER ;
Déclencheur pour vérifier les contraintes avant insertion dans Installer
-- Déclencheur avant insertion dans Installer
DELIMITER //
CREATE TRIGGER Trig_BI_Installer BEFORE INSERT ON Installer
FOR EACH ROW
BEGIN
DECLARE v_typePoste VARCHAR(9);
DECLARE v_typeLog VARCHAR(9);
DECLARE v_dateAch DATE;
-- Récupération du type de poste
SELECT typePoste INTO v_typePoste FROM Poste WHERE nPoste =
[Link];
-- Récupération du type et de la date d'achat du logiciel
SELECT typeLog, dateAch INTO v_typeLog, v_dateAch FROM Logiciel
WHERE nLog = [Link];
-- Vérification que le type du logiciel correspond au type du
poste
IF v_typePoste <> v_typeLog THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Le type du logiciel ne correspond pas au
type du poste';
END IF;
-- Vérification que la date d'installation est soit nulle soit
postérieure à la date d'achat
IF [Link] IS NOT NULL AND v_dateAch IS NOT NULL AND
[Link] < v_dateAch THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'La date d''installation doit être
postérieure à la date d''achat';
END IF;
END //
DELIMITER ;