Доступные версии документации

Пакетное добавление

             

Возможны два режима добавления строк в таблицу: обычный и пакетный.

В обычном режиме добавление строки выполняется с помощью SQL-оператора INSERT. Строки добавляются по одной при каждом исполнении данного оператора.

В пакетном режиме строки добавляются за одну операцию в том количестве, в каком они заданы во входном буфере. Пакетный режим рекомендуется использовать при загрузке больших объемов данных.

Для выполнения пакетного добавления внутри хранимой процедуры:

  1. определить курсор, в котором имена полей соответствуют именам (и типам) полей загружаемой таблицы. Если имя поля таблицы содержит нестандартные символы, совпадает с ключевым словом и т.д. (т.е. требует квотации), в описании поля курсора можно явно указать имя, какое должно использоваться. Для этого после типа столбца можно указать ключевое слово column и строку в кавычках, например:

    cursor(
      i int; // будет соответствовать столбцу "I"
      l char(20) column "lowercase" // будет соответствовать столбцу "lowercase"
      ins date column "INSERT" // будет соответствовать столбцу "INSERT"
    );
  2. перед добавлением выдать оператор:

    start append into <таблица> from <курсор>;

    где:

    <таблица> – имя таблицы;

    <курсор> – курсорная переменная.

    Выполнение этого оператора внутри процедуры приводит к подаче SQL-запроса start append into byte (<список столбцов> согласно именам полей в таблице).

    В случае ошибки при выполнении оператора формируется исключение с кодом завершения.

    В случае если <курсор> – недопустимое выражение, вызывается исключение BADPARAM.

    При попытке выполнить start append или execute, когда предыдущий start append не был завершен при помощи end append, возникает исключение APPENDACTIVE.

  3. чтобы добавить строку, надо заполнить поля соответствующими значениями и выдать оператор:

    putm <буфер>;

    В качестве <буфера> можно использовать любую переменную типа курсор, структура которой совпадает со структурой переменной, использованной в операторе start append. В случае несовпадения возникнет исключение BADCURSOR.

    Этот оператор накапливает данные во внутренней странице пакета (которая выделяется оператором start append) и, если пакет заполняется, загружает его в таблицу.

    Если в процессе формирования пакета и загрузки его в таблицу возникает ошибка, то вырабатывается исключение с кодом завершения, который можно получить с помощью функции errcode().

    Если возникает ошибка, внутренняя страница продолжает содержать данные, которые не удалось добавить, а новая запись игнорируется.

    Чтобы очистить внутреннюю страницу, можно использовать функцию

    clearPutm()

    Узнать количество записей во внутренней странице (которые еще не занесены реально в БД) можно при помощи функции

    int getPutmRecs()

    Эту функцию можно использовать, в частности, после возникновения ошибки в putm. Ошибка означает, что не все записи из внутренней страницы добавлены. Функция

    getPutmRecs()

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

    Можно потребовать принудительно сбросить записи из внутренней страницы в БД при помощи функции

    flushPutm()

    В случае успеха внутренняя страница освобождается для последующей нормальной работы.

    В случае ошибки обработка внутренней страницы аналогична выполнению оператора putm, попытавшегося сбросить заполненную внутреннюю страницу и столкнувшегося с ошибкой: внутренняя страница не очищается. Очистить ее всегда можно при помощи clearPutm().

    Таким образом, вызов подряд putm и flushPutm() приведет, фактически, к добавлению строк по одной через механизм пакетного добавления.

    Если перед подачей putm вызовом функций getPutmRecs, clearPutm и flushPutm не был выполнен оператор start append, или он завершился неудачно, то при попытке выполнить putm возникнет исключение APPENDNOTSTARTED.

  4. по окончании добавления надо выдать оператор end append.

    Если перед подачей end append не было вызвано оператора start append, или он завершился неудачно, то при попытке выполнить end append возникнет исключение APPENDNOTSTARTED.

    После каждого start append должен вызываться end append, прежде чем делать другие execute или start append!

  5. все операции putm выполняются по одному курсору, по тому же, что и execute в процедурах ("курсору по умолчанию").

    Соответственно, между start append и end append нельзя использовать другие start append или execute. Если пытаться сделать это, возникнет исключение QUERYWHENAPPEND (если его не обработать, последующая попытка выполнения SQL-оператора приведет к получению кода завершения 1013 – неверная последовательность команд).

Примечание

При пакетной вставке данных триггеры, настроенные на вставку данных, срабатывать не будут.

Пример

create or replace procedure "PM"() result char(20) for debug
declare
  var c cursor(i int column "start",
               si smallint, bi bigint,
               c char(20), vc varchar(30),
               d date column "THIS IS DATE",
               r real, db double, dc numeric,
               l bool,
               b byte(10), vb varbyte(10)
  );
  var i int;
  exception APPENDNOTSTARTED for APPENDNOTSTARTED;
  exception QUERYWHENAPPEND for QUERYWHENAPPEND;
  exception APPENDACTIVE for APPENDACTIVE;
  exception AAA for 116;
code
  execute direct "drop table pm;";
  execute "create table pm("
                 "\"start\" int, si smallint, bi bigint,"
                 "c char(20), vc varchar(30), "
                 "\"THIS IS DATE\" date,"
                 "r real, db double, dc numeric,"
                 "l boolean,"
                 "b byte(10), vb varbyte(10));";
  start append into "PM" from c;
  i := 1;
  while i < 32001 loop
    c.i := i;
    c.si := i;
    c.bi := i*100;
    c.c := "string value "+tochar(i);
    c.vc := c.c + " ";
    c.d := sysdate() + i;
    c.r := i/10.0;
    c.db := i/100.0;
    c.dc := i/1000.0;
    c.l := mod(i, 10) = 0;
    asc(tochar(i), c.vb);
    asc(tochar(sysdate()+i), c.b);
    putm c;
    if errcode() <> 0 then
      return "PUTM error: "+tochar(errcode());
    endif
    i := i+1;
  endloop
  end append;
  if errcode() <> 0 then
    return "Error "+tochar(errcode());
  endif
  return "Ok";
exceptions
  when AAA then
    return "PUTM failed";
  when APPENDNOTSTARTED then
    return "APPENDNOTSTARTED";
  when APPENDACTIVE then
    return "APPENDACTIVE";
  when QUERYWHENAPPEND then
    return "QUERYWHENAPPEND";
end;

При сравнении этой процедуры с процедурой, которая добавляет те же строки не в пакетном режиме, а по одной (с помощью insert), были получены следующие характеристики.

Время работы:
10 000 строк:
insert: 33 с.
putm: 11 с.
32 000 строк:
insert: 2 мин.
putm: 36 с.

Если добавлять меньше столбцов, выигрыш от putm более очевидный.

Для 4 столбцов и 10 000 строк:
insert: 26 с.
putm: 5 с.
Заметили ошибку?
Выделите текст и нажмите Ctrl + Enter