[sql] Oracle: come UPSERT (aggiornare o inserire in una tabella?)



Answers

L' istruzione MERGE unisce i dati tra due tabelle. L'uso di DUAL ci consente di utilizzare questo comando. Si noti che questo non è protetto contro l'accesso concorrente.

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;

A                      B
---------------------- ----------------------
10                     2
20                     1
Question

L'operazione UPSERT aggiorna o inserisce una riga in una tabella, a seconda che la tabella abbia già una riga che corrisponde ai dati:

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

Poiché Oracle non ha una specifica dichiarazione UPSERT, qual è il modo migliore per farlo?




Un'altra alternativa senza controllo di eccezione:

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;



Una nota riguardante le due soluzioni che suggeriscono:

1) Inserisci, se l'eccezione poi aggiorna,

o

2) Aggiorna, se sql% rowcount = 0 quindi inserisci

La domanda se inserire o aggiornare prima dipende anche dall'applicazione. Ti aspetti più inserti o più aggiornamenti? Quello che ha maggiori probabilità di successo dovrebbe andare per primo.

Se scegli quella sbagliata otterrai un sacco di letture non necessarie dell'indice. Non è un grosso problema ma c'è ancora qualcosa da considerare.




Copia e incolla l'esempio per far avanzare una tabella in un'altra, con 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

Risultato:

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



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

"In Oracle9i, un UPSERT può svolgere questo compito in una singola istruzione:"

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;



Nessuna delle risposte fornite finora è sicura di fronte agli accessi concorrenti , come sottolineato nel commento di Tim Sylvester, e solleverà eccezioni in caso di razze. Per risolvere ciò, la combinazione di inserimento / aggiornamento deve essere racchiusa in una sorta di istruzione di loop, in modo che in caso di un'eccezione l'intera operazione venga ritentata.

Ad esempio, ecco come il codice di Grommit può essere avvolto in un ciclo per renderlo sicuro quando eseguito contemporaneamente:

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, i.e. continue looping
    WHEN DUP_VAL_ON_INDEX THEN -- an entry was concurrently inserted
      NULL; -- exception? -> no op, i.e. continue looping
  END;
 END LOOP;
END; 

NB In modalità di transazione SERIALIZABLE , che non consiglio btw, è possibile eseguire ORA-08177: non è possibile serializzare l'accesso per queste eccezioni di transazione .




Related