Pārējos rakstus var lasīt SQL pamatos.
Pēc pāris intermēdijām par to kā uzdot saprātīgu jautājumu un stāstu par to, ka vajadzētu padomāt kādus datus glabājam pie sevis un kādus dodam citiem internetā, paskatīsimies uz mazliet sausākām lietām. Oracle Merge SQL teikumu ir nodrošinājusi kopš 9i versijas, kas tika izlaista jau diezgan tālajā 2001 gadā. Sākumā tajā bija tikai UPDATE un INSERT klauzas, pie tam abas bija obligātas. 2003 gadā līdz ar versiju 10g MERGE funkcionalitāte tika papildināta pieļaujot neobligātu UPDATE, DELETE un INSERT. 11g versijā nekādi būtiski papildinājumi klāt nav nākuši.
Oracle Merge sintakse lielā mērā ir līdzīga SQL Server Merge sintaksei (kas vēl produkcijā īsti nav), bet tai ir savas atšķirības:
1) Nekur neparādās atslēgas vārdi TARGET un SOURCE;
2) Ierakstu dzēšana (Delete) ir zem Update operācijas, kas arī fiziski nozīmē, ka ieraksti vispirms tiek koriģēti un pēc tam iespējams izdzēsti, ja nosacījums to pieļauj.
Tātad sintakse ir šāda:
MERGE INTO <mērķa tabula>
USING <izejas dati>
ON (<salīdzināšanas nosacījums> )
WHEN MATCHED THEN <darbības, ja nosacījums patiess>
WHEN NOT MATCHED THEN <darbības, ja nosacījums aplams>
Klauza neatšķiras no SQL Server MERGE INTO atbilstošās klauzas, tātad norāda tabulas vārdu kurā tiks koriģēti, dzēsti vai pievienoti dati.
Šajā klauzā tiek norādīts, no kurienes dati tiks ņemti. Pašā triviālākajā gadījumā šeit var norādīt vienkārši tabulas vārdu. Šeit var izmantot arī apakšvaicājumu un atsķirībā no SQL Server nav nepieciešams tam obligāti pievienot aliasu.
ON (<salīdzināšanas nosacījums> )
Klauza praktiski neatšķiras no SQL Server atbilstošās klauzas, vienīgais – sintaktisks smalkums – tā jāliek iekavās. Šeit paliek spēkā tieši tā pati lieta, kas iepriekš – MERGE teikums katru rindu mērķa tabulā var 1) vai nu koriģēt vai dzēst 2) darīt to ne vairāk kā vienu reizi. Tas nozīmē, ka nosacījumam ir jābūt tādam, ka katram ierakstam mērķa tabulā atbilst ne vairāk kā viens ieraksts izejas datos. Tiklīdz kā tādu būs vairāk, Jūs iegūsiet kļūdu.
WHEN MATCHED THEN <darbības ja nosacījums patiess>
Šī ir tā vieta, kur sākas atšķirības. Tātad šādu klauzu var rakstīt tikai vienu vienīgu reizi, taču tai var būt UPDATE daļa un DELETE daļa. Un tā izskatās šādi:
WHEN MATCHED THEN
UPDATE SET <update klauza>
WHERE <update where klauza>
DELETE <delete where klauza>
Šeit tiek rakstītas darbības (Update vai Delete), kas notiek tad, ja salīdzināšanas nosacījums ir izpildījies. Parastajā scenārijā (periodiska mērķa datu atjaunošana) tas nozīmē, ka mērķa tabulā ieraksti tiek koriģēti. Jāatceras vēlreiz augšminētais teikums “ieraksti vispirms tiek koriģēti un tikai pēc tam dzēsti”, tātad no tā burtiski izriet, ka WHERE klauza, kas seko aiz UPDATE reizē ierobežo arī ierakstus, kas potenciāli tiks dzēsti – tātad DELETE tiek pielietota gan tam specifiskā WHERE klauza, gan arī UPDATE atbilstošā WHERE klauza, ja tāda eksistē.
Tagad beidzot ķeramies klāt pie piemēriem – tātad mums ir 2 tabulas, personals, kas būs mērķa tabula, un personals_src, kas tiks izmantota kā izejas datu tabula.
DROP TABLE personals;
DROP TABLE personals_src;
DROP SEQUENCE prs_seq;
CREATE TABLE personals (
pers_id INTEGER NOT NULL PRIMARY KEY,
prs_vards VARCHAR(40),
prs_uzvards VARCHAR(40),
prs_alga NUMERIC(10, 2),
prs_statuss VARCHAR(1));
CREATE TABLE personals_src (
vards VARCHAR(40),
uzvards VARCHAR(40),
alga NUMERIC(10, 2));
CREATE SEQUENCE prs_seq;
INSERT INTO personals VALUES
(prs_seq.nextval, 'JĀNIS', 'BĒRZIŅŠ', 1234.56, 'S');
INSERT INTO personals VALUES
(prs_seq.nextval, 'JĀNIS', 'KALNIŅŠ', 570, 'N');
INSERT INTO personals VALUES
(prs_seq.nextval, 'PĒTERIS', 'ZIRNIS', 680, 'S');
INSERT INTO personals VALUES
(prs_seq.nextval, 'MAIJA', 'ZINĪTE', 700, 'N');
INSERT INTO personals_src VALUES ('JĀNIS', 'BĒRZIŅŠ', 1200.00);
INSERT INTO personals_src VALUES ('JĀNIS', 'KALNIŅŠ', 400);
INSERT INTO personals_src VALUES ('ANNA', 'LEJA', 680);
COMMIT;
Izveidoto piemēru funkcionalitāte būs tieši tāda pati, kā analoģiskajam rakstam par SQL Server Merge teikumu.
Piemērs 1. Tabulas personals sākotnējie dati
SQL> SELECT * FROM personals;
PERS_ID PRS_VARDS PRS_UZVARDS PRS_ALGA P
------- --------- ------------ ----------
1 JĀNIS BĒRZIŅŠ 1234,56 S
2 JĀNIS KALNIŅŠ 570 N
3 PĒTERIS ZIRNIS 680 S
4 MAIJA ZINĪTE 700 N
Piemērs 2. Ja darbinieka vārds un uzvārds sakrīt, tad koriģējam algu uz tādu, kā norādīta izejas tabulā. Šis Merge faktiski ne ar ko neatšķiras no vienkārša Update.
SQL> MERGE INTO personals
2 USING personals_src
3 ON (prs_vards = vards
4 AND prs_uzvards = uzvards)
5 WHEN MATCHED THEN
6 UPDATE SET prs_alga = alga;
2 rows merged.
SQL> SELECT * FROM personals;
PERS_ID PRS_VARDS PRS_UZVARDS PRS_ALGA P
------- ---------- ------------- ----------
1 JĀNIS BĒRZIŅŠ 1200 S
2 JĀNIS KALNIŅŠ 400 N
3 PĒTERIS ZIRNIS 680 S
4 MAIJA ZINĪTE 700 N
Redzam, ka mainījusies alga pirmajam un otrajam ierakstam. Lai varētu veiksmīgi izpildīt nākošos piemērus varam atgriezties uz sākumu vienkārši izpildot ROLLBACK.
Piemērs 3. Ja darbinieka vārds sakrīt, tad koriģējam algu uz tādu, kā norādīta izejas tabulā. Redzam, ka nosacījums ir pārāk vājš un iegūstam kļūdu, jo atbilstoši šādam nosacījuma mērķa tabulā ieraksts ir jākoriģē vairākas reizes.
SQL> MERGE INTO personals
2 USING personals_src
3 ON (prs_vards = vards)
4 WHEN MATCHED THEN
5 UPDATE SET prs_alga = alga;
USING personals_src
*
ERROR at line 2:
ORA-30926: unable to get a stable set of rows in the source tables
Kļūdas paziņojums, protams, ir savādāks nekā atbilstošais SQL Server piemērs, bet gala rezultāts ir tāds pats.
Piemērs 4. Ja darbinieka vārds un uzvārds sakrīt, tad koriģējam algu uz tādu, kā norādīta izejas tabulā gadījumā, ja darbinieks vēl strādā (statuss S). Ja darbinieks vairs nestrādā, tad dzēšam šādu ierakstu ārā.
SQL> MERGE INTO personals
2 USING personals_src
3 ON (prs_vards = vards
4 AND prs_uzvards = uzvards)
5 WHEN MATCHED THEN
6 UPDATE SET prs_alga = alga
7 DELETE WHERE prs_statuss = 'N';
2 rows merged.
SQL> SELECT * FROM personals;
PERS_ID PRS_VARDS PRS_UZVARDS PRS_ALGA P
------- ---------- ------------- ----------
1 JĀNIS BĒRZIŅŠ 1200 S
3 PĒTERIS ZIRNIS 680 S
4 MAIJA ZINĪTE 700 N
Redzam, ka tagad personals tabulā ir tikai 3 ieraksti, ar Delete ir izmests ārā tas ieraksts, kam statuss bija nestrādājošs.
WHEN NOT MATCHED THEN <darbības ja nosacījums aplams>
Tātad šī klauza izpildās tad, ja nosacījums ir aplams. Loģiski, ka tādā gadījumā mums tikai atliek datus pievienot.
Piemērs 5. Ja darbinieks nav mērķa tabulā, bet ir izejas datos, tad tādu pievienojam. Faktiski neatšķiras no vienkāršas Insert klauzas.
SQL> MERGE INTO personals
2 USING personals_src
3 ON (prs_vards = vards
4 AND prs_uzvards = uzvards)
5 WHEN NOT MATCHED THEN
6 INSERT VALUES (prs_seq.nextval, vards, uzvards, alga, 'S');
1 row merged.
SQL> SELECT * FROM personals;
PERS_ID PRS_VARDS PRS_UZVARDS PRS_ALGA P
------- ---------- ------------ ----------
1 JĀNIS BĒRZIŅŠ 1234,56 S
2 JĀNIS KALNIŅŠ 570 N
3 PĒTERIS ZIRNIS 680 S
4 MAIJA ZINĪTE 700 N
7 ANNA LEJA 680 S
Redzam, ka nācis klāt jauns ieraksts, kas nebija iepriekš. Vairāk klauzu Merge teikumam Oraclē nav. Līdz ar to tiem cilvēkiem, kas ir izlasījuši rakstu par SQL Server Merge sintaksi varētu rasties pamatots jautājums – vai ir iespējams dabūt arī to funkcionalitāti, kas tika rādīta iepriekšējā rakstā, t.i., piemēram, tos ierakstus, kas nav izejas datos, bet ir mērķa datos, dzēst ārā? Ir iespējams, tikai šoreiz ir jārīkojas mazliet viltīgāk. Tas nav panākams triviāli, jo galvenais mērķis Merge teikumam ir lielu datu apjomu salīdzināšana un šajos scenārijos, katrā reizē normāli visa kopa netiek atjaunota, bet tiek atjaunota tikai kāda tās daļa vai arī likti papildus ieraksti klāt. Bet nu OK, kā tad to panākt? Jāmodificē mazliet izejas datu kopa. Atceramies, ka izejas dati var būt arī apakšpieprasījums – ne tikai vienkārša tabula – tātad jāmodificē apakšpieprasījums tādā veidā, lai arī tajā parādītos tie dati, ko vajag dzēst. Šeit noder FULL OUTER JOIN klauza, kas vispirms izejas datu tabulu savieno ar mērķa datu tabulu. Nākošajā piemērā skatamies kā pamazām tiek uzbūvēts nepieciešamais MERGE teikums, kurš dara šādas lietas:
- tiem darbiniekiem, kas ir abās kopās un strādā, tiek koriģētas algas;
- tie darbinieki, kas ir abās kopās un nestrādā, tiek dzēsti;
- tie darbinieki, kas ir izejas kopā, bet nav mērķa kopā, tiek tai pievienoti;
- tiem darbiniekiem, kas nav izejas kopā, bet ir mērķa kopā un statuss ir “strādā”, tiek uzstādīts jauns statuss “neskaidrs”;
- tie darbinieki, kas nav izejas kopā, bet ir mērķa kopā un statuss ir “nestrādā”, tiek dzēsti.
Tatad lai tiktu pie beigu Merge teikuma veicam to vairākos soļos:
- Vispirms izveidojam pilnu ārējo savienojumu abām tabulām;
- Tad balstoties uz to kā abās tabulās ir izvietoti dati uzstādam karodziņu;
- Visbeidzot izpildam pašu MERGE SQL teikumu.
Piemērs 6. Izveidojam apakšvaicājumu MERGE izejas datiem.
SQL> SELECT * FROM personals
2 FULL OUTER JOIN personals_src ON
3 (prs_vards = vards
4 AND prs_uzvards = uzvards);
PERS_ID PRS_VARDS PRS_UZVARDS PRS_ALGA P VARDS UZVARDS ALGA
------- --------- ----------- -------- - ------ -------- ----
1 JĀNIS BĒRZIŅŠ 1234,56 S JĀNIS BĒRZIŅŠ 1200
2 JĀNIS KALNIŅŠ 570 N JĀNIS KALNIŅŠ 400
4 MAIJA ZINĪTE 700 N
3 PĒTERIS ZIRNIS 680 S
ANNA LEJA 680
SQL> SELECT nvl(vards, prs_vards) vards,
2 nvl(uzvards, prs_uzvards) uzvards, alga,
3 CASE WHEN vards IS NOT NULL
4 AND prs_vards IS NOT NULL
5 AND prs_statuss = 'S' THEN 'upd_alga'
6 WHEN vards IS NOT NULL
7 AND prs_vards IS NOT NULL
8 AND prs_statuss = 'N' THEN 'del'
9 WHEN vards IS NOT NULL AND prs_vards IS NULL THEN 'ins'
10 WHEN vards IS NULL
11 AND prs_vards IS NOT NULL
12 AND prs_statuss = 'S' THEN 'upd_statuss'
13 WHEN vards IS NULL
14 AND prs_vards IS NOT NULL
15 AND prs_statuss = 'N' THEN 'del'
16 END karodzins
17 FROM (
18 SELECT * FROM personals
19 FULL OUTER JOIN personals_src ON
20 (prs_vards = vards
21 AND prs_uzvards = uzvards)
22 )
23 /
VARDS UZVARDS ALGA KARODZINS
------ -------- ----- -----------
JĀNIS BĒRZIŅŠ 1200 upd_alga
JĀNIS KALNIŅŠ 400 del
MAIJA ZINĪTE del
PĒTERIS ZIRNIS upd_statuss
ANNA LEJA 680 ins
Piemērs 7. Izveidojam MERGE teikumu atbilstoši augšminētajam pilnajam algoritmam.
SQL> MERGE INTO personals
2 USING (
3 SELECT nvl(vards, prs_vards) vards,
4 nvl(uzvards, prs_uzvards) uzvards, alga,
5 CASE WHEN vards IS NOT NULL
6 AND prs_vards IS NOT NULL
7 AND prs_statuss = 'S' THEN 'upd_alga'
8 WHEN vards IS NOT NULL
9 AND prs_vards IS NOT NULL
10 AND prs_statuss = 'N' THEN 'del'
11 WHEN vards IS NOT NULL AND prs_vards IS NULL THEN 'ins'
12 WHEN vards IS NULL
13 AND prs_vards IS NOT NULL
14 AND prs_statuss = 'S' THEN 'upd_statuss'
15 WHEN vards IS NULL
16 AND prs_vards IS NOT NULL
17 AND prs_statuss = 'N' THEN 'del'
18 END karodzins
19 FROM (
20 SELECT * FROM personals
21 FULL OUTER JOIN personals_src ON
22 (prs_vards = vards
23 AND prs_uzvards = uzvards)
24 )
25 )
26 ON (prs_vards = vards
27 AND prs_uzvards = uzvards)
28 WHEN MATCHED THEN UPDATE
29 SET prs_alga = (CASE karodzins WHEN 'upd_alga'
30 THEN alga ELSE prs_alga END),
31 prs_statuss = (CASE karodzins WHEN 'upd_statuss'
32 THEN '?' ELSE prs_statuss END)
33 DELETE WHERE karodzins = 'del'
34 WHEN NOT MATCHED THEN
35 INSERT VALUES (prs_seq.nextval, vards, uzvards, alga, 'S')
36 /
5 rows merged.
SQL> SELECT * FROM personals;
PERS_ID PRS_VARDS PRS_UZVARDS PRS_ALGA P
------- --------- ------------ -------- -
1 JĀNIS BĒRZIŅŠ 1200 S
3 PĒTERIS ZIRNIS 680 ?
19 ANNA LEJA 680 S
Kā redzams, šis SQL Merge teikums izskatās diezgan garš un pirmajā brīdī varbūt nav nemaz tik vienkārši izsekot tam visam līdzi, bet galvenā ideja, ko atcerēties ir tāda, ka mazliet padomājot gala rezultātā šo visu diezgan sarežģīto algoritmu var paveikt ar vienu SQL teikumu.
Kas jāatceras papildus
- Kā jau tika minēts augstāk, katru rindu var koriģēt tikai vienreiz, tātad raugieties, lai salīdzināšanas nosacījums būtu pietiekami stingrs un vienam mērķa tabulas ierakstam atbilstu ne vairāk kā viens izejas tabulas ieraksts.
- Merge teikumā nedrīkst koriģēt salīdzināšanas nosacījumā ietvertās kolonas, citādi būs kļūda.
- Ja Jūs izmantojat sekvences Merge Insert klauzā, tad esat gatavi, ka tājās parādīsies potenciāli diezgan lieli “caurumi”, jo sekvenču vērtības tiek ģenerētas katrai izejas kopas rindai pirms tiek noskaidrots, vai šo rindu pievienos vai koriģēs, vai nedarīs neko. Vairāk par to kā un kāpēc tas tā notiek var lasīt mana angliskā emuāra ierakstā SQL Merge and Sequence gaps. Normāli tam nevajadzētu radīt nekādas problēmas, jo sekvences tiek izmantotas tikai kā unikālu skaitļu ģeneratori un nekas vairāk.
- Merge teikums nestrādā, ja tiek izmantota Fine-grained access control (Oracle iespēja, kas lietotājam nemanot fonā piekārto SQL teikumiem papildus Where nosacījumus, kurus ir iespējams dinamiski izveidot).
Merge teikums citās DBVS
Merge teikums ir pieejams arī citās DBVS, piemēram DB2 un šķiet to taisās ieviest arī PostgreSQL. MySQL Merge teikuma nav, bet ir šai datubāzei specifiski SQL teikumi, kas nekur citur nav, piemēram, REPLACE un INSERT … ON DUPLICATE KEY UPDATE un kas kaut kādā mērā var tikt pielietoti, lai daļēji simulētu Merge funkcionalitāti.
Datubāzes 