oracle 储存过程demo

需求:我们需要从另外一个数据库每天定时备份激活的数据到本地数据库

CREATE OR REPLACE PROCEDURE P_DEALER_ACTIVE_REPORT(PV_DATE inVARCHAR2) 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 ISNULLTHEN

     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 TWHERE 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