Tárolt eljárások

Bevezetés a tárolt eljárásokba

A tárolt eljárások segítségével újrafelhasználható, ismétlődő feladatokat hozhatunk létre, és egyesíthetjük a metódusokat az adatok elérésére több alkalmazáson keresztül. Olyan összetett feladatokat valósíthatunk meg, amelyek meghaladják az egyes SQL-lekérdezések adta lehetőségeket.

A tárolt eljárás egy lehetőség, az ismétlődő folyamatok egységbe zárására. Kevesebb forgalmat jelent az adatbázis szerver és az alkalmazás között. Segítségével kiválthatjuk a több soros lekérdezéseket, hiszen tárolt eljárást használva csak egy eljáráshívást kell indítani, ami egy bonyolult komplex folyamat is lehet.

Egy vagy több utasítást tartalmaz, amit az adatbázis az eljárás meghívásakor, az eljárásban meghatározottak szerinti sorredben (utasításról utasításra) hajt végre.

A legalapvetőbb példa, hogy egy tárolt eljárás adatokat kérhet le az adatbázisból a gyakran használt szűrőkkel. Létrehozhatunk például egy tárolt eljárást az online áruház azon vásárlóinak lekérésére, akik a megadott napon rendeltek. A legösszetettebb feladatokban a tárolt eljárások kiterjedt programokat (vagy programrészeket) tartalmazhatnak, amelyek bonyolult üzleti logikát írnak le.

A tárolt eljárás utasításkészlete tartalmazhat:

Általános SQL utasításokat, például SELECT vagy INSERT lekérdezéseket, amelyek adatokat adnak vissza vagy módosítanak.
A tárolt eljárásnak átadott vagy azon keresztül visszaadott paramétereket.
Deklarált változókat a letöltött adatok közvetlenül az eljáráskódon belüli feldolgozásához.
Feltételes utasításokat, amelyek lehetővé teszik a tárolt eljáráskód részeinek végrehajtását bizonyos feltételektől függően, például az IF vagy a CASE utasításoktól.
Hurkokat. A WHILE, LOOP és REPEAT lehetővé teszik a kód egyes részeinek többszöri végrehajtását.
Hibakezelési utasításokat, például hibaüzenetek visszaküldése az eljáráshoz hozzáférő adatbázis-felhasználóknak.
Az adatbázisban tárolt egyéb eljárások hívását.

A tárolt eljárások közvetlenül az adatbázis-kiszolgálón futnak le, minden számítást helyben hajtanak végre, és csak akkor küldik vissza az eredményeket a felhasználónak, ha befejezték.

Ha módosítani szeretnénk az eljárás viselkedését, frissíthetjük az eljárást az adatbázisban, és az azt használó alkalmazások automatikusan átveszik az új verziót. Minden felhasználó azonnal tudja használni az új eljárási kódot anélkül, hogy módosítania kellene az alkalmazását.

Tárolt eljárás előnyei:

Gyorsítja az adatbázis kezelését: Az eljárás tárolása adatbázis szintjén történik, így gyorsabb a hozzáférés és az értelmezés.
Kevesebb az adatforgalom: A klienstől csak az eljárás nevét (és paramétereit) kell elküldeni, nem egy komplett feladatsort.
Egyszerűbbek a lekérdezések: Saját függvényeket alkothatunk, amelyeket a beépített függvényekhez hasonlóan tudunk használni.
Platformfüggetlen algoritmusok: Az algoritmus adatbázis szinten tárolódik, így bármilyen nyelvből meghívható.

Szintaktika

Eljárások / függvények létrehozása:

    CREATE PROCEDURE eljárásNeve ( [paraméterek] )
        BEGIN
            utasítások
        END

    CREATE FUNCTION függvényNeve ( [paraméterek] )
        RETURNS típus
        BEGIN
            utasítások
            RETURN visszatérésiÉrték
        END

Eljárások / függvények használata, meghívása:

    CALL eljárásNeve ( [paraméterek] );

    SELECT függvényNeve ( [paraméterek] );

Eljárások, függvények törlése:
Előfordulhat, hogy a létrehozott eljárásra már nincs szükség vagy változtatni szeretnénk az eljárás működésén. A MySQL nem teszi lehetővé az eljárás módosítását, így ennek egyetlen módja az, hogy először töröljük az eljárást, és újra létrehozzuk a kívánt változtatásokkal.

    DROP PROCEDURE eljárásNeve;
    DROP FUNCTION függvényNeve;

A tárolt eljárások lehetőséget adnak arra, hogy bennük kisebb / nagyobb programrészeket valósítsunk meg. Ebben az esetben előfordul, hogy szükségünk van változók létrehozására és értékadására is. Ezt a következő módon tehetjük meg:

Változók létrehozása:
A változó érvényességi köre az eljáráson / függvényen belülre terjed ki.

    DECLARE változóNév típus [ DEFAULT érték ]

Értékadás:
Csak deklarálás után lehetséges!

    SET változóNév = kifejezés
    SELECT attribútum [ INTO változóNév FROM ... ]

A tárolt eljárás létrehozásához használt általános SQL-kód:

    DELIMITER //
    CREATE PROCEDURE eljárásNév(paraméter_1, paraméter_2, . . ., paraméter_n)
    BEGIN
        utasítás_1;
        utasítás_2;
        . . .
        utasítás_n;
    END //
    DELIMITER ;

A tárolt eljárás kódja 3 részből épűl fel:

Az új eljárás létrehozása a CREATE PROCEDURE-val kezdődik, amelyet az eljárás neve követ (esetünkben: eljárásNév).
Az eljárás nevét az eljárás által elfogadott paraméterek opcionális listája követi.
Az utolsó rész az eljárás törzse, melyet a BEGIN és az END utasítások közé helyezünk el.

A MySQL pontosvesszőt használ az utasítások elválasztására. Ha több utasítást hajt végre, akkor azokat külön parancsként kezeli a rendszer, és egymástól függetlenül, egymás után hajtja végre. A tárolt eljárás azonban több parancsot is tartalmazhat, amelyek egymás után kerülnek végrehajtásra, amikor meghívásra kerül. Ebben az esetben mi azt szeretnénk hogy a MySQL a tárolt eljárásunkat egy utasításnak vegye és ne állítsa le az utasítás végrehajtását amikor az eljáráson belül találkozik egy pontosvesszővel.

A probléma megoldásaként használjuk a DELIMITER parancsot, amellyel ideiglenesen módosíthatjuk a határolót, ; értékről jelen esetben //-re. (A ;-t bármire kicserélhetjük.) Ezután a tárolt eljárástörzsben (BEGIN ... END közötti rész) lévő összes pontosvessző átadásra kerül a kiszolgálónak. Az END parancs a szokásos pontosvessző helyett a //-re, vagyis az általunk megadott ideiglenes határolóra végződik. A teljes eljárás befejezése után a határolót visszaállítjuk a ;-re az utolsó DELIMITER ; utasítással.

Mi a különbség a tárolt eljárás (procedure) és a tárolt függvény (function) között?

Tárolt eljárás:

Visszatérési érték: Nem kötelező, de OUT paraméterekkel több értéket is visszaadhat.
Használat: CALL eljaras_nev();
Környezet: Nem használható közvetlenül SQL kifejezésekben.
Adatmódosítás: Módosíthat, beszúrhat, törölhet adatokat (INSERT, UPDATE, DELETE).

Tárolt függvény:

Visszatérési érték: Kötelezően egy értéket ad vissza (RETURN).
Használat: SELECT fuggveny_nev();
Környezet: Használható SELECT, WHERE, HAVING záradékokban.
Adatmódosítás: Általában nem módosíthat adatot (csak olvas).

Összességében a függvényeket számításokra, míg az eljárásokat összetett adatbázis-műveletek végrehajtására érdemes használni.

Feladat:

1). Hozza létre az autok táblát, mely a következő mezőket tartalmazza:

marka: Az egyes tulajdonban lévő autók márkája, a varchar adattípus használatával kifejezve, legfeljebb 100 karakterrel.
modell: Az autómodell nevét tartalmazza, a varchar adattípussal kifejezve, legfeljebb 100 karakterrel.
ev: Az autó gyártási évét tárolja int adattípussal.
ertek: Az autó értékét tárolja a decimális adattípus használatával, legfeljebb 10 számjegyen tárolva, melyből 2 számjegy a tizedesekre van fenntartva.

2). Ezt követően töltse fel az autok táblát a következő mintaadatokkal:

marka	modell	ev	ertek
Porsche	911 GT3	2020	16970000
Porsche	Cayman GT4	2018	11800000
Porsche	Panamera	2022	11320000
Porsche	Macan	2019	2740000
Porsche	718 Boxster	2017	4888000
Ferrari	488 GTB	2015	25475000
Ferrari	F8 Tributo	2019	37500000
Ferrari	SF90 Stradale	2020	62700000
Ferrari	812 Superfast	2017	33530000
Ferrari	GTC4Lusso	2016	26800000

3). Tárolt eljárás létrehozása paraméterek nélkül.
Hozzon létre egy get_all_cars nevű tárolt eljárást, amely egyetlen SQL SELECT utasítást foglal magába. A tárolt eljárás visszaadja a tulajdonában lévő autók listáját márkájuk és értékük szerint csökkenő sorrendben.

A feladat megoldására szolgáló SELECT utasítás:

    SELECT * FROM autok ORDER BY marka, ertek DESC;

Feltételezzük, hogy ezt a lekérdezést gyakran használjuk több alkalmazásban vagy több felhasználónak is biztosítani szeretnénk ugyanazt az eredményt, ezért létre kell hozni egy tárolt eljárást.

A feladat megoldására a következő kódrészlet szolgál:

    DELIMITER //
    CREATE PROCEDURE get_all_cars()
    BEGIN
        SELECT * FROM autok ORDER BY marka, ertek DESC;
    END //
    DELIMITER ;

A get_all_cars eljárást lefuttatva el tudjuk menteni az adatbázisunkba.

alt text

A mentett tárolt eljárások végrehajtásához használhatja a CALL SQL parancsot, amelyet az eljárás neve követ.

    CALL get_all_cars;

A fenti utasításból látszódik, hogy az eljárás használatához mindössze az eljárásnévre van szükség. Nem kell beírni a korábban használt SELECT utasítás egyetlen részét sem.

A tárolt eljárás eredménye:

marka	modell	ev	ertek
Ferrari	SF90 Stradale	2020	62700000.00
Ferrari	F8 Tributo	2019	37500000.00
Ferrari	812 Superfast	2017	33530000.00
Ferrari	GTC4Lusso	2016	26800000.00
Ferrari	488 GTB	2015	25475000.00
Porsche	911 GT3	2020	16970000.00
Porsche	Cayman GT4	2018	11800000.00
Porsche	Panamera	2022	11320000.00
Porsche	718 Boxster	2017	4888000.00
Porsche	Macan	2019	2740000.00

4). Tárolt eljárás létrehozása bemeneti paraméterrel.
Hozzunk létre egy get_cars_by_year nevű eljárást az adott gyártási évből származó autók megkeresésére. (Ennek lehetővé tételéhez meg kell határoznia egy paramétert az eljárás definíciójában.) Kérje le a tárolt eljárás segítségével a 2017-es évjáratú autókat!

A korábban létrehozott get_all_cars tárolt eljárás mindig lekérte az összes autót az autok táblából. Hozzunk létre egy másik eljárást az adott gyártási évből származó autók megkeresésére. Ennek lehetővé tételéhez meg kell határozni egy paramétert az eljárás definíciójában.

Futtassuk le a következő kódot:

    DELIMITER //
    CREATE PROCEDURE get_cars_by_year(IN ev_filter int)
    BEGIN
        SELECT * FROM autok WHERE ev = ev_filter ORDER BY marka, ertek DESC;
    END //
    DELIMITER ;

A korábban üres zárójelek most egy paraméterdefiníciót tartalmaznak. Az IN kulcsszó közli az adatbázissal, hogy a paramétert a hívó felhasználó beadja az eljárásba. Az ev_filter a paraméter tetszőleges neve. Ezzel az eljáráskódban szereplő paraméterre hivatkozhatunk. Végül az int az adattípus. Ebben az esetben a gyártási év számértékként van kifejezve.

Az eljárás neve után meghatározott ev_filter paraméter megjelenik a SELECT utasításban a WHERE ev = ev_filter záradékban. Ezúttal a tárolt eljárás elvárja egy paraméter megadását. Tárolt eljárás paraméterekkel történő meghívásához zárójelben megadhatjuk a paraméterértékeket az eljárás által elvárt sorrendben. A 2017-ben gyártott autók lekéréséhez hajtsa végre a következőt:

    CALL get_cars_by_year(2017);

A hívott eljárás visszaadja az adott év autóinak listáját:

marka	modell	ev	ertek
Ferrari	812 Superfast	2017	33530000.00
Porsche	718 Boxster	2017	4888000.00

5). Tárolt eljárás létrehozása bemeneti és kimeneti paraméterekkel
Hozzon létre egy get_car_stats_by_year eljárást, amely összefoglaló adatokat ad vissza az adott gyártási év autóiról kimeneti paraméterek használatával. A tárolt eljárás adja vissza az adott gyártási évre a gyűjtemény legolcsóbb és legdrágább autóit, valamint az összes megfelelő autó átlagárát.

Mindkét előző példában a létrehozott tárolt eljárásoknak egy SELECT utasítás az eredménykészlete. Bizonyos esetekben azonban szükség lehet egy tárolt eljárásra, amely több különböző értéket ad vissza ahelyett, hogy egy egyedi lekérdezéshez egyetlen eredménykészletet adna meg.

Tegyük fel, hogy egy olyan eljárást szeretnénk létrehozni, amely összefoglaló információkat ad az adott év autóiról, beleértve a gyűjteményben lévő autók mennyiségét és piaci értékét (minimum, maximum és átlag).

Ehhez használhatjuk az OUT paramétereket egy új tárolt eljárás létrehozásakor. Az IN paraméterekhez hasonlóan az OUT paraméterekhez nevek és adattípusok vannak társítva. Azonban ahelyett, hogy adatokat adnának át a tárolt eljárásnak, ezek a paraméterek a tárolt eljárással feltölthetők adatokkal, vagyis értékeket adnak vissza a hívó felhasználónak.

A feladatot megvalósító tárolt eljárás:

    DELIMITER //
    CREATE PROCEDURE get_car_stats_by_year(
        IN ev_filter int,
        OUT auto_db int,
        OUT minimum decimal(10, 2),
        OUT atlag decimal(10, 2),
        OUT maximum decimal(10, 2))
    BEGIN
        SELECT COUNT(*), MIN(ertek), AVG(ertek), MAX(ertek)
        INTO auto_db, minimum, atlag, maximum
        FROM autok
        WHERE ev = ev_filter ORDER BY marka, ertek DESC;
    END //
    DELIMITER ;

A feladatban az autók gyártási év szerinti szűrésére használt IN year_filter paraméter mellett négy OUT paraméter van meghatározva a zárójelben lévő blokkban.

A auto_db paraméter int adattípussal van ábrázolva, és a gyűjteményben lévő autók számának visszaadására szolgál.
A min_value, avg_value és max_value paraméterek piaci értéket képviselnek, és decimal(10, 2) típusúak (hasonlóan az autok táblázat ertek oszlopához). Ezek segítségével visszaadják a gyűjtemény legolcsóbb és legdrágább autóit, valamint az összes megfelelő autó átlagárát.

A SELECT utasítás négy értéket kérdez le az autok táblából SQL matematikai függvények segítségével: COUNT az autók teljes számának lekéréséhez, és MIN , AVG és MAX, hogy minimális, átlagos és maximális értéket kapjon a value oszlopból.

Annak érdekében, hogy az adatbázis megmondja, hogy a lekérdezés eredményeit a tárolt eljárás kimeneti paramétereiben kell tárolni, egy új kulcsszó, az INTO kerül bevezetésre. Az INTO kulcsszó után négy eljárási paraméter neve jelenik meg, amelyek megfelelnek a visszakeresett adatoknak. Ezzel a MySQL elmenti a COUNT()* értéket az auto_db paraméterbe, a MIN(ertek) eredményt a minimum paraméterbe, és így tovább.

Futtassa le az új eljárást a következő módon:

    CALL get_car_stats_by_year(2017, @number, @min, @avg, @max);

A négy új paraméter @ jellel kezdődik. Ezek a MySQL-konzol helyi változónevei, amelyek segítségével ideiglenesen tárolhatunk adatokat. Amikor átadja ezeket az éppen létrehozott tárolt eljárásnak, az eljárás értékeket szúr be ezekbe a változókba.

Ez eltér a korábbi viselkedéstől, amikor az eredmények azonnal megjelennek a képernyőn. Ennek az az oka, hogy a tárolt eljárás eredményeit a rendszer kimeneti paraméterekbe menti, és nem adja vissza lekérdezés eredményeként.

Az eredmények lekérdezésére a következő utasítás szolgál:

    SELECT @number, @min, @avg, @max;

Ezzel a lekérdezéssel nem hívjuk meg újra az eljárást, a helyi változókból választ ki értékeket. A tárolt eljárás ezekbe a változókban mentette el eredményeit, és az adatok elérhetők maradnak mindaddig, amíg le nem csatlakozik a shellről.

A kimenet megjeleníti a lekérdezett változók értékeit:

@number	@min	@avg	@max
2	4888000.00	19209000.00	33530000.00

6). Tárolt függvény létrehozása
Hozzon létre egy avg_price_by_brand függvényt, amely kiszámítja egy adott márkához tartozó autók átlagárát.

A függvény létrehozásánál meg kell határozni egy paramétert az eljárás definíciójában, valamint meg kell adni hogy visszatérési értéknek milyen típusú adatot várunk.

A feladatot megvalósító tárolt függvény:

DELIMITER $
CREATE FUNCTION avg_price_by_brand(brand VARCHAR(50))
RETURNS DECIMAL(10, 2)
BEGIN
  DECLARE avg_price DECIMAL(10, 2);
  SELECT AVG(ertek) INTO avg_price
  FROM autok
  WHERE marka = brand;
  RETURN avg_price;
END $
DELIMITER ;

A függvény az autok táblából kéri le az adatokat, ahol a marka oszlop tartalmazza a márkaneveket, az ertek oszlop pedig az autók árait. A lekérdezi a paraméterként megadott típusú adatokat veszi és azoknak számítja ki az átlagát, amit INTO -val átad az avg_price változónak, majd az ebben a változóban eltárolt átlagértéket adja vissza a függvény.

Az eredmények lekérdezésére a következő utasítás szolgál:

SELECT avg_price_by_brand('Porsche') AS porsche_avg_price;  -- Eredmény: 9543600.00

SELECT avg_price_by_brand('Ferrari') AS ferrari_avg_price;  -- Eredmény: 37201000.00

7). Tárolt eljárások / függvények eltávolítása
Törölje a korábban létrehozott tárolt eljárásokat! (get_all_cars, get_cars_by_year, get_car_stats_by_year)

Előfordulhat, hogy a létrehozott eljárásra már nincs szükség. Más esetekben érdemes lehet változtatni az eljárás működésén. A MySQL nem teszi lehetővé az eljárás definíciójának módosítását a létrehozás után, így ennek egyetlen módja az, hogy először eltávolítjuk az eljárást, és újra létrehozzuk a kívánt változtatásokkal.

Távolítsuk el az előző feladatokba létrehozott tárolt eljárásokat és függvényt. Ehhez használjuk a DROP PROCEDURE és DROP FUNCTION utasításokat:

    DROP PROCEDURE get_all_cars;
    DROP PROCEDURE get_cars_by_year;
    DROP PROCEDURE get_car_stats_by_year;
    DROP FUNCTION avg_price_by_brand;

Források: https://www.mysqltutorial.org; https://hu.linux-console.net; https://dev.mysql.com/doc/mysql-tutorial-excerpt/8.0/en/; https://www.geeksforgeeks.org/mysql-tutorial/; https://www.w3schools.com/mysql/;