devoir.py

Created by joelkouakou2080
Created on March 22, 2024
2 KB
exo1 Triggers
CREATE OR REPLACE TRIGGER check_effectif_max
BEFORE INSERT ON INSCRIPTION
FOR EACH ROW
DECLARE
    v_effectif NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_effectif
    FROM INSCRIPTION
    WHERE codMat = :NEW.codMat;
    
    IF v_effectif >= (SELECT effectiMax FROM MATIERE WHERE codMat = :NEW.codMat) THEN
        RAISE_APPLICATION_ERROR(-20001, 'Effectif maximal atteint pour cette matière.');
    END IF;
END;
/

CREATE OR REPLACE TRIGGER check_examen_creation
BEFORE INSERT ON EXAMEN
FOR EACH ROW
DECLARE
    v_effectif NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_effectif
    FROM INSCRIPTION
    WHERE codMat = :NEW.codMat;
    
    IF v_effectif = 0 THEN
        RAISE_APPLICATION_ERROR(-20002, 'Impossible de créer un examen pour une matière sans élèves inscrits.');
    END IF;
END;
/

-----------------------------
exo2 Transactions
CREATE OR REPLACE PROCEDURE annuler_inscriptions_etudiant(
    p_matEtud IN ETUDIANT.matEtud%TYPE
)
IS
BEGIN
    DELETE FROM INSCRIPTION
    WHERE matEtud = p_matEtud;
    
    DELETE FROM RESULTAT
    WHERE matEtud = p_matEtud;
    
    DELETE FROM ETUDIANT
    WHERE matEtud = p_matEtud;
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END annuler_inscriptions_etudiant;
/

-----------------------------
exo3 Curseurs
CREATE OR REPLACE PROCEDURE liste_etudiants_avec_prerequis IS
    CURSOR etudiant_cur IS
        SELECT e.matEtud, e.nom, e.prenom
        FROM ETUDIANT e
        INNER JOIN RESULTAT r ON e.matEtud = r.matEtud
        INNER JOIN PREREQUIS p ON r.codMat = p.codMat
        WHERE r.note >= p.noteMin;
    v_matEtud ETUDIANT.matEtud%TYPE;
    v_nom ETUDIANT.nom%TYPE;
    v_prenom ETUDIANT.prenom%TYPE;
BEGIN
    OPEN etudiant_cur;
    LOOP
        FETCH etudiant_cur INTO v_matEtud, v_nom, v_prenom;
        EXIT WHEN etudiant_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Matricule: ' || v_matEtud || ', Nom: ' || v_nom || ', Prénom: ' || v_prenom);
    END LOOP;
    CLOSE etudiant_cur;
END liste_etudiants_avec_prerequis;
/