Databázové systémy 2

1. Databázové systémy 2 Cvičení č. 9 Ing. Tomáš Váňa (tomas.vana@student.upce.cz) Fakulta elektrotechniky a informatikyUniverzita Pardubice

2. Obsah cvičení • Opakování (procedury, funkce, balíčky) • Triggery pro klientské události • Práce s datem Databázové systémy 2 – cvičení 9

3. Triggery pro klientské události • Vyvolány konkrétní událostí nad uživatelovým schématem (CREATE, GRANT, REVOKE, TRUNCTE, …) • Kompletní výčet událostí: • http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm#CHDGIJDB • Atributy triggerů nad klientskými událostmi. - Atributy blíže popisují událost, ke které došlo. Viz tabulka: Databázové systémy 2 – cvičení 9

4. Oracle a datum • Datový ty DATE. • Základní funkce: • TO_DATE - Provede převod ze znakového datového typu na datový typ DATE. • TO_CHAR - Převede datový typ DATE na znakovou reprezentaci. • SYSDATE - Vrátí aktuální datum. • ROUND - Funkce vrátí zaokrouhlené datum ke specifické jednotce (měsíc, rok, …). • TRUNC - Funkce provede „oříznutá“ data ke specifické jednotce. • MIN, MAX - Nalezení nejmenšího a největšího data. Databázové systémy 2 – cvičení 9

5. Oracle a datum • TO_DATE: • TO_DATE(<string1>, [ format_mask ], [ nls_language ]) • string1 - řetězce, který chceme převést na datum. • format_mask - maska pro námi zadaný řetězec • nls_language - maska se vybere podle národního nastavení databáze • SELECT TO_DATE('21.02.2006','DD.MM.YYYY') FROM dual; • SELECT TO_DATE('20.11.2011 14:15','DD.MM.YYYY HH24:MI') FROM dual; • http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions180.htm Databázové systémy 2 – cvičení 9

6. Oracle a datum • TO_CHAR: • TO_CHAR(<date>, [ format_mask ], [ nls_language ]) • date - datum, které konvertujem na řetězec. • format_mask - maska formátu výstupního řetězce. • nls_language - maska se zvolí dle národního nastavení databáze. • TO_CHAR('4.12.2011','MM/DD/YY') = „12/04/11“ • TO_CHAR('21.12.2011','DDD') = „355“ • TO_CHAR('21.12.2011', 'Q') = 4 • http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions183.htm • http://www.techonthenet.com/oracle/functions/to_char.php Databázové systémy 2 – cvičení 9

7. Oracle a datum • Aritmetika • Ve spojení s daty můžeme využívat operátory + a - • Příklady: • <date> + <integer> Přičte daný počet (integer) dní k datu (date) • <date> - <integer> Odečte daný počet dní od data • <date> - <date> Vrátí rozdíl (ve dnech) mezi oběma daty. • <date> + <date> NELZE • Další příklady práce s type DATE: • http://psoug.org/reference/date_func.html Databázové systémy 2 – cvičení 9

8. Příklady • Vytvořte balíčekCV9_DATUMY. • V rámci balíčku vytvořte následující funkce: • Funkcedatumy_rozdil(datum_odDATE, datum_doDATE, typCHAR) • Návratovým typem je datový typ NUMBER. • Funkce vrátí časový rozdíl mezi zadanými daty dle zvoleného typu. • Typ může nabývat hodnot D (Dny), H (Hodiny), M (Minuty) a S (Sekundy). • Výsledek zaokrouhlete na dvě desetinná místa. • Ověřte, zda byly vyplněny oba datumy a v parametru typ je zadána přípustná hodnota. V případě že nebude dodrženo, funkce skončí výjimkou s textem „Špatné prarametry” Databázové systémy 2 – cvičení 9

9. Příklady • Pokračovaní balíčku CV9_DATUMY: • Funkcepracovni_den(datumDATE) • Návratový typ NUMBER. • Funkce určí, zda zadané datum je pracovním dnem (funkce vrátí 1) či nikoliv (0). • Zanedbejte svátky. (Tedy pokud byl svátek v pracovní den, pro funkci je to stále pracovní den) . • Funkceden_v_mesici(datumDATE, prvniNUMBER) • Návratový typ DATE. • Funkce vrátí datum, jenž bude prvním dnem měsíce (první = 1) či posledním dnem v měsíci (první = 0) vzhledem k zadanému datu. • Př. datum 21.1.2011 a první = 1 = 1.1.2011 Databázové systémy 2 – cvičení 9

10. Příklady • Vytvořte pohledcv9_trpaslici_tezby_sum(JMENOVARCHAR2, VYTEZENONUMBER, DEN_TYPVARCHAR2) • Pohled vytvoří přehled, ve kterém bude celkový souhrn vytěžené rudy pro každého trpaslíka a typ dne (Po-Pá/So-Ne). • Pro určení typu dne můžete využít funkci z vašeho balíčku cv9_datumy. • Pohled seřaďte vzestupně dle jména trpaslíka. Databázové systémy 2 – cvičení 9

11. Příklady • Vytvořte funkciCV9_TEZBY_PREDPOVED (P_ID_TRPASLIKANUMBER , P_DATUM_ODDATE , P_DATUM_DODATE ) RETURN NUMBER • Funkce vypočte odhad vytěžené rudy v zadaném období (p_datum_od - p_datum_do) a u vybraného trpaslíka na základě historických těžeb v tabulce A_SNEHURKA.TEZBY. • Jako základ pro výpočet použijte průměr vytěžených kg na kalendářní den v únoru 2006 • Ve funkci zkontrolujte, zda jsou vyplněny všechny parametry. Pokud nejsou, pak funkce skončí výjimkou. Výjimka bude obsahovat text: „Špatné parametry“ Databázové systémy 2 – cvičení 9

12. Příklady • Pokračování CV9_TEZBY_PREDPOVED • Dále zkontrolujte, zda datum_od není větší než datum_do. Pokud bude, pak funkce skončí výjimkou. Textem výjimky bude: „Datum od je větší než datum do!“. • Dále zajistěte, aby zadané období nekolidovalo s pracovním obdobím zadaného trpaslíka. Pokud bude, vyvolejte výjimku s textem: • „Zadaný rozsah spadá do pracovního období trpaslíka! “ • Za začátek pracovního období se považuje první den kdy trpaslík pracoval na šachtě, jako poslední den období se volí poslední den v záznamu těžeb. Databázové systémy 2 – cvičení 9

13. Příklady • Vytvořte proceduruCV9_TRPASLICI_PREDPOVED (P_DATUM_ODDATE , P_DATUM_DODATE ) • Procedura na standardní výstup vypíše s pomocí funkce CV9_TEZBY_PREDPOVEDpředpověď pro všechny trpaslíky v zadaném období. • Zajistěte, aby procedura vždy vypsala všechny trpaslíky, bez ohledu na to, zda volání funkce CV9_TEZBY_PREDPOVEDskončilo výjimkou. • Pokud skončí výjimkou, tak u daného trpaslíka zobrazte text: „Chyba při výpočtu“. • Pokud uživatel nevyplní datum_od či datum_do, tak je nahraďte aktuálním datem. • Předpovídané množství zaokrouhlete na jedno desetinné místo. • Výstup procedury zhotovte dle příkladů na následující stránce. • Dodržte formátovaní včetně odsazení. Databázové systémy 2 – cvičení 9

14. Příklady Databázové systémy 2 – cvičení 9

15. Příklady • Vytvořte tabulkucv9_my_actions ( uzivatelVARCHAR2(50) NOT NULL, udalostVARCHAR2(50) NOT NULL, typ_objektuVARCHAR2(50), nazev_objektuVARCHAR2(50), datumTIMESTAMP); Databázové systémy 2 – cvičení 9

16. Příklady • Vytvořte triggercv9_trigger_ddl, který bude do tabulky cv9_my_actions ukládat informace o všech DDL operacích provedených ve vašem schématu. Tímto triggerem naplňte všechny sloupce tabulky cv9_my_actions . • Vytvořte triggeryCV9_TRG_LOGON a CV9_TRG_LOGOFF, které budou zaznamenávat vaše přihlášení a odhlášení do schématu. Tyto informace zapisujte opět do tabulky cv9_my_actions (sloupce typ_objektu a nazev_objektu nechte prázdné). • Ukázka tabulky cv9_my_actions je na následující stránce. Databázové systémy 2 – cvičení 9

17. Příklady Databázové systémy 2 – cvičení 9

18. Příklady • Po vyřešení a odzkoušení funkce všech vytvořených databázových objektů v rámci tohoto zadání deaktivujte všechny vámi vytvořené triggery. Obsah tabulky cv9_my_actions ponechejte (předpokládá se, že je neprázdný a jsou v ní řádky ohledně přihlášení, odhlášení a DDL operacích). Databázové systémy 2 – cvičení 9

19. cvičení 9 – kontrola Všechny databázové objekty z tohoto cvičení vytvořte se svém schématu. Pro splnění cvičení je nutné, aby všechny objekty byly správně vytvořeny a funkční do půlnoci dne před následujícím cvičením. Databázové systémy 2 – cvičení 9