两个数据表之间数据同步

type type_array is varray(100) of varchar2(1000); 
function sync_table(p_match varchar2, p_array type_array, p_src_alias varchar2, p_src_where varchar2) return varchar2;
procedure sync_etl_job;

function sync_table(p_match varchar2, p_array type_array, p_src_alias varchar2, p_src_where varchar2)
  return varchar2 is
  v_str_first  varchar(100);
  v_str_last   varchar(100);
  v_idx        integer;
  v_to_fields  clob;
  v_val_fields clob;
  v_set_vals   clob;
  v_sql        clob;
  v_to_table   varchar(50);
  v_to_pk      varchar(50);
  v_src_table  varchar(50);
  v_src_pk     varchar(50);
  
  begin
    v_idx := instr(p_match, ':');
    v_str_first := trim(substr(p_match, 0, v_idx-1));
    v_str_last := trim(substr(p_match, v_idx+1));
  
    v_idx := instr(v_str_first, '.');
    v_to_table := trim(substr(v_str_first, 0, v_idx-1));
    v_to_pk := trim(substr(v_str_first, v_idx+1));
  
    v_idx := instr(v_str_last, '.');
    v_src_table := trim(substr(v_str_last, 0, v_idx-1));
    v_src_pk := trim(substr(v_str_last, v_idx+1));
  
    for i in 1..p_array.count loop  
      v_idx := instr(p_array(i), ':');
      if v_idx > 0 then
        v_str_first := trim(substr(p_array(i), 0, v_idx-1));
        v_str_last := trim(substr(p_array(i), v_idx+1));
        v_to_fields := v_to_fields || v_str_first || ',';
        v_val_fields := v_val_fields || v_str_last || ',';
        if upper(v_str_first) != upper(v_to_pk) then
          v_set_vals := v_set_vals || v_to_table || '.' || v_str_first || '=' || v_str_last || ',';
        end if;
      end if; 
    end loop;
  
    v_to_fields := rtrim(v_to_fields, ',');
    v_val_fields := rtrim(v_val_fields, ',');
    v_set_vals := rtrim(v_set_vals, ',');
  
    v_sql := '
    MERGE INTO '|| v_to_table ||  ' 
      USING (select * from ' || v_src_table || ' ' || p_src_alias || ' where ' || v_src_pk || ' is not null and ' || p_src_where || ') ' || p_src_alias || ' 
      ON (' || v_to_table || '.' || v_to_pk || '=' || p_src_alias || '.' || v_src_pk || ')
      WHEN NOT MATCHED THEN
        INSERT(' || v_to_fields || ')
        VALUES(' || v_val_fields || ')
      WHEN MATCHED THEN
        UPDATE SET ' || v_set_vals;
      
  dbms_output.put_line(v_sql);
  execute immediate v_sql;


  return 'Y';
  
  exception
    when others then
      return 'exception(' || v_sql || ')';
end sync_table;

procedure sync_etl_job is
  v_result  varchar2(5000);
  v_match  varchar2(500);
  v_src_where varchar2(500);
  v_src_alias varchar2(10);
  var_array type_array;
begin
  v_match := 'sys_sap_job_temp.JOB_ID: sys_sap_etl_job_temp.JOB_ID';
  v_src_alias := 'np';
  v_src_where := 'np.batchnumber = ''1''';
  var_array := cux_sap_process_comm_pkg.type_array(
    'DATE_FROM: to_date(np.DATE_FROM,''yyyy-MM-dd'')',
    'DATE_TO: to_date(np.DATE_TO,''yyyy-MM-dd'')',
    'JOB_ID: np.JOB_ID',
    'JOB_NAME: np.JOB_NAME',
    'MANAGE_LEVEL: np.MANAGE_LEVEL'
  );
  v_result := cux_sap_process_comm_pkg.sync_table(v_match, var_array, v_src_alias, v_src_where);
  dbms_output.put_line(v_result);
  if 'Y' = v_result then
    commit;
  end if;
  rollback;
  
  exception
    when others then 
      rollback;
end sync_etl_job;