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

• Cursoare implicite
• Cursoare explicite
• Cursoare cu parametri
• Tipuri de variabile REF CURSOR, SYS_REFCURSOR și RECORD
• Eficiența cursoarelor

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:

• Implicite – create implicit de catre sistemul de gestiune pentru toate comenzile DML, chiar și pentru interogările care returnează o singură înregistrare;
• Explicite – create de dezvoltatori pentru controlul comenzilor DML care returnează mai multe înregistrări.

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):

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

Limitări:

• Există un parametru de sistem OPEN_CURSOR care setează numărul maxim de cursoare care se pot deschide pe parcursul unei sesiuni. Pentru a vedea sau modifica acest parametru trebuie să ai privilegiu de DBA și se folosesc comenzile: “select name, value from v$parameter where name like 'open_cursors'”” sau “ALTER SYSTEM SET open_cursors = 400 SCOPE=BOTH”.
• O altă limitare a numărului de cursoare este dată de memoria disponibilă pentru gestionarea lor.

Î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:

• cursore_name – numele atribuit cursorului
• column_names – numele coloanelor returnate de cursor
• table_names – numele tabelelor folosite de interogare
• conditions – condițiile de filtrare sau de join
• parameter – numele parametrilor cursorului și este opțional
• parameter_type – tipul parametrilor
• variable – este o variabilă de tipul unei linii din cursor
• for update – este opțiunea de blocare a liniilor (lock) selectate de cursor în baza de date

Observatii:

• Interogarea SELECT poate să fie de oricetip, poate să conțină joinuri și poate să conțină subcereri
• Există o convenție în Oracle care recomandă ca numele cursoarelor să înceapă cu litera c; folosind un astfel de nume va fi clar că numele din secțiunea de declarare se referă la un cursor
• O înregistrare care este bazată pe cursor (cursor-based) este o înregistrare a cărei structuri este de tipul unei linii a cursorului
• Pentru a declara o variabilă bazată pe cursor se folosește atributul %ROWTYPE
• Referirea unui element al variabilei de tip cursor se face adăugând ca prefix numele variabilei (asemănător cu referirea unei coloane din tabel): value := variable_name.element_name
• Numele atribuit unui cursor nu este o variabilă PL/SQL, ci un identificator, deci nu se pot atribui valori numelui unui cursor și nici folosi în expresii
• Rezultatele interogării cursorului devine setul activ de date al cursorului

Î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:

• cursor_name - numele atribuit cursorului
• parameter – numele parametrilor formali ai cursorului
• parameter_type – tipul parametrilor

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.

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

• %ISOPEN = Returnează TRUE dacă cursorul este deschis și FALSE dacă cursorul este închis
• %FOUND = Returnează: -INVALID_CURSOR dacă cursorul este declarat dar nu este deschis sau dacă cursorul a fost închis; -NULL dacă cursorul este deschis dar nu a fost executat; -TRUE dacă atribuirea s-a efectuat cu succes; -FALSE dacă nu a fost returnat niciun rând.
• %NOTFOUND = Returnează: -INVALID_CURSOR dacă cursorul este declarat dar nu este deschis sau dacă cursorul a fost închis; -NULL dacă cursorul este deschis dar nu a fost executat; -FALSE dacă atribuirea s-a efectuat cu succes; -TRUE dacă nu a fost returnat niciun rând.
• %ROWCOUNT = Returnează: -INVALID_CURSOR dacă cursorul este declarat dar nu este deschis sau dacă cursorul a fost închis; - Numărul de linii selectate și atribuite.

Î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:

• cursore_name – numele atribuit cursorului
• variable_names – numele variabilelor în care se memorează valorile din linia curentă a cursorului
• condition – condiția de ieșire din ciclul LOOP

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

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

CLOSE cursor_name;

Unde:

• cursor_name – numele cursorului

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

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:

• La inițializarea ciclului se execută în mod implicit o instrucțiune OPEN
• La fiecare parcurgere se execută în mod implicit o instrucțiune FETCH
• La părăsirea ciclului se execută în mod implicit o instrucțiune CLOSE

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).

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.

Se poate asocia o interogare unui cursor astfel:

• Se declară un tip de date REF CURSOR și se declară o variabilă cu acest tip
• Se declară o variabilă cu tipul de date SYS_REFCURSOR

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:

• ref_cursor – numele variabilei de tip REF CURSOR
• c_variable – numele variabilei curso
• variable – numele unei variabile normale
• conditions – condițiile de ieșire din buclă puse pentru c_variable

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.

• Pentru a mări eficiența cursoarelor trebuie să se țină cont de următoarele facilități:
• Crearea de indecși pe coloanele folosite în clauzele WHERE și JOIN
• Prelucrarea procedurală permite un control puternic și flexibil al liniilor din baza de date, dar fiecare INSERT sau UPDATE provoacă o reorganizare a indecșilor
• Efectuarea, pe cât posibil, a calculelor în cererea SELECT
• Prefixarea coloanelor cu numele userului și a tabelei
• În cazul unui JOIN indexat, să se facă referire la tabela cea mai mică la sfârșit
• Să se folosească IF pentru comparație, dacă toate datele de prelucrat sunt stocate în variabile
• Să se evite SELECT … FROM SYS.DUAL deoarece produce deschiderea unui cursor și transmiterea unor cereri către RDBMS
• Cursoarele explicite evită un FETCH ulterior față de cele implicite care execută două cereri(testează și linia următoare)
• Dacă se prelucrează mai multe linii dintr-o tabelă, este bine să se facă într-un singur pas, evitând trecerile repetate prin tabelă

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.

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:

• Dacă salariu_sef < salariu_mediu_departament atunci comision_sef = 10% * salariu_mediu_departament
• Dacă salariu_sef >= salariu_mediu_departament atunci comision_sef = 20% * salariu_mediu_departament