zusammenfassen - sql update mehrere zeilen gleichzeitig




Der beste Weg, um einzigartige und fortlaufende Nummern in Oracle zu generieren (7)

Behalten Sie die aktuelle Sequenz bei - Sie können den folgenden Wert verwenden, um den Wert auf das Maximum der aktuell in den Tabellen gespeicherten Werte zurückzusetzen:

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

Bei dem Beispiel handelt es sich um einen anonymen Sproc - ändern Sie ihn als ordnungsgemäße Prozedur in einem Paket und rufen Sie ihn vor dem Einfügen einer neuen Rechnung auf, um die Nummerierung konsistent zu halten.

Ich muss eindeutige und fortlaufende Nummern (für die Verwendung auf einer Rechnung) schnell und zuverlässig generieren. Derzeit verwenden Sie eine Oracle-Sequenz, aber in einigen Fällen sind generierte Zahlen aufgrund von möglichen Ausnahmen nicht aufeinander folgend .

Ich dachte ein paar Lösungen, um dieses Problem zu lösen, aber keiner von ihnen überzeugte mich. Welche Lösung empfehlen Sie?

  1. Verwenden Sie eine Auswahl max ()

    SELECT MAX (NVL (doc_num, 0)) +1 FROM invoices
  2. Verwenden Sie eine Tabelle, um die letzte für die Rechnung generierte Nummer zu speichern.

    UPDATE docs_numbers
        SET last_invoice = last_invoice + 1
  3. Eine andere Lösung?


Die Lücken werden angezeigt, wenn eine Transaktion eine Sequenznummer verwendet, aber dann zurückgesetzt wird.

Vielleicht lautet die Antwort nicht, die Rechnungsnummer zuzuweisen, bis die Rechnung nicht zurückgesetzt werden kann. Dies minimiert (aber wahrscheinlich nicht) die Möglichkeiten von Lücken.

Ich bin mir nicht sicher, ob es einen schnellen oder einfachen Weg gibt, um Lücken in der Sequenz zu vermeiden - nach MAX zu suchen, eins hinzuzufügen und das Einfügen ist wahrscheinlich das sicherste, wird aber aus Performance-Gründen (und Schwierigkeiten mit Parallelität) nicht empfohlen ) und die Technik erkennt nicht, ob die letzte Rechnungsnummer zugewiesen, dann gelöscht und neu zugewiesen wurde.

Können Sie irgendwie auf Lücken hinweisen - indem Sie feststellen, welche Rechnungsnummern "verwendet" wurden, aber "nicht dauerhaft"? Könnte eine autonome Transaktion dabei helfen?

Eine andere Möglichkeit - unter der Annahme, dass Lücken relativ gering sind.

Erstellen Sie eine Tabelle mit Sequenznummern, die erneut verwendet werden müssen, bevor ein neuer Sequenzwert abgerufen wird. Normalerweise wäre es leer, aber ein Prozess, der alle ... Minuten, Stunden, Tage ausgeführt wird ... prüft auf Lücken und fügt die fehlenden Werte in diese Tabelle ein. Alle Prozesse überprüfen zuerst die Tabelle der verpassten Werte, und falls solche vorhanden sind, verwenden Sie einen Wert von dort, durchlaufen den langsamen Prozess der Aktualisierung der Tabelle und entfernen die Zeile, die sie verwenden. Wenn die Tabelle leer ist, nehmen Sie die nächste Sequenznummer.

Nicht sehr angenehm, aber die Entkopplung von "Rechnungsnummern ausgeben" und "Nach fehlenden Werten suchen" bedeutet, dass der Rechnungsvorgang für einen Thread fehlschlägt, wenn er einen der fehlenden Werte verwendet beim nächsten Mal neu ausgegeben - Wiederholen, bis ein Prozess erfolgreich ist.


Ich bin schon einmal auf dieses Problem gestoßen. In einem Fall waren wir in der Lage, das Unternehmen davon zu überzeugen, dass "echte" Rechnungen Lücken aufweisen könnten, und wir schrieben einen Job, der jeden Tag dazu diente, Lücken mit "ungültigen" Rechnungen für Prüfungszwecke auszufüllen.

Wenn wir in der Praxis NOCACHE auf die Sequenz setzen, wäre die Anzahl der Lücken relativ gering, so dass die Auditoren normalerweise glücklich sind, solange ihre Abfrage der "ungültigen" Rechnungen nicht zu viele Ergebnisse liefert.


Ich denke, Sie werden feststellen, dass die Verwendung der MAX () der vorhandenen Nummern zu einem neuen und aufregenden Problem führt - es können doppelte Daten entstehen, wenn mehrere Rechnungen gleichzeitig erstellt werden. (Frag mich nicht, wie ich das weiß ...).

Eine mögliche Lösung besteht darin, den Primärschlüssel in Ihrer Rechnungstabelle aus einer Sequenz abzuleiten, aber dies muss NICHT die Rechnungsnummer sein. Nachdem Sie Ihre Rechnung richtig und ordnungsgemäß erstellt haben und nach dem Punkt, an dem eine Ausnahme oder ein Wunsch des Benutzers dazu führen könnte, dass die Rechnung erstellt wird, wechseln Sie zu einer zweiten Sequenz, um die laufende Nummer als Rechnungsnummer zu erhalten . Das bedeutet, dass Sie zwei eindeutige, sich nicht wiederholende Zahlen in Ihrer Rechnungstabelle haben werden, und die offensichtliche (INVOICE_NO) wird nicht der Primärschlüssel sein (aber es kann und sollte UNIQUE sein), so dass ein bisschen Böses hereinkriecht, aber Die Alternative - die INVOICE-Zeile mit einem Wert im Primärschlüssel zu erstellen und dann den Primärschlüssel zu ändern, nachdem das INVOICE erstellt wurde - ist einfach zu schlecht für Wörter. :-)

Teile und genieße.


Wenn Sie wirklich keine Lücken haben wollen, müssen Sie den Zugriff vollständig serialisieren, sonst wird es immer Lücken geben. Die Gründe für Lücken sind:

  • Rollback
  • Herunterfahren abbrechen

Wir stellen eine Sequenznummer für die Transaktion aus, und wenn der Artikel, an dem wir gerade arbeiten, abgeschlossen ist, geben wir eine permanente Nummer (auch eine Sequenz) aus. Funktioniert gut für uns.

Grüße
K






plsql