V článku Kurzory v MySQL jsem pro předvedení funkčních příkladů použil jednu z programových jednotek MySQL. Konkrétně stored procedure, tedy uloženou proceduru. V tomto článku bych se jim chtěl věnovat trochu blíže.
Procedury jsou programové jednotky určené pro opakované spouštění skupiny příkazů. Jejich kód je uložený na serveru a je zkompilovaný pro rychlejší vykonání.

Procedury mohou obsahovat vstupní parametry. Pokud však nadefinujeme proceduru s parametrem, nelze jí poté volat bez parametru. Stejně tak volání procedury, která nemá vstupní parametr, skončí chybou, pokud se jí budeme snažit použít s parametrem.
Procedury nevrací žádnou hodnotu. Nelze je tedy použít jako součást vykonávaných dotazů. Je však možné je použít uvnitř dalších programových jednotek. Což může být vhodné u procedur, které modifikují nějaká data.

Volání procedur se provádí příkazem:
CALL jmenoProcedury([parametr1, parametr2]);
Smazání procedury se provede pomocí:
DROP PROCEDURE jmenoProcedury;

Vytvoření a použití procedur ukážu opět na několika příkladech

Příprava databáze

Vytvoření databáze

MySQL:
CREATE DATABASE 'test_procedura';

Vytvoření tabulek

MySQL:
CREATE TABLE `studenti` (
  `rod_cislo` VARCHAR(11) NOT NULL,
  `jmeno` VARCHAR(15) NOT NULL,
  `prijmeni` VARCHAR(15) NOT NULL,
  `trida` VARCHAR(4) NOT NULL,
  PRIMARY KEY  (`rod_cislo`)
) ;

CREATE TABLE `predmety` (
  `cis_predmetu` TINYINT(2) NOT NULL,
  `nazev` VARCHAR(15) NOT NULL,
  PRIMARY KEY  (`cis_predmetu`)
);

CREATE TABLE `znamky` (
  `rod_cislo` VARCHAR(11) NOT NULL,
  `cis_predmetu` TINYINT(2) NOT NULL,
  `znamka` TINYINT(1) NOT NULL,
  PRIMARY KEY  (`rod_cislo`,`cis_predmetu`)
);

Naplnění tabulek testovacími daty (kvůli množství záznamů v tabulce znamky, jsem uvedl insert jen pro jednoho studenta)

MySQL:
INSERT INTO `studenti` (`rod_cislo`, `jmeno`, `prijmeni`, `trida`) VALUES
('886114/3871', 'Petra', 'B', '8A'),
('890702/2587', 'Marek', 'P', '8B'),
('881122/9654', 'Honza', 'U', '8B'),
('870826/8742', 'Petr', 'J', '8A'),
('880828/5141', 'Jarek', 'M', '8A'),
('895502/6574', 'Jana', 'N', '8A'),
('876130/6871', 'Veronika', 'B', '8A'),
('881206/5741', 'Mirek', 'S', '8B'),
('896015/5874', 'Olga', 'H', '8B'),
('881027/6571', 'Rudolf', 'E', '8B');

INSERT INTO `predmety` (`cis_predmetu`, `nazev`) VALUES
(1, 'matematika'),
(2, 'cestina'),
(3, 'dejepis'),
(4, 'zemepis'),
(5, 'telocvik'),
(6, 'chemie');

INSERT INTO `znamky` (`rod_cislo`, `cis_predmetu`, `znamka`) VALUES
('886114/3871', 1, 2),
('886114/3871', 2, 1),
('886114/3871', 3, 1),
('886114/3871', 4, 2),
('886114/3871', 5, 1),
('886114/3871', 6, 1);

Příklad na proceduru bez parametrů

Vyznamenáni jsou vždy studenti, kteří mají průměr známek menší či roven 1.5 a nejhorší známku dvojku. Tato procedura projde všechny studenty a vypíše ty, kteří splňuji podmínky pro vyznamenání.

MySQL:
mysql> DELIMITER //
mysql> CREATE PROCEDURE vyznamenani()
    -> BEGIN
    -> DECLARE prumer FLOAT(3);
    -> DECLARE pocet_znamek INT(2);
    -> DECLARE soucet INT(3);
    -> DECLARE nejvetsi TINYINT(1);
    ->
    -> DECLARE done BIT(1) DEFAULT 0;
    ->
    -> DECLARE s_rod_cislo VARCHAR(11);
    -> DECLARE s_jmeno VARCHAR(15);
    -> DECLARE s_prijmeni VARCHAR(15);
    -> DECLARE s_trida VARCHAR(4);
    ->
    -> DECLARE student CURSOR FOR
    -> SELECT rod_cislo, jmeno, prijmeni, trida
    -> FROM studenti ORDER BY trida;
    ->
    -> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    ->
    -> CREATE TABLE vystup(
    -> jmeno VARCHAR(15),
    -> prijmeni VARCHAR(15),
    -> trida VARCHAR(4),
    -> prumer FLOAT(3));
    ->
    -> OPEN student;
    -> student_loop: LOOP
    -> FETCH student INTO s_rod_cislo, s_jmeno, s_prijmeni, s_trida;
    -> IF done THEN LEAVE student_loop; END IF;
    ->
    -> SELECT sum( znamka ), count( znamka ), max( znamka )
    -> INTO soucet, pocet_znamek, nejvetsi
    -> FROM znamky WHERE rod_cislo = s_rod_cislo;
    ->
    -> SET prumer = soucet / pocet_znamek;
    -> IF prumer <= 1.5 AND nejvetsi <3 THEN
    -> INSERT INTO vystup
    -> VALUES(s_jmeno,s_prijmeni,s_trida,ROUND(prumer,2));
    -> END IF;
    ->
    -> END LOOP student_loop;
    -> CLOSE student;
    ->
    -> SELECT * FROM vystup;
    -> DROP TABLE vystup;
    -> END;//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

Na začátku procedury jsou uvedeny deklarace proměnných. Následuje deklarace explicitního kurzoru, do kterého jsou načteny údaje o studentech. Dále je vytvořena tabulka pro ukládání vyznamenaných studentů. Ta bude na konci procedury smazána.
Zde by šla použít také dočasná tabulka, která se automaticky smaže po ukončení spojení s MySQL serverem. Ovšem v případě, že pracujete v MySQL konzoli, a budete chtít proceduru spustit dvakrát za sebou, skončíte na chybě: ERROR 1050 (42S01): Table 'vystup' already exists. Spojení se serverem totiž nebylo ukončeno a tak se dočasná tabulka nesmazala. Pokud by jste se přesto rozhodli pro dočasnou tabulku, stačí upravit příkaz na její vytvoření na CREATE TEMPORARY TABLE vystup. Příkaz DROP TABLE vystup; na konci procedury by pak nebyl potřeba.
V cyklu, který čte záznamy z kurzoru student je použit implicitni kurzor, načítající údaje o známkách studenta. Pokud známky vyhovují podmínkám pro vyznamenání, je student přidán do tabulky pro výstup.

Z mých testovacích dat, vybere procedura tyto studenty, kteří splňují podmínky pro vyznamenání:

MySQL:
mysql> CALL vyznamenani();
+----------+----------+-------+--------+
| jmeno    | prijmeni | trida | prumer |
+----------+----------+-------+--------+
| Petra    | B        | 8A    |   1.33 |
| Veronika | B        | 8A    |   1.33 |
| Honza    | U        | 8B    |   1.17 |
+----------+----------+-------+--------+
3 rows in SET (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Příklad na proceduru s parametrem

Tato procedura vypíše všechny studenty, a předměty, ze kterých mají známku 5. Vstupní parametr p je řetězec, označující třídu, ze které chcete studenty vypsat. Pokud je zadán parametr, který neodpovídá žádné třídě, jsou vypsáni "propadlíci" ze všech tříd.

MySQL:
mysql> DELIMITER //
mysql> CREATE PROCEDURE propadajici(p VARCHAR(4))
    -> BEGIN
    ->
    -> DECLARE done BIT(1) DEFAULT 0;
    -> DECLARE parametr_ok TINYINT(1);
    -> DECLARE s_jmeno VARCHAR(15);
    -> DECLARE s_prijmeni VARCHAR(15);
    -> DECLARE s_trida VARCHAR(4);
    -> DECLARE s_nazev VARCHAR(15);
    ->
    -> DECLARE studenti CURSOR FOR
    -> SELECT jmeno, prijmeni, trida, nazev
    -> FROM znamky z, studenti s, predmety p
    -> WHERE z.rod_cislo = s.rod_cislo
    -> AND z.cis_predmetu = p.cis_predmetu
    -> AND znamka = 5;
    ->
    -> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    ->
    -> SELECT COUNT(*) INTO parametr_ok
    -> FROM studenti WHERE trida = p;
    ->
    -> CREATE TABLE vystup(
    -> jmeno VARCHAR(15),
    -> prijmeni VARCHAR(15),
    -> trida VARCHAR(4),
    -> predmet VARCHAR(15));
    ->
    -> OPEN studenti;
    -> studenti_loop: LOOP
    -> FETCH studenti INTO s_jmeno, s_prijmeni, s_trida, s_nazev;
    -> IF done THEN LEAVE studenti_loop; END IF;
    -> IF s_trida = p OR parametr_ok = 0 THEN
    -> INSERT INTO vystup VALUES (s_jmeno,s_prijmeni,s_trida,s_nazev);
    -> END IF;
    -> END LOOP studenti_loop;
    -> CLOSE studenti;
    ->
    -> SELECT * FROM vystup;
    -> DROP TABLE vystup;
    -> END;//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

Procedura opět začíná deklarací proměnných, za kterými následuje explicitní kurzor studenti, který pomocí klauzule WHERE spojuje všechny tři tabulky, a kurzor naplní potřebnými daty. Implicitni kurzor poté do proměnné parametr_ok uloží počet výskytů řetězce v tabulce studenti, který byl vložen jako parametr procedury. Pokud je počet roven nule, tedy třída se v tabulce studenti nenachází, je tento parametr považován za neplatný a výpis propadajících studentů bude ze všech tříd.

Výstup volání procedury propadajici s platnými parametry (třídy 8A a 8B) a s neplatným parametrem "8C":

MySQL:
mysql> CALL propadajici("8A");
+-------+----------+-------+------------+
| jmeno | prijmeni | trida | predmet    |
+-------+----------+-------+------------+
| Jarek | M        | 8A    | matematika |
| Jana  | N        | 8A    | dejepis    |
+-------+----------+-------+------------+
2 rows in SET (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> CALL propadajici("8B");
+--------+----------+-------+---------+
| jmeno  | prijmeni | trida | predmet |
+--------+----------+-------+---------+
| Rudolf | E        | 8B    | zemepis |
| Rudolf | E        | 8B    | chemie  |
+--------+----------+-------+---------+
2 rows in SET (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

mysql> CALL propadajici("8C");
+--------+----------+-------+------------+
| jmeno  | prijmeni | trida | predmet    |
+--------+----------+-------+------------+
| Jarek  | M        | 8A    | matematika |
| Jana   | N        | 8A    | dejepis    |
| Rudolf | E        | 8B    | zemepis    |
| Rudolf | E        | 8B    | chemie     |
+--------+----------+-------+------------+
4 rows in SET (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Procedura volaná uvnitř programové jednotky

Tato procedura načte z tabulky studenti všechny třídy a ty pak v cyklu použije jako parametr dříve vytvořené procedury propadajici.

MySQL:
mysql> DELIMITER //
mysql> CREATE PROCEDURE test()
    -> BEGIN
    ->
    -> DECLARE done BIT(1) DEFAULT 0;
    -> DECLARE s_trida VARCHAR(4);
    ->
    -> DECLARE tridy CURSOR FOR
    -> SELECT trida FROM studenti
    -> GROUP BY trida;
    ->
    -> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    ->
    -> OPEN tridy;
    -> tridy_loop: LOOP
    -> FETCH tridy INTO s_trida;
    -> IF done THEN LEAVE tridy_loop; END IF;
    -> CALL propadajici(s_trida);
    -> END LOOP tridy_loop;
    -> CLOSE tridy;
    ->
    -> END;//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> CALL test();
+-------+----------+-------+------------+
| jmeno | prijmeni | trida | predmet    |
+-------+----------+-------+------------+
| Jarek | M        | 8A    | matematika |
| Jana  | N        | 8A    | dejepis    |
+-------+----------+-------+------------+
2 rows in SET (0.01 sec)

+--------+----------+-------+---------+
| jmeno  | prijmeni | trida | predmet |
+--------+----------+-------+---------+
| Rudolf | E        | 8B    | zemepis |
| Rudolf | E        | 8B    | chemie  |
+--------+----------+-------+---------+
2 rows in SET (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

Takto použité volání procedury je v celku zbytečné (stejného efektu lze dosáhnout jednodušeji). Pokud by však procedura, volaná například v cyklu další programové jednotky, upravovala data, nad kterými programová jednotka pracuje, měl by tento způsob použití určitě svůj smysl.