Oracle: jak UPSERTOWAĆ (uaktualnić lub wstawić do tabeli?)

Instrukcja merge łączy dane między dwiema tabelami. Korzystanie z DUAL pozwala nam na użycie tego polecenia. Zauważ, że nie jest to chronione przed równoczesnym dostępem.

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

Powyższy podwójny przykład, który jest w PL / SQL był świetny, ponieważ chciałem zrobić coś podobnego, ale chciałem to klient side...so oto SQL, którego użyłem do wysłania podobnej instrukcji bezpośrednio z jakiegoś 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" )

Jednak z punktu widzenia C# zapewnia to wolniejsze niż wykonywanie aktualizacji i sprawdzanie, czy wiersze, których to dotyczy, były równe 0 i wykonywanie wstawiania, jeśli tak było.

Inna alternatywa bez sprawdzania WYJĄTKÓW:

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. insert if not exists
2. Aktualizacja:

    
INSERT INTO mytable (id1, t1) 
  SELECT 11, 'x1' FROM DUAL 
  WHERE NOT EXISTS (SELECT id1 FROM mytble WHERE id1 = 11); 

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

Żadna z udzielonych dotąd odpowiedzi nie jestBezpieczna w obliczu równoczesnego dostępu , Jak wskazano w komentarzu Tima Sylvestra, i będzie podnosić wyjątki w przypadku ras. Aby to naprawić, kombi insert/update musi być zapakowane w jakąś instrukcję loop, tak aby w przypadku wyjątku całość została powtórzona.

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; 

Uwaga: w trybie transakcji SERIALIZABLE, który Nie polecam btw, może wpadniesz na ORA-08177: nie można serializować dostępu do tej transakcji zamiast WYJĄTKÓW.

Chciałbym Grommit odpowiedź, ale to wymaga dupe wartości. Znalazłem rozwiązanie, gdzie może się pojawić raz: 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); 

Uwaga dotycząca dwóch rozwiązań, które sugerują:

1) Insert, if exception then update,

LUB

2) Update, if SQL % rowcount = 0 then insert

Kwestia, czy najpierw wstawić lub zaktualizować, jest również zależna od aplikacji. Spodziewasz się więcej wstawek lub aktualizacji? Ten, który jest najbardziej prawdopodobne, aby odnieść sukces, powinien iść pierwszy.

Jeśli wybierzesz niewłaściwy, otrzymasz kilka niepotrzebnych odczytów indeksów. Nic wielkiego, ale wciąż coś do zastanów się.

Używam pierwszej próbki kodu od lat. Notice notfound than count.

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;

Poniższy kod jest prawdopodobnie nowym i ulepszonym kodem

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)

W pierwszym przykładzie aktualizacja dokonuje wyszukiwania indeksu. Musi, aby zaktualizować właściwy wiersz. Oracle otwiera Ukryty kursor, a my używamy go do zawijania odpowiedniej wstawki, więc wiemy, że wstawka stanie się tylko wtedy, gdy klucz nie istnieje. Ale insert jest niezależnym poleceniem i musi zrobić drugą / align = "left" / Nie znam wewnętrznego działania polecenia merge, ale ponieważ polecenie jest pojedynczą jednostką, Oracle może wykonać poprawną wstawkę lub aktualizację za pomocą jednego wyszukiwania indeksu.

Myślę, że scalanie jest lepsze, gdy trzeba wykonać pewne przetwarzanie, które oznacza pobranie danych z niektórych tabel i aktualizację tabeli, ewentualnie wstawianie lub usuwanie wierszy. Ale dla przypadku pojedynczego wiersza, możesz rozważyć pierwszy przypadek, ponieważ składnia jest bardziej powszechna.

Przykład kopiowania i wklejania do wstawiania jednej tabeli do drugiej z 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

Wynik:

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

Spróbuj tego,

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
)
;

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

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;