Tárolt eljárások - ciklusok

A MySQL tárolt eljárásokban a ciklusokat az WHILE, REPEAT és LOOP utasításokkal valósíthatjuk meg. Ezek az utasítások lehetővé teszik, hogy egy adott kódrészletet többször is végrehajtsunk, amíg egy feltétel teljesül.

Részletes leírás az egyes ciklus típusokról

Az előltesztelő WHILE ciklus addig fut, amíg a megadott feltétel teljesül (vagyis igaz).

A WHILE ciklus szintaxisa:

WHILE feltétel DO
    -- utasítások
END WHILE;

A REPEAT ciklus legalább egyszer lefut, majd addig ismétli magát, amíg az UNTIL feltétel igazzá nem válik.

A REPEAT ciklus szintaxisa:

REPEAT
    -- utasítások
UNTIL feltétel;
END REPEAT;

A LOOP ciklus folyamatosan ismétlődik, amíg explicit módon (szándékosan, közvetlenül) meg nem szakítjuk. (A LEAVE utasítással lépünk ki a ciklusból.)

A LOOP ciklus szintaxisa:

LOOP_label: LOOP
    -- utasítások
    IF feltétel THEN
        LEAVE LOOP;
    END IF;
END LOOP LOOP_label;

A LOOP ciklus vezérlő kulcsszavai:

• LEAVE: Kilépés a ciklusból (hasonló a break utasításhoz)
• ITERATE: Ugrás a ciklus következő iterációjára (hasonló a continue utasításhoz)

Példa a ciklusok bemutatására:

Tegyük fel, hogy van egy szamok tábla, amelyben egész számokat tárolunk. Szeretnénk kiírni az első 5 számot.

CREATE TABLE szamok (id INT AUTO_INCREMENT, szam INT, PRIMARY KEY (id));

INSERT INTO szamok (szam) VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);

• A feladat megoldása WHILE ciklussal:

DELIMITER //
CREATE PROCEDURE while_pelda()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 5 DO
        SELECT szam FROM szamok WHERE id = i;
        SET i = i + 1;
    END WHILE;
END//
DELIMITER ;

CALL while_pelda();

• A feladat megoldása REPEAT ciklussal

DELIMITER //
CREATE PROCEDURE repeat_pelda()
BEGIN
    DECLARE i INT DEFAULT 1;
    REPEAT
        SELECT szam FROM szamok WHERE id = i;
        SET i = i + 1;
    UNTIL i > 5
    END REPEAT;
END//
DELIMITER ;

CALL repeat_pelda();

• A feladat megoldása LOOP ciklussal

DELIMITER //
CREATE PROCEDURE loop_pelda()
BEGIN
    DECLARE i INT DEFAULT 1;
    loop_label: LOOP
        SELECT szam FROM szamok WHERE id = i;
        SET i = i + 1;
        IF i > 5 THEN
            LEAVE loop_label;
        END IF;
    END LOOP;
END//
DELIMITER ;

CALL loop_pelda();

• Írassuk ki a szamok táblából a páros számokat:

DELIMITER //
CREATE PROCEDURE paros_szamok()
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE db INT;
    SELECT count(szam) INTO db FROM szamok;
    paros_label: LOOP
		SET i = i + 1;	
        -- Páratlan számok kihagyása
        IF (i % 2 = 1) THEN
            ITERATE paros_label;
        ELSE    
			SELECT szam FROM szamok WHERE id = i;
		END IF;
		--  Kilépés, ha elértük az utolsó rekordot
        IF i = db THEN
            LEAVE paros_label;
        END IF;
    END LOOP;
END//
DELIMITER ;

CALL paros_szamok();

• Vegyük a korábban már megismert students - subjects - grades táblákból álló adatbázist! Hozzunk létre egy tárolt eljárást, amely a grades táblában lévő összes jegyet összeadja és az eredményt egy változóban tárolja.

DELIMITER //

CREATE PROCEDURE SumGrades()
BEGIN
    DECLARE total INT DEFAULT 0;
    DECLARE done INT DEFAULT FALSE;
    DECLARE grade_cur INT;
    DECLARE grade_cursor CURSOR FOR SELECT grade FROM grades;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN grade_cursor;

    WHILE NOT done DO
        FETCH grade_cursor INTO grade_cur;
        IF NOT done THEN
            SET total = total + grade_cur;
        END IF;
    END WHILE;

    CLOSE grade_cursor;

    SELECT total AS total_sum;
END//

DELIMITER ;

CALL SumGrades();

A hagyományos SQL-parancsok (pl. SELECT) egyszerre dolgozzák fel az összes sort egy táblában.
Azonban előfordulnak olyan helyzetek, amikor egyenként kell feldolgozni a sorokat, például: amikor egy tábla sorait egyenként kell beolvasni, feldolgozni egy program vagy eljárás által vagy egy felhasználói kérésre kell visszaadni.

A kurzorok és a FETCH parancsok lehetővé teszik, hogy egyenként dolgozzunk fel minden sort, és így megoldhatjuk ezeket a problémákat.

• Kurzor (Cursor) A kurzor egy olyan mutató, amely egy sorozatban (pl. egy tábla soraiban) mozog, és lehetővé teszi, hogy egyenként dolgozzunk fel minden sort. A kurzor segítségével egyenként olvashatjuk be vagy írhatjuk ki a sorokat.
• Fetch A FETCH egy olyan parancs, amely a kurzort mozgatja a következő sorra, és beolvassa az aktuális sor adatait egy vagy több változóba.

A fenti tárolt eljárás magyarázata:

Változók deklarálása

• total: az osztályzatok összegét tárolja, alapértelmezett értéke 0.
• done: egy logikai változó, amely jelzi, hogy a kurzor végére értünk-e, alapértelmezett értéke FALSE.
• grade_cur: az aktuális osztályzatot tárolja.
• Kurzor deklarálása: A grade_cursor kurzor a grades tábla grade oszlopának értékeit olvassa be.
• Folytatás kezelő: A CONTINUE HANDLER FOR NOT FOUND utasítás beállítja a done változót TRUE-ra, ha a kurzor végére érünk.

Kurzor megnyitása: Az OPEN grade_cursor utasítás megnyitja a kurzort.

Ciklus a kurzoron

• A WHILE NOT done DO ciklus addig fut, amíg a kurzor végére nem érünk.
• A FETCH grade_cursor INTO grade_cursor utasítás beolvassa az aktuális osztályzatot a kurzorból.
• Ha nem értünk a kurzor végére, akkor hozzáadjuk az osztályzatot a total összeghez.

Kurzor bezárása: A CLOSE grade_cursor utasítás bezárja a kurzort.

Eredmény visszaadása: A SELECT total AS total_sum utasítás visszaadja az osztályzatok összegét.

Feladatok

1. Írj egy tárolt eljárást, amely kiszámítja az első 10 természetes szám összegét. Írd meg mind a 3 ciklus tipussal! (eljárások neve legyen: while10, repeat10, loop10)

2. Számítsuk ki az első n pozitív, páros egész szám összegét! Írd meg mind a 3 ciklus típussal! (Eljárások nevei: whileParos, repeatParos, loopParos)

A következő feladatokhoz a korábban már megismert students - subjects - grades táblákból álló adatbázist használd! Az sql a "JOIN, Nézet feladat"-nál található!

3. Hozz létre egy tárolt eljárást, amely a students táblában lévő diákok számát számolja össze egy WHILE ciklus segítségével. (StudentsCount)

4. Hozzon létre egy tárolt eljárást, amely a subjects táblában lévő tantárgyak számát számolja össze egy REPEAT ciklus segítségével. (SubjectsCount)

5. Hozz létre egy tárolt eljárást, amely a grades táblában lévő összes jegy átlagát számolja ki egy REPEAT ciklus segítségével. (GradeAvg)

6. Hozz létre egy tárolt eljárást, amely a students táblában lévő diákokat listázza ki egy LOOP ciklus segítségével. (StudentsList)

7. Hozz létre egy tárolt eljárást, amely a subjects táblában lévő tantárgyakat listázza ki egy LOOP ciklus segítségével. (SubjectsList)