129 lines
2.6 KiB
Plaintext
129 lines
2.6 KiB
Plaintext
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;
|
|
/ |