CREATE OR REPLACE PROCEDURE PC_PERF_SUPP_TMP(SDATE in varchar2,
EDATE in varchar2, GNAME in varchar2) IS
V_FN_OBJECTID varchar2(100);
V_COLTIME varchar2(30);
V_IFINDISCARDS NUMBER;
V_IFINNUCASTPKTS number;
V_IFINOCTETS number;
V_IFINSPEED number;
V_IFINSPEED_MAX number;
V_IFINSPEED_MIN number;
V_IFINSPEED_AVG number;
V_IFINUCASTPKTS number;
V_IFINUTIL number;
V_IFINUTIL_MAX number;
V_IFINUTIL_MIN number;
V_IFINUTIL_AVG number;
V_IFOPERSTATUS number;
V_IFOUTDISCARDS number;
V_IFOUTNUCASTPKTS number;
V_IFOUTOCTETS number;
V_IFOUTSPEED number;
V_IFOUTSPEED_MAX number;
V_IFOUTSPEED_MIN number;
V_IFOUTSPEED_AVG number;
V_IFOUTUCASTPKTS number;
V_IFOUTUTIL number;
V_IFOUTUTIL_MAX number;
V_IFOUTUTIL_MIN number;
V_IFOUTUTIL_AVG number;
V_IFOUTPKTSSEC number;
V_IFINDISCRATE number;
V_IFINPKTSSEC number;
V_IFOUTDISCRATE number;
V_COLINTV number;
V_IFINERRORS number;
V_IFOUTERRORS number;
V_IFADMINSTATUS number;
//----------------------------------------------------------------------------------------------//
NAME:
PC_PERF_SUPP_TMP
PURPOSE:
REVISIONS:
Ver
Date
Author
Description
---------
---------- ---------------
------------------------------------
1.0
2011-7-15
yushibo
1. Created this procedure.
NOTES:
Automatically available
Auto Replace Keywords:
Object
Name:
PC_PERF_SUPP_TMP
Sysdate:
2011-7-15
Date and Time: 2011-7-15,
15:37:08, and 2011-7-15 15:37:08
Username:
Administrator (set in TOAD Options, Procedure Editor)
Table
Name:
(set in the "New PL/SQL Object" dialog)
params: SDATE 格式 (2011-07-01 00:05)
EDATE 格式 (2011-07-02 00:00)
GNAME '深圳cncnet出口流量汇总'
//----------------------------------------------------------------------------------------------//
CURSOR C_PERF_TMP(C_FN_OBJECTID
varchar2) is
select
COLTIME,
sum(IFINDISCARDS) as IFINDISCARDS,
sum(IFINNUCASTPKTS) as IFINNUCASTPKTS,
sum(IFINOCTETS) as IFINOCTETS,
sum(IFINSPEED) as IFINSPEED,
sum(IFINSPEED_MAX) as IFINSPEED_MAX,
sum(IFINSPEED_MIN) as IFINSPEED_MIN,
sum(IFINSPEED_AVG) as IFINSPEED_AVG,
sum(IFINUCASTPKTS) as IFINUCASTPKTS,
sum(IFINUTIL) as IFINUTIL,
sum(IFINUTIL_MAX) as IFINUTIL_MAX,
sum(IFINUTIL_MIN) as IFINUTIL_MIN,
sum(IFINUTIL_AVG) as IFINUTIL_AVG,
sum(IFOPERSTATUS) as IFOPERSTATUS,
sum(IFOUTDISCARDS) as IFOUTDISCARDS,
sum(IFOUTNUCASTPKTS) as IFOUTNUCASTPKTS,
sum(IFOUTOCTETS) as IFOUTOCTETS,
sum(IFOUTSPEED) as IFOUTSPEED,
sum(IFOUTSPEED_MAX) as IFOUTSPEED_MAX,
sum(IFOUTSPEED_MIN) as IFOUTSPEED_MIN,
sum(IFOUTSPEED_AVG) as IFOUTSPEED_AVG,
sum(IFOUTUCASTPKTS) as IFOUTUCASTPKTS,
sum(IFOUTUTIL) as IFOUTUTIL,
sum(IFOUTUTIL_MAX) as IFOUTUTIL_MAX,
sum(IFOUTUTIL_MIN) as IFOUTUTIL_MIN,
sum(IFOUTUTIL_AVG) as IFOUTUTIL_AVG,
sum(IFOUTPKTSSEC) as IFOUTPKTSSEC,
sum(IFINDISCRATE) as IFINDISCRATE,
sum(IFINPKTSSEC) as IFINPKTSSEC,
sum(IFOUTDISCRATE) as IFOUTDISCRATE,
sum(COLINTV)
as COLINTV,
sum(IFINERRORS) as IFINERRORS,
sum(IFOUTERRORS) as IFOUTERRORS,
sum(IFADMINSTATUS) as IFADMINSTATUS
from pf_ifpf
where COLTIME>=to_date(SDATE,'yyyy-mm-dd
hh24:mi')
and
COLTIME<=to_date(EDATE,'yyyy-mm-dd hh24:mi')
and OBJECTID
in(select MEMBER_ID from TB_SYSTEM_GROUP_MEMBER where
GROUP_TYPE='960002' and group_id = C_FN_OBJECTID)
group by
COLTIME order by COLTIME;
BEGIN
V_FN_OBJECTID :=
GET_GROUP_ID(GNAME);
delete from pf_ifpf_aggre
where COLTIME>=to_date(SDATE,'yyyy-mm-dd hh24:mi')
and COLTIME<=to_date(EDATE,'yyyy-mm-dd hh24:mi') and
OBJECTID = V_FN_OBJECTID;
OPEN C_PERF_TMP(V_FN_OBJECTID);
LOOP
FETCH C_PERF_TMP
INTO V_COLTIME,
V_IFINDISCARDS,
V_IFINNUCASTPKTS,
V_IFINOCTETS,
V_IFINSPEED,