Databázové systémy 2

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

2. Obsah cvičení • Práce s DBMS_OUTPUT. • Kurzory. • Procedury, funkce a balíčky. • Výjimky. Databázové systémy 2 – cvičení 7

3. Cvičení 7 – Příklad I • Vytvořte funkcicv7_pomer_plat(p_mzda NUMBER, p_oddeleni_id NUMBER)RETURN VARCHAR2; • Parametr p_oddeleni_id nastavte jako volitelný s defaultní hodnotou NULL. • Funkce vrátí řetězec (VARCHAR2), který procentuálně vyjádří výši mzdy zadané parametrem p_mzdak průměrnému platu: • A) Pokud nebude zadáno p_oddeleni_id, poměr se vypočítá z průměrného platu v celé organizaci. • B) Pokud bude p_oddeleni_id zadáno, poměr se počítá proti průměru platů na daném oddělení. • Využijte schématu A_HR, tabulka ZAMESTNANCI. • Příklad výsledku při použití funkce v dotazu: Databázové systémy 2 – cvičení 7

4. Cvičení 7 – Příklad II • Vytvořte funkci cv7_vekova_skupina(p_datum DATE) RETURN VARCHAR2; • Funkce ze zadaného data narození určí, do které věkové skupiny daný člověk patří. Výstup se bude řídit následujícími pravidly: • 0 – 18 let „Dítě“ • 18 – 65 let „Dospělý“ • 65 + „Důchodce“ • V případě jiné hodnoty se vypíše „Neznámá“ • Funkci můžete otestovat ve schématu A_CLOVEK. Databázové systémy 2 – cvičení 7

5. Cvičení 7 – Příklad III • Vytvořte proceduru cv7_delete_script(p_schemaVARCHAR2). • Procedura vypíše na standardní výstup skript, který bude možné použít pro smazaní všech tabulek ve schématu. Schéma bude reprezentováno parametrem p_schema. Pro nalezení tabulek použijte pohled ALL_TABLES. • Parametr p_schema nesmí být citlivý na velikost písma (case insensitive). • Ukázka výstupu: Databázové systémy 2 – cvičení 7

6. Cvičení 7 – Příklad IV • Vytvořte balíček pck_cv7: • Balíček bude obsahovat následující funkce a procedury: • Funkce student_prumer(p_id_studenta NUMBER) RETURN NUMBER; • Funkce vypočte k zadanému studentovi jeho studijní průměr, založený na jeho známkách. Vypočtený průměr zaokrouhlí na 2 desetinná místa. • Pro tento příklad použijte schéma A_SKOLA. Databázové systémy 2 – cvičení 7

7. Cvičení 7 – Příklad IV • Pokračování balíčku pck_cv7: • Vytvořte proceduru ucitel_seznam(p_id_ucitele NUMBER). • Procedura na základě zadaného parametru vypíše předměty vyučované daným vyučujícím spolu se seznamem studentů, jenž tento předmět navštěvují. • Výpis bude proveden na standardní výstup. • Spolu se studentem bude zobrazena i udělená známka. V případě že student dosud nebyl klasifikován, zobrazí se místo známky znak „X“. • Pokud bude zadáno id, které nepatří žádnému učiteli, procedura na výstup vypíše: „Nenalezen žádný vyučující.“. Ošetřete pomocí výjimky. • Na následujícím snímku naleznete vzorový formát výstupu. Databázové systémy 2 – cvičení 7

8. Cvičení 7 – Příklad IV Databázové systémy 2 – cvičení 7

9. Cvičení 7 – Příklad IV • V balíčkupck_cv7 dále vytvořte: • Vytvořte funkciseznam_predmetu(p_id_studenta NUMBER) RETURN VARCHAR2; • Funkce vrátí pro zadaného studenta (reprezentovaného p_id_studenta) zřetězený seznam jeho předmětů spolu s informací o vyučujícím předmětu. • Formát: předmět1(Jmeno a Prijmeni vyučujícího); předmět2(vyučující);…. • Předměty ve výpise budou seřazeny vzestupně podle příjmení vyučujícího. • V případě, že student nemá zapsaný žádný předmět, zobrazí se text: • „Student nemá zapsané žádné předměty. “ • Pro tento příklad použijte schéma A_SKOLA. Databázové systémy 2 – cvičení 7

10. Cvičení 7 – Příklad IV • S pomocí funkce pck_cv7.seznam_predmetuvytvořte pohledcv7_student_predmety(jmenoVARCHAR2, predmetyVARCHAR2),kde ve sloupci jmeno bude jméno a příjmení studenta a ve sloupci predmety bude výsledek funkce cv7_seznam_predmetu. • Příklad výstupu: Databázové systémy 2 – cvičení 7

11. Cvičení 7 – 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í 7