1. 自动报表汇总SP

CREATE OR REPLACE PROCEDURE TOPUP.sp_business_gather (currentDate IN DATE)
AS
   v_bankMoney       NUMBER;                                --查询网银支付金额
   v_bankCount       NUMBER;                                --查询网银支付数量
   v_mobileMoney     NUMBER;                            --查询手机充值订单金额
   v_mobileCount     NUMBER;                            --查询手机充值订单数量
   v_cardMoney       NUMBER;                                  --查询充值卡金额
   v_cardCount       NUMBER;                                  --查询充值卡数量
   gatherBeginDate   DATE;                                      --统计开始日期
   gatherEndDate     DATE;                                      --统计结束日期
BEGIN
   v_bankMoney := 0;                                        --查询网银支付金额
   v_bankCount := 0;                                        --查询网银支付数量
   v_mobileMoney := 0;                                  --查询手机充值订单金额
   v_mobileCount := 0;                                  --查询手机充值订单数量
   v_cardMoney := 0;                                          --查询充值卡金额
   v_cardCount := 0;                                          --查询充值卡数量
   gatherBeginDate := TO_DATE ('2011-09-30', 'YYYY-MM-DD');     --统计开始日期
   gatherEndDate := TO_DATE ('2011-09-30', 'YYYY-MM-DD');       --统计结束日期

--统计开始日期
SELECT TRUNC (currentDate - 1) INTO gatherBeginDate FROM DUAL;

--统计结束日期
SELECT TRUNC (currentDate) INTO gatherEndDate FROM DUAL;

   --查询充值卡
   SELECT   SUM (ci.price), COUNT ( * )
     INTO   v_cardMoney, v_cardCount
     FROM   tab_topup_mobile_cardpay cp,
            tab_topup_mobile_cardpay_item cpi,
            tab_topup_mobile_carditem ci
    WHERE       cp.id = cpi.pay_id
            AND ci.id = cpi.carditem_id
            AND ci.createdate >= gatherBeginDate
            AND ci.createdate < gatherEndDate
            AND ci.ttf IS NULL
            AND ci.status_id = 66;

   IF v_cardMoney IS NULL
   THEN
      v_cardMoney := 0;
   END IF;

   IF v_cardCount IS NULL
   THEN
      v_cardCount := 0;
   END IF;

   --查询手机充值订单
   SELECT   SUM (oi.price), COUNT ( * )
     INTO   v_mobileMoney, v_mobileCount
     FROM   tab_topup_orderitem oi
    WHERE       oi.createdate >= gatherBeginDate
            AND oi.createdate < gatherEndDate
            AND oi.status_id = 82;

   IF v_mobileMoney IS NULL
   THEN
      v_mobileMoney := 0;
   END IF;

   IF v_mobileCount IS NULL
   THEN
      v_mobileCount := 0;
   END IF;

   --查询网银支付
   SELECT   SUM (op.price), COUNT ( * )
     INTO   v_bankMoney, v_bankCount
     FROM   tab_topup_otherpay op
    WHERE       op.createdate >= gatherBeginDate
            AND op.createdate < gatherEndDate
            AND op.status_id = 66;

   IF v_bankMoney IS NULL
   THEN
      v_bankMoney := 0;
   END IF;

   IF v_bankCount IS NULL
   THEN
      v_bankCount := 0;
   END IF;

   INSERT INTO TAB_TOPUP_BUSINESS_GATHER (ID,
                                          BANKMONEY,
                                          BANKCOUNT,
                                          MOBILEMONEY,
                                          MOBILECOUNT,
                                          CARDMONEY,
                                          CARDCOUNT,
                                          GATHERDATE,
                                          CREATEDATE,
                                          LASTDATE)
     VALUES   (SEQ_TAB_TOPUP_BUSINESS_GATHER.NEXTVAL,
               v_bankMoney,
               v_bankCount,
               v_mobileMoney,
               v_mobileCount,
               v_cardMoney,
               v_cardCount,
               gatherBeginDate,
               SYSDATE,
               SYSDATE);
END;
/

2. 自动报表汇总JOB

DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job       => X
   ,what      => 'TOPUP.SP_BUSINESS_GATHER(SYSDATE);'
   ,next_date => to_date('10/01/2012 04:00:00','dd/mm/yyyy hh24:mi:ss')
   ,interval => 'TRUNC(SYSDATE+1)+240/1440'
   ,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/

ITPub博客

自动报表空值处理

最新文章