几个Oracle procedure实例

create or replace procedure PC_TB_HTTPTASK_RESULT_TMP IS
v_http_targetalias varchar2(300);
v_http_dns_avgrtt number;
v_http_avgrtt number;
v_http_firstbyte_avgrtt number;
v_http_body_avgrtt number;
v_http_avgspeed number;
v_http_pagesize number;
v_http_opentimertt NUMBER;

CURSOR c_httptask_result IS
SELECT HTTP_TARGETALIAS,
       AVG(HTTP_DNS_AVGRTT) HTTP_DNS_AVGRTT,
       AVG(HTTP_AVGRTT) HTTP_AVGRTT,
       AVG(HTTP_FIRSTBYTE_AVGRTT) HTTP_FIRSTBYTE_AVGRTT,
       AVG(HTTP_BODY_AVGRTT) HTTP_BODY_AVGRTT,
       AVG(HTTP_AVGSPEED) HTTP_AVGSPEED,
       MAX(HTTP_PAGESIZE) HTTP_PAGESIZE
FROM (SELECT OBJECTID,
       HTTP_TARGETALIAS,
       HTTP_DNS_AVGRTT,
       HTTP_AVGRTT,
       HTTP_FIRSTBYTE_AVGRTT,
       HTTP_BODY_AVGRTT,
       HTTP_AVGSPEED,
       HTTP_PAGESIZE
  FROM TB_HTTPTASK_RESULT
WHERE
   TO_CHAR(COLTIME, 'YYYY-MM-DD') = TO_CHAR(SYSDATE-1, 'YYYY-MM-DD')
   AND HTTP_TARGETALIAS IS NOT NULL) TB_HTTPTASK_RESULT_CON
   
  WHERE
   OBJECTID IN
       (SELECT GROUPTASKID
          FROM GROUPTASK
         WHERE GROUPTASKID IN
               (SELECT TASKID
                  FROM GROUPTASKRELATION
                 WHERE GROUPID IN
                       (SELECT PROBEGROUPID
                          FROM TB_TASK_INFO
                         WHERE ID IN
                               (SELECT TASK_ID
                                  FROM TB_TASK_RELATION
                                 WHERE GROUP_ID IN
                                       (SELECT ID
                                          FROM TB_TASK_GROUP
                                         WHERE GROUPNAME = '宽带感知任务')))))
GROUP BY HTTP_TARGETALIAS;
/*
SELECT HTTP_TARGETALIAS,
       AVG(HTTP_DNS_AVGRTT) HTTP_DNS_AVGRTT,
       AVG(HTTP_AVGRTT) HTTP_AVGRTT,
       AVG(HTTP_FIRSTBYTE_AVGRTT) HTTP_FIRSTBYTE_AVGRTT,
       AVG(HTTP_BODY_AVGRTT) HTTP_BODY_AVGRTT,
       AVG(HTTP_AVGSPEED) HTTP_AVGSPEED,
       MAX(HTTP_PAGESIZE) HTTP_PAGESIZE
FROM TB_HTTPTASK_RESULT
WHERE OBJECTID IN
       (SELECT GROUPTASKID
          FROM GROUPTASK
         WHERE GROUPTASKID IN
               (SELECT TASKID
                  FROM GROUPTASKRELATION
                 WHERE GROUPID IN
                       (SELECT PROBEGROUPID
                          FROM TB_TASK_INFO
                         WHERE ID IN
                               (SELECT TASK_ID
                                  FROM TB_TASK_RELATION
                                 WHERE GROUP_ID IN
                                       (SELECT ID
                                          FROM TB_TASK_GROUP
                                         WHERE GROUPNAME = '宽带感知任务')))))
AND HTTP_TARGETALIAS IS NOT NULL
AND TO_CHAR(COLTIME, 'YYYY-MM-DD') = TO_CHAR(SYSDATE-1, 'YYYY-MM-DD')
GROUP BY HTTP_TARGETALIAS;
*/

-- SELECT * FROM TB_PERF_METRIC WHERE met_name LIKE 'HTTP_BODY_AVGRTT';

/******************************************************************************
   NAME:       PC_TB_HTTPTASK_RESULT_TMP
   PURPOSE:

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        2010-11-10          1. Created this procedure.

   NOTES:

   Automatically available Auto Replace Keywords:
      Object Name:     PC_TB_HTTPTASK_RESULT_TMP
      Sysdate:         2010-11-10
      Date and Time:   2010-11-10, 10:54:32, and 2010-11-10 10:59:15
      Username:         (set in TOAD Options, Procedure Editor)
      Table Name:       (set in the "New PL/SQL Object" dialog)

******************************************************************************/
begin
  OPEN c_httptask_result;
  LOOP
  FETCH c_httptask_result INTO v_http_targetalias,
                               v_http_dns_avgrtt,
                               v_http_avgrtt,
                               v_http_firstbyte_avgrtt,
                               v_http_body_avgrtt,
                               v_http_avgspeed,
                               v_http_pagesize;
 
  EXIT WHEN c_httptask_result%NOTFOUND;
 
  v_http_opentimertt := v_http_dns_avgrtt + v_http_avgrtt + v_http_firstbyte_avgrtt + v_http_body_avgrtt;
  -- DELETE FROM TB_HTTPTASK_RESULT_TMP WHERE HTTP_TARGETALIAS = v_http_targetalias;
  INSERT INTO TB_HTTPTASK_RESULT_TMP(HTTP_TARGETALIAS,
                                     HTTP_DNS_AVGRTT,
                                     HTTP_AVGRTT,
                                     HTTP_FIRSTBYTE_AVGRTT,
                                     HTTP_BODY_AVGRTT,
                                     HTTP_AVGSPEED,
                                     HTTP_PAGESIZE,
                                     HTTP_OPENTIMERTT,
                                     CREATE_DATE)
  VALUES (v_http_targetalias,
          v_http_dns_avgrtt,
          v_http_avgrtt,
          v_http_firstbyte_avgrtt,
          v_http_body_avgrtt,
          v_http_avgspeed,
          v_http_pagesize,
          v_http_opentimertt,
          TRUNC(SYSDATE));
 
  END LOOP;
  COMMIT;
  EXCEPTION   
     WHEN OTHERS THEN  
         CLOSE c_httptask_result; 
         dbms_output.put_line(SQLERRM);
   IF c_httptask_result%ISOPEN THEN    
      CLOSE c_httptask_result;  
   END IF;
end PC_TB_HTTPTASK_RESULT_TMP;

----------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE PC_TB_HTTPTASK_TIME120_RESULT IS
V_SYSDATE DATE DEFAULT SYSDATE;
V_HAS_DATA NUMBER;
V_HTTP_TARGETALIAS VARCHAR2(100);

/******************************************************************************
   NAME:       PC_TB_HTTPTASK_TIME120_RESULT
   PURPOSE:

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        2011-03-03          1. Created this procedure.

   NOTES:

   Automatically available Auto Replace Keywords:
      Object Name:     PC_TB_HTTPTASK_TIME120_RESULT
      Sysdate:         2011-03-03
      Date and Time:   2011-03-03, 14:00:00, and 2011-03-03 14:00:00
      Username:         (set in TOAD Options, Procedure Editor)
      Table Name:       (set in the "New PL/SQL Object" dialog)

******************************************************************************/

CURSOR C_HTTP_TARGETALIAS IS
SELECT HTTP_TARGETALIAS_NAME FROM TB_HTTP_TARGETALIAS;

BEGIN
   
    BEGIN
      SELECT COUNT(*) INTO V_HAS_DATA FROM (SELECT HTTP_TARGETALIAS, AVG(HTTP_DNS_AVGRTT) HTTP_DNS_AVGRTT,MAX(HTTP_PAGESIZE) HTTP_PAGESIZE FROM TB_HTTPTASK_RESULT WHERE HTTP_TARGETALIAS IS NOT NULL AND COLTIME >= V_SYSDATE - INTERVAL '120' MINUTE AND COLTIME < V_SYSDATE AND OBJECTID IN (SELECT GROUPTASKID FROM GROUPTASK WHERE GROUPTASKID IN (SELECT TASKID FROM GROUPTASKRELATION WHERE GROUPID IN (SELECT PROBEGROUPID FROM TB_TASK_INFO WHERE ID IN (SELECT TASK_ID FROM TB_TASK_RELATION WHERE GROUP_ID IN (SELECT ID FROM TB_TASK_GROUP WHERE GROUPNAME = '宽带感知任务'))))) GROUP BY HTTP_TARGETALIAS) TB_HTTPTASK_RESULT_HAS_DATA;
      EXCEPTION
      WHEN no_data_found THEN
           V_HAS_DATA := 0;
    END;
   
    IF V_HAS_DATA=0 THEN
     
       OPEN C_HTTP_TARGETALIAS;
       LOOP
         FETCH C_HTTP_TARGETALIAS INTO V_HTTP_TARGETALIAS;
         EXIT WHEN C_HTTP_TARGETALIAS%NOTFOUND;
         INSERT INTO TB_HTTPTASK_WM_RESULT_TMP(HTTP_TARGETALIAS,HTTP_OPENTIMERTT,CREATE_DATE,HTTP_DNS_AVGRTT,HTTP_PAGESIZE) VALUES (V_HTTP_TARGETALIAS, 0, V_SYSDATE, 0, 0);
       END LOOP;
      
    ELSE
       INSERT INTO TB_HTTPTASK_WM_RESULT_TMP(HTTP_TARGETALIAS,HTTP_OPENTIMERTT,CREATE_DATE,HTTP_DNS_AVGRTT,HTTP_PAGESIZE) SELECT HTTP_TARGETALIAS, AVG(HTTP_DNS_AVGRTT+HTTP_AVGRTT+HTTP_FIRSTBYTE_AVGRTT+HTTP_BODY_AVGRTT) HTTP_OPENTIMERTT, V_SYSDATE, AVG(HTTP_DNS_AVGRTT) HTTP_DNS_AVGRTT, MAX(HTTP_PAGESIZE) HTTP_PAGESIZE FROM TB_HTTPTASK_RESULT WHERE HTTP_TARGETALIAS IS NOT NULL AND COLTIME >= V_SYSDATE - INTERVAL '120' MINUTE AND COLTIME < V_SYSDATE AND OBJECTID IN (SELECT GROUPTASKID FROM GROUPTASK WHERE GROUPTASKID IN (SELECT TASKID FROM GROUPTASKRELATION WHERE GROUPID IN (SELECT PROBEGROUPID FROM TB_TASK_INFO WHERE ID IN (SELECT TASK_ID FROM TB_TASK_RELATION WHERE GROUP_ID IN (SELECT ID FROM TB_TASK_GROUP WHERE GROUPNAME = '宽带感知任务'))))) GROUP BY HTTP_TARGETALIAS;
    END IF;
 
    COMMIT;
   
    EXCEPTION
      WHEN OTHERS THEN
        CLOSE C_HTTP_TARGETALIAS;
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
        IF C_HTTP_TARGETALIAS%ISOPEN THEN    
          CLOSE C_HTTP_TARGETALIAS;  
       END IF;
 
END PC_TB_HTTPTASK_TIME120_RESULT;