oracle 存储过程调用 返回游标结果集

DECLARE
V_BEGIN DATE := TO_DATE('2010-12-26 00:00', 'YYYY-MM-DD HH24:MI');
V_END DATE := TO_DATE('2011-01-06 23:00', 'YYYY-MM-DD HH24:MI');
BEGIN
WHILE V_BEGIN <= V_END LOOP
[color=red]P_GKPIBSC_MOT(TO_CHAR(V_BEGIN, 'YYYY-MM-DD HH24:MI'));[/color]
V_BEGIN := V_BEGIN + 1 / 24;
END LOOP;
END;
/


CREATE OR REPLACE PROCEDURE P_TOPN_CI(V_DATE IN VARCHAR2,
V_TYPE IN INT,
V_RESULT OUT SYS_REFCURSOR) AS
BEGIN
IF V_TYPE = 1 THEN

EXECUTE IMMEDIATE 'TRUNCATE TABLE TOPN_CI_BLOCK1';
INSERT INTO TOPN_CI_BLOCK1
SELECT 0, --时段数量
'#N/A', --BTSNAME
BSC,
BTS,
LAC,
CI,
SUM(TRAFFIC_TCH) / NULLIF(SUM(AV_TCH), 0) TCH每线,
AVG(TRAFFIC_TCH) TCH话务量,
AVG(TRAFFIC_TCHH) TCH半速率话务量,
AVG(AV_TCH) TCH信道,
SUM(BLOCK_TCH) / NULLIF(SUM(REQ_TCH), 0) * 100 TCH拥塞率,
AVG(BAR_MIN) 流控,
SUM(BLOCK_SD) / NULLIF(SUM(REQ_SD), 0) * 100 SD拥塞率,
AVG(BUSY_SD_MAX) SD最大使用数,
AVG(AV_SD) SD信道,
SUM(TRAFFIC_SD) / NULLIF(SUM(AV_SD), 0) SD每线,
AVG(SMS_SD) 短信,
SUM(PCH_DISCARD) / NULLIF(SUM(PCH_REQ), 0) * 100 PCH拥塞率,
SUM(IA_DISCARD) / NULLIF(SUM(IA_REQ), 0) * 100 AGCH拥塞率,
AVG(BUSY_TCH_MAX) TCH最大使用数,
AVG(UL_BUSY_PD_MAX) ULPD最大使用数,
AVG(DL_BUSY_PD_MAX) DLPD最大使用数
FROM G_KPI_CI
WHERE (LAC, CI, RECTIME) IN
(SELECT LAC, CI, RECTIME
FROM TOPN_CI
WHERE TOPN_TYPE = 1
AND RECDATE = TO_DATE(V_DATE, 'YYYY-MM-DD'))
GROUP BY BSC, BTS, LAC, CI;

UPDATE TOPN_CI_BLOCK1 X
SET 时段数量 =
(SELECT BLOCK_HOURS
FROM (SELECT LAC, CI, COUNT(1) BLOCK_HOURS
FROM TOPN_CI
WHERE TOPN_TYPE = 1
AND RECDATE = TO_DATE(V_DATE, 'YYYY-MM-DD')
GROUP BY LAC, CI)
WHERE X.LAC = LAC
AND X.CI = CI);
COMMIT;
UPDATE TOPN_CI_BLOCK1 X
SET BTSNAME =
(SELECT BTSNAME
FROM WY_NE
WHERE X.LAC = LAC
AND X.CI = CI);
COMMIT;

[color=red] OPEN V_RESULT FOR
SELECT *
FROM TOPN_CI_BLOCK1
ORDER BY 时段数量 * (TCH拥塞率 + 流控 + SD拥塞率) DESC;[/color]

END IF;

IF V_TYPE = 2 THEN

。。。

OPEN V_RESULT FOR
SELECT *
FROM TOPN_CI_SETUP1
ORDER BY 时段数量 * (1 - 呼叫建立成功率) * (1 - 呼叫建立成功率) * 呼建请求次数 DESC;

END IF;
END;