; ; RAISE ; END; ; [WHEN ;] ; END; ;
Použití kurzorů s parametry si ukážeme na následujícím příkladu:
DECLARE rec_ucitel ucitel%ROWTYPE; CURSOR c_1 (v_jmeno VARCHAR2) IS SELECT jmeno, id FROM ucitele WHERE jmeno LIKE (v_jmeno || '%'); '%') David Žák IDAS2/6 – Jazyk PL/SQL, syntaxe bloku, cykly, kurzory, záznamy
16
BEGIN FOR rec_ucitel IN CURSOR c_1('Ža') c_1 LOOP BMS_OUTPUT.PUT_LINE(rec_ucitel.v_jmeno DBMS_OUTPUT.PUT_LINE( ||', ', '||rec_ucitel.v_id); END LOOP; END;
Jak je z přeloženého příkladu zřejmé, na výstupu budou pouze ty řádky, kdy jméno učitele začíná řetězcem uvedeným při volání kurzoru. Stejný kurzor pak může být v rámci jednoho programového bloku použit vícekrát, pokaždé bude proveden na základě uvedených parametrů.
Implicitní kurzory Implicitní kurzory databázový systém Oracle otevírá a uzavírá automaticky. Samozřejmě i v těchto případech má každý prováděný DML příkaz přidělenu kontextovou oblast v paměti PGA a tedy i kurzor. Výraznějšího ýraznějšího zjednodušení dosáhneme použitím použití vnořeného dotazu v cyklu FOR. V takovém případě není potřeba deklarovat ani kurzor, ani záznam. BEGIN FOR rec_ucitel IN (SELECT ( jmeno, id FROM ucitele) LOOP DBMS_OUTPUT.PUT_LINE( BMS_OUTPUT.PUT_LINE(rec_ucitel.v_jmeno ||', ', '||rec_ucitel.v_id); END LOOP; END;
Dále si ukážeme princip použití dalších příkazů DML v kódu PL/SQL. BEGIN UPDATE ucitele SET jmeno = 'Josef' 'Josef WHERE jmeno = 'Pepa'; 'Pepa DBMS_OUTPUT.PUT_LINE( BMS_OUTPUT.PUT_LINE('Upraveno řádků :'||SQL%ROWCOUNT); ); END;
Vidíme, že i v těchto případech můžeme pracovat s hodnotami atributů pro testování stavu kurzoru. David Žák IDAS2/6 – Jazyk PL/SQL, syntaxe bloku, cykly, kurzory, záznamy
17
Pojmy k zapamatování Příkazy a funkce:
PL/SQL, programový blok, podmínky, cykly, kurzory
Problém:
psaní kódu PL/SQL, práce s dotazy v kódu PL/SQL
Shrnutí V této lekci jste se seznámili s jazykem PL/SQL, syntaxí programových bloků, příkazy pro řízení běhu programu pro větvení a cykly. Velice důležitá je také znalost použití SQL příkazů v rámci kódu PL/SQL. • PL/SQL je programové rozšíření jazyka SQL na platformě databáze databáze Oracle pro řešení těch problémů, na které jazyk SQL nestačí. nestačí • Pro získání hodnot z SQL dotazu, který vrací jediný řádek, můžeme použít syntaxi SELECT … INTO … • Kurzory jsou privátní paměťové oblasti, do nichž se ukládají výsledky dotazu. • Práce s explicitními icitními kurzory zahrnuje jejich deklaraci, otevření, čtení záznamů v cyklu a uzavření kurzoru. • V případě použití příkazu FOR s explicitním kurzorem není třeba kurzor otevírat a uzavírat. • Práci s kurzory významně mohou usnadnit proměnné typu záznam, které jsou nezbytné použít v cyklech FOR s kurzorem. • Při použití cyklu FOR s vnořeným dotazem (implicitní kurzor) není třeba záznam deklarovat. Otázky na procvičení 1. 2. 3. 4. 5.
Jaký je rozdíl mezi jazyky SQL a PL/SQL? PL/SQL Jaké cykly je možné použít v kódu PL/SQL, popište jejich syntaxi? Co to je kurzor? Jakým způsobem se pracuje s explicitním kurzorem s parametry? Co je to proměnná typu záznam a jak se s ní pracuje?
Odkazy a další studijní prameny • • •
http://www.techonthenet.com/oracle (syntaxe příkazů SQL jazyka a funkcí) http://www.oracle.com/technetwork/database/enterprise http://www.oracle.com/technetwork/database/enterpriseedition/documentation (dokumentace k databázové platformě Oracle) http://www.penguin.cz/noviny/?id=chip/index (seriál Databáze standardu SQL z časopisu CHIP)
David Žák IDAS2/6 – Jazyk PL/SQL, syntaxe bloku, cykly, kurzory, záznamy
18
Odkazy a další studijní prameny • •
LACKO, L. Oracle, správa, programování a použití použití databázového systému. systému Praha: Computer Press, 2007. ISBN 80-251-1490-2. 80 URMAN, S.,., HARDMAN, R., MCLAUGHLIN, M. Oracle - programování v PL/SQL.. Computer Press, 2008. ISBN 978-80-251-1870-2
David Žák IDAS2/6 – Jazyk PL/SQL, syntaxe bloku, cykly, kurzory, záznamy
19
7. blok - část A
Jazyk PL/SQL - zpracování chyb, řízení transakcí
Studijní cíl Tento blok je věnován ošetření chyb a řízení transakcí v kódu PL/SQL.
Doba nutná k nastudování
2 - 3 hodiny
Průvodce studiem Při studiu tohoto bloku se předpokládá, že čtenář je obeznámen s jazykem SQL, je schopen napsat jednoduchý program v libovolném programovacím jazyce a zná základy jazyka PL/SQL. 1. Zpracovávání chyb Dobře napsané programy musí být být schopny správně zpracovávat chyby a umět se z nich vzpamatovat. V PL/SQL se s chybami pracuje pomocí výjimek a zachytávání výjimek. Výjimky je možné navázat na chyby Oracle nebo je možné si definovat vlastní uživatelsky definované chyby. V následujícím textu extu se seznámíme se syntaxí pro práci s výjimkami a s pravidly pro šíření výjimek. Výjimky v PL/SQL se podobají výjimkám v jazyce Java. Ale na rozdíl od výjimek v jazyce Java nejsou výjimky v PL/SQL objekty a nemají definovány žádné metody. V PL/SQL se mohou hou objevit 2 typy chyb: • •
chyby při překladu, které hlásí překladač a které je nezbytné opravit, aby mohl být předkompilován a chyby za běhu programu, které zpracovávají zachytávače výjimek.
Jestliže doje k chybě za běhu programu, je vyvolána výjimka. Provádění Provádění kódu (řízení běhu programu) poté přejde do části zachytávače výjimek, která je oddělena od zbytku programu. Toto oddělení má kromě vlastního zpřehlednění kódu také tu výhodu, že zde budou zachyceny všechny chyby. Program tedy nebude pokračovat dál od příkazu, který způsobil chybu, ale vždy přejde do zachytávače výjimek a poté do libovolného vnějšího bloku. Existují 2 typy výjimek: předdefinované předdefin a uživatelsky definované. David Žák IDAS2/7A – Jazyk PL/SQL - zpracování chyb, řízení transakcí
1
Předdefinované výjimky Oracle má mnoho předdefinovaných výjimek, které odpovídají běžným chybám, viz následující tabulka: Oracle chyba
Hodnota SQLCODE Vyvolána je:
ORA06530
-6530
Přiřazení hodnoty k atributu neinicializovaného (null) objektu.
COLLECTION_IS_NULL ORA06531
-6531
Použití jiné sběrné metody (collection method) než EXISTS na neinicializovanou (atomicky null) vnořenou tabulku nebo pole (varray) nebo přiřazení hodnoty do položek neinicializované vnořené tabulky nebo pole
CURSOR_ALREADY _OPEN
ORA06511
-6511
Snaha o otevření již jednou otevřeného kurzoru (cursor), před opětovným otevřením musíte kurzor nejdříve zavřít, kurzorový FOR cyklus otevírá kurzor automaticky, takže jej nelze uvnitř cyklu opět otevřít.
DUP_VAL_ON_INDEX
ORA00001
-1
Snaha o uložení totožné (již existující) hodnoty do sloupce, který je označen jako unique index - index specifických hodnot.
INVALID_CURSOR
ORA01001
-1001
Nepovolená operace s kurzorem (cursor), například zavření neotevřeného kurzoru.
INVALID_NUMBER
ORA01722
-1722
Selhání převodu mezi řetězcem znaků a číslem v příkazu SQL z toho důvodu, že řetězec neobsahuje platné číslo. V procedurálním příkazu je vyvolána výjimka VALUE_ERROR.
LOGIN_DENIED
ORA01017
-1017
Snaha nalogovat se na Oracle server s neplatným jménem (username) a/nebo heslem (password).
NO_DATA_FOUND
ORA01403
+100
Pokud příkaz SELECT INTO nevrátí žádné řádky nebo se odkazujete na smazaný prvek vnořené tabulky nebo neinicializovaný prvek index-by tabulky. Od příkazu FETCH se případně dá očekávat navrácení prázdného řádku (no rows) a v tomto případě výjimka není vyvolána. SQL kolektivní (group) funkce jako AVG a SUM vracejí vždy hodnotu nebo null. Proto také příkaz SELECT INTO volající group funkci nikdy nevyvolá výjimku NO_DATA_FOUND.
NOT_LOGGED_ON
ORA01012
-1012
Pokud se PL/SQL program pokouší provést databázovou operaci bez předchozího připojení k serveru Oracle.
Výjimka ACCESS_INTO_NULL
David Žák IDAS2/7A – Jazyk PL/SQL - zpracování chyb, řízení transakcí
2
PROGRAM_ERROR
ORA06501
-6501
Vnitřní problém (internal problem) při běhu programu.
ROWTYPE_MISMATCH ORA06504
-6504
Hlavní (host) a PL/SQL kurzorová proměnná vyžádaná dosazením mají nekompatibilní návratové typy. Například, když přenecháte otevřený host kurzor uloženému podprogramu, pak návratové typy aktuálních a formálních parametrů musí být kompatibilní.
STORAGE_ERROR
ORA06500
-6500
PL/SQL vyčerpalo paměť, nebo je paměť poškozená.
SUBSCRIPT_BEYOND _COUNT
ORA06533
-6533
Odkaz na prvek vnořené tabulky (nested table) či pole (varray) s číslem indexu větším než je počet dostupných prvků.
SUBSCRIPT_OUTSIDE _LIMIT
ORA06532
-6532
Odkaz na prvek vnořené tabulky (nested table) či pole (varray) s číslem indexu, který je mimo povolený rozsah (např. -1). 1).
TIMEOUT_ON _RESOURCE
ORA00051
-51
Vypršel čas (time-out), během kterého Oracle čeká na prostředky (resource).
TOO_MANY_ROWS
ORA01422
-1422
Příkaz SELECT INTO vrací více než jeden řádek.
VALUE_ERROR
ORA06502
-6502
Chyba aritmetická, převodní, zkrácení nebo omezení velikosti. Například, když dosadíte hodnotu ze sloupce do znakové proměnné a tato hodnota je delší než nedeklarovaná délka proměnné. V procedurálních příkazech je výjimka vyvolána, pokud selže převod mezi řetězcem a číslem (v SQL je to výjimka INVALID_NUMBER).
ZERO_DIVIDE
ORA01476
-1476
Dělení nulou.
Příklad: DECLARE v_jmeno ucitel.jmeno%TYPE; v_Id ucitel.Id%TYPE; BEGIN SELECT jmeno, Id INTO v_jmeno, v_Id FROM ucitel WHERE Id=2; DBMS_OUTPUT.PUT_LINE('Jméno: ' || v_jmeno); DBMS_OUTPUT.PUT_LINE('Jméno DBMS_OUTPUT.PUT_LINE('Id DBMS_OUTPUT.PUT_LINE('Id: ' || v_Id); EXCEPTION -- ošetření výjimky při nenalezení dat WHEN NO_DATA_FOUND THEN David Žák IDAS2/7A – Jazyk PL/SQL - zpracování chyb, řízení transakcí
3
DBMS_OUTPUT.PUT_LINE( DBMS_OUTPUT.PUT_LINE('Data nenalezena'); -- ošetření výjimky při nalezení více řádků WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE( DBMS_OUTPUT.PUT_LINE('Mnoho řádků'); END;
Uživatelsky definované výjimky V PL/SQL má uživatel možnost nadefinovat si vlastní výjimky. Tyto výjimky se deklarují v deklarační části, vyvolávají se v exekuční části příkazem RAISE a zpracovávají se v oblasti výjimek. Deklarace výjimky začíná jejím jménem následovaným klíčovým slovem EXCEPTION. EXCEPTION Pro vlastní tní výjimky je SQLCODE rovno 1 a SQLERRM vrací Text ‘User-Defined Defined Exception‘. Syntaxe DECLARE
Příklad deklarace a vyvolání výjimky pojmenované PRILIS_MNOHO_TRPASLIKU: DECLARE PRILIS_MNOHO_TRPASLIKU RILIS_MNOHO_TRPASLIKU EXCEPTION; v_pocet_trpasliku NUMBER; BEGIN select count(*) INTO v_pocet_trpasliku FROM trpaslici; IF v_pocet_trpasliku > 7 THEN RAISE PRILIS_MNOHO_TRPASLIKU; END IF; EXCEPTION WHEN PRILIS_MNOHO_TRPASLIKU THEN DBMS_OUTPUT.PUT_LINE('Trpaslíků může být max. max 7!'); '); David Žák IDAS2/7A – Jazyk PL/SQL - zpracování chyb, řízení transakcí
4
END;
Použití raise_application_error Balíček DBMS_STANDARD dodávaný spolu s Oracle poskytuje jazykové prostředky, které mohou vašim aplikacím napomoci při spolupráci s Oracle. Například procedura raise_application_error umožňuje zveřejnit uživatelsky definované chybové hlášky z uložených podprogramů (stored subprograms). subprograms). Touto cestou můžete své aplikaci oznamovat chyby a vyhnout se vracení neošetřených neošetřenýc chyb. Příkaz raise_application_error má následující syntax: RAISE_APPLICATION_ERROR(error_number, (error_number, message[, {TRUE | FALSE}]);
kde error_number je záporné celé číslo č (integer) v rozsahu -20000 .. -20999 20999 a message je řetězec maximální délky 2048 bytů. Jestliže je třetí nepovinný parameter TRUE, chyba je uložena do zásobníku,, pokud je FALSE (default), chyba nahradí všechny dosud uložené chyby. Aplikace může volat raise_application_error pouze ze spustitelného uloženého podprogramu. Je-li zavolána, raise_application_error ukončí podprogram a vrátí uživatelsky definovanou chybu (číslo chyby) a zprávu aplikaci. Číslo chyby a zpráva pak může být odchytnuta stejně jako každá jiná Oracle chyba. V následujícím příkladu je zavolána procedura raise_application_error, pokud není uvedena mzda u daného zaměstnance, zaměstnance v opačném případě provede navýšení mzdy: mzdy DECLARE v_mzda NUMBER; v_id NUMBER := 1; BEGIN SELECT mzda INTO v_mzda mzda FROM zamestnanci WHERE zam_id = v_id; IF v_mzda IS NULL THEN raise_application_error raise_application_error(-20101, 'Mzda neuvedena'); ELSE UPDATE zamestnanci SET mzda = p_mzda * 1,05 WHERE zam_id = v_id; v_id END IF; END;
Volající modul obdrží výjimku, kterou může zpracovat pomocí funkcí na zpracování chyb (error-reporting reporting functions) SQLCODE a SQLERRM v handleru OTHERS.
David Žák IDAS2/7A – Jazyk PL/SQL - zpracování chyb, řízení transakcí
5
Zachytávání výjimek ti výjimek. Jakmile dojde k výjimce, přechází tok programu v daném bloku do oblasti Tato oblast se skládá ze zachytávačů pro některé (WHEN
Všeobecná syntaxe pro zpracování výjimek: EXCEPTION > THEN
THEN
Zachytávač ostatních výjimek (OTHERS) zachytí všechny výjimky neošetřené v klauzuli WHEN. Klauzule WHEN OTHERS zachytí všechny výjimky. Je vhodné mít tento „univerzální“ zachytávač na nejvyšší úrovni programu (nejvyšším bloku), protože poté z programu neunikne řádná výjimka. Jinak hrozí, že se chyba bude šířit do vnějšího prostředí.
Když je vyvolána výjimka v exekuční oblasti bloku, postupuje řízení běhu programu podle následujících pravidel: 1. Jestliže má aktuální blok pro danou výjimku zachytávač, spustí jej a blok dokončí jako úspěšný. Řízení pak přechází do vnějšího bloku. 2. Jestliže neexistuje pro danou výjimku v daném bloku zachytávač, dojde k přenosu chyby do vnějšího (bloku). Pro tento blok se provede krok 1 pokud existuje zachytávač pro danou výjimku, jinak se opakuje přenos do dalšího vnějšího bloku. 3. Jestliže vnější blok neexistuje, neexistuje výjimka see dostane do volajícího prostředí. Pokud je vyvolána výjimka v deklarační oblasti při přiřazování, přechází výjimka ihned do vnějšího bloku. Pokud je vyvolána výjimka v zachytávači výjimek, přechází opět řízení ihned do vnějšího bloku. Neboli platí, že v danou chvíli může být aktivní pouze jediná výjimka.
David Žák IDAS2/7A – Jazyk PL/SQL - zpracování chyb, řízení transakcí
6
Příklad 1:
Po vyvolání výjimky A je tato zachycena ve vnitřním bloku a program pokračuje dalším příkazem ve vnějším bloku.
Příklad 2:
Po vyvolání výjimky A není tato zachycena ve vnitřním bloku a výjimka je propagována do vnějšího bloku, kde je v sekci výjimek zachycena.
Příklad 3:
Po vyvolání výjimky A není tato zachycena ve vnitřním bloku a výjimka je propagována do vnějšího bloku, bohužel ani zde není tato výjimka zachycena. Další vnější programový blok již neexistuje a PL/SQL proto propaguje výjimku do volajícího prostředí.
David Žák IDAS2/7A – Jazyk PL/SQL - zpracování chyb, řízení transakcí
7
V zachytávači výjimek lze ke zjištění informací o chybě použít funkcí SQLCODE a SQLERRM (číslo chyby a chybová zpráva). Pro vnitřní výjimky SQLCODE vrací číslo chyby (Oracle error number), které je záporné (tedy kromě výjimky no data found, found, kdy SQLCODE vrací +100). Funkce SQLERRM vrací příslušnou chybovou zprávu (začínající kódem dané chyby). Pro uživatelské výjimky (user-defined defined exceptions) vrací fce SQLCODE +1 a SQLERRM vrací 'User-Defined Exception'
Příklad: DECLARE v_vysledek NUMBER(9,2); BEGIN v_vysledek := 5/0; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(' Chyba '); DBMS_OUTPUT.PUT_LINE('Kód chyby:' || SQLCODE); DBMS_OUTPUT.PUT_LINE('Popis chyby:' || SQLERRM); END;
Při práci s výjimkami mohou vzniknout následující potřeby: potřeby 1) pokračování po vyvolání výjimky v provádění příkazů v rámci daného bloku - tuto potřebu ošetříme vložením dalšího (vnitřního) bloku do daného bloku a výjimku zpracujeme v rámci tohoto nově vloženého bloku. bloku 2) použití vyhledávací proměnné - pokud v rámci bloku existujee více příkazů, které mohou vyvolat stejnou výjimku, je vhodné použít pomocnou proměnnou pro uložení označení příkazu, který se bude následně provádět. V zachytávači pak můžeme s hodnotou této proměnné pracovat, pracovat například ji logovat spolu s informací o vyskytnuvší se chybě. 3) opakování transakce - pokud chcete po vyvolání výjimky namísto přerušení transakce tuto transakci zopakovat, je potřeba transakci uzavřít do jednoho vnitřního bloku a poté umístit tento vnitřní blok do cyklu. Před započetím transakce je potřeba označit záchranný bod (savepoint). Pokud je transakce úspěšná, přijmete ji a opustíte cyklus. Pokud však selže, kontrola je předána zachytávači výjimek tohoto vnitřního bloku,, kde odrolujete k savepointu a pokusíte se napravit problém. Program poté oté bude pokračovat dalším cyklem. Je však vhodné, aby cyklus měl jen konečný počet pokusů a bylo možné program ukončit.
David Žák IDAS2/7A – Jazyk PL/SQL - zpracování chyb, řízení transakcí
8
2. Řízení transakcí v PL/SQL Z jedné z předchozích lekcí již znáte, že transakce zajišťují konzistentnost databáze a jejich vlastnosti označované zkratkou ACIT chrání transakční data před narušením. Víme, jak se chovají transakce v jazyce SQL, a zajímá nás, jak to bude v případě, kdy operace DML budou s daty pracovat uprostřed bloků PL/SQL. Pokud váš program selže uprostřed transakce, Oracle Or detekuje chybu a vrátí transakci - odroluje na začátek.. Proto je databáze obnovena do původního stavu automaticky. omocí příkazů COMMIT, ROLLBACK, SAVEPOINT a SET Řízení transakcí se zajišťuje pomocí TRANSACTION. COMMIT je trvalé potvrzení změn databáze provedených během aktuální transakce. ROLLBACK ukončí aktuální transakci a zruší ruší všechny změny provedené od začátku transakce. transakce SAVEPOINT označí aktuální bod zpracování transakce. Používá se pro odrolování transakce k bodu návratu příkazem ROLLBACK TO
9
Nyní vložíme 8 řádků s použitím kódu s autonomní transakcí: DECLARE PRAGMA AUTONOMOUS_TRANSACTION; US_TRANSACTION; BEGIN FOR i IN 3 .. 10 LOOP INSERT INTO at_test (id, popis) VALUES (i, 'Popis pro ' || i); END LOOP; COMMIT; END;
Jak vidíme, celá autonomní transakce byla potvrzena příkazem COMMIT. Pokud nyní provedeme příkaz ROLLBACK; ROLLBACK Dotaz SELECT * FROM at_test; vrátí 8 řádků. Příkaz ROLLBACK je tedy aplikován pouze na řádky vložené z hlavní transakce, ale na řádky vložené autonomní transakcí nemá vliv.
Druhý příklad ukazuje způsob zalogování chyby do tabulky error_logs,, která bude použita jako protokol událostí. Nejdříve vytvoříme tuto tabulku a sekvenci pro generování m hodnot primárního klíče: CREATE TABLE error_logs ( id NUMBER(10) NOT NULL, log_timestamp TIMESTAMP NOT NULL, error_message VARCHAR2(4000), CONSTRAINT error_logs_pk PRIMARY KEY (id) ); CREATE SEQUENCE error_logs_seq;
Dále vytvoříme proceduru pro zalogování chyby jako autonomní transakci: CREATE OR REPLACE PROCEDURE log_errors (p_error_message IN VARCHAR2) AS PRAGMA AUTONOMOUS_TRANSACTION; TONOMOUS_TRANSACTION; BEGIN INSERT INTO error_logs (id, log_timestamp, error_message) VALUES (error_logs_seq.NEXTVAL, logs_seq.NEXTVAL, SYSTIMESTAMP, p_error_message); COMMIT; END;
David Žák IDAS2/7A – Jazyk PL/SQL - zpracování chyb, řízení transakcí
10
Nyní provedeme kód PL/SQL, který vyvolá chybu, která je zachycena a uložena. BEGIN -- Platný příkaz INSERT INTO at_test (id, description) VALUES (998, 'Description for 998'); -- Vynutit neplatný INSERT INSERT INTO at_test (id, description) VALUES (999, NULL); EXCEPTION WHEN OTHERS THEN log_errors (p_error_message => SQLERRM); ROLLBACK; END;
Po vykonání kódu dostaneme hlášku:
PL/SQL procedure successfully
completed.
Existenci dat v tabulce at_test zjistíme příkazem: SELECT * FROM at_test WHERE id >= 998;
Odpovědí bude: no rows selected Podíváme se na obsah tabulky error_logs příkazem SELECT * FROM error_logs;
Získáme výsledek:
ID LOG_TIMESTAMP ERROR_MESSAGE ---- -------------------------------- ---------------------------------1 28-FEB-2012 11:10:10.107625 ORA-01400: cannot insert NULL into ("SCHEMANAME"."AT_TEST"."DESCRIPTION")
Opět vidíme, že ROLLBACK hlavní transakce nezpůsobil změny v potvrzené autonomní transakci. Výjimky a transakce Vyvolání výjimky ani konec bloku neukončují transakci. Jedině v případě, kdy neošetřená výjimka je v bloku nejvyšší úrovně, která se rozšíří do volajícího prostředí, server automaticky transakci odvolá.
David Žák IDAS2/7A – Jazyk PL/SQL - zpracování chyb, řízení transakcí
11
Pojmy k zapamatování PL/SQL, výjimky, zachytávač, řízení transakcí, EXCEPTION, Příkazy a funkce: RAISE, COMMIT, SAVEPOINT, ROLLBACK Problém:
řízení toku programu při vyvolání chyby, zachycení výjimky
Shrnutí V této lekci jste se seznámili s detekcí a zpracováním chyb v kódu PL/SQL. Velké množství chyb b je předdefinováno systémem, ale můžeme vytvářet i uživatelsky definované chyby a tyto programově vyvolat. Důležitou částí je pak pochopení pravidel pro šíření výjimek (není-li (není li výjimka zachycena v lokálním bloku, šíří se do bloku vnějšího, a pokud není zachycena zachycena ani v bloku nejvyšší úrovně, je ošetřena ve volajícím prostředí). Další část dokumentu kumentu se věnovala transakčnímu zpracování. Z hlediska transakčního zpracování je důležité zejména neopomenout příkazy pro řízení transakcí u těch programových kódů, které eré budou realizovat DML příkazy.
Otázky na procvičení 1. Jak se pracuje s uživatelsky definovanými výjimkami? 2. Popište zachytávání a šíření chyb mezi bloky PL/SQL. 3. K čemu slouží funkce SQLCODE a SQLERRM? 4. Jakým způsobem zajistit, aby po výskytu chyby zůstal běh programu v daném modulu? 5. Jak probíhá zpracování transakcí v modulech PL/SQL? Odkazy a další studijní prameny • • • •
http://www.oracle-base.com/articles/misc/AutonomousTransactions.php base.com/articles/misc/AutonomousTransactions.php http://www.techonthenet.com/oracle (syntaxe příkazů SQL jazyka a funkcí) http://www.oracle.com/technetwork/database/enterprise http://www.oracle.com/technetwork/database/enterpriseedition/documentation (dokumentace k databázové platformě Oracle) http://www.penguin.cz/noviny/?id=chip/index (seriál Databáze atabáze standardu SQL z časopisu CHIP)
Odkazy a další studijní prameny • •
LACKO, L. Oracle, správa, programování a použití databázového systému. systému Praha: Computer Press, 2007. ISBN 80-251-1490-2. 80 URMAN, S.,., HARDMAN, R., MCLAUGHLIN, M. Oracle - programování v PL/SQL.. Computer Press, 2008. ISBN 978-80-251-1870-2
David Žák IDAS2/7A – Jazyk PL/SQL - zpracování chyb, řízení transakcí
12
7. blok - část B
Procedury a funkce.
Studijní cíl Tento blok je věnován vytváření procedur a funkcí v jazyce PL/SQL.
Doba nutná k nastudování
2 - 3 hodiny
Průvodce studiem Při studiu tohoto bloku se předpokládá, že čtenář je obeznámen s jazykem SQL, je schopen napsat jednoduchý program v libovolném programovacím jazyce a zná základy jazyka PL/SQL. 1. Procedury a funkce Již v jednom z předchozích bloků jsme se naučili syntaxi bloku a seznámili se se dvěma typy bloků: • •
anonymními, které se překládají vždy, když se spouští, a tyto se neukládají do databáze a pojmenovanými, mezi které řadíme procedury, funkce, balíky a spouště, spouš které se ukládají do databáze a spouští podle potřeby
Procedury a funkce v PL/SQL se chovají podobně jako v jiných jazycích třetí generace. Říkáme jim také podprogramy. Stejně jako jiné databázové objekty se procedury i funkce vytváří příkazy CREATE PROCEDURE či CREATE FUNCTION. Vytváření procedury Základní syntaxe pro vytváření procedury je: CREATE [OR REPLACE] PROCEDURE
1
O jednotlivých parametrech se zmíníme později. Seznam parametrů není povinný. Pokud je neuvedete, nepoužívají se ani v deklaraci ani při volání závorky. Je-li Je potřeba upravit kód procedury, je j vhodným řešením použití klíčových slov OR REPLACE a provést změnu v jediném kroku bez nutnosti odstranění předchozí verze procedury příkazem DROP PROCEDURE
Vytváření funkce Základní syntaxe pro vytváření funkce je velice podobná syntaxi pro vytváření procedury: CREATE [OR REPLACE] FUNCTION FUNCTI
Obdobně jako u procedur je seznam parametrů nepovinný. Pokud je neuvedete, nepoužívají se ani v deklaraci ani při volání závorky. Je-li Je li potřeba upravit kód funkce, je vhodným řešení použití klíčových slov OR REPLACE a provést změnu v jediném kroku bez nutnosti osti odstranění předchozí verze funkce příkazem DROP FUNCTION
David Žák IDAS2/7B – Procedury a funkce.
2
Parametry podprogramů Parametry v deklaraci hlavičky procedury jsou takzvanými formálními parametry. Skutečné parametry obsahují hodnoty, které procházejí do procedury při jejím volání a obsahují výsledky procedury při jejím ukončení. Při volání procedury se formální parametry propojí s hodnotami skutečných parametrů. Formální parametry mají tři režimy: •
IN - při volání procedury se do ní předává hodnota skutečného parametru, uvnitř funguje formální parametr jako read-only konstanta PL/SQL, když procedura končí, skutečný parametr se nemění
•
OUT - hodnota skutečného parametru při volání nemá žádný vliv, uvnitř funguje formální parametr jako neinicializovaná proměnná a pro funkci má hodnotu NULL. Je možné z ní číst i zapisovat. Když procedura končí, vkládá se obsah formálního parametru do skutečného parametru.
•
IN OUT - při volání procedury se do ní předává hodnota skutečného parametru, uvnitř procedury funguje odpovídající formální parametr jako inicializovaná proměnná, z níž lze číst a do níž lze zapisovat. Když procedura končí, vkládá se obsah formálního parametru do skutečného parametru.
Při volání procedury se předávají hodnoty skutečných parametrů a v proceduře se na ně odkazuje pomocí formálních parametrů. Omezení parametrů se předávají také. V deklaraci není možné omezovat typ parametrů CHAR a VARCHAR2 na určitou délku ani nelze u typu NUMBER zadávat rozlišení a škálu. Tato omezení se přebírají ze skutečných parametrů. Parametr podprogramu je možné předávat dvěma způsoby - odkazem a hodnotou. Předáváte-li parametr odkazem, dostane se ukazatel na skutečný parametr do odpovídajícího formálního parametru. Pokud se předává hodnotou, zkopíruje se skutečný parametr do formálního. V základním nastavení jsou parametry typu IN předávány odkazem a typu OUT a IN OUT hodnotou. Důvodem je zachování způsobu zpracování výjimek. Pokud použijeme klauzuli NOCOPY při deklaraci parametru v hlavičce procedury, pokusí se překladač předávat parametr odkazem a nikoli hodnotou, jde však o doporučení překladači, nikoli striktní požadavek. Pokud bude akceptován, pak platí, že veškeré změny formálního parametru se odrazí také ve skutečném parametru. Pokud procedura skončí s neošetřenou výjimkou po změně takového formálního parametru, původní hodnota skutečného parametru se ztratí.
David Žák IDAS2/7B – Procedury a funkce.
3
Při volání podprogramů můžeme kromě pozičního zápisu (kdy pořadí proměnných odpovídá pořadí v deklaraci v hlavičce procedury) použít tzv. pojmenovaný pojmenovaný zápis, kdy se při volání podprogramu uvede jak formální tak skutečný parametr. U pojmenovaného zápisu můžeme v případě potřeby změnit pořadí parametrů. Příklad: -- vytvoříme proceduru se 4 parametry CREATE OR REPLACE PROCEDURE CallMe( p_ParameterA VARCHAR2, p_ParameterB NUMBER, p_ParameterC BOOLEAN, p_ParameterD DATE) AS BEGIN NULL; -- nic nebude dělat, jde nám jeden o syntaxi volání END CallMe; -- poziční notace DECLARE v_Variable1 VARCHAR2(10); v_Variable2 NUMBER(7,6); v_Variable3 BOOLEAN; v_Variable4 DATE; BEGIN CallMe(v_Variable1, v_Variable2, v_Variable3, v_Variable4); END; -- příklad použití jmenné notace, pořadí nemusí být dodrženo DECLARE v_Variable1 VARCHAR2(10); v_Variable2 NUMBER(7,6); v_Variable3 BOOLEAN; v_Variable4 DATE; BEGIN CallMe(p_ParameterB => v_Variable2, p_ParameterC => v_Variable3, p_ParameterD => v_Variable4, p_ParameterA => v_Variable1); END; -- příklad použití kombinace poziční a jmenné notace DECLARE v_Variable1 VARCHAR2(10); v_Variable2 NUMBER(7,6); v_Variable3 BOOLEAN; v_Variable4 DATE; BEGIN CallMe(v_Variable1, v_Variable2, p_ParameterC => v_Variable3, p_ParameterD => v_Variable4); END;
David Žák IDAS2/7B – Procedury a funkce.
4
Obdobně jako při deklaraci proměnných proměnných můžeme i při zadávání formálních parametrů procedury nebo funkce použít přednastavenou hodnotu. V takovém případě jej nemusíme z volajícího prostředí předávat. Obecný způsob zápisu syntaxe parametru vypadá takto: jméno_parametru [ IN | OUT | IN OUT] [ NOCOPY ] typ_parametru [ {:= | DEFAULT} počáteční_hodnota ]
Obr. 1. Syntaxe zápisu parametrů procedury či funkce. funkce Zdroj: Oracle.
Volání podprogramů V kódu PL/SQL: BEGIN nazev_procedury(parametry); _procedury(parametry); v_prom := nazev_funkce(parametry); nazev END;
Z konzoly:
CALL nazev_procedury(parametry); _procedury(parametry);
nebo CALL nazev_funkce(parametry) _funkce(parametry) INTO v_prom; PRINT v_prom;
Volání funkce v SQL dotazech: SELECT nazev_funkce(parametry) _funkce(parametry) FROM tabulka … ;
Pokud chceme vyzkoušet funkci a vygenerovat jediný řádek, pak: SELECT nazev_funkce(parametry) _funkce(parametry) FROM DUAL; David Žák IDAS2/7B – Procedury a funkce.
5
Omezení pro volání funkce v SQLL příkazech: příkazech 1) Žádná ádná funkce volaná v příkazu SELECT nesmí měnit měnit žádnou databázovou tabulku. unkce volaná v příkazu DML (INSERT, UPDATE, DELETE) se nesmí dotazovat 2) Funkce nebo měnit kteroukoli tabulku, kterou daný DML příkaz ovlivňuje. Může se odkazovat na jiné tabulky. 3) Funkce, unkce, kterou volá příkaz DML, nesmí vykonávat žádné příkazy řízení transakcí (z toho vyplývá, ani žádné příkazy DDL, neboť tyto implicitně volají volaj příkaz COMMIT). 4) Funkce unkce smí přebírat pouze parametry typu IN, nikoli IN OUT či OUT. OUT 5) Formální ormální parametry i návratový typ funkce musí používat pouze databázové typy, nikoli typy PL/SQL (například BOOLEAN BOOLEAN či RECORD, pokud tyto nejsou nejs vytvořeny uživatelsky příkazem pří CREATE TYPE). 6) Při volání je třeba uvést všechny parametry (přednastavené parametry nelze vynechat) a musí být použit poziční zápis parametrů. 7) Kromě funkcí vracejících jednu (skalární) hodnotu, existují funkce vracející tabulkovou hodnotu. S výsledky těchto funkcí se pracuje stejně jako s tabulkou. Jejich popis však překračuje rámec této lekce.
Příklad I: CREATE OR REPLACE FUNCTION pocet_zamestnancu (p_mzda_min IN NUMBER, p_oddeleni_id IN NUMBER DEFAULT NULL) RETURN NUMBER AS v_pocet NUMBER; BEGIN SELECT count(*) INTO v_pocet FROM A_HR.zamestnanci WHERE oddeleni_id = NVL(p_oddeleni_id,oddeleni_id) AND mzda>p_mzda_min; RETURN v_pocet; END;
A tuto funkci následně použijeme v SQL dotazu pro zjištění počtu zaměstnanců na odděleních s oddeleni_id menším než 100, kteří mají mzdu větší 0 a větší než 7000: 7000 SELECT
oddeleni_nazev, pocet_zamestnancu(0,oddeleni_id), pocet_zamestnancu(7000,oddeleni_id) FROM A_HR.oddeleni WHERE oddeleni_id<100
David Žák IDAS2/7B – Procedury a funkce.
6
Příklad II: CREATE OR REPLACE PROCEDURE zvyseni_mzdy (p_procento procento IN NUMBER) AS BEGIN UPDATE zamestnanci SET mzda = mzda * (1+p_procento/100); procento/100); COMMIT; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR RAISE_APPLICATION_ERROR(-20101, 'Mzda nenavýšeny!'); END;
Pojmy k zapamatování Příkazy a funkce:
PL/SQL, funkce, procedury, podprogramy
Problém: formální a skutečné parametry, parametry volané hodnotou a odkazem,, poziční a pojmenovaný zápis parametrů Shrnutí V této lekci jste se seznámili s vytvářením podprogramů v PL/SQL, zejména procedur a funkcí. Procedury a funkce mají mnoho společných vlastností, např.: • pomocí parametru typu OUT mohou vracet více než jednu hodnotu, • mohou mít přednastavené hodnoty parametrů, které pak není třeba při volání uvádět, • je možné je volat pomocí pozičního nebo pojmenovaného zápisu. Obecné pravidlo pak říká, že pokud chceme vracet více než jednu návratovou hodnotu, použijeme proceduru. Jestliže je návratová hodnota pouze jedna, použijeme funkci.
David Žák IDAS2/7B – Procedury a funkce.
7
Otázky na procvičení 1. 2. 3. 4. 5.
Vysvětlete rozdíl mezi procedurou a funkcí. funkcí Co jsou formální a skutečné parametry? K čemu slouží příkaz RETURN u funkcí a u procedur? procedur Jak se liší parametry typu IN, OUT a IN OUT? OUT Jaký je rozdíl mezi pozičním a pojmenovaným zápisem parametrů? parametrů
Odkazy a další studijní prameny • • • •
http://www.oracle-base.com/articles/misc/AutonomousTransactions.php base.com/articles/misc/AutonomousTransactions.php http://www.techonthenet.com/oracle (syntaxe příkazů SQL jazyka zyka a funkcí) http://www.oracle.com/technetwork/database/enterprise http://www.oracle.com/technetwork/database/enterpriseedition/documentation (dokumentace k databázové platformě Oracle) http://www.penguin.cz/noviny/?id=chip/index (seriál Databáze standardu SQL z časopisu CHIP)
Odkazy a další studijní prameny • •
LACKO, L. Oracle, správa, programování a použití databázového systému. systému Praha: Computer Press, 2007. ISBN ISB 80-251-1490-2. URMAN, S.,., HARDMAN, R., MCLAUGHLIN, M. Oracle - programování v PL/SQL.. Computer Press, 2008. ISBN 978-80-251-1870-2
David Žák IDAS2/7B – Procedury a funkce.
8
8. blok
Balíky Systémové balíky. Balíky.
Studijní cíl Tento blok je věnován vytváření uživatelských balíků funkcí v jazyce PL/SQL a použití systémových balíků.
Doba nutná k nastudování
2 - 3 hodiny
Průvodce studiem Při studiu tohoto bloku se předpokládá, že čtenář je obeznámen s vytvářením a použitím procedur a funkcí v jazyce PL/SQL.
1. Balíky Balíky jsou konstrukcí v PL/SQL,, která umožňuje ukládat spolu související objekty. Balíky mají 2 samostatné části - specifikaci a tělo. Každá z těchto částí se ukládá do datového slovníku samostatně. Balíky také zjednodušují závislosti mezi databázovými objekty. Ve specifikaci jsou deklarovány typy, proměnné, konstanty, výjimky, kurzory a podprogramy pro použití. Specifikace balíku obsahuje informaci o obsahu balíku, ale neobsahuje žádný kód podprogramů. Tělo úplně definuje kurzory a subprogramy – implementační detaily a privátní deklarace, které jsou neviditelné z aplikace. Je možné změnit tělo balíčku balíčku bez změny specifikace a tím vlastně neovlivnit vazbu na další aplikace. Programy volající balík nemusí být rekompilovány při změně těla balíčku (tzv. balíčky přerušují řetězec závislostí). Syntaxe specifikace i těla balíku je ukázána na obr. 1 a 2. Adresace jednotlivých částí balíku: balíku [<schéma>.]
1
Obr. 1. Syntaxe zápisu specifikace balíku Zdroj: Oracle.
Obr. 2. Syntaxe zápisu těla balíku Zdroj: Oracle.
David Žák IDAS2/8 – Balíky. Systémové balíky.
2
Balíky mohou mít kromě veřejných částí také části privátní, ať už jde o procedury, funkce či proměnné. Privátní části balíku jsou přístupné pouze pro podprogramy v balíku a nemohou být volány externími programy, jak ukazuje následující obrázek.
Výhody balíků • •
• • •
• •
Zvětšují obor názvů – může být použit stejný název procedury v různých balících V jednom balíku může být mnoho procedur, ale v datovém slovníku bude existovat pouze jeden objekt – balík, namísto jednoho objektu datového slovníku pro každou proceduru nebo funkci v případě nevyužití balíků Podporují zapouzdření, části kódu (podřízené rutiny), které nemají využití mimo balíček, jsou ukryty v balíku a mimo něj nejsou viditelné Podporují proměnné uchovávané po celou dobu relace - můžete mít proměnné, které si udrží své hodnoty mezi jednotlivými voláními v databázi Podporují spouštěcí kód – tj. úsek kódu, který se provede při prvním odkazu na balík v relaci, tj. umožňuje automatické provedení složitého inicializačního kódu Umožňují seskupení souvisejících funkcí Přerušují řetězec závislostí - v běžném případě při změně definice databázového objektu se zneplatní všechny databázové objekty na něm i nepřímo závislé, k jejich rekompilaci dochází poté při jejich prvním dalším použití. V případě použití balíků je zneplatněno v takovém případě pouze tělo balíku, které je na redefinovaném objektu závislé. Objekty, které jsou na daném balíku závislé, však závisí na specifikaci balíku a nikoli na jeho těle. Proto tyto objekty zůstávají nadále platné a vlastní balíky přeruší řetězec závislostí.
David Žák IDAS2/8 – Balíky. Systémové balíky.
3
Příklad balíku (zdroj Oracle): CREATE OR REPLACE PACKAGE emp_actions AS -- package specification PROCEDURE hire_employee (employee_id (employee NUMBER, last_name VARCHAR2, first_name VARCHAR2, email VARCHAR2, phone_number VARCHAR2, hire_date DATE, job_id VARCHAR2, salary NUMBER, commission_pct NUMBER, manager_id NUMBER, department_id NUMBER); PROCEDURE fire_employee (emp_id NUMBER); FUNCTION num_above_salary _salary (emp_id NUMBER) RETURN NUMBER; END emp_actions;
CREATE OR REPLACE PACKAGE BODY emp_actions AS -- package body -- code for procedure hire_employee PROCEDURE hire_employee (employee_id (employee NUMBER, last_name VARCHAR2, first_name VARCHAR2, email VARCHAR2, phone_number VARCHAR2, hire_date DATE, job_id VARCHAR2, salary NUMBER, commission_pct NUMBER, manager_id NUMBER, department_id NUMBER); IS BEGIN INSERT INTO employees VALUES (employee_id, last_name, first_name, email, phone_number, ber, hire_date, job_id, salary, commission_pct, ommission_pct, manager_id, department_id); END hire_employee; -- code for procedure fire_employee PROCEDURE fire_employee (emp_id NUMBER) IS BEGIN DELETE FROM employees WHERE employee_id employee_i = emp_id; END fire_employee; -- code for function num_above salary FUNCTION num_above_salary (emp_id NUMBER) RETURN NUMBER IS emp_sal NUMBER(8,2); num_count NUMBER; BEGIN SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id; SELECT COUNT(*) INTO num_count FROM employees WHERE salary > emp_sal; RETURN num_count; END num_above_salary; END emp_actions; David Žák IDAS2/8 – Balíky. Systémové balíky.
4
2. Systémové balíky Vestavěné, někdy také nazývané jako systémové balíky, nabízí různé možnosti při řešení problémů. Některé balíky jsou napsány v jazyku C, jsou tedy rychlé a nabízí přímý přístup k jádru. Některé z balíků jsou velice složité. V následujících odrážkách se stručně seznámíme s některými z nich. Níže uvedené balíky vlastní uživatel SYS. Tyto balíky mají veřejné synonymum, a proto není nutné uvádět jméno schématu při jejich použití. Již například ve verzi Oracle 10g Release 2 bylo přibližně 200 systémových balíků. V těchto balících je mnoho podprogramů, které významně rozšiřují možnosti práce s daty, jejich konverze, administraci serveru a podobně. Databázový server již v dnešní době zdaleka není jen prostým bezpečným úložištěm dat, ale umožňuje i plnit řadu dalších funkcí a může na něm být řešena i nemalá část aplikační logiky. Byť právě tato poslední jmenovaná možnost je předmětem mnoha diskusí mezi programátory a databázovými specialisty. V tabulce níže následuje výběr několika desítek vestavěných balíčků, aby si uživatel udělal představu o možnostech, které vestavěné balíčky nabízí.
David Žák IDAS2/8 – Balíky. Systémové balíky.
5
Název balíku
Popis
DBMS_CRYPTO
Umožňuje šifrování a dešifrování uložených dat
DBMS_DDL
Poskytuje přístup k některým SQL DDL z uložených procedur.
Implementuje server-side debuggery a poskytuje způsob, jak ladit server-side PL / SQL programových jednotek. Implementuje vloženou PL / SQL bránu, která umožňuje webovému DBMS_EPG prohlížeči vyvolat PL / SQL uložené procedury pomocí listeneru HTTP. Poskytuje proceduru, která umožňuje vytvářet tabulku pro DBMS_ERRLOG protokolování chyb tak, aby DML operace mohla pokračovat i po vzniku chyby, bez potřeby odrolování transakce. Umožňuje zkopírovat binární soubor v rámci databáze nebo přenést DMBS_FILE_TRANSFER binární soubor mezi databázemi. Umožňuje flashback na verzi databáze při stanoveném čase nebo DMBS_FLASHBACK zadaném system change numberu (SCN). DBMS_DEBUG
DBMS_JAVA
Poskytuje PL / SQL rozhraní pro přístup k databázi z Javy.
DBMS_JOB
Umožňuje naplánovat administrativní postupy, které mají být provedeny v pravidelných intervalech, ale je také rozhraní pro pracovní fronty.
DBMS_LDAP
Poskytuje funkce a postupy pro přístup k datům z LDAP serveru.
DBMS_LDAP_UTL
Poskytuje Oracle Extension funkce utility pro LDAP.
DBMS_LOB DBMS_METADATA
Poskytuje obecné rutiny pro operace s LOB objekty - BLOB, CLOB (čtení / zápis), a BFILEs (pouze pro čtení). Umožňuje snadno získat kompletní definice databázových objektů (metadata) ze slovníku.
DBMS_OLAP
Poskytuje postupy pro souhrny, dimense a přepisy dotazů.
DBMS_OUTPUT
Akumulace informace ve vyrovnávací paměti tak, že to může být načtena později.
DBMS_RANDOM
Poskytuje vestavěný generátor náhodných čísel.
DBMS_RLS
Poskytuje administrativní rozhraní pro řešení bezpečnosti na úrovni řádků tabulky.
DBMS_ROWID
Poskytuje postupy k vytvoření ROWID a interpretaci jejich obsahu.
DBMS_SCHEDULER DBMS_SESSION
Poskytuje množinu plánovacích funkcí, které jsou volatelné z jakéhokoliv PL / SQL programu. Poskytuje přístup k SQL ALTER SESSION a další informace o relaci z uložených procedur.
DBMS_SQL
Umožňuje použít dynamické SQL pro přístup k databázi.
DBMS_SQLTUNE
Poskytuje rozhraní pro ladění SQL příkazů.
DBMS_STAT_FUNCS
Poskytuje statistické funkce.
DBMS_STATS
Poskytuje mechanismus pro uživatele k prohlížení a úpravě statistik optimalizátoru shromážděných pro databázové objekty.
DBMS_TRACE
Poskytuje rutiny pro spuštění a zastavení PL/SQL trasování.
David Žák IDAS2/8 – Balíky. Systémové balíky.
6
DBMS_XMLDOM
Vysvětluje přístup k objektům XMLType
DBMS_XMLGEN
Převede výsledky SQL dotazu do kanonického formátu XML.
DBMS_XMLPARSER
Vysvětluje přístup k obsahu a struktuře XML dokumentů.
DMBS_XMLQUERY
Poskytuje databáze-na-XMLType funkce.
DBMS_XMLSAVE
Poskytuje XML-na-databázi funkce.
DBMS_XMLSCHEMA
Vysvětluje procedury k registraci a odstranění XML schémat.
DBMS_XMLSTORE
Umožňuje ukládání XML dat v relačních tabulkách.
DBMS_XPLAN
Popisuje, jak formátovat výstup příkazu EXPLAIN PLAN.
DBMS_XSLPROCESSOR Vysvětluje přístup k obsahu a struktuře XML dokumentů. HTMLDB_ITEM
HTMLDB_UTIL
Umožňuje uživatelům vytvářet formulářové prvky dynamicky založené na SQL dotazu místo vytváření jednotlivých položek stránku po stránce. Poskytuje nástroje pro získání a nastavení stavu relace, stahování souborů, kontrolu autorizace uživatelů, obnovení různých stavů uživatelů, a také získání a nastavení předvoleb pro uživatele.
HTP
Hypertextové procedury pro generování HTML tagů.
UTL_DBWS
Poskytuje databázové webové služby.
UTL_ENCODE UTL_FILE UTL_HTTP UTL_I18N UTL_INADDR UTL_LMS
Poskytuje funkce, které kódují RAW data do standardního formátu kódování tak, aby data mohla být přepravována mezi počítači. Umožňuje vašim PL/SQL programům čtení a zápis textových souborů operačního systému. Umožňuje HTTP volání z PL/SQL a SQL pro přístup k datům na internetu nebo volání Oracle webového serveru. Poskytuje sadu služeb (Oracle Globalization Service), která pomáhá vývojářům vytvářet vícejazyčné aplikace. Poskytuje procedury na podporu internetové adresace. Načítá a formátuje chybové zprávy v různých jazycích.
UTL_MAIL
Nástroj pro správu e-mailů, který obsahuje běžně používané emailové funkce, jako například přílohy, CC, BCC, a doručenky.
UTL_SMTP
Poskytuje PL/SQL funkce pro odesílání e-mailů.
UTL_TCP
Poskytuje PL / SQL funkce pro podporu jednoduché TCP/IPkomunikace mezi servery a vnějším světem.
UTL_URL
Poskytuje escape a unescape mechanismy pro URL znaky.
XMLType
Popisuje typy a funkce používané pro podporu nativního XML na serveru.
David Žák IDAS2/8 – Balíky. Systémové balíky.
7
Pojmy k zapamatování Příkazy a funkce:
PL/SQL, balíky, řetězec závislostí
Problém: balíky pro uložení souvisejících objektů, závislosti mezi databázovými objekty
Shrnutí V této lekci jste se seznámili s principy balíků a s vybranými systémovými balíky. Balíky zvětšují obor názvů,, zjednodušují závislosti databázových objektů – přerušují řetězec závislostí, podporují odporují zapouzdření, privátní proměnné a podprogramy, podporují odporují proměnné uchovávané po celou dobu relace, relace spouštěcí kód a seskupení souvisejících funkcí.
Otázky na procvičení 1. 2. 3. 4. 5.
K čemu v PL/SQL slouží balíky? Jaké informacee se o balících vyskytují v datovém slovníku? Jaké elementy mohou balíky obsahovat? Jmenujte příklady 10 systémových balíků? balíků Jakým způsobem mění balíky řetězec zavilostí mezi databázovými objekty? objekty
Odkazy a další studijní prameny • • • • • •
http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/packages.htm# i2432 (syntaxe balíků a jejich možnosti v Oracle 10g R2) http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/intro.htm#i101 0103 (seznamy seznamy systémových balíků v Oracle 10g R2) http://www.oracle-base.com/articles/misc/AutonomousTransactions.php base.com/articles/misc/AutonomousTransactions.php http://www.techonthenet.com/oracle (syntaxe příkazů SQL jazyka a funkcí) http://www.oracle.com/technetwork/database/enterprise http://www.oracle.com/technetwork/database/enterpriseedition/documentation (dokumentace k databázové platformě Oracle) http://www.penguin.cz/noviny/?id=chip/index (seriál Databáze standardu SQL z časopisu CHIP)
Odkazy a další studijní prameny • •
LACKO, L. Oracle, správa, programování a použití databázového systému. systému Praha: a: Computer Press, 2007. ISBN 80-251-1490-2. 80 URMAN, S.,., HARDMAN, R., MCLAUGHLIN, M. Oracle - programování v PL/SQL.. Computer Press, 2008. ISBN 978-80-251-1870-2
David Žák IDAS2/8 – Balíky. Systémové balíky.
8
9. blok
Databázové spouště pro DML operace
Studijní cíl Tento blok je věnován vytváření a využití databázových spouští na DML operacích INSERT, UPDATE a DELETE nad tabulkami či pohledy. pohledy
Doba nutná k nastudování
2 - 3 hodiny
Průvodce studiem Při studiu tohoto bloku se předpokládá, že čtenář je obeznámen s jazykem SQL a zná základy jazyka PL/SQL. 1. Databázové spouště Databázové spouště (triggers)) jsou dalším typem pojmenovaných bloků PL/SQL. Tyto databázové objekty mají mnoho společného s podprogramy, ale liší se způsobem vytváření a volání. Databázové spouště jsou pojmenované bloky PL/SQL s deklarační oblastí, exekuční ní oblastí a oblastí výjimek. Spouště se spouští implicitně, kdykoli nastane událost, která je spustí. Tyto události buď mohou být operace typu DML, tedy INSERT, UPDATE či DELETE na databázové tabulce nebo na určitém typu pohledů, nebo může jít o systémové systémov spouště na databázových událostech, událostech jako je start instance, přihlášení uživatele nebo například DDL operace. V této kapitole se budeme věnovat pouze první skupině - tedy spouštěmi aktivovanými DML operacemi.
Obr.1 - Druhy DML spouští nad tabulkou. Zdroj:Oracle. David Žák IDAS2/9 – Databázové atabázové spouště pro DML operace.
1
Spouště mohou plnit například tyto úkoly: • • • •
kontrola zadaných dat, kterou nelze provést pouze pomocí omezení nad tabulkami, například CHECK, zajištění složitějších pravidel referenční integrity, která nelze dodržet pomocí deklarativních omezení, vytváření informací o změnách prováděných v tabulkách (log) s uvedením autora, případně i auditování těchto operací, automatická signalizace či aktivace jiných programů při některých změnách v tabulkách.
2. Spouště DML Spoušť DML se vyvolává příkazem DML a tyto spouště lze definovat pro operace INSERT, UPDATE a DELETE. Je možné při definici spouště také nastavit, nastavit zda spoušť se vyvolá před (BEFORE) nebo po (AFTER) provedení daného DML příkazu. Další možností při definici těchto spouští je určení, zda se budou budou aktivovat jen jednou pro prováděný příkaz, nebo se aktivují pro každý DML příkazem ovlivněný řádek samostatně. Obecná syntaxe na DML operacích je následující: CREATE [OR REPLACE] TRIGGER [<schema>].<jmeno_spouste> {BEFORE | AFTER} [INSTEAD OF] {INSERT | UPDATE | DELETE } ON
Při definici spouště musíme usíme určit: •
akci, která spoušť aktivuje, více spouštěcích příkazů lze oddělit klauzulí OR (např. INSERT OR DELETE), DELETE) pro akci update může být upřesněn sloupec, při jehož modifikaci má být spoušť aktivována, například BEFORE DELETE OR INSERT OR UPDATE OF ename ON Emp_tab
• • •
•
kdy se spoušť aktivuje - před či po provedení DML příkazu. případně, zda spoušť nahrazuje operaci, která ji spustila INSTEAD OF (lze použít pouze pro pohledy) zda se tělo spouště vykoná pro každý příkazem ovlivněný řádek tabulky či pohledu FOR EACH ROW nebo jen jednou pro celý příkaz (hovoříme o spoušti na úrovni řádku nebo příkazu), příkazu) podmínka WHEN může být uvedena pouze pro řádkové spouště a vyhodnocuje jako první, první tělo spouště se spustí pouze v případě, kdy nabyde hodnoty TRUE.
David Žák IDAS2/9 – Databázové atabázové spouště pro DML operace.
2
Tělo spouště musí obsahovat alespoň exekuční oblast, volitelně může obdobně jako jiné bloky PL/SQL obsahovat deklarační oblast a oblast výjimek. Tabulka může mít na sobě libovolný počet spouští a to dokonce více než jednu pro konkrétní DML příkaz. Všechny spouště téhož typu se budou volat postupně. Pořadí pro volání spouští (u všeho platí, pokud existují) 1. provedou se spouště typu BEFORE na úrovni příkazu, 2. pro každý řádek, který příkaz ovlivňuje: a. provedou se spouště typu BEFORE na úrovni řádku, b. provede se samotný příkaz, c. provedou se spouště typu AFTER na úrovni řádku, 3. provedou se spouště typu AFTER na úrovni příkazu. Jakmile dojde k vyvolání spouště, uvidí tato spoušť změny, které udělaly spouště před ní a to i změny, které do té chvíle provedl vyvolávající příkaz. Pořadí, v němž se vyvolají spouště téhož typu, není definováno. Pokud je pořadí důležité, je třeba dát všechny operace do jedné spouště. Úplně triviální příklad vyjadřující rozdíl mezi spouští, která se aktivuje pro každý řádek tabulky ovlivněný daným DML příkazem nebo pouze jednou pro celý DML příkaz, může vypadat například takto:
CREATE [OR REPLACE] TRIGGER potvrzeni_all potvrzeni_ AFTER UPDATE ON ucitele FOR EACH ROW BEGIN DBMS_OUTPUT.PUT_LINE(`Záznam byl úspěšně změněn`); END; CREATE [OR REPLACE] TRIGGER potvrzeni_1 AFTER UPDATE ON ucitele BEGIN DBMS_OUTPUT.PUT_LINE(`Záznam(y) byl(y) úspěšně změněn(y)`); END;
Výše uvedené příklady vypíší řádek s informací na standardní výstupní zařízení.
David Žák IDAS2/9 – Databázové atabázové spouště pro DML operace.
3
Korelační identifikátory Uvnitř spouští na úrovni řádku můžeme přistupovat k datům v řádku, který se aktuálně zpracovává. K tomu slouží korelační identifikátory :old a :new . Korelační identifikátor entifikátor je speciální druh vazebné proměnné v PL/SQL. • •
:OLD označuje původní hodnoty v adresovaném poli - sloupci (použití pro příkazy DELETE, UPDATE) :NEW označuje nové hodnoty pro adresované pole - sloupec (použití pro příkazy INSERT, UPDATE)
Korelačníí identifikátory :old a :new mohou být použity pouze v těle řádkových spouští (vytvořených s klauzulí FOR EACH ROW). Pokusíme-li Pokusíme li se je použít uvnitř příkazových spouští, dojde ke kompilační chybě. Další příklad využívá spouště k uložení dat ze záznamů odstraňovaných příkazem DELETE do tabulky byvali_pracovnici a ukazuje použití obou korelačních identifikátorů. CREATE [OR REPLACE] TRIGGER zaloha_pracovniku BEFORE DELETE ON pracovnici FOR EACH ROW BEGIN INSERT INTO byvali_pracovnici VALUES (:OLD.id_prac, :OLD.jmeno, …); END;
Příkaz DELETE může obecně odstranit hned celou množinu řádků z tabulky. Proto výše uvedená spoušť je aktivována na úrovni jednotlivých ovlivněných řádků tabulky a tyto vždy uloží do tabulky byvali_pracovnici. Dalším typickým použitím spouští v prostředí Oracle databáze je automatické přiřazování hodnot primárních klíčů s využitím sekvencí, z prostředí MySQL známé jako AUTOINCREMENT. Příklad ukazuje automatické přiřazení hodnoty sloupce evaluation_id na základě sekvence evaluations_seq při vkládání řádků do tabulky evaluations s využitím spouště. Pokud bychom hodnotu evaluation_id zadali v operaci INSERT, stejně bude ignorována. CREATE OR REPLACE TRIGGER new_evaluation_trigger BEFORE INSERT ON evaluations FOR EACH ROW BEGIN :NEW.evaluation_id := evaluations_seq.NEXTVAL; END;
David Žák IDAS2/9 – Databázové atabázové spouště pro DML operace.
4
Klauzule WHEN Za klauzuli WHEN se uvádí podmínka spuštění spouště. Klauzule WHEN může být použita pouze u řádkových spouští. V této klauzuli je možné se odkazovat i na identifikátory :old a :new, ale nepíše se zde před nimi dvojtečka, dvojtečka se píše jen v těle spouště. Logické funkce INSERTING, UPDATING, DELETING Jedna spoušť může být aktivována více druhy DML příkazů a proto je někdy potřebné v těle otestovat, který příkaz vedl ke spuštění spouště. Tyto logické funkce nabývají hodnoty TRUE v případě, kdy daná operace byla důvodem aktivace spouště, jinak nabývají hodnoty FALSE. INSTEAD OF spouště Běžné DML spouště se provádí jako dodatek k operacím DML, které je aktivovaly a to buď před či po vlastní DML operaci. Nahrazující (INTEAD OF) spouště však danou operaci nahrazují. Nahrazující spouště mohou být definovány pouze na pohledech, kdežto DML spouště na tabulkách. Nahrazující spouště se používají k • •
změně pohledů, které by jinak měnit nešlo, změně ve vnořených tabulkách, z kterých pohled zpracovává data.
Modifikovatelné pohledy jsou takové, na nichž je možné provést DML operaci. Jde o jednoduché pohledy, které neobsahují operace: • • • • •
operátory pro množinové operace UNION, UNION ALL, MINUS, agregační funkce (například SUM, AVG, COUNT, MIN, MAX), klauzule GROUP BY, CONNECT BY nebo START WITH, operátor DISTINCT, spojení tabulek (některé však modifikovatelné jsou, pokud DML příkaz v jednom okamžiku modifikuje jen základní tabulku).
Pokud pohled není modifikovatelný, je možné na něj napsat INSTEAD OF spoušť, která provede potřebné operace. Nahrazující spouště však mohou být napsány i pro modifikovatelné pohledy. INSTEAD OF spouště jsou jen pro pohledy a mají implicitně nastaveno FOR EACH ROW, není tedy nutné tuto klauzuli znovu uvádět.
David Žák IDAS2/9 – Databázové spouště pro DML operace.
5
INSTEAD OF spouště umožní provést změny do tabulek, na které se pohledy odkazují. Následující příklad ukazuje definici pohledu a příklad p INSTEAD OF spouště update_name_view_trigger pro změnu jména zaměstnance. CREATE VIEW emp_locations AS SELECT e.employee_id, e.last_name || ', ' || e.first_name name, d.department_name department, l.city city, c.country_name country FROM employees e, departments d, locations l, countries c WHERE e.department_id = d.department_id AND d.location_id = l.location_id AND l.country_id = c.country_id ORDER BY last_name; CREATE OR REPLACE TRIGGER update_name_view_trigger INSTEAD OF UPDATE ON emp_locations BEGIN -- allow only the following update(s) UPDATE employees SET first_name = substr( :NEW.name, instr( :new.name, ',' )+2), last_name = substr( :NEW.name, 1, instr( :new.name, ',')-1) ',') WHERE employee_id = :OLD.employee_id; END;
Příklad volání procedury v těle spouště CREATE TRIGGER hr.salary_check BEFORE INSERT OR UPDATE OF salary, job_id ON hr.employees FOR EACH ROW WHEN (new.job_id <> 'AD_VP') CALL check_sal(:new.job_id, :new.salary, :new.last_name);
Doporučení pro použití BEFORE a AFTER spouští pro DML operace • • • •
•
použijte BEFORE řádkové spouště pro modifikování difikování řádku před zápisem dat na disk použijte AFTER řádkové spouště k získání a zajištění operací používajících ROWID BEFORE spouště jsou více efektivní, protože nevyžadují dvojí čtení dat BEFORE spouště umožňují změnit hodnotu :new před provedením vlastní astní akce, akce např. :new.id := 1000 + :new.id Spouště mohou odvozovat hodnoty sloupců automaticky na základě hodnot zadaných v příkazech INSERT nebo UPDATE. Tento typ spouští je vhodný pro případ, kdy odvozené hodnoty jsou vkládány do stejných řádků jako hodnoty uvedené v příkazech INSERT nebo UPDATE. Pro toto řešení je nezbytné použít řádkové spouště typu BEFORE.
David Žák IDAS2/9 – Databázové atabázové spouště pro DML operace.
6
Příklad: CREATE OR REPLACE TRIGGER Derived BEFORE INSERT OR UPDATE OF Ename ON Emp99 /* Před změnou sloupce ENAME urči hodnotu UPPERNAME Omezte uživatele ke změnám tohoto sloupce přímo: */ FOR EACH ROW BEGIN :new.Uppername := UPPER(:new.Ename); END;
Spouště – poznámky k použití • • •
• • •
Přílišné využití spouští může způsobit nepřehlednost aplikace (jejich přehlížení) Nepoužívejte rekursivní spouště Pozor při použití kaskádních spouští (spoušť mění data v jiné tabulce, než na které je sama vytvořena, ale na další tabulce je opět definována jiná spoušť, atd.) Nepoužívejte spouště tam, kde si můžete můžete pomoci například omezeními CHECK na vkládané hodnoty, hodnoty referenční integrita, atd. . Nedělejte kód spouští delší než 32KB. Pokud by měl být delší, použijte procedur a funkcí. Není možné použít příkazy COMMIT, ROLLBACK nebo SAVEPOINT v těle spouště. Protože DDL příkazy způsobují implicitní COMMIT, nemohou být proto použity ve spouštích, spouštích s výjimkou příkazů CREATE, ALTER, DROP TABLE a ALTER...COMPILE pro systémové spouště.
Pro odstranění spouště použijte příkaz DROP DROP TRIGGER <jmeno_spouste>; <jmeno_spouste>
V určitých případech může být vhodné/požadované dočasně deaktivovat funkci spouště,, například při vkládání řádků odkazujících na neexistující objekty, objekty importu větších objemů dat (obnova dat ze zálohy atd.) bez prodlevy. Pro konkrétní spoušť: ALTER TRIGGER <jmeno_spouste> DISABLE; ALTER TRIGGER <jmeno_spouste> ENABLE;
Pro všechny spouště svázané s konkrétní tabulkou: ALTER TABLE
7
Příkaz pro zjištění spouští definovaných v uživatelově schématu: SELECT Trigger_type, Triggering_event, Table_name FROM USER_TRIGGERS WHERE Trigger_name = 'REORDER';
Příkazy pro zjištění spouští a jejich kódu v uživatelově schématu: SELECT Trigger_body FROM USER_TRIGGERS WHERE Trigger_name = 'REORDER';
Obr. 1 - Příklad kaskádování spouští.
David Žák IDAS2/9 – Databázové atabázové spouště pro DML operace.
8
Následující příklad říklad demonstruje vyvolání chyby, chyb , která zamezí vložení trpaslíka do tabulky trpaslíků, pokud jich v této tabulce již bude 7: CREATE OR REPLACE TRIGGER omezeni_trpasliku BEFORE INSERT on TRPASLICI FOR EACH ROW DECLARE v_pocet_trpasliku NUMBER; BEGIN SELECT COUNT(*) INTO v_pocet_trpasliku FROM trpaslici; IF v_pocet_trpasliku >= 7 THEN RAISE_APPLICATION_ERROR( RAISE_APPLICATION_ERROR(-20005, 'Trpaslíků může být maximálně sedm'); END IF; END omezeni_trpasliku;
Jak je vidět, tak transakce spojená s příkazem INSERT INTO trpaslici skončí odrolováním a chybovou hláškou v případě, kdy by během vkládání byl překročen nastavený limit pro počet trpaslíků.
Další příklad ukazuje spoušť aktivovanou více druhy DML příkazů a obsahující podmínku WHEN: CREATE OR REPLACE TRIGGER Vypis_zmeny_mzdy BEFORE DELETE OR INSERT OR UPDATE ON Emp_tab FOR EACH ROW WHEN (new.Empno > 0) DECLARE sal_diff number; BEGIN IF INSERTING THEN :new.emp_id= emp_id_seq.NEXTVAL; dbms_output.put('Nova mzda: mzda ' || :new.sal); ELSIF DELETING THEN dbms_output.put('Puvodni mzda: mzda ' || :old.sal); ELSE sal_diff := :new.sal - :old.sal; dbms_output.put_line('Rozdil mzdy ' || sal_diff); END IF: END;
Spoušť bude aktivována pro příkazy: DELETE FROM INSERT INTO INSERT INTO UPDATE
Emp_tab; Emp_tab Emp_tab Emp_tab
VALUES ( ... ); SELECT ... FROM ... ; SET ... ;
David Žák IDAS2/9 – Databázové atabázové spouště pro DML operace.
9
Následující tabulky ukazují možnosti zajištění referenční integrity mezi rodičovskou tabulkou a tabulkou potomků pro různé požadavky: FOREIGN KEY – DML operace zajištěné omezeními DML příkaz
V tabulce rodičů
V tabulce potomků
INSERT
vždy OK, pokud je hodnota klíče unikátní
OK pouze v případě, pokud hodnota cizího klíče existuje v tabulce rodičů nebo je NULL
UPDATE No Action (default, občas se označuje jako restrict)
Dovoleno v případě, kdy žádná hodnota cizího klíče v tabulce potomků nezůstane bez vazby na tabulku rodičů
Dovoleno, pokud nová hodnota cizího klíče existuje v tabulce rodičů (nebo je NULL)
DELETE No Action (default, občas se označuje jako restrict)
Dovoleno v případě, kdy žádná hodnota v tabulce potomků se neodkazuje na vymazávaný řádek
vždy OK
DELETE Cascade
vždy OK, ovlivněné automaticky odstraní
vždy OK
DELETE Set Null
vždy OK, hodnota cizího klíče v ovlivněných řádcích se změní na NULL
řádky
se
vždy OK
FOREIGN KEY – DML operace s nutností zajištění spouštěmi DML příkaz
V tabulce rodičů
V tabulce potomků
UPDATE CASCADE
vždy OK
Dovoleno, pokud nová hodnota cizího klíče existuje v tabulce rodičů (nebo je NULL)
UPDATE Set Null
Vždy OK, hodnota cizího klíče v ovlivněných řádcích se změní na NULL, pozor na rozpad referenční integrity
Dovoleno, pokud nová hodnota cizího klíče existuje v tabulce rodičů (nebo je NULL)
UPDATE Default
Vždy OK, hodnota cizího klíče v ovlivněných řádcích se změní na default hodnotu, pozor na rozpad referenční integrity
Dovoleno, pokud nová hodnota cizího klíče existuje v tabulce rodičů (nebo je NULL)
DELETE Set Default
vždy OK, hodnota cizího klíče v ovlivněných řádcích se změní na default hodnotu
vždy OK
David Žák IDAS2/9 – Databázové spouště pro DML operace.
10
Příklad použití spouště pro provedené kaskádní změny klíče i do tabulky potomků (PRODUKTY) v případě změny dodavatel_id v tabulce DODAVATELE: CREATE OR REPLACE TRIGGER DODAVATELE_UPDATE_CASCADE AFTER UPDATE OF DODAVATEL_ID ON DODAVATELE FOR EACH ROW BEGIN -- Změní hodnoty cizího klíče pokud hodnota v rodičovské tabulce byla změněna if (:old.dodavatel_id != :new.dodavatel_id) then begin update Produkty set dodavatel_id = :new.dodavatel_id where dodavatel_id = :old.dodavatel_id; end; end if; end;
Práce s omezeními - constraints • •
• •
Při importu dat (většinou z jedné databáze do jiné) mohou nastat problémy s integritními omezeními. Příkladem je nevhodné pořadí vytvářených tabulek a dat do nich vkládaných (tj. například není vložen řádek do nadřízené tabulky, na níž se daný řádek v podřízené tabulce odkazuje). Řešením je vypnutí omezení během importu a poté je opět obnovit. Je třeba mít na paměti, že pokud data nebudou splňovat požadavky omezení, nebude možné již tato omezení obnovit. V takovém případě je nezbytné tné nejdříve data upravit a teprve poté aktivovat omezení, například:
ALTER TABLE
Jakékoli omezení zvyšuje náklady áklady na práci s řádky tabulky. Zjednodušeně lze říci, říci že nároky rostou v pořadí • • • • • •
NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK TRIGGERY
David Žák IDAS2/9 – Databázové atabázové spouště pro DML operace.
11
Pojmy k zapamatování Příkazy a funkce:
databázové spouště pro DML operace
Problém:
události aktivující spouště, zajištění referenční integrity
Shrnutí Spouště jsou cenným rozšířením PL/SQL. Spouště není vhodné použít k zajištění elementárních potřeb, jako je nastavení referenční integrity a podobně. K tomu jsou určena omezení. Použití spouští je vhodné pro zajištění těchto potřeb: • kontrola zadaných dat, dat • zajištění komplexní referenční integrity v databázi, • automatické generování odvozené hodnoty hodnot sloupců, • zamezení invalidním transakcím, • zajištění komplexní bezpečnostní autorizace, autorizace • implementace business busin pravidel, • zajištění logování událostí, událostí • poskytování auditů, • zajištění synchronní replikace tabulek, tabulek • generování statistik přístupu k tabulkám, • modifikace dat v tabulce, když DML příkaz používá pohled, pohled • publikace informací o událostech a příkazech do jiných jinýc aplikací.
Otázky na procvičení 1. 2. 3. 4. 5.
Charakterizujte databázové spouště na tabulkách. Jakýý je rozdíl mezi řádkovou a příkazovou spouští? spouští Jaké pohledy se považují za modifikovatelné? modifikovatelné K čemu slouží INSTEAD OF spouště? spouště K čemu slouží omezení - CONSTRAINTs? CONSTRAINTs
Odkazy a další studijní prameny • • • •
http://www.oracle-base.com/articles/misc/AutonomousTransactions.php base.com/articles/misc/AutonomousTransactions.php http://www.techonthenet.com/oracle (syntaxe příkazů SQL jazyka a funkcí) http://www.oracle.com/technetwork/database/enterprise http://www.oracle.com/technetwork/database/enterpriseedition/documentation (dokumentace k databázové platformě Oracle) http://www.penguin.cz/noviny/?id=chip/index (seriál Databáze standardu SQL z časopisu CHIP)
David Žák IDAS2/9 – Databázové atabázové spouště pro DML operace.
12
Odkazy a další studijní prameny • •
LACKO, L. Oracle, správa, programování a použití databázového systému. systému Praha: Computer Press, 2007. ISBN 80-251-1490-2. 80 URMAN, S.,., HARDMAN, R., MCLAUGHLIN, M. Oracle - programování v PL/SQL. Computer Press, 2008. ISBN 978-80-251-1870-2
David Žák IDAS2/9 – Databázové atabázové spouště pro DML operace.
13
10. blok
Systémové spouště pro databázové a klientské události
Studijní cíl Tento blok je věnován vytváření a využití databázových spouští nad systémovými a klientskými objekty v databázi.
2 hodiny
Doba nutná k nastudování
Průvodce studiem Při studiu tohoto bloku se předpokládá, že čtenář je obeznámen s jazykem SQL a zná základy jazyka PL/SQL. Dále je seznámen s databázovými spouštěmi nad klientskými událostmi. Tato lekce velmi těsně navazuje na předchozí lekci zabývající se spouštěmi na DML událostech. 1. Systémové spouště Systémové spouště mohou být aktivovány DDL příkazy nebo databázovými událostmi. álostmi. Do skupiny DDL patří příkazy CREATE, ALTER a DROP a typickými databázovými událostmi jsou například start či vypnutí serveru, přihlášení či odhlášení uživatele, serverové chyby atd. Obecná syntaxe systémových spouští je analogická syntaxi spouští na DML operacích: CREATE [OR REPLACE] TRIGGER [<schema>].<jmeno_spouste> {BEFORE | AFTER} {<seznam_událostí_DDL seznam_událostí_DDL> | <seznam_událostí_databáze> } ON {DATABASE | [<schema>].SCHEMA} [<schema>]. [WHEN <podmínka>]
Kde: <seznam_událostí_DDL> je jedna či více událostí DDL oddělených klíčovým
slovem
OR,,
tato
skupina
je
také
nazývána
klientskými
událostmi událostmi,
<seznam_událostí_databáze seznam_událostí_databáze> je jedna či více událostí v databázi oddělených
klíčovým slovem OR David Žák IDAS2/10 – Systémové spouště pro databázové a klientské události.
1
Z databázových událostí jmenujme například • • •
AFTER STARTUP - k události dojde při startu instance BEFORE SHUTDOWN - k události dojde při ukončení instance (pokud není instance ukončena abnormálně) AFTER SERVERERROR - k události dojde při chybě
Mezi klientské události patří (opět uvedeno včetně povoleného načasování a popisu, kdy k události dojde): • • • • • • • • • • • • • • • • •
BEFORE / AFTER ALTER - před nebo po úpravě objektu ve schématu BEFORE / AFTER DROP - před nebo po odstranění objektu ze schématu BEFORE / AFTER ANALYZE - před nebo po vydání příkazu ANALYZE STATEMENT BEFORE / AFTER ASSOCIATE STATISTICS - před nebo po vydání příkazu ASSOCIATE STATISTICS BEFORE / AFTER AUDIT - před nebo po vydání příkazu AUDIT BEFORE / AFTER NOAUDIT - před nebo po vydání příkazu NOAUDIT BEFORE / AFTER COMMENT - před nebo po vydání příkazu COMMENT BEFORE / AFTER CREATE - před nebo po vytvoření objektu ve schématu BEFORE / AFTER DDL - před nebo po vydání většiny příkazů DDL BEFORE / AFTER DISASSOCIATE STATISTICS - před nebo po vydání příkazu DISASSOCIATE STATISTICS BEFORE / AFTER GRANT - před nebo po vydání příkazu GRANT AFTER LOGON - po úspěšném připojení uživatele k databázi BEFORE LOGOFF - na začátku odhlášení uživatele BEFORE / AFTER RENAME - před nebo po vydání příkazu RENAME BEFORE / AFTER REVOKE - před nebo po vydání příkazu REVOKE AFTER SUSPEND - po odložení příkazu SQL kvůli nedostatku místa Spoušť může situaci napravit a poté příkaz spustit znovu. BEFORE / AFTER TRUNCATE - před nebo po vydání příkazu TRUNCATE
Systémovou spoušť lze definovat na úrovni konkrétního schématu nebo celé databáze. Úroveň určují klíčová slova SCHEMA respektive DATABASE. Pokud klíčové slovo při vytváření systémové spouště nezadáte, vytvoří se automaticky na schéma, které spoušť vlastní.
David Žák IDAS2/10 – Systémové spouště pro databázové a klientské události.
2
Jako příklad si uveďme vytváření ytváření LOGON a LOGOFF spouští,, které monitorují přihlašování šování a odhlašování uživatelů databáze do logovací tabulky. Vytvořme tedy tabulku hr_users_log pro ukládání informací o událostech LOGON a LOGOFF. Následně vytvoříme spouště note_hr_logon_trigger a note_hr_logoff_trigger pro zápis informací o těchto událostech udál do tabulky. CREATE TABLE hr_users_log (user_name VARCHAR2(30), activity VARCHAR2(20), event_date DATE); CREATE OR REPLACE TRIGGER note_hr_logon_trigger AFTER LOGON ON HR.SCHEMA BEGIN INSERT INTO hr_users_log VALUES (USER, 'LOGON', SYSDATE); END; CREATE OR REPLACE TRIGGER note_hr_logoff_trigger BEFORE LOGOFF ON HR.SCHEMA BEGIN INSERT INTO hr_users_log VALUES (USER, 'LOGOFF', SYSDATE); END;
Příklady funkcí atributů událostí (výběr): (výběr) Atribut
Návratový typ
Popis
ora_client_ip_address
VARCHAR2
IP adresa klienta při jeho přihlášení do databáze.
ora_database_name
VARCHAR2(50)
Název databáze
ora_dict_obj_name
VARCHAR(30)
Název objektu ve slovníku, na kterém došlo k operaci DDL.
ora_dict_obj_name_list (name_list OUT ora_name_list_t)
BINARY_INTEGER
Seznam jmen objektů modifikovaných danou událostí.
ora_dict_obj_owner
VARCHAR(30)
Vlastník objektu ve slovníku, na kterém došlo k operaci DDL.
ora_dict_obj_owner_list(owne BINARY_INTEGER r_list OUT ora_name_list_t)
Seznam vlastníků objektů modifikovaných danou událostí.
ora_dict_obj_type
Typ objektu ve slovníku, na kterém došlo k operaci DDL.
VARCHAR(20)
David Žák IDAS2/10 – Systémové spouště pro databázové a klientské události.
3
ora_grantee( user_list OUT ora_name_list_t)
BINARY_INTEGER
Seznam uživatelů, kteří obdrží příkazem GRANT oprávnění.
ora_instance_num
NUMBER
Číslo instance
ora_login_user
VARCHAR2(30)
Jméno přihlášeného uživatele.
ora_privilege_list( privilege_list OUT ora_name_list_t)
BINARY_INTEGER
Seznam oprávnění, která jsou přidělována nebo odebírána. Návratová hodnota je velikost pole.
ora_revokee ( user_list OUT ora_name_list_t)
BINARY_INTEGER
Seznam uživatelů, kterým budou příkazem GRANT odebrána oprávnění.
ora_sql_txt (sql_text out ora_name_list_t)
BINARY_INTEGER
Test příslušného příkazu, je-li příkaz příliš dlouhý, bude rozdělen na více částí a návratová hodnota bude obsahovat velikost pole.
ora_sysevent
VARCHAR2(20)
ora_with_grant_option
BOOLEAN
Jméno systémové události, která spoušť vyvolává. Vrací TRUE, jsou-li oprávnění přidělována s možností GRANT OPTION.
Systémová spoušť se spustí buď jako samostatná transakce, která se potvrdí (COMMIT) po úspěšném dokončení spouště, nebo jako součást aktuální transakce uživatele. Spouště STARTUP, SHUTDOWN, SERVERERROR a LOGON poběží jako samostatné transakce, spouště LOGOFF a DDL budou součástí aktuální transakce. Úkony spouště se obecně vždy provedou. Obdobně jako spouště DML mohou i systémové spouště obsahovat klauzuli WHEN, v níž se omezí podmínky pro jejich spuštění, například: • • • •
Pro spouště LOGON a LOGOFF se může testovat hodnota USERID a/nebo USERNAME uživatele Pro SERVERERROR spouště se může testovat hodnota ERRNO pro testování konkrétní chyby Pro DML spouště se může testovat typ a jméno DDL příkazem měněného objektu a USERID nebo USERNAME uživatele Spouště STARTUP a STARTDOWN nemohou obsahovat žádné podmínky
David Žák IDAS2/10 – Systémové spouště pro databázové a klientské události.
4
2. Omezení spouští V těle spouště je povoleno používat libovolný příkaz, který je platný v bloku PL/SQL, pokud je splněné následující: •
• • •
•
•
Spoušť nesmí obsahovat žádný příkaz pro řízení transakce, spoušť se volá jako součást volání lání provádějícího příkazu, je tedy součástí téže transakce jako volající příkaze.. Potvrzením nebo odvoláním transakce se také potvrdí nebo odvolá práce, kterou spoušť provádí. Můžete však vytvořit spoušť, která se chová jako autonomní transakce. Ani žádné procedury nebo funkce volané ze spouště nesmí obsahovat příkazy pří řízení transakcí, pokud nejsou deklarovány jako autonomní. V těle spouště se nesmí měnit hodnoty sloupců typu LOB a objektové sloupce, i když se na ně lze odkazovat. Před verzí Oracle 8i muselo být tělo spouště blok PL/SQL, ve vyšších verzích však může tělo spouště tvořit příkaz CALL, který volá proceduru buď v PL/SQL, nebo může jít o obálku pro rutinu v jazyce Java nebo C. Příkazy ve spoušti nesmí načítat data ani je měnit ze žádné tabulky, tabulky, nad níž je definována, a tabulky, které je potřeba aktualizovat jako důsledek příkazu DELET CASCADE a omezení referenční integrity (platí pro spouště na úrovni řádku) Příkazy ve spoušti nesmí načítat či měnit sloupce s primárním, unikátním či cizím klíčem v omezující tabulce, za omezující se považuje taková tabulka, na které je měněná tabulka referenčně závislá (platí pro spouště na úrovni řádku)
Pojmy k zapamatování Příkazy a funkce:
systémové spouště pro DDL operace, databázové události
Problém:
události aktivující spouště, omezení při psaní kódu spouští
Shrnutí Spouště jsou cenným rozšířením PL/SQL. Mohou sloužit k hlídání vazeb, logování či auditování vybraných událostí. Správné použití spouští však vyžaduje určitou koncepci pro jejich nasazení,í, protože jejich nevhodná aplikace může vyvolat řadu rizik. Spouště není vhodné použít k zajištění elementárních potřeb, jako je nastavení referenční integrity a podobně. K tomu jsou určena omezení. Tak jako obecně, ale u spouští zvláště platí, že je nutnéé nejdříve promyslet jiná jednodušší řešení a teprve po důkladném rozmyšlení přistoupit k použití spouští. David Žák IDAS2/10 – Systémové spouště pro databázové a klientské události.
5
Otázky na procvičení 1. Charakterizujte systémové spouště. spouště 2. Jaké typy událostí mohou aktivovat systémové spouště? 3. K čemu slouží klauzule WHEN u systémových spouští? 4. Jakým způsobem pracují spouště s příkazy pro řízení transakcí? 5. Jakáá omezení platí pro úpravy dat v tabulkách u spouští prováděných na úrovni řádků? Odkazy a další studijní prameny • • • •
http://www.oracle-base.com/articles/misc/AutonomousTransactions.php base.com/articles/misc/AutonomousTransactions.php http://www.techonthenet.com/oracle (syntaxe příkazů SQL jazyka zyka a funkcí) http://www.oracle.com/technetwork/database/enterprise http://www.oracle.com/technetwork/database/enterpriseedition/documentation (dokumentace k databázové platformě Oracle) http://www.penguin.cz/noviny/?id=chip/index (seriál Databáze standardu SQL z časopisu CHIP)
Odkazy a další studijní prameny • •
LACKO, L. Oracle, správa, programování a použití databázového systému. systému Praha: Computer Press, 2007. ISBN ISB 80-251-1490-2. URMAN, S.,., HARDMAN, R., MCLAUGHLIN, M. Oracle - programování v PL/SQL.. Computer Press, 2008. ISBN 978-80-251-1870-2
David Žák IDAS2/10 – Systémové spouště pro databázové a klientské události.
6
11. blok
Pokročilé konstrukce SQL dotazů - část I
Studijní cíl Tento blok je věnován pokročilým konstrukcím SQL dotazů, které umožní psát efektivní kód, konkrétně seznámí s funkcemi CASE, DECODE, pseudo-sloupcem sloupcem ROWNUM a konstruktory řádkové a tabulkové hodnoty.
Doba nutná k nastudování
2 - 3 hodiny
Průvodce studiem Při studiu tohoto bloku se předpokládá, že čtenář je obeznámen s DML příkazy jazyka SQL.
1. Výrazy s operátorem CAST Tento výraz zajišťuje konverzi hodnoty do určeného datového typu. Obecná syntaxe použití tohoto operátoru je CAST(
Kromě datového typu je možné použít ve výrazu CAST i doménu, což je specifická množina platných datových ových hodnot. Použití výrazů s operátorem CAST: •
Zejména při volání příkazů jazyka SQL z jiného programovacího jazyka, jehož datové typy neodpovídají datovým typům podporovaným standardem SQL (například pro převod data a času na řetězec)
•
Konverze dat z databázové tabulky, kde je sloupec definován pomocí špatného datového typu (například čísla v uložená jako řetězce znaků)
•
Odstranění rozdílů mezi různými typy pro stejná data ve 2 různých tabulkách
David Žák IDAS2/11 – Pokročilé konstrukce SQL dotazů - část I
1
Příklad použití operátoru ČÁST v dotazu: dotazu SELECT ID, JMENO, CAST(VYSKA AS VARCHAR(4)) FROM TRPASLICI;
2. Výrazy s operátorem CASE Počínaje verzí Oracle9i může být použit operátor CASE uvnitř SQL dotazu. Tento výraz umožňuje provádět jednoduché rozhodování. Obecnou syntaxi je možno zapsat takto: CASE WHEN
V následujícím příkladu můžeme vidět určení hodnoty ve sloupci velikost na základě porovnání atributu vyska s několika konstantními hodnotami: SELECT jmeno, CASE WHEN vyska>110 THEN 'velký' WHEN vyska>107 THEN 'střední' ELSE 'malý' END as velikost FROM TRPASLICI
V dalším příkladu je operátor CASE použit k volbě varianty řazení výsledků dotazu na základě hodnoty vybrané proměnné: proměnné SELECT d.title, a.lastname, a.isbn, cost FROM AUTHORS a, DETAILS d WHERE a.isbn = d.isbn ORDER BY CASE :byorder WHEN 'TITLE' then title WHEN 'LASTNAME' then lastname WHE 'ISBN' then isbn WHEN WHEN 'COST' then cost ELSE NULL END;
David Žák IDAS2/11 – Pokročilé konstrukce SQL dotazů - část I
2
3. Výrazy s operátorem DECODE Operátor DECODE je analogií operátoru CASE, tj. umožňuje provádět jednoduché rozhodování na základě srovnání vstupního výrazu s výčtem hodnot. Základní odlišností je rozdílný způsob zápisu. Podívejme se nejdříve na obecnou syntaxi operátoru DECODE: DECODE(
kde
obdobně pro další porovnání s
Výše uvedený příklad by bylo možné alternativně zapsat pomocí IF-THEN-ELSE příkazů následovně: IF dodavatel_id _id = 10000 THEN výsledek := 'IBM'; ELSIF dodavatel_id _id = 10001 THEN výsledek := 'Microsoft'; ELSIF dodavatel_id _id = 10002 THEN výsledek := 'Hewlett Packard'; ELSE výsledek := 'Jiný Jiný'; END IF;
Z výše uvedeného popisu i příkladů vyplývá, že funkce DECODE porovnává vstupní výraz s množinou konkrétních hodnot. Pokud bychom bych chtěli porovnat určitou David Žák IDAS2/11 – Pokročilé konstrukce SQL dotazů - část I
3
hodnotu například s intervaly hodnot, pak je třeba do vstupního výrazu zakomponovat další operátory či funkce, jeden z možných principů ukazuje následující příklad: SELECT dodavatel_id, _id, DECODE(TRUNC ((dodavatel dodavatel_id - 1) / 10), 0, 'Kategorie 1', 1, 'Kategorie 2', 2, 'Kategorie 3', 'Nezmámý') vysledek FROM DODAVATELE;
4. Konstruktory řádkové a tabulkové hodnoty Standard SQL2 umožňuje řádky v SQL výrazech používat stejným způsobem, jako se používají skalární hodnoty. Standard poskytuje syntaxi pro vytváření řádků dat, umožňuje používat poddotazy s využitím celých řádků a definuje význam řádků pro operátory porovnání atd. Příkladem je konstruktor onstruktor řádkové hodnoty: (7, 'Smudla', 117) 117
což představuje řádek se 3 sloupci. Na základě výše uvedeného principu je tedy možné zapsat následující dotaz: SELECT * FROM TRPASLICI WHERE (id_trpaslika, jmeno) = (7, 'Smudla');
Příkladem konstruktoru tabulkové hodnoty může být ((117, 1984), (115,1983))
což představuje tabulku o 2 řádcích a 2 sloupcích.
Následující příklad říklad ukazuje možnost použití v dotazu pro porovnání konstruktoru řádkového kového hodnoty prezentované (vyska, narozen) a konstruktoru tabulkové hodnoty ((117, 1984), (115,1983)) SELECT * FROM TRPASLICI WHERE (vyska, narozen) IN ((117, 1984), (115,1983)); (115,1983))
David Žák IDAS2/11 – Pokročilé konstrukce SQL dotazů - část I
4
Uvedená konstrukce může významně zkrátit zápis některých komplikovaných podmínek v některých dotazech a tím je celkově zpřehlednit. 5. Pseudo-sloupec ROWNUM ROWNUM je magický sloupec, který je příčinou řady potíží, proto je nezbytné mu porozumět, pak může být velmi užitečný. Hodnota ROWNUM je přiřazena řádku při zpracování dotazu po průchodu fází predikátu dotazu, ale před řazením nebo souhrnem. ROWNUM lze použít: -
Pro ladění dotazů, pro ro číslování v rámci dotazu, pro provádění nejvyšších NN zpracování.
Sloupci ROWNUM budou přiřazena přirozená čísla 1, 2, 3, 4, .. N Příklad: SELECT ROWNUM, ZAMESTNANCI.* ZAMESTNANCI FROM ZAMESTNANCI;
Funkci ROWNUM si můžete ověřit také na tomto případě, kdy bude zřejmé, že výsledek je správně řazen sestupně podle jména zaměstnance, ale hodnoty ROWNUM v jednotlivých řádcích již nebudou uvedeny vzestupně. SELECT ROWNUM, ZAMESTNANCI.* ZAMESTNANCI FROM ZAMESTNANCI ORDER BY JMENO DESC; DESC
Hodnota ROWNUM je zvýšena pouze po jejím přiřazení, proto následující dotaz nikdy nevrátí žádný řádek SELECT * FROM ZAMESTNANCI WHERE ROWNUM<=5 ORDER BY mzda;
Správnýý zápis pro omezení počtu řádků proto musí být založen na přiřazení hodnot ROWNUM řádkům ve vnořeném dotazu a teprve v hlavním dotazu se provede restrikce na vybraná čísla řádků SELECT * FROM (SELECT SELECT dotaz.*, ROWNUM as radek FROM (SELECT * FROM TRPASLICI ORDER BY jmeno) dotaz David Žák IDAS2/11 – Pokročilé konstrukce SQL dotazů - část I
5
) WHERE radek BETWEEN 3 and 5;
Výše uvedený příklad obsahuje dokonce dva vnořené dotazy. Důvodem této konstrukce je skutečnost, že hodnota ROWNUM je přiřazována před řazením podle klauzule ORDER BY. Nejdříve je tedy proveden dotaz včetně klauzule ORDER BY a teprve nadřazený dotaz využívá pseudosloupce ROWNUM, který tedy přiřazuje přiřazuje již seřazeným řádkům z předchozího předchozíh dotazu a označuje tento sloupec názvem radek. Vlastní restrikce dle čísel řádků probíhá až v rámci hlavního dotazu. Použití sloupce ROWNUM nahrazuje pro určení rozsahu řádků na výstupu.
klauzuli
LIMIT
známou
z
MySQL
Alternativně je možné pro očíslování a výběr řádků použít analytické funkce ROW_NUMBER(),, viz následující příklad: SELECT * FROM ( SELECT jmeno, ROW_NUMBER() OVER (ORDER BY jmeno) as radek FROM TRPASLICI) TRPASLICI WHERE radek BETWEEN 3 and 5; 5
Popis analytických funkcí se však již vymyká obsahu této lekce. 6. Použití statických dotazů při měnícím se počtu parametrů filtrů Poměrně často se setkáváme při tvorbě aplikací s problémem, že potřebujeme změnit počet podmínek v dotazu za klauzulí WHERE v závislosti slosti na nastavení filtrů omezujících podmínek pro vyhledávání. vyhledávání Mnoho programátorů tento problém řeší tak, že ke kostře dotazu postupně připojí jednotlivé podmínky spojené operátorem AND v rámci aplikace a sestavený dotaz je následně odeslán databázovému u serveru. Tento přístup má však jedno úskalí. SQL dotaz je v takových jednotlivých případech poskládán odlišně, s různým počtem omezujících podmínek. Databázový server je tak nucen nejdříve vytvořit pro daný dotaz exekuční plán a teprve poté příkaz provést. provés Odlišným způsobem je sestavení dotazu tak, aby v něm byly uvedeny všechny podmínky, které se v aplikaci mohou vyžadovat, a pomocí triku s funkcí NVL se aplikují pouze na ty vázané proměnné, který nenabývají hodnot NULL. Takovýto dotaz je pak jednou zpracován cován optimalizátorem, který pro něho sestaví exekuční plán a následně mnohokrát vykonán (bez potřeby vytvářet exekuční plán pro jednotlivé případy jeho použití).
David Žák IDAS2/11 – Pokročilé konstrukce SQL dotazů - část I
6
Princip zápisu podmínek si můžeme ukázat u na následujícím příkladu: SELECT * FROM TRPASLICI WHERE id_trpaslika = NVL(:id_trp, id_trpaslika) AND jmeno LIKE NVL(:jmeno, jmeno) AND vyska = NVL(:vyska, vyska); vyska)
Pokud příslušná proměnná nabývá hodnoty NULL, je daný výraz převeden na podmínku, že levá strana je rovna pravé straně a podmínka je tedy splněna. Tímto způsobem je tedy jediný dotaz schopen obsloužit různé kombinace podmínek z uživatelského živatelského rozhraní. Jde o jednu z alternativ řešení tohoto problému.
Pojmy k zapamatování Příkazy a funkce:
CAST, CASE, DECODE, ROWNUM
Problém: přehledný zápis dotazu, přiřazení čísla řádku dotazu, konstruktory řádkové a tabulkové hodnoty Shrnutí V této lekci jste se seznámili s dalšími principy pro úpravu dotazů - aplikaci rozhodovacích pravidel do dotazu a číslováním řádků při zpracovávání dotazu. dotazu. Na příkladech jsme si ukázali použití řádkových a tabulkových hodnot v dotazech. Důležitými principy při zápisu dotazu jsou přehlednost, stručnost a nepoužívání atributů a tabulek, které nejsou pro zpracování dotazu nezbytně třeba.
Otázky na procvičení 1. Vysvětlete rozdíl mezi operátory CASE a DECODE? 2. K čemu slouží příkaz CAST a kdy je vhodné jej použít? 3. V které části zpracování dotazu je přiřazena hodnota ROWNUM? 4. Jak zajistit stránkování výsledku dotazu za použití pseudo-sloupce pseudo sloupce ROWNUM? Uveďte konkrétní příklad. 5. Lze použít jediný statický SQL dotaz v případech, kde se mění počet parametrů, podle nichž je prováděna restrikce dat? Odkazy a další studijní prameny • • •
http://www.techonthenet.com/oracle (syntaxe syntaxe příkazů SQL jazyka a funkcí) http://www.oracle.com/technetwork/database/enterprise http://www.oracle.com/technetwork/database/enterpriseedition/documentation (dokumentace k databázové platformě Oracle) http://www.penguin.cz/noviny/?id=chip/index (seriál Databáze standardu SQL z časopisu CHIP)
David Žák IDAS2/11 – Pokročilé konstrukce SQL dotazů - část I
7
Odkazy a další studijní prameny •
LACKO, L. Oracle, správa, programování a použití databázového systému. systému Praha: Computer Press, 2007. ISBN 80-251-1490-2. 80
David Žák IDAS2/11 – Pokročilé konstrukce SQL dotazů - část I
8
12. blok
Pokročilé konstrukce SQL dotazů - část II
Studijní cíl Tento blok je věnován pokročilým konstrukcím SQL dotazů, které umožní psát efektivní kód. Pozornost je věnována vytváření pohledů v rámci dotazů pomocí klauzule WITH a procházení stromovou strukturou. Doba nutná k nastudování
2 - 3 hodiny
Průvodce studiem Při studiu tohoto bloku se předpokládá, že čtenář je seznámen s DML příkazy jazyka SQL. 1. In-line pohledy Pokud se poohlédneme zpět do kurzu IDAS1, najdeme v lekci o pohledech kapitolu hovořící o In-line pohledech. In-line line pohled je vnořený dotaz, který má vlastní alias. V příkazu SELECT se s in-line line pohledem pracuje stejně, jako s běžnou tabulkou. Syntaxe in-line pohledu je: SELECT * FROM (SELECT * FROM tabulka) nazev_pohledu; nazev_pohledu
Použití in-line pohledů jsme prezentovali na jednoduchém příkladu. SELECT vProduktyDodavatele.nazev, ROUND(AVG(vProduktyDodavatele.cena),2) as prumerna_cena FROM (SELECT dodavatele.nazev, produkty.oznaceni, produkty.produkt_id, dodavatele.dodavatel_id, produkty.cena FROM dodavatele JOIN produkty ON produkty.dodavatel_id produkty.dodavatel_ = dodavatele.dodavatel_id ) vProduktyDodavatele GROUP BY vProduktyDodavatele.nazev;
David Žák, Jiří Zechmeister IDAS2/12 – Pokročilé konstrukce SQL dotazů - část II
1
Pro správné pochopení si nyní dotaz rozebereme. Nejdříve byl vyhodnocen inin line pohled vProduktyDodavatele. Výstupem z toho pod-dotazu dotazu byla množina výsledků, na kterou u se provedl druhý dotaz. Stejný příklad by bylo možné řešit i pomocí klasických pohledů: CREATE VIEW vProduktyDodavatele AS SELECT dodavatele.nazev, produkty.oznaceni, produkty.produkt_id, dodavatele.dodavatel_id, produkty.cena FROM dodavatele JOIN produkty ON produkty.dodavatel_id=dodavatele.dodavatel_id WITH READ ONLY; SELECT vProduktyDodavatele.nazev, ROUND(AVG(vProduktyDodavatele.cena),2) as prumerna_cena FROM vProduktyDodavatele GROUP BY vProduktyDodavatele.nazev;
2. Dočasné pohledy - klauzule lauzule WITH Jedním z řešení pro zvýšení čitelnosti a srozumitelnosti dotazů je použití klauzule WITH - kdy hovoříme o Common Table Expression (CTE). CTE je dočasný pohled (temporary view) a používá se zejména v případech, kdy nějaký poddotaz je v rámci dotazu použit vícekrát, nebo - jak si ukážeme v následující kapitole, pro konstrukci rekurzivních dotazů. Výše uvedený příklad můžeme například zapsat následovně s použitím CTE: WITH vProduktyDodavatele AS (SELECT dodavatele.nazev, produkty.oznaceni, produkty.produkt_id, dodavatele.dodavatel_id, produkty.cena FROM dodavatele David Žák, Jiří Zechmeister IDAS2/12 – Pokročilé konstrukce SQL dotazů - část II
2
JOIN produkty ON produkty.dodavatel_id=dodavatele.dodavatel_id) SELECT vProduktyDodavatele.nazev, ROUND(AVG(vProduktyDodavatele.cena),2) as prumerna_cena prumer FROM vProduktyDodavatele GROUP BY vProduktyDodavatele.nazev;
Samozřejmě tímto řešením dostaneme shodný výsledek jako v přechozím případě. CTE významně zvyšují přehlednost dotazů. Navíc není nutné vytvářet pohledy ve smyslu samostatných databázových databázových objektů, ale definovat je jako dočasné pohledy v rámci konkrétních dotazů. Klauzule WITH tedy umožňuje přiřadit název určitému poddotazu uvedenému před vlastním (hlavním) dotazem SELECT. V něm se pak můžete odkazovat na tento dočasný pohled zadáním jeho jména – obdobně jako při práci s pohledy. Oracle optimalizuje zpracování dočasného pohledu uvedeného jména,, pracuje s ním buď jako s inline pohledem em nebo jako s dočasnou tabulkou. Jednotlivé dočasné pohledy se mohou odkazovat na předchozí - dříve definované nované dočasné pohledy,, stejně jako se na ně odkazuje hlavní dotaz. Použití klauzule WITH je velice vhodné v případě, kdy je výsledek dočasného pohledu odkazován vícekrát cekrát v rámci jediného dotazu, když například průměrné hodnoty zjištěné dočasným pohledem musí usí být několikrát porovnávány během vykonávání dotazu a běžné řešení by znamenalo bud zřízení samostatného pohledu nebo uvedení několika totožných vnořených dotazů v rámci hlavního dotazu. Obecná syntaxe je: WITH
David Žák, Jiří Zechmeister IDAS2/12 – Pokročilé konstrukce SQL dotazů - část II
3
3. Hierarchické dotazy (Oracle 10g) Hierarchické dotazy slouží k získání dat seřazených v hierarchickém pořadí. Dotaz je možné použít také nad spojením dvou či více tabulek. Základní princip Jak je zobrazeno na následujícím obrázku, hierarchický dotaz vyžaduje navíc pouze klauzuli CONNECT BY, která definuje, přes které atributy se bude provádět spojení hierarchických dat. K definici nadřazeného atributu je k dispozici klauzule PRIOR. Pomocí konstrukce START WITH, můžeme definovat, od kterého záznamu má být dotaz vyhodnocován. Klauzule NOCYCLE zamezí cyklickému procházení dat. Ke zjištění, na kterém záznamu vzniká cyklický odkaz, slouží pseudo-sloupec CONNECT_BY_ISCYCLE, který obsahuje hodnotu 1 pro řádky, kde k cyklickému odkazu došlo, jinak 0.
V rámci hierarchických dotazů je k dispozici také pseudo-sloupec LEVEL, který zobrazuje úroveň záznamu v hierarchické posloupnosti. Dále máme k dispozici další funkce a operátory pro práci nad hierarchicky organizovanými daty. Pseudo-sloupec CONNECT_BY_ISLEAF Pseudosloupec, který nabývá hodnotu 1, pokud záznam je v hierarchické úrovni listem. Jinak nabývá hodnotu 0. SELECT ..., CONNECT_BY_ISLEAF as jeListem, ... FROM ... CONNECT BY ...
David Žák, Jiří Zechmeister IDAS2/12 – Pokročilé konstrukce SQL dotazů - část II
4
Pseudo-sloupec CONNECT_BY_ROOT Operátor, který vrátí pro daný sloupec hodnotu na nejvyšší hierarchické úrovni. SELECT ..., CONNECT_BY_ROOT jmeno as jmenoReditele, ... FROM ... CONNECT BY ...
Pseudo-sloupec SYS_CONNECT_BY_PATH Funkce, která vrací kompletní cestu sestavenou pomocí sloupce, který vstupuje jako atribut a oddělovače, který vstupuje jako druhý atribut. SELECT ..., SYS_CONNECT_BY_PATH(jmeno, '/') as jmenoCesta, ... FROM ... CONNECT BY ...
Pro třídění dat se zachováním hierarchické úrovně je možné použít v klauzuli ORDER BY klíčové slovo SIBLINGS, které toto zajišťuje. Data jsou na výstupu řazena podle atributů uvedených v klauzuli GROUP BY, ale vždy pouze v rámci množiny, která má stejného předka. V dotazu je klauzule použita následovně. SELECT ... FROM ... CONNECT BY ... ORDER BY SIBLINGS jmeno ASC
David Žák, Jiří Zechmeister IDAS2/12 – Pokročilé konstrukce SQL dotazů - část II
5
Postup vyhodnocení hierarchických dotazů 1. Nejdříve jsou vyhodnocena vyhodnocen spojení tabulek, pokud jsou k dispozici. 2. Řádky vyhovující podmínce ve START WITH jsou považovány za kořenové řádky na první úrovni vnoření 3. Pro každý řádek na úrovni i se rekurzivně hledají přímí potomci vyhovující podmínce v klauzuli CONNECT BY B na úrovni i+1, řádek ádek předka se v podmínce označuje klíčovým slovem PRIOR 4. Na závěr jsou odstraněny řádky nevyhovující podmínce uvedenou v klauzuli WHERE 5. Pokud není definováno třídění, odpovídá pořadí průchodu pre-order pre Příklad použití Fungování hierarchických dotazů si můžeme ukázat na jednoduchém příkladu. Máme tabulku, která popisuje osoby. Obsahuje atributy jméno, příjmení, adresu, datum narození a pohlaví označené písmenem M pro muže a písmenem Z pro ženu. Dále je zde také přítomen atribut ID, ID, sloužící jako jednoznačná identifikace každé osoby, která se v tabulce nachází. Součástí tabulky jsou také identifikátory (ID) otce a matky každé osoby. Pokud tedy vezmeme v úvahu následující generační strom, tabulku si můžeme představit následovně: Jarmila Malá
Jan Malý
Martin Veselý
Petr Vyndal
Petra Veselá
Petr Ostnatý
Jana Mala
Jan Veselý
Jan Vyndal
Robert Ostnatý
Ivana Ostnatá
Marta Velká
Lenka Ostnatá
Obr. 1 - Generační strom pro interpretaci hierarchických dotazů
David Žák, Jiří Zechmeister IDAS2/12 – Pokročilé konstrukce SQL dotazů - část II
6
ID
Jméno
Příjmení
Pohlaví
1
Jan
Malý
M
2
Jarmila
Malá
3
Jana
4
Datum narození
ID otce
ID matky
10. 3. 1898
(null)
(null)
Z
4. 6. 1901
(null)
(null)
Malá
Z
3. 12. 1920
1
2
Jarmil
Veselý
M
9. 9. 1921
(null)
(null)
5
Petr
Ostnatý
M
14. 1. 1918
(null)
(null)
6
Petra
Veselá
Z
5. 5. 1941
4
3
7
Jan
Veselý
M
2. 10. 1945
4
3
8
Robert
Ostnatý
M
8. 2. 1948
5
3
9
Petr
Vyndal
M
25. 7. 1947
(null)
(null)
10
Marta
Velká
Z
13. 9. 1950
(null)
(null)
11
Jan
Vyndal
M
14. 12. 1963
9
6
12
Ivana
Ostnatá
Z
4. 7. 1972
8
10
13
Lenka
Ostnatá
Z
14. 4. 1975
8
10
Tabulka Ilustrační obsah tabulky osoby
Nyní bude ilustrován rozdíl mezi použitím běžného dotazu a použitím hierarchického dotazu. Veškeré dotazy budou pracovat s tabulkou osoby, která je znázorněna výše. Dotazy jsou platné pro databázový server Oracle verze 10g. První příklad použití hierarchických dotazů bude nalezení společných potomků Jarmily Malé a Jana Malého. Nejdříve zápis pomocí běžného dotazu bez použití klauzule CONNECT BY. SELECT osoby.* FROM osoby WHERE id_matky = ( SELECT id FROM osoby WHERE jmeno='Jarmila' jmeno AND prijmeni='Malá' ) AND id_otce = ( SELECT id FROM osoby WHERE jmeno='Jan' jmeno AND prijmeni='Malý' );
David Žák, Jiří Zechmeister IDAS2/12 – Pokročilé konstrukce SQL dotazů - část II
7
Klasický dotaz využívá dvou vnořených dotazů, pro zjištění identifikátoru osob, které následně vstupují do podmínky. Pokud použijeme hierarchický dotaz s klauzulí CONNECT BY, zbavíme se vnořených dotazů. SELECT osoby.* FROM osoby CONNECT BY id_matky = PRIOR id AND id_otce = PRIOR id START WITH (jmeno, prijmeni) IN (('Jarmila','Malá'),('Jan','Malý')) WHERE level = 2
Na první pohled je hierarchický dotaz přehlednější než dotaz běžný. Není třeba žádných vnořených dotazů. Za klauzulí START WITH je použit konstruktor řádkové hodnoty pro nalezení potřebných kombinací jména a příjmení. Podmínka na konci dotazu říká, že mají být zobrazeny pouze záznamy na úrovni 2, což jsou potomci osob, které jsou zadané za klauzulí START WITH. Výstupem z následujícího příkladu jsou všichni vnuci Jarmily Malé. Běžným dotazem je možné výsledku dosáhnout za použití tří do sebe vnořených dotazů. Dotaz na nejnižší úrovni zjišťuje identifikátor Jarmily Malé, druhý dotaz zjišťuje všechny děti Jarmily Malé a teprve až třetí dotaz vrátí požadovaný výsledek, tedy vnuky. SELECT osoby.* FROM osoby WHERE id_otce OR id_matky IN ( SELECT id FROM osoby WHERE id_matky = ( SELECT id FROM osoby WHERE jmeno='Jarmila' AND prijmeni='Malá' ) ) AND pohlavi='M';
Naproti tomu hierarchický dotaz je téměř stejný jako u prvního příkladu, pouze se liší podmínky za klauzulí START WITH a WHERE.
David Žák, Jiří Zechmeister IDAS2/12 – Pokročilé konstrukce SQL dotazů - část II
8
SELECT osoby.* FROM osoby CONNECT BY PRIOR id = id_otce AND PRIOR id = id_matky START WITH jmeno='Jarmila' 'Jarmila' AND prijmeni='Malá' WHERE level=3 AND pohlavi='M'; pohlavi
Posledním příkladem použití hierarchických dotazů je zjištění všech žen, které jsou pokrevně spřízněny s párem Jarmila Malá a Jan Malý. Jedná se na první pohled o primitivní úlohu, ale běžným dotazem ji není možné realizovat v přehledné formě. Použití hierarchického dotazu je však stále jednoduché a mění se pouze podmínky. Pro definování počátku hierarchické ierarchické úrovně je opět využito konstruktoru řádkové hodnoty. SELECT osoby.* FROM osoby CONNECT BY id_matky = PRIOR id AND id_otce = PRIOR id START WITH (jmeno, prijmeni) prijmeni IN ('Jarmila','Malá' 'Malá'),('Jan','Malý')) WHERE pohlavi='Z'
Jak vyplývá z uvedených příkladů, některá zadání již běžnými dotazy není možné řešit a použití hierarchických dotazů je nezbytné. Úlohy, na které je ještě možné běžné dotazy použít, působí při použití hierarchických dotazů více přehledně a minimalizuje se tak možnost chyb při psaní dotazů. Na rozdíl od běžných dotazů je však tento typ značně náročnější na výpočetní výkon databázového stroje. Pokud byste chtěli výstup z dotazu použít pro zobrazení ve formě rozbalovací hierarchie tak, jak to třeba dělá u souborů Windows Windows Explorer, bude se vám hodit i pseudosloupec CONNECT_BY_ISLEAF, který určuje, zda je aktuální záznam na poslední úrovni hierarchie (CONNECT_BY_ISLEAF=1) nebo zda má podřízené záznamy (CONNECT_BY_ISLEAF=0). Níže uvedený příklad využívají i hodnoty LEVEL pro o zarovnání všech dceřiných prvků pomocí mezer vložených před názvy jména osob. SELECT
lpad(' ',level*3)||PRIJMENI||' '||JMENO name, SYS_CONNECT_BY_PATH(PRIJMENI, '/') path, CONNECT_BY_ROOT PRIJMENI topmgr, CONNECT_BY_ISLEAF isleaf, level FROM A_HR.ZAMESTNANCI CONNECT BY MANAZER_ID = PRIOR ZAMESTNANEC_ID START WITH MANAZER_ID is null ORDER SIBLINGS BY PRIJMENI;
David Žák, Jiří Zechmeister IDAS2/12 – Pokročilé konstrukce SQL dotazů - část II
9
4. Hierarchické dotazy dle ANSI SQL Od verze databázového serveru Oracle 11g je k dispozici také alternativa pro konstrukci hierarchických dotazů, která je pro databázový stroj přirozenější a může lépe optimalizovat a plánovat jeho provádění. Tato alternativa je zajištěna pomocí rekurzivního volání pohledů. Hierarchické dotazy dle ANSI SQL používají rekurzívní WITH klauzuli, která se odkazuje sama na sebe. Zavedení této syntaxe do Oracle 11gR2 zajišťuje hierarchickým dotazům Oracle SQL kompatibilitu s ANSI. ¨ Celou syntaxi si ukážeme na následujícím příkladu: WITH ORG_PRACOVNIKU (zamestnanec_id, zamestnanec_id, prijmeni, jmeno, manazer_id, uroven) as ( SELECT zamestnanec_id, prijmeni, jmeno, manazer_id manazer_id, 1 FROM A_HR.ZAMESTNANCI ZAMESTNANCI WHERE manazer_id is NULL -- začni od zaměstnance, který nemá nadřízeného UNION ALL -- spoj výsledek předchozího dotazu s výsledkem následujícího dotazu
SELECT pracovnici. pracovnici.zamestnanec_id , pracovnici. pracovnici.prijmeni, pracovnici. pracovnici.jmeno, pracovnici. pracovnici.manazer_id, manazeri.uroven+ 1 FROM A_HR.ZAMESTNANCI ZAMESTNANCI pracovnici join ORG_PRACOVNIKU manazeri on (manazeri.ZAMESTNANEC_ID (manazeri = pracovnici.MANAZER_ID) -- podmínka spojení a rekurzívní volání ) SELECT * FROM ORG_PRACOVNIKU; ORG_PRACOVNIKU
David Žák, Jiří Zechmeister IDAS2/12 – Pokročilé konstrukce SQL dotazů - část II
10
Pojmy k zapamatování Příkazy a funkce:
Klauzule WITH, CONNECT BY, CTE, hierarchické dotazy
Problém: dočasné pohledy, in-line in line pohledy, průchod stromovou strukturou, hierarchické dotazy Shrnutí V této lekci jste se seznámili s tvorbou SQL dotazů, které umožňují pracovat s dočasnými pohledy a hierarchickými strukturami. Ačkoli se mohou mírně lišit způsoby zápisu dočasných pohledů a hierarchických dotazů mezi jednotlivými databázovými platformami, seznámili jsme se s proprietárním řešením Oracle, které je přehlednější z hlediska zápisu, tak i řešením podle ANSI SQL, které se objevuje objevuje i v dalších databázových platformách a je univerzálnější.
Otázky na procvičení 1. 2. 3. 4. 5.
line pohledy? Vysvětlete, co jsou to in-line Jak se definují dočasné pohledy v rámci SQL dotazu? K čemu slouží hierarchické dotazy? Uveďte obecnou syntaxi hierarchického dotazu do s klauzulí CONNECT BY? Jak se tvoří hierarchické dotazy podle ANSI SQL.
Odkazy a další studijní prameny • • •
http://www.techonthenet.com/oracle (syntaxe příkazů SQL jazyka a funkcí) http://www.oracle.com/technetwork/database/enterprise http://www.oracle.com/technetwork/database/enterpriseedition/documentation (dokumentace k databázové platformě Oracle) http://technology.amis.nl/blog/6104/oracle http://technology.amis.nl/blog/6104/oracle-rdbms-11gr2-goodbyeconnect-by-or-the-end-of of-hierarchical-querying-as-we-know-it
Odkazy a další studijní prameny • •
LACKO, L. Oracle, správa, programování a použití databázového systému. systému Praha: Computer Press, 2007. ISBN 80-251-1490-2. 80 Hierarchical queries. Oracle® Database SQL Reference. [Online] Oracle, 2005. [Citace: 22. Duben 2011.] http://download.oracle.com/
David Žák, Jiří Zechmeister IDAS2/12 – Pokročilé konstrukce SQL dotazů - část II
11
13. blok
Práce s XML dokumenty v databázi Oracle
Studijní cíl Tento blok je věnován práci s XML dokumenty, možnostem možnost jejich uložení a práce s nimi v databázi Oracle a datovému typu XMLType.
Doba nutná k nastudování
2 - 3 hodiny
Průvodce studiem Při studiu tohoto bloku se předpokládá, že čtenář je obeznámen se strukturou XML dokumentů a DDL a DML příkazy jazyka SQL.
1. XML v databázi Oracle a XMLType V dnešní informační době, ve které existují požadavky na kvalitní výměnu informací, značně vzrostl tlak na jednotný komunikační prostředek. Tento prostředek by měl umožňovat komunikaci nezávislou na platformě, databázovém prostředí či operačním systému. V dnešní době se za číslo jedna mezi těmito nástroji považuje XML, a proto se Oracle rozhodl pro jeho komplexní implementaci v rámci svého databázového systému. Databáze společnosti Oracle nejenom že umí zpracovávat databázové dotazy, jejichž výsledkem jsou XML dokumenty, ale rovněž umožňuje celé XML dokumenty do databáze ukládat a to dokonce i v rámci jednotlivých sloupců. V této kapitole bude popsáno velmi stručně, co to je XML a jaká je jeho základní syntaxe. Dále zde bude probrána podpora XML v databázi Oracle a na konec zde bude trochu podrobněji popsán datový typ XMLType. 2. XML XML (z anglického Extensible Markup Languge) je tzv. značkovací jazyk, který vznikl pod hlavičkou konsorcia W3C zjednodušením staršího jazyka SGML. Standard v podstatě definuje obecnou syntaxi, ve které jsou námi zasílaná data označovaná pomoci srozumitelných značek. Jazyk XML je neskutečně flexibilní a lze ho využít v nejrůznějších oblastech. Od webových stránek přes elektronické obchodování, vektorovou grafiku či serializaci objektů. o Tomáš Váňa, David Žák IDAS2/13 – Práce s XML dokumenty v databázi Oracle
1
Podstatné na jazyku XML je to, že se jedná o meta-značkovací jazyk. To znamená, že není definována žádná pevně daná množina značek. XML dává vývojářům volnou ruku v definici elementů a atributů jazyka, takže si lze jazyk libovolně rozšiřovat tak, tak aby by plně vyhovoval mnoha různým potřebám. Na druhou stranu je důležité zmínit, že jak je jazyk volný k definování vlastních elementů, tak je na druhou stranu velmi striktní v jejich zápisu. XML definuje tzv. gramatiku jazyka, jenž přesně definuje umístění, kde se dané elementy a jejich atributy mohou objevit. Gramatika je důležitá vzhledem k vývoji analyzátorů XML dokumentů. Bez této gramatiky by byl vývoj analyzátorů velmi složitý. Každý XML dokument, jenž vyhoví gramatice jazyka, se považuje za správně formulovaný. Pokud je dokument správně formulovaný, znamená to, že projde analyzátorem bez problému. Ovšem správně formulovaný dokument nezaručuje platnost dokumentu, čili zda má dokument vhodnou strukturu pro naši aplikaci. Proto jazyk XML umožňuje vytvořit definici typu dokumentu (DTD), jenž určuje, kde a jak mohou být jednotlivé značky v rámci dokumentu použity. Dokument, jenž vyhoví DTD, je označen za platný. Platnost či neplatnost dokumentu vždy závisí na definici DTD, se kterým ho porovnáváme. DTD je však vš nepovinné. Nyní malá ukázka XML dokumentu:
Tomáš Váňa, David Žák IDAS2/13 – Práce s XML dokumenty v databázi Oracle
2
3. Oracle XML DB Oracle začal s podporou XML přímo v databázi již v roce 1998, a to konkrétně ve verzi 8i. Se zvyšujícím se číslem verze nejenom, že rostly možnosti databáze pracovat s XML formátem, zároveň rostl i celkový výkon databáze při práci s XML formátem. Oracle souhrnně nazývá balík podpory pro práci s XML zjednodušeně XML DB [2]. Ve své podstatě se jedná o množinu technologií, zaměřených na vyhledávání a ukládání XML dokumentů v rámci databáze. Všechny tyto technologie jsou nativně implementovány přímo v databázi, proto můžeme využívat výhody provádění XML dotazů nad daty uloženými v rámci relační databáze, a zároveň můžeme provádět běžné SQL operace nad XML daty. Následující obrázek velmi stručně shrnuje výhody využití XML DB.
Obr. 1 - Výhody využití Oracle XML DB. Zdroj: Oracle.
Tomáš Váňa, David Žák IDAS2/13 – Práce s XML dokumenty v databázi Oracle
3
Základem architektury celé XML DB jsou: •
Tabulky a pohledy využívající XMLType o
S XMLType získáváme možnost přirozeně uložit XML (například jako sloupec v tabulce) s možností nám běžně známého dotazování pomocí SQL.
•
Oracle XML DB Repository o
Repositář XML umožňuje jednotlivé XML dokumenty třídit do složek a řídit k nim přístup, využívat správu verzí, atd…
Následující obrázek obsahuje nákres architektury XML DB:
Obr. 2 - Architektura XML DB. Zdroj: Oracle.
Tomáš Váňa, David Žák IDAS2/13 – Práce s XML dokumenty v databázi Oracle
4
4. Oracle XML Repository Pokud to řekneme velmi jednoduše, XML Repositář je úložiště XML dokumentů, jejich sklad, který umožňuje přístup k těmto dokumentům. Pro přístup k dokumentům můžeme zvolit různé protokoly (HTTP, FTP, WebDAV). V tomto „skladu“ můžeme spravovat různá přístupová práva, spravovat adresáře. XML Repositář se skládá z těchto základních komponent [4]: • • • • • •
Seznam a správa přístupových oprávnění (ACL). Správa složek. WebDAV a FTP protokol pro přístup ke složkám. SQL vyhledávání v XML Repositářích. API pro práci s XML Repositáři. Přístup pomocí Java servletu – manipulace s objekty.
Do Oracle XML repositáře v podstatě existují dvě přístupové cesty [3]: •
•
Přístup založený na cestě (navigační přístup). Při tomto přístupu se využívá hierarchického indexu ukazujícího na jednotlivé zdroje a objekty v repositáři. Každý zdroj v repositáři má jednu nebo více unikátních cest, jež odráží jeho umístění v hierarchii. Těmito přístupovými cestami můžeme referencovat libovolný XMLType objekt v databázi, bez ohledu na jeho umístění v tabulkových prostorách. Přístup do repositáře za využití SQL skrze speciální pohledy, které jsou schopné mapovat prostor repositáře do Oracle XML DB schématu. Pro přístup do repositáře slouží dva pohledy a to konkrétně PATH_VIEW a RESOURCE_VIEW. PATH_VIEW obsahuje jeden řádek pro každou unikátní cestu v repositáři. Naproti tomu RESOURCE_VIEW je vytvářen stylem co jeden zdroj, to jeden řádek. Řádky v těchto pohledech jsou datového typu XMLType. Nad těmito pohledy je možné provádět DML operace, které vkládají, přejmenovávají, mažou či aktualizují obsah zdroje. Pro ostatní operace (jako vytvoření zdroje) je nutné využít služeb API.
Tomáš Váňa, David Žák IDAS2/13 – Práce s XML dokumenty v databázi Oracle
5
5. Datový typ XMLType V této podkapitole se dostaneme ke druhému způsobu ukládání XML dat do databáze. A to pomocí datového typu XMLType. Tento typ umožňuje uložit XML dokument do databázové tabulky. Pokud se podíváme trochu hlouběji do historie XML v databázi Oracle, zjistíme, že datový typ XMLType se poprvé objevil ve verzi 9 pro usnadnění práce s daty v XML uvnitř databáze. Abychom byli úplně přesní, tak XMLType je objektový datový typ, což znamená, že tento datový typ obsahuje i některé členské funkce. Příkladem takové členské funkce může být funkce, jež zkonvertuje textový řetězec obsahující XML dokument do datového typu XMLType. A co nám tedy použití XMLType umožňuje: •
•
•
XMLType může reprezentovat libovolný XML dokument uložený v databázi. Díky tomu, že je uložen v databázové tabulce, je přístupný přes SQL příkazy. Tím nám zůstávají zachovány základní výhody relačního přístupu, a zároveň však zůstávají zachovány všechny výhody XML formátu. XMLType je využitelný v PL/SQL, a proto jej můžeme využít jako vstupní parametr, proměnnou či návratovou hodnotu uložené procedury. XMLType obsahuje členské funkce. Tyto funkce umožňují provádět různé operace nad dokumentem uloženým v databázi. Můžeme například z daného dokumentu extrahovat pouze některé části. (členská funkce extract())
Nyní, když známe možnosti datového typu XMLType, můžeme se podívat na jednoduché ukázky toho, jak typem XMLType pracovat. Nejprve bude předvedeno založení tabulky obsahující XMLType a vkládání hodnot do této tabulky. Dále bude uveden příklad na využití některých členských funkcí typu XMLType v rámci SQL. V závěru bude uveden příklad na vytvoření typu XMLType nad obyčejnou relační tabulkou. Definovat tabulku, obsahující sloupec datového typu XMLType je velmi jednoduché. CREATE TABLE xml_tab ( id number(10), xml XMLType NOT NULL );
Tomáš Váňa, David Žák IDAS2/13 – Práce s XML dokumenty v databázi Oracle
6
Vložení nových řádků do tabulky obsahující XMLType je stejně jednoduché. INSERT INTO xml_tab(id,xml) xml_tab VALUES ( 1, XMLType( '
Při dotazování nad sloupci s XMLType se nejčastěji využívá funkcí extract a existNode. Následující příklad demonstruje využití funkce existNode v klauzuli klauzul WHERE: SELECT x.xml.getClobval getClobval() AS poXML FROM xml_tab x WHERE x.xml.existsNode existsNode('/zamestnanec[plat lat > 80000 ]') ]' = 1;
Zápis '/zamestnanec[plat > 80000 ]' předávaný funkci existNode říká, že se hledají jen ti zaměstnanci, jejichž plat je vyšší než 80000. Funkce extract se využívá k extrakci určitých uzlů z XML Dokumentu. Viz následující příklad: SELECT EXTRACT(xml,'/zamestnanec/jmeno/text()' '/zamestnanec/jmeno/text()').getStringVal() () || ' ' || EXTRACT(xml,'/zamestnanec/prijmeni/text()' '/zamestnanec/prijmeni/text()').getStringVal getStringVal () AS cele_jmeno, EXTRACT(xml,'/zamestnanec/plat/text()' '/zamestnanec/plat/text()').getNumberVal() AS plat FROM xml_tab;
Tomáš Váňa, David Žák IDAS2/13 – Práce s XML dokumenty v databázi Oracle
7
Dává následující výstup:
Obr. 3 - Výstup funkce extract().
Jak je vidět, pomocí funkce extract() lze vytvořit z jednoho sloupce datového typu XMLType více sloupců odpovídajících jednotlivým uzlům XML dokumentu. Takto můžeme nad XMLType sloupci vytvářet pohledy, které budou vytvářet zdání klasické tabulky. Nyní bude představen opačný přístup, kdy z klasické tabulky čítající mnoho sloupců (bez XMLType) vytvoříme jeden XMLType sloupec, který bude mít v každém řádku XML dokument, jehož obsah bude korespondovat s odpovídajícím ícím řádkem původní tabulky. Máme tedy tabulku:
Obr. 4 - Zdrojová data pro XMLELEMNT.
Nyní nad touto tabulkou spustíme následující dotaz: SELECT XMLELEMENT("KLIENT" "KLIENT", XMLAttributes XMLAttributes(cli_id AS id), XMLElement XMLElement("JMENO",cli_name), XMLElement XMLElement("PRIJMENI",cli_surname), XMLElement XMLElement("DATUM_NAR",cli_birthday), ), XMLElement XMLElement("NARODNOST",cli_nationality cli_nationality) , XMLElement XMLElement("MOBIL",cli_mobile), XMLElement XMLElement("EMAIL",cli_email) )KLIENTI KLIENTI FROM clients;
Tomáš Váňa, David Žák IDAS2/13 – Práce s XML dokumenty v databázi Oracle
8
Dotaz vygeneruje následující výstup:
Obr. 1 - Výsledek volání funkce XMLELEMENT.
Pojmy k zapamatování Příkazy a funkce:
EXTRACT, XMLELEMENT, XMLType
Problém: práce s XML dokumenty v prostředí databázového serveru, konverze dat mezi klasickými tabulky či výstupu běžných SQL dotazů a XML dokumenty Shrnutí Využití XML v rámci databáze Oracle je velice jednoduché jednoduché a poskytuje velmi široké možnosti využití. Díky nativní podpoře XML přímo v databázi můžeme nad XML dokumenty provádět standardní XML dotazy a naopak můžeme nad běžnými tabulkami spouštět dotazy, jejichž výsledkem je XML dokument založený na datech obsažených v tabulce. Možnost vytvářet XML dokumenty přímo v databázi značně urychluje vývoj aplikací, neboť není nutné zavádět další vrstvu, která by obhospodařovala tvorbu XML dokumentů. Navíc je generování dokumentů v rámci databáze velmi rychlé a vytvoření oření samotného dokumentu je poměrně jednoduché.
Otázky na procvičení 1. 2. 3. 4. 5.
Popište architekturu XML DB. DB Co je Oracle XML Repository? Repository Charakterizujte možnosti práce s datovým typem XMLType. Jmenujte některé funkce pro práci s XML dokumenty. Jakým způsobem lze vytvořit XML dokument z výstupu běžného SQL dotazu??
Tomáš Váňa, David Žák IDAS2/13 – Práce s XML dokumenty v databázi Oracle
9
Odkazy a další studijní prameny [1] Wikipedia [online]. 2011-02-23 23 [cit. 2011-04-27]. 2011 XML. Dostupné z WWW:
Odkazy a další studijní prameny •
LACKO, L. Oracle, správa, programování a použití databázového systému. systému Praha: Computer Press, 2007. ISBN 80-251-1490-2. 80
Tomáš Váňa, David Žák IDAS2/13 – Práce s XML dokumenty v databázi Oracle
10