ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 自动报表空值处理

自动报表空值处理

原创 Linux操作系统 作者:golden_zhou 时间:2012-01-09 13:34:19 0 删除 编辑
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博客 ” ,链接:http://blog.itpub.net/25198367/viewspace-714605/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-03-09

  • 博文量
    238
  • 访问量
    293438