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
|
CREATEORREPLACEPROCEDURE remplir_table IS
code_action varchar2(20);
libelle_action varchar2(30);
le_sql VARCHAR2(5000);
nom_extract varchar2(30);
nombre number;
BEGIN
executeimmediate'TRUNCATE TABLE COUNT;
FOR c IN(select decode(length(valeur),1,'00',2,'0')||to_char(valeur) mag fromtable_mag)
LOOP
BEGIN
select code, libelle into code_action, libelle_action from table_code wherecocod='S';
select colonne5 into nom from table5where exnum ='REFUSS';
le_sql:='select count(colonne1) from (select colonne1 from table1, table2, table3 where valeur1<>'333' and valeur2= ' ' and valeur3 like ''x%''and valeur4=''Y'' and idtable1 = idtable2 and idtable1 = idtable3 and substr(idagr,length(val)-2)= :1 minus select valeur5 from '||nom||')';
executeimmediate le_sql into nombre using c.mag;
insertintoCOUNT(mag, action, libelle, count)values(c.mag, code_action,libelle_action, nombre);
COMMIT;
END;
-- insertion des comptages pour les mails
BEGIN
select code, libelle into code_action, libelle_action from table_code wherecocod='W';
select colonne5 into nom from table5wherewhere exnum ='REFUSEM';
le_sql:='select count(colonne1) from (select colonne1 from table1, table2, table3 where valeur1<>'333' and valeur2= ' ' and valeur3 <>'' '' and valeur4 IN(''Y'', ''A'', ''T'') and idtable1 = idtable2 and idtable1 = idtable3 and substr(idagr,length(val)-2)= :1 minus select valeur5 from '||nom||')';
executeimmediate le_sql into nombre using c.mag;
insertintoCOUNT(mag, action, libelle, count)values(c.mag, code_action,libelle_action, nombre);
COMMIT;
END;
-- insertion des comptages pour les couriers
BEGIN
select code, libelle into code_action, libelle_action from table_code wherecocod='M';
select colonne5 into nom from table5wherewhere exnum ='REFUSEA';
le_sql:='select count(colonne1) from (select colonne1 from table1, table2, table3 where valeur1<>'333' and valeur2= ' ' and idtable1 = idtable2 and idtable1 = idtable3 and substr(idagr,length(ival)-2)= :1 minus select valeur5 from '||nom||')';
executeimmediate le_sql into nombre using c.mag;
insertintoCOUNT(mag, action, libelle, count)values(c.mag, code_action,libelle_action, nombre);
COMMIT;
END;
ENDLOOP;
Exception
WhenOTHERSthennull;
END;
/
|
Partager