Лучший способ генерировать уникальные и последовательные числа в Oracle

Пробелы появляются, если транзакция использует порядковый номер, но затем откатывается.

Возможно, ответ заключается в том, чтобы не назначать номер счета-фактуры до тех пор, пока счет-фактуру нельзя будет отменить. Это сводит к минимуму (но, вероятно, не устраняет) возможность возникновения пробелов.

Я не уверен, что есть какой-либо быстрый или простой способ гарантировать отсутствие пробелов в последовательности - сканирование MAX, добавление одного и вставка, вероятно, наиболее близкая к безопасности, но не рекомендуется из соображений производительности (и трудностей с параллелизмом). ), и этот метод не обнаружит, если последний номер счета-фактуры был назначен, а затем удален и переназначен.

Можете ли вы каким-то образом объяснить пробелы — определив, какие номера счетов-фактур были «использованы», но каким-то образом «не стали постоянными»? Может ли автономная транзакция помочь в этом?

Другая возможность - предполагая, что разрывы относительно немногочисленны и далеко друг от друга.

Создайте таблицу, в которую записываются порядковые номера, которые необходимо повторно использовать, прежде чем будет получено новое значение последовательности. Обычно она была бы пустой, но какой-то процесс, который запускается каждую... минуту, час, день... проверяет наличие пробелов и вставляет в эту таблицу пропущенные значения. Все процессы сначала проверяют таблицу пропущенных значений и, если они есть, используют значение оттуда, выполняя медленный процесс обновления таблицы и удаления строки, которую они используют. Если таблица пуста, возьмите следующий порядковый номер.

Не очень приятно, но отделение «выставления номеров счетов» от «сканирования пропущенных значений» означает, что даже если процесс выставления счетов завершится ошибкой для какого-либо потока, когда он использует одно из пропущенных значений, это значение будет повторно обнаружено как отсутствующее и повторно переиздается в следующий раз - повторяется до тех пор, пока какой-либо процесс не завершится успехом.

• Вариант 1 всегда может дать сбой в среде с параллельными пользователями.
• Вариант 2 будет работать, но будет ограничивать масштабируемость — обязательная ссылка на Tom Kyte: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1508205334476

Как он рекомендует, вам действительно следует пересмотреть необходимость требования отсутствия пробелов.

Сохранить текущую последовательность — вы можете использовать следующее, чтобы сбросить значение до максимального значения, которое в настоящее время хранится в таблице (таблицах):

-- --------------------------------
-- Purpose..: Resets the sequences 
-- --------------------------------

DECLARE
  -- record of temp data table
  TYPE data_rec_type IS RECORD(
    sequence_name VARCHAR2(30),
    table_name    VARCHAR2(30),
    column_name   VARCHAR2(30));

  -- temp data table
  TYPE data_table_type IS TABLE OF data_rec_type INDEX BY BINARY_INTEGER;

  v_data_table data_table_type;
  v_index      NUMBER;
  v_tmp_id     NUMBER;

  -- add row to temp table for later processing
  --
  PROCEDURE map_seq_to_col(in_sequence_name VARCHAR2,
                           in_table_name    VARCHAR2,
                           in_column_name   VARCHAR2) IS
    v_i_index NUMBER;
  BEGIN
    v_i_index := v_data_table.COUNT + 1;
    v_data_table(v_i_index).sequence_name := in_sequence_name;
    v_data_table(v_i_index).table_name := in_table_name;
    v_data_table(v_i_index).column_name := in_column_name;
  END;

  /**************************************************************************
      Resets a sequence to a given value
  ***************************************************************************/
  PROCEDURE reset_seq(in_seq_name VARCHAR2, in_new_value NUMBER) IS

    v_sql       VARCHAR2(2000);
    v_seq_name  VARCHAR2(30) := in_seq_name;
    v_reset_val NUMBER(10);
    v_old_val   NUMBER(10);
    v_new_value NUMBER(10);

  BEGIN

    -- get current sequence value

    v_sql := 'SELECT ' || v_seq_name || '.nextval FROM DUAL';
    EXECUTE IMMEDIATE v_sql
      INTO v_old_val;

    -- handle empty value
    v_new_value := in_new_value;
    if v_new_value IS NULL then
      v_new_value := 0;
    END IF;

    IF v_old_val <> v_new_value then    
      IF v_old_val > v_new_value then
        -- roll backwards
        v_reset_val := (v_old_val - v_new_value) * -1;
      elsif v_old_val < v_new_value then
        v_reset_val := (v_new_value - v_old_val);
      end if;

      -- make the sequence rollback to 0 on the next call
      v_sql := 'alter sequence ' || v_seq_name || ' increment by ' ||
           v_reset_val || ' minvalue 0';
      EXECUTE IMMEDIATE (v_sql);

      -- select from the sequence to make it roll back
      v_sql := 'SELECT ' || v_seq_name || '.nextval FROM DUAL';
      EXECUTE IMMEDIATE v_sql
        INTO v_reset_val;

      -- make it increment correctly again
      v_sql := 'alter sequence ' || v_seq_name || ' increment by 1';
      EXECUTE IMMEDIATE (v_sql);

      -- select from it again to prove it reset correctly.
      v_sql := 'SELECT ' || v_seq_name || '.currval FROM DUAL';
      EXECUTE IMMEDIATE v_sql
        INTO v_reset_val;

    END IF;

    DBMS_OUTPUT.PUT_LINE(v_seq_name || ': ' || v_old_val || ' to ' ||
                     v_new_value);
  END;

  /*********************************************************************************************
    Retrieves a max value for a table and then calls RESET_SEQ.
  *********************************************************************************************/
  PROCEDURE reset_seq_to_table(in_sequence_name VARCHAR2,
                               in_table_name    VARCHAR2,
                               in_column_name   VARCHAR2) IS

    v_sql_body  VARCHAR2(2000);
    v_max_value NUMBER;

      BEGIN

    -- get max value in the table
    v_sql_body := 'SELECT MAX(' || in_column_name || '+0) FROM ' ||
              in_table_name;
    EXECUTE IMMEDIATE (v_sql_body)
      INTO v_max_value;

    if v_max_value is null then
      -- handle empty tables
      v_max_value := 0;
    end if;

    -- use max value to reset the sequence
    RESET_SEQ(in_sequence_name, v_max_value);

  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Failed to reset ' || in_sequence_name ||
                       ' from ' || in_table_name || '.' ||
                       in_column_name || ' - ' || sqlerrm);
  END;

BEGIN
  --DBMS_OUTPUT.ENABLE(1000000);

  -- load sequence/table/column associations

  /***** START SCHEMA CUSTOMIZATION *****/
  map_seq_to_col('Your_SEQ',  
                 'your_table',
                 'the_invoice_number_column');

  /***** END SCHEMA CUSTOMIZATION *****/

  -- iterate all sequences that require a reset
  FOR v_index IN v_data_table.FIRST .. v_data_table.LAST LOOP

    BEGIN
      RESET_SEQ_TO_TABLE(v_data_table(v_index).sequence_name,
                         v_data_table(v_index).table_name,
                         v_data_table(v_index).column_name);
    END;
  END LOOP;

END;
/

-- -------------------------------------------------------------------------------------
-- End of Script.
-- -------------------------------------------------------------------------------------

Примером является анонимная sproc — измените ее на правильные процедуры в пакете и вызовите ее перед вставкой нового счета, чтобы сохранить согласованность нумерации.

Я думаю, вы обнаружите, что использование MAX() существующих номеров чревато новой и интересной проблемой — дубликаты могут возникать, если одновременно создается несколько счетов. (Не спрашивайте меня, откуда я знаю...).

Возможное решение состоит в том, чтобы получить первичный ключ в таблице INVOICE из последовательности, но это НЕ должно быть номером счета. После правильного и правильного создания счета-фактуры и после того момента, когда исключение или прихоть пользователя могут привести к прекращению создания счета-фактуры, вы переходите ко второй последовательности, чтобы получить порядковый номер, который представлен как «номер счета-фактуры». . Это означает, что у вас будет два уникальных, неповторяющихся числа в таблице INVOICE, и очевидное число (INVOICE_NO) не будет первичным ключом (но оно может и должно быть УНИКАЛЬНЫМ), так что здесь закрадывается немного зла, но альтернатива — создать строку СЧЕТА с одним значением в первичном ключе, а затем изменить первичный ключ после создания СЧЕТА — просто слишком зла для слов. :-)

Делитесь и наслаждайтесь.

Если вы действительно хотите, чтобы не было пробелов, вам нужно полностью сериализовать доступ, иначе всегда будут пробелы. Причинами пробелов являются:

• откат
• выключение прерывание

Непонятно, что вы подразумеваете под «из-за возможных исключений». Если вы хотите, чтобы число НЕ увеличивалось, если ваша транзакция в конечном итоге откатывается, то ПОСЛЕДОВАТЕЛЬНОСТЬ не будет работать для вас, потому что, насколько я знаю, как только NEXTVAL запрашивается из последовательности, позиция последовательности увеличивается, и откат не изменит ее.

Если это действительно требование, вам, вероятно, придется прибегнуть к хранению текущего счетчика в отдельной таблице, но остерегайтесь одновременных обновлений - как с точки зрения «потерянных обновлений», так и с точки зрения масштабируемости.

Я сталкивался с этой проблемой раньше. В одном случае нам удалось убедить бизнес признать, что «настоящие» счета-фактуры могут иметь пробелы, и мы написали задание, которое выполнялось каждый день, чтобы «заполнить» пробелы «недействительными» счетами-фактурами для целей аудита.

На практике, если мы поместим NOCACHE в последовательность, количество пробелов будет относительно небольшим, поэтому аудиторы обычно будут довольны, пока их запросы по «аннулированным» счетам не будут возвращать слишком много результатов.

Возможно, вам придется немного переосмыслить свой процесс и разбить его на несколько шагов. Сделайте один шаг, не связанный с транзакцией, создайте счет-заполнитель (он не будет включен в транзакцию, это должно устранить пробелы), а затем в рамках транзакции выполните остальную часть своего бизнеса. Я думаю, именно так мы делали это в системе, с которой я застрял много лет назад, но я не могу вспомнить — я просто помню, что это было «странно».

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

Ссылка dpbradley в № 2 звучит как лучший выбор. Том сохраняет транзакцию с вызывающей стороной, если вы не хотите, чтобы вы могли сделать это автономной транзакцией, например:

create or replace 
function getNextInvoiceNumber()
return number is
   l_invoicenum     number;

   pragma autonomous_transaction;
   begin
      update docs_numbers
         set last_invoice = last_invoice + 1
      returning last_invoice 
      into l_invoicenum;
      commit;

      return l_invoicenum;

   exception
      when others then
         rollback;
         raise;
end;

Что мы делаем, так это присваиваем транзакции порядковый номер, а затем, когда объект, который мы обрабатываем, завершается, мы присваиваем постоянный номер (также порядковый номер). Хорошо работает для нас.

С уважением
К