Zobrazení zdroje stránky MySQL5:Jemný úvod do uložených procedur MySQL II

Jazyk uložených procedur je programovací procedurální jazyk jako všechny ostatní - Cčko, Perl, Modula nebo ADA. Najdeme v něm deklaraci proměnných, cykly, podmínky. Zvláštností je neexistence vstupních a výstupních funkcí, což je daň za přenesení na server (taky není možné, aby se v uložených procedurach čekalo na interakci uživatele - čas na serveru je příliš drahý). Druhou zvláštností, překvapující, je integrované SQL. Chvíli mi trvalo, než jsem tomu přišel na chuť. Je to můj soukromý názor, který nikomu nevnucuji, ale tím, že SQL příkazy nejsou rozbité uvozovkami a zavřené do parametrů funkcí jsou o hodně čitelnější. To nemluvím o tom, že se vlastně nic nového učit nemusíte: funkce a datové typy znáte a používáte v SQL. Navíc jsou jen podmínky, cykly, proměnné - lepidlo, které udržuje SQL příkazy pohromadě. Na netu najdete tucty jednoduchých příkladů, na kterých je sice názorně vidět syntaktický zápis té které konstrukce, ale vlastně vůbec žádný přínos oproti bez procedurálnímu stylu. Jeden z pěkných příkladů, které jsem našel, je procedura generující křížové sestavy. Tady musím poznat, že pg vývojáři vůbec nedoceňují rozdíl mezi procedurami a funkcemi, a PostgreSQL podobnou techniku řešení nepodporuje a ještě chvíli podporovat nebude. 

Příklad přebírám z [http://rpbouman.blogspot.com/ blogu] Rolanda Baumana, který ostatně doporučuji k přečtení. Z pětky jsou blogeři celkem u vytržení, a ani se nedivím. Vůči čtyřce je to generační skok, navíc podepřený super dokumentací. V podstatě všechny features pětky jsou důkladně popsány, uložené procedury nevyjímaje. Jejich dokumentace je asi to nejlepší, co jsem zatím viděl. I to, co dělají pro uživatele a jak si udržují a budují komunitu. Kód jsem mírně upravil přepsáním do ANSI SQL. 

O co jde. Naznačím postup (jinak, je to hodně šikovný prastarý obecný postup, jak vykouzlit křížovou tabulku, aniž bychom potřebovali podporu systému):

<pre>
CREATE TABLE employees (
    id INT auto_increment PRIMARY KEY,
    shop_id INT,
    gender ENUM('m', 'f'),
    name VARCHAR(32),
    salary INT
);

CREATE TABLE shops (
    shop_id INT auto_increment PRIMARY KEY,
    shop VARCHAR(32)
);

INSERT INTO shops (shop)
VALUES ('Zurich'), ('New York'), ('London');

INSERT INTO employees (shop_id, gender, name, salary)
VALUES
(1, 'm', 'Jon Simpson', 4500),
(1, 'f', 'Barbara Breitenmoser', 4700),
(2, 'f', 'Kirsten Ruegg', 5600),
(3, 'm', 'Ralph Teller', 5100),
(3, 'm', 'Peter Jonson', 5200);
</pre>

Jednodušeme získáme tabulku o prodeji v Londýně nebo v Curychu. Ovšem získat závislost o výši prodeji dle měst a pohlaví prodejce je řádově komplikovanější (sloupce: f, m, total; řádky: města).
<pre>
SELECT shop, 
		SUM(CASE gender WHEN 'f' THEN salary ELSE 0 END) AS f, 
		SUM(CASE gender WHEN 'm' THEN salary ELSE 0 END) AS m, 
		SUM(salary) AS total 
	FROM employees INNER JOIN shops USING (shop_id)
	GROUP BY shop
</pre>
O první půlku práce se postará agregace (GROUP BY), tj. separaci dat do skupin podle měst. Dál si už musíme pomoci sami. V SQL nedokážeme zařídit, aby se ve sloupci 'f' neobjevili "nesprávné" hodnoty, dokážeme ale zajistit, aby se tyto hodnoty nepříčítaly, ignorovaly - CASE gender WHEN 'f' THEN zalary ELSE 0 END. Rolandova procedura nedělá nic jiného, než že SELECT v tomto tvaru vygeneruje a provede. Kontrolní dotaz: jak upravit tento dotaz, aby zobrazoval počty a nikoliv součty.  
<pre>
1  DELIMITER //
2  SET sql_mode=ansi //
3  CREATE PROCEDURE xtab3 (dimx_name VARCHAR(32), dimx_source VARCHAR(32),
                                 dimy_name VARCHAR(32), dimy_source VARCHAR(256),
                                 expr VARCHAR(32))
4  BEGIN
5          SET @col_list_expr = 'SELECT  GROUP_CONCAT(DISTINCT '
                || '\'SUM(CASE ' || dimx_name || ' WHEN \'\'\'||'
                || dimx_name || '||\'\'\' THEN ' || expr || ' ELSE 0 END) '
                || ' AS  \'\'\'||'||dimx_name||'||\'\'\'   \')INTO @col_list ' || dimx_source;
                
6          PREPARE col_list_pc FROM @col_list_expr;
7          EXECUTE col_list_pc;
8          DEALLOCATE PREPARE col_list_pc;

9          SET @xtab_expr = 'SELECT '||dimy_name||','||@col_list
                || ', SUM('||dimx_name||') AS Total '|| dimy_source ||' GROUP BY '||dimy_name;
                                
10        PREPARE xtab FROM @xtab_expr;
11        EXECUTE xtab;
12        DEALLOCATE PREPARE xtab;

13  END //
14  SET sql_mode=''//
15  DELIMITER ;
</pre>
[5] Použit globální proměnné tu bije do očí. Obchází se tak chyba, která neumožňuje v příkazu PREPARE použít lokální proměnnou. Pozor: v PostgreSQL absolutně jiný způsob uložení výsledků EXECUTE, díky čemuž se klíčové slovo INTO se nesmí objevit v dynamickém příkazu. Generování SELECTu jiným SELECTEM je nejrychlejší a taky nejméně čitelný způsob, jak se sami můžete přesvědčit. [7,11] provedení připraveného příkazu - pokud se SQL příkaz generuje až za běhu procedury, označujeme jej jako dynamický příkaz. [7] vygeneruje seznam paremetrů, [9] k seznamu přidá popisku a sloupec Total. Dynamické příkazy nesmí obsahovat lokální proměnné, ty jsou mimo viditelnost. Mohou ale, v místech parametrů nebo části INTO obsahovat globální proměnné. Dynamický SQL příkaz musíme před samotným spuštěním "zkompilovat" - příkaz PREPARE [6,10]. Statické SQL příkazy překládat explicitně nemusíme, o to se postará systém při překladu uložené procedury. Překladem se v tomto případě myslí parsování SQL příkazu a připrava prováděcího plánu. Vygenerovaný SELECT bude uložen v globální proměnné @xtab_expr (SELECT @xtab_expr);
<pre>
mysql> CALL xtab3('gender','FROM employees', 'shop','FROM employees INNER JOIN shops USING (shop_id)','salary');
+----------+-------+------+-------+
| shop     | m     | f    | Total |
+----------+-------+------+-------+
| London   | 10300 |    0 | 10300 |
| New York |     0 | 5600 |  5600 |
| Zurich   |  4500 | 4700 |  9200 |
+----------+-------+------+-------+
3 rows in set (0,00 sec)

Query OK, 0 rows affected (0,00 sec)
</pre>
Přestože tato procedura už nějakou práci zastane, neobsahuje jedinou podmínku, jediný cyklus. A přesto je univerzální. Podobné funkce nemohou nahradit specializované nástroje, a ani se o to nesnaží, v případech, kdy postačí, vám ušetří práci s konfigurací OLAPu. Na internetu jsem hledal podobné příklady chytrých uložených procedur a nenašel. Nenašel jsem ani komerční knihovny, což mne trochu zarazilo. Trochu naivně doufám, že z příchodem MySQL5 se situace změní. Pokud byste věděli o nějaké pěkné a užitečné procedůře nebo balíku, dejte mi vědět.

Našel jsem ještě jednu hezkou a jednoduchou procedůrku, a to v [http://db4free.blogspot.com/2005/11/calculate-uniqueness-of-text-fields.html blogu] Markuse Poppa. MySQL umožňuje nastavit délku indexu. Není praktické indexovat řetězce v celé jejich délce, většinou nám dostatečnou míru selektivity už po prvních n (n <20) znacích. Jak ale zjistit optimální délku. Markus přichází s mírou, kterou nazývá jednoznačnost a spočítá ji, jako poměr mezi počtem jednoznačně identifikovatelných řádků, ku celkovému počtu řádků.
<pre>
SELECT count(distinct left(field_name, indexed_length)) / 
   count(*) * 100 FROM table_name;
</pre>
Markusův kód jsem opět mírně poopravil. Kód používá jednu dočasnou tabulku, které se vytváří v proceduře. Na závěr se provede select z této tabulky. Tato technika se často používá u Microsoft SQL Serveru. Je tu ovšem jeden drobný rozdíl. U MsSQL má dočasná tabulka omezenou životnost na transakci v které byla vytvořena. U MySQL nic takového neexistuje, takže procedura si musí zajistit, že použitá dočasná tabulka neexistuje a před svým koncem ji ještě zrušit.
<pre>
1  DELIMITER $$
2  SET sql_mode=ansi $$
3  DROP PROCEDURE IF EXISTS getUniqueness $$

4  CREATE PROCEDURE getUniqueness (IN _table VARCHAR(255),
5  	IN _column VARCHAR(255), IN _noFrom INT, IN _noTo INT)
6  BEGIN
7  	DROP TEMPORARY TABLE IF EXISTS tt_uniqueness;
8	CREATE TEMPORARY TABLE tt_uniqueness
9		(noChar int unsigned not null, uniqueness decimal(10,2) not null);

10	SET @sql = 'insert into tt_uniqueness ' 
11		|| 'select ?, cast(count(distinct left(' || _column || ', ?)) / count(*) * 100 as decimal(10,2)) from '
12		||  _table;
	
13	PREPARE pSql FROM @sql;
14	SET @count = _noFrom;
	
15	WHILE @count < _noTo DO
16		EXECUTE pSql USING @count, @count;
17		SET @count = @count + 1;
18	END WHILE;
	
19	SELECT * FROM tt_uniqueness;
20	DROP TEMPORARY TABLE tt_uniqueness;
21  END$$
22  DELIMITER ;
</pre>
O co tu jde. Opět používáme dynamický SQL příkaz, a to protože název tabulky nesmí být parametrizován v statickém SQL příkazu [10]. Vygenerovaný dynamický příkaz používá vázané proměnné - symbol ?, a fráze USING v příkazu EXECUTE [16]. Jazyk PSM (Persistent Stored Module) v MySQL neobsahuje cyklus FOR (ANSI SQL3/PSM jej obsahuje - pro iteraci nad tabulkou), musíme si vystačit s klasickým WHILE, END WHILE - je to vlastně první ukázka konstrukce cyklu [15]. Volným SELECTem vrátíme výsledek [19]. Mírně odbočím. MySQL umožňuje tzv. multiquery, tedy dovoluje vrácení několika tabulek z jednoho volání proceduru (znalí MSSQL a ADA - multirecordset). 
<pre>
mysql> CALL getUniqueness('phpbb_posts_text', 'post_text', 4, 10);
+--------+------------+
| noChar | uniqueness |
+--------+------------+
|      4 |      51.18 |
|      5 |      59.41 |
|      6 |      67.94 |
|      7 |      71.47 |
|      8 |      78.82 |
|      9 |      84.12 |
|     10 |      85.00 |
+--------+------------+
10 rows in set (0.03 sec)
</pre>

Jak jsem slíbil minule přidávám ukázku volání této procedury v Perlu (opět zkráceno o kontrolu chyb) prostřednictvím knihvny DBI.
<pre>
use strict; use DBI;
my $dbh->DBI->connect(' ', {RaiseError => 1, AutoCommit => 0});
my $sth = $dbh->prepare("call getUniqueness(?,?,?,?)");
$sth->bind_param(1, $tbname, $DBI::SQL_VARCHAR);
$sth->bind_param(2, $colname, $DBI::SQL_VARCHAR);
$sth->bind_param(3, $nofrom, $DBI::SQL_INTEGER);
$sth->bind_param(4, $noTo, $DBI::SQL_INTEGER);
$sth->execute();
$sth->finish();
$dbh->disconnect();
</pre>