UvUBWUm4

1
2CURSORUL ÃŽN PL/SQL
3
4
5	Atunci când se execută o comandă SQL, Oracle Server deschide o zonă de memorie (context area) în care comanda este executată. Cursorul este un pointer către această zonă.
6	În PL/SQL se utilizează două tipuri de cursoare:
7	implicit: declarat pentru toate instrucţiunile PL/SQL de tip LMD 
8	explicit: declarat şi gestionat de programator.
9
10CURSORUL IMPLICIT
11	Este declarat de PL/SQL implicit pentru toate comenzile de manipulare a datelor (INSERT, UPDATE, DELETE, SELECT);
12	Dacă o instrucțiune LMD nu afectează nici o linie a tabelei, nu se generează automat o excepție tip NO_DATA_FOUND;
13	Atributele cursorului implicit:
14	SQL%ROWCOUNT
15	SQL%FOUND
16	SQL%NOTFOUND
17	SQL%ISOPEN este mereu FALSE
18	Atributele se referă la cea mai recentă instrucțiune LMD. Înaintea primei instrucțiuni LMD din bloc, toate atributele au valoarea NULL;
19	După o instrucțiune COMMIT sau ROLLBACK, SQL%ROWCOUNT are valoarea 0.
20
21Atenție - Cursorul implicit NU este util pentru instrucțiunea SELECT folosită cu INTO. Aceasta va rula cu succes doar dacă interogarea returnează un singur rând și atunci SQL%ROWCOUNT va fi 1, SQL%FOUND va fi TRUE iar SQL%NOTFOUND va fi FALSE. Dacă interogarea nu returnează nici un rând sau returnează două sau mai multe, se va ridica o excepție (NO_DATA_FOUND sau TOO_MANY_ROWS).
22
23Exemplu
24
25Se șterg produsele din categoria hardware3 care nu au fost comandate. Se afișează numărul de rânduri şterse.
26
27SET SERVEROUTPUT ON
28BEGIN
29DELETE FROM produse p
30WHERE categorie='hardware3' and not exists (select 1 from rand_comenzi r where p.id_produs=r.id_produs);
31DBMS_OUTPUT.PUT_LINE (SQL%ROWCOUNT || ' randuri sterse');
32ROLLBACK;
33DBMS_OUTPUT.PUT_LINE (SQL%ROWCOUNT || ' randuri afectate');
34END;
35/
36După ROLLBACK, atributul SQL%ROWCOUNT devine 0. Rezultatul rulării este:
37
3810 randuri sterse
390 randuri afectate
40
41Se încearcă adăugarea unei regiuni și apoi modificarea denumirii produsului având codul 3. În cazul în care acest produs nu există (comanda update nu realizează nici o modificare) va fi afişat un mesaj corespunzător.
42
43BEGIN
44INSERT INTO regiuni VALUES(5,'Oceania');
45UPDATE produse 
46SET denumire_produs='cafea'
47WHERE id_produs=3;
48IF SQL%NOTFOUND THEN
49DBMS_OUTPUT.PUT_LINE('Nu exista produsul cu acest cod');
50END IF;
51ROLLBACK;
52END;
53/
54Observație: dacă regiunea cu id-ul 5 există deja, se va declanșa o excepție, dacă nu, se va rula comanda UPDATE. Atributul SQL%NOTFOUND se referă la ultima comandă, în acest caz, la UPDATE.
55
56Se șterge din tabela REGIUNI, regiunea a cărei ID este introdus de utilizator prin intermediul variabilei de substituție g_rid. Mesajul este afișat folosind variabila de mediu nr_sters.
57ACCEPT g_rid PROMPT 'Introduceti id-ul regiunii:'
58VARIABLE nr_sters varchar2(100)
59DECLARE
60BEGIN
61DELETE FROM regiuni WHERE id_regiune=&g_rid;
62:nr_sters:=TO_CHAR(SQL%ROWCOUNT)||' INREGISTRARI STERSE';
63END;
64/
65PRINT nr_sters
66ROLLBACK;
67
68CURSORUL EXPLICIT 
69	se foloseşte pentru a procesa individual fiecare linie (înregistrare) returnată de o instrucţiune SELECT ce returnează mai multe înregistrări.
70	mulţimea înregistrărilor returnate de o instructiune SELECT este numită mulţime rezultat. 
71	cursorul păstrează un pointer către linia curentă în cadrul unei mulţimi rezultat.
72Verificarea stării unui cursor explicit se realizează prin intermediul următoarelor atribute: 
73	NUME_CURSOR%ISOPEN - evaluat la TRUE în cazul în care cursorul este deschis;
74	NUME_CURSOR%NOTFOUND - evaluat la TRUE în cazul în care cel mai recent FETCH nu a returnat nici o linie;
75	NUME_CURSOR%FOUND - complementul lui %NOTFOUND;
76	NUME_CURSOR%ROWCOUNT - are ca valoare numărul liniilor returnate până în momentul curent.
77
78PARCURGEREA FOLOSIND OPEN-LOOP-FECTH-CLOSE
79
80In această abordare, prelucrarea cursorului explicit presupune parcurgerea următoarelor etape:
811)	se declară variabilele în care vor fi încărcate valorile corespunzătoare unei linii din cursor;
822)	se declară cursorul explicit, specificându-se un nume pentru acesta şi definindu-se interogarea de procesat în cadrul lui:
83		DECLARE nume_cursor IS SELECT........................;
843)	se deschide cursorul prin intermediul instrucţiunii OPEN, care execută interogarea şi legarea tuturor variabilelor referite. Înregistrările returnate de interogare sunt desemnate drept set activ de date, care pot fi de acum încărcate.
85		OPEN nume_cursor;
864)	utilizându-se instrucţiunea FETCH, se încarcă linia curentă din cursor în variabile. Fiecare încărcare determină mutarea pointerului cursorului la linia următoare din setul activ de date.
87		FETCH nume_cursor INTO var1, var2,..............;
885)	este închis cursorul prin instructiunea CLOSE, care dezafectează setul activ de linii. Cursorul poate fi din nou deschis pentru a stabili un nou set activ de linii.
89		CLOSE nume_cursor;
90	Pentru a procesa liniile unui cursor explicit se defineşte de obicei o buclă pentru executarea unui FETCH în fiecare iteraţie. În final, toate liniile din setul activ sunt procesate şi un FETCH executat fără succes poziţionează atributul %NOTFOUND pe TRUE. 
91	Înaintea primului FETCH, %NOTFOUND se evaluează la NULL, ca şi în cazul în care FETCH nu se execută niciodată cu succes.
92
93Exemple:
94
95Să se afişeze lista cu numele şi salariul angajaţilor din departamentul 60 folosind un cursor explicit și trei variabile scalare:
96set serveroutput on
97DECLARE
98cursor ang_cursor is select id_angajat, nume, salariul from angajati where id_departament=60;
99ang_id angajati.id_angajat%type;
100ang_nume angajati.nume%type;
101ang_sal angajati.salariul%type;
102BEGIN
103dbms_output.put_line('Lista cu salariariile angajatilor din departamentul 60');
104open ang_cursor;
105loop
106fetch ang_cursor into ang_id, ang_nume, ang_sal;
107exit when ang_cursor%notfound;
108dbms_output.put_line('Salariatul '||ang_nume||' are salariul: '||ang_sal);
109end loop;
110close ang_cursor;
111end;
112/
113Pentru o flexibilitate mai mare se poate utiliza o variabilă de tip înregistrare pentru încărcarea valorilor din cursor. Această variabilă de tip record poate avea aceleaşi atribute ca şi cursorul prin specificarea proprietăţii %ROWTYPE. În acest caz încărcarea din cursor se va face direct prin instrucţiunea fech var_cursor into var_record.
114Exemplul de mai sus poate fi rescris astfel:
115
116set serveroutput on
117declare
118cursor ang_cursor is select id_angajat, nume, salariul from angajati where id_departament=60;
119--tipul record definit cu %ROWTYPE pt incarcarea valorilor cursorului
120ang_rec ang_cursor%rowtype;
121begin
122dbms_output.put_line('Lista cu salariariile angajatilor din departamentul 60');
123open ang_cursor;
124loop
125fetch ang_cursor into ang_rec;
126exit when ang_cursor%notfound;
127dbms_output.put_line('Salariatul '||ang_rec.nume||' are salariul: '||ang_rec.salariul);
128end loop;
129close ang_cursor;
130end;
131/
132
133Să se încarce în tabela MESAJE primii 5 angajaţi (id şi nume)
134
135CREATE TABLE mesaje 
136(cod varchar2(7),
137nume varchar2(20)
138);
139
140DECLARE 
141v_id angajati.id_angajat%type;
142v_nume angajati.nume%type;
143CURSOR c1 IS SELECT id_angajat, nume FROM angajati;
144
145BEGIN
146OPEN c1;
147FOR i IN 1..5 LOOP
148FETCH c1 INTO v_id, v_nume;
149INSERT INTO mesaje VALUES(v_id, v_nume);
150END LOOP;
151CLOSE c1;
152END;
153/
154SELECT * FROM mesaje;
155
156Testul de ieşire din buclă în acest caz se poate face şi cu ajutorul atributului %ROWCOUNT. Tabela mesaje nu are cheie primară deci pot fi adăugate aceleași rânduri de mai multe ori.
157
158delete from mesaje;
159
160DECLARE 
161v_id angajati.id_angajat%type;
162v_nume angajati.nume%type;
163CURSOR c1 IS SELECT id_angajat, nume FROM angajati;
164
165BEGIN
166OPEN c1;
167LOOP
168FETCH c1 INTO v_id, v_nume;
169EXIT WHEN c1%ROWCOUNT>5 OR c1%NOTFOUND;
170INSERT INTO mesaje VALUES (v_id, v_nume);
171END LOOP;
172CLOSE c1;
173END;
174/
175SELECT * FROM mesaje;
176
177Să se afişeze primele 3 comenzi care au cele mai multe produse comandate. În acest caz înregistrările vor fi ordonate descrescător în funcţie de numărul produselor comandate. Am folosit funcția CAST pentru a transforma în interogare coloana DATA din TIMESTAMP în DATE.
178
179SET SERVEROUTPUT ON
180
181DECLARE
182CURSOR c_com IS 
183select c.nr_comanda, cast (c.data as date) data, count(r.id_produs) Numar
184from comenzi c, rand_comenzi r 
185where c.nr_comanda=r.nr_comanda
186group by c.nr_comanda, c.data
187order by count(r.id_produs) desc;
188
189rec_com c_com%rowtype;
190
191BEGIN
192DBMS_OUTPUT.PUT_LINE('Numarul de produse pentru fiecare comanda:');
193IF NOT c_com%ISOPEN THEN 
194OPEN c_com;
195END IF;
196LOOP
197FETCH c_com INTO rec_com;
198EXIT WHEN c_com%NOTFOUND OR c_com%ROWCOUNT>3;
199DBMS_OUTPUT.PUT_LINE('Comanda '||rec_com.nr_comanda||' data pe '||rec_com.data||' are: '||rec_com.numar||' produse');
200END LOOP;
201CLOSE c_com;
202END;
203/