Oracle INSERT INTO SELECT(...) DUP_VAL_ON_INDEX comportement des exceptions

J'ai une procédure stockée qui ressemble à ceci:

BEGIN
  INSERT INTO result_table
  (SELECT (...) FROM query_table);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    NULL;
END;

Je vais le faire dans une boucle qui passe plusieurs paramètres de l'instruction SELECT et, dans certains cas, certaines valeurs peuvent dupliquer c'est pourquoi je dois prendre le DUP_VAL_ON_INDEX exception.

Ma question est que si l'instruction SELECT retourne plusieurs lignes et seulement l'un d'eux existe déjà dans *result_table*, f l'est. ex.

1 'A'
2 'B'
3 'C'

Et de la première rangée (de 1 'A') serait déjà dans la table, les autres lignes qui n'existent pas (deuxième et troisième à partir de cas ci-dessus) être inséré? Ou aucun d'entre eux serait inséré à tous?

J'ai peur qu'aucun d'entre eux serait inséré (et mon cas de test partiellement confirme que)... Si oui, quelle option dois-je atteindre les objectifs souhaités de comportement? Est-il un bon moyen pour insérer les lignes qui ne viole pas la clé primaire à l'aide de la construction ci-dessus?

InformationsquelleAutor Ziouas | 2013-02-01

exception oracle

2

Vous pouvez utiliser le MERGE déclaration. Insérer les enregistrements s'ils n'existent pas et de ne rien faire s'ils existent déjà.

http://psoug.org/reference/merge.html
- C'est vraiment une réponse utile et il semble qu'il shold travail de la façon dont je voudrais qu'il. Je vais essayer lundi et après le retour.
- Il fonctionne, mais il fonctionne beaucoup plus lent, mais dans mon cas, il va faire pour l'instant.
- Sur le nombre de threads qu'il produit les mêmes erreurs que mon énoncé original malheureusement. Je vais devoir aller avec le curseur :/
- Que voulez-vous dire "beaucoup de fils"? Avez-vous plusieurs séances d'insertion en même temps? Dans ce cas, vous devez avoir une sorte de sérialisation. Une séance doit se terminer l'insertion, seulement après que la prochaine séance peut commencer, et ainsi de suite. Mais, ce ne sera pas à l'échelle.
- Nous avons plusieurs travailleurs de l'exécution de cette procédure simultanément et en essayant de le faire de manière aussi conviviale et évolutive que possible. Mais je crois que cette tâche doit être entièrement sérialisés malheureusement, de sorte que est une déception.
InformationsquelleAutor Alen Oblak
2

Vous avez raison, si un enregistrement viole la contrainte, aucun ne sera inséré.
Je ferais
```
INSERT INTO result_table
(SELECT (...) FROM query_table a WHERE NOT EXISTS
(SELECT NULL FROM result_table b WHERE b.b_unique_key = a.b_unique_key)
)
```
Une autre option est d'utiliser la journalisation des erreurs
```
INSERT INTO result_table
SELECT ... FROM query_table 
LOG ERRORS INTO err$_dest ('INSERT') REJECT LIMIT UNLIMITED;
```
Remarque: vous devez créer d'erreur de la table avant d'exécuter cette requête.
- Mais j'ai peur qu'à cause de plusieurs opérations simultanées, il peut être un moyen que cette instruction select récupère des données qui pourraient être insérés dans d'autres transactions avant cette instruction insert et c'est pourquoi il obtient cette exception. Je l'ai déjà écrit la requête SELECT dans une voie qu'il ne faut pas extraire les lignes qui serait en violation de l'index. Donc mon scénario hypothétique ressemble à ceci: opération d'Un extrait des lignes 1,2,3, opération insère la ligne 1 récupérés avant de transaction, Une tente d'insérer des 3 lignes et soulève une exception parce que la ligne 1 est déjà inséré. Comment pourrais-je gérer cela?
- J'ai pensé à peut-être l'ajout de la même instruction SELECT dans le gestionnaire d'exception, mais cela pourrait aussi soulever la DUP_VAL_ON_INDEX exception. Je ne vois pas de bonne solution ici... :/
- Alors j'ai quelques autres suggestions. 1. Insérer des lignes individuellement ( par exemple, en utilisant les curseurs ). 2. LOCK result_table IN SHARE ROW EXCLUSIVE MODE et ensuite faire votre insert (attention : personne d'autre ne peut mettre à jour/supprimer/insérer une ligne tout en vous tenant lock).
- Ce n'est pas une option, malheureusement, dans mon cas.
InformationsquelleAutor a1ex07

Si vous utilisez 11g, vous pouvez utiliser le ignore_row_on_dupkey_index astuce pour supprimer les erreurs:

create table tab (id integer);

alter table tab add constraint tab_pk primary key (id);

insert into tab
  select rownum from dual connect by level <= 1;

1 rows inserted.
        ID
----------
         1 

SELECT * FROM tab;

insert into tab
  select rownum from dual connect by level <= 3;

SQL Error: ORA-00001: unique constraint (CAM_OWNER.TAB_PK) violated

insert /*+ ignore_row_on_dupkey_index(tab, tab_pk) */into tab
  select rownum from dual connect by level <= 3;

SELECT * FROM tab;

2 rows inserted.
        ID
----------
         1 
         2 
         3

InformationsquelleAutor Chris Saxon

Vous devez vous connecter pour publier un commentaire.