1. Behovet av MERGE, ett exempel
Något som man ofta vill göra med en tabellrad är att att uppdatera den om den finns, eller lägga till den om den inte finns. Låt oss säga att vi har en applikation som håller reda på husdjur. I denna applikation har varje husdjur en ras. Det finns tre operationer man kan utföra på en djurras:
- Skapa en djurras. För detta används en INSERT-sats.
- Editera en djurras. För detta används en UPDATE-sats.
- Ta bort en djurras. För detta används en DELETE-sats.
I detta fall är formuläret med vilket man skapar djurrasen identiskt med det formulär man använder för att editera husdjurets egenskaper. Båda har fält där man kan fylla i:
- Djurrasens namn, t.ex. "dog".
- Djurrasens beskrivning, t.ex. "Man's best friend.".
- Datum för senaste uppdatering, t.ex. "11-APR-04".
Längst ner finns en spara-knapp. För att bespara oss lite arbete bestämmer vi oss för att använda samma formulär för att både skapa och uppdatera en djurras. När formuläret ska arkiveras i databasen utförs först en SELECT-sats:
SQL> SELECT id FROM species WHERE name='dog';
|
Om SELECT-satsen ej returnerar någon rad finns inte husdjuret i databasen. Då måste vi utföra en INSERT:
SQL> INSERT INTO species (id, name, description, updated)
2 VALUES(species_seq.nextval, 'dog', "Man's best friend.",'11-APR-04');
1 row inserted.
|
Om SELECT-satsen returnerar ett id finns djurrasen i databasen. Då måste vi utföra en UPDATE. Låt oss säga att det id som returnerades var 17:
SQL> UPDATE species SET
2 name='dog',
3 description="Man's best friend."
4 updated='11-APR-04',
5 WHERE id=17;
1 row updated.
|
2. MERGE
I exemplet ovan utfördes först en SELECT-sats, och sedan en INSERT eller UPDATE beroende på resultatet av SELECT-satsen. MERGE-satsen är skapad för att underlätta just denna situation, genom att dessa tre satser bakas ihop till en:
SQL> MERGE INTO species s1
2 USING species s2
3 ON (s1.id=s2.id AND s1.name='dog')
4 WHEN MATCHED THEN
5 UPDATE SET s1.description="Man's best friend.",
6 s1.updated='11-APR-04'
7 WHEN NOT MATCHED THEN
8 INSERT (s1.id, s1.name, s1.description, s1.updated)
9 VALUES (species_seq.nextval, 'dog', "Man's best friend.",'11-APR-04');
1 row merged.
|
De intressantaste raderna i satsen ovan är:
- 1: MERGE INTO species s1: Denna rad bestämmer vilken tabell som vi kommer att förändra.
- 2: USING species s2: Denna rad definierar vilken annan tabell som också förekommer i satsen. I vårt fall vill vi inte använda någon annan tabell utan specifierar tabellen species en gång till.
- 3: ON (s1.id=s2.id AND s1.name='dog'): I denna rad joinar vi species med sig själv och bestämmer att vi letar efter en rad med name='dog'. OBS: Denna rad är den enda som innehåller en referens till s2.
- 4: WHEN MATCHED THEN: Efter denna rad kommer UPDATE-satsen. OBS: Vi behöver till skillnad från en vanlig UPDATE inte säga vilken tabell som ska uppdateras. Detta framgår av rad 1.
- 7: WHEN NOT MATCHED THEN: Efter denna rad kommer INSERT-satsen. OBS: Vi behöver till skillnad från en vanlig INSERT inte säga "INTO species" eftersom vi på rad ett har bestämt vilken tabell som ska ändras.
Ännu några påpekanden om satsen ovan:
- Både i UPDATE och i INSERT måste man använda table.column-syntaxen när man refererar till en kolumn, trots att vi bara använder oss av tabellen species. Annars kommer Oracle att generera ett fel eftersom de två tabellerna (s1 och s2) har kolumner med samma namn.