Oracle Merge teikuma sintakse

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>


MERGE INTO <mērķa tabula>

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.


USING <izejas 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.

Tālākā lasāmviela

Komentēt

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Mainīt )

Twitter picture

You are commenting using your Twitter account. Log Out / Mainīt )

Facebook photo

You are commenting using your Facebook account. Log Out / Mainīt )

Google+ photo

You are commenting using your Google+ account. Log Out / Mainīt )

Connecting to %s

%d bloggers like this: