Sv-lncs



?vod do práce s databázíV?stupyZná základní typy databázov?ch systém?Orientuje se v dostupn?ch databázov?ch produktechUmí instalovat databázov? server (Postgresql)Umí se prost?ednictvím databázového klienta p?ipojit k databáziRozumí základní terminologii rela?ních databází a ulo?ení dat v databázov?ch tabulkáchZná základní datové typy atribut?, které jsou pou?ívány v rela?ních databázíchUmí ?íst schematické znázornění datového modelu, rozumí problematice datového modelováníPod pojmem databáze rozumíme softwarovou aplikaci, jejím? primárním úkolem je zajistit jednak ukládání strukturovan?ch dat, jednak zajistit co mo?ná nejrychlej?í a nejefektivněj?í p?ístup k těmto dat?m. Od moderní databáze se takté? o?ekává, ?e zajistí bezpe?nost dat, jak z pohledu omezení p?ístupu k dat?m pouze pro oprávněné osoby, tak z pohledu zachování integrity dat p?i víceu?ivatelském vyu?ívání vlo?en?ch dat. Rozdělení databází podle typu Podle zp?sobu ukládání dat rozli?ujeme několik typ? databází:Hierarchická databáze – tato databáze je zalo?ená na hierarchickém modelu. Logické uspo?ádání dat má stromovou strukturu. Sí?ová databáze – tato databáze je zalo?ená na sí?ovém modelu, ve kterém jsou data logicky i fyzicky uspo?ádána jako uzly rovinného grafu. Ka?d? záznam m??e b?t spojen? s libovoln?m po?tem dal?ích záznam?. Objektová databáze – tato databáze je zalo?ena na objektech, jejich zapouzd?ení a dědi?nosti. Místo tabulek jsou zde ulo?eny p?ímo objekty, v?etně sv?ch vlastností, a místo ?ádk? se ukládají samotné instance objekt?. Rela?ní databáze – tato databáze zalo?ená na rela?ním modelu, v něm? jsou data logicky uspo?ádána do relací, tj. v?sledk? kartézského sou?inu nad doménami neboli mno?inami údaj?. Rela?ní databáze je zalo?ena na tabulkách, které obvykle chápeme tak, ?e uchovávají informace o relacích mezi jednotliv?mi záznamy. Hierarchické a sí?ové databáze se poprvé objevují v 60. letech minulého století na sálov?ch po?íta?ích. Jedním z prvních pr?kopník? databází byl Charles Bachman. Jedním z prvních databázov?ch systém? byl IMS, kter? byl vyvinut firmou IBM pro program letu na Měsíc Program Apollo.V roce 1970 za?ínají zve?ejněním ?lánku E. F. Codda první rela?ní databáze, které pohlí?ejí na data jako na tabulky. Kolem roku 1974 se vyvíjí první verze dotazovacího jazyka SQL. V?voj této technologie po 10 letech p?inesl v?konově pou?itelné systémy, srovnatelné se sí?ov?mi a hierarchick?mi databázemi. V 90. letech 20. století se za?ínaly objevovat první objektově orientované databáze, jejich? filozofie byla p?ebírána z objektově orientovan?ch jazyk?. Tyto databáze měly podle p?edpoklad? vytla?it rela?ní systémy. P?vodní p?edpoklady se v?ak nenaplnily a vznikla kompromisní objektově-rela?ní technologie.Databázové produktyV dne?ní době se m??eme setkat s celou ?adou databázov?ch systém? od mnoha v?robc?. ?kála je velmi ?iroká od open source produkt? a? po velmi drahé komer?ní produkty. Pokud stojíme p?e problémem, kter? produkt zvolit, měli bychom kromě dostupn?ch finan?ních prost?edk? definovat na?e po?adavky:Opera?ní systém, na kterém budeme databázov? systém provozovatPo?et u?ivatel?, kte?í budou k dat?m p?istupovatObjem zpracovávan?ch datPo?adavky na v?konPo?adavky na dostupné funkcePo?adavky na dostupnost a bezpe?nost datPo?adavky na technickou podporu dodavatele Nemusí v?dy platit, ?e komer?ní produkty jsou kvalitněj?í ne? produkty zdarma, pro men?í projekty jsou open-source produkty zcela dosta?ující, komer?ní produkty najdeme v oblasti kriticky d?le?it?ch systém?, kde jsou po?adavky na vysok? v?kon a vysokou dostupnost a spolehlivost. Následující abecedně set?íděn? p?ehled produkt? není zdaleka kompletní, nicméně obsahuje nejroz?í?eněj?í databázové produkty.Cache - Nejroz?í?eněj?í objektová databáze, která umo?ňuje k dat?m p?istupovat také pomocí SQL dotaz?. Nabízí transak?ní zpracování, masivní ?kálovatelnost, real-time anal?zy a webov? p?ístup k databázi. P?ipravená pro Javu a .NET a certifikovaná na Red Hat a SUSE Enterprise Linux.DB2 - komer?ní rela?ní databáze firmy IBM pro aplikace s vysok?mi nároky na dostupnost a zabezpe?ení dat. Firebird - p?vodně známá jako databáze Interbase, která byla vyvíjená firmou Borland, následně uvolněn její zdrojov? kód dále vyvíjena open source komunitou jako rela?ní databáze Firebird. Volně dostupná kompaktní databáze instalovatelná na ?irokém spektru opera?ních systém?, s podporou transak?ního zpracování, ulo?en?ch procedur i trigger?. Ve srovnání s POSTGRESQL men?í nabídka funkcí, v?hodou je snadné zálohování a p?enos databázového souboru (ve?kerá data jsou ukládána v jednom souboru). ObjectDB - Volně dostupná (pro osobní a nekomer?ní pou?ití) objektová databáze pro Javu. M??e pracovat jak v klient-server, tak v embedded re?imu. Objectivity/DB - Jedná se o distribuovanou (data mohou b?t transparentně replikována na r?zn?ch serverech), objektovou databázi s nej?ir?ím v?běrem API – pro C++, Javu, Python, Smalltalk a obecn? ODBC. K dipozici je i 64bitová verze. Stáhnout lze 60denní trial, nebo plnou placenou. ObjectStore - Objektová databáze, která m??e b?t pou?ita v C++, nebo Javě. Nabízí robustní systém cachování, transak?ní zpracování, online zálohy a replikace, ?kálování nebo podporu cluster?. K dispozici je testovací embedded verze omezená na jeden proces, p?ípadně placená plná verze.MS ACCESS - rela?ní databázová aplikace firmy Microsoft ur?ená pro jednou?ivatelské aplikace pro prost?edí opera?ního systému Widows, sou?ást kancelá?ského balíku MS Office.MS SQL - komer?ní rela?ní databáze firmy Microsoft ur?ená pro platformu MS Windows a související Microsft technologie (.NET).MySQL - open source rela?ní databáze, kterou nedávno p?evzala firma ORACLE. Je velmi roz?í?ená jako databáze pro webové aplikace. Doká?e zpracovávat velké objemy dat, méně vhodná je pro aplikace vy?adující efektivní transak?ní zpracování. ORACLE - komer?ní rela?ní databáze s podporou objektově rela?ní technologie. Pat?í ke ?pi?ce v oblasti databázov?ch systém? se ?irou ?kálou specificky zamě?en?ch modul? (anal?za textu, datové sklady, geografická data a dal?í). Databáze je certifikovaná pro systémy MS Windows i vybrané distribuce UNIX a LINUX. Zdarma je dostupná okle?těná a limitovaná verze ORACLE Express. POSTGRESQL - open source alternativa k databázi ORACLE, volně dostupná pro ?irokou ?álu opera?ních systém?. Nabízí ?adu SQL funkcí, vlastní procedurální jazyk PgSQL pro vytvá?ení u?ivatelsk?ch procedur a trigger?. P?ístup k databáziDatabázové systémy lze rozdělit na jednou?ivatelské a víceu?ivatelské. Ty první jsou obvykle pou?ívány pro lokální zpracování dat nebo jako úlo?n? systém pro lokální aplikace. Nepo?ítá se s tím, ?e by data vyu?ívalo více u?ivatel? sou?asně. P?íkladem m??e b?t MS ACCESS. Tato aplikace v sobě obsahuje jak datové úlo?i?tě a SQL engine, tak doplňující nástroje pro tvorbu report? ?i elektronick?ch formulá??. V?e je dostupné v jedné aplikaci. Oproti tomu víceu?ivatelské databáze jsou navr?eny tak, aby dokázaly zpracovat sou?asnou manipulaci dat několika u?ivatel?. Jsou rozděleny na tzv. serverovou ?ást a klienta. Server je aplikace, která bě?í nep?etr?itě na vybraném po?íta?i, zaji??uje ukládání dat a vy?izuje SQL p?íkazy klient?. Klient je samostatná aplikace, pomocí které jsme schopni p?ipojit se k databázovému serveru a ovládat ho pomocí p?íkaz?. Klient nám také zobrazuje z databáze získaná data. Klientsk?ch aplikací k jednomu databázovému produktu je ?asto více. Li?í se obvykle u?ivatelsk?m komfortem. ?ádkov? klient má jen jednoduché u?ivatelské prost?edí v podobě p?íkazového ?ádku, kde pí?eme SQL dotazy, které klient odesílá serveru. Získaná data klient jednodu?e vypí?e na obrazovku nebo zapí?e do souboru. Grafick? klient pak umo?ňuje prohlí?ení databáze pomocí menu, SQL p?íkazy je mo?né skládat pomocí pr?vodc?, data lze exportovat do r?zn?ch formát?.Z v??e uvedeného vypl?vá, ?e p?ed tím ne? za?neme s databází pracovat, musíme bu? instalovat databázov? server, nebo získat pot?ebné informace o tom, kde je server provozován. Druh?m krokem je instalace databázového klienta na ná? po?íta?. Pokud instalujeme databázov? server, je obvykle sou?ástí instala?ního balí?ku i instalace klienta. V p?ípadě POSTGRESQL sta?í na opera?ním systému windows spustit instala?ní soubor pro vybranou verzi (nap?. postgresql-9.2.4-1-windows-x64.exe), kter? lze stáhnout na stránkách projektu . Kromě serveru se nainstaluje ?ádkov? klient psql a grafick? klient pgAdmin.?P?ipojení k POSTGRESQL K databázi POSTGRESQL se v prost?edí MS Windows p?ipojíme spu?těním skriptu runpsql.bat, kter? sou?ástí instalace klienta. Skript se nás postupně dotá?e na:Jméno nebo IP adresu serveru, kde bě?í databázov? server. V?chozí hodnota je ná? po?íta? (localhost).Jméno databáze na daném serveru. Na databázovém serveru m??eme mít ulo?ena data více nezávisl?ch projekt?. P?i p?ipojování specifikujeme, se kterou datovou strukturou chceme pracovat. V?chozí hodnota je postgres, co? je v?chozí datová struktura (databáze) na databázovém serveru POSTGRESQL.Port, na kterém je databázová aplikace na daném serveru dostupná. V?chozí hodnota je 5432. P?ihla?ovací login. V?chozí ú?et je postgres, co? je hlavní administrátorsk? ú?et databázového serveru. Heslo. V?chozí heslo k ú?tu postgres volíme během instalace serveru.Pokud se p?ipojujeme k jinému ne? lokálnímu serveru, musí nám administrátor databáze v??e uvedené údaje poskytnout. Pokud se k databázovému serveru p?ipojujeme p?es klienta pgAdmin, zadáváme stejné údaje, ale v grafickém re?imu. V okně Strom objekt? vybereme nejprve server, zadáme heslo a v následujícím kroku zvolíme databázi, se kterou chceme pracovat. Pokud se chceme p?ipojit poprvé ke vzdálenému serveru, zvolíme v menu Soubor => P?idat server. Obrázek SEQ Obrázek \* ARABIC 1 - Aplikace pgAdminObrázek SEQ Obrázek \* ARABIC 2 - pgAdmin - registrace nového serveru Vyplníme pot?ebné údaje, polo?ku Slu?ba necháváme obvykle prázdnou. Jakmile jsme p?ipojeni k serveru, zvolíme kliknutím databázi, se kterou chceme pracovat. Seznam databází obsahuje minimálně v?chozí databázi postgres. Novou databázi vytvo?íme kliknutím prav?m tla?ítkem na Databáze a v?běrem polo?ky Nová databáze. V dialogovém okně sta?í vyplnit jméno nové databáze, ostatní polo?ky je mo?né nechat ve v?chozím stavu. Alternativní cestou je vytvo?ení databáze pomocí SQL p?íkazu. V ?ádkovém klientu psql m??eme p?íkazy SQL zadávat hned po p?ipojení, v pgAdmin nejprve klikneme na vybranou databázi (postgres) a v hlavní li?tě klikneme na SQL ikonu. P?íkaz pro vytvo?ení databáze v POSTGRESQL vypadá následovně:CREATE DATABASE moje_db WITH ENCODING='UTF8' CONNECTION LIMIT=-1;Název nové databáze je "moje_db", encoding ozna?uje znakovou sadu databáze (UTF8 je univerzální sada podporující ve?keré světové jazyky), connection limit uvádí maximální po?et sou?asně p?ipojen?ch u?ivatel? (-1 zna?í neomezen? po?et). P?ipojení k ORACLEPro p?ipojení k databázi ORACLE opět pot?ebujeme nejprve instalovat klienta databáze. Klienta je mo?né po registraci zdarma stáhnout na . Instala?ní pr?vodce nabízí několik variant instalace. Pro p?ipojení k databázi pot?ebujeme instalovat bu? ?ádkov? klient sqlplus nebo grafick? klient sqldeveloper. Klient sqlplus se hodí hlavně pro automatické spou?tění hotov?ch skript? a sestav, pro dolování dat a testování SQL p?íkaz? je mnohem vhodněj?í sqldeveloper, proto se dále zamě?íme na něj. Zp?sob? p?ipojení k ORACLE databázi skrze sqldeveloper je několik typ? a se správn?m v?běrem nám obvykle musí poradit administrátor databáze. Pot?ebujeme znát kromě loginu a hesla bu? tzv. network alias, nebo IP adresu serveru, port a tzv. SID. Pokud chceme pou?ívat network alias, musíme mít na svém po?íta?i nakonfigurován soubor tnsnames.ora, kter? najdeme ve slo?ce klienta v podadresá?i /network/admin. Tento soubor obsahuje seznam ORACLE databází, ke kter?m se m??eme p?ipojit. Soubor nám musí poskytnout administrátor databáze. Pokud máme mo?nost p?ipojovat se pomocí Network alias, klikneme v SQLDeveloper v zálo?ce Connections na zelené plus a vytvo?íme nové p?ipojení. Definujeme zde název p?ipojení, u?ivatelské jméno, heslo a v Connection type vybereme mo?nost TNS. Pokud máme správně konfigurovan? soubor tnsnames.ora, m??eme v roletce Network alias vybrat cílovou databázi. Tla?ítkem Save definici p?ipojení ulo?íme, tla?ítkem Connect se p?ipojíme k databázi. Obrázek SEQ Obrázek \* ARABIC 3 - ORACLE Sqldeveloper - konfigurace p?ipojení k databázi p?es TNS záznamPokud nem??eme vyu?ít tuto variantu p?ipojení, zvolíme variantu Connection type Basic, vyplníme Hostname a polo?ku SID. Obrázek uvádí variantu, pokud se p?ipojujeme k lokální instalaci ORACLE serveru ve verzi Express. Obrázek SEQ Obrázek \* ARABIC 4 - ORACLE Sqldeveloper - konfigurace p?ímého p?ipojení k databáziPoznámka: Polo?ku Role ponecháváme na mo?nosti default kromě p?ípadu, ?e se p?ipojujeme pod administrátorsk?m ú?tem SYS, pro kter? je nutné zvolit roli SYSDBA. Oproti PostgreSQL v databázi ORACLE jako u?ivatel nevytvá?íme vlastní databázi. Po p?ihlá?ení máme k dispozici vlastní u?ivatelské schéma (odpovídá "databázi" v PostgreSQL), kde vytvá?íme vlastní databázové objekty. Pokud jsou na serveru uchovávána data více nezávisl?ch aplikací, bude mít ka?dá tato aplikace vlastní u?ivatelsk? ú?et a s ním spojené schéma. P?istupovat ze svého ú?tu do jiného schématu m??eme v p?ípadě, ?e nám vlastník schématu udělí pat?i?ná oprávnění. Datová struktura rela?ní databázeRela?ní databáze ukládají data do tabulek, na které se m??eme dívat jako na dvojrozměrné pole nebo jako na matici. Pojem "rela?ní" pochází z anglického "relation", co? je termín z rela?ní algebry, kde ozna?uje v?sledek kartézského sou?inu nad doménami. P?esto?e rela?ní databáze vycházejí z rela?ní algebry, její znalost pro u?ívání databáze není nikterak nutná. V této publikaci se termínu relace a doména budeme vyh?bat, místo toho se podíváme na rela?ní databáze z praktického pohledu. Tabulku rela?ní databáze tvo?í 1 a? n sloupc?, kde ka?d? sloupec p?edstavuje jeden atribut ukládaného objektu. Záznamy jednotliv?ch objekt? pak tvo?í ?ádky tabulky. Objektem m??e b?t cokoliv z reálného světa, co chceme popsat a ulo?it v tabulce, nap?íklad student, u?itel, pacient, vy?et?ení, atd. Tabulku vytvo?íme tak, ?e definujeme p?íkaz SQL jazyka, ve kterém specifikujeme název tabulky a jednotlivé sloupce (atributy). U ka?dého sloupce uvádíme sadu parametr?, p?i?em? povinné jsou dva: jméno sloupce a datov? typ. Jména databázov?ch objekt?P?i práci s databází pojmenováváme nejen tabulky a jejich sloupce, ale ve?keré vytvá?ené objekty. Limity pro pojmenovávání se li?í mezi databázov?mi systémy, hlavně pokud jde o maximální délku jména. Pokud se chceme vyhnout problém?m se jmény, dodr?ujeme následující pravidla:pou?íváme pouze písmena anglické abecedy a ?íslicejméno za?íná v?dy písmenemmísto mezer pou?íváme znak podtr?ítka "_"pou?íváme srozumitelná jména, ale sna?íme se omezit jejich délku (max. 30 znak?)Databáze standardně nerozli?ují velikost písmen v názvech a klí?ov?ch slovech, doporu?uje se proto pou?ívat pro jména bu? pouze malá písmena, nebo jen velká. Datové typyDatov? typ nám definuje, jaké hodnoty budeme schopni do daného sloupce ukládat. Základní datová typy jsou text, ?íslo, datum a LOB. LOB je specifick? datov? typ, kter? pou?ijeme v p?ípadě, ?e chceme do tabulky ukládat objemná data jako je obrázek, hudba, video nebo velmi dlouh? text. Od těchto základních typ? odvozuje ka?d? databázov? systém své specifické typy a podtypy. Nejpou?ívaněj?í datové typy v systému ORACLE a PostgreSQL popisuje tabulka.Tabulka SEQ Tabulka \* ARABIC 1 - Datové typyObecn? typORACLEPOSTGRESQLtextVARCHAR2 (max. délka)VARCHAR (max. délka)?ísloNUMBER (?íslic, des. míst)NUMERIC (?íslic, des.míst)DatumDate, TimestampDate, TimestampLOBBLOB, CLOBByteaU textového sloupce definujeme maximální délku textu (1 a? limit databáze), kter? budeme schopni do sloupce ulo?it. U ?íselného sloupce definujeme maximální po?et ?íslic (1 a? limit databáze) a po?et desetinn?ch míst (0 pro celá ?ísla). Pro LOB definuje ORACLE podtypy BLOB pro ukládání binárních dat (obrázek, video, hudba) a CLOB pro ukládání dlouhého textu (del?í ne? 4096 znak?). Pokud se pokusíme do sloupce tabulky ulo?it data neodpovídající specifikovanému datovému typu, ohlásí databáze chybu a data se nevlo?í. Kontrola na správn? datov? typ je jedna ze základních kontrol, které databáze nabízejí. P?esto?e bychom mohli v?echna data ukládat jako datov? typ text nebo dokonce LOB, nebylo by to efektivní. Jednak jsou ?ísla efektivněji ukládána ne? text, jednak pro ?ísla a datumy nabízí databáze ?adu funkcí, které pro textové polo?ky nem??eme pou?ít. Datov? modelDatabázi obvykle netvo?í jen jedna tabulka, ale obvykle několik, desítky ?i stovky tabulek. Struktura tabulek databáze, tzv. datov? model, odrá?í modelovanou skute?nost. Tabulky jsou mezi sebou provázány pomocí klí??. Klí?em ozna?ujeme 1 a? n sloupc? tabulky. Rozli?ujeme 2 typy klí??: primární klí? a cizí klí?. Ka?dá tabulka by měla obsahovat právě jeden primární klí? a 0 a? N cizích klí??. Primární klí? musí b?t definován tak, aby obsah sloupce nebo sloupc?, které ho tvo?í, byl v ka?dém ?ádku tabulky unikátní. Jin?mi slovy hodnota primárního klí?e, která je ulo?ena v jednom ?ádku, se nesmí objevit v ?ádném dal?ím ?ádku. Ochranu p?ed vlo?ením duplicity do primárního klí?e zaji??uje databáze. Pomocí cizích klí?? se definují vazby mezi tabulkami. Vazby (relationship) se definují mezi dvojicí tabulek a mohou b?t t?ech typ?:?1:11:nm:nPoměry odpovídají po?tu ?ádk?, které si ve vázan?ch tabulkách odpovídají. Vazba 1:1 uvádí, ?e ka?dému ?ádk? z tabulky A se vá?e právě jeden ?ádek z tabulky B. Tato vazba je nejméně ?astá, proto?e obvykle není d?vod rozdělovat popisovan? objekt do dvou tabulek, místo toho vytvo?íme jednu velkou tabulku. D?vodem rozdělení m??e b?t limit databáze na po?et sloupc? jedné tabulky. V tom p?ípadě mají obě tabulky stejn? primární klí?.?Vazba 1:n je nej?astěj?í vazba, pomocí ní definujeme pod?ízen? vztah tabulky B k tabulce A. Jednomu ?ádku tabulky A odpovídá 1 a? N ?ádk? tabulky B. O tabulce A mluvíme jako o nad?ízené nebo rodi?ovské tabulce, o tabulce B jako o závislé nebo dětské tabulce. Jako klasick? p?íklad vazby 1:n je matka a její děti. Matka m??e mít více dětí, ale ka?dé dítě má právě jednu matku. Tato vazba se v rela?ní databázi modeluje tak, ?e primární klí? rodi?ovské tabulky vlo?íme do tabulky dětské, kde o něm mluvíme jako o cizím klí?i. Dětská tabulka má tak jak vlastní primární klí? tak cizí klí? z rodi?ovské tabulky. Rodi?ovská tabulka z?stává nezměněna. Kromě vlo?ení cizího klí?e do dětské tabulky definujeme omezení tzv. constraint, ?ím? databází sdělíme vytvo?ení vazby. Databáze následně zajistí, ?e ke ka?dému ?ádku v dětské tabulce existuje právě jeden ?ádek v rodi?ovské tabulce. P?i vkládání dat musíme za?ít vlo?ením ?ídícího ?ádku a teprve následně vlo?it ?ádek nebo ?ádky to tabulky dětské. Naopak pokud data ma?eme, musíme odstranit nejd?ív záznam v dětské tabulce a teprve následně v tabulce ?ídící.??T?etím typem vazby mezi tabulkami je m:n. P?íkladem této situace je nap?íklad vztah mezi u?iteli a studenty. Ka?d? u?itel u?í více student?, ale zároveň ka?d? student nav?těvuje hodiny několika u?itel?. Pokud chceme namodelovat tuto vazbu, musíme vytvo?it t?etí? vazební tabulku, ve které skombinujeme primární klí?e tabulky u?itel? a student?. Vazební tabulka tak obsahuje dva cizí klí?e, které obvykle tvo?í dohromady primární klí? této tabulky. Tímto zp?sobem dekomponujeme vazbu m:n na dvě vazby 1:n. Nadefinováním tabulek a vazeb mezi nimi vzniká datov? model databáze. Jeho schematick? nákres ukazuje obrázek. Obdélníky odpovídají jednotliv?m tabulkám s jejich atributy, ?áry pak zobrazují vazby mezi nimi. Podle zakon?ení ?ar poznáme ?ídící tabulku od tabulky dětské, u dětské tabulky je zakon?ení rozvětvené. Někdy je u zakon?ení p?ímo p?ipojen popisek, kter? uvádí, kolik dětsk?ch záznam? je pro jeden ?ídící záznam povoleno.?S návrhem datové struktury databáze (datového modelu) souvisí pojem normalizace. Pod pojmem normalizace rozumíme proces zjednodu?ování a optimalizace navr?en?ch struktur databázov?ch tabulek. Hlavním cílem je navrhnout databázové tabulky tak, aby obsahovaly minimální po?et redundantních dat. Správnost navr?ení struktur lze ohodnotit některou z následujících normálních forem.Nultá normální forma (0NF) - tabulka v nulté normální formě obsahuje alespoň jeden sloupec (atribut), kter? m??e obsahovat více druh? hodnot.První normální forma (1NF) - tabulka je v první normální formě, pokud v?echny sloupce (atributy) nelze dále dělit na ?ásti nesoucí nějakou informaci, neboli prvky musí b?t atomické. Jeden sloupec neobsahuje slo?ené hodnoty.Druhá normální forma (2NF) - tabulka je v druhé normální formě, pokud obsahuje pouze atributy (sloupce), které jsou závislé na celém klí?i.T?etí normální forma (3NF) - tabulka je ve t?etí normální formě, pokud neexistují ?ádné závislosti mezi neklí?ov?mi atributy (sloupci).?tvrtá normální forma (4NF) - tabulka je ve ?tvrté normální formě, pokud sloupce (atributy) v ní obsa?ené popisují pouze jeden fakt nebo jednu souvislost.Pátá normální forma (5NF) - tabulka je v páté normální formě, pokud by se p?idáním libovolného nového sloupce (atributu) rozpadla na více tabulek.Takto zní oficiální definice normálních forem, v praxi se aplikují první t?i, kdy se sna?íme v modelu pro ka?dou tabulku definovat primární klí? a sloupce definovat atomicky, tedy tak, aby obsahovaly dále smysluplně nedělitelnou informaci. Jako p?íklad rozdělení na atomické prvky m??eme uvést nap?íklad polo?ku bydli?tě, kterou bychom měli správně rozdělit na atributy (sloupce) ulice, ?íslo domu, město a PS?. Cvi?ení:Nainstalujte si na sv?j po?íta? server POSTGRESQL pro vá? opera?ní systém Vytvo?te si vlastní databázi Student. Základy SQLV?stupyZná základní syntax jednoduch?ch SQL dotaz?Umí pomocí SQL prohlí?et a zji??ovat strukturu a obsah databázov?ch tabulekUmí pomocí SQL vkládat a mazat záznamy v databáziJe schopen tvo?it sumariza?ní p?ehledy dat nad jednou tabulkouRozumí pojmu databázová transakceZná základní p?íkazy pro tvorbu, změnu a ru?ení databázov?ch objekt?Skupiny p?íkaz?SQL jazyk se v?razně li?í od klasick?ch programovacích procedurálních jazyk?. P?i ?e?ení úlohy ne?íkáme databází, jak má po?adovan? úkol splnit, ale pouze formulujeme p?íkaz a specifikujeme na?e po?adavky. P?íkazy, které je schopna databáze zpracovat jsou 4 skupiny. Manipulaci s daty obstarávají p?íkazy ze skupiny DML (Data manipulation language). Pro správu datov?ch struktur a objekt? jsou ur?eny DDL (Data definition language) p?íkazy. T?etí a ?tvrtou skupinou jsou p?íkazy pro ?ízení transakcí a oprávnění jednotliv?ch databázov?ch u?ivatel?.DML p?íkazyNejprve se zamě?íme na DML p?íkazy. Základní DML p?íkazy jsou 4 a umo?ňují provádět následující manipulaci s daty.SELECT - v?běr a zobrazení datINSERT - Vkládání datUPDATE - Změna datDELETE - Smazání datSELECTJazyk SQL je velice snadn?, co se t?ká slovníku neboli klí?ov?ch slov. Nejjednodu??í SQL p?íkaz obsahuje pouhá 2 klí?ová slova.SELECT?*?FROM?jmeno_tabulkyPrvním slovem je jeden ze 4 uveden?ch p?íkaz? (SELECT), následuje operátor hvězdi?ka (*), kter?m ?íkáme, ?e chceme získat v?echny sloupce tabulky. Druhé klí?ové slovo (FROM) uvozuje název tabulky, ze které chceme data získat. Místo operátoru * m??eme zapsat názvy sloupc? oddělené ?árkou. SELECT jmeno_sloupce1, jmeno_sloupce2 FROM jmeno_tabulky Po spu?tění toho dotazu nám databáze zobrazí v?echny ?ádky zvolené tabulky. Pokud chceme zobrazení omezit jen na vybrané ?ádky, pou?ijeme dal?í klí?ové slovo WHERE a specifikujeme omezující podmínku. Podmínku tvo?í název sloupce, operátor a p?ípadně konstanta. Dotaz pak má podobu SELECT * FROM jmeno_tabulky WHERE jmeno_sloupce1 = 10 nebo SELECT * FROM jmeno_tabulky WHERE jmeno_sloupce1 = jmeno_sloupce2 První dotaz zobrazí pouze ?ádky, které mají ve sloupci jmeno_sloupce1 ulo?enu 10, druh? dotaz zobrazí pouze ?ádky, které obsahují stejnou hodnotu ve sloupci jmeno_sloupce1 a jmeno_sloupce2. Podmínek je mo?né specifikovat více, oddělují se pomocí logick?ch operátor? AND a OR.Zobrazen? seznam je mo?né nechat set?ídit dle vybran?ch sloupc?. Jejich seznam specifikujeme na konci dotazu za klí?ové slovo ORDER BY. V?chozí je t?ídění vzestupně, pokud chceme t?ídit podle některého sloupce sestupně, doplníme klí?ové slovo DESC za název sloupce. Názvy sloupc? opět oddělujeme ?árkou.SELECT * FROM jmeno_tabulky WHERE jmeno_sloupce1 = jmeno_sloupce2ORDER BY jmeno_sloupce DESC, jmeno_sloupce2Kromě v?pisu ulo?en?ch dat umo?ňuje p?íkaz SELECT získat základní sumární údaje o obsahu tabulek. K tomuto slou?í agrega?ní funkce, které pou?ijeme místo nebo v kombinaci s názvem sloupce. Pro zji?tění po?tu ?ádk? pou?ijeme funkci COUNT. Tuto funkci lze pou?ít ve t?ech podobách:SELECT COUNT(*), COUNT(sloupec1), COUNT (DISTINCT sloupec1) FROM tabulka1První varianta s hvězdi?kou spo?ítá celkov? po?et ?ádk? v tabulce1, druhá forma spo?ítá ?ádky, které ve sloupci sloupec1 obsahují hodnotu (jsou neprázdné), t?etí forma s klí?ov?m slovem DISTINCT spo?ítá po?et unikátních hodnot ve sloupci sloupec1. Mějme tabulku se t?emi ?ádky:? sloupec1sloupec2Ano1Ano23V?sledek dotazu bude 3, 2, 1, tedy 3 ?ádky celkem, 2 neprázdné ?ádky, 1 unikátní hodnota ('Ano').?Mezi agrega?ní funkce pat?í dále MAX, MIN, AVG, SUM, které pro dan? sloupec vypo?ítají maximum, minimum, aritmetick? pr?měr a celkov? sou?et. Minimum a maximum lze pou?ít pro v?echny datové typy (u textov?ch sloupc? vrací funkce první a poslední záznam dle abecedy), pr?měr a sumaci lze po?ítat pouze nad ?íseln?mi datov?mi typy.?? INSERTDal?ím p?íkazem z rodiny DML je p?íkaz INSERT pro vkládání záznam? do tabulky. Syntaxe tohoto p?íkazu je:INSERT INTO tabulka1 (sloupec1, sloupec2) VALUES (hodnota1, hodnota2)Tento p?íkaz vlo?í do tabulky tabulka1 jeden ?ádek, p?i?em? sloupec1 bude obsahovat hodnotu hodnota1 a sloupec2 hodnotu2. Po?et sloupc? musí odpovídat po?tu hodnot. Pokud tabulka obsahuje je?tě dal?í sloupce, bude jejich hodnota bu? NULL nebo bude rovna v?chozí hodnotě sloupce. V?chozí hodnoty sloupc? je mo?né definovat p?i vytvá?ení tabulky. P?ipomeňme, ?e textové hodnoty je nutné uvádět uzav?ené v jednoduch?ch apostrofech ('textová hodnota'). P?íkaz INSERT je mo?né kombinovat s p?íkazem SELECT, pokud chceme záznamy místo zobrazení ulo?it do tabulky. V tomto p?ípadě vypadá syntaxe následovně:INSERT INTO tabulka (sloupec1, sloupec2)SELECT sloupec3, sloupec4 FROM tabulka2V tomto p?ípadě je klí?ové slovo VALUES nahrazeno p?íkazem SELECT a v?sledkem je, ?e se v?echny ?ádky vybrané p?íkazem SELECT z tabulky2 ulo?í do tabulky1. Po?et sloupc? a jejich datové typy v ?ásti SELECT a INSERT musí odpovídat.UPDATEPokud chceme změnit hodnoty záznam? ulo?en?ch v tabulce, pou?ijeme p?íkaz UPDATE. Jeho syntaxe je následující:UPDATE tabulka SET sloupec = hodnota, sloupec2 = hodnota2Tento p?íkaz změní hodnotu sloupce na definovanou hodnotu ve v?ech ?ádcích tabulky. Místo konstantní hodnoty je mo?né pou?ít název jiného sloupce ?i slo?itěj?í v?raz (v?po?et). Klí?ové slovo SET je povinné, jednotlivá p?i?azení jsou oddělena ?árkou. ?astěji ne? měnit v?echny ?ádky tabulky pot?ebujeme měnit hodnoty jen vybran?ch ?ádk?. V tomto p?ípadě musíme doplnit podmínku za klí?ové slovo WHERE.UPDATE tabulka SET sloupec = hodnota WHERE sloupec = hodnota2Tato varianta změní hodnotu sloupce pouze u těch ?ádk?, kde hodnota sloupce odpovídá hodnotě2. Stejně jako v p?ípadě restrikce ?ádk? v p?íkazu SELECT je mo?né dal?í podmínky p?ipojovat p?es operátory AND nebo OR. Pokud v?sledné podmínce neodpovídá ?ádn? ?ádek, nedojde k chybě, pouze není změněn ?ádn? záznam. SQL dovede v jednom p?íkaze zaměnit hodnotu dvou sloupc?, co? standardní procedurální jazyky nedovedou. Je tedy mo?né napsat: UPDATE tabulka SET sloupec1 = sloupec2, sloupec2=sloupec1Tento dotaz korektně zamění hodnoty sloupc?.DELETEPosledním ze základních DML p?íkaz? je p?íkaz DELETE, kter? odstraní záznamy z tabulky. Základní syntaxe je: DELETE FROM tabulka, kter? sma?e v?echny záznamy v tabulce a jde tedy o velmi destruktivní p?íkaz. Pokud chceme smazat pouze vybrané záznamy, je nutné stejně jako v p?ípadě p?íkaz? UPDATE specifikovat podmínku za klí?ové slovo WHERE.Opomenutí uvedení podmínky za p?íkazy UPDATE a DELETE je ?astá za?áte?nická chyba, která má velmi neblahé d?sledky. Proto je nutné s těmito p?íkazy zacházet v?dy velmi opatrně a podmínku testovat nejprve v kombinaci s p?íkazem SELECT.Databázové transakceDML p?íkazy lze zapouzd?it do tzv. transakcí. V rámci transakce m??eme provést několik DML p?íkaz?, ale teprve po posledním z nich rozhodneme, zda v?echny provedené změny budou platné nebo ne. Databáze zajistí, ?e budou provedeny bu? v?echny změny, nebo ?ádná. Pro ?ízení transakcí existují 2 základní p?íkazy. P?íkaz COMMIT transakci potvrdí, p?íkaz ROLLBACK transakci zru?í. Transakce za?íná spu?těním prvního DML p?íkazu. Změny prováděné v rámci transakce nejsou p?ed spu?těním COMMIT mimo transakci viditelné, jin?mi slovy, ostatní u?ivatelé vidí stále stejná data jako p?ed zahájením transakce. Poznámka: transak?ní re?im je nutné v někter?ch databázích ?i v databázov?ch klientech explicitně aktivovat, nap?íklad PostgreSQL bě?í defaultně v autocommit re?imu, kdy po ka?dém DML p?íkazu se automaticky provede COMMIT. Toto chování lze změnit pomocí?SET AUTOCOMMIT ?= offDDL p?íkazyPomocí p?íkaz? DDL vytvá?íme, měníme a ru?íme databázové objekty. Slou?í k tomu p?íkazy CREATE, ALTER a DROP. Syntaxe těchto p?íkaz? se li?í pro jednotlivé databázové objekty a rozdíly jsou i mezi databázemi. Uká?eme se proto jen základní syntax pro tvorbu a ru?ení databázov?ch tabulek. Tabulku vytvo?íme pomocí p?íkaz? CREATE TABLE. Následuje v závorkách v??et sloupc? a jejich specifikace. Sloupce jsou odděleny ?árkou, u ka?dého sloupce je nutné definovat minimálně jeho datov? typ. P?íkaz pro vytvo?ení jednoduché tabulky v databázi ORACLE m??e vypadat takto:CREATE TABLE tabulka1 (prijmeni VARCHAR2(30),datum_narozeni DATE,hmotnost NUMBER(3))VARCHAR2 je ORACLE datov? typ ur?en? pro ukládání text? s variabilní délkou (v závorce je uvedena maximální délka), DATE slou?í pro ukládání data a ?asu s p?esností na sekundy, NUMBER je datov? typ pro ukládání ?ísel, jejich? maximální velikost a po?et desetinn?ch míst ur?uje parametr/y v závorce.Názvy datov?ch typ? se u databází mírně li?í, stejná tabulka pro databázi PostgreSQL by vypadala následovně:CREATE TABLE tabulka1 (prijmeni VARCHAR(30),datum_narozeni TIMESTAMP,hmotnost NUMERIC(3)) Tabulku zru?íme p?íkazem DROP TABLE tabulka1 . Pozor, zru?ením tabulky nenávratně p?ijdeme o data, která byla v tabulce ulo?ena. Zachránit nás pak m??e u? jen záloha dat nebo specifické funkce konkrétní databáze ( funkce flashback v p?ípadě ORACLE). DDL p?íkazy nelze za?adit do transakce, provádějí se okam?itě a nevratně. Navíc databáze potvrdí v?echny na?e dosud nepotvrzené DML p?íkazy.Pomocí p?íkaz? ALTER lze obvykle p?idávat, p?ejmenovávat nebo ru?it sloupce v tabulce a do ur?ité míry měnit datové typy sloupc?. Lze nap?íklad bez problém? roz?í?it v naplněné tabulce maximální délku textového sloupce, nelze u? ov?em měnit datumov? prvek na ?íseln?.P?íkazy pro ?ízení p?ístupuPoslední skupinou SQL p?íkaz? jsou p?íkazy pro ?ízení p?ístupov?ch oprávnění. V databázích platí politika, ?e které objekty vytvo?ím ty také vlastním a rozhoduji o tom, kdo dal?í k nim bude mít p?ístup. Oprávnění se udělují na celé objekty, tedy nap?íklad na cel? obsah tabulky. Lze v?ak specifikovat, jaké operace bude moci jin? u?ivatel nad objektem pou?ívat. Typy oprávnění odpovídají DML p?íkaz?m. M??eme tedy zp?ístupnit tabulku pro ?tení, tím ?e povolíme p?íkaz SELECT, ale zaká?eme ostatní operace INSERT, UPDATE, DELETE. K udělování oprávnění se pou?ívá p?íkaz GRANT. Syntaxe je?GRANT opravneni ON objekt TO uzivatelGRANT SELECT ON tabulka1 TO NovakPo spu?tění tohoto p?íkazu m??e u?ivatel Novak spou?tět p?íkazy SELECT nad tabulkou tabulka1. Odejmout p?idělená práva m??eme p?íkazem REVOKE.?REVOKE opravneni?ON objekt?FROM uzivatelREVOKE SELECT ON tabulka1 FROM Novak?Funkce a operátory v SQLV?stupy:Umí pou?ít základní operátory SQLOvládá mno?inové operátory SQLMá p?ehled a umí vyu?ít v databázích dostupné matematické funkce, funkce pro práci s textov?mi ?etězci a funkce pro práci s ?asov?mi atributyZná základní agrega?ní funkce SQL? Jazyk SQL nám umo?ňuje nejen z databáze získávat ulo?ená data, ale zároveň tato data na v?stupu modifikovat nejr?zněj?ím zp?sobem. Slou?í k tomu bohatá v?bava operátor? a funkcí, kterou dne?ní databázové systémy nabízejí. Bohu?el standardizace v této oblasti není vysoká, a proto stejné funkce se v jednotliv?ch systémech jmenují jinak a v někter?ch p?ípadech se mírně jinak i chovají. Testování funkcí a operátor?Funkce a operátory se v SQL konstrukcích aplikují na jednotlivé ?ádky z databáze získan?ch dat. Pokud si chceme vyzkou?et některou funkci, je nejp?ehledněj?í testovat s co nejjednodu??ím SQL dotazem a ideálně na jednom záznamu. Nejjednodu??í SQL dotaz je:SELECT 1+1 FROM tabulka_s_jednim_radkemDatabáze POSTGRESQL umo?ňuje pro testování funkcí dokonce jednodu??í podobu:SELECT 1 + 1Tento p?íkaz zobrazí jeden ?ádek s jedním sloupcem s p?ekvapivou hodnotou 2. Tento zápis nelze aplikovat v databázi ORACLE (klí?ové slovo FROM je zde v?dy povinné), nabízí ale tzv. pseudotabulku DUAL, která obsahuje právě jeden ?ádek. Prost? sou?et v ORACLE lze tedy realizovat takto:SELECT 1+1 FROM DUALV databázi PGSQL jako generátor ?ádk? slou?í funkce generate_series(od, do). Tato funkce vrací ?ádky v intervalu parametr? od do.SELECT 1 + 1 FROM GENERATE_SERIES(1,1)vrátí 1 ?ádekSELECT cislo + 1 FROM GENERATE_SERIES(5,9) as cislovrátí 5 ?ádk? s ?ísly 6 a? 10V dal?ím textu budou funkce a operátory prezentovány ve variantě pro ORACLE databázi. V reálné praxi v?ak zpracováváme pomocí funkcí a operátor? data skute?n?ch tabulek. Nap?íklad zobrazení ceny zbo?í v?etně DPH by vypadalo následovně (p?edpokládá tabulku s názvem zbozi s ?íseln?m sloupcem cena):SELECT cena * 1.21 FROM zboziOperátoryZákladní operátoryMezi základní operátory pat?í operace s?ítání (+) a ode?ítání (-), násobení (*) a dělení (/). S?ítat a ode?ítat lze ?íselné konstanty a hodnoty ?íseln?ch sloupc? tabulek. Od datumov?ch sloupc? a konstant lze ode?ítat a p?i?ítat ?íselné hodnoty, ?íslo p?edstavuje po?et p?i?ítan?ch ?i ode?ítan?ch dn?. Pokud datov? sloupec obsahuje i ?asovou komponentu, lze ode?ítat a p?i?ítat i desetinné ?íslo, kdy desetinná ?ást odpovídá ?ásti jednoho dne, nap?.: SELECT datum_narozeni + 5.5 FROM patientsp?i?te k datu narození 5 dn? a 12 hodin (p?l dne). Ode?ítat lze také dvě data vzájemně, v?sledkem je ?íslo, které odpovídá po?tu dn? mezi daty.?Násobit a dělit lze pouze ?íselné datové typy a ?íselné konstanty.Pomocí operátoru lze také spojovat textové ?etězce, v databázi ORACLE A PGSQL jde o operátor dvou svislítek (||):SELECT jmeno || ' ' || prijmeni FROM pacient?Tento dotaz pojí hodnotu sloupce jména a p?íjmení a oddělí je mezerou.Operátory a funkce se pou?ívají primárně za klí?ov?m slovem SELECT nebo p?i definování podmínek v ?ásti WHERE. Jejich vstupem, u funkcí mluvíme o parametrech, ?jsou bu? názvy sloupc? tabulky nebo konstanty. Parametry se uvádějí v kulat?ch závorkách za názvem funkce a oddělují se ?árkou. Pokud pou?ijeme funkci v kombinaci s názvem sloupce tabulky, zpracovává funkce ?i operátor hodnotu ka?dého ?ádku a v?stupem je modifikovaná hodnota pro ka?d? ?ádek, kter? SQL dotaz vrátí. O v?sledku funkcí mluvíme jako o navrácené hodnotě. ?Logické operátoryMezi operátory pat?í také v?razy, které vyu?íváme p?i sestavování slo?itěj?ích podmínek v ?ásti SQL dotazu za WHERE. Jde o tzv. logické operátory: AND , OR a NOT. Operátory AND a OR spojují dvě podmínky. Pracují tak, ?e nejprve vyhodnotí pravdivost těchto podmínek (TRUE/FALSE/NULL, pravda/nepravda/NULL) a následně vyhodnotí v?sledek dle tabulek. Tabulka SEQ Tabulka \* ARABIC 2 - Logick? operátor ANDANDTRUEFALSENULLTRUETRUEFALSEFALSEFALSENULLNULLFALSENULLTabulka SEQ Tabulka \* ARABIC 3 - Logick? operátor ORORTRUEFALSENULLTRUETRUEFALSETRUEFALSENULLTRUENULLNULLOperátor NOT má jen jeden parametr, nad kter?m provede negaci TRUE => FALSE, FALSE => TRUE.Podmínka vek > 30 AND vek < 50 je pravdivá(splněna) pro hodnoty sloupce vek mezi 30 a 50, podmínka vek < 30 OR vek > 50 je pravdivá pro věk pod 30 nebo nad 50. Podmínka vek < 30 AND vek > 50 není pravdivá nikdy, podmínka vek > 30 OR vek < 50 je pravdivá v?dy. Mno?inové operátoryJin?m typem operátor? jsou mno?inové operátory UNION, UNION ALL, INTERSECT a MINUS. Těmito operátory lze spojovat celé SQL dotazy a získávat tak spojené mno?iny v?sledk?. Pomocí UNION a UNION ALL m??eme slou?it v?sledky dvou dotaz?, kdy k v?sledn?m záznam?m prvního dotazu se p?ipojí v?sledky druhého dotazu:SELECT patient_id FROM ambulanceUNIONSELECT patient_id FROM nemocnicePo?et sloupc? prvního a druhého dotazu musí b?t stejn? a musí b?t stejného datového typu. Rozdíl mezi UNION a UNION ALL je v tom, ?e UNION odstraňuje duplicitní záznamy, zatímco UNION ALL provede prosté slou?ení v?sledk? (mno?in). Operátor INTERSECT provede pr?nik mno?in a v?sledkem jsou pouze ty záznamy, které jsou obsa?eny ve v?sledku obou dotaz?. Operátorem MINUS získáme mno?inu ?ádk?, které vrátí první dotaz a které zároveň neobsahuje v?sledek druhého dotazu. FunkceFunkce lze dělit podle datov?ch typ? jejich parametr? na funkce pro textové hodnoty, ?íselné hodnoty a datumové hodnoty. Speciální kategorií jsou pak funkce pracující s libovoln?m typem a s prázdnou hodnotou NULL. Funkce nahrazující NULLNULL hodnotu?lze ve v?sledku nahrazovat konstantou nebo hodnotou jiného sloupce pomocí funkcí?NVL, NVL2, COALESCE. Funkce NVL a NVL2 lze po?ít pouze v databázi ORACLE, funkce COALESCE je dostupná v ORACLE i PGSQL.Funkce NVL vyhodnotí první parametr a pokud je NULL, v?sledkem je druh? parametr. Pokud první parametr není NULL, je v?sledkem funkce první parametr.?SELECT NVL(NULL, 0) FROM DUAL - v?sledek je 0SELECT NVL(5, 0) FROM DUAL - v?sledek je 5SELECT NVL(cena, 0) FROM zbozi - v?sledkem je bu? hodnota ve sloupci cena nebo 0, pokud není na daném ?ádku cena uvedena.?Funkce NVL2 má o parametr víc, druh? parametr je v?sledkem funkce v p?ípadě, kdy první parametr není NULL, t?etí parametr je v?sledkem, pokud první parametr je NULL. ?SELECT NVL2(999,0,1) FROM DUAL - v?sledek je 0, proto?e ?999 není NULLFunkce COALESCE má neomezen? po?et parametr? a jejím v?sledkem je první NOT NULL parametr.?SELECT COALESCE (cena_akce, cena_prodej, cena_nakup, 0) FROM zbozi - testuje pro ka?d? ?ádek tabulky zbozi postupně jednotlivé sloupce a vrátí tu hodnotu, která není NULL.?Funkce GREATEST a LEASTFunkce GREATEST a LEAST pat?í mezi funkce s neomezen?m po?tem parametr? a lze je vyu?ít pro v?echny datové typy, které lze nějak?m zp?sobem t?ídit. Funkce porovnává v?echny zadané parametry a vrací ten největ?í resp. nejmen?í.?SELECT GREATEST (3,6,9,0), LEAST (3,6,9,0) FROM DUALV?sledek je jeden ?ádek a dva sloupce s hodnotami 9 a 0.Podmíněn? v?raz CASE a funkce DECODEDatumové funkceNejv?znamněj?í funkce pro datov? typ datum jsou:Funkce vracející aktuální datum a ?asFunkce pro práci s ?asov?m intervalemFunkce pro formátování vstupního ?i v?stupního data?Standardní funkcí, která vrátí systémové datum je CURRENT_DATE, v jednotliv?ch databázov?ch systémech se v?ak m??e li?it její návratová hodnota. V ORACLE je synonymem funkce SYSDATE, která vrátí aktuální datum i ?as s p?esností na sekundy. V databáze PGSQL vrací tato funkce pouze datum. Standardní funkcí pro získání aktuálního ?asu je CURRENT_TIME, která v?ak není dostupná ORACLE. Funkce CURRENT_TIMESTAMP?je dostupná v ORACLE i PGSQL a vrací datum i ?as. Jak bylo zmíněno v??e, pro práci s datumy lze vyu?ívat operátory + a - . Takto lze pracovat, pokud pracujeme v jednotkách dn? ?i t?dn?. Problém je, pokud pot?ebujeme pracovat s p?esností na měsíce nebo roky. Kalendá?ní měsíc má 28 - 31 dn?, rok má 365 nebo 366 dn?. Vyjád?it rozdíl mezi dvěma daty jako po?et měsíc? nebo let bez zaokrouhlování není tedy zcela triviální. ORACLE nabízí pro tyto úlohy 2 velmi u?ite?né funkce: ADD_MONTHS a MONTHS_BETWEEN. První umo?ňuje p?i?ítat ?i ode?ítat měsíce k danému datu, druhá vrací po?et měsíc? mezi dvěma daty. Proto?e rok má v?dy 12 měsíc?, je mo?né s pomocí uveden?ch funkcí pracovat i s roky. P?íklad ukazuje, jak zjistit sou?asn? věk osoby, pokud máme v databázi ulo?eno datum narození. Sou?asn? věk odpovídá rozdílu aktuálního data a data narození v měsících, pokud toto ?íslo podělíme 12 a zaokrouhlíme dol? na celé ?íslo, dostáváme věk v letech.?SELECT TRUNC (MONTHS_BETWEEN (CURRENT_DATE, date_of_birth)/12) FROM patientsDatabáze PGSQL nabízí funkci AGE pro v?po?et rozdílu mezi dvěma daty.SELECT AGE (current_date, date_of_birth) FROM patientsFormátování datumuDatum a ?as je databází interně ukládán jako ?íslo, které udává po?et dn? od databázově specifického v?chozího data. Pokud chceme datum zobrazit jako v?sledek SQL dotazu, databáze transformuje toto ?íslo do defaultního formátu. Tento formát nám ale ?asto nevyhovuje. Máme ale k dispozici (ORACLE i PGSQL) formátovací funkci TO_CHAR, která nám umo?ňuje p?esně specifikovat v?stupní formát. Funkce má dva parametry, prvním je formátované datum (sloupec tabulky s datumov?m typem), druh?m je specifikace formátu ohrani?ená apostrofy. Pro specifikaci formátu se vyu?ívají zástupné znaky, seznam nej?astěji pou?ívan?ch uvádí tabulka.Tabulka SEQ Tabulka \* ARABIC 4 - Symboly pro formátování datumuSymbolPopisddden měsícemmkalendá?ní měsíc (1-12)yyyykalendá?ní rokhh24hodiny (0-23)miminuty (0-59)sssekundy (0-59)ww?íslo t?dne v roce Pokud chceme zobrazit datum a ?as dle ?esk?ch zvyklostí, specifikujeme formát následovněSELECT TO_CHAR(CURRENT_TIMESTAMP, 'dd. mm. yyyy hh24:mi:ss') FROM DUALV?sledek je 20. 7. 2013 10:13:23. Pokud chceme formát datumu vhodn? ke t?ídění, pou?ijeme SELECT TO_CHAR(CURRENT_TIMESTAMP, 'yyyy-mm-dd') FROM DUALS formátem datumu je problém i p?i vkládání dat do databáze. Aby databáze byla schopna p?evést vkládané datum do svého interního formátu, musíme opět p?esně specifikovat vkládan? formát. Pou?ijeme k tomu funkci TO_DATE, jejím? prvním parametrem je textov? ?etězec p?edstavující vkládané datum, druh? parametr je opět specifikace formátu. Specifikace formátu je shodná jako v p?ípadě funkce TO_CHAR. P?íkaz INSERT pro vlo?ení data do tabulky patients, vypadá taktoINSERT INTO patients (date_of_birth) VALUES (TO_DATE('13.3.1950','dd.mm.yyyy'))Nezku?ení u?ivatelé databáze ?asto funkce TO_CHAR a T0_DATE zaměňují, co? m??e zp?sobit bu? chybu p?i vykonávání SQL p?íkazu, v hor?ím p?ípadě pak vrácení ?i vlo?ení chybn?ch dat. Pravidlo je p?itom jednoduché, pokud je prvním parametrem textov? sloupec nebo text v apostrofech, musí jít v?dy o funkci TO_DATE, pokud je parametrem datumov? sloupec nebo zmíněné funkce vracející aktuální datum a ?as, je na místě funkce TO_CHAR. Funkce TO_CHAR a TO_DATE lze kombinovat, nap?íklad pokud chceme p?eformátovat datumovou konstantu. SELECT TO_CHAR(TO_DATE('22.3.2000','dd.mm.yyyy'), 'yyyy-mm') FROM DUAL;Dotaz v p?íkladu nejprve specifikované datum p?evede do interního formátu a následně ho funkce TO_CHAR zobrazí ve formátu rok-měsíc.Extrahovat komponenty data lze také pomocí funkce EXTRACT. Pomocí této funkce m??eme z data získat rok (YEAR), měsíc (MONTH), den (DAY)SELECT EXTRACT(YEAR FROM TO_DATE('22.3.2000','dd.mm.yyyy')) FROM DUAL;Textové funkceDatabáze nabízí také ?adu funkcí pro práci s textov?mi ?etězci. Mezi nejpou?ívaněj?í a nejroz?í?eněj?í pat?í funkce uvedené v tabulce. Tabulka SEQ Tabulka \* ARABIC 5 - Funkce pro práci s textemNázev funkcePopis funkceSUBSTR(text, od, po?et) Vrací pod?etězec textu dle pozice INSTR(text, subtext) Hledání pod?etězce v textu, vrací pozici nalezeného pod?etězce (pouze ORACLE) STRPOS(text, subtext) obdoba INSTR (POSTGRESQL)LOWER (text), UPPER(text)P?evede text na malá, resp. velká písmenaINITCAP (text)P?evede první písmeno slov na velké písmeno, ostatní na maláLTRIM (text), RTRIM (text)Odstranění mezer (ne?ádoucích znak?) z textu (zleva, zprava)REPLACE(text, puvodni, nove) Nahrazení pod?etězce za jin?TRANSLATE(text, nahradit_co, nahradit_cim)Nahrazení po znacích LENGTH(text)Vrací délku textu ve znacíchPokud chceme z textu získat ur?it? pod?etězec, pou?ijeme funkci SUBSTR. Parametrem je zpracovávan? ?etězec, druh?m parametrem je po?adí prvního znaku, kter? chceme extrahovat, t?etím nepovinn?m parametrem je po?et znak?, které chceme extrahovat. Pokud chceme zkrátit ?etězec na prvních 5 znak?, pou?ijeme funkci SUBSTR následovně:SELECT SUBSTR('dlouh? text', 1, 5) FROM DUAL;Funkce, která prohledává text na v?skyt specifikovaného pod?etězce, se jmenuje v ORACLE INSTR, v PGSQL pak STRPOS. Parametrem je prohledávan? ?etězec a hledan? text, v?sledkem je po?adí prvního znaku nalezeného ?etězce nebo nula, pokud pod?etězec není nalezen. Pokud chceme získat z textu ur?itou ?ást, která je oddělena definovan?m symbolem, pou?ijeme kombinaci funkcí SUBSTR a INSTR. SELECT SUBSTR('V?sledek:67',INSTR('V?sledek:67',':')+1) FROM DUALFunkce INSTR nejprve nalezne symbol ':', vrátí po?adí tohoto znaku, kter? p?edá funkci SUBSTR. Ta extrahuje text za tímto znakem. Jedni?ka je p?ipo?tena proto, aby v?sledek neobsahoval úvodní dvojte?ku. Funkce, která vrátí po?et znak? v ?etězci, se jmenuje v ORACLE i PGSQL LENGTH. Parametrem je analyzovan? ?etězec. P?i práci s textem je ?asto pot?ebné sjednocení velikosti písmen. Cel? text m??eme snadno p?evést na malá písmena (LOWER) nebo velká písmena (UPPER). Tyto funkce vyu?ijeme, pokud budeme chtít pracovat s ?etězci bez rozli?ení velikosti písmen. Funkci INITCAP vyu?ijeme, pokud chceme, aby ka?dé slovo ?etězce za?ínalo velk?m písmenem, nap?íklad v p?ípadě vlastních jmen.SELECT INITCAP('klIMe? daNIel') FROM DUAL -- v?sledek je Klime? Daniel Dvojice funcí LTRIM a RTRIM umo?ňuje odstranit ne?adoucí znaky z levé, resp. z pravé strany ?etězce. Defaultně se odstraňují mezery, pokud chceme odstranit specifikované znaky, uvedeme jejich v??et jako druh? parametr.SELECT RTRIM('text ++++ ', '+ ') FROM DUALDotaz odstraní zprava znaky mezer a plus, v?sledkem je 'text'. K nahrazování znak? v ?etězcích slou?í funkce REPLACE a TRANSLATE. Pomocí REPLACE nahradíme pod?etězec definovan? v druhém parametru za text uveden? jako t?etí parametr. Pokud neuvedeme t?etí parametr, bude nalezen? pod?etězec odstraněn. Funkce REPLACE defaultně nahrazuje v?echny nalezené pod?etězce. Funkce TRANSLATE slou?í pro nahrazení jednotliv?ch znak?. Druh?m jejím parametrem je seznam nahrazovan?ch znak?, t?etí parametr pak obsahuje seznam znak? nahrazujících. Nahrazuje se v?dy první znak druhého parametru za první znak t?etího parametru, druh? za druh?, atd. Pokud chybí t?etí parametr, jsou znaky druhého parametru odstraněny. Funkce se vyu?ívá, nap?. pokud chceme odstranit ?eskou diakritiku z textového ?etězce.SELECT TRANSLATE('?lu?ou?k? k?ň','?????ň','ztcyun') FROM DUAL -- v?sledek je 'zlutoucky kun'Funkce s ?íseln?m parametrem?iroká ?kála funkcí existuje i pro ?íselné datové typy. Základní p?ehled uvádí tabulka. Tabulka SEQ Tabulka \* ARABIC 6 - Funkce pro práci s ?íslyNázev funkcePopis funkceSIN, COS, TANGoniometrické funkce s jedním parametrem, kter?m je úhel v radiánechABS(?íslo)Vrací absolutní hodnotu z ?íslaPOWER(?íslo, exponent)Umocňuje ?íslo na exponentSQRT (?íslo)Vrací druhou mocninu z ?íslaLN(?íslo), LOG (?íslo)Vrací p?irozen?, dekadick? logaritmus ?íslaROUND(?íslo, p?esnost), CEIL(?íslo), TRUNC(?íslo, p?esnost), FLOOR(?íslo)Zaokrouhluje ?ísloMODVrací zbytek po celo?íselném děleníK zaokrouhlování ?ísel slou?í trojice funkcí ROUND, CEIL, TRUNC (resp. FLOOR). Nabízí v?echny základní mo?nosti zaokrouhlování. ROUND zaokrouhluje od 5 nahoru, funkce CEIL zaokrouhluje v?dy nahoru, funkce TRUNC nebo FLOOR zaokrouhluje v?dy dol? (od?íznutí). SELECT ROUND(5.5), TRUNC(5.5), FLOOR(5.5), CEIL (5.5) FROM DUALV?sledkem dotazu je 6, 5, 5, 6U funkcí ROUND a TRUNC je mo?né specifikovat druh? parametr a ur?it jím, na kolik desetinn?ch míst se má zaokrouhlení provést. Uveden? v??et funkcí je jen základ ze ?iroké nabídky někter?ch databázov?ch systém?. Celou nabídku a podrobnosti k jednotliv?m funkcím je t?eba v?dy hledat v dokumentaci daného databázového systému.Agrega?ní funkceSpeciální skupinou jsou funkce agrega?ní. Zatímco dosud zmíněné funkce vrací jednu hodnotu pro ka?d? ?ádek zpracovávaného SQL dotazu, agrega?ní funkce vrací jen jeden ?ádek, tedy agregují v?echny ?ádky do jedné hodnoty. Seznam standardních funkcí uvádí tabulka Tabulka SEQ Tabulka \* ARABIC 7 - Agrega?ní funkceNázev funkcePopis funkceCOUNTPo?et ?ádk?, které jsou v?sledkem SQL dotazuAVG(sloupec)Vypo?ítá aritmetické pr?měr sloupce SUM(sloupec)Vypo?ítá sumární sou?et sloupceMIN(sloupec)Vrací minimum sloupceMAX(sloupec)Vrací maximum sloupceSTDDEV(sloupec)Po?ítá standardní odchylkuMEDIAN(sloupec)Po?ítá medián ze sloupceFunkci COUNT lze pou?ít ve t?ech variantách:SELECT COUNT(*), COUNT(date_of_birth), COUNT(DISTINCT date_of_birth) FROM patientsVarianta s hvězdi?kou vrací prost? po?et ?ádku SQL dotazu, druhá varianta s názvem sloupce vrací po?et ?ádk? s vyplněnou hodnotou daného sloupce (NOT NULL), t?etí varianta s klí?ov?m slovem DISTINCT vrací po?et unikátních hodnot v daném sloupci.Pro v?sledek musí platit v?dy COUNT(*) >= COUNT(sloupec) >= COUNT(DISTINCT sloupec)Funkce AVG, STDDEV a SUM lze pou?ít pouze pro ?íselné sloupce, sloupce MIN, MAX pro v?echny základní datové typy.Agrega?ní funkce nelze kombinovat s ostatními funkcemi. Nelze: SELECT COUNT(*), LENGTH (patient_id) FROM patients. V?jimkou jsou funkce, které vrací jednu hodnotu jako nap?. CURRENT_DATE. I v tomto p?ípadě je v?ak doporu?eno aplikovat na tyto funkce funkci agrega?ní:SELECT COUNT(*), MAX(CURRENT_DATE) FROM patients.Bez omezení je mo?né aplikovat standardní funkce na v?sledek agrega?ní funkce:SELECT ROUND(COUNT(*)/10) FROM patientsCvi?eníJak extrahujete posledních 5 znak? z textového ?etězce?Jak zjistíte, kolikát? den v roce právě je? Pokro?ilé SQLV?stupy:P?ipomene si problematiku vytvá?ení vztah? mezi tabulkamiUmí získat data spojením více tabulekRozumí rozdílu mezi vnit?ním a vněj?ím spojením tabulekZná mo?nosti zano?ení SQL dotaz?Ve druhé kapitole jsme se seznámili se základní konstrukcí SQL dotaz?. Víme, jak získat po?adované sloupce, jak omezit v?pis na ur?ité ?ádky a jak získat sumární data pomocí agrega?ních funkcí. V této kapitole si uká?eme agregování dat pomocí klí?ov?ch slov GROUP BY a HAVING. Proto?e databáze jsou jen z?ídka tvo?eny jednou tabulkou, vysvětlíme si zp?sob získávání dat z více tabulek pomocí tzv. spojování (joining) tabulek. V poslední ?ásti kapitoly budou vysvětleny mo?nosti vytvá?ení slo?itěj?ích zano?en?ch SQL dotaz?.Seskupování datPodívejme se nejprve na mo?nost pokro?ilé agregace. Mějme tabulku pacient? se sloupci identifikátor pacienta, pohlaví a datumu narozeníCREATE TABLE patients(patient_id VARCHAR(10),sex VARCHAR(1) ,date_of_birth TIMESTAMP);Vlo?íme několik ?ádk?:INSERT INTO patients (patient_id, sex, date_of_birth) VALUES ('pat1','F',TO_DATE('2.4.1940','dd.mm.yyyy'));INSERT INTO patients (patient_id, sex, date_of_birth) VALUES ('pat2','M',TO_DATE('30.3.1950','dd.mm.yyyy'));INSERT INTO patients (patient_id, sex, date_of_birth) VALUES ('pat3','F',TO_DATE('13.8.1947','dd.mm.yyyy'));INSERT INTO patients (patient_id, sex, date_of_birth) VALUES ('pat4','M',TO_DATE('23.11.1987','dd.mm.yyyy'));INSERT INTO patients (patient_id, sex, date_of_birth) VALUES ('pat5','F',TO_DATE('3.9.1975','dd.mm.yyyy'));Nyní se budeme sna?it získat sumární p?ehled o obsahu tabulky. Zajímá nás, kolik záznam? tabulka obsahuje, kolik je v ní ?en kolik mu?? a v jak?ch věkov?ch kategoriích. Po?et ?ádk? ji? zjistit umíme:SELECT COUNT(*) FROM patients;Snadno také zjistíme, po?et unikátních hodnot v jednotliv?ch sloupcích:SELECT COUNT(DISTINCT patient_id), COUNT(DISTINCT sex), COUNT(DISTINCT date_of_birth) FROM patients ;Z v?sledku je vidět, ?e máme tabulku s 5 ?ádky, kde sloupce patient_id, date_of_birth obsahují v?dy unikátní hodnotu, sloupec sex obsahuje jen 2 unikátní hodnoty, bu? F nebo M.Pokud chceme získat p?ehled, kolik je v tabulce ?en a kolik mu??, m??eme sestavit 2 dotazy:SELECT COUNT(*) FROM patients WHERE sex = 'F';SELECT COUNT(*) FROM patients WHERE sex = 'M';SQL standard v?ak nabízí elegantněj?í zp?sob, jak tento v?sledek získat v jediném dotazu. Slou?í k tomu klí?ové slovo GROUP BY, za kter?m specifikujeme název sloupce nebo sloupc?, podle kter?ch chceme data agregovat. GROUP BY se umís?uje v SQL dotazu za definici podmínky (WHERE), p?ípadně za název tabulky, pokud podmínka není specifikována. Po?ty pacient? dle pohlaví lze získat následovně:SELECT sex, COUNT(*) FROM patients GROUP BY sex;V?sledkem jsou 2 ?ádky (odpovídá po?tu unikátních hodnot v agregovaném sloupci). Agrega?ní funkce COUNT po?ítá ?ádky zvlá?? pro ka?dou kategorii agregovaného sloupce. Pou?itím klauzule GROUP BY zna?ně omezujeme mo?nosti v?raz? za klauzulí SELECT. M??eme zde uvést pouze názvy sloupc? uveden?ch za GROUP BY a agrega?ní funkce. ?astou chybou je pokus vlo?it za SELECT název neagregovaného sloupce:SELECT sex, date_of_birth, COUNT(*) FROM patients GROUP BY sex;Tento dotaz nedává logick? smysl, databáze neví, jaké datum narození má zobrazit (v?sledek agregace jsou dva ?ádky, tabulka ale obsahuje 5 r?zn?ch dat narození). Je ale mo?né po?adovat pro ka?dé pohlaví nejstar?ího a nejmlad?ího pacienta:SELECT sex, MIN(date_of_birth) nejstarsi, MAX(date_of_birth) nejmladsi, COUNT(*) FROM patients GROUP BY sex;Agregovat lze podle více sloupc? i podle modifikovan?ch sloupc?. Následující dotaz vrátí p?ehled po?tu pacient? agregovan?ch p?es pohlaví a p?es dekádu data narození. SELECT sex, TRUNC(EXTRACT (YEAR FROM date_of_birth)/10) AS dekada, COUNT(*) FROM patients GROUP BY sex, TRUNC(EXTRACT (YEAR FROM date_of_birth)/10)Pov?imněte si, ?e v?raz, kter? tvo?í agregovan? sloupec za SELECT, musí odpovídat v?razu za GROUP BY. V?raz TRUNC(EXTRACT (YEAR FROM date_of_birth)/10) nejprve extrahuje rok z data narození, tuto hodnotu podělí 10 a funkcí TRUNC provede zaokrouhlení dol?, ?ím? dostáváme dekádu narození pacienta. Co v p?ípadě, ?e bychom chtěli ve v?sledku vidět je záznamy s hodnotou COUNT(*) vět?í ne? 1? Tuto podmínku nem??eme specifikovat za klí?ové slovo WHERE, proto?e podmínky za WHERE se aplikují P?ED vlastní agregací na primární data, která do agregace teprve vstupují. Filtrovat agregovan? záznam je mo?né pomocí HAVING, která se umís?uje za GROUP BY v?raz:SELECT sex, TRUNC(EXTRACT (YEAR FROM date_of_birth)/10) AS dekada, COUNT(*) FROM patients GROUP BY sex, TRUNC(EXTRACT (YEAR FROM date_of_birth)/10)HAVING COUNT(*) > 1Podle v?sledku agrega?ních funkcí je mo?né i t?ídit pomocí ORDER BY:SELECT sex, TRUNC(EXTRACT (YEAR FROM date_of_birth)/10) AS dekada, COUNT(*) FROM patients GROUP BY sex, TRUNC(EXTRACT (YEAR FROM date_of_birth)/10)HAVING COUNT(*) > 1ORDER BY COUNT(*)Po?adí klí?ov?ch slov SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY je dané a nelze je měnit. Práce s více tabulkamiDosud jsme pomocí SQL p?íkaz? pracovali pouze s jednou tabulkou. Databáze jsou v?ak v drtivém po?tu tvo?eny sadou tabulek, jejich? struktura odpovídá modelované realitě. Tabulky jsou mezi sebou svázané vazbou 1:1 nebo 1:n za pomoci cizích klí??, viz kapitola datov? model. SQL standard umo?ňuje pracovat s více tabulkami pomocí tzv. join operace. Mluvíme o spojování tabulek. Spojování tabulek je dvojího typu, existuje vnit?ní a vněj?í spojení. Rozdíl nejlépe ilustruje p?íklad, kdy máme 2 tabulky s vazbou 1: n, tabulka pacient? a tabulka jejich vy?et?ení, ka?d? pacient m??e mít 0 a? n vy?et?ení, primárním klí?em v tabulce pacient? je sloupec id_pacienta, kter? slou?í jako cizí klí? v tabulce vy?et?ení: Tabulka SEQ Tabulka \* ARABIC 8 Tabulka pacient?patient_idjmenoprijmeni 1JanStar?2Karel Nov?3OlgaMladáTabulka SEQ Tabulka \* ARABIC 9 Tabulka vy?et?enípatient_iddatum_vysetrenihmotnost11.2.20126611.6.201270214.7.201369Tabulka SEQ Tabulka \* ARABIC 10V?sledek vnit?ního spojenípatient_idjmenoprijmenidatum_vysetrenihmotnost1JanStar?1.2.2012661JanStar?1.6.2012702Karel Nov?14.7.201369Tabulka SEQ Tabulka \* ARABIC 11V?sledek vněj?ího spojenípatient_idjmenoprijmenidatum_vysetrenihmotnost1JanStar?1.2.2012661JanStar?1.6.2012702Karel Nov?14.7.2013693OlgaMladáV?sledkem vnit?ního spojení jsou ?ádky, které existují v obou spojovan?ch tabulkách, ?ádky, které existují pouze v jedné z tabulek, jsou vynechány.? Oproti tomu vněj?í spojení umo?ňuje získat v?echny ?ádky z jedné tabulky a k nim p?ipojit existující ?ádky v druhé tabulce. Jedna ze spojovan?ch tabulek je u vněj?ího spojení ?ídící, k ní? se dle podmínky vá?í ?ádky druhé tabulky. Pro získání dat z více tabulek slou?í v SQL klí?ové slovo JOIN, které se umís?uje mezi názvy spojovan?ch tabulek. Následuje klí?ové slovo ON, po kterém je nutné definovat zp?sob propojení. Pokud pou?ijeme samotné slovo JOIN, provede se vnit?ní spojení:SELECT p.id_pacienta, p.jmeno, p.prijmeni, v.datum_vysetreni, v.hmotnostFROM pacienti p JOIN vysetreni v ON p.id_pacienta = v.id_pacientaProto?e pracujeme s více tabulkami, je nutné sloupce identifikovat pln?m jménem, které se skládá z názvu tabulky a názvu samotného sloupce, kter? oddělíme te?kou. Místo plného názvu tabulek m??eme definovat zkrácené pojmenování v ?ásti FROM, zkratku zapí?eme p?ímo za název tabulky. Zkratku tabulky pak pou?íváme ve v?ech ?ástech SQL dotazu. Zp?sob spojení tabulek je obvykle definováno podmínkou za klí?ov?m slovem ON. Podmínka definuje, které ?ádky se spolu mají párovat. Pokud podmínku nedefinujeme, vzniká tzv. kartézsk? sou?in, kdy se ka?d? ?ádek jedné tabulky spojí s ka?d?m ?ádkem druhé tabulky. V?sledná mno?ina má pak m x n ?ádk?, co? u vět?ích tabulek m??e b?t enormní po?et. Toto chování je ve vět?ině p?ípad? ne?ádoucí, proto musíme b?t p?i definování podmínky spojení velmi pozorní.? Databáze se tak bude chovat i v p?ípadě, kdy dle spojovací podmínky odpovídá jednomu ?ádku první tabulky více ?ádk? v tabulce druhé. Hodnoty ?ádku první tabulky se kopírují ke ka?dému ?ádku druhé tabulky.? V?sledkem spojení tabulek je n ?ádk?, kde n je u vnit?ního spojení v rozsahu 0 a? m * n, u vněj?ího spojení v rozsahu m a? m * n.?Pokud chceme provést vněj?í spojení tabulek, doplníme p?ed JOIN jedno z dal?ích klí?ov?ch slov: LEFT, RIGH nebo FULL. Pokud chceme, aby ?ídící byla uvedená první, pou?ijeme klí?ové slovo LEFT JOIN, pokud chceme mít ?ídící druhou tabulku, pou?ijeme RIGHT JOIN. Extrémem je tzv. úplné spojení (full join), jeho? v?sledkem jsou v?echny ?ádky obou tabulek. Tento typ spojení v?ak pou?ijeme jen z?ídka, navíc je pro databáze v?konnostně nejnáro?něj?í.?V na?em p?ípadě pou?ijeme variantu LEFT JOIN:SELECT p.id_pacienta, p.jmeno, p.prijmeni, v.datum_vysetreni, v.hmotnostFROM pacienti p LEFT JOIN vysetreni v ON p.id_pacienta = v.id_pacienta?Alternativou k syntaxi JOIN ON je p?ím? v??et spojovan?ch tabulek za FROM a specifikace spojovací podmínky za WHERE. Tato konstrukce m??e b?t v p?ípadech spojování více tabulek lépe ?itelná. Lze ji v?ak standardně pou?ít jen pro vnit?ní spojení.?Stejn? v?sledek jako v prvním p?ípadě dostaneme i po spu?tění této varianty:SELECT p.id_pacienta, p.jmeno, p.prijmeni, v.datum_vysetreni, v.hmotnostFROM pacienti p, vysetreni v WHERE p.id_pacienta = v.id_pacienta3 a více tabulekPokud pot?ebujeme získat data z více jak dvou tabulek, syntaxe z?stává stejná, pomocí v?razu JOIN p?ipojíme dal?í tabulku. Mějme tabulku RTG vy?et?ení, na kter?ch m??e b?t zji?těna u daného pacienta 0 a? n zlomenin. Tabulka zlomenin je vázána s tabulkou rtg pomocí klí?e vysetreni_id. CREATE TABLE rtg(vysetreni_id NUMERIC(9),patient_id VARCHAR(10),)CREATE TABLE zlomeniny(zlomenina_id NUMERIC(9),vysetreni_id NUMERIC(9),lokalizace VARCHAR(50))Naplníme tabulky daty, pacient pat 1 měl 2 vy?et?ení, jedno negativní, druhé ukázalo 2 zlomené kosti. Pacient pat2 měl jedno vy?et?ení bez nálezu. INSERT INTO rtg (patient_id, vysetreni_id) VALUES ('pat1', 1);INSERT INTO rtg (patient_id, vysetreni_id) VALUES ('pat1', 2);INSERT INTO rtg (patient_id, vysetreni_id) VALUES ('pat2', 3);INSERT INTO zlomeniny (zlomenina_id, vysetreni_id, lokalizace) VALUES (1, 2, 'femur');INSERT INTO zlomeniny (zlomenina_id, vysetreni_id, lokalizace) VALUES (2, 2, 'ulna');Vnitr?ní spojení bychom provedli takto:SELECT * FROM patients p LEFT JOIN rtg ON p.patient_id = rtg.patient_id LEFT JOIN zlomeniny z ON rtg.vysetreni_id = z.vysetreni_idV?sledkem jsou pouhé 2 ?ádky, které obsahují popis dvou zlomeni pacienta pat1.Pokud chceme získat p?ehled o v?ech pacientech, jejich RTG vy?et?eních a p?ípadně zji?těn?ch zlomeninách musíme obě spojení definovat jako vněj?í:SELECT * FROM patients p LEFT JOIN rtg ON p.patient_id = rtg.patient_id LEFT JOIN zlomeniny z ON rtg.vysetreni_id = z.vysetreni_idV totmo p?ípadě je v?sledkem 8 ?ádk?, popis zlomenin je ov?em jen u dvou z nich, v ostatních ?ádcích ve sloupci popis je hodnota NULL. Vnit?ní spojení více tabulek m??eme provést i bez klauzule JOIN:SELECT * FROM patients p, rtg, zlomeniny z WHERE p.patient_id = rtg.patient_id AND rtg.vysetreni_id = z.vysetreni_idObdobně postupujeme i p?i spojování vět?ího mno?ství tabulek. Maximálně mo?n? po?et spojovan?ch tabulek závisí na databázovém systému.?Vno?ené dotazyP?ipomeňme si, co s pomocí SQL jazyka ji? umíme. Získat konkrétní hodnotu zvoleného sloupce, filtrovat konkrétní ?ádek z libovolné tabulky, pomocí agrega?ních funkcí a klauzule GROUP BY získat sumární p?ehled o obsahu zvolen?ch sloupc? a umíme propojit záznamy ve více tabulkách pomocí v?razu JOIN ON. Pro ?erpání primárních dat z databáze pro dal?í zpracování nap?íklad ve statistickém software je to zcela dosta?ující. Zdaleka to ale není v?e, co rela?ní databáze a standard SQL nabízejí. Pokro?ilé SQL za?íná mo?ností vno?en?ch (nested) dotaz?. Vno?en? dotaz má stejnou strukturu jabo bě?n? dotaz, pouze je uzav?en v kulat?ch závorkách a umístěn v nad?azeném dotazu na jednom z těchto míst:Na místě v??tu sloupc? mezi slovy SELECT a FROMNa místě názvu tabulky za FROMJako sou?ást podmínky za slovem WHEREZa klí?ov?m slovem SELECT m??eme pou?ít zano?en? dotaz, kter? vrátí právě jeden sloupec a právě jeden ?ádek. Tuto mo?nost vyu?ijeme, pokud chceme do p?ehledu ?i do v?po?tu získat v?sledek agrega?ní funkce, nap?. chceme procenticke zastoupení. P?edstavme si tabulku s daty o denní spot?ebě lék?, ze které chceme získat p?ehled, kolik procent se vy?erpalo dan? den.?SELECT datum, mnozstvi, mnozstvi / (SELECT SUM(mnozstvi) FROM spotreba) * 100? FROM spotrebaUveden? dotaz je slo?en ze dvou ?ástí. Základem je prost? SELECT do tabulky spotreba, odkud získáme data sloupc? datum a mnozstvi. Do tohoto dotazu je vlo?en vno?en? dotaz, kter? pomocí agrega?ní funkce SUM získá celkové spot?ebované mno?ství. Tímto ?íslem dělíme hodnotu ka?dého ?ádku tabulky spot?eby a násobíme stem, ?ím? získáme spot?ebu daného dne v procentech.???Vno?en? dotaz je také mo?né uvést za klí?ové slovo FROM a pou?ít ho tak místo názvu tabulky. Tento postup pou?ijeme p?i sestavování slo?it?ch dotaz?, kdy za?neme jednodu??ím dotazem, jeho? v?sledek pou?ijeme v nad?ízeném dotazu k dal?í manipulaci. Tento typ zano?ení pou?ijeme také v p?ípadě, kdy pot?ebujeme rychle získat po?et ?ádk?, které vrací ná? dotaz. Mějme dotaz:?SELECT * FROM spotreba WHERE mnozstvi > 100Pokud tabulka spotreba obsahuje tisíce a více ?ádk?, netu?íme, kolik ?ádk? dotaz vrátil, dokud nenecháme v?echny v?sledné ?ádky zobrazit, co? je p?i ladění dotaz? velmi neefektivní. Nejrychlej?í zp?sob, jak získat po?et ?ádk? laděného dotazu, je jeho zapouzd?ení do vno?eného dotazu a aplikace agrega?ní funkce COUNT:SELECT COUNT(*) FROM (SELECT * FROM spotreba WHERE mnozstvi > 100)??Platí, ?e vno?ené dotazy na pozici za FROM je mo?né v?dy spustit samostatně, tedy nezávisle na nad?ízeném dotazu. Zano?ení je mo?né opakovat na dal?í vy??í úrovni. Po?et mo?n?ch zano?ení je závislé na limitech daného databázového systému.?T?etím a nej?astěj?ím umístění vno?eného dotazu je v podmínce za WHERE. Zde m??e b?t vyu?it jako operand podmínky nebo v kombinaci s v?razem (NOT) EXISTS jako samostatná podmínka. Dotaz vkládán? jako operand m??e b?t umístěn bu? p?ímo za operátor (=, <, >, <>), nebo s pou?itím modifikátoru ANY nebo ALL. Pokud je vno?en? dotaz p?ímo za operátorem, musí dotaz vracet právě jeden sloupec a právě jeden ?ádek. Typicky se zde pou?ívají dotazy s agrega?ní funkcí. Pokud pou?ijeme kromě operátoru také modifikátor, z?stává omezení na jeden sloupec, ale ?ádk? m??e dotaz vracet 0 a? N. Poslední mo?ností je umístění vno?eného dotaz? za v?raz EXISTS. V této variantě není po?et sloupc? vno?eného dotazu v?znamn?, pou?ívá se bu? * nebo jakákoliv konstanta (1). Podle po?tu vrácen?ch ?ádk? se vyhodnotí pravdivost v?razu EXISTS. Pokud dotaz nevrátí ?ádn? ?ádek, je v?sledek FALSE, pokud vrátí 1 a? N ?ádk?, je v?sledek v?razu TRUE. Pokud pou?ijeme negaci v podobě v?razu NOT EXISTS je v?sledek opa?n?.?U vno?en?ch dotaz? umístěn?ch za WHERE budeme a? na v?jimky definovat podmínku, která prová?e vno?en? dotaz s rodi?ovsk?m dotazem. Mějme dvě tabulky, jedna s názvem student obsahuje jména student?, druhá tabulka s názvem zkou?ky obsahuje informace o slo?en?ch zkou?kách jednotliv?ch student?. Pomocí spojení (JOIN) těchto tabulek m??eme získat p?ehled o absolvovan?ch zkou?kách jednotliv?ch student?. Co ale v p?ípadě, ?e chceme získat seznam student?, kte?í doposud ?ádnou zkou?ku neslo?ili a nemají ?ádn? ?ádek v tabulce zkouska. Právě v těchto p?ípadech vyu?ijeme vno?en? dotaz s v?razem NOT EXISTS.?SELECT * FROM student WHERE NOT EXIST (SELECT 1 FROM zkouska WHERE student.uco = zkouska.uco)V?imněme si podmínky student.uco = zkouska.uco. Pokud bychom ji vynechali, dostali bychom neprázdn? v?sledek jen v p?ípadě, kdyby tabulka zkouska byla prázdná. Vlo?ená podmínka zajistí, ?e se bude tabulka zkouska prohledávat pro ka?dé uco studenta zvlá??.? ?astou chybou b?vá opomenutí nebo chybná definice propojovací podmínky, co? má za následek zcela chybn? v?sledek dotazu. V propojovací podmínce spojujeme sloupce z nad?azeného dotazu se sloupci vno?eného dotazu. Platí, ?e ve vno?eném dotazu se m??eme odkazovat na v?echny sloupce dotazu nad?ízeného, ale nikoliv naopak, v nad?ízeném dotazu nesmí b?t ?ádn? odkaz na v něm vno?ené dotazy.?V SQL vede ke stejnému v?sledku ?asto několik cest. Pokud ne?e?íme rychlost dotazu, zále?í na na?ich preferencích, kterou cestu zvolíme. Nap?íklad hledání nejstar?ího studenta m??eme ?e?it minimálně t?emi zp?soby:SELECT * FROM student WHERE datum_narozeni = (SELECT MIN (datum_narozeni) FROM student)?Tímto zp?sobem hledáme studenty, jejich? datum narození se rovná nejmen?ímu (nejstar?ímu) datu v tabulce.?SELECT * FROM student WHERE datum_narozeni <= ALL (SELECT datum_narozeni FROM student)Tímto zp?sobem hledáme studenty, jejich? datum narození?je men?í nebo rovno ne? v?echny datumy v tabulce. Pokud nemá nikdo men?í datum narození ne? já, jsem nejstar?í.SELECT * FROM student ridici WHERE NOT EXIST (SELECT 1 FROM student vnoreny WHERE ridici.datum_narozeni > vnoreny.datum_narozeni)Pro ka?d? ?ádek ?ídícího dotazu je prohledávána tabulka vno?eného dotazu (v na?em p?ípadě stejná tabulka student), zda obsahuje záznam s men?ím datem narození. Pokud takov? ?ádek neexistuje je splněna podmínka NOT EXISTS a dan? ?ádek je zobrazen.?Jako slo?itěj?í p?íklad m??eme uvést po?adavek, kdy chceme vidět jména student?, kte?í ji? absolvovali alespoň t?i zkou?ky? a v?echny na první pokus. Toto je opět p?íklad, kde k v?sledku povede více cest, podívejme se na jednu z nich. Pot?ebujeme nejprve vybrat ty studenty, kte?í mají v tabulce zkouska alespoň 3 ?ádky s r?zn?m kódem p?edmětu. Pokud se spokojíme s uco studenta, vysta?íme si s tabulkou zkouska.SELECT uco, COUNT(DISTINCT predmet) FROM zkouska GROUP BY uco?Tento dotaz nám vrátí p?ehled o po?tu zkou?ek jednotliv?ch student?.? Klí?ové slovo DISTINCT zajistí, ?e se bude ka?d? p?edmět po?ítat jen jednou. Studenti, kte?í doposud ?ádnou zkou?ku neslo?ili, v seznamu nebudou, proto?e ?ádn? záznam v tabulce nemají. To nám nevadí, proto?e nás zajímají pouze studenti s alespoň t?emi zkou?kami. Abychom vyfiltrovali studenty s jednou a dvěmi zkou?kami, doplníme dotaz o podmínku. Podmínku uvedeme nikoliv za WHERE, ale za klí?ové slovo HAVING, proto?e ji? pracujeme s agregovan?m v?sledkem (po?et zkou?ek v primární tabulce není).?Doplněn? dotaz vypadá takto:SELECT uco, COUNT(DISTINCT predmet) FROM zkouska GROUP BY ucoHAVING COUNT(DISTINCT predmet) >= 3?Nyní ově?íme, ?e v na?em seznamu není student s neúspě?nou zkou?kou (?ádn? ?ádek s F):SELECT uco, COUNT(DISTINCT predmet) FROM zkouska WHERE NOT EXIST (SELECT 1 FROM zkouska vnoreny WHERE vnoreny.uco = ridici.uco and vnoreny.znamka = 'F')?GROUP BY ucoHAVING COUNT(DISTINCT predmet) >= 3?Pokud se ptáte, pro? jsme misto vno?eného dotazu nevlo?ili p?ímo do p?vodního dotazu za WHERE podmínku znamka <> 'F', uvědomte si, ?e v tomto p?ípadě by nám v seznamu z?stal student, kter? má 3 a více úspě?n?ch zkou?ek a libovoln? po?et neúspě?n?ch. Jednoduchá podmínka by pouze odfiltrovala jeho neúspě?né pokusy je?tě p?ed provedením operace GROUP BY. My v?ak chceme studenty bez F, proto je nutné podmínku prově?it v zano?eném dotazu.?Nyní známe uco hledan?ch student? a pot?ebujeme doplnit jméno. P?ipojíme k v?slednému dotazu tabulku student.SELECT jmeno FROM student JOIN(SELECT uco, COUNT(DISTINCT predmet) FROM zkouska WHERE NOT EXIST (SELECT 1 FROM zkouska vnoreny WHERE vnoreny.uco = ridici.uco and vnoreny.znamka = 'F')?GROUP BY ucoHAVING COUNT(DISTINCT predmet) >= 3) filtr ON student.uco = filtr.ucoP?ipojení jsme provedli tak, ?e jsme ná? p?ipraven? dotaz zano?ili a umístili místo názvu tabulky na pozici za JOIN. Za uzavírací závorkou jsme si tento dotaz pojmenovali jako "filtr", abychom mohli definovat spojovací podmínku za ON. ?Cvi?ení:Vlo?te do tabulky patients dal?í záznam s hodnotou NULL místo data narození. Vyzkou?ejte chování v?ech SQL dotaz? uveden?ch v ?ásti seskupovaní dat. Najděte nejstar?ího studenta ?tvrt?m zp?sobemP?epi?te v?echny varianty a najděte nejmlad?ího studenta?Najděte p?edmět, ze kterého ?ádn? student nemá F.Analytické a statistické funkce SQLV?stupy z u?eníUmí pou?ívat analytické SQL funkceUmí pou?ívat statistické funkce SQLV následující kapitole si popí?eme pokro?ilé funkce, které nabízí databázov? systém ORACLE a POSTGRESQL. Tyto specifické funkce nám umo?ňují ?e?it elegantně typ úloh, které jsou pomocí standardního SQL ?asto jen velmi obtí?ně ?e?itelné. Jde o úlohy, kde je pot?eba:Pracovat s po?adím ?ádk?Odkazovat se na p?edchozí ?i následující ?ádky ve v?sledku dotazu Pracovat s agregovan?mi datyFunkce pro ur?ení po?adí ?ádk? ve v?sledku - Ranking functionVelmi ?asto pot?ebujeme v praxi stanovit po?adí záznam? ve v?sledku. Pro set?ídění v?sledk? nám standard SQL nám nabízí klí?ové slovo ORDER BY umís?ované na konec SELECT dotazu. O?íslovat v?sledek m??eme v prost?edí ORACLE pomocí pseudosloupce ROWNUM.?SELECT ROWNUM poradi, jmeno, prijmeni FROM student?Pseudosloupec ROWNUM p?i?azuje ?íslo v?sledn?m ?ádk?m. Bohu?el p?i?azení probíhá je?tě p?ed finálním set?íděním podle v?razu za ORDER BY, a proto následujícím zp?sobem po?adí student? dle abecedy nezískáme:SELECT ROWNUM poradi, jmeno, prijmeni FROM student?ORDER BY prijmeni, jmenoPokud chceme ?íslovat a? set?íděn? seznam, musíme dotaz zano?it:SELECT ROWNUM poradi, jmeno, prijmeni FROM (SELECT jmeno, prijmeni FROM student?ORDER BY prijmeni, jmeno)Na dal?í omezení narazíme, pokud pot?ebujeme vybrat záznamy podle po?adí, nap?íklad t?etího studenta dle abecedy. V takovém p?ípadě musíme podmínku vlo?it a? do t?etí vrstvy nad?azeného dotazu.?Nelze napsat:SELECT ROWNUM poradi, jmeno, prijmeni FROM student?WHERE rownum ?= 3ORDER BY prijmeni, jmeno?aniSELECT * FROM (SELECT ROWNUM poradi, jmeno, prijmeni FROM student?ORDER BY prijmeni, jmeno?) WHERE poradi = 3?D?vodem je, ?e databáze pseudosloupec ROWNUM ?nastavuje, a? kdy? ?ádek splní podmínku za WHERE, ?první ?ádek je v?dy ROWNUM = 1, a proto?e tento ?ádek nesplňuje podmínku ROWNUM = 3, na v?stup se nedostane. Databáze ?tak projde v?echny záznamy v tabulce, ale ?ádn? podmínku nesplní. Druh? problém je, ?e ROWNUM se nastavuje je?tě p?ed set?íděním p?es ORDER BY. Databáze vybere t?i ?ádky z tabulky, které terpve následně set?ídí.? V?sledkem druhého dotazu je t?etí ?ádek, kter? databáze na?la, ale nikoliv nutně t?etí dle ?p?íjmení studenta. Náhoda ?asto zp?sobí, ?e p?i letmém testování se m??e zdát, ?e dotaz funguje, teprve p?i hlub?í kontrole se uká?e, ?e jde o chybu.?Správně je:SELECT * FROM (SELECT?ROWNUM poradi, jmeno, prijmeni?FROM (SELECT jmeno, prijmeni FROM student?ORDER BY prijmeni, jmeno?))WHERE poradi = 3Tedy nejprve set?ídit, pak o?íslovat a teprve ve t?etí vrstvě filtrovat. Jak je vidět tato konstrukce je dost komplikovaná a navíc ne?e?í variantu, kdy máme studenty se stejn?m p?íjmením a tyto bychom chtěli ozna?it stejn?m po?adov?m ?íslem. Proto je pro tento typ úloh v?hodněj?í pou?ít některou z tzv. ranking function. ORACLE i POSTGRESQL nabízí t?i funkce:RANK()DENSE_RANK()ROW_NUMBER()? Funkce se li?í zp?sobem, jak?m ?ádky ?íslují v p?ípadě, kdy se objeví ve v?sledku stejné, dle pravidel t?ídění rovnocenné hodnoty. Funkce RANK() a DENSE_RANK() ?íslují stejné hodnoty stejn?m po?adov?m ?íslem. Funkce ROW_NUMBER p?iděluje ka?dému ?ádku unikátní ?íslo, u shodn?ch hodnot rozhoduje o po?adí náhoda. Rozdíl mezi RANK a DENSE_RANK spo?ívá v tom, jaké po?adové ?íslo následuje po sérii shodn?ch ?ádk?. Funkce DENSE_RANK pokra?uje nep?eru?enou ?íselnou ?adou, funkce RANK p?esko?í odpovídající po?et ?ísel. V?e osvětlí následující tabulka.Tabulka SEQ Tabulka \* ARABIC 12 -Srovnání v?sledk? funkcí RANK, DENSE_RANK a ROW_NUMBERP?íjmeníRANK()DENSE_RANK()ROW_NUMBER()Mlad?111Novák222Novák223Novák224Star?535 Syntaxe v?ech t?í funkcí je následující:RANK () OVER (ORDER BY sloupec)Za názvem funkce následují prázdné závorky, dále klí?ové slovo OVER, za kter?m následuje definice t?ídění, podle kterého chceme ur?ovat po?adí ?ádk?. Definice za ORDER BY v ranking funkci nemá ?ádnou vazbu k ORDER BY klauzuli na konci celého SQL dotazu. M??eme ur?ovat po?adí zcela nezávisle na finálním set?ídění v?sledku.?V p?ípadě, kdy pot?ebujeme stanovit po?adí v jednotliv?ch kategoriích v?sledku, lze? v?raz OVER dále roz?í?it o klauzuli PARTITION BY. Nap?íklad pokud chceme ?íslovat po?adí vy?et?ení jednotliv?ch pacient? podle data vy?et?ení.?SELECT patient_id, datum_vysetreni, RANK() OVER (PARTITION BY patient_id ORDER BY datum_vysetreni) poradiFROM vysetreni Tabulka SEQ Tabulka \* ARABIC 13 - ?íslování v?sledku s klauzulí PARTITION BYPatient_iddatum_vysetreniporadiPAT_112. 5. 20121PAT_214. 5. 20121PAT_223. 9. 20122PAT_24. 2. 20133PAT_315. 3. 20121Ranking funkce lze v dotazu umístit mezi klí?ová slova SELECT a FROM nebo jako v?raz pro t?ídění za závěre?né ORDER BY. Naopak nelze je umístit do podmínky za WHERE ?i HAVING. Pokud chceme pomocí nich definovat podmínku, musíme pou?ít vno?en? SQL dotaz. Funkce pro pro p?ístup k p?edchozím a následn?m ?ádk?m - LAG (), LEAD ()Dal?í speciální operací, která je ve standardním SQL obtí?né proveditelná, je práce s jin?m ne? aktuálně zpracovávan?m ?ádkem v set?íděném seznamu. P?ipomeňme, ?e standardní funkce a operátory jako SUBSTR(), LN(), TRUNC() atd. pracují v?dy s hodnotami aktuálně zpracovávaného ?ádku. Co kdy? ale chceme nap?íklad porovnat ?íselnou hodnotu jednoho ?ádku s p?edchozím ?ádkem, nap?íklad sledujeme u pacient? změnu v po?tu leukocyt? od p?edchozího vy?et?ení. Ve standardním SQL bychom museli pomocí JOIN operace spojit tabulku vysetreni se sebou samou, abychom dostali na jeden ?ádek hodnotu p?edchozího a následného vy?et?ení. V?razně snadněj?í a p?ehledněj?í je vyu?ití speciálních funkcí LAG() nebo LEAD(). Funkce LAG() nám umo?ňuje pracovat s p?edchozími záznamy, funkce LEAD() s následn?mi záznamy. Syntaxe obou funkcí je shodná. LAG (sloupec1, n, hodnota) OVER (ORDER BY sloupec2)Prvním parametrem je v?raz, nej?astěji název sloupce, jeho? p?edchozí nebo následující hodnota nás zajímá. V?raz m??e obsahovat libovoln? operátor ?i standardní funkci. Druh?m parametrem je celé ?íslo, které udává, o kolik ?ádk? se chceme vrátit nebo posunout vp?ed. T?etí nepovinn?m parametrem je hodnota, kterou chceme, aby funkce vrátila, pokud se posune mimo hranice vybrané mno?iny ?ádk? (tedy p?ed první nebo za poslední ?ádek). V?chozí hodnotou t?etího parametru je NULL. Následuje v?raz OVER s definicí set?ídění a p?ípadně seskupení zpracovávané mno?iny v?sledk?. Touto klauzulí ur?íme funkci LAG() ?i LEAD(), co míníme p?edchozím a následn?m ?ádkem.?V?raz ORDER BY v klauzuli OVER nijak nesouvisí s finálním set?íděním v?sledku SQL dotazu, i kdy? pro kontrolu správnosti na?eho v?sledku bude nej?astěji v?raz ORDER BY v klazuli OVER stejn? jako na konci celého SQL dotazu.?Mějme tabulku vy?et?ení se sloupci patient_id, datum_vysetreni a pocet_leukocytu. Zajímá nás změna po?tu leukocyt? u ka?dého pacienta oproti p?edchozímu vy?et?ení.?SELECT patient_id, datum_vysetreni, pocet_leukocytu,LAG(pocet_leukocytu, 1) OVER (PARTITION BY patient_id ORDER BY datum_vysetreni) predchozi_pocet,pocet_leukocytu - LAG(pocet_leukocytu, 1) OVER (PARTITION BY patient_id ORDER BY datum_vysetreni) zmenaFROM vysetreniTabulka SEQ Tabulka \* ARABIC 14 - Ukázka v?sledku funkce LAG()patient_iddatum_vysetrenipocet_leukocytupredchozi_pocetzmenaPAT_112. 5. 20127,4PAT_212. 5. 20125,3PAT_223. 9. 20122,45,3-2,9PAT_24. 2. 20133,72,31,4PAT_315. 3. 20121,9PAT_36. 9. 20124,51,92,6Reportovací funkceDatabáze ORACLE i POSTGRESQL nabízí nadstavbu standarního SQL, které se ozna?uje jako window nebo jako reportovací (reporting) funkce. V?raz window znamená, ?e funkce zpracovávají definovanou podmno?inu v?sledku dotazu, tzv. okno (window). V podstatě jde o aplikaci agrega?ních funkcí na vymezen? rozsah ?ádk?, kter? je nezávisl? na v?razu v sekci GROUP BY. Podívejme se na ?ast? p?ípad, kdy pot?ebuje znát procentické zastoupení zvolené kategorie v tabulce. Mějme tabulku pacient? se sloupcem ozna?ující pohlaví. Chceme získat sumární p?ehled s procentick?m zastoupením ?en a mu??. Pro získání po?tu jednotliv?ch kategorií pou?ijeme standardní seskupovací v?raz GROUP BY. Abychom ale mohli vyjád?it procentické zastoupení, pot?ebujeme zároveň celkov? po?et záznam?, co? ve standardním SQL m??eme provést pomocí vno?eného dotazu na pozici sloupce:SELECT pohlavi, COUNT(*) pocet, (SELECT COUNT(*) FROM pacient) celkem,COUNT(*) * 100 / (SELECT COUNT(*) FROM pacient) procento FROM pacientGROUP BY pohlavi Tabulka SEQ Tabulka \* ARABIC 15 - V?sledek seskupení s procentick?m vyjád?enímpohlavipocetcelkemprocentoF8020040M12020060Pomocí reportovací funkce m??eme stejného v?sledku dosáhnout bez vno?eného dotazu:SELECT pohlavi, COUNT(*) pocet, SUM (COUNT(*)) OVER () celkem,COUNT(*) * 100 / SUM (COUNT(*)) OVER () procento FROM pacientGROUP BY pohlaviJak je vidět, jde o aplikaci agrega?ní funkce (SUM) na v?sledek jiné agrega?ní funkce (COUNT) s vymezením rozsahu agregace. Rozsah agregace je definován za klí?ov?m slovem OVER, v na?em p?ípadě agregujeme p?es celou mno?inu, co? je vyjád?eno prázdn?mi závorkami. M??eme v?ak chtít vytvo?it sumární report, kde bude procento mu?? a ?en rozvedeno dle státní p?íslu?nosti: Tabulka SEQ Tabulka \* ARABIC 16 - Parciální procentické vyjád?enístatpohlavipocetcelkemprocento?RF5016031,25?RM11016068,75SRF304075SRM104025Tuto sestavu s parciálními sou?ty získáme drobnou úpravou p?vodního dotazu:SELECT stat, pohlavi, COUNT(*) pocet, SUM (COUNT(*)) OVER (PARTITION BY stat) celkem,COUNT(*) * 100 / SUM (COUNT(*)) OVER (PARTITION BY stat) procento FROM pacientGROUP BY stat, pohlavi Window funkce nejsou vázány jen na agrega?ní konstrukce s GROUP BY. Lze je pou?ít i v jednoduch?ch v?pisech, kde chceme srovnat konkrétní hodnotu nap?íklad s pr?měrem. Mějme tabulku s aplikovanou lé?bou konkrétního léku jednotliv?m pacient?m. Tabulka obsahuje identifikaci pacienta, datum podání a mno?ství podaného léku. V tabulárním reportu chceme srovnávat jednotlivé aplikace s celkov?m pr?měrem v celé tabulce. Tuto sestavu získáme z databáze následovně:SELECT patient_id, datum_podani, davka, AVG(davka) OVER () prumerna_davkaFROM lecbaPokud bychom vynechali klauzuli OVER, hlásila by databáze chybu nesprávného pou?ití agrega?ní funkce. V tomto p?ípadě je v?e v po?ádku a ve ?tvrtém sloupci bude ve v?ech ?ádcích stejná hodnota, která odpovídá pr?měrné dávce v celé tabulce lecba. Window funkce vyu?íváme také p?i v?po?tech kumulativních sou?t?. P?i kumulativním sou?tu se?ítáme v?echny hodnoty vybraného sloupce od prvního ?ádku a? po aktuální. Nap?íklad z tabulky lecba z p?edchozího p?íkladu chceme sledovat kumulativní spot?ebu léku v ?ase. Pou?ijeme agrega?ní funkci SUM() doplněnou o klauzuli OVER, ve které specifikujeme pravidlo set?ídění: SELECT patient_id, datum_podani, davka, SUM(davka) OVER (ORDER BY datum_podani) kumulativni_spotrebaFROM lecbaTímto zajistíme, ?e funkce SUM() agreguje data od prvního záznamu a? po aktuální ?ádek. Jde o implicitně definované agrega?ní okno. Tabulka SEQ Tabulka \* ARABIC 17 - Kumulativní sou?etpatient_iddatum_podanidavkakumulativni_spotrebaPAT_112. 3. 20121010PAT_218. 4. 20122030PAT_319. 4. 20121040PAT_320. 6. 20122060PAT_42. 9. 20123090Rozsah agregace (agrega?ní okno) lze specifikovat i explicitně, co? umo?ňuje po?ítat nap?íklad klouzav? pr?měr. Klouzav? pr?měr je pr?měrná hodnota vypo?ítaná v ?asové ?adě v definovaném ?asovém okně. Obvykle po?ítáme pr?měrnou hodnotu z několika p?edchozích hodnot. Toto explicitní okno definujeme za klauzuli ROWS BETWEEN, za ní? m??eme pou?ít některou z následujících mo?ností:UNBOUNDED PRECEDING - v?echny p?edchozí ?ádkyUNBOUNDED FOLLOWING - v?echny následující ?ádkyCURRENT ROW - aktuálně zpracovávan? ?ádekn PRECEDING - n p?edchozích ?ádk?n FOLLOWING - n následujících ?ádk?Kdy? se vrátíme k p?íkladu s celkovou pr?měrnou dávkou, m??eme jej roz?í?it o v?po?et klouzavého pr?měru z posledních t?í p?edchozích aplikací následovně: SELECT patient_id, datum_podani, davka, AVG(davka) OVER () celkova_prumerna_davka,AVG(davka) OVER (ORDER BY datum_podani ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) klouzavy_prumerFROM lecbaTabulka SEQ Tabulka \* ARABIC 18 - Klouzav? pr?měrpatient_iddatum_podanidavkaklouzavy_prumerPAT_112. 3. 20121010PAT_218. 4. 20122015PAT_319. 4. 20121013,333333PAT_320. 6. 20122015PAT_42. 9. 20123020Hodnota klouzavého pr?měru se spo?ítá jako sou?et 3 p?edchozích hodnot plus hodnota v po?ítaném ?ádku podělen? ?ty?mi. U prvních t?í ?ádk? se po?ítá pr?měr z redukovaného po?tu dostupn?ch ?ádk?. Statistické funkceAnalytické funkce jsou v databázovém systému ORACLE roz?í?eny o základní sadu statistick?ch funkcí. P?ehled nejv?znamněj?ích z nich uvádí tabulka. Tabulka SEQ Tabulka \* ARABIC 19 - Vybrané statistické funkce v ORACLEFunkcePopisUspo?ádáníCORRPearson?v korela?ní koeficientpárovéCORR_SSpearman?v korela?ní koeficientpárovéSTATS_BINOMIAL_TESTBinomick? testbinomickéSTATS_F_TESTF - testnepárovéSTATS_KS_TESTKolmogorov-Smirnov?v testnepárovéSTATS_MW_TESTMann Whitney testnepárovéSTATS_ONE_WAY_ANOVAANOVA -anal?za rozptylunepárovéSTATS_T_TEST_*Varianty T testupárové i nepárovéSTATS_WSR_TESTWilcoxn?v znaménkov? testpárovéREGR_INTERCEPTα koeficient rovnice lineární regrese párovéREGR_SLOPEβ koeficient rovnice lineární regresepárové Sou?ástí následujícího textu není detailní vysvětlení statistick?ch test? a funkcí ani p?edpoklady pro adekvátní pou?ití těchto funkcí. Jde o standardní testy a v?po?ty, jejich? podrobné vysvětlení lze nalézt v publikacích věnovan?ch statistickému zpracování dat. Vysvětlíme si pouze zp?sob pou?ití těchto funkcí v SQL dotazech. Pou?ití funkce je závislé na uspo?ádání vlastního testu, zda se jedná o srovnávání hodnot v páru nebo o hodnocení nepárové. Pou?ití funkcí s párov?m uspo?ádáním je p?ímo?aré, funkcím p?ímo p?edáváme názvy hodnocen?ch sloupc?. Funkce s nepárov?m uspo?ádáním mají jeden parametr pro vlastní data a druh? pro vysvětlovací (kategoriza?ní) proměnnou. V?po?et korelace Pou?ití funkcí CORR a CORR_S pro v?po?et korela?ního koeficientu je snadné. Funkce vy?adují 2 paremetry, kter?mi jsou nej?astěji 2 sloupce, mezi nimi? chceme spo?ítat dan? korela?ní koeficient. Jde o agrega?ní funkce, jejich? v?sledkem je jeden ?ádek. Stejně jako standardní agrega?ní funkce je lze roz?í?it o window klauzuli OVER (viz p?edchozí podkapitola o reportovacích funkcích). Mějme tabulku pacient? se sloupci, které obsahují namě?enou v??ku a hmotnost jednotliv?ch pacient?. Korela?ní koeficienty mezi v??kou a hmotností spo?ítáme následovně:SELECT CORR (vyska, hmotnost) pearson, CORR_S (vyska, hmotnost) spearmanFROM pacientKoeficienty zvlá?? pro mu?e a pro ?eny spolu s koeficientem za cel? soubor získáme takto: SELECT pohlavi, CORR (vyska, hmotnost) pearson, CORR_S (vyska, hmotnost) spearman,, CORR (vyska, hmotnost) OVER () pearson_vseFROM pacientGROUP BY pohlaviPearson?v korela?ní koeficient (funkce CORR()) je dostupn? takté? v databázi POSTGRESQL verze 9.1. Párové statistické testyMezi testy s párov?m uspo?ádáním pat?í párov? T-test (funkce STATS_T_TEST_PAIRED()) a Wilcoxn?v znaménkov? test (funkce STATS_WSR_TEST()). Tyto funkce mají 3 parametry, první dva jsou párově uspo?ádané vstupní parametry, t?etím parametrem je specifikace po?adovaného v?sledku ve formě jedné z následujících textov?ch konstant:STATISTIC - v?sledek testové funkceONE_SIDED_SIG - jednostranná míra v?znamnostiTWO_SIDED_SIG - oboustranná míra v?znamnosti Mějme tabulku, kde je uveden po?et leukocyt? p?ed cytotoxickou lé?bou a po cytotoxické lé?bě. Jde o klasické párové uspo?ádání, kde m??eme otestovat v?znamnost změny po?tu leukocyt? po provedené lé?bě. Statistickou v?znamnost získáme následovně:SELECT STATS_T_TEST_PAIRED (leu_pred_lecbou, leu_po_lecbe, 'TWO_SIDED_SIG') t_test,STATS_WSR_TEST (leu_pred_lecbou, leu_po_lecbe, 'TWO_SIDED_SIG') wilcoxon FROM lecbaOpět jde o agrega?ní funkce vracející jeden ?ádek, roz?í?ení o klazuli OVER v?ak není ve verzi ORACLE 11g podporováno. Nepárové statistické testyNepárové testy obecně testují proti sobě 2 nezávislé v?běry, kde nulová hypotéza je stanovena tak, ?e oba v?běry pochází ze stejné populace a ?e mezi nimi není statisticky v?znamn? rozdíl. Pokud test vyjde statisticky v?znamně, zamítáme tuto nulovou hypotézu. Oproti párovému uspo?ádání nemusí b?t velikost vzorku pro oba v?běry stejná, N se m??e li?it. Proto vstupní data těchto funkcí mají odli?n? formát ne? funkce párov?ch funkcí. Prvním parametrem je název sloupce, kter? kategorizuje vlastní data do 2 vzork? (nap?. hodnoty ze vprvního v?běru mouhou b?t ozna?eny "A", hodnoty z druhého v?běru písmenen "B"). Pokud sloupec obsahuje více jak dvě unikátní hodnoty, ohlásí databáze chybu). Druh? parametr je název sloupce s vlastními daty, t?etím parametrem je po?adovan? v?stup, stejně jako v p?ípadě párov?ch test?. Tabulka se vstupními daty pro nepárové testy vypadá následovně:Tabulka SEQ Tabulka \* ARABIC 20 - Vstupní data pro nepárové testyVZOREK_IDHodnotaA12A21A17B20B16B13B18Porovnání vzork? A a B pomocí funkce Mann Whitney testu a nepárového T-testu provedeme takto:SELECT STATS_MW_TEST (vzorek_id, hodnota, 'TWO_SIDED_SIG') mw,STATS_T_TEST_INDEP (vzorek_id, hodnota, 'TWO_SIDED_SIG') t_testFROM tabulkaV?sledkem je hladina v?znamnosti p. Jednofaktorová anal?za rozptylu (one way ANOVA)ANOVA je statistická metoda, která umo?ňuje porovnání více ne? 2 vzork?. Jednofaktorová ANOVA p?edstavuje nejjednodu??í p?ípad anal?zy rozptylu, kdy analyzujeme ú?inek jednoho faktoru na zkoumanou závislou proměnnou. Databáze ORACLE nabízí pro jednofaktorovou ANOVA anal?zu funkci STATS_ONE_WAY_ANOVA(). Vstupní data jsou stejná jako v p?ípadě nepárov?ch test?, pouze kategoriza?ní proměnná m??e obsahovat více ne? dvě unikátní hodnoty. Li?í se také nabídka mo?ností pro t?etí parametr, kter? ur?uje v?stupní hodnotu funkce. M??eme volit z těchto mo?ností: Tabulka SEQ Tabulka \* ARABIC 21 - Mo?né v?stupy funkce STATS_ONE_WAY_ANOVAV?stupní hodnotaPopisSUM_SQUARES_BETWEENSuma ?tverc? mezi skupinamiSUM_SQUARES_WITHINSuma ?tverc? uvnit? skupinDF_BETWEENStupeň volnosti mezi skupinamiDF_WITHINStupeň volnosti uvnit? skupinMEAN_SQUARES_BETWEENMean squares mezi skupinamiMEAN_SQUARES_WITHINMean squares uvnit? skupinF_RATIOPoměr MSB/MSWSIGMíra v?znamnostiMějme tabulku pacient? se sloupcem, kter? ur?uje stádium onemocnění v době diagnózy a sloupec s celkov?m p?e?itím v měsících od diagnózy. Vliv stádia na p?e?ití pomocí jednofaktorové ANOVA anal?zy posoudíme takto: SELECT STATS_ONE_WAY_ANOVA(stadium, preziti, 'F_RATIO') f_ratio, STATS_ONE_WAY_ANOVA(stadium, preziti, 'SIG') p_valueFROM patientsOpět jde o agrega?ní funkci, v?sledkem je tedy jeden ?ádek s hodnotou testu a statistickou v?znamností. Binomick? testPomocí binomického testu m??eme otestovat, zda procentick? v?skyt zkoumaného jevu odpovídá o?ekávané frekvenci. M??eme tak nap?íklad otestovat, zda procento mu?? v na?í tabulce pacient? odpovídá o?ekávan?m 50 procent?m v?ech pacient?:SELECT STATS_BINOMIAL_TEST (sex, 'M', 0.5, 'EXACT_PROB') exaktni, STATS_BINOMIAL_TEST (sex, 'M', 0.5, 'TWO_SIDED_PROB' ) oboustrannaFROM patientsPrvním parametrem je sloupec s kategoriální proměnou, druh? ur?uje testovanou kategorii, t?etí parametrem je o?ekávan? podíl v?skytu dané kategorie, ?tvrt?m parametrem specifikujeme po?adovan? v?stup. Kategoriální proměnná musí obsahovat právě dvě unikátní hodnoty. Varianty pro ?tvrt? parametr jsou následující:Tabulka SEQ Tabulka \* ARABIC 22 - Mo?né v?stupy funkce STATS_BINOMIAL_TESTV?stupní hodnotaPopisTWO_SIDED_PROBHodnota oboustranné pravděpodobnostiEXACT_PROBHodnota exaktní pravděpodobnostiONE_SIDED_PROB_OR_MOREHodnota jednostranné pravděpodobnosti (vět?í ne?)ONE_SIDED_PROB_OR_LESSHodnota jednostranné pravděpodobnosti (men?í ne?) Lineární regreseDatabázov? systém ORACLE i POSTGRESQL nám umo?ňuje snadno provést nad daty lineární regresi a vypo?ítat alfa a beta koeficienty regresní rovnice. Slou?í k tomu funkce REGR_INTERCEPT() a REGR_SLOPE(), které o?ekávají na vstupu dva parametry, prvním je název sloupce se závislou spojitou proměnou, druh?m je sloupec s nezávislou proměnnou. Vyhledávání v textuV?stupyUmí pou?ít operátor LIKE pro jednoduché prohledávání textuRozumí termínu regulární v?raz a umí jednodu??í v?raz sestavitUmí sestavit SQL dotaz s regulárním v?razemDoká?e pomocí SQL a regulárního v?razu extrahovat pot?ebnou informaci z textové informaceV této kapitole se seznámíme s databázov?mi prost?edky, které nám umo?ňují prohledávat textové ?etězce, tedy hodnoty ulo?ené ve sloupcích s obecn?m datov?m typem CHAR, VARCHAR, VARCHAR2, p?ípadně CLOB. P?edstaven bude jednak operátor LIKE, jednak tzv. regulární v?razy. Standardní funkce INSTR a operátor LIKEFunkci INSTR v databázi ORACLE, respektive STRPOS v p?ípadě POSTGRESQL, jsme si p?edstavili ji? ve t?etí kapitole. Pro p?ipomenutí tato funkce umo?ňuje prohledávat text na v?skyt specifikovaného pod?etězce, kter?m m??e b?t jeden a? N znak?. V?sledkem funkce je pozice nalezeného pod?etězce nebo nula v p?ípadě, kdy pod?etězec nebyl nalezen. Funkce má dva povinné parametry, prohledávan? text a hledan? pod?etězec. Ve funkci INSTR v p?ípadě pot?eby m??eme vyu?ít dal?í dva parametry, kter?mi jsou startovací pozice vyhledávání a po?adí v?skytu:SELECT INSTR('str? prst skrz krk', 'r', 5, 2) FROM DUALTento p?íklad hledá druh? v?skyt písmena 'r' od páté pozice ?etězce 'str? prst skrz krk'. V?sledkem je 13, co? je pozice druhého písmena 'r' hledaného od pátého znaku, tedy 'r' ve slově skrz. V?chozí pozice m??e b?t i záporné ?íslo, v tom p?ípadě se pozice po?ítá od konce ?etězce a vyhledávání probíhá od konce na za?átek: SELECT INSTR('str? prst skrz krk', 'r', -5, 2) FROM DUAL P?íkaz provede hledání od pátého znaku od konce, tedy od písmene 'z', směrem k za?átku ?etězce a v?sledkem je pozice 7, tedy písmeno 'r' ve slově prst.Funkci INSTR m??eme pou?ít i v definici podmínky WHERE:SELECT * FROM tabulka WHERE INSTR(sloupec, 'r', 1, 2) > 0Tento p?íklad vrátí ?ádky tabulky, které ve sloupci obsahují alespoň dvě písmena "r" (hledáme pozici druhého písmena 'r'). Obvyklej?ím zp?sobem vyhledávání ?ádk?, které obsahují v textovém sloupci ur?it? pod?etězec, je pou?ití operátoru LIKE a zástupn?ch znak?. Jako zástupn? znak se v p?ípadě ORACLE databáze pou?ívá znak "_" (podtr?ítko) a znak "%" (procento). Podtr?ítko nahrazuje právě jeden libovoln? znak, procento 0 a? N libovoln?ch znak?. Syntaxe operátoru LIKE je následující:sloupec LIKE '%pod?etězec%'Operátor LIKE pou?íváme p?i definici vyhledávacích podmínek za klí?ov?m slovem WHERE:SELECT * FROM tabulka WHERE sloupec LIKE '_rk'Tento p?íkaz najde v?echny ?ádky, které obsahují ve sloupci t?ípísmenné slovo kon?ící na "rk".V p?ípadě, kdy pot?ebujeme vyhledávat v textu samotn? zástupn? znak, definujeme pro operátor LIKE je?tě tzv. ESCAPE znak, kter? kdy? umístíme p?ed zástupn? znak, vrátí zástupnému znaku jeho p?vodní v?znam. Pokud tedy pot?ebujeme najít ?ádky, které obsahují ve sloupci symbol procento, definujeme podmínku následovně:SELECT * FROM tabulka WHERE sloupec LIKE '%\%%' ESCAPE '\'Jako ESCAPE znak zde slou?í zpětné lomítko, které zbavuje druh? znak procenta jeho funkce zástupného znaku. První a t?etí znak procento jsou interpretovány jako zástupné znaky, procento se tedy m??e ve sloupci vyskytovat kdekoliv (m??e ho p?edcházet i následovat libovolné mno?ství jin?ch znak?). Pomocí operátoru LIKE bychom se mohli pokusit hledat ?etězce obsahující datum následujícím zps?sobem:SELECT * FROM tabulka WHERE sloupec LIKE '%__.__.____%'.Hledáme takto dva znaky, te?ku, dva znaky, te?ku a ?ty?i znaky umístěné libovolně v textu. Tento zp?sob nám v?ak m??e vrátit mnoho fale?ně pozitivních v?sledk? (nap?. IP adresa 88.45.12.45 bude taká vyhovovat uvedenému vzoru) a naopak mnohé ?ádky p?ehlédne (nap?. 1.2.2000 má pouze jednu ?íslici p?ed první i druhou te?kou). Pro dosa?ení lep?ích v?sledk? musíme pou?ít tzv. regulární v?razy.Regulární v?razyRegulární v?razy je pokro?ilá technika prohledávání textu, se kterou se setkáme ve vět?ině programovacích jazyk?, v pokro?il?ch textov?ch editorech a takté? v databázov?ch systémech. Regulárním v?raz?m jsou věnované samostané publikace, v této kapitole se seznámíme pouze se základními konstrukcemi a se zp?sobem pou?ití v databázi ORACLE a POSTGRESQL. Základy regulárních v?raz?Na regulární v?raz se m??eme dívat jako na roz?í?ení operátoru LIKE. Jde o textovou ?ablonu, která se skládá z:hledan?ch znak?zástupn?ch znak?kvantifikátor?operátor?modifikátor?Zatímco operátor LIKE má pouze 2 zástupné znaky, u regulárních v?raz? je nabídka ?ir?í. Zástupn?m znakem m??eme odli?it nap?. ?íslici od písmena nebo tzv. bílého znaku (mezera, tabulátor). P?ehled základních zástupn?ch znak? uvádí REF _Ref394233903 \h Tabulka 23. Tabulka 23 - Zástupné znaky v regulárních v?razechZástupn? znakV?znam. (te?ka) Jak?koliv znak ^ Za?átek ?etězce $ Konec ?etězce \d?íslice \D V?e kromě ?íslice \wPísmeno, ?íslice, podtr?ítko \W Doplněk k \w \s Bíl? znak – mezera, tabulátor \S Doplněk k \s Pokud pot?ebujeme v textu hledat samotn? zástupn? znak v p?vodním v?znamu, tedy nap?íklad te?ku, musíme p?ed hledan? znak umístit zpětné lomítko. Toto pravidlo platí pro v?echny speciální znaky regulárních v?raz?. '^\^\..$'Uveden?m v?razem hledáme t?íznakové ?etězce, které za?ínají "^." a libovoln?m následujícím znakem. Zástupn? znak zastupuje v?dy právě jeden znak v prohledávaném ?etězci. Toto chování m??eme změnit pomocí tzv. kvantifikátor?, které v regulárním v?razu umístíme těsně za dan? zástupn? znak. P?ehled kvantifikátor? je uveden v REF _Ref394234011 \h Tabulce 24. Tabulka 24 - Kvantifikátory v regulárních v?razechKvantifikátorV?znam* 0 – n opakování ("greedy" chování)*?0 – n opakování ("nongreedy" chování)+ 1 – n opakování ("greedy" chování)+?1 – n opakování ("nongreedy" chování)? 0 nebo 1 opakování {m}P?esně m opakování {m,}m nebo více opakování {m,n}Minimálně m, maximálně n opakování Spojením zástupného znaku "." a kvantifikátoru "*" dostáváme regulární v?raz, kter? pokr?vá libovoln? textov? ?etezec. Tuto kombinaci pou?íváme ve dvou variantách:"hladová" (greedy)"nehladová" (nongreedy)Pokud pou?ijeme hladovou variantu, bude se hledat shoda s co nejdel?ím ?etězcem, naopak nehladová varianta hledá shodu s co nejkrat?ím ?etězcem. Blí?e se na tento problém podíváme v dal?í ?ásti kapitoly věnované nahrazování pod?etězc? s pou?itím regulárních v?raz?. Pokud bychom hledali v textu datum, mohli bychom pou?ít tento regulární v?raz:'\d{1,2}\.\d{1,2}\.\d{2,4}'Hledáme jednu a? dvě ?íslice jako den, následuje te?ka, jedna a? dvě ?íslice na pozici měsíce, te?ka a dvě a? ?ty?i ?íslice na pozici roku. Pokud by komponenty datumu oddělovaly kromě te?ky i mezery, roz?í?ili bychom v?raz o mezeru s otazníkem za ka?dou te?ku:'\d{1,2}\. ?\d{1,2}\. ?\d{2,4}'Ani tento v?raz v?ak není stále ideální, proto?e den i měsíc m??e ve skute?nosti na první pozici obsahovat jen vybrané ?íslice, konkrétně den 0, 1, 2 nebo 3, měsíc pouze 0 nebo 1. Tento problém nám pomohou ?e?it tzv. operátory regulárních v?raz?. Jejich p?ehled je uveden v REF _Ref394234126 \h tabulce 25. Tabulka 25 - Operátory regulárních v?raz?OperátorV?znam| nebo [abc] Jeden z uveden?ch znak? (a nebo b nebo c) [^abc] Libovoln? znak kromě uveden?ch (v?e kromě a b c) (abc) Uzav?ení skupiny znak? - blok \1, \2, \3, ... Odkaz na první, druh?, t?etí blok Pro specifikaci vybran?ch znak? m??eme vyu?ít bu? operátor svislítko "|" nebo operátor hranat?ch zvorek. Upraven? v?raz pro hledání datumu m??e vypadat takto:'(0|1|2|3)?\d\.(0|1)?\d\.\d{2,4}' nebo takto:'[0123]?\d\.[01]?\d\.\d{2,4}'V tomto p?ípadě jsou obě varianty rovnocenné, rozdíl by byl, pokud bychom kombinovali operátor se zástupn?m znakem. Zatímco operátor svislítko zástupné znaky interpretuje, operátor hranat?ch závorek nikoliv. V?raz hledající v textu ?íslici nebo bíl? znak proto musí vypadat takto:(\d|\s)Oproti tomu v?raz "[\d\s]" bude hledat v textu znaky "\" nebo "d" nebo "s". Pomocí kulat?ch závorek m??eme vybranou skupinu znak? uzav?ít do bloku a na ten se pak následně odkazovat pomocí zpětného lomítka a ?ísla po?adí bloku. Tuto techniku pou?ijeme, pokud hledáme nap?íklad repetitivní vzor a chceme ho definovat co nejobecněji. Nap?íklad pokud hledáme opakování t?í stejn?ch ?íslic, m??eme napsat:'(\d)\1\1'?íkáme tím, ?e chceme najít ?íslici (\d), za kterou se má opakovat stejn? znak (\1). V?raz je podstatně krat?í ne? rovnocenn? v?raz s pou?itím operátoru svislítka a v?pisem v?ech variant:'(111|222|333|444|555|666|777|888|999|000)'Pomocí odkaz? m??eme hledat text, kter? za?íná dvěmi ne?íseln?mi znaky a kon?í stejn?mi znaky v opa?ném po?adí:'^(\D)(\D).*\2\1$' Poslední komponentou regulárních v?raz? jsou modifikátory, které mění chování celého procesu vyhledávání. Základním modifikátorem je volba, zda chceme p?i vyhledávání rozli?ovat velikost písmen. Pokud ano, jde o "case sensitive" hledání, pro které se pou?ívá znak "c", pokud ne, jde o "case insensitive" ozna?ované znakem "i". Pou?ití regulárních v?raz? v databázi ORACLEPodpora regulárních v?raz? v databázi ORACLE zahrnuje funkce vyhledávání ?etězc?, hledání a extrakci pod?etězce i nahrazování pod?etězce za jin? text. Funkce pro vyhledávání ?ádk? v tabulce, které ve sloupci obsahují text odpovídající specifikovanému regulárnímu v?razu, se v databázi ORACLE naz?vá REGEXP_LIKE (). Její pou?ití je následující:SELECT * FROM tabulka WHERE REGEXP_LIKE(sloupec,'reg. vyraz', modifikator)Hledáme-li v tabulce ?ádky obsahující ve zvoleném textovém slouci datum, pou?ijeme tento p?íkaz:SELECT * FROM tabulka WHERE REGEXP_LIKE (sloupec, '[0123]?\d\.[01]?\d\.\d{2,4}', 'c');V?sledek funkce REGEXP_LIKE je hodnota true v p?ípadě, ?e dan? ?ádek tabulky obsahuje ve sloupci text odpovídající regulárnímu v?razu. Regulární v?raz m??eme pou?ít i pro vyhledání ?i extrakci pod?etězce z textu. Slou?í k tomu funkce, které jsou obdobou textov?ch funkcí SUBSTR() a INSTR(), pouze místo hledaného pevného ?etězce pou?íváme regulární v?raz. Funkce pro extrakci pod?etězce je definována takto:REGEXP_SUBSTR(text, 'reg. vyraz', hledat_od, vyskyt, modifikator) Pomocí této funkce extrahujeme ze sloupce pod?etězec specifikovan? regulárním v?razem, hledání probíhá od specifikované pozice (t?etí parametr), hledá se n-t? v?skyt (?tvrt? parametr) p?i zohlednění modifikátor? specifikovan?ch posledním parametrem. Povinné parametry jsou první dva. V?sledem funkce je extrahovan? pod?etězec nebo NULL.Zatímco funkci REGEXP_LIKE m??eme pou?ít pouze p?i definování vyhledávací podmínky, ostatní REGEXP funkce m??eme pou?ít v SQL dotazech na v?ech místech jako standardní funkce. Extrakci datumu z textu provedeme tak, ?e stejn? regulární v?raz pou?ijeme ve funkci REGEXP_LIKE() i REGEXP_SUBSTR(): SELECT REGEXP_SUBSTR(sloupec, '[0123]?\d\.[01]?\d\.\d{2,4}') datum FROM tabulka WHERE REGEXP_LIKE(sloupec, '[0123]?\d\.[01]?\d\.\d{2,4}') Pokud chceme získat pouze pozici pod?etězce místo samotného pod?etězce, pou?ijeme místo funkce REGEXP_SUSBSTR() funkci REGEXP_INSTR(). Její parametry jsou shodné, pouze návratová hodnota je pozice prvního znaku pod?etězce nebo nula.U extrakce pod?etězc? se vrátíme k pojm?m "greedy" a "nongreedy", které jsme zmínili u p?ehledu kvantifikátor?. Mějme situaci, kdy chceme z textu extrahovat text, kter? je uveden v závorkách. Text v závorkách m??e obsahovat libovolné znaky, nap?íklad v?sledek cytogenetického vy?et?ení. SELECT REGEXP_SUBSTR('translokace t(9;22)', '\(.*\)') FROM DUALPokud text obsahuje pouze jeden pár závorek, funkce správně vrátí pod?etězec "(9;22)". Pokud ale prohledávan? text obsahuje více závorek, projeví se "hladovost" kvantifikátoru "*":SELECT REGEXP_SUBSTR('translokace t(9;22) (Ph-chromozom) ', '\(.*\)') FROM DUALV?sledkem je "(9;22) (Ph-chromozom)", proto?e v?raz ".*" byl rozta?en na maximální po?et znak? uzav?en?ch mezi první otevírací závorkou a druhou uzavírací závorkou. Pokud chceme získat jen obsah první závorky, musíme pou?ít "nehladov?" kvantifikátor "*?":SELECT REGEXP_SUBSTR('translokace t(9;22) (Ph-chromozom) ', '\(.*?\)') FROM DUAL Pokud chceme hledan? pod?etězec nahradit jin?m textem, pou?ijeme funkci REGEXP_REPLACE(). Její syntaxe je následující:REGEXP_REPLACE(text, reg.v?raz, novy_text, hledat_od, vyskyt, modifikator)Oproti REGEXP_SUBSTR je tu rozdíl v t?etím parametru, kter?m je text nahrazující nalezen? vzor. Parametr "vyskyt" specifukuje, kolikát? nález se má nahradit, pokud uvedeme nulu (v?chozí hodnota), nahradí se v?echny v?skyty. Nahrazovan? text m??e obsahovat odkazy na bloky specifikované ve vyhledávaném regulárním v?razu. To nám umo?ní nap?íklad p?evést ?esk? formát datumu na formát (rok-měsíc-den):SELECT REGEXP_REPLACE(sloupec, '([0123]?\d)\.([01]?\d)\.(\d{4})', '\3-\2-\1') datum FROM tabulka WHERE REGEXP_LIKE(sloupec, '[0123]?\d\.[01]?\d\.\d{2,4}') Poslední v?znamnou funkcí z REGEXP rodiny je REGEXP_COUNT(), která vrací po?et nalezen?ch v?raz? v prohledávaném textu. Její syntaxe je:REGEXP_COUNT(text, reg.v?raz , hledat_od, modifikator)V?znam parametr? je stejn? jako v p?ípadě funkce REGEXP_SUBSTR(). Pou?ití regulárních v?raz? v databázi POSTGRESQLV databázi POSTGRESQL najdeme místo funkce REGEXP_LIKE() operátor "~" (vlnka), kter? provádí porovnání ?etězce s regulárním v?razem s ohledem na velikost písmen (case sensitive), zatímco operátor "~*" porovnává shodu bez ohledu na velikost písmen (case insesitive). SELECT * FROM tabulka WHERE sloupec ~ '[0123]?\d\.[01]?\d\.\d{2,4}'respektiveSELECT * FROM tabulka WHERE sloupec ~* '[0123]?\d\.[01]?\d\.\d{2,4}'Funkce REGEXP_SUBSTR() je v POSTGRESQL zastoupena funkcí SUBSTRING(), její? syntaxe je následující:SUBSTRING(text, reg.vyraz) Extrakci datumu z textového sloupce bychom tedy v databázi POSGRESQL provedli takto: SELECT SUBSTRING(sloupec, '[0123]?\d\.[01]?\d\.\d{2,4}') datum FROM tabulka WHERE sloupec ~ '[0123]?\d\.[01]?\d\.\d{2,4}' Oproti funkci REGEXP_SUBSTR() je tu u?ite?ná v?hoda, ?e m??eme ze specifikovaného regulárního v?razu extrahovat pouze omezenou ?ást, kterou uzav?eme do kulat?ch závorek. Zatímco tedy p?edchozí p?íklad vrátí celé datum, v následujícím p?íkladu m??eme drobn?m doplněním regulárního v?razu získat pouze rok z nalezeného datumu: SELECT SUBSTRING(sloupec, '[0123]?\d\.[01]?\d\.(\d{2,4})') datum FROM tabulka WHERE sloupec ~ '[0123]?\d\.[01]?\d\.\d{2,4}' Shodn? název jak v databázi ORACLE tak POSTGRESQL mají funkce pro nahrazení nalezeného pod?etězce za jin? text. Jde o funkci REGEXP_REPLACE(), kde rozdíl je pouze ve voliteln?ch parametrech. Syntaxe v POSTGRESQL je:REGEXP_REPLACE (text, reg.vyraz, novy_text [, priznaky ])V prohledávaném textu je nahrazen nalezen? vzor za nov? text. Pomocí p?íznak? ovlivňujeme chování funkce. Nejd?le?itěj?í p?íznaky shrnuje REF _Ref394234168 \h tabulka 26:Tabulka 26 - P?ehled nejv?znamněj?ích p?íznak? funkce REGEXP_REPLACE v POSTGRESQLP?íznakV?znamgNahradit v?echny v?skyty regulárního v?razu iPorovnávání bez ohledu na velikost písmencPorovnávání s ohledem na velikost písmen Pokud chceme v textu zamaskovat ?ísla hvězdi?kou, m??eme pou?ít toto ?e?ení:SELECT REGEXP_REPLACE ('Rodné ?íslo 770922/1234', '\d', '*', 'g') FROM GENERATE_SERIES(1,1) P?íznak "g" zajistí, ?e budou zaměněny v?echny nalezené ?íslice. Shrnutí Regulární v?razy jsou velmi mocn?m nástrojem p?i operacích s textem. K nev?hodám této techniky pat?í tě?ká ?itelnost v?sledn?ch v?raz? a ?asté hlavu lámající chování slo?itěj?ích konstrukcí. V této kapitole byla popsána základní syntaxe regulárních v?raz? a práce s nimi v prost?edí ORACLE a POSTGRESQL. Nejde v?ak o vy?erpávající popis, pro dal?í mo?nosti této techniky je t?eba prostudovat dokumentaci k vybranému databázovému systému. SQL skripty, u?ivatelské procedury a funkceV?stupyChápe, co jsou SQL skripty, SQL procedury a funkce a rozdíl mezi nimiDoká?e sestavit a spustit SQL skriptRozumí základním prvk?m jazyka PL/SQLUmí vytvo?it jednoduchou u?ivatelskou proceduru a funkci v jazyce PL/SQLJednotlivé SQL p?íkazy je velmi ?asto pot?eba spou?tět v men?ích ?i vět?ích sériích. Typick?m p?ípadem je vytvá?ení databázové struktury ?i manipulace s daty pomocí DML p?íkaz? INSERT, UPDATE, DELETE. Nejjednodu??ím zp?sobem seskupení SQL p?íkaz? je vytvo?ení SQL skriptu. Nejedná se o nic jiného ne? o se?azení pot?ebn?ch SQL p?íkaz? za sebou do textového souboru. Jednotlivé p?íkazy se standardně oddělují st?edníkem (;). M??eme kombinovat DDL p?íkazy (CREATE, ALTER, DROP) s DML p?íkazy. Na konec skriptu umís?ujeme potvrzovací p?íkaz COMMIT. Spu?tění SQL skriptu lze provést r?zn?mi zp?soby. Nejsnaz?í je spu?tění v grafickém klientu, kam do p?íkazového okna nakopírujeme p?íslu?n? skript a stiskneme tla?ítko pro spu?tění skriptu. Ukázku spu?tění jednoduchého skriptu lze vidět na obrázku REF _Ref375853346 \h Obrázek 5 (ORACLE) a REF _Ref375853353 \h Obrázek 6 (POSTGRESQL). Obrázek 5 - SQL skript v SQLdeveloperu (ORACLE)Obrázek 6 - SQL skript v pgAdmin (POSTGRESQL)SQL skript lze spustit také z ?ádkového klienta. V ORACLE klientovi sqlplus spustíme p?ipraven? skript, kter? jsme ulo?ili do souboru script.sql, tím, ?e napí?eme cestu a název souboru se skriptem a umístíme p?ed něj znak zaviná? (@). SQL > @/oracle/scripts/script.sqlPokud chceme spustit skript p?ímo z p?íkazové ?ádky opera?ního systému, spustíme sqlplus s parametrem:SQLPLUS login/heslo @/oracle/scripts/script.sqlV p?ípadě databáze POSTGRESQL je nejen ke spou?tění skript? k dispozici ?ádkov? klient psql. Spu?tění SQL skriptu z prost?edí psql provedeme p?íkazem \i:postgres=# \i script.sqlPokud chceme spustit SQL skript p?ímo z opera?ního systému, zavoláme psql následovně:PSQL -U login -f script.sql Ulo?ené procedury a u?ivatelské funkceSekvence DML p?íkaz? m??eme také ulo?it jako objekt databáze formou ulo?ené procedury nebo u?ivatelské funkce. Stejně jako tabulky jsou ulo?ené procedury a funkce sou?ástí databáze, mají svého vlastníka a p?ístup k nim se ?ídí p?idělen?m oprávněním. Stejně jako tabulky se vytvá?ejí p?íkazem CREATE a ru?í p?íkazem DROP. Rozdíl mezi ulo?enou procedurou a u?ivatelskou funkcí není velk?. Databáze POSTGRESQL má pouze u?ivatelské funkce, které ale doká?í toté? co ulo?ené procedury v ORACLE. V databázi ORACLE jedním z rozdíl? mezi procedurou a funkcí spo?ívá ve zp?sobu spou?tění. Zatímco u?ivatelskou funkci voláme stejně jako standardní funkci (nap?. SUBSTR() nebo ROUND()) v těle SQL dotazu, ulo?enou proceduru spou?tíme jako procedurální kód, co? si uká?eme v této kapitole. Databázové systémy ?asto kromě SQL jazyka podporují i specifick? procedurální jazyk. Pomocí něj lze provádět operace, které v SQL provést nelze nebo které jsou p?íli? komplikované. Procedurálním jazykem na rozdíl od SQL zpracováváme ?ádek po ?ádku pomocí programov?ch smy?ek a podmíněn?ch v?raz?. Procedurální kód nám také umo?ňuje provádět vstupně/v?stupní operace jako je ukládání dat do souboru na disk, odesílání emailem apod. V ulo?en?ch procedurách a u?ivatelsk?ch funkcích kombinujeme procedurální jazyk s SQL p?íkazy. Procedurální jazyky jsou je?tě více produktově specifické ne? jazyk SQL. Procedurální jazyk v ORACLE se ozna?uje jako PL/SQL, jazyk v PostgreSQL jako PGSQL. Základní koncept je v obou p?ípadech stejn?, odli?nosti jsou v?ak v syntaxi, jak si uká?eme dále. Základy databázového procedurálního jazykaDatabázov? procedurální jazyk vychází ze stejného konceptu a pou?ívá stejné prvky jako jak?koliv standardní procedurální programovací jazyk. V následujícím textu se seznámíme s těmito jeho základními prvky:Oddělova?e bloku kódu a oddělova? p?íkaz?Práce s proměnn?miPodmíněn? v?razProgramová smy?kaVolání jin?ch procedur ?i funkcíZpracování v?jimekProcedurální kód PL/SQL je v?dy ohrani?en klí?ov?mi slovy BEGIN na za?átku a END na konci. Pro oddělení jednotliv?ch p?íkaz? se pou?ívá st?edník (;) v?etně finálního slova END. Za klí?ov?m slovem BEGIN se naopak st?edník nevkládá. BEGIN a END vymezují tzv. blok kódu, kter? m??e obsahovat dal?í zano?en? blok opět ohrani?en? slovy BEGIN a END. Stejně jako v SQL funguje symbol "--" k oddělení jedno?ádkov?ch komentá??, více?ádkové komentá?e uzavíráme mezi "/*" a "*/". BEGIN--toto je jedno?ádkov? komentá?/* toto je více?ádkov? komentá? */END;V procedurálním kódu se vyu?ívají tzv. proměnné pro uchování a p?enos zpracovávan?ch hodnot. Proměnná je obdobou sloupce tabulky, má své jméno a datov? typ, základní typy proměnn?ch v?ak mohou p?ená?et v danou chvíli jen jednu hodnotu. Datové typy jsou stejné jako v p?ípadě SQL. Proměnné musíme deklarovat je?tě p?ed úvodním slovem BEGIN, kde uvedeme název proměnné a její datov? typ oddělen? mezerou, jednotlivé proměnné oddělujeme st?edníkem:i NUMBER (5);str VARCHAR2 (100);BEGIN....END;Proměnné p?i?adíme hodnotu pomocí operátoru p?i?azení, kter?m je ":=". Pomocí standardních operátor? m??eme provádět základní aritmetické operace. Proměnné m??eme vyu?ívat na místě konstant v SQL p?íkazech:i NUMBER (5);str VARCHAR2 (100);BEGINi:=1; str := 'text';i:= (i-14) * 9875 + 456;str := str || ' pripojeny text';DELETE FROM tab WHERE sloupec = i AND sloupec2 = str;INSERT INTO tab2 (sloupec, sloupec2) VALUES (i, str);END;Jednou ze základních technik v procedurálním programování je větvení kódu dle definované podmínky pomocí konstrukce IF-THEN-ELSE. V prost?edí PL/SQL má podmínková konstrukce následující tvar:IF podminka THEN prikaz1;ELSEprikaz2;END IF;Pokud je podmínka splněna provede se p?íkaz1 (obecně sada p?íkaz? mezi THEN a ELSE), pokud podmínka platná není, provede se p?íkaz 2 (sada p?íkaz? mezi ELSE a END). ?ást ELSE je nepovinná, naopak p?i pot?ebě vyhodnotit postupně více podmínek, m??eme konstrukci roz?í?it o ELSEIF (deklara?ní ?ást proměnn?ch leu a grade je vynechána):IF leu >= 3000 THEN grade := 'I';ELSIF leu < 3000 AND leu >= 2000 THEN grade := 'II'; ELSIF leu < 2000 and leu >= 1000 THEN grade := 'III'; ELSE grade := 'IV'; END IF;V konstrukci IF/ELSIF/ELSE se provede kód v?dy jen jednoho ramena. Pokud je p?i pr?chodu splněna podmínka více ne? jednoho ramena, provede se pouze první se splněnou podmínkou. Pokud není splněna ?ádná podmínka, provede se ?ást ELSE. Konstrukci IF/ELSEIF m??eme nahradit za podmíněn? v?raz CASE, kter? známe z SQL. Dal?ím prvkem procedurálního programování jsou programové smy?ky, které nám umo?ní provádět ur?itou ?ást kódu opakovaně. Smy?ek je v PL/SQL několik typ?, my si uká?eme jednu z nejvíce vyu?ívan?ch, která umo?ňuje procházet v?sledek SQL dotazu ?ádek po ?ádku. Její syntaxe je následující:FOR vektor IN (SELECT_dotaz) LOOPprikaz;END LOOP;Tato smy?ka vyu?ívá speciální proměnnou (vektor), která se p?i ka?dém pr?chodu naplní hodnotami jednoho ?ádku z v?sledné mno?iny specifikovaného SELECT dotazu. Na jednotlivé hodnoty (sloupce) se odkazujeme jako na sloupce tabulky, pouze místo názvu tabulky pou?íváme název proměnné (vektoru):FOR k IN (SELECT patient_id, sex, date_of_birth FROM patients) LOOPIF k.date_of_birth > SYSDATE THENINSERT INTO tabulka_chyb (patient_id, popis_chyby)VALUES (k.patient_id, 'Chybné datum narození');END IF;IF k.sex <> 'M' AND k.sex <> 'F' THEN INSERT INTO tabulka_chyb (patient_id, popis_chyby)VALUES (k.patient_id, 'Chybné pohlaví');END IF;END LOOP;Uvedená smy?ka projde postupně v?echny ?ádky tabulky patients a provádí p?íkazy mezi klí?ov?mi slovy LOOP a END LOOP, v na?em p?ípadě zkontroluje správnost vyplnění pohlaví a datumu narození. Zji?těné chyby zapisuje do tabulky tabulka_chyb. Pokud by tabulka patients byla prázdná, a tedy zdan? SQL dotaz by nevrátil ?ádn? ?ádek, cel? FOR blok by se p?i běhu programu p?esko?il. Vektor v tomto typu smy?ky na rozdíl od bě?n?ch proměnn?ch nemusíme dop?edu deklarovat, za ukon?ením END LOOP se v?ak ji? na něj nelze odkazovat. Smy?ku lze p?ed?asně ukon?it pou?itím p?íkazu EXIT, kter? umístíme do vhodné podmínky. PL/SQL podporuje i dal?í typy smy?ek jako je nekone?ná smy?ka LOOP .. END LOOP, smy?ka pro dop?edu neur?en? po?et pr?chod? WHILE-LOOP ?i smy?ka FOR-LOOP pro p?edem ur?en? po?et pr?chod?. Detaily lze najít v dokumentaci k databázi ORACLE. Uvnit? PL/SQL procedury m??eme volat jiné ulo?ené procedury. Bu? na?e vlastní nebo některou z procedur z bohaté knihovny databázové systému. Proceduru spustíme prost?m uvedením jejího jména a p?ípadnou specifikací parametr?, které uzav?eme do kulat?ch závorek. Procedury a funkce mohou b?t seskupeny do tzv. balíku (package), v tom p?ípadě p?i volání procedury vkládáme p?ed jejich název i název balíku oddělen? te?kou. V následujícím p?íkladě spustíme proceduru PUT_LINE, která je sou?ástí balíku DBMS_OUTPUT. Spustíme ji s textov?m parametrem:BEGINDBMS_OUTPUT.PUT_LINE('V?echno ?patně' ); END;Balík procedur DBMS_OUTPUT je sou?ástí databázového systému ORACLE. Jeho procedura PUT_LINE vypisuje na obrazovku obsah p?edaného parametru a slou?í nej?astěji k v?pisu ladících zpráv ulo?en?ch procedur a funkcí. Během provádění procedurálního kódu m??e dojít k chybě, nap?íklad kdy? se pokusíme dělit nulou nebo p?i?adit do proměnné hodnotu, která neodpovídá jejímu datovému typu. Těmto chybám lze ?áste?ně p?edcházet pomocí podmínkov?ch konstrukcí, ale existuje i jiná varianta ?e?ení chybov?ch stav?. Jde o tzv. zachytávání v?jimek a obslou?ení chybového stavu. Pokud dojde p?i běhu PL/SQL k chybě, dojde k p?eru?ení vykonávání kódu na chybovém ?ádku a generuje se v?jimka (exception). Pokud je na konci bloku kódu, kde chyba nastala, sekce pro zpracování v?jimek, p?esune se vykonávání kódu sem. Ka?dá vzniklá v?jimka s sebou nese identifikaci chyby, která ji zp?sobila. V sekci v?jimek pak lze reagovat na jednotlivé druhy chyb: BEGIN......EXCEPTION WHEN ZERO_DIVIDE THEN -- osetreni deleni nulou WHEN VALUE_ERROR THEN -- osetreni chyb pri konverzi mezi datovymi typy WHEN OTHERS THEN -- osetreni vsech ostatnich chybEND;P?edefinovan?ch druh? v?jimek je více, detaily lze nalézt v dokumentaci databázového systému. ?asto v?ak vysta?íme pouze s ramenem WHEN OTHERS, kdy chceme pouze zaregistrovat jakoukoliv vzniklou chybu. Reakcí na chybu m??e b?t provedení INSERT p?íkazu do tabulky chyb nebo nastavení v?stupních parametr? tak, aby signalizovali volající procedu?e vznik chyby. Pokud chyba není o?et?ena v sekci v?jimek, propaguje se do nad?ízeného bloku kódu nebo do volající procedury, kde opět je mo?né její zpracování. Pokud není zachycena a o?et?ena nikde, vykonávání celé procedury je ukon?eno s chybov?m hlá?ením. Pokud je v?jimka zachycena, pokra?uje vykovávání programu za sekcí v?jimek za koncov?m END, tedy vykonávání kódu se ji? nevrátí na p?vodní místo, kde chyba vznikla. Jeliko? v?ak bloky kódu mohou b?t zano?ené, m??eme elegantně navázat na chybov? stav a pokra?ovat dále ve vykonávání procedury:BEGINBEGININSERT INTO tab1....INSERT INTO tab2.....INSERT INTO tab3....DBMS_OUTPUT.PUT_LINE('V?echno OK' );EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('Něco se nepovedlo, ale..' );END;DBMS_OUTPUT.PUT_LINE('... jedeme dál' );...END;Pokud v uvedeném p?íkladu dojde k chybě p?i provádění některého z INSERT p?íkaz?, p?esko?í vykonávání do sekce EXCEPTION a následně pokra?uje program dál. Pokud k chybě nedojde, je sekce EXCEPTION p?esko?ena. V?jimky generuje databáze p?i vzniku chyb, je mo?né ale také v?jimku vyvolat cíleně p?ímo v kódu voláním p?íkazu RAISE. Vytvá?ení u?ivatelsk?ch procedur a funkcíJak bylo ?e?eno, ulo?ené procedury a funkce jsou objekty databáze stejně jako tabulky. Vytvá?íme je p?íkazem CREATE PROCEDURE, resp. CREATE FUNCTION. Syntaxe těchto p?íkaz? je následující: CREATE PROCEDURE jmeno_proc (parametry) ISi NUMBER; -- deklarace proměn?ch BEGIN –tělo procedury END; CREATE FUNCTION jmeno_funkce (parametry) RETURN datovy_typ IS i NUMBER;BEGIN --tělo funkce RETURN vysledek;END; Místo p?íkazu CREATE m??eme pou?ít CREATE OR REPLACE, kter? v p?ípadě, ?e procedura ?i funkce ji? existuje, provede její nahrazení. P?i vytvá?ení funkce na rozdíl od procedury musíme specifikovat datov? typ v?sledku funkce. V?sledek funkce spo?ítáme v těle funkce v libovolné proměnné, na konci funkce ozna?íme zvolenou proměnnou jako proměnnou obsahující finální v?sledek pomocí p?íkazu RETURN. Tímto p?íkazem vykonávání funkce kon?í. Parametry procedur a funkcí jsou proměnné, pomocí kter?ch p?edáváme procedu?e ?i funkci vstupní hodnoty p?i jejím volání. U procedur mohou parametry slou?it i k p?edávání v?sledk? (OUTPUT parametry). Parametr? m??e b?t 0 a? N, stejně jako interní proměnné mají sv?j název a datov? typ. Definují se za názvem procedury a funkce a vzájemně jsou odděleny ?árkou. Jako p?íklad funkce m??eme uvést u?ivatelskou funkci, která ze dvou datum? spo?íta věk, tedy rozdíl datum? v cel?ch rocích. CREATE OR REPLACE FUNCTION age (datum1 DATE, datum2 DATE) RETURN NUMBERISroku NUMBER;BEGIN roku := ABS(TRUNC(MONTHS_BETWEEN(datum1, datum2) / 12)); RETURN roku;END; Na vstupní hodnoty pou?ijeme ORACLE funkci MONTHS_BETWEEN, v?sledek podělíme dvanácti, od?ízneme desetinnou ?ást a funkcí ABS zajistíme, ?e v?sledek je kladné ?íslo pro p?ípad, ?e p?edáme funkci datumy v opa?ném po?adí. Tuto funkci m??eme pak následně pou?ít jako jakoukoliv jinou funkci v SQL dotazech:SELECT age(date_of_birth, SYSDATE) vek FROM patientsJako ulo?enou proceduru si m??eme definovat kód, kter? provádí hromadné mazání (sérii DELETE p?íkaz?) tabulek v na?em schématu.CREATE PROCEDURE uklid () ISBEGINDELETE FROM tab1;DELETE FROM tab2;DELETE FROM tab3; END;Abychom spustili ulo?enou proceduru z databázového klienta, musíme její volání vymezit jako PL/SQL blok pomocí BEGIN a END. Spu?tění procedury uklid bude vypadat takto:BEGINuklid();END;Procedurální jazyk PG/SQLProcedurální jazyk databáze POSGRESQL se ozna?uje jako PG/SQL nebo také plpgsql a od PL/SQL databáze ORACLE se odli?uje v několika bodech. P?edně databáze POSTGRESQL podporuje pouze u?ivatelské funkce, nikoliv procedury. Nicméně pomocí funkcí lze dosáhnou stejného efektu jako pomocí procedur v PL/SQL. Funkce PG/SQL definujeme následovně:CREATE FUNCTION nejaka_funkce() RETURNS NUMERIC AS $$DECLARE promenna NUMERIC := 30;BEGIN RAISE NOTICE 'Promenna obsahuje cislo %', promenna; -- % je ve vypisu nahrazeno obsahem promenne RETURN promenna;END;$$ LANGUAGE plpgsql; Na p?íkladu lze vidět některé odli?nosti (?erveně) v syntaxi oproti PL/SQL. Cel? procedurální kód je uzav?en mezi symboly "$$", na konci je pak navíc specifikace pou?itého jazyka za klí?ov?m slovem LANGUAGE. Deklarace proměnn?ch je uvozena klí?ov?m slovem DECLARE. P?íkaz RAISE, kter? v ORACLE slou?í v?hradně pro vyvolání v?jimky, se v POSTGRESQL vyu?ívá i k zobrazení zpráv. RAISE NOTICE pouze zobrazí specifikovan? text, zatímco RAISE EXCEPTION vyvolá v?jimku. Operátor p?i?azení (:=) je shodná s PL/SQL, stejně jako podmínkové konstrukce IF-ELSIF-ELSE. Podporovány jsou takté? smy?ky LOOP a WHILE. Smy?ka FOR pro procházení ?ádk? v?sledku SQL dotazu má drobné odli?nosti od verze v PL/SQL:CREATE OR REPLACE FUNCTION smycka() RETURNS integer AS $$DECLARE k RECORD;BEGIN FOR k IN SELECT * FROM patients LOOPRAISE NOTICE 'pacient %', k.patient_id; END LOOP; RETURN 1;END;$$ LANGUAGE plpgsql; P?edně proměnná pou?itá jako vektor musí b?t deklarována jako ka?dá jiná proměnná, datov? typ pro kurzor je RECORD. Druh? drobněj?í rozdíl je, ?e SQL dotaz ve FOR smy?ce není t?eba uzavírat do závorek. U?ivatelskou funkci voláme jako standardní funkce pomocí SQL dotaz?. Pokud chceme jen vynutit její spu?tění, pou?ijeme konstrukci:SELECT nazev_funkce() FROM GENERATE_SERIES(1,1)nebo zkrácenou variantuSELECT nazev_funkce()Tento SQL dotaz zajistí právě jedno spu?tění u?ivatelské funkce. Co se t?ká v?jimek, je zp?sob jejich zachytávání shodn? s PL/SQL, li?í se pouze seznam mo?n?ch typ? systémov?ch v?jimek. Export a import datV?stupy:Umí exportovat data z databázového klientaZná databázová rozhraní ozna?ovaná jako ODBC, OLED DBUmí na?íst data do databáze z textového souboruZná základní ovládání nástroje SQLLDRUmí pracovat s externími tabulkami ORACLEHlavním úkolem databázového systému je uchovávat data a na vy?ádání je poskytovat oprávněn?m u?ivatel?m. Doposud jsme data pouze prohlí?eli ve v?stupním okně klienta na na?em monitoru. V této kapitole si uká?eme, jak data exportovat ven z databáze, a to bu? do souboru, nebo p?ímo do jiné softwarové aplikace jako je nap?íklad statistick? program. Uká?eme si také, jak data naopak efektivně do databáze vkládat.?Export dat pomocí databázového klientaDatabázov? klient nám prost?ednictvím SELECT p?íkazu zp?ístupňuje data ulo?ená v databázi. V základním re?imu tato data pouze zobrazuje na monitor. Jednou z věcí, kterou se klienti li?í, je nabídka jiného zp?sobu poskytnutí (exportu) dat.?Grafick? klient ORACLE SQLDeveloper nabízí následující mo?nosti exportu dat:csvdelimitedfixedhtmlinsertloaderpdftext (tabulátor)XLSXMLK této nabídce se dostaneme, pokud v tabulce s v?sledkem dotazu klikne prav?m tla?ítkem my?i a zvolíme volbu Export:Obrázek 7 - Export dat v programu SQLDeveloper"Html" a "PDF" typ exportu jsou ur?ené pro prezenta?ní v?stupy, ostatní typy jsou ur?eny pro dal?í zpracování nebo import do jiné databáze ?i software. Varianta "insert" exportuje data ve formátu INSERT p?íkaz?, které m??eme spustit jako skript a p?enést tak data do jiné databáze. Obdobně typ "loader" exportuje data ve formátu ur?eném pro na?tení do jiné ORACLE databáze prost?ednictvím aplikace SQLLDR, o které si povíme dále v této kapitole. Exporty typu "csv" a "text" jsou podskupinou exportu "delimited". Jde v?dy o export dat do textového souboru, kde jsou jednotlivé sloupce exportované tabulky odděleny vybran?m oddělovacím znakem. V p?ípadě "csv" je to ?árka, v p?ípadě "text" je to znak tabulátoru, v p?ípadě "delimited" exportu si m??eme oddělovací znak zvolit. Varinta "fixed" exportuje data takté? jako textov? soubor, místo oddělova?e ale mají sloupce v?dy stejnou ?í?ku, v?echny hodnoty jsou zarovnané mezerami na maximální mo?nou ?í?ku sloupce. XLS export zapí?e data ve formátu MS Excel. XML typ exportu vytvo?í z dat XML soubor. XML formátem se budeme zab?vat v samostatné kapitole. Jednodu??í ?ádkov? ORACLE klient SQLPLUS umo?ňuje p?esměrovat v?stup z monitoru do textového souboru pomocí p?íkazu SPOOL, za které uvedeme cestu k v?stupnímu souboru. SPOOL jmeno_soboru Po spu?tění tohoto p?íkazu bude ve?ker? v?stup na monitor zapisován do uvedeného souboru. Ukon?ení zápisu provedeme p?íkazem SPOOL OFF. V PostgreSQL v klientovi pgAdmin je mo?né v?sledek dotazu exportovat p?es funkci Exportovat v menu programu Soubor. Jde o variantu "delimited" exportu do textového souboru. V ?ádkovém klientu m??eme vyu?ít p?íkaz COPY, kter? ulo?í obsah tabulky do specifikovaného textového souboru.?COPY patients TO 'C:/vystup.txt'Ve v?sledném textovém souboru jsou sloupce odděleny znakem tabulátoru. V uvedeném p?íkladu je v plném názvu v?stupního souboru skute?ně normální lomítko, nikoliv obrácené, jak je obvyklé v systému Windows. Pokud chceme exportovat v?sledek SQL dotazu, musíme ho uzav?ít do kulat?ch závorek:COPY (SELECT patient_id FROM patients) TO 'C:/vystup.txt'Univerzální databázová rozhraníODBCDal?í mo?ností jak získat data z databáze pro dal?í zpracování je na?tení do aplikací p?es databázová rozhraní ODBC nebo OLEDB. ODBC je star?í rozhraní, které umo?ňuje na?ítání dat z databáze do aplikací systému Windows (podporu ODBC najdeme v?ak i v systému Linux) prost?ednictvím tzv. ODBC ovlada?e (driveru), kter? je? obvykle dodáván? v?robcem databáze. ODBC standard nám umo?ní ?erpat data stejn?m zp?sobem z nejr?zněj?ích databází. Odli?ností a specifika databázov?ch systém? ?e?í právě zmíněn? ODBC ovlada?. ODBC rozhraní je sou?ástí systému Windows. Konfiguraci provádíme p?es Nástroje pro administrátory - ODBC rozhraní (Windows 7). Zde v kartě nalezneme seznam instalovan?ch ovlada?? ( REF _Ref395008308 \h Obrázek 8).?Obrázek 8 - Správce ODBC zdroj? v MS WindowsKonkrétní konexi do cílové databáze vytvo?íme bu? v kartě U?ivatelské DSN nebo Systémové DSN podle toho, zda chceme, aby spojení bylo dostupné pouze nám nebo v?em u?ivatel?m na?eho po?íta?e. Ve zvolené kartě klikneme na tla?ítko p?idat a vybereme ze seznamu adekvátní ovlada?. Pokud v nabídce po?adovaná ovlada? nenalezneme, musíme získat jeho instala?ní soubor a provést instalaci. Po dvojkliknutí na název ovlada?e se otev?e konfigura?ní okno, které se li?í podle zvoleného ovlada?e. Konfigura?ní okno ORACLE ODBC ovlada?e ukazuje REF _Ref394990662 \h Obrázek 9. Obrázek 9 - Konfigura?ní okno ORACLE ODBC ovlada?ePolo?kou "Data source name" identifikujeme spojení. Tento název budeme následně pou?ívat p?i p?ipojování do databáze. "Description" je nepovinn? popis spojení. "TNS Service name" je specifikum ORACLE databáze a identifikuje nám cílov? databázov? server. Polo?ka "UserID" nastavíme na databázov? login, kter? budeme p?i p?ipojení pou?ívat. Není to v?ak nutné, login a heslo se standardně specifikují a? v momentě p?ipojování do databáze. Ostatní nastavení ORACLE ODBC ovlada?e m??eme ponechat ve v?chozím nastavení. Nadefinované p?ipojení m??eme otestovat stisknutím tla?ítka "Test Connection". Po kliknutí na "OK" máme p?ipraveno ODBC zdroj, kter? m??eme za?ít pou?ívat. ODBC p?ipojení podporuje nap?íklad MS Excel. P?es aplikaci Microsoft Query lze na?íst data z databáze do zvoleného listu ( REF _Ref394990742 \h Obrázek 11). Po kliknutí na tuto volbu je nám nabídnut seznam nadefinovan?ch ODBC spojení. Zvolíme to, které jsme nadefinovali v p?edchozím kroku a v následujícím okně vyplníme ná? login a heslo do databáze ( REF _Ref394991099 \h Obrázek 12). Po úspě?ném p?ipojení m??eme definovat SQL SELECT dotaz, jeho? v?sledek bude p?enesen do excelového listu. Obrázek 11 - ODBC p?ipojení jako zdroj dat v MS ExcelObrázek 12 - P?ipojování do ORACLE databáze p?es ODBC spojení OLEDBOLEDB je nověj?í obdobou ODBC rozhraní. Opět pot?ebujeme ovlada? pro dan? databázov? systém, kter? zajistí, ?e stejn?m zp?sobem p?istupujeme k ulo?en?m dat?m bez ohledu na v?robce databáze. Rozdíl oproti ODBC z u?ivatelského pohledu je? v konfiguraci p?ipojení. To se obvykle provádí v konfigura?ním souboru s p?íponou .udl. Pokud v systému Windows poklepeme na takov?to soubor, objeví se dialog konfigurace OLEDB p?ipojení k databázi. V prvním okně je seznam nainstalovan?ch OLEDB driveru, dal?í okna se li?í podle konkrétního driveru ( REF _Ref394989073 \h Obrázek 13). Obrázek 13 - Okno konfigurace OLEDB p?ipojeníP?ipojení je mo?né definovat i bez konfigura?ního souboru p?ímo v cílové aplikaci. OLEDB je moderněj?í, rychlej?í a snadněji konfigurovatelné rozhraní ne? ODBC, nicméně nabídka ovlada?? je o něco ni??í a ne v?echny jsou plně funk?ní, zvlá?tě ty dodávané t?etí stranou. Pro ORACLE je nejvhodněj?í vyu?ívat ovlada? p?ímo od ORACLE.Také OLEDB p?ipojení je mo?né pou?ít pro p?enos dat do MS Excel, ve verzi 2007 a vy??í najdeme nabídku opět v menu "Data", skupina tla?ítek "Na?íst externí data", skupina funkcí "Z jin?ch zdroj?", varianta Pr?vodce datov?m p?ipojením. Import datImport pomocí INSERT p?íkaz?Základní metoda vkládání dat do databáze je prost?ednictvím p?íkazu INSERT. Jde o univerzální metodu, která se hodí pro vkládání men?ího objemu dat. Insert p?íkazy lze sestavit v textovém editoru, pomocí programovacích prost?edk? nebo t?eba v aplikaci MS Excel pomocí vzorc?. Tuto metodu si uká?eme na jednoduchém p?íkladu, kdy máme v excelovém souboru ?íseln? sloupec a sloupec s textem. Do t?etího sloupce sestavíme vzorec:Tabulka SEQ Tabulka \* ARABIC 27 - Ukázka tvorby INSERT p?íkaz? v MS Excel pomocí vzorc??íseln? sloupecTextov? sloupecVzorec1text1="INSERT INTO tabulka (cislo, text) VALUES (" & A2 & ",'" & B2 & "');"2text2="INSERT INTO tabulka (cislo, text) VALUES (" & A3 & ",'" & B3 & "');"3text3="INSERT INTO tabulka (cislo, text) VALUES (" & A4 & ",'" & B4 & "');"Vzorec v MS Excel za?íná v?dy znakem "=", pokud vzorec obsahuje text, musí b?t uzav?en mezi uvozovky. Symbol "&" spojuje text s odkazem na obsah vybraného pole. Odkaz je tvo?en písmenem sloupce a ?íslem ?ádku (A2 = sloupec A, druh? ?ádek). Nesmíme zapomenout na to, ?e p?íkaz INSERT vy?aduje, aby vkládaná textová hodnota byla ohrani?ena apostrofy. Pomocí vzorc? získáme následující slo?en? text INSERT p?íkaz?:INSERT INTO tabulka (cislo, text) VALUES (1,'text1');INSERT INTO tabulka (cislo, text) VALUES (2,'text2');INSERT INTO tabulka (cislo, text) VALUES (3,'text3'); Máme p?ipraven importní skript, kter? nakopírujeme do databázového klienta a spustíme. Import dat pomocí SQLLDR (ORACLE)Metoda vkládání dat pomocí sestavovan?ch INSERT p?íkaz? je vhodná do cca 10 tisíc záznam?. Pro vět?í objem dat je vhodněj?í pou?ít specializovan? software pro import data, tzv. datovou pumpu. Více ?i méně kvalitních datov?ch pump je ke sta?ení velké mno?ství, pro práci s databází ORACLE je v?ak nejspolehlivěj?í pou?ít aplikaci SQLLDR p?ímo od firmy ORACLE. Tato aplikace je sou?ástí instalace databázového klienta a pat?í mezi databázové utility. Je primárně ur?ena k importu dat z textového souboru do databáze. Jde o ?ádkovou aplikaci, která se ovládá p?es kontrolní soubor. Pomocí něj specifikujeme strukturu importovan?ch dat i cílovou tabulku. Importovat lze textov? soubor se sloupci oddělen?mi specifick?m oddělova?em i textové soubory s pevnou strukturou. Mo?nosti konfigurace aplikace Sqlloader jsou velice ?iroké, podíváme se na nejd?le?itěj?í prvky. Mějme textov? soubor (zdroj.txt), kter? obsahuje 3 sloupce, jeden ?íseln?, druh? textov?, t?etí bude obsahovat datumy, sloupce jsou od sebe odděleny st?edníkem. Ne? za?neme s importem, musíme nejprve vytvo?it cílovou tabulku:CREATE TABLE importovana_data(cislo NUMBER(10,0),text VARCHAR2(50),datum DATE);Nejstru?něj?í konfigura?ní soubor aplikace Sqlloader musí obsahovat minimálně odkaz na zdrojov? soubor, cílovou tabulku, specifikaci oddělova?e sloupc? a seznam importovan?ch sloupc?:LOAD DATA INFILE "C:\zdroj.txt" APPEND INTO TABLE importovana_dataFIELDS TERMINATED BY ";" (cislo, text, datum DATE "DD.MM.YYYY")Tímto zápisem v konfigura?ním souboru ?íkáme datové pumpě, aby na?etla data ze souboru zdroj.txt, kter? obsahuje 3 sloupce oddělené st?edníkem, a zapsala je do tabulky importovana_data. V??tem sloupc? v kulat?ch závorkách specifikujeme po?adí sloupc? ve zdrojovém souboru a u datumu navíc specifikujeme formát. Konfigura?ní soubor lze dále roz?í?it o sekci OPTIONS, pomocí které specifikujeme chování datové pumpy p?i importu. Nej?astěj?í konfigurovatelné vlastnosti shrnuje REF _Ref395009149 \h Tabulka 28: Tabulka SEQ Tabulka \* ARABIC 28 - Nej?astěji pou?ívané konfigurovatelné vlastnosti SQLLDRVlastnostV?znamSKIP=nP?esko?it na za?átku n ?ádk? (nap?. vynechat záhlaví)ERRORS=nUkon?it import po dosa?ení n chybROWS=nProvést commit po importu n ?ádk? DIRECT=trueVolba rychlej?í cesty importuPokud chceme, aby se p?i importu ignoroval první ?ádek v importovaném souboru a aby import skon?il p?i první chybě, vlo?íme na za?átek konfigura?ního souboru tento ?ádek: OPTIONS (SKIP=1, ERRORS=0)Je d?le?ité zmínit, ?e i v p?ípadě, ?e import skon?í chybou, bude proveden commit na úspě?ně importované ?ádky. Toto chování je nep?íjemné, pokud importujeme data do neprázdné tabulky, kdy p?i neúspě?ném importu nem??eme jednodu?e v?e smazat a za?ít znovu a zároveň nechceme v cílové tabulce duplicity. V tomto p?ípadě se doporu?uje nastavit vlastnost ERROR na velmi vysoké ?íslo, aby do?lo ke zpracování v?ech korektních ?ádk?. Chybové ?ádky budou aplikací zapsány do soubor.bad souboru, ?ím? vznikne sekundární importní soubor, kter? pro vy?e?ení p?í?in chyb m??eme opět zkusit importovat. Tuto iteraci opakujeme, dokud se nám nepoda?í importovat v?echny ?ádky. Druhou mo?ností je provádět import do prázdné pracovní tabulky a teprve z ní pomocí SQL p?íkazu INSERT SELECT p?evést data do cílové tabulky. U extrémně velk?ch soubor?, kdy samotn? import trvá hodiny a opakování importu je ?asově neefektivní, si p?i p?ed?asném ukon?ení importu pomáháme nastavením parametru SKIP, kter? nastavujeme za poslední úspě?ně importovan? ?ádek, p?ípadně těsně za detekovan? chybov? ?ádek. Pomocí p?epína?e DIRECT ?ídíme metodu importu. V?chozí metoda (DIRECT=false) je metoda INSERT p?íkaz?, kdy SQLLDR ze vstupního souboru sestavuje INSERT p?íkazy, které spou?tí. Touto metodou lze bez problém? zpracovat soubory v ?ádu sta tisíc ?ádk?, ale v p?ípadě vět?ích soubor? je ji? tato cesta ?asově náro?ná. U velk?ch soubor? se doporu?uje vyu?ít p?ímou cestu importu (DIRECT=true). Tato varianta je rychlej?í, nicméně nekontrolují se některá integritní omezení a m??e se tak do databáze dostat ?ádek, kter? by p?i standardní cestě byl odmítnut. U velk?ch soubor? je proto zcela na místě provádět import do pracovních tabulek, zde provést nezbytné kontroly a teprve následně p?ená?et data do cílov?ch tabulek. Import p?es externí tabulky (ORACLE)Jako alternativu p?i importu dat z textového souboru m??eme v p?ípadě databáze ORACLE pou?ít techniku ozna?ovanou jako externí tabulka (external table). Tato metoda je vhodná, pokud provádíme import dat v definované struktu?e opakovaně. Podstatou této metody je, ?e se díváme na importní textov? soubor jako na tabulku, která je p?ímo sou?ástí databáze, jde o databázov? objekt. Tento typ objektu vytvo?íme pomocí p?íkazu CREATE TABLE ORGANIZATION EXTERNAL. V tomto p?íkazu popí?eme umístění a strukturu textového souboru a s vytvo?en?m objektem m??eme následně pracovat jako s bě?nou interní tabulkou pomocí p?íkazu SELECT. Platí zde v?ak jistá omezení, mezi hlavní pat?í skute?nost, ?e zdrojov? soubor je nutné umístit p?ímo na databázov? server do vyhrazeného adresá?e (naproti tomu import p?es SQLLDR m??eme provádět odkudkoliv, kde máme nainstalovaného ORACLE klienta). Dále oproti interní tabulce nelze pou?ít p?íkazy INSERT, UDATE, DELETE pro manipulaci s daty uvnit? externí tabulky. Nad externí tabulkou takté? nelze vytvá?et indexy pro optimalizaci vyhledávání. Abychom mohli pou?ívat externí tabulky, musíme mít nejd?íve na serveru vytvo?en? a zp?ístupněn? importní adresá?. Tuto operaci obvykle provádí správce databáze:CREATE OR REPLACE DIRECTORY IMPORT_DIR AS '/home/oracle/import';GRANT READ, WRITE ON DIRECTORY IMPORT_DIR TO uzivatel;Do tohoto adresá?e nakopírujeme zdrojov? soubor a p?istoupíme k vytvo?ení objektu externí tabulky. Syntaxe tohoto p?íkazu p?ipomíná konfigura?ní soubor SQLLDR aplikace, nicméně není to to samé. Uká?eme si vzor p?íkazu pro import souboru s fixní velikostí sloupc? (pozicov? formát) a vzor pro import souboru, kde sloupce jsou odděleny vybran?m oddělova?em. V obou p?ípadech zdrojov? soubor obsahuje 3 sloupce, jeden ?íseln?, druh? textov?, t?etí je datum. Pro pozicov? soubor s názvem data_fix.txt pou?ijeme následující p?íkaz:CREATE TABLE data_fix( cislo NUMBER, jmeno VARCHAR2(20), datum DATE) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY IMPORT_DIR ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE FIELDS ( cislo CHAR(3), jmeno CHAR(6), datum CHAR(10) DATE_FORMAT DATE MASK "dd.mm.yyyy" ) ) LOCATION ('data_fix.txt') ); P?íkaz obsahuje dvakrát jména importovan?ch sloupc?, li?í se datové typy. V prvním p?ípadě jde o definici, jak se na data chceme dívat ze vnit? databáze, v druhém p?ípadě za klí?ov?m slovem FIELDS popisujeme situaci ve zdrojovém textovém souboru. Zde jsou v?echny prvky pouze text s vymezenou délkou, proto pou?íváme datov? typ CHAR s vymezením délky. V p?ípadě datumu doplňujeme specifikaci formátu ve stejné notaci jako v p?ípadě funkce TO_DATE. Po vytvo?ení externí tabulky si data m??eme prohlédnout bě?n?m SELECT p?íkazem:SELECT * FROM data_fixPokud jsou sloupce ve zdrojovém souboru definovány pomocí oddělovacího znaku, musíme p?íkaz pro vytvo?ení externí tabulky doplnit o specifikaci oddělova?e: CREATE TABLE data_delimited( cislo NUMBER, jmeno VARCHAR2(20), datum DATE) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY IMPORT_DIR ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ";" ( cislo CHAR(3), jmeno CHAR(6), datum CHAR(10) DATE_FORMAT DATE MASK "dd.mm.yyyy" ) ) LOCATION ('data_delimited.txt') );Vytvo?ení objektu externí tabulky nám nebrání manipulovat se zdrojov?m souborem. M??eme ho pomocí nástroj? opera?ního systému bez problém? nahradit a opětovně pou?ít SELECT p?íkaz pro prohlédnutí dat. Pokud SELECT p?íkaz skon?í chybou, najdeme bli??í vysvětlení v log souboru, kter? se vytvo?í na serveru v importním adresá?i. Z uvedeného vypl?vá, ?e externí tabulky jsou u?ite?n?m nástrojem pro pravidelně opakovan? import dat, nicméně neobejdeme se bez spolupráce s administrátorem databáze, resp. serveru. Import do POSTGRESQLTextov? soubor lze do POSTGRESQL importovat pomocí COPY p?íkazu ve variantě:COPY cilova_tabulka FROM 'zdrojovy_soubor'Podporován je import textového souboru, kde sloupce jsou odděleny specifikovan?m jednoznakov?m oddělova?em. Ten specifikujeme za klí?ov?m slovem DELIMITER, v?chozím oddělovacím znakem je tabulátor, kter? nemusíme explicitně specifikovat. V jin?ch p?ípadech je nutné p?íkaz COPY uvést v?etně oddělova?e:COPY patients FROM 'C:/vystup.txt' DELIMITER ';'Existují je?tě dal?í mo?nosti specifikace formátu zdrojového souboru, nicméně nabídka je nesrovnatelně men?í ne? v p?ípadě ORACLE databáze a její datové pumpy SQLLDR. P?íkaz COPY podporuje také binární formu exportu a importu, tuto variantu lze pou?ít v?ak pouze p?i p?enosu dat mezi dvěma POSTGRESQL databázemi. Základy XML Databázové systémy jsou primárně ur?eny k ukládání dat. Data je v?ak ?asto t?eba sdílet, vyměňovat a migrovat z jednoho systému do druhého. Základní mo?ností importu a exportu dat z databáze jsme si ukázali v p?edchozí kapitole. Jedním z formát?, kter? m??eme pro export dat pou?ít v klientovi Sqldeveloper je XML formát. Právě tomuto formátu je věnována tato kapitola. Seznámíme se s jeho základy, s nástroji pro jeho zpracování, se souvisejícími technologiemi a v neposlední ?adě se zp?sobem zpracování tohoto formátu v databázi.XML formát je textov? formát, kde p?ená?ené datové polo?ky jsou obklopeny popisn?mi polo?kami tzv. tagy: <nazev>obsah</nazev>? XML formát tak p?ipomíná HTML jazyk webov?ch stránek, nicméně jeden zásadní rozdíl. Názvy tag? v XML jsou ur?ovány autorem dané konkrétní datové struktury nikoliv samotn?m XML standardem. XML standard ve svém základu standardizuje pouze několik věcí. Mezi ně pat?í nap?íklad zp?sob p?enosu speciálních znak?. Speciálními znaky myslíme znaky, které mají v samotém XML standardu speciální v?znam, nap?. znaky "<" a ">" ohrani?ující názvy tag?. Pokud tyto znaky chceme zapsat do XML v p?vodním v?znamu, musíme je nahradit specifikovan?m v?razem. Jejich p?ehled uvádí REF _Ref394240803 \h Tabulka 29. Tabulka 29 - Vyhrazené znaky v XML a jejich kódováníVyhrazen? znakNáhradní v?raz< men?í ne??&lt;> vět?í ne??&gt;& ampersand?&amp;' apostrof?&apos;" uvozovky&quot;?? Základním prvky XML formátu jsouElementyAtributyKomentá?eInstrukceElement je základní jednotka struktury XML dokumentu. Má své jméno a obsah. Jméno je uzav?eno mezi symboly < a >, nap?. <jmeno_elementu>. Jménem elementu je ohrani?en obsah elementu, mluvíme o otevíracím a uzavíracím tagu. Uzavírací tag se od otevíracího li?í pouze lomítkem p?ed jménem elementu. Jednoduch? element v?etně obsahu vypadá takto:<jmeno_elementu>obsah elementu</jmeno_elementu>Element m??e b?t i prázdn?, bez obsahu:<book></book>Zjednodu?en? zápis prázdného elementu vypadá takto<book />?Jméno elementu nesmí obsahovat mezery a musí za?ínat písmenem. Bez problém? je mo?né pou?ívat ?íslice a podtr?ítko. Znaky s diakritikou ve jménech elementu jsou sice standardem povoleny, nicméně doporu?uje se jim vyh?bat stejně jako jin?m nealfanumerick?m znak?m. Rozli?ují se velká a malá písmena, <JMENO> je tedy odli?né od <jmeno>Obsahem elementu m??e b?t libovoln? text, pozor je t?eba dávat jen na speciální znaky, které jsou uvedeny v REF _Ref394240803 \h tabulce 29. Text obsahující vět?í mno?ství vyhrazen?ch znak? je bez nahrazování vlo?it do XML dokumentu jako CDATA blok. Pokud obsah elementu uvedeme mezi?"<![CDATA[" a "?]]>", budou zmíněné speciální znaky brány jako znaky standardní, bez speciálního v?znamu. Do CDATA bloku je mo?né uvést cokoliv s v?jimkou ukon?ovací sekvence?"]]>". CDATA blok nám poslou?í t?eba v p?ípadě, kdy chceme p?ená?et zdrojov? kód webové stránky v HTML. Element m??e obsahovat zano?en? dal?í element, ?ím? vzniká charakteristická stromová struktura XML dokumentu.<pacient><vysetreni><datum_vysetreni>27.7.2014</datum_vysetreni></vysetreni></pacient> Mo?nosti zano?ení jsou neomezené, platí v?ak, ?e na nejvy??í úrovni m??e b?t jen jeden, tzv. root element. Zakázán je taky p?ekryv element?. Následující zano?ení tedy není povoleno <pacient><vysetreni><lecba></vysetreni></lecba></pacient> Elementy mohou obsahovat tzv. atributy, které up?esňují informaci p?ená?enou v těle elementu. Atributy se zapisují do otevíracího tagu elementu, jejich hodnoty se uvádí v uvozovkách za znakem "=". <nazev_elementu nazev_atributu="hodnota atributu">obsah elementu<nazev_elementu><delka_hospitalizace jednotky="dny">9< delka_hospitalizace> V XML dokumentu je mo?né uvádět komentá?e, které se p?i zpracování ignorují a slou?í pouze k uvedení vysvětlivek. Komentá?e musí b?t ohrani?eny tímto zp?sobem?<!-- Toto je komentá? -->Posledním prvkem, kter? se obejvuje v XML dokumentech jsou procesní instrukce (Processing instructions). Jak název napovídá, jde o instrukce, které ?íkají XML procesoru, jak se zachovat. Procesní instrukce jsou uzav?eny mezi symboly <? a ?>. Ka?d? XML dokoment musí obsahovat na svém za?átku tuto procesní instrukci:<?xml version="1.0"?>?asto tato úvodní instrukce b?vá doplněna o znakovou sadu, která je pou?ita pro kódování znak?. Standardem je kódování UTF-8. <?xml version="1.0" encoding="UTF-8" ?>? ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download