Beste Weg zur Generierung von Eindeutigen und fortlaufenden Nummern in Oracle

Den Lücken erscheinen, wenn eine Transaktion verwendet wird, eine Sequenz-Nummer, ist aber dann rollte zurück.

Vielleicht die Antwort ist nicht berechtigt, die Rechnungsnummer, bis die Rechnung kann nicht zurückgesetzt werden. Dies minimiert (aber wahrscheinlich nicht beseitigen) die Möglichkeiten der Lücken.

Ich bin mir nicht sicher, dass es keine rasche oder einfache Weise zu gewährleisten, dass keine Lücken in der Sequenz - Scans für MAX, das hinzufügen und einfügen, dass ist wahrscheinlich die am nächsten zu sichern, aber wird nicht empfohlen, aus performance-Gründen (Schwierigkeiten mit der Gleichzeitigkeit) und die Technik nicht erkennen, ob das die Letzte Rechnungsnummer zugewiesen ist, gelöscht und neu zugewiesen.

Können Sie das Konto für die Lücken, die irgendwie - durch die Identifizierung, die Rechnung zahlen wurden "gebraucht", aber "nicht dauerhaft" irgendwie? Könnte eine autonome Transaktion helfen dabei, dass?

Andere Möglichkeit - vorausgesetzt, die Lücken sind relativ wenige und weit zwischen.

Erstellen einer Tabelle, die Datensätze Sequenznummern, müssen wiederverwendet werden, bevor eine neue Sequenz Wert ist packte. Normalerweise, es würde leer sein, aber ein gewisser Prozess, der ausgeführt wird ... minute, Stunde, Tag ... überprüft Lücken und fügt die vergessene Werte in dieser Tabelle. Alle Prozesse, überprüfen Sie zuerst die Tabelle der vergessene Werte, und wenn es irgendeine vorhanden ist, verwenden Sie den Wert von dort aus, gehen durch den langsamen Prozess der Aktualisierung der Tabelle und entfernen Sie die Zeile, die Sie verwenden. Wenn die Tabelle leer ist, dann schnappen Sie sich die nächste Sequenz-Nummer.

Nicht sehr angenehm, aber die Entkopplung von 'Ausstellung Rechnung zahlen' von 'scan für verpasste Werte' bedeutet, dass selbst wenn die Rechnungsstellung Prozess schlägt fehl, wenn einige thread, wenn er mit einer der vergessene Werte, wird dieser Wert wiederentdeckt zu fehlen und wieder neu aufgelegt Nächstes mal - wiederholen, bis ein gewisser Prozess gelingt es.

Halten die aktuelle Reihenfolge - können Sie die folgenden zurücksetzen, um den Wert auf das maximum von dem, was derzeit in der Tabelle gespeichert(en):

-- --------------------------------
-- 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.
-- -------------------------------------------------------------------------------------

Beispiel wird eine anonyme sproc - ändern Sie ihn auf ordnungsgemäße Verfahren in einem Paket, und nennen Sie es vor dem einfügen einer neuen Rechnung zu halten, die Nummerierung konsequent.

Ich denke, Sie werden feststellen, dass mit Hilfe der MAX () - von den vorhandenen zahlen ist anfällig für eine neue und spannende problem - Duplikate können auftreten, wenn mehrere Rechnungen erstellt werden, zur gleichen Zeit. (Fragt mich nicht wie ich weiß...).

Eine mögliche Lösung ist die Ableitung der primären Schlüssel, der auf Ihrer RECHNUNG Tabelle aus einer Sequenz, aber haben diese NICHT die Rechnungsnummer. Nach korrekt und ordnungsgemäß erstellen Ihre Rechnung, und nach dem Punkt, an dem eine Ausnahme oder Benutzer Laune konnte, dass die Erstellung der Rechnung gekündigt werden, Sie gehen, um eine zweite Sequenz, um die fortlaufende Nummer, die als "die" Rechnungsnummer. Dies bedeutet, dass Sie zwei eindeutige, nicht wiederholte zahlen auf Ihre RECHNUNG Tisch, und die offensichtlich einen (INVOICE_NO) wird nicht der Primärschlüssel (aber es kann und sollte EINDEUTIG sein), so dass es ein wenig böse schleichen, aber die alternative - die zur Erstellung der RECHNUNG Zeile mit einem Wert in der Primärschlüssel, dann ändern Sie die primäre Taste, nachdem Sie die RECHNUNG erstellt wird, ist einfach zu böse für die Worte. 🙂

Teilen und genießen.

Ich habe über dieses problem vor. In einem Fall konnten wir überzeugen, das Geschäft zu akzeptieren, dass "echte" Rechnungen könnte Lücken haben, und wir schrieb einen job, der lief jeden Tag zu "füllen" die Lücken mit "void" - Rechnungen für audit-Zwecke.

In der Praxis, wenn wir NOCACHE auf die Reihenfolge, die Anzahl der Lücken wäre relativ gering, so ist die Revisionsstelle wird in der Regel gerne, solange Ihre Abfrage über den "void" - Rechnungen nicht wieder zu viele Ergebnisse.

dpbradley link in #2 klingt wie Ihre beste Wette. Tom hält die transactionality mit dem Anrufer, wenn Sie nicht möchten, dass Sie es schaffen könnte eine autonome Transaktion wie folgt:

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;