1. blok Systémový katalog

1. blok

Systémový katalog

Studijní cíl Tento blok je věnován seznámení se systémovým katalogem z pohledu ANSI/ISO SQL2, ale i s reálným řešením systémového katalogu v databázích Oracle.

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 s základy jazyka SQL a různými druhy databázových objektů. objektů

1. Systémový katalog Pro správné fungování databáze a provádění jednotlivých funkcí je třeba velké množství informací. Tyto informace o struktuře databáze jsou uloženy v systémovém katalogu. Systémový katalog je souborem všech tabulek a pohledů nad nimi, které databáze jednak udržuje pro svou vlastní potřebu, ale také zpřístupňuje uživatelům databáze prostřednictvím standardních SQL dotazů. Údaje systémového katalogu také používají různé aplikace pro zadávání dotazů a komunikaci s databázovým serverem, které na pozadí volají dotazy dotazy do systémového katalogu a výsledky vizualizují ve formě tabulek, stromových struktur, předvyplněných formulářů a podobně. Takto využívá systémový katalog i SQL Developer. V systémovém katalogu můžeme například zjistit informace o existujících databázových ových objektech, jejich vlastnících, datech vytvoření a posledních změnách změnách, seznamy tabulek, jejich sloupců, použitých datových typů, omezeních, právech, ale i spoustu informací souvisejících s fyzickým uložením tabulek a podobně.

David Žák IDAS2/1 – Systémový katalog

1

1.1 Co je systémový katalog Systémový katalog (někdy nazývaný také jako Datový slovník – Data Dictionary) je soubor speciálních tabulek v databázi, které vytváří, aktualizuje a vlastní sám databázový systém obvykle prostřednictvím některého ze systémových uživatelů pod identifikátory SYSTEM, SYS nebo DBA. Tyto tabulky obsahují data, která popisují strukturu databáze. Abychom pochopili smysl údajů ze systémového katalogu, podívejme se na zpracování následujícího dotazu: Zjistěte dodavatele a produkty, které dodávají: SELECT nazev, oznaceni, cena FROM PRODUKTY JOIN DODAVATELE ON PRODUKTY.dodavatel_id = DODAVATELE.dodavatel_id;

V systémovém katalogu bude třeba ověřit •

• •

•

Existenci názvů uvedených tabulek či pohledů PRODUKTY a DODAVATELE ve schématu uživatele, který zadal dotaz, dotaz, pokud zde nebudou, pak ověřit, zda neexistuje synonyma ukazující na objekt s jiným názvem či v jiném schématu Ověření, že uživatel má právo pro SELECT těchto tabulek Zkontrolovat, zda uvedené sloupce v daných databázových objektech existují a zda neexistuje istuje konflikt v názvech požadovaných sloupců v uvedených databázových objektech Určit datové typy pro požadované sloupce

Jak již bylo uvedeno, databázový systém také zpřístupňuje obsah systémových tabulek uživatelům a to buď přímo, nebo prostřednictvím pohledů. pohledů. Samozřejmě jsou tyto přístupy určeny pouze pro čtení z důvodu zachování integrity dat. Aktualizaci zajišťuje sama databáze na základě uživatelem prováděných akcí přímo přímo v databázových objektech. Lze říci, že každá DDL operace provede kromě vlastních změn (vytvoření, úprava či odstranění) databázového objektu také aktualizaci dat v systémovém katalogu.


2

1.2 Systémový katalog dle standardu ANSI/ISO SQL1 a SQL2 Standard ANSI/ISO SQL1 nespecifikoval strukturu a obsah systémového katalogu, proto se v různých databázích značně liší struktura katalogu a obsah jeho tabulek. V době přijetí standardu SQL2 již nebylo možné dosáhnout shody, neboť jednotlivé komerční produkty se již značně v této části lišily. Místo toho byl alespoň definován idealizovaný systémový katalog (ve standardu nazýváno jako definiční schéma), který by mohl výrobce databáze použít, kdyby začínal tzv. na zelené louce. Místo toho ale standard SQL2 definuje pohledy na tabulky systémového katalogu, jež identifikují objekty databáze, ke kterým má uživatel přístup (těmto pohledům se ve standardu říká informační schéma). Pro střední a plnou podporu standardu SQL2 musí databáze tyto pohledy podporovat. V praxi to znamenalo, že většina výrobců databází implementovala nad vlastní strukturou tabulek systémového katalogu toto jednotné informační schéma. My se však nebudeme zabývat pohledy definovanými v informačním schématu standardu SQL2 a v další kapitole se podíváme na systémový katalog databáze Oracle. 1.3 Systémový katalog v databázích Oracle Systémový katalog (Oracle Data Dictionary) se skládá z tabulek a pohledů poskytujících informace o databázi a její struktuře, ty jsou uloženy v tabulkovém prostoru SYSTEM. Slovník se skládá ze: •

základních tabulek (typicky označované x$…) – – –

•

uchovávají informace o databázi pouze Oracle by měl zapisovat a číst do/z těchto tabulek jsou důležité pro správnou funkčnost databáze

pohledy –

tyto pohledy shromažďují a zobrazují v čitelné podobě informace uložené v základních tabulkách katalogu (např. DBA_TABLES)

•

Všechny tabulky i pohledy vlastní uživatel SYS.

•

systémový katalog je složen z množin pohledů. V mnoha případech mají množiny tři podobné pohledy lišící se v názvu prefixem: – –

USER_ ALL_


– všechny objekty, které uživatel vlastní – všechny objekty, ke kterým má uživatel přístup

3

–

•

DBA_ – všechny administrátory)

objekty

(pohledy

pro

databázové

sloupce v každé množině jsou identické s následujícími výjimkami: – –

pohledy s prefixem USER_ USER většinou nemají sloupce OWNER (očekává se, že vlastníkem je uživatel, který dotaz spustil) některé DBA_ pohledy mají další sloupce navíc s informacemi užitečnými pro DBA

Jelikož likož počet pohledů systémového katalogu je obrovský počet,, nemůžeme se zabývat všemi, ale ukážeme si nejdříve, jak je zjistit: Zjištění různých pohledů systémového katalogu a jejich významu: SELECT * FROM dictionary ORDER BY table_name; table_name

Rozsah výsledku lze ze omezit např. pokud hledáme pohledy pojednávající o tabulkách: SELECT table_name, comments FROM dictionary WHERE table_name LIKE '%TABLE%' ORDER BY table_name

Popisy sloupců jednotlivých pohledů systémového katalogu lze najít takto,, uveden příklad pro pohled ALL_TAB_COLUMNS: SELECT column_name, comments FROM dict_columns WHERE table_name = 'ALL_TAB_COLUMNS'


4

Pro získání základní přehledu uveďme aspoň základní pohledy (tučně uvedené slovo se může vyskytovat se v závorce uvedenými uvedeným předponami, pokud je uveden v druhé závorce ještě zkrácený název, může být použit i jen tento samostatně bez předpony), příklad: SELECT * FROM USER_CATALOG; -- jen objekty vlastníka SELECT * FROM CAT; -- shodné s USER_CATALOG SELECT * FROM ALL_CATALOG; -- všechny uživateli přístupné objekty SELECT * FROM DBA_CATALOG; -- jen pro administrátory databáze

A nyní výběr několika základních pohledů systémového katalogu: CATALOG (ALL_, DBA_, USER_, (CAT)) Informace o databázových tabulkách, pohledech, synonymech a sekvencích OBJECTS (ALL_, DBA_, USER_, (OBJ)) Informace o databázových objektech TABLES (ALL_, DBA_, USER_, (TABS)) Informace o tabulkách v databázi USER_ (COLS)) TAB_COLUMNS (DBA_, ALL_, USER_, Informace o sloupcích tabulek a pohledů, jejich datových typech, atd. TAB_PRIVS (DBA_, ALL_, USER_) Informace o objektových právech přidělených uživateli nebo přidělených uživatelem (dílčí pak TAB_PRIVS_MADE a TAB_PRIVS_RECD) CONSTRAINTS (ALL_, DBA_, USER_) Informace o integritních omezeních o databáze INDEXES (ALL_, DBA_, USER_, (IND)) Informace o indexech v databázi VIEWS (ALL_, DBA_, USER_) Informace o pohledech v databázi David Žák IDAS2/1 – Systémový katalog

5

SEQUENCES (ALL_, DBA_, USER_, (SEQ)) Informace o sekvencích v databázi PROCEDURES (ALL_, DBA_, USER_) Informace o procedurách a funkcích v databázi TRIGGERS (ALL_, DBA_, USER_) Informace o triggerech v databázi SOURCE (ALL_, DBA_, USER_) Zdrojové texty uložených procedur, funkcí, specifikací a těl balíků v databázi DEPENDENCIES (ALL_, DBA_, USER_) Informace o závislostech mezi objekty v databázi USERS (ALL_, DBA_, USER_) Informace o uživatelích v databázi ROLE_PRIVS (DBA_, USER_) Informace o rolích přidělených uživateli TABLESPACES (DBA_, USER_) Informace o tabulkových prostorech databáze DATA_FILES (DBA_) Informace o datových souborech databáze LOCKS (DBA_) Informace o DLL a DML uzamčeních v databázovém serveru V$TRANSACTION Informace o aktuálně prováděných databázových transakcích


6

Tak jako jiné pohledy, i pohledy informačního schématu systémového katalogu je možné spojovat. To si můžeme ukázat na následujícím příkladu: Zjištění jištění názvů sloupců ve schématu A_HR, které jsou typu NUMBER a jsou součástí primárních klíčů tabulek. SELECT column_name, table_name, data_type FROM all_constraints nstraints JOIN all_cons_columns USING (constraint_name, owner, table_name) JOIN all_tab_cols USING (owner, table_name, column_name) WHERE owner='A_HR' AND constraint_type='P' AND data_type = 'NUMBER';

Pojmy k zapamatování Příkazy a funkce: GRANT, CREATE ROLE

REVOKE,

CREATE/ALTER/DROP

USER,

Problém: zabezpečení, práva, role, přidělování a odejímání oprávnění, využití pohledů pro zabezpečení, správa uživatelů, bezpečnostní mechanismy Oracle databáze Shrnutí V této lekci jste se seznámili see systémovým ým katalogem databáze, zejména Oracle databáze. • Systémový katalog obsahuje každá relační databáze, vlastní tabulky systémového katalogu tvoří definiční schéma, to se však mezi databázemi obvykle liší. systémového katalogu, • Mnohem standardnější jsou již pohledy nad tabulkami systémového které tvoří informační schéma. Produkty se střední a plnou podporu standardu SQL2 musí standardní názvy pohledů podporovat. • Pohledy systémového katalogu je možné (a často účelné) spojovat, aby byly nalezeny požadované informace. • Protože pohledů systémového katalogu je velice mnoho, je dobré znát název pohledu DICTIONARY, kde lze zjistit seznam pohledů. • Prefixy názvů pohledů USER_, ALL_ a DBA_ určují rozsah výstupních dat,, zda se vztahují k objektům o vlastněným uživatelem (USER_), o přístupným uživateli (ALL_) či o data o všech objektech určená pro administrátora databáze (DBA_). (DBA_) David Žák IDAS2/1 – Systémový katalog

7

Otázky na procvičení 1. K čemu slouží systémový katalog? katalog 2. Jak se liší jednotlivé databázové produkty pokud jde o strukturu systémového katalogu? 3. Čím je tvořeno informační schéma systémového katalogu? katalogu 4. Jak se liší pohledy podle prefixu USER_, ALL_ a DBA_? DBA_ 5. Jak se aktualizují data v tabulkách systémového katalogu? katalogu

Odkazy a další studijní prameny •

http://www.oracle.com/technetwork/database/enterprisehttp://www.oracle.com/technetwork/database/enterprise edition/documentation (dokumentace k databázové platformě Oracle)

Odkazy a další studijní prameny • • •

GROFF, J.R., WEINBERG, P.N. SQL - kompletní průvodce.. Praha: Computer Press, 2005. ISBN 80-251 251-0369-2. LACKO, L. Oracle – Správa, programování a použití databázového systému. Praha: Computer Press, 2007. 2007 ISBN 978-80-251-1490-2. STEPHENS, K.R., PLEW, R.R. Naučte se SQL za 21 dní. Praha: Computer Press, 2004. ISBN 80-7226 7226-870-8.


8

2. blok

Zabezpečení a ochrana dat

Studijní cíl Tento blok je věnován základům zabezpečení a ochrany dat uložených v relačních databázích, tj. uživatelským účtům, systémovým a objektovým oprávněním m a rolím. Dále jsou zde uvedeny i příklady pro uložení hesel v tabulkách.


2 - 3 hodiny

Průvodce studiem Při studiu tohoto bloku se předpokládá, že čtenář je obeznámen se s základy jazyka SQL a databázovými objekty typu tabulka a pohled. pohled

1. Zabezpečení dat V předchozích lekcích jsme se zabývali za zejména prací s daty v tabulkách a vytvářením databázových objektů. Stále více se seznamujeme s tím, že databáze není jen úložiště dat, ale ucelené řešení, řešení, které se stará nejen o jejich uložení a rychlé zpřístupňování, ale také o jejich konzistenci a to za libovolných okolností. Samozřejmostí pak musí být i řízení přístupu uživatelů k datům v databázi. Typickými požadavky jsou: • • • • • •

Data v konkrétní tabulce by měla být přístupná pouze některým uživatelům. uživatelům Někteří uživatelé mohou tato data aktualizovat, zatímco zatímco jiní je mohou pouze číst. Některým uživatelům chceme chce zpřístupnitt pouze vybrané sloupce tabulky. tabulky Některým uživatelům chceme zpřístupnit zpřístup pouze vybrané řádky tabulky. Před některými uživateli chceme skrýt skutečnou skut strukturu dat v tabulkách. Jednotlivým uživatelům chceme umožnit vytváření pouze určitých druhů databázových objektů.

Systém řízení báze dat musí být tedy odpovědný za implementaci a kontrolu bezpečnostních omezení. Pro nastavení bezpečnostních omezení jsou využívány příkazy SQL.

David Žák IDAS2/2 – Zabezpečení a ochrana dat

1

Systém zabezpečení je založen na těchto třech prvcích: •

•

•

Uživatelé – každá činnost v databázi je spojena s konkrétním uživatelem a systém umožňuje nebo zamezuje konkrétní konkré akci v závislosti na tom, který uživatel akci požaduje zové Databázové objekty – zabezpečení se vztahuje i na konkrétní databázové objekty, tedy dy například tabulky, pohledy, funkce, procedury. procedury. Zatímco uživatelé mají většinou plný přístup k objektům jimi vytvořeným, ořeným, přístupy k objektům ostatních uživatelů jsou defaultně zakázány a musí být explicitně nastaveny. Práva – akce, které může uživatel provádět na konkrétním databázovém objektu.

1.1 Účet uživatele Každý uživatel je identifikován krátkým jménem. Každý příkaz příkaz prováděný databázovým systémem se provádí jménem konkrétního uživatele a podle tohoto jména je operace povolena nebo zamítnuta. Maximální délka identifikátoru uživatele (často hovoříme o takzvaném uživatelském účtu) se v jednotlivých implementacích databázových abázových systémů liší a pohybuje se od 8 do 30 znaků. Ve standardu ANSI/ISO se namísto identifikátoru uživatele hovoří o autorizačním identifikátoru. Smyslem identifikátoru je zjistit autorizaci nebo práva v databázi. V provozní databázi se často používají používají autorizační identifikátory nikoli pro konkrétní osoby, ale pro programy nebo skupiny programů. Vlastní autorizace uživatelů pracujících s těmito programy se řeší v rámci těchto programů samotných. Při vytvoření relace (session) databázový systém vyžaduje identifikátor uživatele a heslo. Databázový systém heslo poté zkontroluje. Uživatelský účet se vytvoří příkazem CREATE USER IDENTIFIED BY ; Příklad CREATE USER

jnovak IDENTIFIED BY w25sqx;

Příkaz vytvořil v databázi uživatele s identifikátorem JNOVAK, který bude mít heslo "w25sqx".


2

Uživatel nebo administrátor databáze může změnit heslo příkazem ALTER USER. ALTER USER IDENTIFIED BY ; Příklad ALTER USER JNOVAK IDENTIFIED BY sdf45t;

Odstranění účtu uživatele z databázového systému provedeme příkazem DROP USER ; identifikator_uzivatele>;

Databáze Oracle neodstraní účet uživatele, jehož schéma obsahuje nějaké databázové objekty, pokud nedoplníte doplníte kluazuli CASCADE. V takovém případě pak je nezbytné zadat příkaz ve tvaru: DROP USER identifikator_uzivatele> CASCADE;

1.2 Skupiny uživatelů Dle standardu zabezpečení ANSI/ISO SQL můžeme pracovat se skupinami uživatelů s podobnými potřebami jedním z těchto způsobů: • •

Všem přiřadíme stejný identifikátor identi uživatele, bohužel v takovém případě případ uživatelé nemohou být odlišeni jeden od druhého ve zprávách databáze.. Každému uživateli přiřadíme samostatný identifikátor, práva pro každého uživatele zadáváme individuálně. individuálně

Jednotlivé databázové systémy nabízí další možnosti, jak zařadit jednotlivé uživatele do skupin a využít práva definovaná pro skupiny nebo role. 1.3 Práva Skupina akcí, které lze provádět na databázovém objektu, se nazývá práva pro daný objekt. Standard SQL1 specifikuje 4 základní práva pro tabulky a pohledy: • •

SELECT - umožňuje načítání dat z tabulky nebo pohledu INSERT - umožňuje vkládat nové řádky do tabulky nebo pohledu


3

• •

UPDATE - umožňuje upravovat řádky v tabulce nebo pohledu, právo UPDATE může být omezeno na konkrétní sloupce tabulky nebo pohledu DELETE - umožňuje odstraňovat řádky tabulky nebo pohledu

Standard SQL2 přidal: • • •

•

právo REFERENCES, které umožňuje vytvářet odkazy na tabulku z cizího klíče v jiné tabulce, dále přidal právo USAGE, které řídí přístup k databázovým strukturám domén, znakových sad, způsobům řazení a překladů u práva INSERT je možné omezit sloupce, které je možné zadávat, ostatní sloupce u nově zadaného řádku budou mít hodnotu NULL, případně defaultní hodnotu dle definice tabulky u práva SELECT připouští (nicméně nedefinuje) možnost omezení na vybrané sloupce, proto to některé databázové systémy mají implementováno

Vlastník tabulky má automaticky práva SELECT, INSERT, UPDATE a DELETE (a případná další práva poskytovaná použitou databází) nad touto tabulkou. Ostatní uživatelé obvykle nemají k této tabulce žádná práva a v případě potřeby jsou jim práva explicitně přidělena pomocí příkazu GRANT. U vytvořeného pohledu má vlastník pohledu automaticky právo SELECT, které vychází z toho, že aby pohled mohl vytvořit, musí mít právo SELECT na všechny zdrojové tabulky pohledu. U ostatních práv (INSERT, UPDATE a DELETE) obdržíte právo pouze v případě, že stejné právo máte na všechny zdrojové tabulky pohledu. Jak již bylo řečeno, jednotlivé databázové systémy mohou nabízet další práva k databázovým objektům. Například Oracle nabízí k tabulkám právo ALTER a INDEX. Jejich smysl je zřejmý z názvů těchto práv. Pro procedury a funkce se používají práva EXECUTE a DEBUG. 1.4 Pohledy Dalším klíčovým prvkem při řízení přístupu uživatelů k datům jsou pohledy. Správnou definicí pohledů a přiřazením práv uživatelů k pohledům a nikoli k jejich zdrojovým tabulkám můžeme omezit přístup uživatele pouze k vybraným sloupcům a řádkům. Pohledy tedy nabízí velmi přesnou kontrolu nad tím, co může vidět konkrétní uživatel. Při bližším přemýšlení je možné dokonce filtrovat zdrojová data na základě identity uživatele, neboli různí uživatelé mohou vidět různý obsah pohledu. David Žák IDAS2/2 – Zabezpečení a ochrana dat

4

Kromě objektových práv SELECT, INSERT, UPDATE a DELETE nabízí pohledy ještě další omezeníí pro aktualizace dat. Kromě varianty READ ONLY pohledů jde o to, že pomocí klauzule WITH CHECK OPTION mohu vyžadovat, aby aktualizované a vkládané řádky splnily určitou podmínku. Bližší informace o pohledech je možné najít v jedné z předešlých lekcí. 1.5 Přidělování práv ování práv slouží příkaz GRANT. Obecnou syntaxi příkazu GRANT nad Pro přidělování databázovými objekty můžeme zapsat takto: GRANT <seznam_práv seznam_práv> ON TO identifikatory_uzivatelů> [WITH WITH GRANT OPTION]; Příklad GRANT SELECT, INSERT ON PRODUKTY TO JNOVAK; GRANT UPDATE(NAZEV, DODAVATEL_ID) ON PRODUKTY TO KSVOBODA; První příkaz slouží k předání práv SELECT a INSERT tabulky PRODUKTY uživateli JNOVAK, druhý příklad předává práva práv pro aktualizaci sloupců NAZEV a DODAVATEL_ID uživateli KSVOBODA. Klauzule WITH GRANT OPTION umožňuje předat uživateli také právo přidělovat dalším uživatelům ta práva, která sám k danému databázovému objektu má přidělena. Pokud chceme určitá práva předat všem uživatelům, uvedeme namísto identifikátoru uživatele klíčové slovo PUBLIC. Příklad GRANT SELECT ON DODAVATELE TO PUBLIC;


5

1.6 Odebírání práv Pro odebírání již přidělených práv slouží příkaz REVOKE. Obecná syntaxe příkazu REVOKE nad databázovými objekty je velice podobná příkazu GRANT a můžeme ji zapsat takto: REVOKE <seznam_práv seznam_práv> ON FROM ; identifikatory_uzivatelů>; Příklad pro odebrání dříve přiděleného práva uživateli JNOVAK: REVOKE INSERT ON PRODUKTY FROM JNOVAK; Příklad pro odebrání všech práv k tabulce DODAVATELE všem uživatelům: REVOKE ALL PRIVILEGES ON DODAVATELE FROM PUBLIC; 1.7 Systémová oprávnění V předchozích kapitolách jsme hovořili o objektových oprávněních. Kromě objektových oprávnění existují ještě systémová oprávnění, která definují činnosti, které daný uživatel může v databázi dělat. Databázový administrátor například musí mít tato systémová oprávnění – vytváření a rušení uživatelů, přidělování systémových oprávnění těmto uživatelům, odstraňování tabulek v libovolných schématech a podobně. Například v databázích Oracle jsou desítky systémových oprávnění, která je možné uživatelům přidělovat. Systémová oprávnění přidělujeme obdobně jako oprávnění k databázovým objektům pomocí příkazu GRANT a odebíráme pomocí příkazu REVOKE. GRANT <seznam_oprávnění seznam_oprávnění> TO identifikatory_uzivatelů> [WITH GRANT OPTION]; Příklad GRANT CREATE TABLE, CREATE SEQUENCE, CREATE VIEW TO JNOVAK, KSVOBODA; KSVOBODA REVOKE CREATE SEQUENCE FROM KSVOBODA; David Žák IDAS2/2 – Zabezpečení a ochrana dat

6

1.8 Role V mnoha případech potřebujeme více uživatelům poskytnout stejná objektová, případně i systémová práva. Role umožňují sdružovat uživatele do skupin. Mezi uživateli a rolemi je vztah M:N, tedy jeden uživatel může mít více rolí a jedna role může být přiřazena azena více uživatelům. Příklad CREATE ROLE REFERENT; REFERENT GRANT CREATE TABLE, CREATE VIEW TO REFERENT; GRANT SELECT ON DODAVATELE TO REFERENT; GRANT SELECT ON PRODUKTY TO REFERENT; GRANT REFERENT TO AMALA, BSTASTNA; BSTASTNA

V databázi Oracle jsou vytvořeny implicitně role CONNECT pro připojení k databázi a RESOURCE pro vytváření základních databázových objektů. Každý uživatel, který se může k databázi připojit, musí mít minimálně roli CONNECT. Tyto role je třeba po vytvoření nového uživatele tomuto uživateli přiřadit. 1.9 Schéma Schéma je množina databázových objektů, které spolu souvisejí. Databázovými objekty mohou být tabulky, pohledy, indexy, clustery, sekvence, synonyma, uložené procedury, uložené triggery, databázové linky, snapshoty (používané pro repliky vzdálených tabulek). Při vytvoření uživatele správcem se implicitně vytvoří databázové schéma uživatele v definovaném tabulkovém prostoru. Ve svém databázovém schématu vlastní uživatel všechny objekty.


7

2. Bezpečnostní mechanismy Oracle Databáze Oracle je robustní architektura architektura používaná bankami, pojišťovnami, velkými společnostmi a jinými významnými klienty. Proto je problematika problematika bezpečnosti dat jedním ze základních cílů vývojářů a vznikají různé nástroje na na usnadnění správy bezpečnostních pravidel, které rozšiřují vlastnosti implementované dle standardu SQL. Z uplatněných a principů můžeme uvést například: • • • • • • • • •

Zajištění principu minimálních nutných práv Mechanismus řízení přístupu pomocí systémových i objektových práv Řízení přístupu na úrovni záznamů Transparentní šifrování dat Šifrování záloh Šifrování komunikace Detailní audit operací Centrální správa uživatelů - spolupráce s LDAP Bezpečnostní certifikace

V následujících odstavcích budou krátce charakterizovány některé mechanismy, které slouží ke zvýšení bezpečnosti uložených dat da a usnadnění řízení přístupu. 2.1 Mechanismus Secure Application Role • Uživatel živatel může určité operace provádět, jen pokud přistupuje z dané aplikace a nikoliv, pokud k přístupu použije třeba databázovou konzoli. • Tyto role ole jsou pevně svázány s určitým databázovým balíčkem (knihovnou uložených procedur) a mohou být aktivovány pouze z tohoto toho balíčku. 2.2 Mechanismus Virtual Private Database (VPD) • VPD zajišťuje automatické a transparentní doplnění jakéhokoliv dotazu o bezpečnostní podmínku. Ta pak omezuje data, se kterými uživatel může pracovat. I když tedy dva uživatelé zadají stejný dotaz (například výpis celé tabulky), může každý získat jiná data. data • Např. uživatelé z různých oddělení mohou pracovat pouze se svými daty, i když jsou data celého podniku ve stejné st tabulce.


8

2.3 Mechanismus Oracle Label Security Option • Každý záznam je označen štítkem určujícím jeho citlivost. Každý uživatel má na druhé straně definovány bezpečnostní úrovně, se kterými může pracovat. • Databáze Oracle pak zajistí, že uživatel může pracovat pouze s těmi daty, jejichž citlivost odpovídá jeho úrovni. 2.4 Mechanismus Oracle Advanced Security Option (ASO) • V rámci ASO je možné využít pokročilé metody ověřování, jako jsou třeba klientské SSL certifikáty, nebo využití různých autentizačních služeb postavených na standardech Kerberos či RADIUS. • Pomocí těchto služeb lze například zajistit ověřování pomocí různých elektronických karet nebo biometrických údajů. 2.5 Mechanismus Proxy Authentication • Problém s identifikací uživatelů však může nastat u webových aplikací, kdy se aplikace do databáze přihlašuje stále pod stejným jménem a heslem. Databáze totiž neví, kdo s ním skutečně pracuje, což blokuje řadu jejích bezpečnostních funkcí. • Mechanismus Proxy Authentication aplikaci sice umožní přistupovat do databáze stále pod stejným jménem a heslem (a ušetřit tak čas na vytvoření spojení), avšak zároveň dovoluje předat informace o skutečném uživateli, který s aplikací pracuje. • Databázový systém pak tuto informaci využije při řízení přístupu i auditování. 2.6 Mechanismus Advanced Security Option pro šifrování • Šifrování přenosu dat má za cíl zabránit odposlechnutí či dokonce pozměnění komunikace. Šifrování komunikace je přitom transparentní pro samotnou aplikaci – neznamená nutnost zásahu do kódu aplikace. Kromě SSL existují i další techniky. • Nástroje pro výběrové programové šifrování a dešifrování dat pomocí standardních algoritmů (DES, 3DES, AES a MD5) umožňují zašifrovat obzvláště citlivá data (rodná čísla, čísla kreditních karet). • Možnost šifrování dat v tabulkách, které je transparentní z pohledu aplikace, nastavuje se deklarativně při definici tabulky. Tento mechanismus chrání před unikem dat při přístupu obcházejícím databázový server – např. kopírováním datových souborů, nebo zcizením záložních médií.


9

2.7 Mechanismus Fine Grained Auditing (FGA) • Nedílnou součástí bezpečnostních mechanismů je i možnost auditu operací prováděných jednotlivými uživateli. • Základní auditovací mechanismy většinou vytvářejí velký objem záznamů – nepřehledné, těžce zpracovatelné – proto Oracle vyvinul FGA. • FGA umožňuje detailně určit, jakých dat se má operace týkat, specifikací sloupce tabulky a podmínky. Pouze pokud se v rámci uživatelem prováděné operace začne pracovat se záznamem splňujícím podmínku a je vrácena informace z definovaného sloupce, je tato operace zapsána do auditovacího logu. Při takové události je možné spustit i uloženou proceduru, která zajistí patřičnou reakci – například zašle SMS správci databáze. 2.8 Mechanismus Oracle Internet Directory (OID) • Standardní správa uživatelů vyžaduje aktualizace uživatelů v mnoha systémech při odchodech či nástupech zaměstnanců do zaměstnání a změnách funkcí zaměstnanců. Častým jevem je neaktuálnost uživatelských účtů a nezrušení jejich účtů v systémech. • Řešením je centrální správa uživatelů na jednom místě, ze kterého si jednotlivé aplikace přebírají data pomocí standardu LDAP. V prostředí Oracle funkci tohoto centrálního prvku plní OID. • Centrální správu uživatelů v OID lze samozřejmě využít i pro správu databázových uživatelů a rolí. Tato funkcionalita je označena jako Enterprise User Security. Databáze může přebírat nejen seznam uživatelů, ale může z OID získat i seznam rolí přiřazených uživatelům. 3. Správa uživatelů V mnoha společnostech se stále setkáváme se správou uživatelů individuálně v jednotlivých aplikacích. Tento tradiční přístup však kromě vysokých nároků na správu, kdy je nezbytné udržovat uživatelské účty a role v několika systémech, hrozí neaktuálností, nekonzistencí, pomalými reakcemi na změny pracovního zařazení a potřeb zaměstnanců, přístupová oprávnění jsou špatně kontrolovatelná a hlavně hrozí ponechání přístupu neoprávněným osobám. Současným trendem je proto centrální evidence uživatelů ideálně všech informačních systémů společnosti na jediném místě, například formou LDAP služeb. Takové řešení je snáze spravovatelné, přehlednější, jednoduše zařaditelné do procesu přijímání a propouštění pracovníků, což přináší i rychlejší reakce na požadované změny.


10

Pojmy k zapamatování Příkazy a funkce: GRANT, CREATE ROLE

REVOKE,

CREATE/ALTER/DROP CREATE/ALTER/DROP

USER,

Problém: zabezpečení, práva, role, přidělování a odejímání oprávnění, využití pohledů pro zabezpečení, správa uživatelů, bezpečnostní mechanismy Oracle databáze

Shrnutí V této lekci jste se seznámili see zabezpečením a ochranou dat v databázích. • Bezpečnost dat v SQL databázích je založena na právech, objektových a systémových oprávněních. • Pro dosažení zabezpečení je možno využít projekce a restrikce dat zajišťované pohledy. • Pro ro přidělování práv slouží příkaz GRANT. • Příkaz az REVOKE slouží pro odebírání dříve přidělených práv. • Při přidělování práv je vhodné se řídit principem minimálních nutných objektových i systémových práv, která uživatel potřebuje pro svoji práci. • Správu uživatelů ve společnosti je vhodné realizovat centrálně. cen Otázky na procvičení 1. 2. 3. 4. 5.

K čemu slouží příkazy GRANT a REVOKE? Která práva se pojí s databázovým objektem tabulka? tabulka Vyjmenujte příklady systémových práv? práv K čemu slouží role? K čemu slouží klíčové slovo PUBLIC? PUBLIC


http://www.oracle.com/technetwork/database/enterprisehttp://www.oracle.com/technetwork/database/enterprise edition/documentation (dokumentace k databázové platformě Oracle) http://www.penguin.cz/noviny/?id=chip/index (seriál Databáze standardu SQL z časopisu CHIP) http://www.techonthenet.com/oracle/grant_revoke.php (Oracle/PLSQL: Oracle/PLSQL: Grant/Revoke příkazy)


11


GROFF, J.R., WEINBERG, P.N. SQL - kompletní průvodce.. Praha: Computer Press, 2005. ISBN 80-251 251-0369-2. LACKO, L. Oracle – Správa, programování a použití databázového systému. Praha: Computer Press, 2007. 2007 ISBN 978-80-251-1490-2. STEPHENS, K.R., PLEW, R.R. Naučte se SQL za 21 dní. Praha: Computer Press, 2004. ISBN 80-7226 7226-870-8.


12

3. blok

Transakce

Studijní cíl Tento blok je věnován základům řízení transakcí, které jsou nezbytné pro zachování konzistentního stavu databáze při aktualizacích dat v tabulkách. 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 (SELECT, INSERT, UPDATE, DELETE) jazyka SQL. 1. Zpracování transakcí Pro pochopení pojmu transakce se vysloveně nabízí příklad z bankovního světa, tedy finanční transakce. Představte si, že chcete převést částku na půlroční pobyt na vysokoškolských kolejích na účet správy kolejí. Podáte příkaz k úhradě z vašeho účtu a předpokládáte, že částka bude následující pracovní den po jejím stržení z vašeho účtu připsána na účet příjemce. Nejprve je tedy částka odepsána z účtu plátce a následně připsána na účet příjemce. Co se ale stane v případech, případech kdy: • • • •

příjemce svůj účet zrušil, omylem jste uvedli neexistující číslo účtu, účtu na účtu plátce není dostatek finančních prostředků, na účtu plátce je exekuce. exekuce

V takovém případě se buď částka vůbec neodepíše, nebo se částka odepíše z účtu plátce, ale není možné ji připsat na účet příjemce. Obdobná situace nastane, když při převodu dojde k selhání lhání techniky. Je tedy nezbytné takovou transakci stornovat a připsat odepsanou částku zpět na účet plátce. Transakce tedy buď proběhne jako celek (v našem příkladu se peníze pomocí několika elementárních operací převedou z účtu plátce na účet příjemce) nebo se jako celek odvolá. V systémech řízení báze dat transakce představují skupinu operací, které je nezbytné úspěšně provést jako celek ve správném logickém pořadí jednotlivých elementárních operací nebo neprovést vůbec. V případě neprovedení musí výsledný dný David Žák IDAS2/3 – Transakce

1

stav databáze odpovídat stavu před provedením první operace zařazené do transakce. Hovoříme zde o tom, že výchozí a závěrečných stav databáze před a po provedení či odvolání transakce jsou konzistentními stavy. Celý princip transakce je důležitý pro programy, které aktualizují databázi, neboť zajišťuje integritu dat. Databáze je odpovědná za dodržování principu vše nebo nic i v případě, kdy dojde k technickému selhání systému, například hardwarové poruše či výpadku dku napájení. Při zotavení z tohoto selhání systému jsou nepotvrzené transakce považovány za nedokončené a provedené změny jsou odrolovány zpět. Příkladem z databázového prostředí může být například skupina DML operací, kdy se změny musí realizovat v několika několika tabulkách nebo pomocí posloupnosti několika elementárních operací. Bohužel se často zapomíná na to, že dle modelu ANSI/ISO i jedna operace INSERT, UPDATE či DELETE provedená uživatelem nebo programem automaticky zahájí transakci a transakci je nezbytné některým z dále uvedených způsobů ukončit. V různých databázových systémech se mohou drobně lišit způsoby pro řízení transakcí. Po zahájení transakce je jí přiřazen dostupný návratový tabulkový prostor (undo undo tablespace),, kam se ukládají staré hodnoty dat, dat které byly SQL příkazem kazem v rámci transakce změněny a které mohou být využity pro případný ROLLBACK transakce a pro operaci konzistentního čtení, která je popsána níže v tomto dokumentu. 2. Ukončení transakcí Transakce je ukončena buď: • • • • •

jejím potvrzením příkazem kazem COMMIT, vrácením zpět/odvoláním transakce příkazem ROLLBACK, uživatel řádně ukončí spojení s databázovým serverem (transakce je potvrzena automaticky),, spojení pojení uživatele se serverem se ukončí abnormálně – transakce se odroluje zpět automaticky, explicitní potvrzení transakce, transakce zadáním příkazu DDL (CREATE, DROP, RENAME, ALTER), před provedením příkazu DDL se automaticky ukončí předchozí transakce,, v samostatné transakci se pak provede příkaz DDL.

David Žák IDAS2/3 – Transakce

2

Stav databáze před transakcí

SELECT

SELECT

SELECT Odrolování transakce

transakce

UPDATE

INSERT

Odrolování transakce

UPDATE

SELECT Odrolování transakce

UPDATE

INSERT

UPDATE

INSERT Selhání HW

DELETE

ROLLBACK Chyba programu

COMMIT

Stav databáze po transakci

Obrázek 1: Princip transakce v SQL.

Změny provedené příkazy obsaženými v transakci jsou viditelné pro ostatní uživatele a jiná spojení (sessions) uživatele provádějícího transakci až od okamžiku potvrzení transakce. Výsledky elementárních operací v rámci rozpracované transakce je možné zjistit pouze v session, v níž transakce probíhá. Ve všech ostatních session je tedy možné vidět konzistentní stavy související s potvrzenými transakcemi. 3. Základní vlastnosti transakcí - ACID •

• •

•

Atomicity (Atomičnost)) - Atomičnost transakce znamená, že buď je provedena celá transakce, nebo žádná z databázových operací, které ji tvoří. Consistency (Konzistence Konzistence) - Konzistence transakce znamená, že izolovaná transakce zachovává konzistenci databáze. Isolation (Izolace) - Izolace transakce znamená, že i při souběžném běhu transakcí SŘBD zajistí, že pro každou dvojici souběžných transakcí Ti a Tj se Ti jeví, že Tj skončila dříve, než Ti zahájila provádění nebo Tj zahájila provádění až poté, co Ti skončila. Durability (Trvalost) - Trvalost transakce znamená, že poté, co transakce úspěšně skončí, budou mít všechny změny v databázi, které transakce provedla, trvalý charakter a to i při výpadku systému.


3

4. Odrolování na úrovni příkazu Pokud během provádění SQL příkazu nastane nějaká chyba, všechny změny provedené příkazem jsou odrolovány zpět. Toto se nazývá Statement-Level Rollback (odrolování na úrovni příkazu). Není totiž možné připustit stav, kdy by příkaz ovlivnil jen ty řádky tabulky, než nastala chyba a poté by se jeho činnost přerušila. Takový stav by byl vysoce rizikový, neboť by nebyla zajištěna konzistence dat. Příklady takových chyb: • • •

Pokus o vložení řádku s duplicitní hodnotou primárního klíče, Narušení referenční integrity, Deadlock (pokus o současnou změnu shodných dat transakcemi).

dvěma

Syntaktická chyba při parsování neumožní spustit provádění příkazu, proto se nejedná o odrolování na úrovni příkazu. Chyba při provádění příkazu příkazy v rámci dané transakce.

neovlivní

změny

provedené

předchozími

5. Návratové body Návratové (někdy v literatuře nazývané jako „úložné“) body umožňují označit další místa mezi jednotlivými elementárními SQL příkazy, které jsou součástí transakce. Představme si situaci, kdy v rámci jednotlivých příkazů pracujeme s velkým množstvím záznamů, jejichž zpracování trvá určitou dobu. Pokud v určitý okamžik dojde k potřebě odrolovat probíhající transakci, nemusíme provést odrolování transakce až na její počátek, ale jen k danému návratovému bodu. Veškeré změny provedené transakcí po návratovém bodu jsou tedy odrolovány, ale změny před návratovým bodem jsou ponechány a je tedy možno na tento rozpracovaný stav transakce navázat. Příkazy: •

SAVEPOINT - slouží pro označení místa (například po provedení některých operací), kam bychom se v případě potřeby mohli navrátit, slouží pro rozdělení velké transakce na menší části

•

ROLLBACK TO – odrolování transakce do bodu návratu


4

Příklad: SAVEPOINT plosne_zvyseni_mzdy; UPDATE pracovnici SET mzda=mzda*1.04; SAVEPOINT navyseni_manazeri; UPDATE pracovnici SET mzda=mzda+1000 WHERE pozice LIKE ‘manažer’; ROLLBACK TO navyseni_manazeri;

Po odrolování transakce k návratovému bodu: • • •

•

jsou vráceny pouze změny provedené SQL příkazy po nastavení návratového bodu, všechny návratové body nastavené po daném návratovém bodu jsou ztraceny, Oracle uvolní všechny uzamčené tabulky a řádky, které byly uzamčeny po nastavení návratového bodu (ty nastavené dříve zůstanou zachovány), Transakce zůstává aktivní a může dále pokračovat. pokračovat

6. Konzistentní čtení Představme si situaci, kdy probíhá SELECT, jehož vyhodnocení trvá několik hodin (může jít například o měsíční uzávěrku) a mezitím někteří uživatelé provedou určité změny dat a tyto potvrdí příkazem COMMIT. To znamená, že tyto jimi provedené změnyy jsou přístupné a viditelné i dalším uživatelům. Do SQL dotazu, který byl zahájen před potvrzením změn provedených jinými uživateli, však tyto změny nebudou zahrnuty. Výsledek tedy bude takový, že: • •

ve výsledku dříve spuštěného dotazu nebudou potvrzené změny ěny během doby zpracování dotazu zahrnuty, ostatní uživatelé budou normálně pracovat s aktuální verzí dat (tedy po potvrzení změn).

Obrázek ukazuje příklad pro čtení dat z tabulky. Příkaz SELECT je spuštěn v okamžiku SCN (System Change Number) N = 10023. Pokud se při čtení narazí na bloky dat změněné po tomto „čase“ (SCN je vyšší, vyšší neboť se s každou potvrzenou transakcí zvyšuje o 1), ), budou pro výsledek dotazu vstupní data rekonstruována s využitím informací uložených v návratovém tabulkovém prostoru. Tím je zajištěna konzistence vstupních dat pro zpracování dotazu a platnost výsledku.


5

Obrázek 2: Princip konzistentního čtení. Zdroj: neznámý.

Oracle vždy zajišťuje konzistenci pro čtení na úrovni dotazu. Ani potvrzení jiné transakce příkazem COMMIT během zpracování dotazu neovlivní jeho výsledek – vždy je důležitý stav při zahájení dotazu. Tato konzistence je zajištěna automaticky bez účasti uživatele. Vztahuje se i na vnořené dotazy. Toto ovšem také znamená, že daný dotaz nevidí změny vyvolané jím samotným, ale vždy pracuje s daty ve stavu při jeho spuštění! Poznámka: Problém by mohl nastat, pokud by příkaz SELECT spouštěl funkci, která by opět obsahovala jiný SELECT (ta by ovšem měla jiné SCN začátku) a uvažovala by již změněná data. 7. Nezapomínejte na potvrzování transakcí Doporučuje se, aby transakce obsahovala jen tolik operací, které nezbytně potřebuje. Pokud nedochází k potvrzování transakcí, výrazně se zpomaluje práce databázového serveru, neboť musí být do transakčního logu odkládáno spousta změn a tyto se mohou z transakčního žurnálu odstranit až po dokončení (potvrzení či odrolování) transakce. Také je dobré si uvědomit, že například pro smazání dat z databáze o velikosti 10 GB potřebujeme 3-4 GB pro uložení transakčního žurnálu. Databázový server se totiž musí pojistit pro případ, kdyby se něco nepovedlo. Paradoxně to může vést k hlášce David Žák IDAS2/3 – Transakce

6

„Data není možné vymazat, protože na disku není dostatek volného prostoru“. Řešením by pak bylo odmazávání dat po menších blocích s průběžným potvrzováním příkazem COMMIT. Druhou variantou by mohlo být použití příkazu TRUNCATE. Zatímco DELETE vymaže řádky po jednom a do transakčního logu se zaznamená každé jednotlivé vymazání samostatně, TRUNCATE TABLE vymaže data dealokací (uvolněním) datových stránek použitých pro tabulku a do transakčního logu jsou zaznamenány jen tyto dealokace. Některé databáze podporují volbu AUTOCOMMIT. v příkazech:

Tuto volbu lze používat

SET AUTOCOMMIT ON;

-- potvrzuje všechny příkazy jako transakce

SET AUTOCOMMIT OFF;

-- příkazy nejsou implicitně potvrzovány, nutno používat příkazy pro řízení transakcí

8. Transakce a víceuživatelské zpracování Pokud souběžně přistupuje do databáze více uživatelů, databáze musí zajistit, aby se akce uživatele nemíchaly s akcemi jiného uživatele. Ideálním případem by bylo, kdyby k ní měl každý uživatel exklusivní přístup bez starání se o akce jiných uživatelů. Odborně se tato problematika nazývá izolací uživatelů a model transakcí SQL definuje několik izolačních úrovní. Je nutné si uvědomit, že dochází k ovlivňování probíhajících transakcí potvrzenými změnami dokončených transakcí. Míra ovlivňování je dána izolační úrovní, v níž transakce pracuje. V některých případech může konflikt s jinou transakcí způsobit zrušení transakce, i když v ní nebyla chyba. Aplikační program musí být připraven tuto situaci obsloužit. V následujících odstavcích se podívejme na rizika při zpracování souběžných transakcí. Obecně mohou nastat tyto 4 problémy: • • • •

problém ztracené aktualizace, problém nepotvrzených dat, problém neopakovatelného čtení, problém přízračných dat.

8.1. Problém ztracené aktualizace • Dva uživatelé se v současně rozpracovaných transakcích pokouší aktualizovat stejný záznam (záznamy). Pokud již jedna transakce tato aktualizovaná data potvrdí (příkazem COMMIT), došlo by při potvrzení druhé transakce ke ztrátě dat aktualizovaných předchozí transakcí.


7

• Pokud však aspoň jedna z těchto transakcí odrolovala změny příkazem ROLLBACK, k problému by nedošlo. Příklad: Na obrázku níže hrozí uváznutí. Obě transakce transakce se snaží aktualizovat data dříve aktualizovaná jinou současně probíhající transakcí. Transakce 1

Transakce 2

UPDATE PRODUKTY SET NAZEV=’V1234’ WHERE ID=11;

čas

UPDATE PRODUKTY SET NAZEV=’M17X Base’ WHERE ID=22;

UPDATE PRODUKTY SET NAZEV=’M17x’ WHERE ID=22;

UPDATE PRODUKTY SET NAZEV=’Vostro 1234’ WHERE ID=11;

CHYBA: ORA-00060: Deadlock detected while waiting for resource.

Obrázek 3:: Problém ztracené aktualizace. Zdroj: neznámý.

Databáze automaticky detekuje uvíznutí (deadlocky) tak, že periodicky každých například 5 sekund kontroluje zámky, které jsou v držení více transakcemi. Zjistí-li Zjistí uvíznutí, řeší ší je tím, že odroluje zpět jednu jed z příkazů, který deadlock způsobil. Transakce dostává zprávu, že proběhl rollback na úrovni příkazu. Transakce se poté může odrolovat drolovat zpět explicitně nebo se pokusí odrolovaný příkaz znovu provést. 8.2. Problém nepotvrzených dat (Dirty ( read) • Transakce může číst data, která byla zapsána jinou transakcí, ale ještě nedošlo k jejich potvrzení. • V tabulce mohou být v aktuální chvíli jakákoliv data. Není tedy zaručena úplnost dat. V databázích Oracle je Dirty read nemožné. • Příklad: Transakce 1 provede dvakrát stejný příkaz SELECT. Mezi těmito dvěma příkazy ale Transakce 2 změní řádek zobrazovaný Transakcí 1 bez potvrzení transakce. Transakce Transakce 1 při druhém SELECTu přečte nepotvrzená data a poté co bude Transakce 2 odrolována zpět, tak mohou být tato podruhé zobrazená data špatná.


8

Transakce 1

Transakce 2

SELECT * FROM PRODUKTY WHERE ID=123;


čas

UPDATE PRODUKTY SET NAZEV=’Vostro1320’ WHERE ID=123;

ROLLBACK;

Obrázek 4: Problém nepotvrzených dat.

8.3. Problém neopakovatelného atelného čtení (Nonrepeatable read / Fuzzy read) • Transakce čte opakovaně stejné hodnoty, ale dostává různé výsledky. Ty jsou ovlivněny potvrzenými změnami v současně běžících jiných transakcích. Příklad: Transakce 1 se skládá ze dvou příkazů SELECT. V mezičase mezičase mezi dvěma příkazy Transakce 2 změní řádek a příkazem COMMIT se ukončí. Transakce 1 při obou vykonávaných příkazech čte potvrzená data, ale i přesto se tato data při běhu jedné transakce liší. Nezmění se počet řádků na výstupu, ale změní se jejich obsah. ah. Nastalo tedy tzv. Nonrepeatable read neboli neopakovatelné čtení. Transakce 1

Transakce 2


čas

UPDATE PRODUKTY SET NAZEV=’Inspiron 15R’ WHERE ID=123; COMMIT; SELECT * FROM PRODUKTY WHERE ID=123;

Obrázek 5:: Problém neopakovatelného čtení. čtení


9

8.4. Problém přízračných dat (Phantom Phantom read) read • Když uživatel provede čtení v čase t1 a v čase t2 provede stejný příkaz, do databáze mohou být přidány v mezidobí další řádky jinou současně běžící potvrzenou transakcí a ty mohou ovlivnit výsledek. • Od problému neopakovatelného čtení se problém přízračných dat liší v tom, že data, která uživatel právě čte, čte nejsou změněna, ale vee výsledku v čase t2 je více dat. Příklad: Transakce 1 je opět složena příkazů SELECT. Současně s Transakcí 1 nyní Transakce 2 provede vložení řádku a následné potvrzení transakce. Oba dva příkazy SELECT v Transakci 1 budou vracet na výstupu potvrzená data, ale počet řádků se od sebe bude lišit. Objeví se tzv. zjevení – přízračná data. Transakce 1

Transakce 2

SELECT * FROM PRODUKTY WHERE ID > 100;

čas

INSERT INTO PRODUKTY (ID, NAZEV) VALUES (131,’XPS L702x’); COMMIT; SELECT * FROM PRODUKTY WHERE ID=123;

Obrázek 6: Problém přízračných dat.

8.5. Izolační úrovně transakcí Standard SQL2 definuje příkaz SET TRANSACTION , který slouží k nastavení izolačních úrovní transakcí. Čím vyšší izolační úroveň, tím vyšší jsou systémové požadavky na databázový server a tím více hrozí vzájemné blokování transakcí. Následující tabulka popisuje, které problémy se mohou vyskytnout v jednotlivých tlivých izolačních úrovních.


10

Izolační úroveň

Ztracená aktualizace

READ UNCOMMITTED READ COMMITTED REPEATABLE READ SERIALIZABLE

Nepotvrzená data

Neopakovatelné čtení

Přízračná data

NE

ANO

ANO

ANO

NE

NE

ANO

ANO

NE

NE

NE

ANO

NE

NE

NE

NE

Tabulka 1 - Izolační úrovně transakcí.

Pojmy k zapamatování Příkazy a funkce: COMMIT, ROLLBACK TO SAVEPOINT

ROLLBACK,

SET

SAVEPOINT,

Problém: transakce, ACID, explicitní a implicitní potvrzení transakce, explicitní a implicitní odrolování transakce, problém ztracené aktualizace, problém nepotvrzených dat, problém nekonzistentních dat, problém přízračných dat

Shrnutí V této lekci jste se seznámili s řízením transakcí jako se základním principem pro zajištění konzistence dat. • Transakce jsou logickou jednotkou práce v databázi, která se skládá z několika elementárních operací – příkazů SQL. • Příkaz COMMIT potvrzuje provedenou transakci a činí změny v databázi trvalými. • Příkaz ROLLBACK odroluje změny doposud provedené transakcí do výchozího stavu před započetím transakce. transakc • V případě selhání systému v databázi zůstanou po zotavení pouze změny, které byly v čase selhání potvrzeny. • Transakce umožňují souběžný přístup více uživatelů či aplikací k databázi. Je však nutné si uvědomit, že i tak dochází k ovlivňování transakcí potvrzenými tvrzenými změnami souběžně probíhajících transakcí. V některých případech může konflikt s jinou transakcí způsobit zrušení transakce, i když v ní nebyla chyba.


11

Otázky na procvičení ROLLBACK 1. K čemu slouží příkazy COMMIT a ROLLBACK? 2. Co se stane s rozpracovanou transakcí při ukončení spojení s databázovým serverem? 3. Jaké riziko představuje práce bez používání příkazů pro řízení transakcí? transakcí 4. Popište problémy, jak se mohou ovlivňovat transakce při souběžné práci více uživatelů? 5. Co je to „deadlock“ a kdy k němu může dojít? do


http://www.root.cz/clanky/transakce-a-izolace-transakci-v-databazich/ http://www.root.cz/clanky/transakce (Transakce Transakce a izolace transakcí v databázích) databázích 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 //www.penguin.cz/noviny/?id=chip/index (seriál Databáze standardu SQL z časopisu CHIP)


GROFF, J.R., WEINBERG, P.N. SQL - kompletní průvodce.. Praha: Computer Press, 2005. ISBN 80-251 251-0369-2. STEPHENS, K.R., PLEW, R.R. Naučte se SQL za 21 dní. Praha: Computer Press, 2004. ISBN 80-7226 7226-870-8. LACKO, L. Oracle – Správa, programování a použití databázového systému. Praha: Computer Press, 2007. 2007 ISBN 978-80-251-1490-2.


12

4. lekce

Přístup řístup k databázi z vyššího programovacího jazyka

Studijní cíl Tento blok popisuje základní principy princip přístupu k databázi z vyššího programovacího jazyka.


2 - 3 hodiny

Průvodce studiem Při studiu tohoto bloku se předpokládá, že čtenář je obeznámen se základy SQL a běžnými pojmy z oblasti výpočetní techniky a její historie. historie 1. Úvod K přístupu k databázi je možné využít níže popsaná rozhraní. Výběr Výběr závisí na potřebách konkrétní aplikace - požadavcích na výkon, použité technologii, databázové platformě, dostupnosti ovladačů i konkrétní implementaci. Na nejnižší úrovni lze pro komunikaci se systémem pro řízení báze dat (SŘBD) používat jeho nativní rozhraní. Je specifické pro každou databázovou platformu a připojuje se v podobě dynamických knihoven. Využívá se výjimečně v nízkoúrovňových systémech extrémně náročných na rychlost. 2. Rozhraní pro přístup k databázi Efektivnější je využití některého ho níže popsaného databázového rozhraní.. Ta lze v základu rozdělit do dvou linií: a) obecné b) nativní (přímé) Obecná rozhraní jsou nezávislá na použité databázové platformě. Poskytují omezenou množinu služeb. Umožňují relativně jednoduchou změnu databázové platformy i současné využití více typů SŘBD v rámci jedné aplikace. Příkladem mohou být ODBC (JDBC) ovladače.

Jiří Lebduška, David Žák IDAS2/4 – Přístup k databázi z vyššího programovacího jazyka

1

Přímá rozhraní jsou závislá na konkrétní databázové platformě (někdy i verzi) a mnohdy také klientské tské straně (vývojovém nástroji). Výhodou je možnost využití všech dostupných funkcí databázového stroje a jeho výkonu. Příkladem může být OCI (Oracle Call Interface) ovladač. 2.1 ODBC (Open Database Connectivity) Představuje aplikační rozhraní pro přístup k datům v relační databázi nezávisle na databázové platformě (Oracle, Oracle, Microsoft SQL Server, Microsoft Access, DB2, Sybase, MySQL, PostgreSQL atd.) i operačním systému (Windows, Linux). 2.2 ODBC Driver Manager (Object Linking and Embedding Databases) Správce ovladačů (ODBC DM) tvoří mezivrstvu mezi aplikací a ODBC ovladačem. Zajišťuje načítání potřebných knihoven ODBC ovladače. Umožňuje současnou inicializaci více ovladačů a tedy i jejich přepínání v rámci jedné aplikace. Je tedy možné přistupovat současně k více íce zdrojům dat. 2.3 OCI (Oracle Call Interface) Rozhraní OCI určené pro platformu Oracle nabízí širokou paletu funkcí pro komunikaci s databázovým serverem a zároveň poskytuje vyšší výkon, než např. ODBC rozhraní. Rozhraní OCI obsahuje (kromě základních funkcí funkc pro práci s db) podporu pro volání PL/SQL, transakce, načítání a ukládání objektu typu LOB/CLOB, funkce unkce pro správu. 3. Připojení k databázi V následujících ukázkách kódu bude pro jednoduchost jako příklad použito jazyka PHP, nicméně volání db. v jiných jazycích jazycích (i použitá množina funkcí) je analogické. Pro práci s databází je nejprve nutné navázat spojení a provést autorizaci i autentizaci uživatele. 3.1 Prostřednictvím ODBC Pro vytvoření spojení se používá funkce odbc_connect.. Její deklarace vypadá následovně: $database_resource = odbc_connect($dsn, odbc_connect $user, $password);

Parametry: $dsn

resa databázového zdroje, např. "localhost" – adresa


2

$user

– uživatelské jméno (název schématu)

$password

– uživatelské živatelské heslo

V případě úspěšného připojení funkce vrací identifikátor spojení, který se dále používá jako ko parametr pro volání dotazů, pro ukončení spojení,, či zjištění vzniklé chyby. 3.2 Prostřednictvím OCI Pro vytvoření spojení prostřednictvím OCI rozhraní oci_connect.. Její deklarace vypadá následovně:

lze

využít

metodu

$database_resource = oci_connect($username , $password [, $connection_string [, $character_set]] )

Parametry: $username

– uživatelské jméno (název schématu).

$password

– uživatelské heslo

$connection_string

– adresa databázového zdroje, např. "localhost "localhost:1528"

$character_set

- znaková sada, např. "UTF8"

Metoda opět v případě úspěšného spojení vrátí identifikaci spojení. 3.3 Perzistentní spojení Na rozdíl od desktopových aplikací, kde lze udržovat platnost objektu po celou dobu běhu programu, u webových aplikací aplik ztrácí objekt (např. instance databázového spojení) platnost po dokončení ní požadavku (načtení stránky). Z toho vyplývá, že i v případě dynamických stránek (využívajících AJAX) musí při každém požadavku vznikat nová instance databázového spojení a tedy i nová relace v databázi. Tento problém řeší perzistentní spojení, které v případě opakované žádosti (stejná adresa, uživatelské jméno a heslo) o připojení přidělí již otevřenou volnou instanci. Vše probíhá na pozadí (v režii serveru). Deklarace je obdobná: $database_resource = oci_pconnect($username connect($username , $password [, $connection_string [, $character_set]] )


3

Respektive (v případě ODBC spojení): $database_resource = odbc_p pconnect($dsn, $user, $password);

4. Volání příkazů SQL dotazů esp. získání instance spojení lze přikročit k volání samotných Po úspěšném připojení, resp. příkazů SQL dotazů. 4.1 Syntaxe Syntaxe volaných příkazů SQL dotazů pochopitelně vychází ze specifikace SQL jazyka konkrétní platformy. Jednotlivé příkazy PL/SQL jsou oddělovány středníky. SQL dotazy středníky ukončeny nejsou. 4.2 Vytvoření handleru SQL dotazu Funkce oci_parse vytvoří handler SQL dotazu a vrátí jeho identifikátor. Funkce neprovádí dotaz ani validaci, ta probíhá až při jeho následném spuštění. $statement = oci_parse($database_resource, $query);

4.3 Volání SQL dotazu Funkce oci_execute spustí dotaz. Parametrem je identifikátor $statement získaný funkcí oci_parse. Návratová hodnota funkce je typu bool, a nabývá hodnoty true v případě úspěšného provedení dotazu, dotazu false v případě chyby. oci_execute($statement);

4.4 Volání SQL dotazu s parametry Funkce oci_bind_by_name umožňuje bezpečné vázání (binding) parametrů k volanému SQL dotazu. V dotazu je parametr nahrazen zástupným symbolem, který je spárován funkcí oci_bind_by_name s vlastní hodnotou. // Dotaz s použitím zástupných ástupných symbolů $query = "UPDATE employees SET emp_name = :name WHERE emp_id = :id" "; // Vytvoření handleru dotazu. $statement = oci_parse($database_resource, database_resource, $query); // Vázání hodnot na zástupné symboly oci_bind_by_name($statement, $statement, ":id", 1); oci_bind_by_name($statement, ($statement, ":emp_name", "Jan");


4

// Spuštění dotazu oci_execute($statement);

4.5 Zpracování výstupu dotazu Ke zpracování výstupu dotazu lze použít sadu funkcí (lišících se návratovým typem) typu fetch: • • • • •

oci_fetch oci_fetch_row oci_fetch_assoc oci_fetch_array oci_fetch_all

Podrobný manuál jednotlivých funkcí je nad rámec tohoto bloku (více viz. PHP manuál dostupný na www.php.net). www.php.net Nejpoužívanější funkcí je oci_fetch_array, oci_fetch_array která vrací jednorozměrné pole – řádek na aktuální pozici kurzoru. Přístupové indexy odpovídají názvům sloupců tabulky (dotazu). // Dotaz vracející všechna echna data z tabulky zaměstnanců $query = "SELECT id, name, phone FROM employees"; // Vytvoření ytvoření handleru dotazu. $statement = oci_parse($database_resource, database_resource, $query); // Spuštění dotazu oci_execute($statement); // Průchod kurzorem a vypsání vybraných prvků pole while (($row $row = oci_fetch_array($statement))){ oci_fetch_array($statement) echo "Jméno: " + $row["name name"]; echo "Telefon: " + $row[" "phone"]; }

5. PL/SQL Volání příkazů PL/SQL jazyka probíhá analogicky jako volání SQL dotazů. Jednotlivé příkazy jsou oddělené středníkem ředníkem a jsou obsaženy v programovém bloku ohraničeném direktivami begin a end;. Příklad volání PL/SQL: $query = "begin -- Volání db. procedury pridej_osobu s parametry pridej_osobu(:jmeno jmeno,:prijmeni);


5

end;"; // Vytvoření handleru dotazu. $statement = oci_parse($database_resource, database_resource, $query); // Vázání hodnot na zástupné symboly oci_bind_by_name($statement, ($statement, ":jmeno", "Jan"); oci_bind_by_name($statement, ($statement, ":prijmeni", "Novák"); // Spuštění dotazu oci_execute($statement);

6. Transakce Databázový server prostřednictvím transakcí zajišťuje konzistenci dat v databázových tabulkách a nedělitelnost prováděných změn. Je možné např. ošetřením výjimky vrátit změny provedené v rámci celé transakce. Platnost transakce může být implicitně potvrzena funkcí oci_execute,, nebo ji lze potvrdit explicitně voláním funkce oci_commit. 6.1 Volaná implicitně Druhým volitelným parametrem výše uvedené funkce oci_execute je parametr $mode. Defaultně nabývá hodnoty OCI_COMMIT_ON_SUCCESS, tedy v případě úspěšného provedení dotazu je transakce automaticky dokončena. Parametr je možné nastavit na hodnotu OCI_NO_AUTO_COMMIT , tedy nedokončovat transakci po provedení dotazu. // Spuštění štění dotazu bez potvrzení transakce oci_execute($statement, ($statement, OCI_NO_AUTO_COMMIT);

6.2 Volaná explicitně Transakce je vytvořena prvním voláním funkce oci_execute s parametrem OCI_NO_AUTO_COMMIT. Transakci lze explicitně potvrdit voláním funkce oci_commit: oci_commit($database_resource) database_resource);

nebo je možné transakci odvolat voláním funkce oci_rollback: oci_rollback($database_resource) database_resource);


6

7. Výjimky Ošetření výjimek tvoří důležitou součást komunikace s databázovým strojem. V programu lze zachytávat vnitřní (tedy systémem definované) ale i uživatelské výjimky. Syntaktické chyby by měly být rozpoznány a odstraněny již ve fázi testování. Logické chyby (např. typické dělení nulou) nelze s jistotou ve všech případech předvídat, a proto je vhodné jejich ošetření i na více úrovních. V PHP (na rozdíl např. od Javy) nejsou výjimky volání příkazů SQL dotazů zachytávány v try – catch bloku, ale explicitním testováním testováním návratových hodnot (např. funkce oci_execute). Pokud tato funkce vrátí hodnotu false, došlo k výjimce, kterou je třeba ošetřit. Pro zjištění podrobností o vzniklé chybě je určena funkce oci_error,, vracející informaci ve tvaru asociativního pole. // Vytvoření handleru dotazu. $statement = oci_parse($database_resource, "SELECT FROM DUAL"); // Volání dotazu oci_execute($statement); // Zachycení výjimky $error = oci_error($statement); // Výpis pole print_r($error); Array ( [code] => 936 [message] => ORA-00936: 00936: missing expression [offset] => 7 [sqltext] => SELECT FROM DUAL )


7

Pojmy k zapamatování Pojmy: spojení

Open Database Connectivity, Oracle Call Interface, perzistentní

Problém: spojení

bezpečné vázání parametrů dotazu, ošetření výjimek, perzistentní

Shrnutí V této lekci jste se seznámili se základy přístupu a volání příkazů SQL dotazů z jazyka PHP: • Perzistentní spojení umožňuje využívat existující relace. • Pro vázání proměnných je nutné využívat dostupné funkce ovladače. • Klíčovou součástí komunikace je důsledné ošetření výjimek na všech úrovních aplikace. Otázky na procvičení 1. 2. 3. 4.

Jakéé jsou výhody nativních rozhraní? Co je perzistentní spojení a kde se využívá? Jakým způsobem jsou zadávány parametry volaných dotazů? Jaké jsou způsoby transakčních zpracování?

Odkazy a další studijní prameny • •

http://www.oracle.com/technetwork/database/features/oci/index.html http://www.php.net

Odkazy a další studijní prameny

•

LACKO, L., Oracle – Správa, programování a použití tí databázového systému.. Praha: Computer Press, 2007. 200 ISBN 978-80-251-1490-2. KOSEK, J., PHP – Tvorba interaktivních internetových aplikací. Grada

•

Publishing, 1999.. ISBN 80-7169-373-1 80 HORTON, I., Java 5. Neocortex, Praha. ISBN 80-86330-12-5.

•


8

5. blok

SQL Injection

Studijní cíl Tento blok je věnován problematice SQL injection, tedy riziku vložení rizikového kódu do příkazů SQL prostřednictvím uživatelského rozhraní.


2 hodiny

Průvodce studiem Při studiu tohoto bloku se předpokládá, že čtenář je obeznámen se s základy jazyka SQL, detailně s příkazem SELECT a s různými druhy databázových objektů.

1. SQL Injection Existuje několik verzí jazyka SQL, SQL jejich společnou vlastností však je textová forma komunikace s databází. Typickou jednotkou jazyka je dotaz, což je příkaz pro databázi, která na dotaz odpoví množinou výsledků. Příkazy SQL mohou modifikovat strukturu databáze (Data Definition Language - DDL) nebo ebo mohou manipulovat s obsahem databáze (Data (Dat Manipulation Language - DML). V historii českého Internetu najdete řadu hacknutých webů díky bezpečnostní chybě - v angličtině označované jako SQL injection. injection Jde de o souhrnné označení pro nepříjemné bezpečnostní chyby, které umožní vsouvat do SQL kódu (odtud SQL injection) internetové aplikace vlastní informace - pochopitelně takové informace, které umožní změnit smysl původních SQL příkazů. příkazů Na internetu lze stále najít mnoho webových serverů, které nejsou imunní imunn vůči SQL injection. Řada tvůrců aplikací nevěnuje základní pozornost těm nejobyčejnějším návykům a vypouští na veřejnost aplikace, které neměly nikdy opustit vývojářskou dílnu. Webové aplikace tvoří mnoho lidí a jen málokdy je podrobí testům na použitelnost, ost, natož aby proběhl alespoň základní bezpečnostní audit.

David Žák IDAS2/5 – SQL Injection

1

Možná rizika SQL injection lze vyjádřit například takto: • • • • •

získání přístupu k datům, ke kterým uživatel mít přístup nemá nebo nesmí (tedy i citlivým, důvěrným, …), možnost změny dat, která měla být určena jen pro čtení, možnost vstoupit do administračních částí internetové aplikace, možnost vyvolat příkazy SQL serveru, které umožní ovládnout stroj, na kterém SQL server běží, možnost smazat nějaké tabulky nebo celou databázi.

SQL injection je riziková všude tam, kde autoři internetové aplikace zapomněli na základní bezpečnostní pravidlo - veškeré vstupy do aplikace je nutné kontrolovat na povolené hodnoty/typy. A je úplně jedno, jestli jde o PHP/MySQL či ASP/MS-SQL či jinou kombinaci webového a databázového serveru. Potenciálně rizikové části internetové aplikace jsou všechna místa, kde nějaký údaj vstupuje zvenčí, tedy: • • • •

Formuláře (POST/GET) Parametry URI adresy HTTP/XML/SOAP komunikace (nezapomenout ani na Cookies) "Importy" souborů

K formulářům je vhodné dodat ještě jedno varování - řada webových aplikací používá "hidden" (skryté) prvky. Tvůrci aplikace mají falešný pocit bezpečí, že tento prvek "není" vidět a ani "není" editovatelný. Přitom stačí příslušnou stránku uložit jako HTML, libovolně si ji upravit a pak používat. Nejjednodušší způsob jak otestovat nějakou internetovou aplikaci na SQL injection je začít si hrát se vstupy do této aplikace. Nejdůležitější pomůckou pro ruční test na SQL Injection je řetězec ' or 1=1 --

(skutečně bez apostrofu na konci)

Pokud takovýto řetězec (můžete si odpustit prozatím ty dvě pomlčky "--" na konci) použijete pro testování, možná dostanete nějakou tu chybu od SQL serveru - pak víte s jistotou, že tvůrce webové aplikace něco zanedbal - má neošetřené vstupy do aplikace. Tuto konstrukci použijete v nejčastějším způsobu testování přihlašovacích informací - vložíte ji do "políčka" pro heslo!


2

Předpokládejme,, že aplikace obsahuje dotaz vzniklý zřetězením částí syntaxe dotazu a parametrů z formulářových polí či z adresního řádku: cSQL = "SELECT uniqueid FROM Users Where UserName='" & request("userid") & "' and Pwd='" & request("pwd") & "' "

Z uvedeného příkladu se pak stane (například) toto: SELECT uniqueid FROM Users Where UserName='' or 1=1 --' and Pwd='heslo'

Výsledkem bude likvidace nutné A pravděpodobně získání přístupu postará o ignorování zbytku SQL dotazu.

podmínky na do systému.

shodu hesla. " " se totiž "--"

Existuje nespočet návodů jak se SQL Injection vyhnout vyhno - pomocí Google a zadání "SQL Injection" jich najdete řadu - budete si tak moci i vybrat, zda potřebujete návod pro Microsoft SQL, MySQL či pro "něco" jiného. Principy jsou obdobné: •

Textové vstupy prohnat náhradou ['] za [''] (neboli dvojici apostrofů). apostrofů). Jde o nejjednodušší způsob, který znemožní použít ['] pro "ukončení" vytvářeného SQL dotazu.

•

Textové vstupy prohnat odpovídající RegExp() transformací, která v nich ponechá pouze znaky, které v nich mají být. Tímto způsobem je pochopitelně více než vhodné ošetřovat i vstupy, které se stanou později výstupy - vyhnete se tak nebezpečí jinému, jménem XSS.

•

Netextové vstupy prohnat odpovídající typovou konverzí - tj. například pokud ?id=XXX má být integer, intege tak použít odpovídající konverzníí funkci třeba int()

•

Vstupy s pevně daným malým výčtem hodnot opravdu testovat na to, zda danému výčtu odpovídají. Příkladem může být testování na True/False, On/Off.

•

Uvedeným způsobem se vyhnete SQL Injection v okamžiku, okamžiku, kdy nechcete použít nějakou jinou metodu tvorby SQL dotazů a zůstáváte u "operativní" klasiky sestavování SQL dotazů (způsobem naznačeným výše). Pokud to není nutné (a popravdě, ono to ani není příliš vhodné), tak se můžete vydat dalšími cestami, kteréé úplně stejně zabrání SQL Injection.


3

•

Parametrizovatelné SQL využívá toho, že "objekty" používané pro vyvolání SQL dotazů zpravidla umožňují předávat parametry způsoby, které samy o sobě zabezpečí potřebné zabezpečení parametrů.

•

Sestavený dotaz v takovém takovém případě není pouhým zřetězením částí syntaxe dotazu a parametrů, jako v předchozím případě. cSQL = "SELECT uniqueid FROM Users WHERE UserName=@username AND Pwd=@password"

•

Následně postačí využít SqlCommand a s pomocí SQLParameter provést odpovídajícíí "náhradu" definovaných parametrů.

•

Více íce než vhodné je, aby účet, pod kterým přistupuje internetová aplikace do SQL serveru, zcela určitě nebyl účtem systémového administrátora a musí mít pouze nezbytná práva!

•

V úplně ideálním případě by měly existovat pouze pouze uložené procedury a příslušný účet by měl mít práva pouze pro jejich spouštění - tím se dá odstranit i případné nebezpečí z "podvrhnutých" lahůdek jako "TRUNCATE TABLE" či "DELETE * FROM" (popravdě i možnost vytvářet nové tabulky může být nebezpečná, stačí je vytvářet vytvářet tak dlouho a tak velké až zaberou veškeré místo na discích serveru). serveru

•

Aplikace by neměla zobrazovat detailní informace o vyskytnuvší se chybě. Webová aplikace často nemá potlačeno zobrazování detailních chyb a je tak možné vidět i části SQL QL kódu - což útočníkovi umožní snadno se rozhodovat, jak SQL injection použít.

•

PHP nepovoluje vykonávání několika SQL dotazů v jednom volání funkce mysql_query(), takže tím odpadá problém s řetězcem typu '; truncate tabulka; - provádí se vše, co se nachází ází před prvním středníkem.

•

V MySQL neexistuje nic, co by mohlo zavolat externí aplikaci, jako je tomu například u MS SQL, takže odpadá spousta dalších potenciálně nebezpečných dotazů.

Níže následuje několik příkladů, nad nimiž si vysvětlíme různé druhy zneužití SQL Injection. Následující příklady budeme aplikovat na tabulce UZIV: Název sloupce Datový typ Id INT Nick VARCHAR(32) Email VARCHAR(255)


4

Příklad 1 - Zobrazení celého obsahu obs tabulky Původní dotaz: SELECT nick, email FROM uziv WHERE id = {$id}

Použité proměnné: $id = integer získaný od uživatele

Vložená hodnota: 9 OR 1=1 --

Výsledný dotaz: SELECT nick, email FROM uziv WHERE id = 9 OR 1=1 --

Principem tohoto útoku je,, že v dotazu musí být vždy platná podmínka a tudíž dotaz ovlivní (v případě příkazu SELECT vypíše) celý obsah tabulky. Jediným omezením při použití útoku typu SQL injection je, je, že vzniklý dotaz musí být syntakticky správný - musíte nejdřív ukončit původní původn podmínku a pak teprve můžete pokračovat s úmyslně vloženým kódem. V našem případě je pro ukončení původního dotazu použit pouze první znak (9), zbytek už je vložená "vždy platná“ platná podmínka, 1 se vždy rovná 1. MySQL má komentáře ve stylu "---". To se hodí pro případ, že v originálním dotazu je ještě něco za částí, kterou zneužíváme (klauzule ORDER BY, LIMIT a jiné) Tento dotaz bude ze strany serveru fungovat vždy, i když budete mít magic_quotes nastavené na „ON“ - vložený řetězec neobsahuje žádné „escapovatelné“ znaky (integery se nemusí dávat to apostrofů), je tedy jen na Vás, abyste si ohlídali vstupní data, jestli obsahují pouze povolené znaky (např. málokdy potřebujete vložit ?=? nebo ?-?? ). Kdy to bude fungovat? Přes řes nastavení serveru to projde vždy, je tedy jen na vás a na vašich skriptech, jak si s tím poradíte. Co s tím? Pokud okud víte, že potřebujete vypsat právě jednu (nebo víc, záleží na Vás) hodnotu a nikdy jinak, kontrolujte počet řádků ve výsledku, když číslo nebude jakkoliv souhlasit, zalogujte si to a nedovolte zobrazení takového výsledku.


5

Když máte předem daný počet řádků, které očekáváte, nepoužívejte while($x = mysql_fetch_*()),

ale obyčejnou funkci mysql_result(), mysql_result() kde přesně stanovíte, kolik řádků výsledku chcete zobrazovat (omezený počet iterací cyklu for nebo konkrétně vámi definovaná čísla řádků, které se mají vypsat). Příklad 2 - Zobrazení celého obsahu tabulky Původní dotaz: SELECT nick, email FROM uziv WHERE nick = '{$nick}'

Použité proměnné: $nick = řetězec získaný od uživatele třeba stejnou cestou,jako cestou, v příkladě #1

Vložená ložená hodnota proměnné $nick: ' or 'a' = 'a ' or 1=1 --

Výsledný dotaz: SELECT nick, email FROM uziv WHERE nick = '' or 'a' = 'a'

SELECT nick, email FROM uziv WHERE nick = '' or 1=1 --'

Toto je příklad velmi podobný příkladu 1 s tím rozdílem, že pokud chcete pracovat s řetězci, musíte je uzavřít do apostrofů, a ty už by neprošly bez újmy skriptem, který “escapuje“ nebezpečné proměnné. Ten by apostrof ' přeměnil na \'' a výsledek by byl (ve většině případů) adů) prázdný. Pro ro zamezení tohoto typu SQL injection stačí „escapovat“ vstupní hodnoty nastavením direktivy magic_quotes_gpc na hodnotu on zautomatizujete kontrolu vstupních hodnot, přesto ale doporučuji nespoléhat se na cizí opatření.


6

Příklad 3 - Jak vyzrát nad skriptem se zajištěném zobrazení jediného řádku výsledku? Původní dotaz: SELECT nick, email FROM uziv WHERE nick = '{$nick}' LIMIT 1

Ve skriptu aplikace navíc implementováno vypsání pouze prvního řádku výsledku. výsledku Použité proměnné: $nick = řetězec tězec získaný od uživatele

Vložená ložená hodnota proměnné $nick: ' OR id = [cislo] ORDER BY [sloupec][DESC/ASC] -' OR id = 2 ORDER BY id DESC --

Výsledný dotaz: SELECT nick, email FROM uziv WHERE nick = '' OR id = 2 ORDER BY id DESC --' -LIMIT 1

V tomto případě jde o to dostat námi požadovaný záznam na první místo, nic víc. Problém je v tom, že zobrazíte pouze jediný záznam a navíc musíte znát jednu z hodnot, která je v požadovaném záznamu, pak musíte zvolit jednu vyšší (nebo nižší) a následně seřadit výsledek buď sestupně, sestupně nebo vzestupně v závislosti na zvolené hodnotě. Co s tím? Téměř éměř nic. Pokud už povolíte uživateli, aby vkládal do vašich SQL dotazů apostrofy a uvozovky, nezmůže s tím skript vůbec nic, pouze zpracuje zpracuje data vrácená SQL dotazem. Pokud útočník točník upraví SQL dotaz tak, aby vracel na prvním řádku data, která se snaží získat, máte smůlu. Jediná obrana proti tomuto typu útoku je vždy kontrolovat počet řádků výsledku (když znáte předem počet řádků, které má dotaz standardně vracet). vracet


7

Příklad 4 - Použití klauzule UNION pro získání získá dat z jiných tabulek Klauzule UNION slouží pro spojení výsledků 2 různých dotazů a jejich vrácení jako jediný výsledek.

Původní dotaz: SELECT nick, email FROM uziv WHERE uid = '{$uid}' LIMIT 1

Použité proměnné: $uid = řetězec získaný od uživatele, použitelný kdekoliv

Vložená ložená hodnota proměnné $nick: 1' UNION SELECT heslo AS nick, nick AS email FROM uziv --

Výsledný dotaz: SELECT nick, email FROM uziv WHERE uid = '1' UNION SELECT heslo AS nick, nick AS email FROM uziv --' LIMIT 1

První částí SQL dotazu se vybere jeden jeden uživatel a jeho jméno a vloženou druhou částí dotazu se vyberou všichni uživatelé a jejich hesla. Klauzule UNION obě části spojí do jediného výsledku, takže pokud toto útočník vloží například do seznamu uživatelů (kde zpravidla bývá cyklický výpis výsledku), tak skript poslušně vypíše i velice citlivá data všech uživatelů. uživatelů Samozřejmě v dotazu můžete kombinovat jakékoliv sloupce a jakékoliv tabulky. tabulky Řešením je nedovolit dovolit útočníkovi vložit řetězec podobný podobný tomuto příkladu. Pokud se mu to podaří, nedá se s tím zpravidla dělat už vůbec nic. Konkrétně pro případ uchovávání hesel je doporučeno „hashovat“ hesla například pomocí PHP funkce sha1(), pokud už dojde k ukradení těchto údajů, nesmírně tím znepříjemníte znepříjemníte útočníkovi jejich prolomení.


8

2. PHP a MySQL funkce. Například pro PHP má pro komunikaci s různými databázemi samostatné funkce. Oracle je to ora_bind, ora_execute. Některé databázové ovladače nemají podporu placeholderů (například MySQL) a je třeba z každé proměnné zvlášť odstranit nebezpečné znaky,, viz následující příklad: příklad $esc_jmeno = mysql_real_escape_string($jmeno); $query = "SELECT id, jmeno, prijmeni FROM lide WHERE jmeno=$esc_jmeno“; $result = mysql_query($query)

mysql_real_escape_strin upraví řetězec pro bezpečné použití Funkce mysql_real_escape_string v mysql_query. Tato funkce opatří opat speciální znaky v neupraveném řetězci zpětným lomítkem pro bezpečné použití v mysql_query() v závislosti na aktuální znakové sadě spojení (konce řádků jsou nahrazeny značkou \n atd.).

Pojmy k zapamatování Příkazy a funkce:

mysql_query mysql_real_escape_string mysql_query,

Problém: zneužití dat, neoprávněný přístup, odstranění dat z databáze, získání citlivých dat z databáze

Shrnutí V této lekci jste se seznámili s rizikem nazývaným SQL Injection. Pro zamezení vzniku SQL injection je nezbytné ošetřit vstupní formuláře a z nich získaná data: • • • • • •

použití regulárních ích výrazů, výrazů využití konverzních funkcí, funkcí nahrazení rizikových znaků (například apostrofy, středníky), kontrola na výčet hodnot, hodnot omezení délky vstupního pole formuláře, formuláře použití funkcí pro nahrazení nebezpečných znaků v textu.


9

Otázky na procvičení Injection 1. Vysvětlete pojem SQL Injection? 2. Jaká jsou rizika SQL Injection? 3. Vyjmenujte aspoň 3 techniky, jak lze SQL Injection zamezit? zamezit 4. Z jakých prvků WWW aplikace lze očekávat pokus o SQL Injection? Injection 5. Existují nějaké jiné techniky výměny dat mezi webovým a databázovým serverem, které nepoužívají SQL dotazů? dotazů

Odkazy a další studijní prameny • • • • • • • •

http://digiweb.ihned.cz/c4-10122900-19732020-i00000_d-sql-injectionhttp://digiweb.ihned.cz/c4 princip-a-ochrana (článek článek o SQL Injection) Injection Při tvorbě prezentace byly použity informace z WWW http://www.security-portal.cz/sql portal.cz/sql-injection.html http://cs.wikipedia.org/wiki/SQL_injection http://digiweb.ihned.cz/c4 http://digiweb.ihned.cz/c4-10122900-19732020-i00000_d-sql-injectionprincip-a-ochrana http://php.vrana.cz/obrana http://php.vrana.cz/obrana-proti-sql-injection.php http://www.youtube.com/watch?v=1ZSx2GakVWk&feature=fvst http://www.youtube.com/watch?v=jMQ2wdOmMIA&feature=related http://www.youtube.com/watch?v=jMQ2wdOmMIA&feature=related.


10

6. blok Jazyk PL/SQL, syntaxe bloku, cykly, kurzory, záznamy

Studijní cíl Tento blok je věnován úvodu do jazyka PL/SQL, základní syntaxi bloku PL/SQL, deklaracím, větvení, cyklům a kurzorům 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 a je schopen napsat jednoduchý program v libovolném programovacím jazyce. 1. Úvod do PL/SQL Hlavním omezením, nebo spíše charakteristikou SQL jazyka je to, že se jedná o jazyk neprocedurální. Lze říci, že v jazyce SQL říkáme, co chceme získat a nikoli, jak to chceme získat. Vlastní postup při provádění dotazu pak určuje optimalizátor, který na základě ákladě různých informací počínaje syntaxí příkazu a s využitím například statistik o datech v tabulkách a jejich sloupcích sestaví exekuční plán příkazu, který je následně proveden. Ne vždy však s tímto vystačíme a tak prakticky každá moderní databázová platforma platforma implementovala procedurální rozšíření jazyka SQL. Společnost Oracle toto rozšíření nazvala PL/SQL (Procedural Language). PL/SQL vyšlo z jazyka Ada. Jde o jazyk třetí generace, který umožňuje například deklarace proměnných, kurzorů, cykly, podmínky či zpracování chyb. Podporuje i dynamické deklarace. Původně se jednalo o jazyk pouze procedurální, ale postupem doby do něho byla implementována i práce s objekty a tak od verze Oracle 10g jsou k dispozici téměř všechny objektově orientované vlastnosti. Samozřejmostí je podpora SQL dotazů a transakčního zpracování. 2. Historie PL/SQL Historie jazyka PL/SQL je již více než dvacetiletá a sahá do roku 1991. V následující tabulce si stručně shrneme základní informace o verzích PL/SQL a jejich vlastnostech.

David Žák IDAS2/6 – Jazyk PL/SQL, syntaxe bloku, cykly, kurzory, záznamy

1

Verze PL/SQL 1.x (Oracle 6.0) 2.x (Oracle 7.3) 8.0 8.1 9.0 9.2 10.0

11.0

Vybrané nové vlastnosti Základní verze Podpora uložených procedur a funkcí, mnoho vestavěných balíků Základní podpora objektů Integrace jazyka Java, nativní dynamické dotazy (EXECUTE IMMEDIATE) Objekty podporují dědičnost a typový vývoj, Nativní kompilace kódu PL/SQL Přibyly vestavěné funkce, vylepšený přístup PL/SQL k dokumentům XML uloženým v datovém typu XMLTYPE Podpora regulárních výrazů Možnost dostávat varování při kompilaci kódu Balík DBMS_LOB pro práci s objekty až 128 TB Úprava práce s řetězci Jmenné a smíšené zápisy parametrů při volání podprogramů Automatické přilinkování podprogramů při kompilaci Příkaz CONTINUE pro ukončení cyklu Přímé volání hodnot sekvencí sekv Native Oracle XML DB Web Services

3. Základy jazyka PL/SQL V této kapitole se podíváme na základní zák vlastnosti jazyka PL/SQL. 3.1. Syntaxe bloku Programový jazyk PL/SQL je modulární a skládá se z bloků. Základem je programový blok, který se typicky skládá ze 3 sekcí: -

deklarační sekce výkonná sekce sekce pro zpracování výjimek

Deklarační sekce obsahuje deklarace proměnných, konstant a kurzorů. Výkonná sekce obsahuje vlastní aplikační jádro, které často manipuluje i s daty v databázi. Chybové stavy, které mohou vzniknout za běhu programu, lze ošetřit v sekci pro zpracování výjimek. Obecnou syntaxi a strukturu programového bloku tedy možno zapsat takto: DECLARE -- deklarační sekce (nepovinná) BEGIN -- výkonná sekce EXCEPTION -- sekce pro zpracování výjimek (nepovinná) END; David Žák IDAS2/6 – Jazyk PL/SQL, syntaxe bloku, cykly, kurzory, záznamy

2

Jak je již uvedeno, povinnou sekcí je jen výkonná sekce, takže minimální zápis programového bloku v jazyku PL/SQL bude: BEGIN -- výkonná sekce END;

Blok PL/SQL je ucelená část kódu PL/SQL. Program PL/SQL se může skládat z více takových bloků, které budou do sebe vloženy. Před uvedením konkrétních příkladů je vhodné se seznámit s příkazem DBMS_OUTPUT.PUT_LINE( DBMS_OUTPUT.PUT_LINE('řetězec');

který vypíše na konzoly obsah h daného řetězce. Před použitím tohoto příkazu je však nezbytné příkazem SET SERVEROUT ON aktivovat zachytávání těchto výstupů. Anonymní bloky Anonymní bloky jsou bloky kódu, které se nepojmenovávají ani neukládají. Pokud bychom chtěli tento anonymní blok provést opakovaně, musíme jej uložit do souboru a znovu jej ručně spustit v okamžiku potřeby, nebo jej vložit do programu, který blok spouští. DECLARE v_pocet NUMBER(2); BEGIN SELECT count(*) INTO v_pocet FROM TRPASLICI; TRPASLICI DBMS_OUTPUT.PUT_LINE( DBMS_OUTPUT.PUT_LINE('Počet trpaslíků: '||v_pocet) v_pocet); IF v_pocet <> 7 THEN DBMS_OUTPUT.PUT_LINE( DBMS_OUTPUT.PUT_LINE('Chyba - trpaslíků není 7!') ); END IF; END;

Anonymní bloky se hodí na skripty nebo na činnosti, které nechceme často opakovat. Blok začíná příkazem DECLARE, DECLARE pokud obsahuje deklarační část nebo BEGIN.


3

Procedury Procedury se ukládají do databáze a mají názvy. Mohou, ale nemusí vracet hodnotu. Uložené procedury mají v rámci schématu jedinečné jméno. Procedurám je věnován samostatný blok. Funkce Na rozdíl od procedur funkce musí vracet hodnotu. Strukturou se velice podobají procedurám, ale navíc obsahují povinnou klauzuli RETURN. Obdobně jako u procedur i v rámci daného schématu musí být jméno jedinečné v rámci všech názvů použitých pro databázové objekty v daném databázovém schématu. Balíky Procedury a funkce je možné umístit do logické skupiny - balíku. Balíky mají 2 části: specifikaci a tělo. Specifikace je veřejná a ukazuje strukturu balíku, tělo obsahuje vlastní kód pro realizaci požadovaných činností. Pokud Pok budeme vytvářet a s objekty PL/SQL běžně pracovat, jsou balíky to správné řešení pro udržení přehlednosti řešení, rozšíření oboru názvů procedur a funkcí, ale i snížení zátěže databázového serveru při sledování závislostí mezi databázovými objekty. Objekty Jak již bylo uvedeno, pomocí PL/SQL je možné zapsat objektově orientovaný kód. Obdobně jako balíky mají i objekty specifikaci a tělo. Umožňují určitou míru abstrakce. Objekty mohou obsahovat atributy a metody. Atributy definují vlastnosti objektu, metody pracují s datovými strukturami a veškeré interakce mezi aplikací a daty objektů by měly probíhat právě (a pouze) prostřednictvím metod. 3.2. Proměnné Před prvním použitím je nutné proměnné v jazyce PL/SQL deklarovat. To se provede v takzvané deklarační sekci, kde se proměnné vyhradí odpovídající paměťový prostor, navíc je možné během deklarace také proměnnou inicializovat. Inicializace je možná buď pomocí operátoru perátoru přiřazení ( := ) nebo pomocí klíčového slova DEFAULT.. Oba principy jsou uvedeny na následujícím následu příkladu,, u proměnné v_promenna3 je navíc omezení, že během běhu programu nesmí nabývat hodnoty NULL. DECLARE v_promenna1 NUMBER(2) NUMBER(2); v_promenna2 NUMBER(2) := 13; v_promenna3 NUMBER(4,2) NOT NULL DEFAULT 4.27; BEGIN David Žák IDAS2/6 – Jazyk PL/SQL, syntaxe bloku, cykly, kurzory, záznamy

4

DBMS_OUTPUT.PUT_LINE('v_promenna1: '|| v_promenna1); DBMS_OUTPUT.PUT_LINE('v_promenna2: '|| v_promenna2); DBMS_OUTPUT.PUT_LINE('v_promenna3: '|| v_promenna3); END;

Po provedení tohoto kódu budou na konzolu vypsány 3 řádky: v_promenna1: v_promenna2: 13 v_promenna3: 4,27

Vidíme, že hodnota NULL proměnné v_promenna1 není zobrazena. Většina datových typů používaných v PL/SQL je shodných či odvozených od datových typů databáze Oracle. Přehled datových typů uvádí následující obrázek:

Obr.1 - Datové typy v PL/SQL. Zdroj: [1]

V následujícím příkladu si ukážeme jednoduché použití proměnné znakového datového typu, logické proměnné datového typu BOOLEAN a datového typu pro vyjádření hodnoty data a času, například DATE.


5

DECLARE v_datum_cas DATE; VARCHAR2(20) := 'Pokus'; v_text v_logicka BOOLEAN BOOLEAN; BEGIN v_logicka := TRUE; IF v_logicka THEN v_datum_cas := sysdate; sysdate END IF; DBMS_OUTPUT.PUT_LINE( DBMS_OUTPUT.PUT_LINE('v_datum_cas: '|| v_datum_cas); DBMS_OUTPUT.PUT_LINE( DBMS_OUTPUT.PUT_LINE('v_text: '|| v_text); END;

Po provedení kódu bude na výstupu uveden aktuální datum a čas a obsah řetězce v_text: v_datum_cas: 30.03.12 13:49:18 v_text: Pokus

Je nutné si uvědomit, že došlo k implicitní konverzi typu DATE na řetězec podle aktuálních NLS (National Language Support) parametrů platných pro danou relaci (session). Pokud bychom chtěli jiný formát data či času, použijme pro převedení typu DATE nebo TIMESTAMP na znakový řetězec funkci TO_CHAR. Deklarace podle jiné proměnné či sloupce tabulky Datový typ i rozsah proměnné je také možné určit podle jiné proměnné nebo podle sloupce tabulky, viz následující příklad: DECLARE v_text v_jmeno v_narozen BEGIN ...

VARCHAR2(20); v_text%T v_text%TYPE; A_O_SNEHURCE.TRPASLICI.NAROZEN A_O_SNEHURCE.TRPASLICI.NAROZEN%TYPE;

END;

Výhodou této deklarace je, že si nemusíme pamatovat či dohledávat konkrétní datový typ v ostatním kódu a definici tabulek. Tento typ deklarace zvláště oceníme v případech, kdy dojde k úpravě datových typů „vzorových“ proměnných či sloupců tabulek. V takovém m případě není nutné kód (např. proceduru či funkci) upravovat. Na základě sledování závislostí mezi odkazujícími se objekty bude odkazující se objekt zneplatněn a při prvním použití následujícím po změně vzorového objektu dojde k jeho kompilaci (pokud ji neprovedeme ručně dříve). Nebrání-lili nic použití David Žák IDAS2/6 – Jazyk PL/SQL, syntaxe bloku, cykly, kurzory, záznamy

6

kódu s upravenými datovými typy, bude automaticky zkompilován a nadále může být používán ve své aktualizované verzi. 3.3. Komentáře a kultura zdrojového kódu Komentáře označují části kódu, který se neprovádí, neprovádí ale slouží k psaní poznámek a zpřehlednění kódu. Jednořádkové komentáře se začínají dvěma pomlčkami ( -- ) a veškerý text následující až do konce řádku se považuje za komentář, víceřádkové komentáře se uzavírají do párových značek /* komentář */ . Praktické tické použití si ukážeme v následujících příkladech. K přehlednosti kódu nejen pro kolegy, ale i pokud se my sami vrátíme po určité době k vlastnímu kódu, je vhodné dodržovat některá pravidla, například: -

-

SQL příkazy, klíčová slova jazyka PL/SQL a datové typy typy je vhodné psát velkými písmeny. Názvy proměnných, tabulek, sloupců a parametrů ve vhodné psát malými písmeny. Pomocí prvního znaku identifikátoru lze naznačit typ, například v_text (od v-ariable -- promenna), e_osetreni od (e-xeption (e -- výjimka), … Někdyy se u proměnných na základě tzv. maďarské notace jako první znak za podtržítkem použije znak označující její datový typ, např. v_nSuma (proměnný typu n-umber). umber). Jednotlivé úrovně a vnořené kódy by měly být správně a dostatečně znatelně odsazené pomocí tabulátorů tabu nebo mezer.

3.4. Vnořené bloky Syntaxi základního bloku jsme si již dříve ukázali. Nyní si ukážeme obecný příklad, kdy do hlavního bloku vnoříme ještě jeden blok. DECLARE -- deklarační sekce (nepovinná) BEGIN -- výkonná sekce DECLARE -- deklarační sekce (nepovinná) BEGIN -- výkonná sekce EXCEPTION -sekce pro zpracování výjimek (nepovinná) END; EXCEPTION David Žák IDAS2/6 – Jazyk PL/SQL, syntaxe bloku, cykly, kurzory, záznamy

7

-- sekce pro zpracování výjimek (nepovinná) END;

Programový kód ve vnitřním bloku může pracovat s proměnnými deklarovanými ve vnějším bloku. Naopak to ale neplatí. 3.5. Operátory Obdobně jako v jazyce SQL, také v PL/SQL se používají operátory pro zápis matematických a logických operací a platí zde běžné priority operátorů. Prioritu operátorů je možné ovlivnit pomocí závorek. Musíme si uvědomit, že operátory NOT, AND a OR pracují s třemi stavy TRUE, FALSE a NULL, pravdivostní tabulky jsou uvedeny v bloku o logických operátorech v jazyce SQL. Přehled operátorů dle priorit je uveden v následující tabulce.

Operátor ** +, *, / +, -, || =, <, >, < =, > =, <>, !=, ~= IS NULL, LIKE, BETWEEN, IN NOT AND OR Operace s vyšší prioritou jsou použity jako první. Operátory se stejnou prioritou jsou použity dle jejich pořadí v textu příkazu. Pořadí provádění můžete změnit pomocí závorek. Pokud výraz obsahuje závorky, provedení začíná u nejvnitřnějšího páru. 3.6. Příkaz SELECT pro výběr dat z tabulek v PL/SQL Jak už je ze samotného označení PL/SQL zřejmé, předpokládá se, že jazyk PL/SQL bude primárně využíván na získávání, zpracování a ukládání dat do databázových tabulek. Data z tabulek můžeme například zjistit pomocí tohoto typu dotazu: SELECT [* | seznam_položek] INTO [seznam_položek nebo proměnná typu záznam] FROM název_tabulky WHERE podmínky_výběru; David Žák IDAS2/6 – Jazyk PL/SQL, syntaxe bloku, cykly, kurzory, záznamy

8

Jak vidíme, jde o standardní dotaz jazyka SQL doplněný klauzulí INTO, pomocí níž přiřazujeme hodnoty jednotlivých sloupců na výstupu dotazu konkrétním proměnným. Podmínkou úspěšnosti tohoto dotazu je, aby dotaz vrátil vždy přesně jeden řádek. Je to logické, neboť do jedinéé skalární proměnné není možné vložit více hodnot zároveň. Z uvedených důvodů musíme věnovat pozornost konstrukci dotazu. Buď v podmínce WHERE budeme pracovat s konkrétní hodnotou primárního klíče nebo můžeme použít agregačních funkcí v souhrnných dotazech.. Již při zápisu kódu si musíme uvědomit, že podmínka jediného řádku na výstupu tohoto dotazu musí být naplněna i kdykoli v budoucnu, kdy se databázová aplikace bude používat a dat v tabulkách bude přibývat. Na následujícím příkladu si ukážeme použití dotazu dota přímo v kódu PL/SQL: DECLARE v_jmeno meno 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); END;

V tomto příkladu je podmínka omezení výsledku dotazu na jediný řádek naplněná aplikací podmínky WHERE Id=2 nad danou tabulkou, neboť sloupec Id je primárním klíčem, žádná z hodnot se tedy nesmí opakovat. Nicméně i v tomto případě hrozí, že žádný řádek v tabulce UCITEL nebude mít Id=2,, pak by dotaz nevrátil žádný výsledek (na výstupu dotazu by byl nulový počet řádků). Jediným řešením bude tedy ošetření chyb v sekci pro zpracování výjimek. Při použití dotazu SELECT … INTO … může dojít obecně k dvěma chybám. První z nich nastane v případech, kdy výsledkem dotazu je více než jeden řádek a ta má název TOO_MANY_ROWS, v druhém případě nastane výjimka s názvem NO_DATA_FOUND,, tj. výsledkem dotazu není ani jeden řádek. Malinko předběhneme ostatní teorii a ukážeme si si výše uvedený příklad doplněný o sekci pro zpracování výjimek, která ošetří obě popsané výjimky.


9

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 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;

V tomto místě by bylo vhodné poznamenat, že takto jednoduše můžeme pracovat také s dalšími SQL příkazy, například INSERT, UPDATE či DELETE,, tj. mohou být přímo uvedeny v kódu PL/SQL. Základním Základním podmínkou však je, že názvy tabulek a sloupců jsou v dotazech pevně stanoveny a existují v okamžiku kompilace kódu PL/SQL. V takovém případě hovoříme o tzv. statických dotazech.

3.7. Podmínky Jednou nou ze základních konstrukcí pro ovlivňování toku programu jsou podmínky, které umožňují větvení programového kódu. Následující kód je natolik jednoduchý, že jeho popis je zbytečný. Ukazuje syntaktická pravidla pro různé konstrukce podmínek. IF podmínka THEN posloupnost_příkazů END IF;

nebo IF podmínka THEN posloupnost_příkazů1 ELSE posloupnost_příkazů2 END IF; David Žák IDAS2/6 – Jazyk PL/SQL, syntaxe bloku, cykly, kurzory, záznamy

10

nebo IF podmínka1 THEN posloupnost_příkazů1 ELSIF podmínka2 THEN posloupnost_příkazů2 ELSE posloupnost_příkazů3 END IF;

nebo IF podmínka1 THEN posloupnost_příkazů1 ELSIF podmínka2 THEN posloupnost_příkazů2 ELSE posloupnost_příkazů3 END IF;

Příkaz CASE pro vícenásobné větvení programu: CASE WHEN podmínka1 THEN posloupnost_příkazů1; WHEN podmínka2 THEN posloupnost_příkazů2; .. WHEN podmínkaN THEN posloupnost_příkazůN; [ ELSE posloupnost_příkazůN+1; ] END CASE;

Výše uvedené konstrukce jsou natolik jednoduché, že k nim nejsou uváděny příklady a jsou použity v kódech některých příkladů v dalších kapitolách.

3.8. Cykly Obdobně jako větvení, také cykly patří mezi základní konstrukce při řízení toku programu. Stejnou činnost tak můžeme provádět opakovaně, například s jinými hodnotami proměnných. V jazyce PL/SQL pracujeme s třemi druhy cyklů:


11

a) jednoduchý cyklus LOOP bez podmínky, ínky, ukončení se provádí příkazem EXIT b) cyklus FOR s čítačem c) cyklus WHILE s podmínkou na začátku cyklu Na následujících příkladech si ukážeme syntaktická pravidla pro používání jednotlivých druhů cyklů a ukázkové příklady kódu: a) Jednoduchý cyklus LOOP Syntaxe LOOP posloupnost_příkazů IF podmínka THEN EXIT END IF; ; END LOOP;

nebo zkráceně LOOP posloupnost_příkazů EXIT WHEN podmínka; END LOOP;

Příklad DECLARE v_pocet _pocet NUMBER := 0; 0 BEGIN LOOP v_pocet:=v_pocet+1 _pocet:=v_pocet+1; IF v_pocet >=100 THEN EXIT; END IF; END LOOP; DBMS_OUTPUT.PUT_LINE( DBMS_OUTPUT.PUT_LINE('v_pocet: ' || v_pocet ); END;


12

b) Cyklus FOR s čítačem Syntaxe FOR počítadlo IN [REVERSE REVERSE] min_hodnota..max_hodnota LOOP posloupnost_příkazů END LOOP;

Příklad BEGIN FOR v_citac IN 1..3 LOOP DBMS_OUTPUT.PUT_LINE('v_citac ' || v_citac ); DBMS_OUTPUT.PUT_LINE('v_citac: END LOOP; END;

Proměnnou použitou jako čítač v cyklu není třeba ani deklarovat ani inicializovat. Po každém průchodu cyklu se automaticky inkrementuje, v případě použití klauzule REVERSE dochází ke snižování čítače o 1.

c) Cyklus WHILE s podmínkou na začátku cyklu Syntaxe WHILE podmínka LOOP posloupnost_příkazů END LOOP;

Příklad DECLARE v_pocet _pocet NUMBER := 0; BEGIN WHILE v_pocet < 100 LOOP v_pocet:=v_pocet+1; END LOOP; DBMS_OUTPUT.PUT_LINE( DBMS_OUTPUT.PUT_LINE('v_pocet ' || v_pocet ); END;


13

3.9. Kurzory SQL příkazy typu SELECT standardně vrací na konzolovou aplikaci požadované hodnoty, tyto hodnoty jsou obsaženy v několika řádcích (můžeme také říci záznamech). Pro každý příkaz SQL vytvoří databázový server v pracovní paměti oblast, kam umístí výsledky dotazu a postupně je zasílá na výstupní zařízení. Mezi privátní pracovní oblasti v paměti patří i kurzory. Kurzory rozdělujeme na implicitní, které jsou vytvářeny automaticky databázovým serverem a explicitní, které jsou deklarovány programátorem. My se v dalších kapitolách seznámíme s oběma druhy. Explicitní kurzory Používáme-lili explicitní kurzory pro přístupy k datům v databázi, pak se celá operace skládá ze 4 kroků:

deklarace kurzoru (v deklarativní části) DECLARE CURSOR c_1

IS SELECT jmeno, id FROM ucitele;

otevření kurzoru OPEN c_1;

postupné procházení kurzoru a vybírání dat v cyklu FETCH c_1 INTO v_jmeno, v_id; v_id

uzavření kurzoru, čímž dojde k uvolnění alokované paměti CLOSE c_1;

Celý princip si můžeme ukázat na následujícím příkladu: DECLARE v_jmeno v_Id CURSOR c_1 BEGIN OPEN c_1; LOOP

ucitel.jmeno%TYPE; ucitel.Id%TYPE; IS SELECT jmeno, id FROM ucitele; ucitele

FETCH c_1 INTO v_jmeno, v_id; v_id DBMS_OUTPUT.PUT_LINE(v_jmeno|| BMS_OUTPUT.PUT_LINE(v_jmeno||', '||v_id); EXIT WHEN c_1%NOTFOUND; c_1 END LOOP; CLOSE c_1; END;


14

Na příkladu ladu můžeme vidět využití atributu %NOTFOUND, který bude vracet hodnotu TRUE v případě, kdy poslední příkaz FETCH žádný záznam nenačetl. Je proto využit pro testování konce cyklu. Příklady atributů pro testování stavu kurzoru jsou: %ROWCOUNT zjištění jištění pořadového čísla aktuálního záznamu (pokud nebyl vybrán žádný, je hodnota 0) %FOUND

pokud okud poslední příkaz FETCH načetl nějaký záznam, má atribut hodnotu TRUE.

%NOTFOUND

používá oužívá se pro zjišťování konce cyklu, cyklu negace %FOUND

%ISOPEN

pokud je kurzor or otevřen, má hodnotu TRUE

Použití: %ROWCOUNT

Záznamy Struktura typu záznam zapouzdřuje více položek i rozdílných datových typů. S pochopením a využitím záznamů bude snadnější práce s kurzory. Syntaxe deklarace záznamu: DECLARE TYPE IS RECORD ( [, …] );

Příklad konkrétní deklarace: DECLARE TYPE rec_ucitel IS RECORD ( jmeno ucitel.jmeno%TYPE, Id ucitel.Id%TYPE );

Nebo po zjednodušení jednodušení je možné deklarovat záznam se stejnými atributy, jako má databázová tabulka pomocí atributu %ROWTYPE . DECLARE rec_ucitel

ucitel%ROWTYPE;


15

Cyklus FOR s explicitním kurzorem Obecná syntaxe cyklu FOR s explicitním kurzorem je: FOR rec_promenna_typu_zaznam IN c_nazev_kurzoru LOOP … END LOOP;

Kurzor není třeba otevírat ani zavírat, ani cyklicky vybírat jednotlivé záznamy. Nejsou tedy potřeba příkazy OPEN, FETCH ani CLOSE, neboť je server provede implicitně. Příklad pak vypadá následovně:

DECLARE rec_ucitel ucitel%ROWTYPE; CURSOR c_1 IS SELECT jmeno, id FROM ucitele; ucitele BEGIN FOR rec_ucitel IN CURSOR c_1 LOOP DBMS_OUTPUT.PUT_LINE( BMS_OUTPUT.PUT_LINE(rec_ucitel.v_jmeno ||', ', '||rec_ucitel.v_id); END LOOP; END;

Všechny výše uvedené příklady neobsahovaly žádné parametry při volání kurzoru, což by bylo velice nepraktické, neboť často potřebujeme do kurzoru „dostat“ parametry z vnějšího bloku a tyto použít například v klauzuli WHERE. Obecná syntaxe deklarace eklarace explicitního kurzoru s parametry vypadá následovně: CURSOR [( , … )] IS ;

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


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?


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)


18


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


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.


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 EXCEPTION; BEGIN ; RAISE ; EXCEPTION WHEN THEN ; END;

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.


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 ) nebo všechny ostatní (OTHERS). Za klauzulí THEN je uveden kód, který se v daném případě provede.

Všeobecná syntaxe pro zpracování výjimek: EXCEPTION > THEN ; [WHEN [WHEN > OR [OTHERS THEN ;] ; END;

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.


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í.


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.


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 . SET TRANSAKCE nastaví transakční vlastnosti - například úroveň izolace. Autonomní transakce Autonomní transakce probíhají samostatně - tj. bez transakčního dohledu z nadřízené transakce. To znamená, že jestliže v rámci autonomní nebo hlavní transakcee použijeme potvrzení nebo odrolování, druhou transakci to neovlivní. Autonomní transakce se používají například pro zápis do do protokolu událostí, který si sami chceme vytvářet. To nám umožní monitorovat aktivitu bez ohledu na její výsledek (tím mějme na mysli sli hlavní transakci), a opačně, úspěch či selhání zápisu do protokolu (pracuje v autonomní transakci) nemá vliv na hlavní transakci. Pro vytvoření autonomní transakce se použije direktiva (pragma) AUTONOMOUS_TRANSACTION, která se umístí do deklarační oblasti oblasti bloku. Autonomní kód můžeme použít ve funkcích, procedurách, spouštích, balíčcích a objektových typech. Chování si můžeme vysvětlit na následujícím příkladu. CREATE TABLE at_test ( id NUMBER NOT NULL, popis VARCHAR2(50) NOT NULL ); INSERT INTO at_test (id, popis) VALUES (1, 'Popis pro 1'); INSERT INTO at_test (id, popis) VALUES (2, 'Popis pro 1'); Příkaz SELECT * FROM at_test; vrátí 2 řádky. David Žák IDAS2/7A – Jazyk PL/SQL - zpracování chyb, řízení transakcí

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;


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á.


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)


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


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.


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 [(<seznam parametrů>)] AS -- nepovinná deklarační sekce BEGIN -- výkonná sekce (povinná) EXCEPTION -- nepovinná sekce pro zpracování výjimek END []; David Žák IDAS2/7B – Procedury a funkce.

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 ; . Příkazy CREATE či DROP patří do skupiny DDL příkazů a proto je před jejich provedením automaticky provedeno potvrzení rozpracované transakce.

Vytváření funkce Základní syntaxe pro vytváření funkce je velice podobná syntaxi pro vytváření procedury: CREATE [OR REPLACE] FUNCTION FUNCTI > [(<seznam parametrů>)] RETURN AS -- nepovinná deklarační sekce BEGIN -- výkonná sekce (povinná) RETURN ; EXCEPTION -- nepovinná sekce pro zpracování výjimek END [];

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 ; . Příkaz RETURN se používá uvnitř funkce a vrací řízení běhu programu zpět do volajícího prostředí společně s návratovou hodnotou. V okamžiku provádění příkazu RETURN se výraz převede na typ specifikovaný v klauzuli RETURN v definici funkce. Pokud je více příkazů RETURN ve funkci, provede se jen jeden z nich - ten, na který se při běhu programu narazí nejdříve. Příkaz RETURN může být uveden i v zachytávači výjimky.. Pokud je příkaz přík RETURN uveden v proceduře (zapisuje se bez parametru), předá se řízení programu ihned do volajícího prostředí.

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í.


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;


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


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;


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.


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)


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


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ů.


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>.].< ma>.]. [<schéma>.].< ma>.]. [<schéma>.].< ma>.]. [<schéma>.].< ma>.]. David Žák IDAS2/8 – Balíky. Systémové balíky.

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í.


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í.


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í.


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.


7


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í.


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)


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


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


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 ] [FOR EACH ROW] ;

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í.


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;


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.


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í.


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 ... ;


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


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


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


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 tabulky> DISABLE CONSTRAINT ; ALTER TABLE tabulky> ENABLE CONSTRAINT ;
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


11


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í.


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)


12


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


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


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)


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


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)


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)


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


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.


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( AS )

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 THEN [WHEN THEN ] [ELSE ] END

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;


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( , , [, , ]... [,<defaultní_hodnota> <defaultní_hodnota>] )

kde je hodnota k porovnání, je hodnota, která je porovnávána s hodnotou výrazu, je hodnota, která je vrácena v případě, kdy =

obdobně pro další porovnání s a , <defaultní_hodnota> je e hodnota, kterou operátor vrátí v případě, kdy hodnota se nerovná žádné z uvedených hodnot , pokud defaultní hodnota není uvedena, vrátí v takovém případě operátor DECODE hodnotu NULL Příklad SELECT nazev, DECODE(dodavatel dodavatel_id, 10000, 'IBM', 10001,'Microsoft', 10002,'Hewlett Packard', 'Jiný') vysledek FROM DODAVATELE;

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))


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í).


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.


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)


7


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


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 AS (subquery_sql_statement) [, AS (subquery_sql_statement)… (subquery_sql_statement) ] SELECT FROM … ;


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


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


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ů


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ý' );


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.


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;


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


10


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ší.


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.


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


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/


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.


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: Gambardella, Gambardella, Matthew Matthew XML XML Developer's Guide Guide Computer Computer <price>44.95 publish_date>2000-10-01 <description> <description>An in-depth look at creating applications with XML. Ralls, Ralls, Kim Kim Midnight Midnight Rain Rain Fantasy Fantasy <price>5.95 2000-12-16 <description>A former architect battles corporate <description>A zombies, an evil sorceress, and her own childhood to become queen of the world. world.

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.


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.


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.


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 );


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( ' <jmeno>Tom <jmeno>Tomas <prijmeni>V <prijmeni>Vana 100000 ' ) ); INSERT INTO xml_tab(id,xml) xml_tab VALUES ( 2, XMLType( ' <jmeno>Petra <prijmeni>Bar <prijmeni>Barakova 70000 ' ) );

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;


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;


8

Dotaz vygeneruje následující výstup:

Obr. 1 - Výsledek volání funkce XMLELEMENT.


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é.


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??


9

Odkazy a další studijní prameny [1] Wikipedia [online]. 2011-02-23 23 [cit. 2011-04-27]. 2011 XML. Dostupné z WWW: . [2] DRAKE, Mark. Oracle [online]. 2008 [cit. 2011-04-20]. 2011 20]. Oracle Database 11g XML DB. Dostupné z WWW: http://www.oracle.com/technetwork/database/features/xmldb/11g.pdf> [4] KRYL, Milan. Milan Kryl web [online]. 2010 [cit. 2011-04-20]. 2011 XML a Oracle9i. Dostupné z WWW: . [5] Oracle [online]. 2008 [cit. 2011-04-21]. 2011 21]. XMLType Operations. Dostupné z WWW: .


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


10

1. blok Systémový katalog

Recommend Documents