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
|
CREATE OR REPLACE Procedure P_ECART_TYPE_QUINZE ( v$NOM_SCHEMA IN varchar2 )
IS
CURSOR C_QUINZE IS
SELECT DATEFIC, HOSTNAME, MOUNT_POINT, T_OCCUP
FROM MCO_FS
where DATEFIC = sysdate
AND T_OCCUP > 70
ORDER BY T_OCCUP DESC;
-- Déclartion des variables
v$DATEFIC MCO_FS.DATEFIC%TYPE;
v$HOSTNAME MCO_FS.HOSTNAME%TYPE;
v$MOUNT_POINT MCO_FS.MOUNT_POINT%TYPE;
v$T_OCCUP MCO_FS.T_OCCUP%TYPE;
v$ECART NUMBER(10) :=0;
v$SUM_CARRE NUMBER(10) :=0;
v$ECART_CARRE NUMBER(10) :=0;
v$MOYENNE NUMBER(10) :=0;
v$NB_RESULT NUMBER(10) :=0;
v$LA_VARIANCE NUMBER(10) :=0;
v$ECART_TYPE NUMBER(10) :=0;
BEGIN
OPEN C_QUINZE;
LOOP
FETCH C_QUINZE INTO
v$DATEFIC,
v$HOSTNAME,
v$MOUNT_POINT,
v$T_OCCUP;
SELECT COUNT(*) INTO v$NB_RESULT
FROM MCO_FS WHERE HOSTNAME = v$HOSTNAME and MOUNT_POINT = v$MOUNT_POINT
AND DATEFIC <= SYSDATE AND DATEFIC >= (SYSDATE-15);
SELECT SUM(T_OCCUP) INTO v$T_OCCUP
FROM MCO_FS WHERE HOSTNAME = v$HOSTNAME AND MOUNT_POINT = v$MOUNT_POINT
AND DATEFIC <= SYSDATE AND DATEFIC >= (SYSDATE-15);
v$MOYENNE := v$T_OCCUP/v$NB_RESULT;
CURSOR C_QUINZE_T_OCCUP IS
SELECT T_OCCUP FROM MCO_FS
WHERE HOSTNAME = v$HOSTNAME and MOUNT_POINT = v$MOUNT_POINT
AND DATEFIC <= SYSDATE AND DATEFIC >= (SYSDATE-15);
v$T_OCCUP_DERIVE MCO_FS.T_OCCUP%TYPE;
OPEN C_QUINZE_T_OCCUP;
LOOP
v$ECART := v$T_OCCUP_DERIVE - v$MOYENNE;
v$ECART_CARRE := v$ECART*v$ECART;
v$SUM_CARRE := v$SUM_CARRE+v$ECART_CARRE;
END LOOP;
v$LA_VARIANCE := v$SUM_CARRE/v$NB_RESULT;
v$ECART_TYPE := SQRT(v$LA_VARIANCE);
CLOSE C_QUINZE_T_OCCUP;
INSERT INTO MCO_DERIVE_QUINZE
(DATEFIC, HOSTNAME, MOUNT_POINT, T_OCCUP, ECART_TYPE)
values
(v$DATEFIC, v$HOSTNAME, v$MOUNT_POINT, v$T_OCCUP, v$ECART_TYPE);
COMMIT;
END LOOP;
CLOSE C_QUINZE; -- fermeture du curseur Table brute
END; |
Partager