Oracle: comment UPSERT (mettre à jour ou insérer dans une table?)

L’opération UPSERT met à jour ou insère une ligne dans une table, selon que la table contient déjà une ligne correspondant aux données:

if table t has a row exists that has key X: update t set mystuff... where mykey=X else insert into t mystuff... 

Oracle ne disposant pas d’une instruction UPSERT spécifique, quelle est la meilleure façon de procéder?

Une alternative à MERGE (la “manière démodée”):

 begin insert into t (mykey, mystuff) values ('X', 123); exception when dup_val_on_index then update t set mystuff = 123 where mykey = 'X'; end; 

L’ instruction MERGE fusionne les données entre deux tables. Utiliser DUAL nous permet d’utiliser cette commande. Notez que cela n’est pas protégé contre les access concurrents.

 create or replace procedure ups(xa number) as begin merge into mergetest m using dual on (a = xa) when not matched then insert (a,b) values (xa,1) when matched then update set b = b+1; end ups; / drop table mergetest; create table mergetest(a number, b number); call ups(10); call ups(10); call ups(20); select * from mergetest; AB ---------------------- ---------------------- 10 2 20 1 

Le double exemple ci-dessus qui est en PL / SQL était génial parce que je voulais faire quelque chose de similaire, mais je voulais le côté client … voici le code SQL que j’ai utilisé pour envoyer directement une instruction similaire à partir de C #

 MERGE INTO Employee USING dual ON ( "id"=2097153 ) WHEN MATCHED THEN UPDATE SET "last"="smith" , "name"="john" WHEN NOT MATCHED THEN INSERT ("id","last","name") VALUES ( 2097153,"smith", "john" ) 

Cependant, du sharepoint vue de C #, cela permet d’être plus lent que de faire la mise à jour et de voir si les lignes affectées étaient 0 et de faire l’insertion si c’était le cas.

Une autre alternative sans vérification des exceptions:

 UPDATE tablename SET val1 = in_val1, val2 = in_val2 WHERE val3 = in_val3; IF ( sql%rowcount = 0 ) THEN INSERT INTO tablename VALUES (in_val1, in_val2, in_val3); END IF; 
  1. insérer si n’existe pas
  2. mettre à jour:
    
 INSERT INTO mytable (id1, t1) 
   SELECT 11, 'x1' DE DUAL 
   O NOT N'EXISTE PAS (SELECT id1 FROM mytble WHERE id1 = 11); 

 UPDATE mytable SET t1 = 'x1' WHERE id1 = 11;

Aucune des réponses données jusqu’ici n’est sans danger face aux access simultanés , comme indiqué dans le commentaire de Tim Sylvester, et soulèvera des exceptions dans le cas des courses. Pour résoudre ce problème, le combo insertion / mise à jour doit être encapsulé dans une sorte d’instruction de boucle, de sorte qu’en cas d’exception, l’ensemble soit réessayé.

À titre d’exemple, voici comment le code de Grommit peut être encapsulé dans une boucle pour le rendre sûr lorsqu’il est exécuté simultanément:

 PROCEDURE MyProc ( ... ) IS BEGIN LOOP BEGIN MERGE INTO Employee USING dual ON ( "id"=2097153 ) WHEN MATCHED THEN UPDATE SET "last"="smith" , "name"="john" WHEN NOT MATCHED THEN INSERT ("id","last","name") VALUES ( 2097153,"smith", "john" ); EXIT; -- success? -> exit loop EXCEPTION WHEN NO_DATA_FOUND THEN -- the entry was concurrently deleted NULL; -- exception? -> no op, ie continue looping WHEN DUP_VAL_ON_INDEX THEN -- an entry was concurrently inserted NULL; -- exception? -> no op, ie continue looping END; END LOOP; END; 

NB Dans le mode de transaction SERIALIZABLE , que je déconseille à btw, vous pourriez rencontrer ORA-08177: vous ne pouvez pas sérialiser l’access à cette exception de transaction .

Je voudrais répondre Grommit, sauf que cela nécessite des valeurs de dupe. J’ai trouvé la solution où il peut apparaître une fois: http://forums.devshed.com/showpost.php?p=1182653&postcount=2

 MERGE INTO KBS.NUFUS_MUHTARLIK B USING ( SELECT '028-01' CILT, '25' SAYFA, '6' KUTUK, '46603404838' MERNIS_NO FROM DUAL ) E ON (B.MERNIS_NO = E.MERNIS_NO) WHEN MATCHED THEN UPDATE SET B.CILT = E.CILT, B.SAYFA = E.SAYFA, B.KUTUK = E.KUTUK WHEN NOT MATCHED THEN INSERT ( CILT, SAYFA, KUTUK, MERNIS_NO) VALUES (E.CILT, E.SAYFA, E.KUTUK, E.MERNIS_NO); 

Une note concernant les deux solutions qui suggèrent:

1) Insérer, si exception puis mettre à jour,

ou

2) Mettre à jour si sql% rowcount = 0 puis insérer

La question de savoir s’il faut d’abord insérer ou mettre à jour dépend également de l’application. Vous attendez plus d’insertions ou plus de mises à jour? Celui qui est le plus susceptible de réussir devrait aller en premier.

Si vous choisissez le mauvais, vous obtiendrez un tas de lectures d’index inutiles. Pas une grosse affaire mais encore quelque chose à considérer.

J’utilise le premier exemple de code depuis des années. Remarquez pas trouvé plutôt que de compter.

 UPDATE tablename SET val1 = in_val1, val2 = in_val2 WHERE val3 = in_val3; IF ( sql%notfound ) THEN INSERT INTO tablename VALUES (in_val1, in_val2, in_val3); END IF; 

Le code ci-dessous est le code éventuellement nouveau et amélioré

 MERGE INTO tablename USING dual ON ( val3 = in_val3 ) WHEN MATCHED THEN UPDATE SET val1 = in_val1, val2 = in_val2 WHEN NOT MATCHED THEN INSERT VALUES (in_val1, in_val2, in_val3) 

Dans le premier exemple, la mise à jour effectue une recherche d’index. Il doit le faire pour mettre à jour la bonne ligne. Oracle ouvre un curseur implicite, et nous l’utilisons pour envelopper un insert correspondant afin que nous sachions que l’insertion ne se produira que lorsque la clé n’existe pas. Mais l’insert est une commande indépendante et doit effectuer une seconde recherche. Je ne connais pas le fonctionnement interne de la commande de fusion, mais comme la commande est une seule unité, Oracle aurait pu exécuter l’insertion ou la mise à jour correcte avec une seule recherche d’index.

Je pense que la fusion est meilleure lorsque vous avez un traitement à faire qui consiste à prendre des données de certaines tables et à mettre à jour une table, en insérant éventuellement des lignes. Mais pour le cas d’une seule ligne, vous pouvez considérer le premier cas puisque la syntaxe est plus courante.

Copier et coller un exemple pour renverser une table dans une autre, avec MERGE:

 CREATE GLOBAL TEMPORARY TABLE t1 (id VARCHAR2(5) , value VARCHAR2(5), value2 VARCHAR2(5) ) ON COMMIT DELETE ROWS; CREATE GLOBAL TEMPORARY TABLE t2 (id VARCHAR2(5) , value VARCHAR2(5), value2 VARCHAR2(5)) ON COMMIT DELETE ROWS; ALTER TABLE t2 ADD CONSTRAINT PK_LKP_MIGRATION_INFO PRIMARY KEY (id); insert into t1 values ('a','1','1'); insert into t1 values ('b','4','5'); insert into t2 values ('b','2','2'); insert into t2 values ('c','3','3'); merge into t2 using t1 on (t1.id = t2.id) when matched then update set t2.value = t1.value, t2.value2 = t1.value2 when not matched then insert (t2.id, t2.value, t2.value2) values(t1.id, t1.value, t1.value2); select * from t2 

Résultat:

  1. b 4 5
  2. c 3 3
  3. un 1 1

Essaye ça,

 insert into b_building_property ( select 'AREA_IN_COMMON_USE_DOUBLE','Area in Common Use','DOUBLE', null, 9000, 9 from dual ) minus ( select * from b_building_property where id = 9 ) ; 

De http://www.praetoriate.com/oracle_tips_upserts.htm :

“Dans Oracle9i, un UPSERT peut accomplir cette tâche dans une seule déclaration:”

 INSERT FIRST WHEN credit_limit >=100000 THEN INTO rich_customers VALUES(cust_id,cust_credit_limit) INTO customers ELSE INTO customers SELECT * FROM new_customers;