Exercitii date spre rezolvare: Enunt/Rezolvare pentru Luni EG305 10-14
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:
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 20):
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.
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.
În secțiunea CLOSE se închide setul de linii returnate de catre cursor după deschidere. Sintaxa este:
CLOSE cursor_name;
Unde:
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 1981.
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).
Ex. 8 Să se modifice comisionul cu 10% din salariu pentru angajații care au peste 20 ani vechime în companie.
Ex. 9 Să se șteargă din tabela EMP toți angajații care au comision.
BEGIN FOR angajat IN (SELECT empno FROM emp WHERE comm IS NULL) LOOP DELETE FROM emp WHERE empno = angajat.empno; END LOOP; END;
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:
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 EMP.
Ex. 13 Să se folosească un record pentru a păstra numele, funcția și salariul pentru toți angajații.
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: