DB2 Flashcards
PL/SQL
• PL/SQL - Procedural Language/SQL
• PL/SQL rozšiřuje SQL o konstrukce s procedurálního programování.
• Doplňuje SQL, nikoliv nahrazuje.
– PL/SQL kombinuje možnosti deklarativního a imperativního programování.
Kurzory
- Privátní pracovní oblasti, které jsou databázovým serverem vytvořeny pro každý příkaz SQL.
- Kurzory jsou v podstatě ukazatele do paměti, ne přímo do dat. Výsledné záznamy dotazu jsou do paměti vloženy ve chvíli otevření kurzoru.
- V podstatě se jedná o mechanismus, kdy SELECT příkazu přiřadíme jméno, přes které pak můžeme manipulovat s daty získanými příkazem.
- Implicitní kurzory jsou vytvářeny automaticky databázovým serverem, není nutné je otevírat, zavírat, deklarovat nebo z něj načítat data,
- Explicitní – deklarované programátorem
Základní kroky pro práci s explicitními kurzory:
- Deklarace kurzoru
- Otevření kurzoru
- Výběr dat prostřednictvím kurzoru
- Uzavření kurzoru
Explicitní kurzory - syntaxe, testování stavu, + podívat se na příklad
- Deklarace kurzoru CURSOR IS <p>; - Otevření kurzoru OPEN ; - Výběr dat prostřednictvím kurzoru (opakovat v cyklu) FETCH INTO ; - Uzavření kurzoru CLOSE ;
Pro testování stavu kurzoru jsou k dispozici atributy %ROWCOUNT Zjištění pořadového čísla aktuálního záznamu (pokud nebyl vybrán žádný, je hodnota 0) %FOUND Pokud poslední příkaz FETCH načetl nějaký záznam, má atribut hodnotu TRUE Používá se pro zjišťování konce cyklu %NOTFOUND Používá se pro zjišťování konce cyklu %ISOPEN Pokud je kurzor otevřen, má hodnotu TRUE Použití: %ROWCOUNT</p>
Práce s implicitními kurzory
a) Příkaz SELECT … INTO … FROM … musí vrátit alespoň jeden a nejvýše jeden řádek, počet sloupců musí odpovídat počtu proměnných uvedených za klauzulí INTO včetně použitelnosti datových typů.
b) Následující příklad ukazuje využití implicitního kurzoru pro sady výsledků s omezeným počtem řádků (řekněme méně než 100)
BEGIN
FOR x IN (SELECT jmeno, Id FROM trpaslici)
loop
DBMS_OUTPUT.PUT_LINE(‘Jméno ‘ || x.jmeno || ‘, Id ‘ || x.Id);
END LOOP;
END;
Explicitní vs. implicitní kurzory
- Implicitní kurzory jsou obecně výkonnější.
- Implicitní kurzory jsou z pohledu kódu úspornější a automatizují operace OPEN, CLOSE, FETCH a EXIT WHEN .. %NOTFOUND explicitního kurzoru.
- Nehrozí opomenutí EXIT WHEN, což má za následek uváznutí v nekonečné smyčce.
- Explicitní kurzory lze předávat parametrem mezi jednotlivými procedurami/funkcemi.
- Využívá se při tom datový typ SYS_REFCURSOR.
- Větší flexibilita než implicitní kurzory.
Záznamy
Struktura typu záznam zapouzdřuje více položek i rozdílných datových typů. Deklarace záznamu DECLARE TYPE IS RECORD ( [, …] ); Příklad DECLARE TYPE rec_ucitel IS RECORD ( jmeno ucitel.jmeno%TYPE, Id ucitel.Id%TYPE ); Nebo po zjednodušení jen DECLARE rec_ucitel ucitel%ROWTYPE;
Práce s kurzory a záznamy
DECLARE
rec_trpaslik a_snehurka.trpaslici%ROWTYPE;
CURSOR k1 IS SELECT jmeno, Id FROM a_snehurka.trpaslici;
BEGIN
OPEN k1;
LOOP
FETCH k1 INTO rec_trpaslik.jmeno, rec_trpaslik.id;
EXIT WHEN k1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘Jméno ‘ || rec_trpaslik.jmeno || ‘, Id ‘ || rec_trpaslik.Id);
END LOOP;
CLOSE k1;
END;
S využitím záznamů můžeme s kurzory pracovat mnohem efektivněji Cyklus FOR s explicitním kurzorem (kurzor v tomto případě nemusíme ani otevírat ani zavírat, dokonce ani cyklicky vybírat data pomocí příkazu FETCH, všechny tyto úkony za nás provede server standardně) Příklad DECLARE rec_ucitel ucitel%ROWTYPE; CURSOR k1 IS SELECT jmeno, Id FROM ucitel; BEGIN FOR rec_ucitel IN k1 LOOP DBMS_OUTPUT.PUT_LINE('Jméno ' || rec_ucitel .jmeno || ', Id ' || rec_ucitel.Id); END LOOP; END;
Kurzory s parametry
Kurzor můžeme rozšířit o parametry, které budou dosazeny do dotazu až během otevření kurzoru
Deklarace explicitního kurzoru s parametrem
CURSOR [( , … )] IS <p>;
Příklad DECLARE rec_ucitel ucitel%ROWTYPE; CURSOR k1 (v_jmeno VARCHAR2) IS SELECT jmeno, Id FROM ucitel WHERE jmeno LIKE (v_jmeno || '%'); BEGIN FOR rec_ucitel IN k1 (‘Za’) LOOP DBMS_OUTPUT.PUT_LINE('Jméno ' || rec_ucitel .jmeno || ', Id ' || rec_ucitel.Id); END LOOP; FOR rec_ucitel IN k1 (‘Sm’) LOOP DBMS_OUTPUT.PUT_LINE('Jméno ' || rec_ucitel .jmeno || ', Id ' || rec_ucitel.Id); END LOOP; END; </p>
Ošetření chyb PL/SQL
V zásadě se mohou v PL/SQL vyskytnout 2 druhy chyb:
Syntaktické – projeví se ještě v procesu kompilace (upozorní nás na ně překladač)
Run-time – projeví se až za běhu programu
Nejčastěji se vyskytují následující výjimky:
DUP_VAL_ON_INDEX výskyt duplicitní hodnoty ve sloupci, který připouští jen jedinečné hodnoty
INVALID_NUMBER neplatné číslo nebo data nemohou být převedena na číslo
NO_DATA_FOUND nebyly nalezeny žádné záznamy
TOO_MANY_ROWS dotaz vrátil více než jeden záznam
VALUE_ERROR problém s matematickou funkcí
ZERO_DIVIDE dělení nulou
+ všeobecná syntaxe (a RAISE (ten může i vlastní výjimky))
+ obsluha výjimky
+ chyba má SQLCODE a SQLERRM
+ nezachycené výjimky jsou propagovány výš a výš až do hostitelského prostředí
Procedury a funkce
Bloky příkazů jazyka PL/SQL lze pojmenovat a uložit ve spustitelné formě do databáze. Těmto blokům říkáme procedury, resp. funkce.
Vlastnosti procedur a funkcí:
Jsou uloženy ve zkompilovaném tvaru v databázi.
Mohou volat další procedury či funkce, či samy sebe.
Lze je volat ze všech prostředí klienta.
Funkce, na rozdíl od procedury, vrací jedinou hodnotu (procedura může vracet hodnot více, resp. žádnou).
+ syntaxe
+ parametry, vstupní, výstupní, vstupně-výstupní
Aktivní pravidla
aktivní pravidla (active rules)
◦ pro vyhodnocení složitých podmínek kladených na data (tzv. business rules)
◦ kontrola na databázové úrovni
◦ usnadnění práce – auditovatelnost, bezpečnost
triggery (triggers) ◦ v překladu „spoušť“ , „kohoutek“ ◦ jiný název pro aktivní pravidla ◦ v praxi je dávána přednost názvu trigger AKTIVNÍ PRAVIDLA = TRIGGERY
• Trigger („spoušť“) je “procedura”, která se spustí při
výskytu nějaké sledované události.
• V relačních databázích trigger = aktivní pravidlo
• Od SQL 1999
Vs. integritní omezení (ty jsou jednodušší, rychlejší, ale ne vždy postačující, nejsou auditovatelné)
Starburst
• IBM, Almaden Research Center -> Starburst Active Rule System • Získalo popularitu -> Jednoduchá syntaxe a sémantika -> Množinově orientovaná • Pravidla založena na ECA-paradigmatu (Event-Condition-Action)
• Událost (Event)
-> SQL-příkazy pro manipulaci s daty (INSERT, DELETE,
UPDATE)
• Podmínka (Condition)
-> booleovský predikát nad stavem databáze, vyjádřen pomocí SQL
• Akce (Action)
-> provádí libovolné SQL dotazy (například SELECT, INSERT, DELETE, UPDATE)
-> navíc mohou obsahovat příkazy pro manipulaci s aktivními pravidly a transakční instrukci ROLLBACK WORK
Sémantika aktivních pravidel
• Když nastane Událost, pokud je splněna Podmínka, proveď Akci.
Říkáme, že pravidlo je:
• spuštěno (triggered) – pokud nastane příslušná Událost
• vyhodnoceno (considered) – po vyhodnocení dané
Podmínky
• vykonáno (executed) – po provedení jeho Akce
Vlastnosti aktivních pravidel
• Jsou přidané do schématu databáze a jsou sdílené všemi aplikacemi.
• Mohou být dynamicky aktivovány a deaktivovány každou transakcí.
• Mohou tvořit skupiny.
• Každé pravidlo ve Starburstu má jedinečné jméno a je spojeno s jednou určitou tabulkou, zvanou rule’s target.
• Každé aktivní pravidlo může sledovat více Událostí, tzv. rule’s triggering operations.
• Jeden SQL příkaz může být sledován více pravidly.
-> Pořadí pravidel je určeno na základě jejich částečného uspořádání.
Problémy s triggery
• standardizace
- > není
- > snaha by byla od 80. let v normě SQL-92 nejsou standardně uvedeny
- SQL 1999 – základní podmínky realizace, ale …
- proprietární řešení výrobců DB systémů
- > rozdíly v syntaxi i sémantice
- > vazba aplikace na konkrétního výrobce
• technické problémy
- > nekonečné vzájemné volání triggerů (retriggering)
- > několik možných řešení
- > používají se všechna
Triggery v PL/SQL
- Jedná se o PL/SQL objekty spouštěné vyvoláním příslušné události v DB
- Vyvolání může způsobit DML událost, DDL operace nebo speciální DB událost
- Vyvolat trigger můžeme před nebo po provedení operace
- Existuje také možnost vyvolání místo příslušné operace
- Je možné omezit vyvolání podmínkou
+ DML triggery (delete, insert, update)
+ DML triggery vyvolání pro celou operaci / for each row / sloučením OR
Syntaxe DML triggeru:
CREATE OR REPLACE TRIGGER jméno BEFORE | AFTER | INSTEAD OF DELETE | INSERT | UPDATE OF cols ON tabulka [ způsob odkazování ] [ FOR EACH ROW ] [ WHEN ( podmínka ) ] AS pl/sql kód
Způsob odkazování • Definuje, jak budou přístupné původní a nové záznamy (vstupující do DML operací) • Implicitně :new, :old, :parent • Existuje klauzule: REFERENCING [ OLD AS jméno ] [ NEW AS jméno ] [ PARENT AS jméno ]
- Pro BEFORE a AFTER je trigger chápán jako tzv. statement trigger a vyvolán je pouze jedenkrát (není-li klauzulí FOR EACH ROW explicitně stanoveno jinak)
- V případě INSTEAD OF triggeru je trigger implicitně chápán jako řádkový trigger, protože zde statement trigger nemá prakticky žádný význam
DDL triggery
• Jsou vyvolány po provedení DDL příkazu • Mohou být BEFORE, AFTER • Mohou být omezeny podmínkou (WHEN) • Definují se dvěma způsoby: ->jméno události ON DATABASE -> jméno události ON jméno schématu • Existuje řada definovaných událostí, např. CREATE, ALTER, DROP, RENAME, GRANT, COMMENT, AUDIT, DDL
Triggery databázových událostí
- Pracují stejně jako DDL triggery, pouze je jiná množina povolených událostí
- Typicky se jedná o události zásadních událostí v celé databázové instanci, např. STARTUP, SHUTDOWN, LOGON, LOGOFF, SERVERERROR, SUSPEND apod.
- Uvnitř DDL a databázových triggerů nelze provádět jiné DDL operace
- Velmi specifické použití
Omezení triggerů
- BEFORE a AFTER triggery nelze specifikovat nad pohledy
- V BEFORE triggerech není možné zapisovat do :old záznamů
- V AFTER triggerech nelze zapisovat ani do :old, ani do :new záznamů
- INSTEAD OF triggery pracují jen s pohledy, mohou číst :old i :new, ale nemohou zapisovat ani do jednoho
- Nelze kombinovat INSTEAD OF a UPDATE
- Nelze definovat trigger nad LOB atributem
- Nelze použít transakce, pokud je zpracovávána jiná transakce (tedy prakticky nelze použít transakce vůbec)
- Není možné sledovat (ani modifikovat) data v tabulce, která způsobila vyvolání DML triggeru – toto omezení je často velice nepříjemné
- Jediné známé řešení: zrcadlení tabulek
Emulace AUTOINCREMENT
- Některé SQL databázové systémy používají modifikátor typu AUTOINCREMENT pro definici číslování primárních klíčů
- Je možné toto chování emulovat umístěním BEFORE INSERT triggeru, který vyčte novou hodnotu ze sekvence a modifikuje :new.id na tuto hodnotu
Základní možnosti uložení XML dat
- Uložení v systému souborů
- Uložení v relační databázi
- Uložení v objektově orientovaném systému
- Uložení v objektově-relačním databázovém systému
- Nativní XML úložiště
Uložení xml v systému souborů
1) konstrukce celých DOM stromů
- > nutnost držet celý dokument v paměti
- > nutnost analyzovat celý text
2) možnost značkování, abychom se vyhnuli nevýhodám
- > zbytečně složité, nemožná aktualizace
Uložení xml v relační databázi
1) buď xml jako LOB
- snadná implementace
- s xml se musí pracovat jako s celkem
2) xml dokument se rozloží do několika tabulek
- dekompozice xml dokumentu do relačního modelu
- relační model špatně hierarchickou strukturu => je to pomalé
- některé dotazy rychlé převedením na SQL dotaz
a) Uložení hran stromu
b) Odvození schématu z DTD