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