Databázové systémy II

1. Databázové systémy II Přednáška č. 8 RNDr. David Žák, Ph.D. Fakulta elektrotechniky a informatikydavid.zak@upce.cz

2. Obsah • Operátor CAST • Operátory a funkce pro práci s hodnotami NULL • Řádkové a tabulkové konstruktory • Pokročilé dotazy ve standardu SQL 2 • Synonyma

3. Výrazy s operátorem CAST CAST ( <hodnota_výrazu> AS <datový_typ>) Tento výraz zajišťuje konverzi hodnoty do určeného datového typu. Kromě datového typu je možné použít ve výrazu CAST i doménu, což je specifická množina platných datových hodnot. Použití výrazů s operátorem CAST: • zejména při používání jazyka SQL z jiného programovacího jazyka, jehož datové typy neodpovídají datovým typům podporovaným standardem SQL (například pro převod data a času na řetězec) • Konverze dat z databázové tabulky, kde je sloupec definován pomocí špatného datového typu (například čísla v uložená jako řetězce znaků) • Odstranění rozdílů mezi různými typy pro stejná data ve 2 různých tabulkách

4. Výrazy s operátorem CAST Příklad SELECT ID, JMENO,CAST ( VYSKA AS VARCHAR(4)) FROM TRPASLICI;

5. Výrazy s operátorem CASE CASE WHEN <vyhledávací_podmínka> THEN <výsledný_výraz_pravda> [WHEN <vyhledávací_podmínka2> THEN <výsledný_výraz_pravda2>] [ELSE<výsledný_výraz_nepravda>] END Tento výraz umožňuje provádět jednoduché rozhodování. Příklad SELECT jmeno, CASE WHEN vyska>110 THEN 'velký' WHEN vyska>107 THEN 'střední' ELSE 'malý' END as velikost FROM trpaslici

6. Výrazy s operátorem CASE Příklad dotazu, který umožňuje prostřednictvím proměnné zvolit vhodnou variantu řazení výsledků dotazu: SELECT d.title, a.lastname, a.isbn, cost FROM authors a, details d WHERE a.isbn = d.isbn ORDER BY CASE :byorder WHEN 'TITLE' then title WHEN 'LASTNAME' then lastname WHEN 'ISBN' then isbn WHEN 'COST' then cost ELSE NULL END;

7. Práce s hodnotami NULL NVL(testovaný_výraz, výraz_if_NULL) Pokud není hodnota testovaný_výraz NULL, je vrácena hodnota testovaný_výraz, jinak výraz_if_NULL NVL2(testovaný_výraz, výraz_if_NOT_NULL, výraz_if_NULL) Pokud není hodnota testovaný_výraz NULL, je vrácena hodnota výraz_if_NOT_NULL, jinak výraz_if_NULL Příklady: select NVL(dodavatel_mesto, 'neznámé')from dodavatele; select NVL2(dodavatel_mesto, 'vyplněno', ' neznámé ')from dodavatele;

8. Výrazy s operátorem COALESCENCE Podmínky ve výrazech CASE se používají nejčastěji pro zpracování hodnot NULL. Obvykle je potřeba nahradit hodnotu NULL hodnotou nějakého řetězce nebo výchozí hodnotou. Operátor COALESCENCE vrací první nenulovou hodnotu. Pokud jsou všechny hodnoty NULL, vrací NULL. Příklad a jeho přepis do výrazu s operátorem COALESCENCE SELECT jmeno, CASE WHEN (prodej_plan IS NOT NULL) THEN plan_prodej WHEN (prodej_skutecnost IS NOT NULL) THEN prodej_skutecnost ELSE 0 END as prodej_objem FROM prodejci; SELECT jmeno, COALESCENCE(prodej_plan , prodej_skutecnost, 0 ) FROM prodejci;

9. Funkce NULLIF NULLIF( <výraz1>, <výraz2> ) NULLIFfunkce porovnává výrazyvýraz1 a výraz2.Pokudvýraz1 a výraz2se rovnají, NULLIF funkce vrací NULL. V opačném případě vrací výraz1. Příklady využití funkce NULLIF NULLIF(12, 12) vrací NULL NULLIF(12, 13) vrací 12 NULLIF('apples', 'apples') vrací NULL NULLIF('apples', 'oranges') vrací 'apples' NULLIF(NULL, 12) vrací chybové hlášení ORA-00932

10. Konstruktor řádkové hodnoty Standard SQL2 umožňuje řádky v SQL výrazech používat stejným způsobem, jako se používají skalární hodnoty. Standard poskytuje syntaxi pro vytváření řádků dat, umožňuje používat poddotazy s využitím celých řádků a definuje význam řádků pro operátory porovnání atd. Konstruktor řádkové hodnoty: Příkladem je například: (7, 'Smudla', NULL, 1993) což představuje řádek se 4 sloupci

11. Konstruktor tabulkové hodnoty Příklad ukazuje možnost použití pro porovnání konstruktoru řádkového hodnoty prezentovaného (vyska, narozen) a konstruktoru tabulkové hodnoty ((117, 1984), (115,1983)) SELECT jmeno FROM trpaslici WHERE (vyska, narozen) IN ((117, 1984), (115,1983)) Uvedená konstrukce může významně zkrátit zápis některých komplikovaných podmínek v dotazech. SELECT trpaslici.jmeno, count (*) FROM trpaslici WHERE ('Stydlin','na louce') = ANY (SELECT jmeno, sachta FROM sachty WHERE sachty.Id=tezby.Id_sachty AND trpaslici.Id=tezby.Id_trpaslika AND tezby.SKUTECNOST>0) GROUP BY trpaslici.jmeno;

12. CREATE TABLE AS SELECT Jednou z možností efektivní práce je vytvoření nové tabulky z tabulky existující. Použitím této syntaxe budou vytvořeny sloupce stejného typu, jako jsou sloupce ve výsledku vloženého dotazu SELECT, který je využit pro generování řádků nové tabulky. CREATE TABLE new_table  AS (SELECT * FROM old_table); Příklad: CREATE TABLE dodavateleAS (SELECT * FROM spolecnosti WHERE id > 1000);

13. CREATE TABLE AS SELECT Upřesnění sloupců a řádků vkládaných do nové tabulky : CREATE TABLE new_table  AS (SELECT column_1, column2, ... column_n FROM old_table WHERE podmínka); Příklad: CREATE TABLE trpaslici3  AS (SELECT JMENO,CAST ( VYSKA AS VARCHAR(4)) vyska_ch FROM TRPASLICI WHERE ID<5); Samozřejmě je možné, aby vložený dotaz SELECT pracoval s několika tabulkami, výsledná tabulka bude sloupci a řádky odpovídat výsledku vnořeného dotazu.

14. INSERT INTO …. SELECT Stejně jako je možné vytvořit tabulku s více řádky jediným příkazem, je možné i vložit více řádku do tabulky pomocí jediného výrazu. Příklad INSERT INTO trpaslici3 (jmeno, vyska_ch) SELECT jmeno, vyska FROM trpaslici WHERE jmeno='Stydlin'; Uvedený příklad zároveň demonstruje i automatickou konverzi formátů (sloupce vyska a vyska_ch).

15. JOIN s klauzulí USING Doplňme si možnosti zápisu spojení tabulek pomocí klauzule JOIN o syntaxi s využitím klauzule USING či NATURAL JOIN. Při přirozeném spojení pomocí NATURAL JOIN se porovnávají všechny sloupce z obou spojovaných tabulek, které mají stejné názvy. Při spojení pomocí USINGse porovnávají ty sloupce z obou spojovaných tabulek, které jsou uvedeny v závorce za klauzulí USING (sloupec1, sloupec2, …). Uvedené příklady mohou zpřehlednit zápis dotazů. SELECT * FROM sachty JOIN rudy2 ON sachty.id_rudy=rudy2.id_rudy SELECT * FROM sachty JOIN rudy2 USING (id_rudy) SELECT * FROM sachty NATURAL JOIN rudy2

16. CROSS JOIN Křížové spojení odpovídající kartézskému součinu vygenerujeme zápisem spojení s vynecháním podmínek spojení. SELECT jmeno, sachta FROM trpaslici, sachty Při zápisu s využitím klauzule JOIN je nutné použít ještě klíčové slovo CROSS SELECT jmeno, sachta FROM trpaslici CROSS JOINsachty

17. TRUNCATE Smaže všechny řádky tabulky nebo clusteru, není možné provést rollback TRUNCATE TABLE nazev_tabulky; Smazání řádků příkazem TRUNCATE může být více efektivní než odstranění tabulky příkazem DROP a opětovné vytváření příkazem CREATE TABLE. Důvodem je potřeba vytvoření velkého množství objektů závislých na tabulce (indexy, omezení, triggery). Smazání řádků příkazem TRUNCATE může být rychlejší než použití příkazu DELETE a to zvláště v případě, kdy nad tabulkou jsou vytvořeny triggery, indexy a další závislosti.

18. Vnořené dotazy Vnořené dotazy (poddotazy) se mohou vyskytovat prakticky na libovolném místě hlavního dotazu. Výsledkem vnořeného dotazu je obecně „virtuálního tabulka“ o několika řádcích a několika sloupcích (v určitých případech pak jednosloupcová či jednořádková) nebo jen jedna jediná hodnota. a) Vnořený dotaz v klauzuli FROM nebo JOIN – s výsledkem poddotazu se pracuje stejně jako při spojení s tabulkou či pohledem, výsledek poddotazu je v tomto případě vhodné označit aliasem b) Vnořený dotaz vracející jednu hodnotu v části WHERE – např. výsledek agregační funkce (SUM, COUNT, MIN, MAX, AVG) se porovnává s hodnotou v určitém sloupci tabulky (nebo je vnořeným dotazem vyhledána hodnota v určitém sloupci, kdy řádek tabulky je identifikován pomocí primárního klíče) c) Použití vnořeného dotazu v části WHERE s využitím množinových operátorů IN, ANY, SOME, ALL

19. Vnořené dotazy d) Využití výsledků vnořeného dotazu pomocí operací – UNION, INTERSECT, MINUS, … e) Použití vnořeného dotazu v části definice výstupních sloupců dotazu, kde se prostřednictvím vnořeného dotazu doplňují hodnoty na základě ostatních hodnot v daném řádku SELECT (select c1 from t1 b where a.c1 = b.c1), c2 FROM t1 a WHERE <condition>

20. Komplexní UPDATE Můžeme také provádět mnohem komplexnější příkazy UPDATE, například změnit hodnoty v řádku na základě hodnot v jiných tabulkách. Poněvadžnení možné uvést více než jednu tabulku v příkazu UPDATE, můžeme použít klauzuli EXISTS. Příklad: UPDATE suppliers SET supplier_name = (SELECT customers.nameFROM customersWHERE customers.customer_id = suppliers.supplier_id) WHERE EXISTS(SELECT customers.nameFROM customersWHERE customers.customer_id = suppliers.supplier_id); Kdykoli je supplier_id nalezen mezihodnotami customer_id v tabulce customers, jeho customers_name přepíše stávající hodnotu supplier_name. Předpokládáme, že sloupce customer_id je primárním klíčem tabulky customers.

21. Synonyma Použijte příkaz CREATE SYNONYM pro vytvoření synonyma, které je alternativním jménem pro tabulku, pohled, sekvenci, proceduru, funkci, balíček, uživatelem definovaný objekt nebo jiné synonyma. Příklad vytvoření synonamaoffices pro tabulku „locations“ ve schématu „hr“. CREATE SYNONYM offices FOR hr.locations; Specifikací PUBLIC vytvoříte veřejné synonyma, které jsou použitelné pro všechny uživatele. Pro použití synonyma však musí mít uživatel definována přístupová práva k danému objektu, na který se synonyma odkazuje. Příklad CREATE PUBLIC SYNONYM suppliersFOR app.suppliers;

22. Pseudo-sloupec ROWNUM ROWNUM – magický sloupec, příčinou řady potíží, proto je nezbytné mu porozumět, pak může být velmi užitečný. Lze jej použít: • Pro ladění dotazů, • Pro číslování v rámci dotazu, • pro provádění nejvyšších N- zpracování Sloupci ROWNUM budou přiřazena čísla 1, 2, 3, 4, .. N Hodnota ROWNUM není přiřazena řádku, řádek v tabulce nemá číslo. Hodnota ROWNUM je přiřazena řádku po jeho průchodu fází predikátu dotazu, ale před řazením nebo souhrnem.

23. Pseudo-sloupec ROWNUM Hodnota ROWNUM je zvýšena pouze po jejím přiřazení, proto následující dotaz nikdy nevrátí žádný řádek SELECT * FROM zamestnanci WHERE ROWNUM<=5ORDER BY mzda Správný zápis pro omezení počtu řádků: SELECT * FROM (SELECT * FROM zamestnanci ORDER BY mzda) WHERE ROWNUM<=5 Správný zápis pro uvedení rozsahu řádků: SELECT * FROM (SELECT dotaz.*, ROWNUM as radekFROM (SELECT * FROM trpaslici ORDER BY jmeno) dotaz) WHERE radek BETWEEN 3 and 5; Použití sloupce ROWNUM nahrazuje klauzuli LIMIT známou z MySQLpro určení rozsahu řádků na výstupu.

24. Náhrada ROWNUM Alternativně je možné použít analytické funkce ROW_NUMBER() SELECT * FROM (SELECT jmeno,ROW_NUMBER() OVER (ORDER BY jmeno) as radek FROM trpaslici) WHERE radek BETWEEN 3 and 5

25. Příkazy s klauzulí WITH Klauzule WITH umožňuje přiřadit název určitému poddotazu uvedenému před vlastním dotazem SELECT. V něm se pak můžete odkazovat na tento poddotaz zadáním jména poddotazu – obdobně jako při práci s pohledy.Oracle optimalizuje zpracování poddotazu uvedeného jména - buď jako inline pohled nebo jako dočasnou tabulku. Jednotlivé poddotazy se mohou odkazovat na předchozí poddotazy, stejně jako se na ně odkazuje hlavní dotaz.Použití klauzule WITH je velice vhodné v případě, kdy je výsledek poddotazu odkazován vícekrát v rámci jediného dotazu, například, když průměrné hodnoty zjištěné poddotazem musí být několikrát porovnávány během vykonávání dotazu a běžné řešení by znamenalo bud zřízení samostatného pohledu nebo uvedení několika totožných vnořených dotazů v rámci hlavního dotazu. Architektury a techniky DS – cv. 1

26. Příkazy s klauzulí WITH - příklad WITH <alias_name> AS (subquery_sql_statement)SELECT <column_name_list> FROM <alias>; Příklad: select store_name, sum(quantity)store_sales, (select sum(quantity) from sales)/(select count(*) from store) avg_sales from store s, sales sl where s.store_key = sl.store_key having sum(quantity) > (select sum(quantity) from sales)/(select count(*) from store) group by store_name Architektury a techniky DS – cv. 1

27. Příkazy s klauzulemi WITH - příklad WITH sum_salesAS select sum(quantity) all_sales from stores, number_stores AS select count(*) nbr_stores from stores, sales_by_store AS select store_name, sum(quantity) store_sales from store natural join sales SELECT store_name FROM store, sum_sales, number_stores, sales_by_store WHERE store_sales > (all_sales/ nbr_stores); Architektury a techniky DS – cv. 1

28. Syntaxe průchodu stromů v Oracle 9i,10 SELECTsloupceFROMtabulka[WHEREpodmínka3]START WITH podmínka1CONNECT BYpodmínka2[ORDER BY…] Řádky vyhovující podmínce ve START WITH jsou považovány za kořenové řádky na první úrovni vnoření Pro každou řádku na úrovni i se rekurzivně hledají přímí potomci vyhovující podmínce v klauzuli CONNECT BY na úrovni i+1 Řádka předka se v podmínce označuje klíčovým slovem PRIOR Na závěr jsou odstraněny řádky nevyhovující podmínce ve WHERE Pokud není definováno třídění, odpovídá pořadí průchodu pre-order Každý řádek obsahuje pseudo-sloupec LEVEL, obsahující úroveň řádku v hierarchii Architektury a techniky DS – cv. 1

29. Příklad hierarchického dotazu Příklad: SELECT PRIJMENI, JMENO,level-- pseudosloupec označující úroveň FROM A_HR.ZAMESTNANCI CONNECT BY MANAZER_ID = PRIOR ZAMESTNANEC_ID -- MANAZER_ID se rovná ZAMESTNANEC_ID u předchůdce START WITH MANAZER_ID is null; -- začni od zaměstnance, který nemá nadřízeného Architektury a techniky DS – cv. 1

30. Doplnění hierarchických dotazů • Pro jednotlivé záznamy můžete také získat cestu od nejvyššího záznamu (jak to znáte třeba ze souborového systému) nebo řadu dalších informací: • Funkce SYS_CONNECT_BY_PATH vrací cestu v hierarchii k aktuálnímu záznamu. • Klauzule CONNECT_BY_ROOT vrací hodnotu z příslušného záznamu nejvyšší úrovně (tj. například nejvyššího manažera). • Pokud byste chtěli výstup z dotazu použít pro zobrazení ve formě rozbalovací hierarchie tak, jak to třeba dělá u souborů Windows Explorer, bude se vám hodit i pseudosloupec CONNECT_BY_ISLEAF, který určuje, zda je aktuální záznam na poslední úrovni hierarchie (CONNECT_BY_ISLEAF=1) nebo zda má podřízené záznamy (CONNECT_BY_ISLEAF=0). Architektury a techniky DS – cv. 1

31. Příklad hierarchických dotazů • SELECT lpad(' ',level*3)||PRIJMENI||' '||JMENO name, • SYS_CONNECT_BY_PATH(PRIJMENI, '/') path, • CONNECT_BY_ROOT PRIJMENItopmgr, • CONNECT_BY_ISLEAF isleaf, • level • FROM A_HR.ZAMESTNANCI • CONNECT BY MANAZER_ID = PRIOR ZAMESTNANEC_ID • START WITH MANAZER_ID is null • ORDER SIBLINGS BY PRIJMENI; Architektury a techniky DS – cv. 1

32. Hierarchické dotazy dle ANSI SQL • Hierarchické dotazy dle ANSI SQL používají rekurzívní WITH klauzuli, která se odkazuje sama na sebe.Zavedení této syntaxe do Oracle 11gR2 zajišťuje hierarchickým dotazům Oracle SQL kompatibilitu s ANSI. Architektury a techniky DS – cv. 1

33. Hierarchické dotazy dle ANSI SQL with employees (empno, name, job, mgr, hierlevel) as ( select empno, ename, job, mgr, 1 from emp where mgr is null union all select e.empno, e.ename, e.job,e.mgr, m.hierlevel + 1 from emp e join employees m on (m.empno = e.mgr) -- podmínka spojení a rekurzívní volání ) select * from employees Viz např. http://technology.amis.nl/blog/6104/oracle-rdbms-11gr2-goodbye-connect-by-or-the-end-of-hierarchical-querying-as-we-know-it Architektury a techniky DS – cv. 1

34. Hierarchické dotazy dle ANSI SQL with org_pracovniku (ZAMESTNANEC_ID, PRIJMENI, JMENO, MANAZER_ID, uroven) as ( select ZAMESTNANEC_ID, PRIJMENI, JMENO, MANAZER_ID , 1 from A_HR.ZAMESTNANCI where MANAZER_ID is null-- začni od zaměstnance, který nemá nadřízeného union all-- spoj výsledek předchozího dotazu s výsledkem následujícího dotazu select pracovnici.ZAMESTNANEC_ID, pracovnici.PRIJMENI, pracovnici.JMENO,pracovnici.MANAZER_ID, manazeri.uroven+ 1 from A_HR.ZAMESTNANCI pracovnici joinorg_pracovniku manazeri on (manazeri.ZAMESTNANEC_ID = pracovnici.MANAZER_ID) -- podmínka spojení a rekurzívní volání ) select * from org_pracovniku Viz např. http://technology.amis.nl/blog/6104/oracle-rdbms-11gr2-goodbye-connect-by-or-the-end-of-hierarchical-querying-as-we-know-it Architektury a techniky DS – cv. 1