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 83 84 85 86 87 88 89 90 91 92 93 94
|
Set serveroutput on;
Declare
vnumero number(12); /* Numero de la requete (GFITEST.numero) */
Vreq varchar2(3000); /* Requete (GFITEST.requete) */
vdatedebutrequetenumber number(20,6);
vdatedebutrequetestring varchar2(20);
vdatefinrequetenumber number(20,6);
vdatefinrequetestring varchar2(20);
vtempsrequetenumber number(20,6);
vtempsrequetestring varchar2(20);
/* Sélection des requetes SQL*/
cursor C1 is SELECT G.NUMERO,G.REQUETE
FROM GFITEST G;
BEGIN
OPEN C1;
LOOP
BEGIN
fetch C1 into Vnumero,Vreq;
exit when C1%NOTFOUND;
/**************************************/
/*Affichage du numero et de la requete*/
/**************************************/
DBMS_OUTPUT.PUT_LINE('Requete numero:' ||Vnumero);
DBMS_OUTPUT.PUT_LINE('Requete: ***' ||Vreq || '***');
/*******************************/
/* Recuperation heure de debut */
/*******************************/
select to_char(systimestamp, 'SSSS.ff') into vdatedebutrequetestring from dual;
/*************************/
/*Execution de la requete*/
/*************************/
execute immediate Vreq;
/*******************************/
/* Recuperation heure de fin */
/*******************************/
select to_char(systimestamp, 'SSSS.ff') into vdatefinrequete from dual;
/*********************************/
/* Calcul du temps de la requete */
/*********************************/
vdatedebutrequetenumber:= to_number(vdatedebutrequetestring);
vdatefinrequetenumber := to_number(vdatefinrequetestring);
vtempsrequetenumber := vdatefinrequetenumber - vdatedebutrequetenumber);
DBMS_OUTPUT.PUT_LINE('Temps :'||to_char(vtempsrequete));
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('XXX Erreur ('|| sqlcode ||') lors du traitement ');
DBMS_OUTPUT.PUT_LINE(chr(13));
DBMS_OUTPUT.PUT_LINE(chr(13));
DBMS_OUTPUT.PUT_LINE(chr(13));
ROLLBACK;
END;
END LOOP;
CLOSE C1;
IF Vcompteur = 0 THEN
DBMS_OUTPUT.PUT_LINE('Compteur a zero c pas normal');
ELSE
DBMS_OUTPUT.PUT_LINE( Vcompteur || ' requetes trouvees');
END IF;
EXCEPTION
WHEN OTHERS THEN
raise_application_error (-20000,'XXX Erreur '|| sqlcode ||' rencontrée ds test.sql');
END;
/ |
Partager