prc_init_lps.prc

数据迁移使用的ETL程序包

CREATE OR REPLACE PROCEDURE PRC_INIT_LPS(p_Release     varchar2,
                                         p_Domain      varchar2,
                                         p_City        varchar2,
                                         p_MultiStream number) as
  l_Flag             number(6) := 1;
  l_ProcessName      log_process_status.process_name%type;
  l_TableName        varchar2(20);
  v_NumAvgFl         NUMBER;
  v_TotalRec         NUMBER(10);
  v_MaxRec           number(10);
  v_MinRec           number(10);
  o_StartRec         number(10);
  o_EndRec           number(10);
  o_StreamTotal      number(10);
  l_CountStreamTotal number(10);
begin

  select decode(p_Domain,
                'CA',
                'SRC_CUST',
                'BA',
                'SRC_ACCT',
                'SI',
                'SRC_SERV',
                'PR',
                'SRC_PROD',
                'DIS',
                'SRC_DISCOUNT')
    into l_TableName
    from dual;

  /*    execute immediate 'select MAX(ROW_ID) - min(row_id) + 1, max(row_id), min(row_id) from ' ||
                  l_TableName || ' Where src_id = :1 or src_id = :2'
  into v_TotalRec, v_MaxRec, v_MinRec
  using p_City || '97', p_City || 'Bl';*/

  execute immediate 'select MAX(ROW_ID) from ' || l_TableName ||
                    ' Where src_id = :1 or src_id = :2'
    into v_MaxRec
    using p_City || '97', p_City || 'Bl';

  execute immediate 'select min(row_id) from ' || l_TableName ||
                    ' Where src_id = :1 or src_id = :2'
    into v_MinRec
    using p_City || '97', p_City || 'Bl';

  v_TotalRec := v_MaxRec - v_MinRec + 1;
  v_NumAvgFl := floor(v_TotalRec / p_MultiStream);
  while l_Flag <= p_MultiStream loop
    l_ProcessName := 'PRC_T_' || p_Domain || '_' || p_City || '_' ||
                     p_MultiStream || '_' || l_Flag;
  
    o_StartRec := v_NumAvgFl * (l_Flag - 1) + v_MinRec;
  
    IF l_Flag <> p_MultiStream THEN
      o_EndRec := v_NumAvgFl * l_Flag + v_MinRec - 1;
    ELSE
      o_EndRec := v_MaxRec;
    END IF;
  
    IF l_Flag = 1 then
      execute immediate 'select Count(1) from ' || l_TableName ||
                        ' where row_id >=:1  and row_id <= :2 '
        into o_StreamTotal
        using o_StartRec, o_EndRec;
    
      l_CountStreamTotal := o_StreamTotal;
    ELSIF l_Flag = p_MultiStream then
      execute immediate 'select Count(1) from ' || l_TableName ||
                        ' where row_id >=:1  and row_id <= :2 '
        into o_StreamTotal
        using o_StartRec, o_EndRec;
      l_CountStreamTotal := o_StreamTotal;
    END IF;
  
    insert into log_process_status
    values
      (l_ProcessName,
       o_StreamTotal,
       0,
       0,
       o_StartRec,
       o_EndRec,
       sysdate,
       null,
       p_Release,
       p_Domain,
       l_Flag,
       'RD',
       p_MultiStream,
       p_City,
       null,
       'TT');
  
    l_Flag := l_Flag + 1;
  end loop;
exception
  when others then
    rollback;
    dbms_output.put_line(substrb(sqlerrm, 1, 200));
end PRC_INIT_LPS;
/