Meilleure façon de générer des nombres uniques et consécutifs à Oracle

Gardez la séquence actuelle - Vous pouvez utiliser ce qui suit pour réinitialiser la valeur au maximum de ce qui est actuellement stocké dans la table (s):

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

• L'option 1 peut toujours être faite pour échouer une certaine manière dans un environnement avec simultanément Utilisateurs.
• l'option 2 fonctionnera, mais limitera l'évolutivité - obligatoire Tom Kyte référence: http://asktom.oracle .com / pls / asktom / f? p = 100: 11: 0 ::::: p11_question_id: 1508205334476

  Comme il le recommande, vous devriez vraiment passer en revue la nécessité de la nécessité "Pas de lacunes".

Ce n'est pas clair ce que vous entendez par 'à cause d'exceptions pouvant survenir ". Si vous souhaitez que le numéro ne soit pas incrémenté si votre transaction est finalement renvoyée, la séquence ne fonctionnera pas pour vous, car autant que je sache, une fois que NextVal est demandé à la séquence, la position de séquence est incrémentée et la restauration ne l'inverse pas.

S'il s'agit bien d'une exigence, vous devriez probablement avoir recours à stocker du comptoir actuel dans une table séparée, mais méfiez-vous des mises à jour simultanées - de la «mise à jour perdue» et de l'évolutivité prospective.

Les lacunes apparaissent si une transaction utilise un numéro de séquence mais est ensuite roulée en arrière.

Peut-être que la réponse est de ne pas attribuer le numéro de facture tant que la facture ne peut pas être renvoyée. Cela minimise (mais n'élimine probablement pas) les possibilités des lacunes.

Je ne suis pas sûr qu'il y ait un moyen swift ou facile d'assurer aucune lacune dans la séquence - balayage pour max, en ajoutant un et l'insertion est probablement la plus proche de la sécurité, mais n'est pas recommandée pour des raisons de performance (et Difficultés avec la concurrence) et la technique ne détectera pas si le dernier numéro de facture est attribué, puis supprimé et réaffecté.

Pouvez-vous rendre compte des lacunes en quelque sorte - en identifiant les numéros de facturation "utilisés" mais "non rendu permanent" d'une manière ou d'une autre? Une transaction autonome pourrait-elle aider à le faire?

Une autre possibilité - en supposant que les lacunes soient relativement peu nombreuses entre.

Créer une table qui enregistre des numéros de séquence qui doivent être réutilisés avant la prise d'une nouvelle valeur de séquence. Normalement, il serait vide, mais un processus qui fonctionne toutes les minutes, heure, jour ... vérifie les lacunes et insère les valeurs manquées dans cette table. Tous les processus vérifient d'abord le tableau des valeurs manquées et s'il y en a un présent, utilisez une valeur à partir de là, en passant par le processus lent de mise à jour de la table et de supprimer la ligne qu'ils utilisent. Si la table est vide, puis saisissez le numéro de séquence suivant.

Pas très agréable, mais le découplage des «numéros de facturation émettant» de «numérisation pour les valeurs manquées» signifie que même si le processus de facturation échoue à un fil lorsqu'il utilise l'une des valeurs manquées, cette valeur sera redécouverte vers être manquant et réémayé la prochaine fois, répétant jusqu'à ce que certains processus réussissent avec elle.

Vous devrez peut-être repenser votre processus légèrement et la casser en plus de pas. Avoir une étape non transactionnelle crée la facture d'espace réservé (cela ne pas être dans la transaction devrait éliminer les lacunes), puis dans la transaction effectuer le reste de votre entreprise. Je pense que c'était la façon dont nous l'avons fait dans un système que j'étais coincé avec des années il y a des années, mais je ne me souviens pas - je me souviens juste que c'était "bizarre".

Je dirais que la séquence garantira des nombres uniques / consécutifs, mais lorsque vous lancez des transactions dans le mélange qui ne peut être garantie que si la génération de séquence n'est pas dans cette transaction.

Le lien de DPBradley dans le n ° 2 sonne comme votre meilleur pari. Tom conserve la transacalité avec l'appelant, si vous ne voulez pas que vous puissiez en faire une transaction autonome, comme:

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;

Je pense que vous constaterez que l'utilisation du maximum () des numéros existants est enclin à un nouveau problème excitant - des doublons peuvent survenir si plusieurs factures sont créées en même temps. (Ne me demandez pas comment je sais ...).

Une solution possible consiste à dériver la clé primaire de votre table de facturation à partir d'une séquence, mais ne pas être le numéro de facturation. Après avoir correctement et correctement créé votre facture, et après le point où une exception ou un caprice de l'utilisateur pourrait entraîner la résiliation de la facture, vous allez à une seconde séquence pour obtenir le numéro séquentiel qui est présenté comme "le" numéro de facturation . Cela signifie que vous aurez deux nombres uniques et non répétitifs sur votre table de facturation, et l'évideur (factor_no) ne sera pas la clé primaire (mais cela peut et devrait être unique) donc il y a un peu de mauvais coup de poule dans, mais L'alternative - qui consiste à créer la ligne de facturation avec une seule valeur dans la clé primaire, puis modifiez la clé primaire après la création de la facture - est trop diabolique pour les mots. : -)

Partager et profiter.

Ce que nous faisons est de publier un numéro de séquence à la transaction, puis lorsque l'élément que nous traitons est finalisé, nous émettons un numéro permanent (également une séquence). Fonctionne bien pour nous.

regarde
K

Si vous ne voulez vraiment avoir aucune lacune, vous devez complètement sérialiser l'accès, sinon il y aura toujours des lacunes. Les raisons des lacunes sont:

• Rollback
• arrêt d'arrêt

Je suis tombé sur ce problème auparavant. Dans un cas, nous avons pu convaincre l'entreprise d'accepter que les factures «réelles» puissent avoir des lacunes et nous avons écrit un emploi qui a couru tous les jours pour «combler» les lacunes avec des factures «nulles» à des fins d'audit.

En pratique, si nous mettons Nocache sur la séquence, le nombre de lacunes serait relativement faible, les auditeurs seront donc généralement heureux tant que leur requête sur les factures "Void" ne reviennent pas trop de résultats. < / p>