Procedury a funkce

Databázový systém (jako například MariaDB) umožňuje komunikaci nejenom pomocí přímých příkazů (CREATE, INSERT, UPDATE, SELECT) ale umožňuje také příkazy ukládat v podobě procedur nebo funkcí a následně je (opakovaně) volat. Procedury nebo funkce mohou zároveň působit i jako rozhraní mezi databází a aplikací – mohou zajišťovat, že aplikační vývojář má jen takový přístup k datům, jaký mu databázový vývojář připravil.

Tak například nebude volat výběr dat pomocí příkazu

SELECT jmeno, prijmeni, name, samotnehodnoceni FROM 04_hodnoceni JOIN 04_person ON 04_person.personID = 04_hodnoceni.personID JOIN 04_cukrovi ON 04_cukrovi.cukroviID = 04_hodnoceni.cukroviID ORDER BY 04_cukrovi.cukroviID;

ale zavolá jednoduše již připravenou proceduru pomocí příkazu

CALL showHodnoceni;

Používáním procedur a funkcí tedy zajistíme správné použití databázového modelu a zároveň zvyšujeme bezpečnost uložených dat (aplikace nemohou číst resp. měnit data jak chtějí, ale pouze tak, jak jsme jim jakožto databázoví vývojáři připravili).

Procedury

Procedury se používají pro vykonávání akcí (vkládání dat, úpravy, mazání, složitější transakce). Nemusejí vracet nic, ale mohou vracet i celé tabulky (výsledky SELECTu). Abychom si mohli vyzkoušet nadefinovat nějakou proceduru, je nejprve nutné změnit delimiter. Delimiter je ve standardní MariaDb znak ; (středník). Protože ale v definici procedury budeme potřebovat ukládat i konce příkazů (středníky), na chvíli si delimiter změníme a pak jej opět vrátíme:

DELIMITER // 
CREATE PROCEDURE 04_getPersons()
BEGIN
  SELECT * FROM 04_person;
END//
DELIMITER ;

Na příkladu výše jsme si ukázali, jak nadefinovat prostou proceduru, která nemá žádné vstupní parametry a vypisuje tabulku (výsledek SELECTu). Vytvořenou proceduru zavoláme:

CALL 04_getPersons;

Nyní pojďme vyzkoušet složitější proceduru, která bude mít i vstup:

DELIMITER //
CREATE PROCEDURE 04_getPerson(IN ID int) 
BEGIN 
  SELECT * FROM 04_person WHERE personID = ID; 
END//
DELIMITER ;

Proceduru opět zavoláme pomocí příkazu:

CALL 04_getPerson(7);

Stejným způsobem můžeme nadefinovat a následně zkusmo zavolat například proceduru pro vložení nové osoby:

DELIMITER //
CREATE PROCEDURE 04_addPerson(IN ID int, IN jmeno varchar(50), IN prijmeni varchar(50))
BEGIN
  INSERT INTO 04_person (personID, jmeno, prijmeni) VALUES (ID, jmeno, prijmeni);
END//
DELIMITER ;

CALL 04_addPerson(9, "Zora", "Bergmannová");

Funkce

Narozdíl od procedury, funkci můžeme použít přímo v příkazech. Pojďme si ukázat opravdu jednoduchou funkci, která bude mít vstupní parametr váhu v gramech (datový typ int) a vrátí váhu v kilogramech (datový typ DECIMAL(10,3)). Naše funkce vůbec nebude používat žádné relace, jenom vrátí jednu tisícinu.

DELIMITER //
CREATE FUNCTION gToKg(vahag INT)
RETURNS DECIMAL(10,3)
DETERMINISTIC
BEGIN
  RETURN vahag / 1000;
END//
DELIMITER ;

Nyní si funkci můžeme vyzkoušet:

SELECT gToKg(3750);
+-------------+
| gToKg(3750) |
+-------------+
|       3.750 |
+-------------+

Nyní si zkusme o fousek složitější funkci, která vrátí poštovné v závislosti na váze zásilky. Řekněme si například, že bude-li zásilka vážit <= 100 g, bude poštovné 70 Kč, bude-li vážit > 100 g, bude poštovné 110 Kč.

DELIMITER //
CREATE FUNCTION postovne(vahag INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
  IF vahag <= 100 THEN
    RETURN 70.00;
  ELSE
    RETURN 110.00;
  END IF;
END//
DELIMITER ;

Ověříme funkci opět prostým selectem:

SELECT postovne(90) AS postovne;
+----------+
| postovne |
+----------+
|    70.00 |
+----------+
SELECT postovne(450) AS postovne;
+----------+
| postovne |
+----------+
|   110.00 |
+----------+

Funkce

Funkce používáme přímo v SQL příkazech – nejčastěji v SELECTu. Krásný jednoduchý příklad si můžeme ukázat na funkci, která velmi jednoduše kontroluje, zda-li e-mail obsahuje zavináč (@):

DELIMITER //

CREATE FUNCTION CheckEmail(email VARCHAR(255))
RETURNS BOOLEAN
DETERMINISTIC
RETURN email LIKE '%@%'; 

//
DELIMITER ;

Abychom si funkci vyzkoušeli (ještě předtím, než ji ve skutečnosti použijeme), zavoláme ji ze SELECTU:

SELECT CheckEmail("bezzavinace");
SELECT CheckEmail("neco@neco");

Vidíme, že pravidlo LIKE ‚%@%‘ bylo možná příliš jednoduché – zkuste jej nahradit například LIKE ‚%@%.%‘
Tělo funkce bohužel nelze změnit – proto je nutné funkci nejprve smazat a poté znovu vytvořit.

DROP FUNCTION CheckEmail;

Bohužel MariaDB neumožňuje uživatelsky definovanou funkci použít pro definici doménového integritního omezení – na to už budeme muset použít trigger, ale to je jiná kapitola.

Funkce, která spočítá věk

Zkusíme ještě další příklad – funkce, která nám vrátí aktuální věk na základě vstupního atributu datum narození. Budeme k tomu používat funkci TIMESTAMPDIFF(jednotka, první_date, druhý_date) – čili budeme počítat rozdíl v jednotce YEAR mezi datem ve vstupním atributu a aktuálním datem. Vnitřek tedy bude vypadat podle vzoru:

TIMESTAMPDIFF(YEAR, "2008-01-01", CURDATE());

Pojďme si tedy funkci nadefinovat:

DELIMITER //

CREATE FUNCTION SpoctiVek(birth_date DATE)
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN TIMESTAMPDIFF(YEAR, birth_date, CURDATE());
END //

DELIMITER ;