100 lines
2.5 KiB
SQL
100 lines
2.5 KiB
SQL
/*
|
|
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*/ |