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
| create or replace procedure CORRECTION_DOSS is
nb number;
i number;
message varchar2(32767);
err_num varchar2(20);
err_msg varchar2(200);
-- Sélection
CURSOR dossier_a_maj is
select t.hos from table1 t, table2 t2
where t.hoscod = t2.hoscod and t2.codtype = 'XX'
and t2.anuuti = '0' and t2.inddfa = 'N'
and t.tde = 'SS'
and (t.pec = 'DM' OR t.pec = 'DP')
and t.psp ='49'
and (t.EDIPEC ='1' OR t.EDIPEC is null)
begin
nb :=0;
message :='';
titreCourriel := '<b>Traitement automatique du '||sysdate||'</b><br>';
-- MAJ des dossiers sélectionnés
for dossier in dossier_a_maj
loop
dbms_output.put_line (dossier.hoscodidehos);
message := message || '<br>' || dossier.hoscodidehos || '<br>' ;
nb := nb+1;
end loop;
if (nb > 0) then
dbms_output.put_line (nb);
update table1 a set a.pec = 'AC', a.edipec = Null
where a.hos in (
select t.hos from table1 t, table2 t2
where t.hoscod = t2.hoscod and t2.codtype = 'XX'
and t2.anuuti = '0' and t2.inddfa = 'N'
and t.tde = 'SS'
and (t.pec = 'DM' OR t.pec = 'DP')
and t.psp ='49'
and (t.EDIPEC ='1' OR t.EDIPEC is null)
);
--Validation de la transaction
--COMMIT;
message := message || '<br>Nombre de dossiers corrigés : ' || to_char(nb);
else
--dbms_output.put_line ('Aucun dossier à corriger !');
message := 'Aucun dossier à corriger !';
end if;
-- Envoi mail
envoiemail('dummy@nowhere.fr', 'Test', titreCourriel || message, 'html', 'test');
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line ('Aucun dossier à corriger !');
WHEN OTHERS THEN
err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 200);
dbms_output.put_line('Erreur ! ' || err_num || ' : ' || err_msg );
-- Annulation de la transaction
--ROLLBACK;
i := 1/0;
end CORRECTION_DOSS_; |
Partager