piatok, 22 november 2013 13:01 Written by 2346 times
Rate this item
(3 votes)

SQL - Vkladanie záznamov do tabuľky z inej tabuľky s kontrolou na duplicitu.

Zdravím databázových programátorov.
Programátori, ktorí programujú databázové aplikácie sa už mnohokrát stretli s problémom, ako efektívne vyriešiť problém s vložením viacerých záznamov do "cieľovej" tabuľky z inej "zdrojovej" tabuľky s tým, že pred vložením záznamu do "cieľovej" tabuľky sa skontrolujú záznamy na duplicitu.

Predpokladám, že čitateľ tohto príspevku vie, čo je to duplicita záznamov, ak nie, jednoduché vysvetlenie pre duplicitu: znamená, že sa pokúšame vložiť do "cieľovej" tabuľky rovnaký záznam, ktorý má splnenú podmienku z kľúčov (buď primárneho alebo akéhokoľvek "UNIQUE" - jedinečného kľúča definovaného na "cieľovej" tabuľke). Pretože, ak by sme sa pokúsili vložiť do akejkoľvek tabuľky záznam, ktorého dáta sa zhodujú s hodnotami v spomenutých kľúčoch, databázový systém nám odmietne takýto záznam do tabuľky vložiť.
 
Ďalej predpokladám, že programátor ovláda základy SQL a PL SQL v prostredí Oracle 11g.

Vytvoríme si príklad.
Úloha: Potrebujeme prekopírovať dnešné záznamy zo "zdrojovej" tabuľky do "cieľovej", s kontrolou na duplicitu na primárny kľúč.
 
TABLE_SOURCE - "zdrojová" tabuľka z ktorej sa záznamy kopírujú.
TABLE_DEST - "cieľová" tabuľka do ktorej sa záznamy kopírujú zo "zdrojovej" tabuľky.
Stľpec ID_LOG - je primárnym kľučom v obidvoch tabuľkách.
Trocha si príklad zjednodušíme a máme tabuľky s úplne zhodnou štruktúrou.
 
Jednoduchý príklad, ako by na prvý pokus programátor chcel kód vytvoriť napr. pomocou anonymného PL SQL bloku:

 

DECLARE 
    ID_LOG_VAR TABLE_SOURCE.ID_LOG%TYPE;
BEGIN
    FOR REC IN (SELECT TS.ID_LOG, TS.NAME_DESC, TS.DATE_TIME FROM TABLE_SOURCE TS WHERE TS.DATE_TIME>TRUNC(SYSDATE))
	/* napríklad takto: */
	BEGIN
	    SELECT TD.ID_LOG INTO ID_LOG_VAR FROM TABLE_DEST TD WHERE TD.ID_LOG=REC.ID_LOG;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                ID_LOG_VAR := 0;
        END;
        IF ID_LOG_VAR = 0 THEN
            INSERT INTO TABLE_DEST (ID_LOG, NAME_DESC, DATE_TIME) 
            VALUES (REC.ID_LOG, REC.NAME_DESC, REC.DATE_TIME);
        END IF;
        /* alebo napríklad takto:
	BEGIN
            INSERT INTO TABLE_DEST (ID_LOG, NAME_DESC, DATE_TIME) 
            VALUES (REC.ID_LOG, REC.NAME_DESC, REC.DATE_TIME);		
        EXCEPTION
            WHEN DUP_VAL_ON_INDEX THEN
                CONTINUE;
	END;
        */
    END LOOP;
    COMMIT;
EXCEPTION
    WHWN OTHERS THEN
        ROLLBACK;
END;

 

Teraz si ukžážeme, ako to môžeme celé zjednodušiť.
V tomto prípade ale vytvoríme jednoduchý SQL kód, ktorý nám urobí toto všetko naraz jedným príkazom.

INSERT /*+ ignore_row_on_dupkey_index(TABLE_DEST, PK_TABLE_DEST) */  
INTO TABLE_DEST (ID_LOG,NAME_DESC,DATE_TIME)
SELECT TS.ID_LOG, TS.NAME_DESC, TS.DATE_TIME FROM TABLE_SOURCE TS WHERE TS.DATE_TIME>TRUNC(SYSDATE);
          
COMMIT;

 

Hotovo.
 
Vysvetlenie:
Na celé toto sme zneužili SQL hint "ignore_row_on_dupkey_index".  
PK_TABLE_DEST - je názov primárneho indexu (ako objektu) v "namespace" schéme, v ktorej sa tabuľka nachádza.
Týmto hintom sme jednoducho povedali databázovému serveru, že záznamy, ktoré sú duplicitné na hodnotu primárneho kľúča, nemá do "cieľovej" tabuľky vložiť a má ich jednoducho ignorovať. Tento hint funguje aj pre iné indexy, samozrejme aj na nie "UNIQUE" indexy, vtedy ale musíme rátať s tým, že ak hint síce zachytí duplicitu na hodnoty v zadanom indexe, ale potom pre hodnoty v primárnom kľúči alebo iných UNIQUE kľúčoch (ak sú na tabuľke vytvorené) duplicitu nezachytí a SQL príkaz jednoducho zlyhá. Čiže treba mať na pamäti tento fakt.
 
S pozdravom a prajem veľa kódu bez chýb:)

Last modified on piatok, 22 november 2013 20:03
Alojz Benďák

Autor je administrátor webu a venuje sa programovaniu takmer 20 rokov.



  • prvý kontakt s počítačmi na strednej škole - PMD 1,2,3, PP06, Atari, Sinclair (programovanie v jazyku: strojový kód, basic a pascal):  2 roky
  • na VŠ - jazyky Turbo pascal a Turbo C: 1rok
  • programovanie databázového ekonomického širokoškálneho software v Delphi a C++ Borland (databázy Paradox): 8 rokov v Codex s.r.o. Nitra
  • programovanie webových aplikácií na databázach MySQL a MS SQL server: 1 roky na živnosť (firmy: Hermes Nitra, Schindler v Bratislave)
  • pracoval ako DBA v Homecredit Brno a vo VÚB Bratislava ako vyvojár + optimalizácie v SQL a PL/SQL na Oracle 11g (firma Accenture)
  • programoval programy pre súkromné firmy pre HW na skenovanie povrchov nádrží pre skladovanie tekutých palív (pristroj Leica)
  • momentálne pracuje ako DBA pod Oracle 11g a ako vyvojár vnútropodnikových databázových aplikacií v PHP, SQL, C++, C# a interface v PL/SQL: 8 rokov
  • ďalej programuje s HTML, CSS, Ajax, Javascript, VBA, MS visual C++ a C# malé podporné aplikácie
  • certifikát SQL expert for Oracle 11g   
  • spolupracuje a aktívne učí pre počítačové firmy: Lapis, IVIT v Nitre