Table of Contents

Laboratorul 04 - PL/SQL Cursoare

Exercitii date spre rezolvare: Enunt/Rezolvare pentru Luni EG305 10-14

Conținut

Cursoare

Un cursor este o construcție PL/SQL prin care se pot controla și accesa informațiile dintr-o zonă de memorie alocată pentru a stoca datele procesate prin instrucțiuni SQL.

Există două tipuri de cursoare:

Cursoare Implicite

În PL/SQL se creează implicit un cursor pentru fiecare instrucțiune DML (SELECT, INSERT, DELETE, UPDATE).

Pentru un utilizator acest lucru este total transparent și nu poate să controleze modul de execuție al unui cursor implicit, acesta poate numai să prelucreze datele returnate de cursorul implicit. Trebuie să se acorde mare atenție la modul în care se creează o cerere DML pentru că pot să apară probleme la execuția unui bloc. Trebuie să se acorde atenție și la modul în care sunt puse condițiile în clauza WHERE.

De exemplu, dacă nu se folosește o variabilă de tip colecție(vector, indexby table, nested table) cu un BULK COLLECT INTO într-un SELECT care returnează mai multe înregistrări și se va folosi o variabilă simplă atunci blocul o să dea o eroare deoarece se încearcă să se insereze mai multe valori intr-o singură variabilă.

Ex. 1 Exemplu greșit de folosire a unui cursor implicit (returnează mai mute valori care nu pot să fie inserate în variabila salariu):

Ex. 2 Exemplu corect de folosire a unui cursor implicit (clauza where conține o condiție care garantează că se va întoarce o singură valoare):

Ex. 3 Exemplu corect de folosire a unui cursor implicit care returnează mai multe înregistrări (se folosește un nested table în care se inserează salariile angajaților din departamentul 80):

Cursoare Explicite

Cursoarele explicite sunt declarate de catre utilizator și sunt folosite pentru prelucrarea rezultatelor interogărilor care returnează linii multiple.

Limitări:

În secțiunea DECLARE se definește numele cursorului și structura interogării care va fi efectuată cu el. În această secțiune, interogarea este compilată dar nu este executată, semnalându-se eventualele erori de compilare.

Sintaxa este: 

DECLARE 
	CURSOR cursor_name [(<parameter> <parameter_type>)] IS 
		SELECT column_manes 
		FROM table_names 
		WHERE conditions [FOR UPDATE [OF col1_name[, OF col2_name[, …]]]]; 
 
	variable cursor_name%rowtype;

Unde:

Observatii:

În secțiunea OPEN se deschide cursorul și se face execuția efectivă a interogării, obținându-se ca rezultat liniile care vor constitui setul activ de date.

Sintaxa este: 

OPEN cursor_name [(<parameter> <parameter_type>)];

Unde:

Cursorul se deschide cu valorile actuale pentru fiecare parametru formal specificat în declarația cursorului, în caz contrat parametrii care apar în declarația cursorului vor avea valori prestabilite. După ce s-a deschis cursorul se pot atribui parametrii actuali la parametrii formali folosind operatorul de atribuire.

Atunci când se execută comanda OPEN, cursorul identifică numai acele linii care satisfac condiția de interogare, dar liniile nu sunt citite până când nu se face prelucrarea de către cursor.

Cursorul se inițializează la prima linie a setului activ.

Fiecare cursor explicit are patru atribute care sunt setate automat de catre sistemul de gestiune și conțin informații despre starea lor:

În secțiunea FETCH se memorează valorile din linia curentă a cursorului într-o variabilă. Sintaxa este: 

LOOP 
	FETCH cursor_name 
	INTO variable_names; 
	EXIT WHEN condition; 
	... 
END LOOP;

Unde:

Instrucțiunea FETCH preia liniile din setul activ una câte una și sortează setul activ, dacă este cazul.

Cursorul avansează automat pe următoarea linie la executarea comenzii FETCH

În secțiunea CLOSE se închide setul de linii returnate de catre cursor după deschidere. Sintaxa este: 

CLOSE cursor_name;

Unde:

După închiderea unui cursor rezultatul interogării se pierde. Pentru a accesa mulțimea datelor asociate unui cursor închis trebuie redeschis cursorul.

Structura completă a unui cursor explicit este următoarea: 

DECLARE 
	CURSOR cursor_name IS SELECT ...; 
	variable cursor_name%rowtype; 
BEGIN 
	OPEN cursor_name; 
	LOOP 
		FETCH cursor_name 
		INTO variable; 
		EXIT WHEN condition 
		... 
	END LOOP; 
	CLOSE cursor_name; 
END

În general, condition este cursor_name%NOTFOUND.

Ex. 4 Să se declare un cursor care selectează denumirea departamentului, numele angajatului, salariul și data angajării pentru acei angajati care au venit în companie în 2003.

Ex. 5 Să se folosească un cursor pentru a face o listă cu veniturile managerilor din companie.

Setul activ de date definite de cursorul c_angajati este procesat linie cu linie. Ciclul iterativ de citire și procesare a liniilor individuale continuă până la procesarea tuturor liniilor din setul activ.

Ieșirea din buclă se face testând condiția %NOTFOUND care se setează pe true după ce cursorul procesează toate liniile. După închiderea cursorului datele din setul activ sunt resetate.

Ciclul FOR este folosit pentru simplificarea sintaxei de prelucrare a liniilor dintr-un cursor. Cursorul însuși este cel care determină momentul în care se părăsește ciclul. Sintaxa este: 

DECLARE 
	CURSOR cursor_name IS SELECT ...; 
	variable cursor_name%rowtype; 
BEGIN 
	FOR variable IN cursor_name 
	LOOP 
		... 
	END LOOP; 
END;

Ciclul FOR simplifică codul programului și efectuează următoarele operații:

Ex. 6 Rescriere exemplu 5 folosind FOR.

Într-un ciclu FOR se poate folosi un SELECT care este tot un cursor, dar care nu trebuie să mai fie declarat în secțiunea DECLARE. Sintaxa este: 

BEGIN 
	FOR variable IN (SELECT column_manes FROM table_names WHERE conditions) 
	LOOP 
		... 
	END LOOP; 
END;

Ex. 7 Să se rescrie exercițiul 6 folosind un SELECT în ciclul FOR.

Când ne referim la rândul curent dintr-un cursor explicit comenzile SQL pot folosi clauza WHERE CURRENT OF cursor_name. Această clauză permite actualizarea sau ștergerea în punctul în care se află cursorul, fără a fi necesară folosirea condițiilor în clauza WHERE pentru identificarea unică a liniei. Trebuie să se folosească clauza FOR UPDATE în definirea cursorului pentru a se bloca rândurile la deschidere (se face un lock).

Clauza WHERE CURRENT OF cursor_name se poate folosi si in comenzile UPDATE și DELETE.

Ex. 8 Să se modifice comisionul cu 10% din salariu pentru angajații care au peste 18 ani vechime în companie.

Ex. 9 Să se șteargă din tabela employees toți angajații care nu au comision.

Parametrii permit transmiterea unor valori efective unui cursor când acesta este deschis. Parametrii formali se definesc în momentul declarării cursorului. Tipurile parametrilor sunt aceleași cu ale variabilelor scalare, dar nu primesc dimensiuni. Parametrii sunt folosiți pentru referirea în cadrul expresiei de definire din cadrul cursorului și pot fi tratați și ca variabile PL/SQL.

Ex. 10 Să se facă o listă cu angajații care fac parte dintr-un departament specificat, au o anumită funcție și au venit în companie la o anumită dată specificată. Aceste condiții să fie transmise ca parametri unui cursor.

Tipuri de variabile REF CURSOR, SYS_REFCURSOR și RECORD

Se poate asocia o interogare unui cursor astfel:

Acest mod de a declara un cursor este util în momentul în care se dorește să se transmită seturi de date la subprograme(proceduri, funcții, pachete). Utilizatorul poate să-și definească propriul tip de date pentru a păstra o linie dintr-un cursor folosind o variabilă de tip RECORD.

Sintaxa REF CURSOR: 

DECLARE 
	TYPE ref_cursor IS REF CURSOR; 
	c_variable ref_cursor; 
	variable variable_type; 
BEGIN 
	OPEN c_variable 
		FOR SELECT column_manes 
		FROM table_names 
		WHERE conditions  [FOR UPDATE [OF col1_name[, col2_name, …]]]; 
	LOOP 
		FETCH c_variable INTO variable; 
		EXIT WHEN condition 
		... 
	END LOOP; 
	CLOSE c_variable; 
END;

Unde:

Sintaxa SYS_REFCURSOR: 

DECLARE
	c_variable SYS_REFCURSOR;
	variable variable_type;
BEGIN
	OPEN c_variable 
		FOR SELECT column_manes 
		FROM table_names 
		WHERE conditions  [FOR UPDATE [OF col1_name[, col2_name, …]]]; 
	LOOP 
		FETCH c_variable INTO variable; 
		EXIT WHEN condition 
		... 
	END LOOP; 
	CLOSE c_variable; 
END;

Ex. 11 Să se facă o listă cu toate departamentele.

Ex. 12 Să se listeze toți angajatii din tabela employees.

Ex. 13 Să se folosească un record pentru a păstra numele, funcția și salariul pentru toți angajații.

Eficiența cursoarelor

Performanțele cursoarelor pot fi îmbunătățite prin ajustarea parametrului de sistem CURSOR_SPACE_FOR_TIME care precizează momentul în care zona partajată (shared pool) poate fi dealocată pentru a face loc unei noi comenzi SQL și are valoarea implicită FALSE. Aceasta înseamnă că SGBD-ul poate dealoca zona partajată chiar dacă este deschis un cursor al unei aplicații. Valoarea TRUE înseamnă că o zonă partajată poate fi dealocată numai atunci când toate cursoarele aplicației sunt închise, cu condiția ca zona să fie suficient de mare pentru ca toate cursoarele să fie deschise simultan. Setarea parametrului pe TRUE face ca SGBD-ul să realizeze o economie de timp și resurse și poate îmbunătăți performanțele apelurilor de execuție.

Nu se recomandă totdeauna setarea pe TRUE deoarece parametrul trebuie corelat cu alți parametri de sistem pentru dimesionarea zonei partajate și a numărului maxim de cursoare deschise simultan.

Ex. 14 Să se scrie un cursor explicit PL/SQL care face o listă pentru acordarea de comisioane tuturor șefilor de departament(care se identifică în coloana mgr din tabela EMP), după următorul algoritm: