Crearea cursorilor
Parcurgerea rezultatului unei interogari poate fi realizata la nivelul procedurilor stocate prin intermediul cursorilor. Cursorii ofera un mecanism eficient de identificare a interogarilor si iterare a inregistrarilor acestora.
In sistemele de tip MySQL, cursorii sunt structuri de tip read-only, adica nu ofera posibilitatea actualizarii datelor din tabelele de baza prin intermediul lor. Parcurgerea inregistrarilor de la nivelul unui cursor poate fi realizata doar in ordinea stabilita in interogare, intr-o singura directie si fara salturi. Din acest punct de vedere, cursorii sunt structuri de tip non-scrollable. In afara acestor doua proprietati, read-only si non-scrollable, cursorii din cadrul sistemelor de tip MySQL beneficiaza si de o a treia proprietate, sunt structuri de tip asensitive, care refera datele de la nivelul tabelelor de baza si nu copii temporare ale acestor date.
Crearea unei structuri de tip cursor se realizeaza prin intermediul instructiunii DECLARE, astfel:
DECLARE nume_cursor CURSOR FOR instructiune_select
Cursorii trebuie declarati inainte de utilizare. La nivelul declaratiei este stabilit un nume pentru cursor, nume caruia i se asociaza o definitie a unei interogari. In cadrul acestui proces nu sunt colectate date de la nivelul tabelelor referite.
DECLARE film_cursor CURSOR FOR SELECT film_id, title FROM film WHERE title LIKE CONCAT('%', film_title, '%');
In exemplul precedent, poate fi observata declaratia cursorului film_cursor, dar si interogarea asociata acestuia. Interogarea are in vedere extragerea valorilor corespunzatoare coloanelor film_id si title ale inregistrarilor din tabelul film al bazei de date sakila pentru care titlul contine valoarea transmisa prin intermediul variabilei film_title.
O procedura stocata poate contine mai multe declaratii ale unor cursori, atata timp cat acestia prezinta denumiri diferite.
Deschiderea si inchiderea cursorilor
Utilizarea unui cursor are in vedere realizarea urmatoarelor operatii: deschiderea cursorului, citirea de inregistrari de la nivelul cursorului, respectiv inchiderea cursorului.
Dupa declararea unui cursor, acesta poate fi deschis prin intermediul instructiunii OPEN.
OPEN film_cursor;
Deschiderea cursorului implica executia interogarii asociate acestuia si obtinerea inregistrarilor din rezultat. Pentru cursorul declarat anterior, operatia de deschidere presupune precizarea numelui cursorului, film_cursor, intr-o instructiune OPEN.
Inchiderea explicita a unui cursor deschis se realizeaza la nivelul unei instructiuni de tip CLOSE. Aceasta operatie are in vederea dezactivarea cursorului si eliberarea memoriei asociate acestuia.
CLOSE film_cursor;
Daca se incearca inchiderea unui cursor care nu a fost deschis anterior, este generata o eroare. Inchiderea unui cursor se realizeaza automat la finalul unui bloc de tip BEGIN … END, daca nu a fost solicitata inchiderea explicita a acestuia.
Utilizarea datelor de la nivelul cursorilor
Inregistrarile de la nivelul rezultatului interogarii asociate unui cursor pot fi parcurse, una cate una, prin intermediul instructiunii FETCH. In cadrul sistemelor de tip MySQL, instructiunea FETCH prezinta urmatoarea sintaxa generala:
FETCH [[NEXT] FROM] nume_cursor INTO nume_variabila [, nume_variabila] ...
Daca sunt prezente inregistrari la nivelul rezultatului interogarii, executia instructiunii FETCH determina stocarea valorilor coloanelor in variabilele precizate dupa clauza INTO. Numarul de coloane precizate in interogarea asociata unui cursor trebuie sa corespunda cu numarul de variabile precizate la apelul instructiunii FETCH.
In momentul in care nu mai sunt disponibile inregistrari din rezultat, este generata o conditie care precizeaza acest aspect. O astfel de situatie poate fi gestionata prin intermediul unui obiect handler de tip NOT FOUND. Declaratia obiectului handler de tip NOT FOUND se realizeaza astfel:
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
Se poate observa ca la nivelul acestei declaratii este actualizata si valoarea variabilei done, variabila care initial primeste valoarea 0 si care indica faptul ca structura de tip cursor a ajuns la finalul rezultatului interogarii asociate.
DECLARE done INTEGER DEFAULT 0;
USE sakila; | |
DROP PROCEDURE IF EXISTS get_film_actors; | |
DELIMITER && | |
CREATE PROCEDURE get_film_actors(IN film_title VARCHAR(128)) | |
BEGIN | |
DECLARE done INTEGER DEFAULT 0; | |
DECLARE current_film_id INT; | |
DECLARE current_title VARCHAR(128) DEFAULT ''; | |
DECLARE film_cursor CURSOR FOR | |
SELECT film_id, title | |
FROM film | |
WHERE title LIKE CONCAT('%', film_title, '%'); | |
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; | |
OPEN film_cursor; | |
get_actors: LOOP | |
FETCH film_cursor INTO current_film_id, current_title; | |
IF done = 1 THEN | |
LEAVE get_actors; | |
END IF; | |
SELECT current_title; | |
SELECT first_name, last_name | |
FROM actor INNER JOIN film_actor USING(actor_id) | |
INNER JOIN film USING(film_id) | |
WHERE film_id = current_film_id; | |
END LOOP; | |
CLOSE film_cursor; | |
END && | |
DELIMITER ; |
Procedura stocata get_film_actors este definita la nivelul bazei de date sakila pentru a afisa numele actorilor pentru toate filmele care contin in titlu sirul de caractere furnizat ca si parametru de intrare. Valoarea corespunzatoare acestui parametru se utilizeaza la nivelul interogarii asociate cursorului, intr-o expresie care permite filtrare inregistrarilor (clauza WHERE).
Dupa deschiderea cursorului, si executia interogarii asociate acestuia, are loc parcurgerea inregistrarilor din rezultat prin intermediul unei structuri iterative de tip LOOP (get_actors). Aceasta structura contine in prima linie instructiunea FETCH, care preia de la nivelul cursorului film_cursor datele corespunzatoare fiecarei inregistrari. In acest caz, din cursor sunt preluate valorile corespunzatoare coloanelor film_id si title, valori care sunt stocate in variabilele current_film_id, respectiv current_title.
FETCH film_cursor INTO current_film_id, current_title;
La nivelul structurii de control iterative (LOOP) cele doua variabile sunt utilizate in doua instructiuni SELECT. Prima instructiune SELECT afiseaza titlul filmului curent. Variabila current_film_id este utilizata in cea de-a doua instructiune SELECT, operatia de tip JOIN, pentru a obtine numele si prenumele actorilor care joaca in filmul curent, film ce poate fi identificata prin current_film_id.
SELECT current_title; SELECT first_name, last_name FROM actor INNER JOIN film_actor USING(actor_id) INNER JOIN film USING(film_id) WHERE film_id = current_film_id;
Parasirea structurii iterative este asigurata prin verificarea variabilei done. In momentul in care se identifica la nivelul variabilei done valoarea 1 este apelata structura de salt LEAVE, pentru a permite parasirea buclei.
IF done = 1 THEN LEAVE get_actors; END IF;
Apelul procedurii stocate get_film_actors, care furnizeaza valoare ‘ALIEN’ pentru cautarea in titlul filmelor, afiseaza pentru fiecare film care respecta expresia de cautare lista actorilor care joaca in acel film.
mysql> CALL sakila.get_film_actors('ALIEN'); +---------------+ | current_title | +---------------+ | ALIEN CENTER | +---------------+ 1 row in set +------------+-----------+ | first_name | last_name | +------------+-----------+ | BURT | DUKAKIS | | KENNETH | PALTROW | | SIDNEY | CROWE | | RENEE | TRACY | | HUMPHREY | WILLIS | | MENA | HOPPER | +------------+-----------+ 6 rows in set +---------------+ | current_title | +---------------+ | DESIRE ALIEN | +---------------+ 1 row in set +------------+-----------+ | first_name | last_name | +------------+-----------+ | TOM | MCKELLEN | | JOHNNY | CAGE | | ANGELINA | ASTAIRE | | JULIANNE | DENCH | | CATE | HARRIS | | LAURA | BRODY | | ROCK | DUKAKIS | +------------+-----------+ 7 rows in set +---------------+ | current_title | +---------------+ | HOBBIT ALIEN | +---------------+ 1 row in set +------------+-----------+ | first_name | last_name | +------------+-----------+ | VIVIEN | BERGEN | | ELVIS | MARX | | DUSTIN | TAUTOU | | WALTER | TORN | | WARREN | JACKMAN | | DARYL | CRAWFORD | | LAURA | BRODY | | REESE | WEST | +------------+-----------+ 8 rows in set Query OK, 0 rows affected