/*
A
Nom et prénom : Wamster alexis
Groupe : 2
Salle TP : 235
Nom du voisin de gauche : DIMITRIJEVIC Hugo
Nom du voisin de droite : Schnur Edouard
*/

/*Exercice 1*/

Create or replace procedure update_COMM
IS 
    cursor comm_emp IS Select EMPNO,COMM from EMP;
    e_comm EMP.COMM%TYPE;
    e_empno EMP.EMPNO%TYPE;
    comm_min EMP_SEUIL.COMM_MIN%TYPE;
Begin
    for v_comm in comm_emp
    loop
        e_comm := v_comm(comm_emp.COMM);
        e_empno := v_comm(comm_emp.EMPNO);
        select COMM_MIN into comm_min from EMP_SEUIL S, EMP E where E.EMPNO = e_empno AND S.JOB = E.JOB;
        if (e_comm < comm_min) then
            update EMP set COMM=comm_min where EMPNO=e_empno;
        else if(e_comm = null) then
            update EMP set COMM=comm_min where EMPNO=e_empno;
        end if;
    end loop;
end;

/*Exercice 2*/

create or replace procedure insert_emp(empno EMP.EMPNO%TYPE, ename EMP.ENAME%TYPE, job EMP.JOB%TYPE, mgr EMP.MGR%TYPE, hiredate EMP.HIREDATE%TYPE, sal EMP.SAL%TYPE, comm EMP.COMM%TYPE, deptno EMP.DEPTNO%TYPE)
is
    comm_min EMP_SEUIL.COMM_MIN%TYPE;
    nb_emp SUIVI_EMP.Nombre_Emp%TYPE;
begin
    select COMM_MIN into comm_min
    from EMP_SEUIL S, EMP E
    where E.EMPNO = empno AND S.JOB = E.JOB;

    if (comm < comm_min) then
        comm := comm_min;
    else if(comm = null) then
        comm := comm_min;
    end if;
  
    insert into EMP values (empno, ename, job, mgr, hiredate, sal, comm, deptno);

    select Nombre_Emp into nb_emp from SUIVI_EMP where DEPTNO=deptno;
    nb_emp := nb_emp+1;
    update SUIVI_EMP set Nombre_Emp=nb_emp where DEPTNO=deptno;

    exception
    WHEN OTHERS THEN
        DBMS.OUTPUT.PUT_LINE('departemen inexisatant');

end insert_emp;

/*Exercice 3*/

create or replace function salaire_moyen(job EMP.JOB%TYPE)
    RETURN int
    is
        moyenne int;
    begin
        select AVG(SAL) into moyenne
        from EMP
        where JOB=job;
    return moyenne
end salaire_moyen;

/*Test*/
select distinct JOB, salaire_moyen(JOB) from emp;


/*Exercice 4*/

Create table EMP_HIS (
    EMPID EMP.EMPNO%TYPE PRIMARY KEY,
    ENAME EMP.JOB%TYPE,
    JOB EMP.MGR%TYPE,
    HIREDATE EMP.HIREDATE%TYPE,
    DEPTNO EMP.DEPTNO%TYPE,
    ENDDATE DEPT.DNAME%TYPE
);

CREATE OR REPLACE TRIGGER historique
AFTER  DELETE
ON EMP
FOR EACH ROW
DECLARE
    dname DEPT.DNAME%TYPE;
BEGIN
    select DNAME into dname from DEPT where DEPTNO=DEPTNO:OLD;
    insert into EMP_HIS values(EMPNO:OLD, JOB:OLD, MGR:OLD, HIREDATE:OLD, DEPTNO:OLD, dname);
END historique;

/*J'ai pas eu le temps de corriger les erreurs et de tester correctement*/