SQL - procedúry

Uložené procedúry sú špeciálne používateľom definované funkcie, ktoré sú uložené priamo na databázovom serveri, v konkrétnej databáze. MySQL podporuje uložené procedúry od verzie 5.

Uložené procedúry

Uložená procedúra je sada príkazov SQL, ktoré sú:

• Uložené na serveri
• Skompilované pre rýchlejšie použitie

Načo sú vlastne uložené procedúry v praxi dobré? Jedným dôvodom je fakt, že niektoré databázové operácie sa neustále opakujú. Ak je takáto databázová operácia vykonaná ako dávka na serveri, nemusia sa jednotlivé príkazy (ktorých môžu byť desiatky) vypisovať zakaždým, keď ich chceme vykonať.

Ďalším dobrým dôvodom pre zavedenie uložených procedúr je istá unifikácia požiadaviek od jednotlivých klientov. Ak chce niekoľko databázových klientov vykonávať rovnakú (alebo veľa podobnú) prácu, bude pre nich príjemné zistenie, že niečo také už server "vie" vďaka uloženej procedúre, ktorú má k dispozícii.

Uložené procedúry môžu tiež podstatným spôsobom uľahčovať spravovanie databázových aplikácií. Majme ako príklad server, ktorý ukladá a spracováva dáta. Ak to celé pobeží pomocou uložených procedúr, môžu so rovnakým spôsobom voči databáze správať klienti pochádzajúce z najrôznejších prostredí - desktopová aplikácia napísaná v Jave, webový klient v PHP alebo napríklad vzdialený server, ktorý s tým "naším" komunikuje. Ak je potreba uloženú procedúru upraviť (alebo opraviť), môže sa to urobiť na jednom mieste a táto zmena je ihneď použiteľný pre všetky aplikácie, ktoré s databázou komunikujú.

Ďalším významným rysom uložených procedúr je to, že prispievajú k bezpečnosti serveru (respektíve môžu to robiť, ak sú dobre napísané). Keď hovoríme o bezpečnosti v súvislosti s uloženými procedúrami, môžeme mať na mysli dve veci:

• Procedúry môžu byť v databázových systémoch nastavené tak, že je smie spúšťať iba niekto s určitými oprávneniami
• Procedúry môžu samy kontrolovať počet, typ, veľkosť a niektoré iné charakteristiky parametrov, ktoré sú im posielané. To je v praxi veľmi významné, pretože to umožňuje vyhnúť sa fenoménu SQL injection.

Je ale zaujímavé, že ani jeden z uvedených argumentov nie je pre nasadenie uložených procedúr ten najvýznamnejší. Zďaleka najdôležitejším argumentom pre nasadenie uložených procedúr na server býva to, že sú všeobecne schopné bežať rýchlejšie, ako keby sa príslušný kód vykonával príkaz po príkaze z klientskej aplikácie. Dôvodom je fakt, že uložené procedúry, ako sme už uviedli, sú na serveri skompilované.

Skompilovať uložené procedúry neznamená, že je táto procedúra prevedená do spustiteľnej, binárne formy, ani to, že je uložená do samostatného súboru. Zkompilováním je myslené to, že server si pre uloženú procedúru vytvorí a spravuje tzv. realizačný plán. Vďaka tomu je subsystém serveru zvaný optimalizátor zvyčajne schopný nájsť najrýchlejší spôsob, ako uloženú procedúru vykonať, a potom tento spôsob opakovane používať pri jednotlivých volaniach tejto uloženej procedúry.

Čo môžu uložené procedúry robiť

Zjednodušene sa dá povedať, že uložené procedúry môžu robiť väčšinu z toho, čo "bežné" príkazy. Medzi najčastejšie aktivity, ktoré taká uložená procedúra vykonáva, patria najmä:

• Vyberanie dát
• Vkladanie, aktualizácia, odstraňovanie dát
• Vytváranie, používanie a rušenie dočasných tabuliek
• Matematické a štatistické výpočty

Syntax vytvorenia uložených procedúr

V databázovom systéme mySQL je syntax CREATE PROCEDURE nasledovná:

CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

type:
    Ľubovolné dátové typy mySQL

routine_body:
    Platný SQL príkaz

kde:

• DEFINER - používateľ, ktorému bude uložená procedúra patriť.
• sp_name - názov uloženej procedúry. V názve nepoužívajte medzery a diakritiku.
• proc_parameter - procedúra môže mať vstupné alebo výstupné parametre
  • IN - špecifikátor pre vstupný parameter. Ako príklad môžeme uviesť: vytvorte procedúru, ktorá z tabuľky city vypíše mesta určitej krajiny. Kód krajiny chceme meniť, preto kód krajiny použijeme ako vstupný parameter procedúry.
  • OUT - špecifikátor pre výstupný parameter. Ako príklad môžeme uviesť: vytvorte procedúru ktorá vyhľadá v danej krajine (kód krajiny bude vstupným parametrom) mesto s najväčšou populáciou. Toto mesto bude výstupným parametrom procedúry.

Poznámky pri vytváraní uložených procedúr:

• každý SQL príkaz musí byť ukončený ukončovacím znakom
• Štandardný ukončovací znak je bodkočiarka ";". Avšak ak použijeme vo vnútri procedúry bodkočoarku, databázový systém to vyhodnotí ako ukončenie definície procedúry, pokúsi sa procedúru uložiť. Toto uloženie však skončí neúspechom, pretože procedúra nie je dokončená a sú tam syntaktické chyby.
• Na dočasnú zmenu ukončovacieho znaku slúži príkaz DELIMITER. Zápisom DELIMITER // spôsobíme, že ukončovací znak nebude bodkočiarka (;) ale dve lomítka (//).

Príklad najjednoduchšej procedúry:

1    delimiter  //
2    create procedure mesta() 
3    begin 
4       select * from city;
5    end //
6    delimiter ;

Vysvetlenie:

• Riadok 1: Ukončovací znak zmeníme na "//"
• Riadok 2: Definícia uloženej procedúry mesta. Procedúra nemá žiadne parametre.
• Riadok 3: Začiatok procedúry. Vždy sa začína kľúčovým slovom begin
• Riadok 4: Telo procedúry. Za každým SQL príkazom musí byť ukončovací znak bodkočiarka (;)
• Riadok 5: Koniec procedúry. Vždy sa končí kľúčovým slovom end. Procedúra musí byť ukončená ukončovacím znakom (pozor, nie bodkočiarka!)
• Riadok 6: Vrátime naspäť význam znaku ";"

Spustenie uloženej procedúry

Uloženú procedúru môžeme spustiť pomocou kľúčového slova CALL . Syntax je nasledovná:

   CALL sp_name([parameter[,...]])
   alebo
   CALL sp_name[()]

Našu prvú procedúru spustíme nasledovne:

   CALL mesta();

Poznámka k vytváraniu a spúšťaniu uložených procedúr: Pri vytváraní a spúšťaní uložených procedúr je vhodnejšie používať príkazový riadok (resp. databázovú konzolu), alebo nástroj, ktorý dovoľuje pracovať priamo v konzole. Takýmto prostredím je program MySQL Workbench. Prostredie phpMyAdmin nie je vhodné pre spúšťanie uložených procedúr.

Postup pri vytváraní uložených procedúr v prostredí MySQL Workbench

1. V úvodnom okne programu vyberieme v ľavom stĺpci databázové spojenie.

2. Vyberieme si databázu, s ktorou budeme pracovať. (V našom prípade, to bude databáza student)

3. Zvolíme "Add routine" (pridať funkciu/procedúru). Otvorí sa sprievodca pridaním procedúry s predpripraveným kódom:

-- -----------------------------------------------
-- Routine DDL
-- -----------------------------------------------
DELIMITER $$

CREATE PROCEDURE `student`.`new_routine` ()
BEGIN

END$$

4. Zmeníme názov procedúry, doplníme telo procedúry a procedúru uložíme na server (apply).

Konkrétne príklady uložených procedúr sú v nasledujúcom texte.

Príklady na uložené procedúry

Procedúry bez parametrov

Úloha:

Vytvorte uloženú procedúru, ktorá vráti všetky hlavné mestá Európy.

Analýza:

Procedúra nemá žiadne vstupné parametre. Úlohou je zistiť len hlavné mestá Európy. Pomocou jednoduchého SQL príkazu zistíme všetky hlavné mestá Európy. Procedúru nazvime HlavneMestaEuropy.

Riešenie:

DELIMITER $$

CREATE PROCEDURE `student`.`HlavneMestaEuropy` ()
BEGIN
   SELECT Country.Name as Krajina, City.Name as Mesto 
   FROM country, city 
   WHERE Capital=city.ID 
         and Continent="Europe";
END$$

Procedúry s vstupnými parametrami

Úloha:

Vytvorte uloženú procedúru, ktorá vráti všetky hlavné mestá zadaného kontinentu.

Analýza:

Procedúra bude mať 1 vstupný parameter - názov kontinentu. V tabuľke country sú kontinenty zadefinované ako vymenovaný typ: 'Asia','Europe','North America','Africa','Oceania','Antarctica','South America'. Procedúra teda očakáva názov kontinentu a podľa tejto hodnoty vypíše hlavné mestá daného kontinentu. Názov procedúry bude HlavneMesta, parameter procedúry je vstupný parameter (IN).

Riešenie:

DELIMITER $$

CREATE PROCEDURE `student`.`HlavneMesta` (IN kontinent  VARCHAR(16))
BEGIN
   SELECT Country.Name as Krajina, City.Name as Mesto 
   FROM country, city 
   WHERE Capital=city.ID 
         and Continent like kontinent;
END$$

Výsledok:

Procedúry s výstupnými parametrami

Úloha:

Vytvorte uloženú procedúru, ktorá vráti krajinu, ktorá je v danom kontinente najľudnatejšia, teda má najviac obyvateľov

Analýza:

Procedúra bude mať 1 vstupný parameter - názov kontinentu a jeden výstupný parameter - krajina, ktorú hľadáme. Procedúra očakáva názov kontinentu a podľa tejto hodnoty zistí krajinu, ktorá má v danom kontinente najväčšiu populáciu. Výstupný parameter označujeme značkou OUT.

Riešenie:

DELIMITER $$
CREATE  PROCEDURE `NajludnatejsiaKrajina`(IN kontinent VARCHAR(16), OUT nazov VARCHAR(32))
BEGIN
  SELECT Name from country where population=
   (   
        SELECT MAX(population) FROM country WHERE Continent LIKE kontinent
   );
END

Použite procedúry:

S výstupnými parametrami procedúr sa pracuje odlišne. Pri volaní takejto procedúry musíme určiť, kam sa hodnota výstupného parametra uloží a následne ju odtiaľ prečítať. Použitie procedúry pre zistenie krajiny v Afrike s najväčším počtom obyvateľov:

call NajludnatejsiaKrajina("Africa",@krajina);
select @krajina;

Poznámka" Výsledok procedúry sa uloží do lokálnej premennej označenej ako @krajina. Príkazom select @krajina zistíme hodnotu tohoto parametru.

Výsledok:

Name
Nigeria

Procedúry s riadením vykonávania SQL príkazov

V procedúrach môžeme používať aj príkazy na podmienené vykonávanie príkazov (if) alebo cyklické opakovnaie vykonávania príkazov (while, for, do).

Úloha:

Vytvorte procedúru up_jazyky ktorá zistí počet oficiálnych, resp. neoficiálnych jazykov v danej krajine. O tom, či budeme počítať oficiálne alebo neoficiálne jazyky rozhodne parameter oficialne.

Analýza úlohy: O tom, či je jazyk oficiálny alebo nie, hovorí atribút isOfficial, ktorý môže nadobúdať hodnoty T (true) alebo F (false). Naša procedúra bude mať parameter oficialne, ktorý bude typu boolean a teda môže nadobúdať len hodnoty true alebo false. SQL príkaz, ktorý vyrieši danú úlohu je nasledovný:

  SELECT CountryCode, count(CountryCode) from countryLanguage WHERE IsOfficial='T' GROUP BY CountryCode
resp.
  SELECT CountryCode, count(CountryCode) from countryLanguage WHERE IsOfficial='F' GROUP BY CountryCode

Pre určenie ktorý SQL príkaz sa vykoná použijeme príkaz IF-ELSE. Syntax príkazu IF je nasledovná:

   IF vyraz THEN
      príkaz 1;
   ELSE
      príkaz 2;
  END IF;

V prrípade, ak je splnený logický výraz pri príkaze IF, bude vykonaný prvý príkaz1, v opačnom prípade bude vykonaný SQL príkaz za kľúčovým slovom ELSE. Príkaz IF musí byť ukončený príkazom END IF:

Riešenie príkladu bude nasledovné

DELIMITER $$

CREATE PROCEDURE `up_jazyky`(IN oficial boolean)
BEGIN
   if oficial=true then
      SELECT CountryCode, count(CountryCode) AS pocet from countryLanguage WHERE IsOfficial='T' GROUP BY CountryCode;
   else
      SELECT CountryCode, count(CountryCode) AS pocet from countryLanguage WHERE IsOfficial='F' GROUP BY CountryCode;
  end if;       
END

Výsledok:

CALL up_jazyky(false) CountryCode pocet ABW 3 AFG 3 AGO 9 ALB 2 AND 3 ANT 1 ARE 1

CALL up_jazyky(true) CountryCode pocet ABW 1 AFG 2 AIA 1 ALB 1 AND 1 ANT 2 ARE 1

Poznámka: parameter procedúry up_jazyky nedávame do úvodzoviek, lebo sa nejedná o reťazec, ale o logickú hodnotu.

Úloha:

Vytvorte procedúru ktorá zabezpečí vloženie nového záznamu do tabuľky city. V prípade, ak s v tabuľke už dané mesto nachádza, záznam nevkladajte, ale aktualizujte pôvodný záznam.

Analýza:

Procedúra VlozMesto bude mať 4 vstupné parametre: názov mesta, kód krajiny, počet obyvateľov a okres. V prípade, ak ešte v tabuľke neexistuje dané mesto údaje vložíme pomocou príkazu INSERT, v opačnom prípade údaje aktualizujeme pomocou príkazu UPDATE.

DELIMITER $$

CREATE PROCEDURE `student`.`VlozMesto` (mesto CHAR(35), kod CHAR(3), okres CHAR(20), populacia INT)
BEGIN

IF EXISTS(SELECT * FROM  city WHERE name LIKE mesto)THEN
     UPDATE city SET CountryCode=kod , District=okres, Population=populacia WHERE name=mesto; 
  ELSE
     INSERT INTO city (name, CountryCode, District, Popuslation) values (mesto, kod, okres, populacia);   
  end if; 
END$$

Výsledok:

   call VlozMesto("Soblahov","SVK","Trenciansky",2114);

Výsledok po zavolaní procedúry: (SELECT * FROM city WHERE CountryCode="SVK")

ID	Name	CountryCode	District	Population
3210	Košice	SVK	Východné Slovensko	241874
3211	Prešov	SVK	Východné Slovensko	93977
3209	Bratislava	SVK	Bratislava	431061
4084	Soblahov	SVK	Trenciansky	2114

Druhé volanie tej istej procedúry:

   call VlozMesto("Soblahov","SVK","Trenciansky",2345);

Výsledok po druhom zavolaní procedúry: (SELECT * FROM city WHERE CountryCode="SVK")

ID	Name	CountryCode	District	Population
3210	Košice	SVK	Východné Slovensko	241874
3211	Prešov	SVK	Východné Slovensko	93977
3209	Bratislava	SVK	Bratislava	431061
4084	Soblahov	SVK	Trenciansky	2345