Блог

Мне нужно получить данные, которые одновременно записывают данные csv в файл и обновляют таблицу с помощью этой же записи. Этот курсор потенциально может найти 100-300 тыс. записей.

Есть ли более эффективный способ сделать это вместо зацикливания? Я думал о выполнении вставки с выбором вне цикла, но нет способа узнать, что каждая запись была успешно записана в файл.

 DECLARE
...

   file_output VARCHAR2 (3000);
   col2 column2%type;
   col3 column3%type;

 CURSOR my_cursor
   IS
    select
      csv_val1 || ',' || csv_val2|| ',' || csv_val3,
      column2,
      column3
    from my_cursor_table

...

BEGIN

...
   FETCH my_cursor INTO file_output,col2,col3;

   WHILE my_cursor%FOUND
   LOOP
      BEGIN
         UTL_FILE.PUT_LINE (ID, file_output);
         insert into my_insert_table values(col2,col3);
..

END;
  

Вы можете:

• bulk collect запрос в массив с ограничением
• Перебирайте массив, записывая каждый элемент в файл
• Оберните utl_file вызов в обработчик исключений, который регистрирует ошибку и удаляет этот элемент из массива
• Используйте forall i in indices of только для вставки значений, которые были успешными

Это должно получиться заметно быстрее, чем обработка одной строки. В этом простом примере обработка 100 тыс. строк:

• Обработка строки за строкой заняла ~ 15 секунд
• Массовый сбор / forall занял ~ 1,5 с

Таким образом, примерно в 10 раз быстрее:

 create table t (
  c1 int, c2 int
);

set timing on
declare
  cursor cur is
    select level c1, mod ( level, 10 ) c2
    from   dual
    connect by level <= 100000;
  rec cur%rowtype;
  f utl_file.file_type;
begin
  f := utl_file.fopen ('TMP', 'test.csv', 'w');
  open cur;
  loop
    fetch cur into rec;
    exit when cur%notfound;
    
    begin 
      utl_file.put_line ( f, rec.c1 || ',' || rec.c2 );
      insert into t values ( rec.c1, rec.c2 );
    exception
      when others then
        dbms_output.put_line( 'Error logging' );
    end;
  end loop;
  close cur;
  utl_file.fclose(f);
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:15.192

declare
  cursor cur is
    select level c1, mod ( level, 10 ) c2
    from   dual
    connect by level <= 100000;
  type rec_arr is table of cur%rowtype
    index by pls_integer;
  rec rec_arr;
  f utl_file.file_type;
begin
  f := utl_file.fopen ('TMP', 'test.csv', 'w');
  open cur;
  loop
    fetch cur bulk collect into rec
    limit 100;
    exit when rec.count = 0;
    
    for i in 1 .. rec.count loop
      begin
        utl_file.put_line ( f, rec(i).c1 || ',' || rec(i).c2 );
      exception
        when others then
          dbms_output.put_line( 'Error logging' );
          rec.delete(i);
      end;
    end loop;
    forall i in indices of rec
      insert into t values ( rec(i).c1, rec(i).c2 );
  end loop;
  close cur;
  utl_file.fclose(f);
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.578
  

Обратите внимание, что это не обрабатывает случай, когда запись выполняется успешно, но вставка завершается неудачей…

Чтобы обойти это, вы можете сделать что-то вроде:

• Вставьте строки (с помощью forall ... save exceptions или insert ... log errors )
• Напишите строки, которые завершились успешно
• Удалите строки, которые не удалось записать в файл