存储过程etl_bas_asset_call_x_new.prc

包括两个关于java高级的小程序工程项目

create or replace procedure ETL_BAS_ASSET_CALL_X_NEW(ETL_MONTH varchar2,
                                                     AREA_CODE varchar2,
                                                     ERROR_MSG out varchar2) is
  /*-----------------------------------------------------------------------------
      模块名称    ： ETL_BAS_ASSET_CALL_X
      模块编号    ：
      功能描述    ： 话单
      输入参数    ： ETL_DATE    ：数据抽取的时间
                     AREA_CODE   ：数据抽取的地区代码      
      输出参数    ： ERROR_MSG   : 错误信息
      返回值      ： 无
      引用表      ： 无
      目标表      ： BAS_ASSET_CALL_X
      被调用模块  ： 无
      调用模块    ： 无
      运行时间    :  无
      运行用时    ： 无
      编写人      ： 朱磊
      编写日期    ： 2008-03-05
      修改人      ： 无
      修改日期    ： 无
  -----------------------------------------------------------------------------*/
  var_etl_month  varchar2(6) := ETL_MONTH;
  var_area_code  varchar2(4) := AREA_CODE;
  var_start_time date;
  ----
  var_lcl_cnt_cnct_before3      number(12); --本地通话次数集中度
  var_lcl_cnt_cnct_sum          number(12);
  var_lcl_dur_cnct_before3      number(12); --本地通话时长集中度
  var_lcl_dur_cnct_sum          number(12);
  var_tol_cnt_cnct_before3      number(12); --长途通话次数集中度
  var_tol_cnt_cnct_sum          number(12);
  var_tol_dur_fact_cnct_before3 number(12); --长途通话时长集中度
  var_tol_dur_fact_cnct_sum     number(12);
  ----
  var_pid_12 number(2);
begin
  --记录存储过程开始执行的时间
  var_start_time := sysdate;
  --删除历史数据
  execute immediate 'alter table tmp_asset_call_bill_new truncate partition PART_' ||
                    var_area_code;
  execute immediate 'alter table tmp_call_cnt_top3_new truncate partition PART_' ||
                    var_area_code;
  execute immediate 'alter table tmp_call_dur_top3_new truncate partition PART_' ||
                    var_area_code;
  execute immediate 'alter table tmp_tol_call_cnt_top3_new truncate partition PART_' ||
                    var_area_code;
  execute immediate 'alter table tmp_tol_call_dur_top3_new truncate partition PART_' ||
                    var_area_code;
  pro_truncate_table(var_etl_month,
                     var_area_code,
                     'ETL_BAS_ASSET_CALL_X_NEW',
                     'BAS_ASSET_CALL_X',
                     var_pid_12);
  --向临时中间表插数据步骤一
  begin
    insert into tmp_asset_call_bill_new
      (Area_Code,
       Stats_Month,
       Calling_Area_Code,
       Calling_Nbr,
       Called_Area_Code,
       Called_Nbr,
       Call_Cnt,
       Call_Dur,
       Call_Charge,
       Call_Type,
       Call_Time_Area,
       Serv_id)
      select var_area_code,
             data_month,
             billing_area_code,
             billing_nbr,
             billing_area_code,
             term_nbr,
             Count(*),
             sum(rum_value1),
             sum(charge1),
             acct_item_type_id1,
             to_char(start_time, 'hh'),
             serv_id
        from zdyx_inf.inf_njf_ur_fix_local
       where acct_item_type_id1 is not null
         and area_code = var_area_code
         and data_month = var_etl_month
       group by var_area_code,
                data_month,
                billing_area_code,
                billing_nbr,
                billing_area_code,
                term_nbr,
                acct_item_type_id1,
                to_char(start_time, 'hh'),
                serv_id;
  exception
    when others then
      ERROR_MSG := sqlcode || ':插入tmp_asset_call_bill表步骤一出错';
      pro_execute_log('zdyx_analyze',
                      '模块：话单,插入tmp_asset_call_bill表步骤一出错',
                      null,
                      'ETL_BAS_ASSET_CALL_X',
                      var_start_time,
                      1,
                      var_area_code,
                      var_etl_month);
      rollback;
      return;
  end;
  commit;

  --向临时中间表插数据步骤二
  begin
    insert into tmp_asset_call_bill_new
      (Area_Code,
       Stats_Month,
       Calling_Area_Code,
       Calling_Nbr,
       Called_Area_Code,
       Called_Nbr,
       Call_Cnt,
       Call_Dur,
       Call_Charge,
       Call_Type,
       Call_Time_Area,
       Serv_id)
      select var_area_code,
             data_month,
             billing_area_code,
             billing_nbr,
             billing_area_code,
             term_nbr,
             Count(*),
             sum(rum_value2),
             sum(charge2),
             acct_item_type_id2,
             to_char(start_time, 'hh'),
             serv_id
        from zdyx_inf.inf_njf_ur_fix_local
       where area_code = var_area_code
         and data_month = var_etl_month
         and acct_item_type_id2 is not null
       group by var_area_code,
                data_month,
                billing_area_code,
                billing_nbr,
                billing_area_code,
                term_nbr,
                acct_item_type_id2,
                to_char(start_time, 'hh'),
                serv_id;
  exception
    when others then
      ERROR_MSG := sqlcode || ':插入tmp_asset_call_bill表步骤三出错';
      pro_execute_log('zdyx_analyze',
                      '模块：话单,插入tmp_asset_call_bill表步骤三出错',
                      null,
                      'ETL_BAS_ASSET_CALL_X',
                      var_start_time,
                      1,
                      var_area_code,
                      var_etl_month);
      rollback;
      return;
  end;
  commit;

  --向临时中间表插数据步骤三
  begin
    insert into tmp_asset_call_bill_new
      (Area_Code,
       Stats_Month,
       Calling_Area_Code,
       Calling_Nbr,
       Called_Area_Code,
       Called_Nbr,
       Call_Cnt,
       Call_Dur,
       Call_Charge,
       Call_Type,
       Call_Time_Area,
       Serv_id)
      select var_area_code,
             data_month,
             billing_area_code,
             billing_nbr,
             billing_area_code,
             term_nbr,
             Count(*),
             sum(rum_value1),
             sum(charge1),
             acct_item_type_id1,
             to_char(start_time, 'hh'),
             serv_id
        from zdyx_inf.inf_njf_ur_fix_trunk
       where acct_item_type_id1 is not null
         and area_code = var_area_code
         and data_month = var_etl_month
       group by var_area_code,
                data_month,
                billing_area_code,
                billing_nbr,
                billing_area_code,
                term_nbr,
                acct_item_type_id1,
                to_char(start_time, 'hh'),
                serv_id;
  exception
    when others then
      ERROR_MSG := sqlcode || ':插入tmp_asset_call_bill表步骤二出错';
      pro_execute_log('zdyx_analyze',
                      '模块：话单,插入tmp_asset_call_bill表步骤二出错',
                      null,
                      'ETL_BAS_ASSET_CALL_X',
                      var_start_time,
                      1,
                      var_area_code,
                      var_etl_month);
      rollback;
      return;
  end;
  commit;

  --向临时中间表插数据步骤四
  begin
    insert into tmp_asset_call_bill_new
      (Area_Code,
       Stats_Month,
       Calling_Area_Code,
       Calling_Nbr,
       Called_Area_Code,
       Called_Nbr,
       Call_Cnt,
       Call_Dur,
       Call_Charge,
       Call_Type,
       Call_Time_Area,
       Serv_id)
      select var_area_code,
             data_month,
             billing_area_code,
             billing_nbr,
             billing_area_code,
             term_nbr,
             Count(*),
             sum(rum_value2),
             sum(charge2),
             acct_item_type_id2,
             to_char(start_time, 'hh'),
             serv_id
        from zdyx_inf.inf_njf_ur_fix_trunk
       where area_code = var_area_code
         and data_month = var_etl_month
         and acct_item_type_id2 is not null
       group by var_area_code,
                data_month,
                billing_area_code,
                billing_nbr,
                billing_area_code,
                term_nbr,
                acct_item_type_id2,
                to_char(start_time, 'hh'),
                serv_id;
  exception
    when others then
      ERROR_MSG := sqlcode || ':插入tmp_asset_call_bill表步骤四出错';
      pro_execute_log('zdyx_analyze',
                      '模块：话单,插入tmp_asset_call_bill表步骤四出错',
                      null,
                      'ETL_BAS_ASSET_CALL_X',
                      var_start_time,
                      1,
                      var_area_code,
                      var_etl_month);
      rollback;
      return;
  end;
  commit;
  --向目标表插入数据
  begin
    insert into BAS_ASSET_CALL_X
      (Pid_12,
       Serv_Id,
       Bill_Month,
       Area_Code,
       Lcl_Cnt_Cnct_total,
       Lcl_Dur_Cnct_total,
       Inner_Rgn_Cnt,
       Inner_Rgn_Dur,
       Inner_Rgn_Dstn,
       Inter_Rgn_Cnt,
       Inter_Rgn_Dur,
       Inter_Rgn_Dstn,
       Inner_Mobile_Cnt,
       Inner_Mobile_Dur,
       Inner_Mobile_Dstn,
       Inter_Mobile_Cnt,
       Inter_Mobile_Dur,
       Inter_Mobile_Dstn,
       Ip_Conn_Ctc_Cnt,
       Ip_Conn_Ctc_Dur,
       Ip_Conn_Oth_Cnt,
       Ip_Conn_Oth_Dur,
       Dial_Conn_Ctc_Cnt,
       Dial_Conn_Ctc_Dur,
       Dial_Conn_Oth_Cnt,
       Dial_Conn_Oth_Dur,
       Tol_Cnt_Cnct_total,
       Tol_Dur_Fact_Cnct_total,
       Trd_Tol_Dms_Cnt,
       Trd_Tol_Dms_Dur,
       Trd_Tol_Dms_Dstn,
       Tra_Loc_Mbl_Cnt,