day_sum_ctl.prc

数据汇总

CREATE OR REPLACE PROCEDURE DAY_SUM_CTL(AVC_RETERN OUT VARCHAR2) IS
  N_MIN_DAY NUMBER(2);
  N_MAX_DAY NUMBER(2);
  VC_DATE   VARCHAR2(8);
  VC_REGION VARCHAR2(3);
  SZSQL     VARCHAR2(2000);

  TYPE VARRAY_TYPE IS VARRAY(50) OF VARCHAR2(10);
  TYPE VARRAY_REGION_CODE IS VARRAY(50) OF VARCHAR2(10);
  VARRAY_REGION VARRAY_TYPE;
  VARRAY_AREA   VARRAY_REGION_CODE;

BEGIN
  AVC_RETERN    := '成功！';
  N_MIN_DAY     := 1;
  N_MAX_DAY     := 28;
  VARRAY_REGION := VARRAY_TYPE('451','452','453','454','455','456','457','458',
                               '459','464','467','468','469');

  VARRAY_AREA := VARRAY_REGION_CODE('0451','0452','0453','0454','0455','0456','0457','0458','0459',
                                    '0464','0467','0468','0469');
  FOR REG IN VARRAY_REGION.FIRST .. VARRAY_REGION.LAST LOOP
    FOR C1 IN N_MIN_DAY .. N_MAX_DAY LOOP
      SELECT '200902' || DECODE(LENGTHB(C1), 1, '0' || C1, C1)
        INTO VC_DATE
        FROM DUAL;
      /*DAY_SUM(vc_date,avc_retern);*/
      SZSQL := 'insert into dzl_sum_fee_' || VARRAY_REGION(REG) ||
               ' SELECT user_id,sum(sum_charge) from (select user_id,sum(charge1+charge2+charge3+charge4) sum_charge FROM jf.dr_ps_' ||
               VARRAY_AREA(REG) || '_' || VC_DATE || ' group by user_id' ||
               ' UNION ALL select user_id,sum(charge1+charge2+charge3+charge4) sum_charge FROM jf.dr_CS_' ||
               VARRAY_AREA(REG) || '_' || VC_DATE || ' group by user_id' ||
               ' UNION ALL select user_id,sum(charge1+charge2+charge3+charge4) sum_charge FROM jf.dr_SMS_' ||
               VARRAY_AREA(REG) || '_' || VC_DATE || ' group by user_id' ||
               ' UNION ALL select user_id,sum(charge1+charge2+charge3+charge4) sum_charge FROM jf.dr_ISMP_' ||
               VARRAY_AREA(REG) || '_' || VC_DATE || ' group by user_id' ||
               ') group by user_id';
      EXECUTE IMMEDIATE SZSQL;
      COMMIT;
    END LOOP;
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    AVC_RETERN := '失败！' || SQLERRM || '--' || SZSQL;
    ROLLBACK;
    RETURN;
END DAY_SUM_CTL;
/