Architektury a techniky DS

Architektury a techniky DS Cvičení č. 1 a 2 RNDr. David Žák, Ph.D. Fakulta elektrotechniky a informatikydavid.zak@upce.cz

Cíl cvičení • Konfigurace připojení k databázovému serveru • Přístup k databázovému serveru přes VPN • Práce s SQL Developerem • Seznámení s tabulkou A_POPIS ve vzorových schématech • Rekurzivní příkazy

Přístupová data na DS Oracle jméno serveru: SQL101.upceucebny.cz servicename / connectstring: oracle10 username: vaše_doménové_jméno (např. ST12345) password: při prvním přihlášení vaše_doménové_jméno velkými písmeny port: 1521 změna hesla ALTER USER st12345IDENTIFIED BY nove_heslo

Připojení mimo síť Upce: • Přihlaste se na http://vpnc.upce.cz, použijte svůj účet ve tvaru stxxxxx@upce.cz, kde stxxxxx je vaše uživatelské jméno k systémům na síti Upce (knihovna, objednávky obědů, …). • Vyčkejte, příp. potvrďte nainstalování ActiveX (nebo Javového) appletu. • Aktivujte VPN připojení do sítě Upce. • Poté můžete využít některou z možností přístupu, např.: • SSH přístup na server a konzolu SQLplus • iSQLplus z WWW prohlížeče • SQL Developer

iSQLplus Další možností je využití WWW prohlížeče pro práci s Oracle http://SQL101.upceucebny.cz:5560/isqlplus Možnosti: • nastavit vyšší šířku textového pole v Preferences • vložení skriptů s více SQL příkazy • přehledné zobrazení výsledků (s možností volby počtu řádků na stránku)

SQL Developer Další možností je využití freeware aplikace SQL Developer • dobrý nástroj, má však určité nedostatky • je třeba být opatrný při práci v PL/SQL • pozor na aktualizace (neprovádí se automaticky) • přehledná struktura objektů • práce i přes menu (netřeba zadávat textové příkazy pro některé operace) http://www.oracle.com/technology/software/products/sql/index.html

SQL Developer • Nástroj pro vizuální práci s databází • Obdoba je například • Toad for Oracle • Toad for MySQL 2.0 Freeware • vytváření databázových objektů (průvodci) • vkládání a editace dat • kompilace funkcí a procedur • zadávání SQL příkazů • reporty, grafické zobrazení výsledku

SQL Developer - přehled

SQL Developer – SQL worksheet

SQL Developer – editace, kompilace

Častý problém • z hlediska transakčního zpracování dat je vhodné provedené změny v databázi potvrdit příkazem COMMIT a to i v prostředí SQL Developeru (buď stiskem tlačítka COMMIT nebo zapsáním příkazu COMMIT) • jinak jde totiž o rozpracovanou transakci, která • zabírá systémové prostředky • je možné ji vrátit zpět příkazem rollback • pokud nebude ukončena, může dojít i k automatickému návratu zpět (tedy ztrátě změn provedených od minulého příkazu commit) například při nekorektním ukončení připojení k databázovému serveru

Vzorová schémata • Na databázovém serveru je několik vzorových schémat, s nimiž budeme během semestru pracovat, tato schémata mají název „A_problematika“, např. A_CLOVEK • V těchto schématech je tabulka A_POPIS, která obsahuje • ve sloupci TEXT popis problematiky, která je v daném schématu řešena • ve sloupci ERD diagram ve formátu jpg souboru, který je možné zobrazit, případně i uložit

Cvičení 1 – úvodní část Vyzkoušejte připojení k serveru Oracle prostřednictvím • SSH přístupu na server a konzoly SQLplus • iSQLplus z WWW prohlížeče • SQL Developeru Pro přihlášení používejte váš osobní účet.

Cvičení 1 Úkoly: Máme dánu ve schématu A_CLOVEK tabulku: Lide = (Id, jmeno, prijmeni, narozen, pohlavi, Id_otce, Id_matky) • zjistěte, kolik dětí má otce s ID=2 a matku s ID=3 • zjistěte jména a příjmení všech vnoučat osoby Božena Malá • zjistěte počet vnoučat osoby Božena Malá • zjistěte průměrný počet dětí, které mají "plodné" páry (za pár uvažujte stejného otce a matku) • vypište počty dětí narozených v jednotlivých letech 2000 až 2005, uveďte i nulové hodnoty, pro řešení použijte PL/SQL – vytvořte proceduru narozeno_deti(od_roku, do_roku)

Hierarchické dotazy v Oracle • 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í (pseudo-sloupec LEVEL nabývá hodnoty 1) • 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

Příklad hierarchických dotazů SELECT ZAMESTNANEC_ID, JMENO, PRIJMENI, MANAZER_ID, LEVEL FROM A_HR.ZAMESTNANCI CONNECT BY MANAZER_ID = PRIOR ZAMESTNANEC_ID START WITH MANAZER_ID is null; • Sloupce, přes které je hierarchie provázána určuje klauzule CONNECT BY. Klauzule PRIOR říká, že se pole MANAZER_ID má porovnávat s polem ZAMESTNANEC_ID nadřízeného (tedy "předchozího") záznamu v hierarchii. • Klauzule START WITH definuje podmínku určující záznamy v první úrovni hierarchie. V tomto případě je nejvyšší manažer definován tak, že nemá vyplněné pole MANAZER_ID . Viz také např. http://www.dbsvet.cz/view.php?cisloclanku=2006060701

Příklad hierarchických dotazů Pseudosloupec LEVEL určuje úroveň daného záznamu v hierarchii. Můžete jej mimo jiné jednoduše použít pro formátování výstupu: SELECT lpad(' ',level*3)||PRIJMENI||' '||JMENO name, ZAMESTNANEC_ID, MANAZER_ID, level FROM A_HR.ZAMESTNANCI CONNECT BY MANAZER_ID = PRIOR ZAMESTNANEC_ID START WITH MANAZER_ID is null ORDER SIBLINGS BY PRIJMENI; Uvedený příklad ukazuje způsob, jakým lze ovlivnit řazení záznamů na jednotlivých úrovních – jde o klauzuli ORDER SIBLINGS BY. Běžnou klauzuli ORDER BY provádí Oracle až úplně nakonec – tedy přes všechny záznamy, čímž by způsobila, že by již záznamy vizuálně netvořily hierarchii.

Příklad hierarchických dotazů • SELECT lpad(' ',level*3)||PRIJMENI||' '||JMENO name, • ZAMESTNANEC_ID, MANAZER_ID, level • FROM A_HR.ZAMESTNANCI • CONNECT BY MANAZER_ID = PRIOR ZAMESTNANEC_ID • START WITH MANAZER_ID is null • ORDER SIBLINGS BY PRIJMENI;

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

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

Cvičení 2 Úkoly: Máme dánu ve schématu A_CLOVEK tabulku: Lide = (Id, jmeno, prijmeni, narozen, pohlaví, Id_otce, Id_matky) Úkoly řešte • pomocí příkazu SELECT s klauzulí START WITH pro rekurzivní spojení • Pomocí PL/SQL – viz funkce níže pro příklad 2 bez využití rekurzivního SELECTu • zjistěte jména a příjmení všech dětí, vnoučat a pravnoučat osoby Božena Malá, u každé osoby uveďte vztah (dítě, vnouče, pravnouče) PL/SQL: procedura vypis_potomky(jmeno, prijmeni) • zjistěte, kolik dětí, vnoučat a pravnoučat má Božena Malá (výsledkem tohoto dotazu je více čísel podle vztahu)PL/SQL: procedura vypis_pocty_potomku(jmeno, prijmeni) Pro řešení vytvořte funkci: pocet_nasledniku(id_osoby IN INT, uroven IN INT)

Architektury a techniky DS