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
| CREATE PROCEDURE PSARCHIVAGE AS
DECLARE
dateArchivage DATE;
Unit_Archivage T1_LIGNE.UNIT_ARCHIVAGE%TYPE;
Long_Archivage T1_LIGNE.LONG_ARCHIVAGE%TYPE;
BEGIN
-- si T1_PRODUIT n'existe pas -> NO_DATA_FOUND -> création de la table
BEGIN
SELECT 1 FROM dual WHERE EXISTS (SELECT 1 FROM user_tables WHERE table_name='T1_PRODUIT');
EXCEPTION WHEN NO_DATA_FOUND THEN
EXECUTE IMMEDIATE ('CREATE TABLE t1_produit (id_produit INTEGER NOT NULL PRIMARY KEY, date_last_modif DATE NOT NULL, idecli VARCHAR2(40) NOT NULL, num_ligne INTEGER NOT NULL, reference VARCHAR2(30) NOT NULL, lot VARCHAR2(30), odf VARCHAR2(30))');
END;
-- si T1_DEFPRCD n'existe pas -> NO_DATA_FOUND -> création de la table
BEGIN
SELECT 1 FROM dual WHERE EXISTS (SELECT 1 FROM user_tables WHERE table_name='T1_DEFPRCD');
EXCEPTION WHEN NO_DATA_FOUND THEN
EXECUTE IMMEDIATE ('CREATE TABLE t1_defprcd (id_defprcd INTEGER NOT NULL PRIMARY KEY, num_ligne INTEGER NOT NULL, num_station INTEGER NOT NULL, date_rec NOT NULL, code_prcd INTEGER NOT NULL)');
END;
-- si T1_MODMARCH n'existe pas -> NO_DATA_FOUND -> création de la table
BEGIN
SELECT 1 FROM dual WHERE EXISTS (SELECT 1 FROM user_tables WHERE table_name='T1_MODMARCH');
EXCEPTION WHEN NO_DATA_FOUND THEN
EXECUTE IMMEDIATE ('CREATE TABLE t1_modmarch (id_modmarch INTEGER NOT NULL PRIMARY KEY, num_ligne INTEGER NOT NULL, num_station INTEGER NOT NULL, date_rec NOT NULL, code_mode INTEGER NOT NULL)');
END;
SELECT T1_LIGNE.UNIT_ARCHIVAGE,T1_LIGNE.LONG_ARCHIVAGE * (-1) INTO Unit_Archivage,Long_Archivage from T1_LIGNE;
IF Unit_Archivage = 2
dateArchivage = ADD_MONTHS(SYSDATE,Long_Archivage);
ELSE
-- j'aoute 7 fois le nombre de semaine pour ajouter le nombre de jour correspondant
dateArchivage = SYSDATE + (Long_Archivage*7);
END IF;
INSERT INTO Base_Archive.t1_modmarch
SELECT num_ligne, num_station, date_rec, code_mode
FROM t1_modmarch
WHERE date_rec < dateArchivage;
INSERT INTO Base_Archive.t1_defprcd
SELECT num_ligne, num_station, date_rec, code_prcd
FROM t1_defprcd
WHERE date_rec < dateArchivage;
INSERT INTO Base_Archive.t1_produit
SELECT date_last_modif, idecli, num_ligne, reference, lot, odf
FROM t1_produit
WHERE date_last_modif < dateArchivage;
DELETE FROM t1_produit WHERE date_last_modif < dateArchivage;
DELETE FROM t1_modmarch WHERE date_rec < dateArchivage;
DELETE FROM t1_defprcd WHERE date_rec < dateArchivage;
COMMIT;
END;
/ |
