„Oracle“: kaip UPSERT (atnaujinti arba įterpti į lentelę?)

UPSERT operacija atnaujina arba įterpia eilutę į lentelę, priklausomai nuo to, ar lentelėje jau yra atitinkama eilutė:

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

Kadangi „Oracle“ neturi konkretaus UPSERT pareiškimo, koks yra geriausias būdas tai padaryti?

239
26 окт. Nustatė Mark Harrison 26 spalis 2008-10-26 04:24 '08 at 4:24 2008-10-26 04:24
@ 12 atsakymų

Alternatyvus MERGE („senamadiškas būdas“):

 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; 
33
27 окт. Atsakymą pateikė Tony Andrews, spalio 27 d 2008-10-27 14:12 '08 14:12 pm 2008-10-27 14:12

MERGE pareiškimas sujungia duomenis tarp dviejų lentelių. DUAL naudojimas leidžia mums naudoti šią komandą. Atkreipkite dėmesį, kad jis nėra apsaugotas nuo vienalaikės prieigos.

border=0
 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 
182
26 окт. Mark Harrison atsakymas spalio 26 d 2008-10-26 04:24 '08 at 4:24 2008-10-26 04:24

Anksčiau pateiktas dvigubas pavyzdys, kuris yra PL / SQL, buvo puikus, nes norėjau kažką panašaus padaryti, bet norėjau, kad jis būtų kliento pusėje ... taigi čia SQL, kurį naudodavau panašiam pareiškimui tiesiogiai iš kai kurių 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" ) 

Tačiau, C # požiūriu, tai bus lėtesnė nei atnaujinimo atlikimas, ir pažiūrėkite, ar paveiktos linijos buvo 0 ir įterpė, jei ji buvo.

91
22 апр. atsakymą pateikė „ MyDeveloperDay “ balandžio 22 d 2010-04-22 19:00 '10 19:00 val. 2010-04-22 19:00

Kita alternatyva be patikrinimo išimties:

 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; 
42
28 окт. atsakymą pateikė Brian Schmitt, spalio 28 d 2008-10-28 17:37 '08 at 17:37 pm 2008-10-28 17:37
  • įterpti, jei neegzistuoja
  • atnaujinti:
 INSERT INTO įjungiamas (id1, t1)SELECT 11, „x1“ IŠ DUALKUR NEIŠSKYRUS (PASIRINKITE ID1 IŠ NUO Mytble WHERE id1 = 11);  UPDATE mytable SET t1 = 'x1' KUR id1 = 11;
21
23 янв. atsakymas pateikiamas test1 . 2014-01-23 17:02 '14 ne 17:02 2014-01-23 17:02

Nė vienas iš iki šiol pateiktų atsakymų nėra saugus tuo pačiu metu , kaip parodė Tim Sylvestr komentaras, ir lems rasių išimtis. Norėdami išspręsti šią problemą, įterpti / atnaujinti derinys turi būti suvyniotas į tam tikrą kilpos išraišką, taigi, išimties atveju, visa tai bus pakartota.

Pavyzdžiui, čia, kodą „Grommit“ galima suvynioti į kilpą, kad jis būtų saugus, kai veikia:

 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 SERIALIZABLE sandorio SERIALIZABLE , kurį nerekomenduoju btw, galite susidurti su ORA-08177: vietoj to neleidžiama naudoti šios operacijos prieigos .

19
01 апр. atsakymą pateikė Eugene Beresovsky 01 Bal. 2014-04-01 08:45 '14, 08:45 AM 2014-04-01 08:45

Pastaba dėl dviejų sprendimų, kurie siūlo:

1) Įklijuoti, jei išimtis atnaujinama,

arba

2) Atnaujinkite, jei sql% rowcount = 0, tada įklijuokite

Klausimas, ar įterpti ar atnaujinti, taip pat priklauso nuo programos. Ar tikitės daugiau investicijų ar daugiau atnaujinimų? Tai, kas greičiausiai pavyks, pirmiausia turėtų eiti.

Jei pasirinksite neteisingą, gausite nereikalingų indeksų krūva. Ne didžiulis dalykas, bet vis tiek ką reikia apsvarstyti.

8
26 нояб. Atsakymas duotas AnthonyVO lapkričio 26 d. 2011-11-26 02:19 '11 at 2:19 2011-11-26 02:19

Aš daugelį metų naudoju pirmojo kodo pavyzdį. Pastaba nepažymėta, neskaitoma.

 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; 

Žemiau yra kodas, galbūt naujas ir patobulintas kodas

 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) 

Pirmajame pavyzdyje naujinimas ieško indekso. Jis turi atnaujinti tinkamą liniją. „Oracle“ atveria numanomą žymeklį, ir mes naudojame jį atitinkamam įterpimui, kad žinotume, kad įterpimas įvyksta tik tada, kai raktas neegzistuoja. Bet įterpimas yra nepriklausoma komanda ir turi atlikti antrą paiešką. Nežinau sujungimo komandos vidinių veiksmų, bet, kadangi komanda yra visa, „Oracle“ gali tinkamai įterpti arba atnaujinti vieną indeksą.

Manau, kad susijungimas yra geresnis, kai turite tam tikrą tvarkymą, kuris bus atliktas, o tai reiškia, kad reikia gauti duomenis iš kai kurių lentelių ir atnaujinti lentelę, galbūt įterpiant ar ištrinant eilutes. Tačiau vienos eilutės atveju galite apsvarstyti pirmąjį atvejį, nes sintaksė yra labiau paplitusi.

6
12 янв. Atsakymas pateikiamas Arturo Hernandez 12 d 2015-01-12 19:48 '15, 19:48, 2015-01-12 19:48

Nukopijuokite ir įklijuokite pavyzdį, jei norite pridėti vieną lentelę į kitą, naudodami 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 

Rezultatas:

  • b 4 5
  • c 3 3
  • a 1 1
0
09 окт. Atsakymas pateikiamas Bechyňák Petr 09 okt. 2017-10-09 18:08 '17 at 18:08 2017-10-09 18:08

Išbandykite

 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 ) ; 
-2
15 марта '11 в 6:34 2011-03-15 06:34 atsakymas pateikiamas r4bitt 15 d. 11 d. 6:34 2011-03-15 06:34

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

„„ Oracle9i “UPSERT gali atlikti šią užduotį viename pareiškime:„

 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; 
-5
10 февр. Anon atsakė vasario 10 dieną 2010-02-10 04:24 '10, 4:24, 2010-02-10 04:24

Kiti klausimai apie žymes arba paklauskite klausimą