ITPub博客

首页 > 数据库 > Oracle > Oracle带参数的游标使用

Oracle带参数的游标使用

Oracle 作者:valvezh 时间:2011-07-16 17:04:56 0 删除 编辑

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,
                                                V_IFINSPEED_MAX,
                                                V_IFINSPEED_MIN,
                                                V_IFINSPEED_AVG,
                                                V_IFINUCASTPKTS,
                                                V_IFINUTIL,
                                                V_IFINUTIL_MAX,
                                                V_IFINUTIL_MIN,
                                                V_IFINUTIL_AVG,
                                                V_IFOPERSTATUS,
                                                V_IFOUTDISCARDS,
                                                V_IFOUTNUCASTPKTS,
                                                V_IFOUTOCTETS,
                                                V_IFOUTSPEED,
                                                V_IFOUTSPEED_MAX,
                                                V_IFOUTSPEED_MIN,
                                                V_IFOUTSPEED_AVG,
                                                V_IFOUTUCASTPKTS,
                                                V_IFOUTUTIL,
                                                V_IFOUTUTIL_MAX,
                                                V_IFOUTUTIL_MIN,
                                                V_IFOUTUTIL_AVG,
                                                V_IFOUTPKTSSEC,
                                                V_IFINDISCRATE,
                                                V_IFINPKTSSEC,
                                                V_IFOUTDISCRATE,
                                                V_COLINTV,
                                                V_IFINERRORS,
                                                V_IFOUTERRORS,
                                                V_IFADMINSTATUS;
                               
       EXIT WHEN C_PERF_TMP%NOTFOUND;
      
       INSERT INTO PF_IFPF_AGGRE(COLTIME,
                                                    IFINDISCARDS,
                                                    IFINNUCASTPKTS,
                                                    IFINOCTETS,
                                                    IFINSPEED,
                                                    IFINSPEED_MAX,
                                                    IFINSPEED_MIN,
                                                    IFINSPEED_AVG,
                                                    IFINUCASTPKTS,
                                                    IFINUTIL,
                                                    IFINUTIL_MAX,
                                                    IFINUTIL_MIN,
                                                    IFINUTIL_AVG,
                                                    IFOUTDISCARDS,
                                                    IFOUTNUCASTPKTS,
                                                    IFOUTOCTETS,
                                                    IFOUTSPEED,
                                                    IFOUTSPEED_MAX,
                                                    IFOUTSPEED_MIN,
                                                    IFOUTSPEED_AVG,
                                                    IFOUTUCASTPKTS,
                                                    IFOUTUTIL,
                                                    IFOUTUTIL_MAX,
                                                    IFOUTUTIL_MIN,
                                                    IFOUTUTIL_AVG,
                                                    IFINERRORS,
                                                    IFOUTERRORS
                                                    ) VALUES (
                                                        V_COLTIME,
                                                        V_IFINDISCARDS,
                                                        V_IFINNUCASTPKTS,
                                                        V_IFINOCTETS,
                                                        V_IFINSPEED,
                                                        V_IFINSPEED,
                                                        V_IFINSPEED,
                                                        V_IFINSPEED,
                                                        V_IFINUCASTPKTS,
                                                        V_IFINSPEED,
                                                        V_IFINSPEED,
                                                        V_IFINSPEED,
                                                        V_IFINSPEED,
                                                        V_IFOUTDISCARDS,
                                                        V_IFOUTNUCASTPKTS,
                                                        V_IFOUTOCTETS,
                                                        V_IFOUTSPEED,
                                                        V_IFOUTSPEED,
                                                        V_IFOUTSPEED,
                                                        V_IFOUTSPEED,
                                                        V_IFOUTUCASTPKTS,
                                                        V_IFOUTSPEED,
                                                        V_IFOUTSPEED,
                                                        V_IFOUTSPEED,
                                                        V_IFOUTSPEED,
                                                        V_IFINERRORS,
                                                        V_IFOUTERRORS
                                                    );
      
      
   END LOOP;
  
   COMMIT;
  
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       CLOSE C_PERF_TMP; 
         dbms_output.put_line(SQLERRM);
         IF C_PERF_TMP%ISOPEN THEN    
            CLOSE C_PERF_TMP;  
         END IF;
       RAISE;
END PC_PERF_SUPP_TMP;

<!-- 正文结束 -->

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21938319/viewspace-1116975/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2009-07-21