1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70
| CREATE OR REPLACE PROCEDURE archivage
IS
DATE dates%ROWTYPE DEFAULT SYSDATE;
CURSOR cu_resident
IS
(SELECT *
FROM bateau b, occuper o
WHERE b.numb = o.numb);
CURSOR cu_stationnement
IS
(SELECT *
FROM stationner);
order_dym VARCHAR2 (300);
BEGIN
FOR s IN cu_stationnement
LOOP
IF ((s.duree_s + TO_CHAR (s.valeur, 'mm')) > TO_CHAR (DATE, 'mm'))
THEN
order_dym :=
'INSERT INTO Ar_Stationnement (Valeur, Bassin, Ponton, Num_Emp, NumB, Duree_S) VALUES ('
|| s.valeur
|| ','
|| s.bassin
|| ','
|| s.ponton
|| ','
|| s.num_emp
|| ','
|| s.numb
|| ','
|| s.duree_s
|| ')';
EXECUTE IMMEDIATE order_dym;
order_dym :=
'DELETE FROM Stationner WHERE Valeur='
|| s.valeur
|| ' and Bassin='
|| s.bassin
|| ' and Ponton='
|| s.ponton
|| 'and Num_Emp='
|| s.num_emp;
EXECUTE IMMEDIATE order_dym;
END IF;
END LOOP;
FOR r IN cu_resident
LOOP
IF ((r.duree_o + TO_CHAR (r.valeur, 'mm')) > TO_CHAR (DATE, 'mm'))
THEN
order_dym :=
'INSERT INTO Ar_Resident (NumB, Nom) VALUES (' || r.numb || ',' || r.nom
|| ')';
EXECUTE IMMEDIATE order_dym;
order_dym :=
'DELETE FROM Bateau WHERE ' || r.numb || '=NumbB and ' || r.nom || '=Nom';
EXECUTE IMMEDIATE order_dym;
END IF;
END LOOP;
END;
/ |
Partager