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 71 72 73 74 75 76 77 78 79 80 81 82
| .
create or replace
PROCEDURE BDEHISTO AS
v_Periodicite number;
v_table varchar2(50);
v_requete varchar2(2000);
v_verif_purge varchar2(100);
v_cpt number ;
cursor C1 is
select Latable,periodicite,ladate, ladate2
from histo;
C1r C1%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE ('DEBUT');
OPEN C1;
FETCH C1 INTO C1r;
-- on parcours la table Histo
while C1%FOUND loop
v_requete:='SELECT COUNT(*) FROM '||C1r.Latable||'@bdepkg';
EXECUTE IMMEDIATE v_requete into v_cpt ;
DBMS_OUTPUT.PUT_LINE( To_char( v_cpt ) || ' enregistrements') ;
DBMS_OUTPUT.PUT_LINE(C1r.Latable);
-- si =0 alors il vient d'avoir une purge donc on ne met rien a jour
if v_cpt>0 then
if C1r.latable='VLBL' then
v_requete := 'delete from vlbl where chronobl in ( select chronobl from vbl where to_date(dpre,''DD/MM/YY'') > TO_DATE(TO_CHAR(SYSDATE-7,''DD/MM/YY''),''DD/MM/YY''))';
EXECUTE IMMEDIATE v_requete;
commit;
v_requete := 'insert into vlbl select * from vlbl@bdepkg where chronobl in ( select chronobl from vbl where to_date(dpre,''DD/MM/YY'') > TO_DATE(TO_CHAR(SYSDATE-7,''DD/MM/YY''),''DD/MM/YY''))';
EXECUTE IMMEDIATE v_requete;
commit;
end if;
if C1r.ladate is null then
-- on supprime les anciennes saisies
v_requete := 'delete from '|| C1r.Latable ||' where to_date('|| C1r.ladate ||',''YYYY/MM/DD'') > TO_DATE(TO_CHAR(SYSDATE-7,''YYYY/MM/DD''),''YYYY/MM/DD'')';
EXECUTE IMMEDIATE v_requete;
commit;
-- on met à jour BDE Histo
v_requete := 'insert into '|| C1r.Latable ||' select * from '|| C1r.Latable ||'@bdepkg where to_date('|| C1r.ladate ||',''YYYY/MM/DD'') > TO_DATE(TO_CHAR(SYSDATE-7,''YYYY/MM/DD''),''YYYY/MM/DD'')';
EXECUTE IMMEDIATE v_requete;
commit;
elsif c1r.ladate2 is null then
-- on supprime les anciennes saisies
v_requete := 'delete from '|| C1r.Latable ||' where to_date('|| C1r.ladate2 ||',''DD/MM/YY'') < TO_DATE(TO_CHAR(SYSDATE-7,''DD/MM/YY''),''DD/MM/YY'')';
EXECUTE IMMEDIATE v_requete;
commit;
-- on met à jour BDE Histo
v_requete := 'insert into '|| C1r.Latable ||' select * from '|| C1r.Latable ||'@bdepkg where to_date('|| C1r.ladate2 ||',''DD/MM/YY'') < TO_DATE(TO_CHAR(SYSDATE-7,''DD/MM/YY''),''DD/MM/YY'')';
EXECUTE IMMEDIATE v_requete;
commit;
elsif c1r.ladate is null and c1r.ladate2 is null then
-- on supprime les anciennes saisies
v_requete := 'TRUNCATE TABLE '||c1r.latable;
EXECUTE IMMEDIATE v_requete;
commit;
-- on met à jour BDE Histo
v_requete := 'insert into '|| C1r.Latable ||' select * from '|| C1r.Latable ||'@bdepkg';
EXECUTE IMMEDIATE v_requete;
commit;
end if;
end if;
FETCH C1 into C1r;
end loop;
DBMS_OUTPUT.PUT_LINE ('FINI');
CLOSE C1;
END; |
Partager