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
    P_GKPIBSC_MOT(TO_CHAR(V_BEGIN, 'YYYY-MM-DD HH24:MI'));
    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;
 
    OPEN V_RESULT FOR
      SELECT *
        FROM TOPN_CI_BLOCK1
       ORDER BY 时段数量 * (TCH拥塞率 + 流控 + SD拥塞率) DESC;
 
  END IF;

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