需求:我们需要从另外一个数据库每天定时备份激活的数据到本地数据库
CREATE OR REPLACE PROCEDURE P_DEALER_ACTIVE_REPORT(PV_DATE in VARCHAR2) IS
/**
* 統計日激活報表
*/
PN_ID NUMBER; --主键
PV_CREATEDATE VARCHAR2(20); --创建时间
PV_ROUTEID VARCHAR2(50); --渠道编码
CURSOR activationList IS
SELECT T.*
FROM T_DEALER_ACTIVATION_LIST T
WHERE SUBSTR(V_ACT_DATE,1,8)= PV_CREATEDATE ;
--统计某天的激活量与产品信息
CURSOR orderList IS
SELECT COUNT(L.V_ROUTEID) N_NUM,L.V_ROUTEID,
T.V_BIG_TYPE,B.V_PROD_BIG_NAME,T.V_SMALL_TYPE,Y.V_SMALL_NAME,L.V_PROD_CODE,T.V_PROD_NAME,
V_ROUTENAME ,V_ROUTETYPE, V_ROUTETYPENAME,V_LEVEL,V_LEVELNAME,V_AREA,
V_AREANAME,V_SUBAREA,V_SUBAREANAME,V_MANAGERID,V_MANAGERNAME,
SUBSTR(V_ACT_DATE,1,4)||'/'|| SUBSTR(V_ACT_DATE,5,2)||'/'|| SUBSTR(V_ACT_DATE,7,2) V_FINISHTIME,
SUBSTR(V_ACT_DATE,1,6) V_MONTH
FROM T_DEALER_ACTIVATION_LIST L , T_DEALER_PRODUCT T , T_DEALER_PROD_TYPE Y, T_DEALER_PROD_BIG B,V_DEALER_DICT_ROUTE V
WHERE SUBSTR(V_ACT_DATE,1,8)=PV_CREATEDATE
AND L.V_PROD_CODE= T.V_PROD_CODE(+)
AND L.V_ROUTEID= V.V_ROUTEID(+)
AND T.V_SMALL_TYPE=Y.V_SMALL_TYPE(+)
AND Y.V_BIG_TYPE=B.V_PROD_BIG_CODE(+)
GROUP BY L.V_ROUTEID,L.V_PROD_CODE,V_ROUTENAME,V_ROUTETYPE, V_ROUTETYPENAME,V_LEVEL,V_LEVELNAME,V_AREA,
V_AREANAME,V_SUBAREA,V_SUBAREANAME,V_MANAGERID,V_MANAGERNAME,
T.V_BIG_TYPE,B.V_PROD_BIG_NAME,T.V_SMALL_TYPE,
Y.V_SMALL_NAME,T.V_PROD_NAME, SUBSTR(V_ACT_DATE,1,4)||'/'|| SUBSTR(V_ACT_DATE,5,2)||'/'|| SUBSTR(V_ACT_DATE,7,2),SUBSTR(V_ACT_DATE,1,6);
--统计激活量同步到_DEALER_ORDER_PROD
CURSOR prodListActivation IS
SELECT O.V_ORDERID,L.V_PROD_CODE,COUNT(*) N_ACTIVATION_NUMFROM T_DEALER_PROD_LIST L ,T_DEALER_CONSIGNMENT_ORDER O
WHERE O.V_ORDERID = L.V_ORDERID
AND D_USE IS NOT NULL
GROUP BY O.V_ORDERID,L.V_PROD_CODE;
BEGIN
--输入参数为空默认取前一天的日期
IF PV_DATE ISNULL THEN
SELECT TO_CHAR(SYSDATE-1,'YYYYMMDD')INTO PV_CREATEDATE FROM DUAL;
ELSE
PV_CREATEDATE := PV_DATE;
END IF;
--删除已有记录
DELETE FROM T_DEALER_ACTIVATION_LIST
WHERE SUBSTR(V_ACT_DATE,1,8)=PV_CREATEDATE;
--删除日激活报表的数据
DELETE FROM T_DEALER_ACTIVE_REPORT RWHERE TO_CHAR(R.D_DAY,'YYYYMMDD')=PV_CREATEDATE;
--第一步,同步清单
INSERT INTO T_DEALER_ACTIVATION_LIST(V_ICCID,V_ROUTEID,V_ACT_DATE,D_IMPORT)
SELECT HD.ICCID,HD.SIMDEALERID ROUTEID,HD.TIMEENTERACTIVE,SYSDATE
FROM TB_DW_SUBSCRIBER_HIS_DAY HD ,T_DEALER_DICT_ROUTE R
WHERE HD.SIMDEALERID =R.V_ROUTEID
AND SUBSTR(HD.TIMEENTERACTIVE,1,8)= PV_CREATEDATE
AND HD.SIMDEALERID IS NOT NULL;
--第二步,补充完成激活清单
FOR O IN activationListLOOP
--如果在分销系统销售的,补充产品信息
UPDATE T_DEALER_ACTIVATION_LIST L
SET L.V_PROD_CODE =
(SELECT V_PROD_CODEFROM T_DEALER_PROD_LIST T WHERE T.V_ICCID=O.V_ICCID)
WHERE L.V_ICCID=O.V_ICCID;
--回填激活时间
UPDATE T_DEALER_PROD_LIST L
SET L.D_USE =TO_DATE(O.V_ACT_DATE,'yyyyMMddHH24MISS')
WHERE L.V_ICCID=O.V_ICCID;
COMMIT;
END LOOP;
--第三步,统计日激活报表
--添加当天激活记录
FOR o IN orderListLOOP
SELECT SEQ_ACTIVE_REPORT.NEXTVAL INTO PN_ID FROM DUAL;
PV_ROUTEID := o.V_ROUTEID;
INSERT INTO T_DEALER_ACTIVE_REPORT(
N_ID,V_MONTH,D_DAY,V_ROUTEID,V_BIG_TYPE,V_SMALL_TYPE,V_SMALL_NAME,V_PROD_CODE,V_PROD_NAME,N_NUM,
V_ROUTENAME,V_MANAGERID,V_MANAGERNAME,V_AREAID,V_AREANAME,V_LEVELID,V_LEVELNAME,V_TYPEID,V_TYPENAME,D_ACT)
VALUES(
PN_ID,
o.V_MONTH,
TO_DATE(o.V_FINISHTIME,'YYYY/MM/DD'),
o.V_ROUTEID,
o.V_BIG_TYPE,
o.V_SMALL_TYPE,
o.V_SMALL_NAME,
o.V_PROD_CODE,
o.V_PROD_NAME,
o.N_NUM,
o.V_ROUTENAME,
o.V_MANAGERID,
o.V_MANAGERNAME,
o.V_AREA,
o.V_AREANAME,
o.V_LEVEL,
o.V_LEVELNAME,
o.V_ROUTETYPE,
o.V_ROUTETYPENAME,
SYSDATE
);
COMMIT;
END LOOP;
--激活量同步到_DEALER_ORDER_PROD
FOR O IN prodListActivationLOOP
UPDATE T_DEALER_ORDER_PROD SET N_ACTIVATION_NUM =O.N_ACTIVATION_NUM
WHERE V_ORDERID=O.V_ORDERIDAND V_PROD_CODE =O.V_PROD_CODE;
COMMIT;
END LOOP;
END P_DEALER_ACTIVE_REPORT;
end P_DEALER_ACTIVE_REPORT