O kurzorech jsem se již letmo zmínil v článku Triggery v MySQL. Dneska se na ně koukneme trošku blíže.
Kurzory slouží k načítání údajů z databáze do proměnných, které lze dále použít pří zpracovávání programové jednotky. MySQL nyní umožňuje použití kurzorů pouze s příkazem SELECT.
Proměnná, do které chceme načtenou informaci uložit, musí být stejného datového typu a délky jako sloupec, ze kterého informace pochází.

Kurzory lze je rozdělit na:

Implicitní kurzory

  • nejsou předem definované
  • jsou použity přímo v těle triggeru / uložené funkce či procedury
  • smí obsahovat pouze SQL příkazy, které vracejí jen jeden řádek

Explicitní kurzory

  • jsou předem definované
  • mohou vracet více řádků
  • pracuje se s nimi jako se soubory. Nejprve je třeba kurzor otevřít, poté se čtou data a na závěr se musí kurzor zavřít

Zápis a použití kurzorů se pokusím předvést na několika příkladech. Kurzory lze použít pouze v programových jednotkách MySQL, tedy triggerech, uložených procedurách a funkcích. Pro ukázání funkčnosti jsem zvolil proceduru. Aby byla činnost kurzoru patrná, je výstupní text ukládán do tabulky vystup. Před ukončením procedury je celá tabulka vystup vypsána.

Příprava databáze

Vytvoření databáze:

MySQL:
CREATE DATABASE `test_kurzor`;

Vytvoření tabulek:

MySQL:
CREATE TABLE `lide` (
  `r_cislo` VARCHAR(11) NOT NULL,
  `jmeno` VARCHAR(20) NOT NULL,
  `mesto` VARCHAR(20) NOT NULL,
  `student` TINYINT(1) NOT NULL,
  UNIQUE KEY `r_cislo` (`r_cislo`)
);

CREATE TABLE `vystup` (
  `TEXT` VARCHAR(100) NOT NULL
);

Naplnění daty:

MySQL:
INSERT INTO `lide` (`r_cislo`, `jmeno`, `mesto`, `student`)
VALUES
('840512/5134', 'Martin', 'Praha', 1),
('726117/8451', 'Petra', 'Brno', 0),
('911106/3598', 'Honza', 'Praha', 1),
('820225/8442', 'Radim', 'Praha', 0),
('855322/6871', 'Veronika', 'Praha', 0),
('711203/1571', 'Pavel', 'Brno', 0),
('885120/8751', 'Jana', 'Brno', 1);

Implicitní kurzory

Struktura

Po deklaraci proměnných, následuje příkaz SELECT s výčtem sloupců, jejichž obsah se uloží do proměnných, uvedených za klíčovým slovem INTO. Dále následuje zbytek příkazu SELECT.

MySQL:
mysql> DECLARE promenna1 datovy_typ(velikost);
    -> DECLARE promenna2 datovy_typ(velikost);
    -> ...
    -> DECLARE promennaN datovy_typ(velikost);
    ->
    -> SELECT sloupec1, sloupec2, ..., sloupecN
    -> INTO promenna1, promenna2, ..., promennaN
    -> FROM tabulka
    -> ...

Příklad

Implicitní kurzor uloží do proměnné počet studentů z tabulky lide

MySQL:
mysql> DELIMITER //
mysql> CREATE PROCEDURE pocet_studentu()
    -> BEGIN
    -> DECLARE pocet INT(3);
    -> TRUNCATE TABLE vystup;
    -> SELECT COUNT(*) INTO pocet
    -> FROM lide
    -> WHERE student=1;
    -> INSERT INTO vystup
    -> VALUES (CONCAT("Pocet studentu: ",pocet));
    -> SELECT * FROM vystup;
    -> END;//
Query OK, 0 rows affected (0.05 sec)

mysql> DELIMITER ;
mysql> CALL pocet_studentu();
+-------------------+
| TEXT              |
+-------------------+
| Pocet studentu: 3 |
+-------------------+
1 row in SET (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

Explicitní kurzory

Struktura

I zde jsou první deklarace proměnných, po kterých následují deklarace kurzorů. Jelikož MySQL zatím nepodporuje cyklus FOR pro procházení result setu kurzoru, je třeba vložit deklaraci handleru pro ošetření chyby, vzniklé nenačtením dat.
Samotnou práci s kurzorem zahájíme jeho otevřením. Data z něj načteme nejčastěji pomocí některého z cyklů. Pokud s kurzorem nechceme dále pracovat, je třeba jej zavřít.

MySQL:
mysql> DECLARE promenna1 datovy_typ(velikost);
    -> DECLARE promenna2 datovy_typ(velikost);
    -> ...
    -> DECLARE promennaN datovy_typ(velikost);
    ->
    -> DECLARE jmeno_kurzoru CURSOR FOR
    -> SELECT sloupec1, sloupec2, .., sloupecN FROM tabulka ..
    -> DECLARE CONTINUE HANDLER FOR chyba proved;
    ->
    -> OPEN jmeno_kurzoru;
    -> zacatek cyklu
    -> prace s kurzorem - nacitani dat DO promennych
    -> konec cyklu
    -> CLOSE jmeno_kurzoru;

Příklad

Pomocí explicitního kurzoru načteme z databáze všechny záznamy. Pro každý záznam se dle rodného čísla určí, zda jde o muže či ženu (připomenu, že ženy mají k měsíci narození přičteno číslo 50 a tak třetí číslice v jejich rodném čísle může nabývat pouze hodnoty 5 nebo 6).

MySQL:
mysql> DELIMITER //
mysql> CREATE PROCEDURE pohlavi()
    -> BEGIN
    -> DECLARE pohlavi VARCHAR(5);
    -> DECLARE z_r_cislo VARCHAR(11);
    -> DECLARE z_jmeno VARCHAR(10);
    -> DECLARE done BIT(1) DEFAULT 0;
    -> DECLARE zaznam CURSOR FOR SELECT r_cislo, jmeno FROM lide;
    -> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    -> TRUNCATE TABLE vystup;
    -> OPEN zaznam;
|   -> zaznam_loop: LOOP
|   -> FETCH zaznam INTO z_r_cislo, z_jmeno;
|   -> IF done THEN LEAVE zaznam_loop; END IF;
|   -> IF z_r_cislo LIKE '__1________' OR z_r_cislo LIKE '__0________' THEN
|   -> SET pohlavi = "muz";
|   -> ELSE SET pohlavi = "zena";
|   -> END IF;
|   -> INSERT INTO vystup
|   -> VALUES (CONCAT(z_jmeno," je ",pohlavi));
|   -> END LOOP zaznam_loop;
    -> CLOSE zaznam;
    -> SELECT * FROM vystup;
    -> END;//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> CALL pohlavi();
+------------------+
| TEXT             |
+------------------+
| Martin je muz    |
| Petra je zena    |
| Honza je muz     |
| Radim je muz     |
| Veronika je zena |
| Pavel je muz     |
| Jana je zena     |
+------------------+
7 rows in SET (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Pár vysvětlivek

Příkaz [DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;] říká, co se má stát v případě, že dojde k chybě NOT FOUND. Ta se vyvolá ve chvíli, kdy kurzor v příkazu FETCH nenačte žádná data. Tedy kdy dojde ke konci result setu. Ve chvíli, kdy je tato chyba vyvolána, nastaví se proměnná done na hodnotu '1'. Jelikož jde o CONTINUE HANDLER, který ošetří chybu a pokračuje dalším příkazem, je třeba kontrolovat, zda proběhlo úspěšné načtení dat do proměnných.
To zajistí příkazy [IF done THEN LEAVE zaznam_loop; END IF;] Pokud je nastavena proměnná done, je přerušena smyčka zaznam_loop a proběhne vypsání tabulky vystup.

Další způsoby práce s explicitními kurzory

Označenou část kódu z předchozího příkladu, lze zaměnit za kód uvedený níže. Výstup zůstane nezměněn.

Pomocí cyklu WHILE

MySQL:
|   -> FETCH zaznam INTO z_r_cislo, z_jmeno;
|   -> WHILE NOT done DO
|   -> IF z_r_cislo LIKE '__1________' OR z_r_cislo LIKE '__0________' THEN
|   -> SET pohlavi = "muz";
|   -> ELSE SET pohlavi = "zena";
|   -> END IF;
|   -> INSERT INTO vystup
|   -> VALUES (CONCAT(z_jmeno," je ",pohlavi));
|   -> FETCH zaznam INTO z_r_cislo, z_jmeno;
|   -> END WHILE;

Pomocí konstrukce REPEAT UNTIL

MySQL:
|   -> REPEAT
|   -> FETCH zaznam INTO z_r_cislo, z_jmeno;
|   -> IF NOT done THEN
|   -> IF z_r_cislo LIKE '__1________' OR z_r_cislo LIKE '__0________' THEN
|   -> SET pohlavi = "muz";
|   -> ELSE SET pohlavi = "zena";
|   -> END IF;
|   -> INSERT INTO vystup
|   -> VALUES (CONCAT(z_jmeno," je ",pohlavi));
|   -> END IF;
|   -> UNTIL done END REPEAT;

Rozdíly mezi zmíněnými variantami jsou minimální a je na Vás, kterou budete používat