1/
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE CHANTIERS (
Reference VARCHAR2(10) PRIMARY KEY,
Lieu VARCHAR2(50),
DateDebut DATE,
Duree NUMBER,
nbrOuvrierAffectes NUMBER
)';
EXECUTE IMMEDIATE 'CREATE TABLE OUVRIERS (
Matricule VARCHAR2(10) PRIMARY KEY,
Nom VARCHAR2(30),
Prenom VARCHAR2(30),
PrixJr NUMBER,
Ref VARCHAR2(10),
FOREIGN KEY (Ref) REFERENCES CHANTIERS(Reference)
)';
END;
2/
CREATE OR REPLACE PROCEDURE proc_insertion_CHANTIER (
p_Reference IN VARCHAR2,
p_Lieu IN VARCHAR2,
p_DateDebut IN DATE,
p_Duree IN NUMBER
IS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM CHANTIERS WHERE Reference = p_Reference;
IF v_count = 0 THEN
INSERT INTO CHANTIERS (Reference, Lieu, DateDebut, Duree, nbrOuvrierAffectes)
VALUES (p_Reference, p_Lieu, p_DateDebut, p_Duree, NULL);
ELSE
DBMS_OUTPUT.PUT_LINE('Erreur : Référence déjà existante.');
END IF;
END;
3/
BEGIN
proc_insertion_CHANTIER('CH001', 'Tunis', TO_DATE('2025-05-01', 'YYYY-MM-DD'), 30);
proc_insertion_CHANTIER('CH002', 'Sfax', TO_DATE('2025-05-10', 'YYYY-MM-DD'), 45);
proc_insertion_CHANTIER('CH003', 'Bizerte', TO_DATE('2025-06-01', 'YYYY-MM-DD'), 20);
END;
4/
CREATE OR REPLACE PROCEDURE proc_insertion_OUVRIER (
p_Matricule IN VARCHAR2,
p_Nom IN VARCHAR2,
p_Prenom IN VARCHAR2,
p_PrixJr IN NUMBER,
p_Ref IN VARCHAR2
IS
v_countMat NUMBER;
v_countRef NUMBER;
BEGIN
SELECT COUNT(*) INTO v_countMat FROM OUVRIERS WHERE Matricule = p_Matricule;
SELECT COUNT(*) INTO v_countRef FROM CHANTIERS WHERE Reference = p_Ref;
IF v_countMat > 0 THEN
DBMS_OUTPUT.PUT_LINE('Erreur : Matricule déjà existant.');
ELSIF v_countRef = 0 THEN
DBMS_OUTPUT.PUT_LINE('Erreur : Chantier inexistant.');
ELSIF p_PrixJr < 10 THEN
DBMS_OUTPUT.PUT_LINE('Erreur : Prix/jour < 10 dinars.');
ELSE
INSERT INTO OUVRIERS (Matricule, Nom, Prenom, PrixJr, Ref)
VALUES (p_Matricule, p_Nom, p_Prenom, p_PrixJr, p_Ref);
END IF;
END;
5/
BEGIN
proc_insertion_OUVRIER('O001', 'Ali', 'Ben Salah', 12, 'CH001');
proc_insertion_OUVRIER('O002', 'Sami', 'Trabelsi', 15, 'CH001');
proc_insertion_OUVRIER('O003', 'Leila', 'Mansour', 11, 'CH002');
END;
6/
CREATE OR REPLACE PROCEDURE proc_insertion_OUVRIER (
p_Matricule IN VARCHAR2,
p_Nom IN VARCHAR2,
p_Prenom IN VARCHAR2,
p_PrixJr IN NUMBER,
p_Ref IN VARCHAR2,
p_TotalOuvriers OUT NUMBER
)
IS
v_countMat NUMBER;
v_countRef NUMBER;
BEGIN
SELECT COUNT(*) INTO v_countMat FROM OUVRIERS WHERE Matricule = p_Matricule;
SELECT COUNT(*) INTO v_countRef FROM CHANTIERS WHERE Reference = p_Ref;
IF v_countMat > 0 THEN
DBMS_OUTPUT.PUT_LINE('Erreur : Matricule déjà existant.');
ELSIF v_countRef = 0 THEN
DBMS_OUTPUT.PUT_LINE('Erreur : Chantier inexistant.');
ELSIF p_PrixJr < 10 THEN
DBMS_OUTPUT.PUT_LINE('Erreur : Prix/jour < 10 dinars.');
ELSE
INSERT INTO OUVRIERS (Matricule, Nom, Prenom, PrixJr, Ref)
VALUES (p_Matricule, p_Nom, p_Prenom, p_PrixJr, p_Ref);
END IF;
SELECT COUNT(*) INTO p_TotalOuvriers FROM OUVRIERS;
END;
7/
CREATE OR REPLACE FUNCTION fn_fin_chantier (
p_Reference IN VARCHAR2
) RETURN DATE
IS
v_DateFin DATE;
BEGIN
SELECT DateDebut + Duree INTO v_DateFin FROM CHANTIERS WHERE Reference = p_Reference;
RETURN v_DateFin;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;
8/
CREATE OR REPLACE PROCEDURE proc_liste_ouvriers
IS
BEGIN
FOR r IN (
SELECT [Link], [Link], [Link], [Link], [Link], [Link]
FROM CHANTIERS c
JOIN OUVRIERS o ON [Link] = [Link]
ORDER BY [Link]
) LOOP
DBMS_OUTPUT.PUT_LINE('Chantier: ' || [Link] || ' - ' || [Link]);
DBMS_OUTPUT.PUT_LINE(' Ouvrier: ' || [Link] || ' - ' || [Link] || ' ' || [Link] || ' - PrixJr:
' || [Link]);
END LOOP;
END;