几个Oracle procedure范例
几个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;
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;