简单存储过程实例：P_GCELL_PEAK_ZTE

最新推荐文章于 2024-08-27 10:10:42 发布

最新推荐文章于 2024-08-27 10:10:42 发布 · 105 阅读

本文介绍了一个Oracle PL/SQL过程，用于批量处理指定日期的数据记录。该过程首先检查源表是否存在相关记录，如果存在，则进一步抽取每个基站ID在指定日期的最大忙时业务信道(TCH)流量和补充数据业务(SD)流量，并将这些数据汇总到临时表中。

CREATE OR REPLACE PROCEDURE P_GCELL_PEAK_ZTE(I_RECDATE IN VARCHAR) AS
V_EXIST INT;
V_RECDATE DATE := TO_DATE(I_RECDATE, 'YYYY-MM-DD');

MY_BREAK EXCEPTION;

BEGIN
SELECT COUNT(1)
INTO V_EXIST
FROM A_BASIC_CS_RADIO_H
WHERE COLLECTTIME BETWEEN V_RECDATE AND V_RECDATE + 23 / 24;
IF V_EXIST = 0 THEN
RAISE MY_BREAK;
ELSE
DBMS_OUTPUT.PUT_LINE('A_BASIC_CS_RADIO_H 记录:' || V_EXIST);
END IF;

EXECUTE IMMEDIATE 'TRUNCATE TABLE GCELL_PEAK_TEMPZTETCH';
INSERT INTO GCELL_PEAK_TEMPZTETCH
SELECT BSCID, SITEID, BTSID, COLLECTTIME, TRAFFIC_TCH
FROM (SELECT BSCID,
SITEID,
BTSID,
COLLECTTIME,
(C100030127 + C100030129) / 3600 TRAFFIC_TCH,
ROW_NUMBER() OVER(PARTITION BY BSCID, SITEID, BTSID ORDER BY(C100030127 + C100030129) DESC) RN
FROM A_BASIC_CS_RADIO_H
WHERE COLLECTTIME BETWEEN V_RECDATE AND V_RECDATE + 23 / 24)
WHERE RN = 1;
COMMIT;
SELECT COUNT(1) INTO V_EXIST FROM GCELL_PEAK_TEMPZTETCH;
DBMS_OUTPUT.PUT_LINE('GCELL_PEAK_TEMPZTETCH TCH忙时已经汇总:' || V_EXIST);

EXECUTE IMMEDIATE 'TRUNCATE TABLE GCELL_PEAK_TEMPZTESD';
INSERT INTO GCELL_PEAK_TEMPZTESD
SELECT BSCID, SITEID, BTSID, COLLECTTIME, TRAFFIC_SD
FROM (SELECT BSCID,
SITEID,
BTSID,
COLLECTTIME,
C100030124 / 3600 TRAFFIC_SD,
ROW_NUMBER() OVER(PARTITION BY BSCID, SITEID, BTSID ORDER BY C100030124 DESC) RN
FROM A_BASIC_CS_RADIO_H
WHERE COLLECTTIME BETWEEN V_RECDATE AND V_RECDATE + 23 / 24)
WHERE RN = 1;
COMMIT;
SELECT COUNT(1) INTO V_EXIST FROM GCELL_PEAK_TEMPZTESD;
DBMS_OUTPUT.PUT_LINE('GCELL_PEAK_TEMPZTESD SD忙时已经汇总:' || V_EXIST);

EXCEPTION
WHEN MY_BREAK THEN
DBMS_OUTPUT.PUT_LINE('A_BASIC_CS_RADIO_H 没有记录');
END;