create or replace PACKAGE BODY LECTURE is
PROCEDURE LISTE_EMPLOYE(LE_AFFECT IN NUMBER ,liste OUT liste_emp) is
BEGIN
SET TRANSACTION READ ONLY;
IF (LE_AFFECT=0) THEN
OPEN liste FOR
SELECT NUEMPL,NOMEMPL,HEBDO,AFFECT,NOMSERV
FROM EMPLOYE,SERVICE
WHERE EMPLOYE.affect=SERVICE.nuserv;
ELSE
OPEN liste FOR
SELECT NUEMPL,NOMEMPL,HEBDO,AFFECT,NOMSERV
FROM EMPLOYE,SERVICE
WHERE EMPLOYE.affect=SERVICE.nuserv AND EMPLOYE.affect=LE_AFFECT;
END IF;
COMMIT;
END;

PROCEDURE LISTE_SERVICE(liste OUT liste_serv) is
BEGIN
SET TRANSACTION READ ONLY;
OPEN liste FOR
SELECT NUSERV,NOMSERV,CHEF,NOMEMPL
FROM SERVICE,EMPLOYE
WHERE SERVICE.chef=EMPLOYE.nuempl;
COMMIT;
END;

PROCEDURE LISTE_PROJET(liste OUT liste_proj) is
BEGIN
SET TRANSACTION READ ONLY;
OPEN liste FOR
SELECT NUPROJ,NOMPROJ,RESP,NOMEMPL
FROM PROJET,EMPLOYE
WHERE PROJET.resp=EMPLOYE.nuempl;
COMMIT;
END;

PROCEDURE LISTE_TRAVAIL(LE_NUEMPL IN NUMBER, LE_NUPROJ IN NUMBER ,liste OUT liste_trav) is
BEGIN
SET TRANSACTION READ ONLY;
IF (LE_NUPROJ=0) THEN
IF (LE_NUEMPL=0) THEN
OPEN liste FOR
SELECT TRAVAIL.nuempl,EMPLOYE.nomempl , TRAVAIL.nuproj, PROJET.nomproj, TRAVAIL.duree
FROM TRAVAIL,EMPLOYE, PROJET
WHERE TRAVAIL.nuempl=EMPLOYE.nuempl AND TRAVAIL.nuproj=PROJET.nuproj;
ELSE
OPEN liste FOR
SELECT TRAVAIL.nuempl,EMPLOYE.nomempl , TRAVAIL.nuproj, PROJET.nomproj, TRAVAIL.duree
FROM TRAVAIL,EMPLOYE, PROJET
WHERE TRAVAIL.nuempl=EMPLOYE.nuempl AND TRAVAIL.nuproj=PROJET.nuproj
AND TRAVAIL.nuempl=LE_NUEMPL;
END IF;
ELSE
IF (LE_NUEMPL=0) THEN
OPEN liste FOR
SELECT TRAVAIL.nuempl,EMPLOYE.nomempl , TRAVAIL.nuproj, PROJET.nomproj, TRAVAIL.duree
FROM TRAVAIL,EMPLOYE, PROJET
WHERE TRAVAIL.nuempl=EMPLOYE.nuempl AND TRAVAIL.nuproj=PROJET.nuproj
AND TRAVAIL.nuproj=LE_NUPROJ;
ELSE
OPEN liste FOR
SELECT TRAVAIL.nuempl,EMPLOYE.nomempl , TRAVAIL.nuproj, PROJET.nomproj, TRAVAIL.duree
FROM TRAVAIL,EMPLOYE, PROJET
WHERE TRAVAIL.nuempl=EMPLOYE.nuempl AND TRAVAIL.nuproj=PROJET.nuproj
AND TRAVAIL.nuproj=LE_NUPROJ
AND TRAVAIL.nuempl=LE_NUEMPL;
END IF;
END IF;
COMMIT;
END;
END;