Webstack
Bejelentkezés

Elfelejtetted a jelszavad?

Nem vagy még tag? Kattints ide és regisztrálj most!

MySQL tárolt eljárások - 1.rész

mySQL | tomizej  | 2012-10-02 10:26:34

Bevezetés

A MYSQL 5 sok új funkciót hozott. Köztük az egyik leghasznosabb a tárolt eljárások támogatása. A tárolt eljárás egy lehetőség, az ismétlődő folyamatok egységbe zárására.
A tárolt eljárások a nevéből adódóan az adatbázis motor szinten vannak tárolva. Meghívhatja program, trigger vagy akár egy másik eljárás is. Saját magát is hívhatja, ekkor rekurzív tárolt eljárásról beszélünk.

Előnyök

  • Az eljárásokat az adatbázis motor, létrehozáskor lefordítja és tárolja. Így ezek meghívása esetén gyorsabb a futási idő, mintha szimplán a megszokott módon alkalmazásokból küldenénk sql parancsokat (mivel ekkor minden lekérdezésnél le kell fordítani a queryt).
  • Kevesebb forgalmat jelent az adatbázis szerver és az alkalmazás között, mivel nem kell több 10 soros lekérdezéseket elküldeni, csak egy eljáráshívást, ami lehet egy bonyolult komplex folyamat.
  • Biztonságos, jogosultságokat az adatbázis adminisztrátora adhat, hogy mely alkalmazás futtathatja eljárásainkat.
  • A tárolt eljárások transzparensek(átlátszók) más alkalmazások számára. Mivel motor szinten van tárolva így különböző platformokon nem kell újra implementálni az adatbázis műveleteket.

Hátrányok

  • Erősen deklaratív nyelv, részben procedurális. Nem a legegyszerűbb nyelvek közé tartozik. Nehézkes megvalósítani benne komplexebb feladatokat.
  • Tárolt eljárásokat nem igazán van mód debuggolni, így egy komolyabb feladat megvalósításához átfogó tudás kell. A helyzeten még nehezít, ha eljárásból eljárást hívunk.

Tárolt eljárások írása

Tárolt eljárásokat már létrehozhatunk php myAdminban is (php myAdmin 5-től). Korábbi verziók még a régi php-mysql modult használták, amik nem támogatták rendesen a tárolt eljárásokat.
Az alábbi tárolt eljárás visszaadja az összes cikket az article táblából.

DELIMITER $$
CREATE PROCEDURE GetAllArticle()
  BEGIN
  SELECT * FROM article;
  END $$
DELIMITER ;

Részletezzük mit is csinál a fenti kód:

  1. A tárolt eljárás elején a DELIMITER $$ paranccsal meg kell változtatni az alapértelmezett záró karaktert pontos vesszőről, valamire (jelen esetben $$-ra). Ezek után több utasítást is megadhatunk a tárolt eljárásban. Ha ezt nem tennénk, a parancs feldolgozó megállna a SELECT * FROM products; (az első pontosveszző) végén és hibát dobna.
  2. A CREATE PROCEDURE GetAllArticle() paranccsal létrehozzuk az eljárást, megadjuk a nevét, mint ahogy más nyelvekben függvényt deklarálunk.
  3. A BEGIN és END kulcsszó pár közötti résszel határozhatjuk meg az eljárás törzsét, ez felfogható úgy, mint a C alapú nyelvekből ismert kapcsos zárójel „{ }”.
  4. Az END után a $$ jellel lezárjuk a parancsot
  5. A DELIMITER ; paranccsal visszaállítjuk az alapértelmezett delimitert pontosvesszőre.

Tárolt eljárások hívása

Tárolt eljárásokat hívhatunk az alábbi módon a CALL eljaras_neve() parancssal:

CALL GetAllArticle();

 

Ha PHP-vel szeretnénk hívni tárolt eljárást, ne használjuk a php-mysql modult, mivel nem megfelelően támogatja őket, több rekordos eredmény halmazt nem megfelelően ad vissza. Ajánlott tehát a mysqli (Mysql Improved Extension) használata, vagy PDO.

Példa MYSQLi-vel:

$mysqli = new mysqli("localhost", "teszt", "teszt", "teszt");
$rs = $mysqli->query("CALL GetAllArticle()");

Példa PDO-val:

$pdo = new PDO('mysql:dbname=teszt;host=127.0.0.1', 'teszt', 'teszt');
$rs = $pdo->query('CALL GetAllArticle()');

Tárolt eljárások listázása

Szép dolog, hogy tudunk tárolt eljárást írni, de meg is tudnunk jeleníteni őket. Az újabb PHPmyAdminban ez már egy használható funkció. A táblák listázásakor felajánl a rendszer nekünk egy menüpontot eljárások néven.

parancsból a következőképp lehet listázni:

SHOW PROCEDURE STATUS;

Szűkíteni is tudjuk a listát a LIKE szűréssel. Pl a „rendeles_” szóval kezdődő eljárások listája:

SHOW PROCEDURE STATUS LIKE 'rendeles_%';

Természetesen használhatunk szimplán WHERE feltételt is, ekkor azonban az aktuális procedúra propertyjére kell hivatkozni.

SHOW PROCEDURE STATUS WHERE Name LIKE 'create%';

Tárolt eljárások megjelenítése

A tárolt eljárásokat (CREATE kódját) meg tudunk jeleníteni a SHOW CREATE PROCEDURE eljaras_neve paraccsal.

SHOW CREATE PROCEDURE insert_order;

Tárolt eljárások módosítása, törlése

Tárolt eljárást a DROP PROCEDURE eljarasnév paranccsal törölhetünk.

DROP PROCEDURE create_order;

Módosításra egyelőre nincs mód. Módosítani csak úgy lehet, hogy töröljük az eljárást, majd újra létrehozzuk. Igaz létezik ALTER PROCEDURE parancs, de ezzel nem tudjuk módosítani a kódot, csak az eljárás tulajdonságait (comment, jogosultság típus…). Ha megnézzük a PHP myadmin eljárás listájában, ha törlés ikonra kattintunk, elnavigál egy szerkesztő felületre ahol az sql parancs első sorában az eljárást törli, majd újra létrehozza.

Változók

Deklarálás

Tárolt eljárásokban használhatunk változókat, átmeneti értékek tárolására. Az alábbi szintaktikával deklarálhatunk egy változót.
DECLARE változó_neve adattípus(méret) DEFAULT kezdő érték;

Ügyelnünk kell, a névadás konvencióra, változó neve nem lehet megegyező egy tábla nevével, illetve mező nevével. Adattípus megadásánál az összes adattípust használhatjuk amit a mysql támogat (INT, VARCHAR, DATE…stb). A változók értéke deklaráláskor null, kezdőértéket a DEFAULT kulcsszóval adhatunk nekik. Lássunk pár valós példát:

Egyszerre több azonos típusú változó is deklarálható egy paranccsal:

DECLARE i, j INT DEFAULT 0;

Kezdőértéket nem kötelező megadni, ez esetben az érték NULL lesz:

DECLARE nev VARCHAR(50);

Értékadás

Változóknak a SET paranccsal adhatunk értéket:

SET i = 10;
SET nev = 'Kis Pista';

Egy lekérdezés eredmény halmazát is értékül adhatjuk egy változónak a SELECT INTO paranccsal.

DECLARE atlagfizetes DOUBLE;
SELECT AVG(fizetes_ertek) INTO atlagfizetes 
FROM dolgozok

A fenti egy átfogóbb példa, amiben deklarálunk egy változót, majd egy SELECT eredményét értékül adjuk a változónak. A dolgozók átlagfizetését lekérdezzük az atlagfizetes változóba.

Session változók

Mysql adatbázis rendszerekben tárolhatunk session változókat. Ez értékei mindaddig megmaradnak, míg a Session nem jár le. Bárhol létrehozhatunk session változót.
Az alábbi paranccsal hozhatunk létre session változókat. A megkülönböztető jelzés a @ prefixum a változó neve előtt, ez a jel határozza meg, hogy session változótól van szó.

SET @svar = 1;

Az alábbi példában láthatjuk, hogyan tartja meg az értékét a session változó

CREATE PROCEDURE session_test ()
BEGIN
    DECLARE var2 INT DEFAULT 1;
    SET var2 := var2 + 1;
    SET @var2 := @var2 + 1;
    SELECT var2, @var2;
END;

SET @var2 = 1;

CALL session_test();

var2  @var2
---   ---
2     2


CALL session_test();

var2  @var2
---   ---
2     3

Láthatjuk, hogy a @var2 értéke minden hívásnál eggyel nő, nem veszti el a korábbi értékét, ellentétben a tárolt eljárásban deklarált var2 változóval, ami ugye minden meghívásnál megkapja a DEFAULT értéket az 1-et.

Hatókörök

Egy változó hatóköre az őt befogó BEGINEND parancs között tart. Deklarálhatunk több ugyanolyan nevű változót más hatókörökben.
A session változókat szuper globális változóknak tekintjük, bárhol elérhetőek és bárhol létrehozhatjuk őket, értéket adhatunk nekik.

Szerző: tomizej

címkék
Címkék: tutorial, mySQL, php,

Hozzászólások

Hozzászóláshoz be kell jelentkezni!

Keress minket Facebookon
Ajánlások