Relační databáze

1. Relační databáze Jakub Lokoč

2. Rekapitulace ZS Proč a jak vzniká relační datový model? Kvalita relačního modelu? Co relační model umožňuje? SQL – jazyk pro definici dat, modifikaci dat a hlavně pro jejich dotazování Transakce – konkurenční prostředí Co mají všechna témata společného? Relační databáze, T-SQL, RNDr. Jakub Lokoč, Ph.D.

3. Plán na letní semestr • Seznámení s MSSQL serverem • Instalace express edition 2008 (DÚ) • Microsoft SQL Server Management Studio • T-SQL • Uložené procedury • Triggery • Datový sklad pomocí MSSQL serveru • Operátor CUBE Relační databáze, T-SQL, RNDr. Jakub Lokoč, Ph.D.

4. Cvičení • Používaný datový model • DUM(Cislo, Ulice, Barva) • OKNO(Id, Cislo, Ulice, JeStresni) Cislo, Ulice je CK do DŮM • KOCKA(Id, Jmeno, Barva) • PREDENI(OknoId, KockaId) OknoId a KockaId jsou Ck do tabulek Okno a Kocka • Vytvořte tabulky na MSSQL serveru (včetně primárních a cizích klíčů) • Nastavte, že barva domu nebo kočky může být jen bílá, hnědá nebo černá • Naplňte tabulky daty • Napište a otestujte SQL dotazy • Střešní okna na ulici Estonská nebo Vltavská • Jména koček, které předly na ulici Estonská • Kolik koček předlo v Estonské ulici? • Na které ulici nepředla žádná kočka? Relační databáze, T-SQL, RNDr. Jakub Lokoč, Ph.D.

5. T-SQL • Rozšíření jazyka SQL (Microsoft a Sybase) • Lokální proměnné • Řízení toku programu (control of flow) • Knihovna systémových procedur a funkcí • Uživatelské procedury a funkce • Rozšíření operací UPDATE a DELETE • BULK INSERT • … Relační databáze, T-SQL, RNDr. Jakub Lokoč, Ph.D.

6. Běžné datové typy na MSSQL serveru Relační databáze, T-SQL, RNDr. Jakub Lokoč, Ph.D.

7. T-SQL systémové funkce • Matematické operátory • +, -, /, *, %, Abs, Round, Floor, Ceiling, Rand • Agregační funkce • Sum, Min, Max, Avg, Count • Funkce pro práci s řetězci • +, Left, Right, Lower, Len, Replace, Substring • Funkce pro práci s datem a s NULL • Year, Month, Day, GetDate, IsNull • Funkce pro konverzi mezi datovými typy • Cast(vyraz as typ), Convert(typ, vyraz, styl) Relační databáze, T-SQL, RNDr. Jakub Lokoč, Ph.D.

8. T-SQL deklarace proměnných Klíčové slovo DECLARE Proměnná musí začínat symbolem @ Příklady na přiřazení hodnoty proměnné DECLARE@PocetDomuINT, @Datum DATETIME; SET @PocetDomu = 5;SET @Datum =‘3-20-2012’; SET@Datum = DATEADD(DAY, 1, @Datum); -- Náhodná inicializace proměnné na hodnotu 2, 3 nebo 4 SET @PocetDomu = 2 + Rand() * 3; SET @PocetDomu = (SELECTCount(*) FROM Dum); SELECT @PocetDomu = Count(*) FROM Dum; Relační databáze, T-SQL, RNDr. Jakub Lokoč, Ph.D.

9. T-SQL platnost proměnné Proměnné jsou lokální, tj. platí pouze v daném vykonávaném bloku Pozor na příkaz GO ukončující dávku Příklad DECLARE@PocetDomuINT; GO -- Dojde k chybě, nezná proměnnou @PocetDomu SET @PocetDomu = 5; SELECTUlice, Count(Cislo) FROM Dum GROUPBYUliceHAVINGCount(*) > @PocetDomu Relační databáze, T-SQL, RNDr. Jakub Lokoč, Ph.D.

10. T-SQL tabulková proměnná Slouží k uchování mezivýsledku (optimalizace dotazu) Záleží na velikosti mezivýsledku!!! Příklad DECLARE@T TABLE (CisloINT not null, AdresaVARCHAR(50) not null); -- Naplníme jako klasickou tabulku INSERTINTO@T SELECTCislo, AdresaFROMDůmWHERE … SELECT * FROM@T, OknoWHERE … Relační databáze, T-SQL, RNDr. Jakub Lokoč, Ph.D.

11. T-SQL řízení toku programu BEGIN, END – začátek a konec bloku IF, ELSE – větvení programu WHILE – opakované provádění bloku kódu BREAK, CONTINUE – řízení cyklu RETURN – opuštění vykonávané procedury WAITFOR – pozastavení programu GOTO – skok na návěští (moc nepoužívat) Relační databáze, T-SQL, RNDr. Jakub Lokoč, Ph.D.

12. T-SQL větvení programu DECLARE@PocetOkenINT; SELECT @PocetOken = Count(*) FROMOknoWHEREUlice = ‘Estonská’ANDCislo = 1; -- Pro objednávku 50 a více oken je 500 Kč sleva IF @PocetOken > 49 PRINT ‘Cena za jedno okno je 10.000 Kč.’ ELSE PRINT‘Cena za jedno okno je 10.500 Kč.’ -- Pokud chceme vykonat více operací v rámci jedné větve, tak -- musíme příkazy uzavřít do bloku BEGIN a END -- Příkaz PRINT vytiskne řetězec do okna zpráv (Messages) Relační databáze, T-SQL, RNDr. Jakub Lokoč, Ph.D.

13. T-SQL cyklus DECLARE@PocetINT, @UliceVARCHAR(25); SET @Pocet = 0; WHILE @Pocet < 10 BEGIN SET@Pocet = @Pocet + 1; IFRand() < 0.25 SET @Ulice = ‘Estonská’; ELSESET @Ulice = ‘Vltavská’; INSERTINTO DumVALUES (@Pocet, @Ulice, ‘Modrá’); END -- Upravte skript tak, aby se generovaly různé barvy domů (použijte CASE) -- Upravte skript tak, aby se ke každému novému domu vložilo 5 oken. -- Třetí a páté okno bude vždy střešní. Relační databáze, T-SQL, RNDr. Jakub Lokoč, Ph.D.

14. T-SQL řízení cyklu DELETEFROM Dum; DECLARE@PocetINT; SET @Pocet = 0; WHILE @Pocet < 10 BEGIN SET@Pocet = @Pocet + 1; IF @Pocet = 2 CONTINUE; INSERTINTO DumVALUES (@Pocet, ‘Estonská’, ‘Modrá’); IF @Pocet = 7 BREAK; END -- Popište, jak bude vypadat tabulka Dumpoprovedení skriptu. Relační databáze, T-SQL, RNDr. Jakub Lokoč, Ph.D.

15. T-SQL kurzory FETCH NEXT do proměnných @x a @y FETCH NEXT … FETCH NEXT … FETCH NEXT … FETCH NEXT @@FETCH_STATUS != 0 • Slouží pro sekvenční zpracování výsledku SQL dotazu • Často vyžadují hodně paměti! • Neopomíjet alternativy založené na sadách řádků! • Pravidla práce s kurzorem • Deklarace kurzoru pomocí SELECT příkazu • Před použitím je nutné kurzor otevřít (po použití zase zavřít) • Pohyb kurzoru si řídí uživatel sám (FETCH NEXT, FIRST, LAST, …) • Uživatel má k dispozici data z aktuální pozice kurzoru Relační databáze, T-SQL, RNDr. Jakub Lokoč, Ph.D.

16. T-SQL práce s kurzory DECLARE @CisloINT, @UliceVARCHAR(50); DECLARE dum_cursor CURSORFOR SELECT Cislo, Ulice FROM Dum; OPENdum_cursor; FETCHNEXTFROMdum_cursorINTO @Cislo, @Ulice WHILE @@FETCH_STATUS = 0 BEGIN PRINTCast(@CisloASCHAR(4)) + ' ' + @Ulice FETCHNEXTFROMdum_cursorINTO @Cislo, @Ulice END CLOSEdum_cursor; DEALLOCATEdum_cursor; Relační databáze, T-SQL, RNDr. Jakub Lokoč, Ph.D.

17. T-SQL kurzory • Příklad 1 • S využitím kurzoru přidejte každému domu v Estonské jedno střešní okno • Jiné řešení (sice jednodušší, ale zato efektivnější…) INSERTINTO Okno (Cislo, Ulice, JeStresni) SELECTCislo, Ulice, 1 FROM Dum WHERE Ulice = ‘Estonská’ • Příklad 2 • Spočítejte kumulativní sumu počtu střešních oken pro posloupnost domů (1, 1+1, 1+1+1, …). Srovnejte efektivitu kurzoru a řešení pomocí dotazu s Group By Relační databáze, T-SQL, RNDr. Jakub Lokoč, Ph.D.

18. T-SQL transakce • Zajištění ACID vlastností složitějších operací • MS SQL server podporuje několik módů transakcí • Autocommit mode – běžné nastavení, každá operace je transakcí která potvrdí nebo abortuje v případě chyby • Implicitní transakce – neuvádí se start transakce, potvrzení odstartuje novou transakci • Explicitní transakce – explicitně definované startem a koncem transakce • Autocommit mód je nevhodný pro rozsáhlé operace Relační databáze, T-SQL, RNDr. Jakub Lokoč, Ph.D.

19. T-SQL transakce Autocommit mode - doba vložení kolem 40s DECLARE @PocetINT SET @Pocet = 100000 WHILE @Pocet > 0 BEGIN SET @Pocet = @Pocet - 1 INSERTINTO Dum VALUES (@Pocet, 'Estonska', 'Bila') END Relační databáze, T-SQL, RNDr. Jakub Lokoč, Ph.D.

20. T-SQL transakce Explicitně vyjádřená transakce - doba vložení kolem 3s DECLARE @PocetINT SET @Pocet = 100000 BEGINTRAN WHILE @Pocet > 0 BEGIN SET @Pocet = @Pocet - 1 INSERTINTO Dum VALUES (@Pocet, 'Estonska', 'Bila') END COMMITTRAN Relační databáze, T-SQL, RNDr. Jakub Lokoč, Ph.D.

21. T-SQL explicitní transakce BEGIN TRAN – zahájení transakce COMMIT TRAN – potvrzení transakce ROLLBACK TRAN – zrušení efektu transakce DECLARE @PocetDomuINT, @Limit INT SET @Limit = AktualniPocetPovolenychDomu() BEGINTRAN INSERTINTO Dum VALUES (1, 'Estonska', 'Bila') SET @PocetDomu = (SELECTCount(*) FROMDum) IF @PocetDomu > @Limit ROLLBACKTRAN COMMITTRAN Relační databáze, T-SQL, RNDr. Jakub Lokoč, Ph.D.

22. T-SQL explicitní transakce Implicitní chování není ACID – chyba jedné z operací nevyvolá abort celé transakce BEGINTRAN INSERTINTO Dum VALUES (1, 'Estonska', 'Bila') INSERTINTO Dum VALUES (1, 'Estonska', 'Bila‘) COMMITTRAN Druhý insert neproběhne kvůli porušení omezení primárního klíče, nicméně, první insert proběhne a je potvrzen Relační databáze, T-SQL, RNDr. Jakub Lokoč, Ph.D.

23. T-SQL explicitní transakce Lze vyřešit pomocí přepínače… SETXACT_ABORTON BEGINTRAN INSERTINTO Dum VALUES (1, 'Estonska', 'Bila') INSERTINTO Dum VALUES (1, 'Estonska', 'Bila') COMMITTRAN Relační databáze, T-SQL, RNDr. Jakub Lokoč, Ph.D.

24. T-SQL explicitní transakce Nebo ručněpomocí zachycení a ošetření chyby BEGINTRAN BEGINTRY INSERTINTO Dum VALUES (1, 'Estonska', 'Bila') INSERTINTO Dum VALUES (1, 'Estonska', 'Bila') COMMITTRAN ENDTRY BEGINCATCH ROLLBACKTRAN ENDCATCH Relační databáze, T-SQL, RNDr. Jakub Lokoč, Ph.D.

25. T-SQL savepointy Mechanismus, jak vrátit zpět pouze část transakce BEGINTRAN INSERTINTO Dum VALUES (1, 'Estonska', 'Bila'); SAVETRANSP1 INSERTINTO Dum VALUES (2, 'Estonska', 'Bila'); ROLLBACKTRANSP1 INSERTINTO Dum VALUES (3, 'Estonska', 'Bila'); COMMITTRAN Do tabulky Dům se vloží pouze Domy 1 a 3 Relační databáze, T-SQL, RNDr. Jakub Lokoč, Ph.D.

26. T-SQL uložené procedury Hlavní program Procedura Parametry • Zajišťují znovupoužitelnost kódu • Komunikují s volajícím pomocí parametrů • INPUT a OUTPUT parametry, příkaz RETURN • Ve svém těle mohou volat jiné procedury • Procedury se volají/spouštějí příkazem EXEC Relační databáze, T-SQL, RNDr. Jakub Lokoč, Ph.D.

27. T-SQL uložené procedury Příklad uživatelské procedury, kterávrátí počet domů na zadané ulici příkazem RETURN CREATEPROCEDURE PocetDomu_1 @UliceVARCHAR(50) AS BEGIN DECLARE@PocetINT; SELECT @Pocet = Count(*) FROM Dum WHEREUlice = @Ulice; RETURN @Pocet; END GO DECLARE @PocetINT; EXEC @Pocet = PocetDomu_1‘Estonská’; Relační databáze, T-SQL, RNDr. Jakub Lokoč, Ph.D.

28. T-SQL uložené procedury Příklad uživatelské procedury, kterávrátí počet domů na zadané ulici OUTPUT parametrem CREATEPROCEDURE PocetDomu_2 @UliceVARCHAR(50), @PocetINTOUTPUT AS BEGIN SELECT @Pocet = Count(*) FROM Dum WHEREUlice = @Ulice; END GO DECLARE @PocetINT; EXEC PocetDomu_2 ‘Estonská’, @PocetOUTPUT; Relační databáze, T-SQL, RNDr. Jakub Lokoč, Ph.D.

29. T-SQL uložené procedury Zapouzdřují složité operace nad daty Minimalizují síťovou komunikaci Rozmanité možnosti nastavení oprávnění pro přístup k datům Můžou (ale nemusí) vracet hodnotu Jsou primárně navrženy tak, aby vracely výsledky aplikaci (v datasetu) Relační databáze, T-SQL, RNDr. Jakub Lokoč, Ph.D.

30. T-SQL uložené funkce • Podobné jako procedury až na to, že… • Vždy vrací nějakou hodnotu • Můžou vracet i tabulku (volání v SELECT dotazu) • Na rozdíl od pohledu můžou přijímat parametry • Nelze v nich volat některé příkazy CREATEFUNCTION Domy(@UliceVARCHAR(50)) RETURNSTABLE AS RETURN (SELECT * FROM Dum WHEREUlice = @Ulice); GO SELECTCount(*) FROMDomy(‘Estonská’); Relační databáze, T-SQL, RNDr. Jakub Lokoč, Ph.D.

31. T-SQL příkaz EXEC • EXEC(‘SELECT * FROM Dum’) • Spustí SQL dotaz zadaný řetězcem • EXEC nazev_procedury parametry • Spustí proceduru s parametry • EXEC @return = nazev_procedury • Spustí proceduru a převezme výsledek Relační databáze, T-SQL, RNDr. Jakub Lokoč, Ph.D.

32. T-SQL triggery • Tzv. spouštěče událostí u DDL nebo DML akcí • U DML operací lze triggery spouštět v módech • AFTER – po operaci (pouze u tabulek) • INSTEAD OF – namísto operace (u pohledů a tabulek) • Většinou se na událost volá nějaká stored procedura • U DML triggerů je klíčové mít k dispozici vložená, změněná nebo opravená data Relační databáze, T-SQL, RNDr. Jakub Lokoč, Ph.D.

33. T-SQL triggery CREATETRIGGER UpravaTabulkyDum ON Dum AFTERINSERTAS INSERTINTO DumLog SELECT I.Id, GetDate() FROM Inserted I GO -- Vypnutí triggeru DISABLE TRIGGER UpravaTabulkyDumON Dum GO -- Zapnutí triggeru ENABLE TRIGGER UpravaTabulkyDumON Dům -- Jak by se zalogovaly všechny operace s tabulkou? Relační databáze, T-SQL, RNDr. Jakub Lokoč, Ph.D.

34. T-SQL triggery • Jak zjistit Id nových nebo změněných objektů? • SELECT I.Id FROM Inserted I • Jak zjistit Id smazaných objektů? • SELECT D.Id FROM Deleted D • Jak zjistit, jestli byla změněna hodnota ve sloupci Ulice? • IFUPDATE(Ulice) … • Jak stornovat aktualizační operaci? • Příkazem ROLLBACK Relační databáze, T-SQL, RNDr. Jakub Lokoč, Ph.D.