Otrzymałem ostatnio maila od jednego z czytelników, który dopytuje się w jaki sposób można przeiterować po danych w tabeli – chodzi o wykonanie operacji typu:

FOR rekord IN tabela.kolumna DO cokolwiek

Czytelnik ten zauważył, że w przypadku funkcji i procedur składowanych w MySQL nie ma możliwości zastosowania pętli FOR. Faktycznie, tak jest w istocie – tego typu składnia nie istnieje. Można natomiast zrobić coś innego – możemy wykorzystać kursor.

Załóżmy, że mamy tabelę zawierającą następujące dane:

mysql> SHOW CREATE TABLE tabela\G
*************************** 1. row ***************************
Table: tabela
Create Table: CREATE TABLE `tabela` (
`id` int(11) DEFAULT NULL,
`a` varchar(50) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin2
1 row in set (0,07 sec)

mysql> SELECT * FROM tabela;
+------+------+
| id   | a    |
+------+------+
|    4 | AAAA |
|    2 | TTT  |
|    5 | BBBB |
|    1 | WWW  |
+------+------+
4 rows in set (0,00 sec)

Przykładowa procedura może wyglądać w sposób następujący.

DROP PROCEDURE IF EXISTS proc1;
DELIMITER //

CREATE PROCEDURE proc1 ()

BEGIN

DECLARE done INT DEFAULT 0;
DECLARE vid INT;
DECLARE va VARCHAR(50);
DECLARE cur1 CURSOR FOR SELECT id FROM tabela;
DECLARE cur2 CURSOR FOR SELECT a FROM tabela;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

SELECT 'Teraz dla kazdego rekordu w kolumnie id wypisujemy wartosc tego rekordu:' AS a;
OPEN cur1;

WHILE done=0 DO

FETCH cur1 INTO vid;
SELECT vid;

END WHILE;
CLOSE cur1;

SELECT 'A teraz powtarzamy ta operacje dla kazdego rekordu w kolumnie a:' AS a;

SET done=0;

OPEN cur2;
WHILE done=0 DO
FETCH cur2 INTO va;
SELECT va;
END WHILE;
CLOSE cur2;
END//

DELIMITER ;

O co tu chodzi? Przyglądnijmy się krok po kroku:

DROP PROCEDURE IF EXISTS proc1;

Jeśli istnieje procedura o nazwie proc1, usuwamy ją.

DELIMITER //

Ustawiamy delimiter na znak ‘//’ – jest to nam potrzebne do tego, aby blok CREATE PROCEDURE został wykonany przez MySQL jako całość – w obrębie tego bloku będziemy stosowali średniki, bez zmiany delimitera MySQL zacznie protestować przy pierwszym z nich.

CREATE PROCEDURE proc1 ()

Rozpoczynamy tworzenie procedury o nazwie proc1. Procedura ta nie będzie przyjmować żadnych parametrów.

DECLARE done INT DEFAULT 0;
DECLARE vid INT;
DECLARE va VARCHAR(50);
DECLARE cur1 CURSOR FOR SELECT id FROM tabela;
DECLARE cur2 CURSOR FOR SELECT a FROM tabela;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

Trzy pierwsze linie to deklaracja zmiennych ‘done’, ‘vid’ i ‘va’. Pierwsza będzie nam potrzebna do zamknięcia pętli odczytywania danych z kursora, pozostałe dwie wykorzystamy do przechowywania danych odczytanych przez kursor. Co istotne, zmienna done domyślnie przyjmować będzie wartość 0.

Dwie kolejne linie to deklaracja dwóch kursorów. Pierwszy z nich ma nazwę cur1 i operować będzie na wyniku zapytania SELECT id FROM tabela;. Drugi to cur2 i operować będzie na wyniku SELECT a from tabela;.

Ostatnia linia to deklaracja handlera dla wyjątku ‘NOT FOUND’. Kursor będzie operował na wyniku jakiegoś zapytania. Handler definiuje nam co powinno się stać, jeśli wystąpi jakiś wyjątek w trakcie tych operacji. W tym konkretnym wypadku chcemy, aby w momencie, gdy kursor nie jest w stanie pobrać kolejnego rekordu (bo dotarł już do ostatniego rekordu w wyniku zapytania), zmienna done została ustawiona na 1.

SELECT 'Teraz dla kazdego rekordu w kolumnie id wypisujemy wartosc tego rekordu:' AS a;
OPEN cur1;

WHILE done=0 DO

FETCH cur1 INTO vid;
SELECT vid;

END WHILE;
CLOSE cur1;

SELECT to oczywiście tylko komentarz pojawiający się podczas uruchamiania procedury.

Następnie mamy to, co najważniejsze. Otwieramy kursor o nazwie cur1.
Rozpoczynamy pętlę WHILE. Dopóki zmienna done przyjmuje wartość 0, wykonujemy zawartość pętli. Przyglądnijmy się dokładniej operacjom wewnątrz pętli:

FETCH cur1 INTO vid;
SELECT vid;

Pobieramy wynik z cursora cur1 do zmiennej vid, następnie wypisujemy zawartość zmiennej vid.

Co się dzieje w praktyce? Kursor cur1 działa w oparciu o zapytanie:

mysql> SELECT id FROM tabela;
+------+
| id   |
+------+
|    4 |
|    2 |
|    5 |
|    1 |
+------+
4 rows in set (0,00 sec)

Pierwszą wartością jest wartość 4. Jest ona pobierana do zmiennej vid i wypisywana przy pomocy SELECT’a. Zmienna done ma nadal wartość 0, tak więc pobierana jest kolejny rekord z wyniku zapytania (2), przypisywany jest do zmiennej vid, a następnie zmienna ta zostaje wypisana. Całość powtarza się do momentu, gdy dochodzimy do ostatniego elementu w zestawie (1). Wartość ta jest pobierana, przypisywana do zmiennej vid, wypisywana. Podczas kolejnej próby pobrania wyniku pojawia się błąd NOT FOUND, który obsługiwany jest tak, że zmienna done przyjmuje wartość 1. To powoduje, że pętla WHILE kończy się.
Na sam koniec zamykamy kursor cur1.

Pozostała część procedury działa identycznie:

SELECT 'A teraz powtarzamy ta operacje dla kazdego rekordu w kolumnie a:' AS a;

SET done=0;

OPEN cur2;
WHILE done=0 DO
FETCH cur2 INTO va;
SELECT va;
END WHILE;

CLOSE cur2;
END//

DELIMITER ;

Różnicą jest to, że działamy na kursorze cur2, a także to, że zanim przejdziemy do kolejnej pętli musimy ustawić zmienną done z powrotem na 0.

Całość procedury kończymy przy pomocy END// (stosując nowo zdefiniowany delimiter), następnie ustawiamy delimiter na tradycyjny średnik.

Poniżej przykład innej procedury, która operuje na tej samej tabeli. Tym razem nie będę się już wgłębiał w szczegóły.

DROP PROCEDURE IF EXISTS proc2;
DELIMITER //

CREATE PROCEDURE proc2 ()

BEGIN

DECLARE done INT DEFAULT 0;
DECLARE vid INT;
DECLARE va VARCHAR(50);
DECLARE cur1 CURSOR FOR SELECT id FROM tabela;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;



SELECT 'Teraz dla kazdego id wypisujemy odpowiadajace mu a, a takze id zwiekszone o 2:' AS a;
OPEN cur1;

WHILE done=0 DO

FETCH cur1 INTO vid;
SELECT a, vid + 2 FROM tabela WHERE id = vid;

END WHILE;
CLOSE cur1;

END//

DELIMITER ;

Zastosowanie kursorów pozwala na zastosowanie trochę bardziej skomplikowanych algorytmów. Pamiętajcie tylko, drodzy czytelnicy, że język stosowany w funkcjach i procedurach składowanych w MySQL w żadnym wypadku nie może się równać z PL/SQL. Jeśli chcemy wykonać bardziej skomplikowane operacje, to może się okazać to po prostu niewykonalne. Ewentualnie wymagać będzie zastosowania niewydajnych algorytmów, hacków właściwie. To jest coś, z czym się trzeba pogodzić.

Druga kwestia, jeśli już jesteśmy przy procedurach i funkcjach składowanych – testujcie ich wydajność, drodzy czytelnicy, bardzo dokładnie. Analiza działania i wydajności większych funkcji jest trudna i czasochłonna.