Distanční opora předmětu: Databázové systémy Tématický blok č. 8: Transact SQL Autor: RNDr. Jan Lánský, Ph.D. Obsah kapitoly 1 Motivace 2 Základy syntaxe 3 Procedury a Funkce 4 Kurzory 5 Výjimky Studijní cíle Schopnost vytvořit a použít proceduru a funkci v Transact SQL Schopnost vytvořit a použít.kurzor v Transact SQL Schopnost vyvolat a zpracovat výjimku v Transact SQL
Čas potřebný ke studiu 2 - 6 hodiny na prostudování výukových textů + zodpovězení otázek k rekapitulaci 1 - 4 hodiny na vypracování modelových úloh na PC 1 - 2 hodiny na praktické zopakování učiva na PC ( v jiný den) 30 min - 1 hodina na (znovu)zodpovězení otázek k rekapitulaci (v jiný den) Časy jsou hodně individuální a jsou závislé na míře znalostí z oblasti databázových systémů získaných během bakalářského studia. Úvod V tomto bloku probereme následující témata. Seznámíme se s důvody, proč používat procedurální rozšíření jazyka SQL. Seznámíme se základy syntaxe Transact SQL, procedurálního rozšíření SQL Serveru. Naučíme se deklarovat proměnné, nastavovat jím hodnotu, používat podmínku a cyklus. Naučíme se vytvářet procedury a funkce. Naučíme se vytvářet funkce, jejichž návratovou hodnotou je tabulka. Naučíme se vytvářet a používat kurzory pro čtení jednotlivých řádků z výsledků příkazu SELECT. Naučíme se vyvolávat a ošetřovat výjimky.
Výkladová část Vysvětlivky Červený text – Porušením nebo opomenutím takto označených pravidel vznikají těžko odladitelné chyby (zejména pro začínající programátory). Modrý text – Doporučení jak programovat v praxi. Často prevence závažných chyb. 1 Motivace Ve standardním SQL server obdrží a vyhodnotí pouze jednotlivý SQL příkaz (SELECT, INSERT, UPDATE, …), jehož výsledek pak vrátí zpátky klientovi. Na slajdu č. 2 vpravo vidíme příklad aplikace na zpracování dávky dat. Mohlo by se například jednat o přidání nové zásilky zboží do skladu. V cyklu ze serveru nejprve získáme nějakou informaci (například zda od daného druhu zboží máme nějaké na skladě), poté se nějak rozhodneme a vykonáme buďto kladnou nebo else větev podmínky. V kladné větvi (zboží ve skladě není) přidáme do tabulky nový záznam. V else větvi (zboží ve skladu je) upravíme pouze jeho množství. V našem příkladě v každém kroku cyklu provádíme dva SQL příkazy, které se posílají a vyhodnocují na serveru. Prvním příkazem je načtení dat o daném zboží, druhým příkazem je pak vložení nového záznamu nebo modifikace záznamu existujícího. Pokud má cyklus n iterací, na server se pošle 2n paketů a stejné množství se jich ze serveru pošle ke klientovi. Alternativním řešením je pomocí procedurálního rozšíření SQL napsat proceduru (slajd č. 3), která se bude vykonávat na serveru a její funkčnost bude shodná s funkčností aplikace ze slajdu č. 2. V tomto případě se na server odešle pouze jeden paket a server pošle zpět také pouze jeden paket. Při použití procedurálního rozšíření šetříme komunikační kanál. Místo velkého množství paketů se odesílá pouze jeden paket. Rovněž zvyšujeme rychlost provedení, protože odpadá zdržení vzniklé síťovou komunikací. Na klientovi snižujeme výpočetní náročnost, kterou naopak přenášíme na server, kde se nároky na systémové prostředky zvýší. Kód procedur může být navíc uložen na serveru, a může být ta využit všemi klientskými aplikacemi. Povolené manipulace s daty jsou pomocí procedurálního rozšíření zapouzdřena do procedur (a funkcí). Uživatelské aplikace nemusejí mít přímo práva k celým tabulkám, ale pouze k těmto procedurám. Procedurální rozšíření nebyla dlouhou dobu standardizována (až do normy SQL 99), proto existuje velké množství procedurálních rozšíření (každý databázový systém má obvykle vlastní). Funkčnost jednotlivých procedurálních rozšíření bývá přibližně stejná, liší se však v syntaxi. Skript napsaný pomocí procedurálního rozšíření pro jeden databázový systém je nepřenositelný na jiný databázový systém. Transact SQL (zkratka T-SQL) je procedurální rozšíření jazyka SQL v Microsoft SQL Serveru. Ostatní databázové systémy mívají také svá procedurální rozšíření, například V Oracle se nazývá PL/SQL.
Vysvětlivky k použitým slajdům. Slajdy byly původně vyrobeny pro srovnání Transact SQL a PL/SQL. Nás zajímá pouze text napsaný na modrém podkladu, který se týká Transact SQL. Text napsaný na žlutém podkladu se týká PL/SQL a v rámci naší výuky ho lze ignorovat. 2 Základy syntaxe Zdrojový kód v Transact SQL je tvořen posloupností příkazů, které nemusí (ale mohou) být ukončeny středníkem. Několik příkazů uzavřených mezi klíčová slova BEGIN a END se považují za jeden (složený) příkaz. Za jeden příkaz se považuje i celá sekce DECLARE, která slouží pro deklaraci proměnných. Příkazem Transact SQL je i libovolný SQL příkaz. Na slajdu č. 8 vpravo vidíme ukázku zdrojového kódu. V sekci DECLARE deklarujeme proměnnou @podíl typu reálné číslo. Názvy proměnných musejí začínat symbolem @. Za názvem proměnné musí následovat její datový typ. Pokud deklarujeme více proměnných, navzájem se jejich deklarace oddělují čárkou. Druhým příkazem je příkaz SELECT, ve kterém do proměnné @podíl dosadíme hodnotu aritmetického výrazu spočteného hodnot sloupců tabulky Zlomky, ze které byl vybrán právě jeden řádek. Třetím příkazem je podmínka IF, které otestuje hodnotu proměnné @podíl. V případě splnění podmínky se vykoná příkaz UPDATE, který modifikuje hodnotu sloupce Výsledek v tabulce Zlomky pro vybraný řádek. Hodnoty proměnným lze nastavovat dvěmi způsoby. Prvním způsobem je samostatný příkaz SET, který se skládá z klíčového slova SET následovaného názvem proměnné, operátorem = a novou hodnotou proměnné (například SET @x = 5). Druhou možností jak nastavit hodnotu proměnné je vyžití příkazu SELECT jak jsme viděli v příkladě na slajdu č. 8. Pro jednoznačnost přiřazení hodnoty proměnné by příkaz SELECT by měl vracet právě jednu řádku. (lze ošetřit například v klauzuli WHERE podmínkou na konkrétní hodnotu klíče). Pokud příkaz SELECT vrací více řádek, hodnota proměnné se (pravděpodobně podle mých pokusů) počítá z poslední vrácené řádky. Z řídících konstrukcí lze používat podmíněný příkaz IF a cyklus WHILE. Podmíněný příkaz (slajd č. 18 vpravo) se skládá z klíčového slova IF následovaného podmínkou a jedním příkazem nebo složeným příkazem, který se vykoná pokud je podmínka splněna. Podmíněný příkaz může nepovinně obsahovat ELSE větev jejíž příkaz nebo složený příkaz je vykonán pokud je podmínka nesplněna. Pokud je podmínka vyhodnocena jako NULL je považována za nesplněnou. Cyklus (slajd č. 19 vpravo) se skládá z klíčového slova WHILE následovaného podmínkou a jedním příkazem nebo složeným příkazem, který se bude vykonávat do té doby, dokud bude splněna podmínka. Cyklus lze také ukončit příkazem BREAK nebo přeskočit jednu jeho iteraci příkazem CONTINUE. Příkaz GO není Transact SQL příkazem, ale odděluje od sebe jednotlivé bloky Transact SQL příkazů. Po uvedení příkazu GO je blok příkazu odeslán na server a tam vyhodnocen. V dalším bloku příkazů nelze využít proměnné deklarované v předchozím bloku, protože vykonáním předchozího bloku tyto proměnné zanikly.
3 Procedury a Funkce Na slajdu č. 12 vidíme syntax tvorby procedury. Klíčová slova CREATE PROCEDURE jsou následována názvem procedury. Následuje deklarace parametrů procedury, která je obdobná jako deklarace proměnný v sekci DECLARE. Navíc oproti proměnným můžeme parametrům zadávat implicitní hodnoty. Tělo procedury je tvořeno klíčovým slovem AS a posloupností příkazů. Procedura se volá příkazem EXEC následovaného jménem procedury a jejími parametry navzájem oddělenými čárkou. Parametry volané procedury nejsou uzavřeny v závorkách. Parametry lze předat buďto podle jejich pořadí uvedeném při definici procedury nebo pomocí jména parametru a k němu přiřazené hodnoty pomocí operátoru =. Na slajdu č. 13 vidíme příklad vytvoření a zavolání procedury. Procedura má tři parametry, z nichž poslední má implicitní hodnotu NULL, nemusí být tedy při volání procedury zadán. Tělo procedury je tvořeno jedním UPDATE příkazem, který modifikuje hodnoty v tabulce Emp na základě hodnot získaných z parametrů procedury. Procedura je volána dvakrát. Nejprve jsou ji parametry předány pomocí jejich názvů a je vynechán parametr, který má implicitní hodnotu. V druhém volání procedury jsou parametry zadány v pořadí, v jakém byly deklarovány při definici procedury. Na slajdu č. 14 vidíme syntax tvorby funkce. Oproti proceduře je zde několik rozdílů. Parametry funkce se při její deklaraci uzavírají do kulatých závorek. Za parametry funkce následuje klíčové slovo RETURNS a datový typ návratové hodnoty funkce. Po klíčovém slovu AS následuje tělo funkce uzavřené mezi BEGIN a END. Návratová hodnota funkce se vrací pomocí příkazu RETURN. Funkce se volá v příkazu SELECT. Před názvem funkce fce musí být uveden prefix dbo (příklad dbo.fce), pro odlišení od vestavěných funkcí. Volaná funkce musí mít parametry uzavřené v kulatých závorkách. Kulaté závorky nesmějí chybět ani u bezparametrické funkce. Parametry lze opět předávat podle pořadí nebo podle jména. Na slajdu č. 15 vidíme příklad vytvoření a zavolání funkce. Funkce má čtyři parametry typu reálné číslo z toho tři mají implicitní hodnoty. Funkce vrací návratovou hodnotu typu reálné číslo. V těle funkce se počítá výraz z hodnot parametrů funkce, který je vrácen jako návratová hodnota funkce. V dolní části slajdu je funkce třikrát zavolána s různými parametry. V prvních dvou případech kombinujeme předávání parametrů podle pořadí (pro první parametr) a podle jména (pro zbylé parametry). Funkce je volána v příkazu SELECT, který neobsahuje žádné další klauzule. Pokud budete příklad zkoušet zkompilovat, je nutné doplnit při volání funkce před její název prefix dbo. Na slajdu č. 16 vidíme syntax funkce vracející tabulku. Tuto funkce lze použít jako pohled (dokonce i parametrický). Oproti normální funkci je změna pouze u návratového typu funkce, kdy za klíčovým slovem RETURNS následuje typ TABLE a poté u příkazu RETURN v těle funkce, za kterým bude následovat příkaz SELECT.
Na slajdu č. 17 vidíme druhou variantu syntaxe pro funkci vracející tabulku. V tomto případě po klíčovém slovu RETURNS uvedeme jméno nové proměnné (bude reprezentovat návratovou hodnotu funkce) následované TABLE a definicí tabulky (zejména sloupců). V těle funkce vkládáme data do námi vytvořené proměnné (typu tabulka) reprezentující výsledek funkce. Funkci ukončíme příkazem RETURN po kterém nenásleduje žádná hodnota. Výsledkem funkce je hodnota námi vytvořené proměnné (typu tabulka). 4 Kurzory Kurzory slouží ke zpracování víceřádkového výsledku SELECT příkazu. Kurzor si lze představit jako ukazatel na konkrétní řádek z výsledku dotazu, který můžeme v daný okamžik zpracovávat nezávisle na ostatních řádcích výsledku dotazu. Obvykle se kurzor používá k postupnému projití (od prvního do posledního) všech řádků výsledku dotazu. Při práci s kurzory je nutno dodržet následující posloupnost kroků (slajd č. 21). Nejprve kurzor deklarujeme pomocí klauzule DECLARE. Na rozdíl od jiných proměnných kurzory nejsou uvozeny znakem @. Deklarace se skládá ze jména kurzoru, datového typu CURSOR, klíčového slova FOR a příkazu SELECT, nad jehož výsledkem bude kurzor pracovat. Pomocí příkazu OPEN do kurzoru načteme data (provede se SELECT příkaz, pro který je kurzor vytvořen). Kurzor ukazuje na první řádek výsledku dotazu. Pomocí příkazu FETCH NEXT FROM vyzvedneme data uložená v prvním řádku výsledku dotazu a kurzor ukazuje na druhý řádek výsledku dotazu. V podmínce WHILE cyklu budeme testovat, zda poslední načtení dat z pozice výsledku dotazu, na kterou ukazoval kurzor, proběhlo v pořádku (@@FETCH_STATUS =0). V těle cyklu můžeme načtená data zpracovat. Posledním příkazem cyklu je posun kurzoru na další řádek a načtení dat, která se na tomto řádku nacházejí. Po přečtení řádku nacházejícím se za posledním řádkem výsledku dotazu nebude splněna podmínka cyklu a cyklus končí. Po skončení práce s kurzorem (skončení cyklu) je třeba kurzor zavřít příkazem CLOSE a kurzor zrušit příkazem DEALLOCATE. Na slajdu č. 22 je podrobněji popsána deklarace kurzoru a příkaz FETCH. Při deklaraci kurzoru lze za název kurzoru (před slovo CURSOR) přidat klíčové slovo SCROLL a poté se v kurzoru můžeme pohybovat nejen postupně (řádek po řádku) od začátku ke konci, ale provádět libovolný pohyb (skok na absolutně nebo relativně určenou pozici ve výsledku dotazu, na začátek, na konec). U příkazu FETCH jsme zatím poznali pouze variantu NEXT, která posune kurzor o jeden řádek dopředu a tento řádek načte. načte aktuální řádek a posune se o jeden řádek déle. Další varianty (PRIOR, ABSOLUTE, RELATIVE, LAST, FIRST) lze použít jen pro kurzory deklarované pomocí klíčového slova SCROLL. Za příkazem FETCH může být nepovinně přidáno klíčové slovo INTO následované seznamem proměnných, do kterých se mají načítat hodnoty sloupců získané z právě přečteného řádku. Počet proměnných a jejich typu musí odpovídat počtu sloupců výsledku dotazu a jejich typům. Například FETCH NEXT FROM C INTO @prvni, @druhy; načte do proměnné @prvni hodnotu prvního sloupce výsledku dotazu, do proměnné @druhy hodnotu druhého sloupce výsledku dotazu.
5 Výjimky Výjimky slouží k ošetření výjimečných stavů při vykonávání SQL příkazů. K výjimkách dochází velmi často a je nutné je korektně obsloužit Výjimky mohou být generované serverem (například dělení nulou), nebo definovány a vyvolány programátorem. Pomocí mechanizmu výjimek nelze odchytávat kompilační chyby, varování a některé velmi závažné chyby. Ošetření výjimky je znázorněno na slajdu č. 26. Část zdrojového kódu, která může vyvolat výjimku by měla být uzavřena v TRY bloku (BEGIN TRY, END TRY). Poté následuje CATCH blok (BEGIN CATCH, END CATCH), ve kterém se zjišťuje jaká výjimka nastala a podle typu výjimky proběhne její ošetření. Pro zjištění typu chyby je možno použít funkci ERROR_NUMBER, pro zjištění její závažnosti funkci ERROR_SEVERITY. Funkce ERROR_LINE určí číslo řádky, na které došlo k chybě. Funkce ERROR_MESSAGE vrátí text chybového hlášení. Na slajdu č. 27 vidíme příklad na vyvolání a ošetření výjimky. V TRY bloku vyvoláme výjimku dělení nulou, v CATCH bloku příkazem SELECT vypíšeme všechny dostupné informace i této výjimce. Uživatelsky definované výjimky se vyvolávají funkcí RAISERROR (slajd č. 28). Této funkci se jako parametr předává číslo chyby (větší než 50 000) nebo text chybového hlášení. Dalšími argumenty jsou závažnost chyby (vhodné volit mezi 11 až 18) a stav chyby (upřesnění jak chyba vznikla). Klíčové pojmy proměnné, DECLARE, SET IF, ELSE, WHILE, BREAK, CONTINUE procedura, parametry, EXEC funkce, RETURNS, RETURN funkce vracející tabulku kurzor, FETCH NEXT FROM, FETCH_STATUS, OPEN, CLOSE, DEALLOCATE výjimky, TRY, CATCH, RAISERROR Otázky k rekapitulaci Upozornění: odpovědi na některé zde uvedené otázky nelze najít ve studijním textu tohoto tématického bloku. Lze je získat vlastním experimentováním se zdrojovými kódy nebo studiem doporučené literatury. Jaké jsou výhody a nevýhody používání procedurálního rozšíření jazyka SQL? Co je Transact SQL? Jak se deklarují proměnné a jak se proměnným přiřazují hodnoty v Transact SQL? Jak se vytvářejí podmínky a cykly v Transact SQL?
Jaké náležitosti má definice procedury v Transact SQL? Jaké náležitosti má definice funkce v Transact SQL? Jaký je rozdíl mezi voláním procedury a funkce v Transact SQL? Jakými dvěma způsoby lze vytvořit funkci vracející tabulku v Transact SQL? Co je kurzor a k čemu slouží? Popište průběh práce s kurzorem v v Transact SQL? Jaké jsou dva typy kurzorů, jak se navzájem liší? Co jsou výjimky a k čemu slouží? Jaké jsou dva typy výjimek a kdo k nim dochází? Jak se ošetřují výjimky v Transact SQL? Své odpovědi zdůvodněte. Můžete přidat i syntaktické zápisy tam, kde je to vhodné. Doporučené příklady k naprogramování Následující dotazy utvořte nad databázi Northwind s použitím Transact SQL. 1. Vytvořte proceduru, která pro zadané jméno a příjmení zaměstnance vypíše seznam zákazníků, kterým prodal zboží. 2. Vytvořte funkci, která pro zadané jméno zákazníka vrátí počet různých druhů zboží, které nakoupil? 3. Vytvořte funkci, která vrátí tabulku obsahující seznam měst a počtu zaměstnanců, kteří v daném městě bydlí. 4. Vypište jméno a příjmení třetího nejstaršího zaměstnance. 5. Zkuste vyvolat uživatelsky definovanou výjimku a ošetřit ji. Studijní literatura [1] Kopecký: Výběr ze slajdů k 3. přednášce z předmětu Databázové aplikace (DBI026) vyučovaného na MFF UK. (v tomto tématickém bloku označované jako „slajdy“) https://is.vsfs.cz/auth/el/6410/leto2010/EQ_N_DS/um/DS8.ppt [2] Sack: Velká kniha T-SQL & SQL Server 2005, Zoner Press, 2007
