Exercice 1 :


1. Fonction pour retourner le salaire moyen d'un JOB


CREATE OR REPLACE FUNCTION get_salaire_moyen(p_job VARCHAR2)
RETURN NUMBER AS
    salaire_moyen NUMBER;
BEGIN
    SELECT AVG(SAL)
    INTO salaire_moyen
    FROM EMP
    WHERE JOB = p_job;

    IF salaire_moyen IS NULL THEN
        RAISE_APPLICATION_ERROR(-20001, 'Le JOB spécifié n''existe pas.');
    END IF;

    RETURN salaire_moyen;
END;
/


2. Afficher la liste des JOBs et leur salaire moyen par ordre décroissant


SELECT 
    JOB,
    AVG(SAL) AS Salaire_Moyen
FROM 
    EMP
GROUP BY 
    JOB
ORDER BY 
    Salaire_Moyen DESC;




Exercice 2 :
Trigger pour mettre à jour Nombre_Emp dans Suivi_Emp


CREATE OR REPLACE TRIGGER trg_update_nombre_emp
AFTER INSERT OR DELETE OR UPDATE OF DEPTNO ON EMP
FOR EACH ROW
DECLARE
    v_nombre_emp NUMBER;
BEGIN
    -- Calculer le nombre d'employés pour le département affecté
    SELECT COUNT(*)
    INTO v_nombre_emp
    FROM EMP
    WHERE DEPTNO = :NEW.DEPTNO;

    -- Mettre à jour la table Suivi_Emp
    UPDATE SUIVI_EMP
    SET Nombre_Emp = v_nombre_emp
    WHERE DEPTNO = :NEW.DEPTNO;

    -- Si suppression ou transfert, mettre à jour l'ancien département
    IF UPDATING AND :OLD.DEPTNO IS NOT NULL AND :OLD.DEPTNO != :NEW.DEPTNO THEN
        SELECT COUNT(*)
        INTO v_nombre_emp
        FROM EMP
        WHERE DEPTNO = :OLD.DEPTNO;

        UPDATE SUIVI_EMP
        SET Nombre_Emp = v_nombre_emp
        WHERE DEPTNO = :OLD.DEPTNO;
    END IF;
END;
/




Exercice 3 :
Script pour mettre à jour la colonne COMM dans EMP en fonction de COMM_MIN


BEGIN
    UPDATE EMP e
    SET COMM = (
        SELECT COMM_MIN
        FROM EMP_SEUIL es
        WHERE e.JOB = es.JOB
    )
    WHERE EXISTS (
        SELECT 1
        FROM EMP_SEUIL es
        WHERE e.JOB = es.JOB
          AND (e.COMM IS NULL OR e.COMM < es.COMM_MIN)
    );
END;
/




Exercice 4 : 
Procédure sp_update_commission pour mettre à jour COMM


CREATE OR REPLACE PROCEDURE sp_update_commission(p_job VARCHAR2, p_com NUMBER) AS
BEGIN
    UPDATE EMP
    SET COMM = p_com
    WHERE JOB = p_job
      AND (COMM IS NULL OR COMM < p_com);

    -- Vérification : Si la commission mise à jour est inférieure à COMM_MIN
    UPDATE EMP e
    SET COMM = (
        SELECT COMM_MIN
        FROM EMP_SEUIL es
        WHERE e.JOB = es.JOB
          AND es.JOB = p_job
    )
    WHERE EXISTS (
        SELECT 1
        FROM EMP_SEUIL es
        WHERE e.JOB = es.JOB
          AND es.JOB = p_job
          AND p_com < es.COMM_MIN
    );
END;
/