Tárolt eljárások és függvények MySQL-ben

Bevallom derekasan, MySQL-ben életemben nem írtam még tárolt eljárást. Vagyis írni mondjuk írtam, de nem használtam ki annak lehetőségeit. Tisztában vagyok a miértjével és a hogyanjával, de amikor öt éve írtam ezt a blogmotorszerűséget, akkor gőzöm nem volt még ilyenekről, mostanra pedig az a bajom, hogy sok helyen egyáltalán nem is engedik a dolgot.

A tárolt eljárások és a függvények rendkívül hasznos dolgok, többek között azért is, mert adott esetben nem rondítják a PHP-kódot a hat-nyolc-tíz soros hat táblát összekapcsoló négyféle JOIN-nal megtoldott lekérdezések, csak mondjuk egy sor. Nem is beszélve arról, hogy karbantartó műveleteknél sokkal biztonságosabb is. Ami pedig a megdönthetetlen érv, hogy ha módosul valami oknál fogva a lekérés, akkor azt az annak megfelelő helyen, az adatbázisban tudjuk megváltoztatni, nem pedig a PHP kódokban kotorászva.

Bizonyára mindenki számára ismeretes, hogyan lehet ilyeneket csinálni, de ha mégsem, akkor itt a módja. A phpMyAdminban kicsit macerás a kezelés (értsd: nincsen olyan felület mint például az SQL Server Management Studióban, hogy jobbklikk és látod minden tulajdonságát), így akiknek csak ez a felület van, azok a SHOW PROCEDURE|FUNCTION STATUS [LIKE '%enyem_valamim%]; kódot részesítsék majd előnyben.

Szintaktikailag máskülönben teljesen hasonló a legtöbb SQL-szerver nyelvezetéhez a MySQL-ben használatos módszer; itt van mondjuk egy függvény, amely az adott blogbejegyzéshez érkező kommentek számát adja eredményül:

CREATE FUNCTION postKommentekSzama(post_id INT)
RETURNS INT NOT DETERMINISTIC
RETURN (SELECT COUNT(komment.post_id) FROM komment WHERE komment.post_id = post_id);

Ha sikeresen lefutott, és nem panaszkodik semmire, akkor elvileg létrejött a dolog, a fent említett SHOW FUNCTION STATUS; kiadásával megkapjuk hogy van egy postKommentekSzama nevű függvényünk. Így lehet őt meghívni:

SELECT postKommentekSzama(1);

Ha pedig törölni szeretnénk, akkor ezt kell csinálni:

DROP FUNCTION IF EXISTS postKommentekSzama;

Megmondom őszintén, olyat nem találtam, hogy a MySQL-ben létezik-e a RETURNS TABLE, de ha valamilyen lekérdezést szeretnénk, ami eredményül egy táblát ad vissza, akkor az alábbi kód kell nekünk:

DROP PROCEDURE IF EXISTS blogbejegyzes;
DELIMITER $$
CREATE PROCEDURE blogbejegyzes(IN blog_id INT)
LANGUAGE SQL
READS SQL DATA
BEGIN
  START TRANSACTION;
    SELECT * FROM post AS post_result WHERE post_result.id = id;
  COMMIT;
END$$
DELIMITER ;

A DELIMITER szükséges, ugyanis ha nem változtatjuk meg az alapértelmezett pontosvesszőt, akkor a kód közben első helyen leírt pontosvesszőnél (itt a tranzakció eleje) meghal az egész.

Ellenben ha meg akarjuk hívni az eljárást:

CALL blogbejegyes(1);

Akkor az alábbi nagyon szexi hibaüzenet jön elő:

#1312 - PROCEDURE blogbejegyzes can't return a result set in the given context

Ez pedig legjobb tudomásom szerint azért van, mert a PHP-vel érkező MySQL modul, amit a phpMyAdmin is használ, sajnos képtelen megfelelően kezelni a tárolt eljárásokat. A megoldás az lehet, hogy másik modult használunk. A MySQL Improved Extension biztosan, de úgy tudom hogy a Pear::DB is jól kezeli a tárolt eljárásokat.

Illetve ha valaki tud jobbat, az persze jöhet.