7. Cursori
7.1.
Ce este un cursor
Pentru executia cererilor SQL prezente in blocurile PL/SQL si pentru stocarea rezultatelor acestora (cum e cazul cererilor SELECT) Oracle foloseste un spatiu de lucru accesibil utilizatorului prin intermediul unui obiect numit cursor. Acesta poate fi de exemplu folosit pentru a parcurge linie cu linie rezultatul si pentru a afla cate linii are acesta.
Cursorii sunt de doua tipuri:
§ Cursori impliciti. Ei sunt definiti automat de Oracle pentru orice cerere de tip DML, inclusiv pentru cererile SELECT .. INTO care returneaza o singura linie prezentate in capitolele anterioare. Acesti cursori nu au un nume declarat de utilizator iar cererea SQL asociata lor este ultima cerere SQL executata.
§ Cursori expliciti. Acestia sunt definiti de utilizator care precizeaza numele cursorului si cererea SQL asociata.
Pentru a folosi cursori expliciti pasii sunt
urmatorii:
§
In
zona de declaratii se defineste cursorul (nume, cerere SQL asociata).
§
In
zona executabila (dupa BEGIN, deci inclusiv in zona de tratare a exceptiilor)
se pot folosi instructiunile de lucru cu un cursor: OPEN, FETCH si CLOSE.
§
Pentru
orice tip de cursor se pot folosi metodele asociate acestora: %ISOPEN,
%NOTFOUND, %FOUND si %ROWCOUNT
Observatii:
§
Instructiunile
OPEN, FETCH si CLOSE sunt specifice cursorilor expliciti si nu pot fi folosite
pentru cei impliciti (pentru acestia operatiile respective sunt executate
automat de Oracle).
§
Pentru
cursorii impliciti valorile atributelor acestora (%NOTFOUND, %FOUND si
%ROWCOUNT) se pot afla folosind un nume de cursor generic - SQL - care desemneaza cel mai recent cursor
implicit deschis.
7.2.
Lucrul cu cursori impliciti
Ultima cerere executata a unui bloc PL/SQL are
asociat un cursor cu numele generic SQL.
Trebuie remarcat ca spre deosebire de cererile
SELECT . . INTO, celelalte cereri DML nu genereaza exceptii in cazul in care
nici o linie nu este afectata (de exemplu in cazul DELETE sau UPDATE).
Atributele cursorului SQL vor avea urmatoarele
valori:
§
SQL%ROWCOUNT:
numarul de linii afectate de cerere
§
SQL%FOUND:
TRUE daca exista linii afectate de cerere si FALSE altfel
§
SQL%NOTFOUND:
TRUE daca nu exista linii afectate de cerere si FALSE altfel
§
SQL%ISOPEN:
nu se aplica cursorilor impliciti.
Exemplu:
BEGIN
UPDATE EMP
SET SAL = SAL * 1.1
WHERE DEPTNO = 30;
dbms_output.put_line('Au fost
modificate '||
SQL%ROWCOUNT || ' linii');
if sql%found then
dbms_output.put_line('Found TRUE');
else
dbms_output.put_line('Found FALSE');
end if;
if sql%notfound then
dbms_output.put_line('NotFound TRUE');
else
dbms_output.put_line('NotFound FALSE');
end if;
END;
7.3.
Lucrul cu cursori expliciti
Asa cum am mentionat pasii de lucru sunt:
A. Declararea cursorului
Sintaxa
declaratiei unui cursor este:
DECLARE
CURSOR nume_cursor IS
cerere_select;
§
nume_cursor este un identificator
PL/SQL care denumeste cursorul.
§
cerere_select este o cerere SELECT care
NU trebuie sa contina clauza INTO. Variabila/variabilele in care se va incarca
linia curenta din rezultat va fi setata de catre instructiunea FETCH.
B. Deschiderea cursorului
Prin executia instructiunii OPEN se 'deschide' un
cursor. Efectul practic este urmatorul:
§
se executa cererea SQL asociata cursorului.
§
se
aduce in zona de memorie a acestuia rezultatul cererii (o tabela rezultat).
§
se
seteaza inregistrarea (linia) curenta la prima linie
Sintaxa instructiunii este:
OPEN nume_cursor;
Observatie: daca cererea SQL nu intoarce nici o
linie sau intoarce mai multe linii NU se ridica exceptiile NO_DATA_FOUND
respectiv TOO_MANY_ROWS.
C. Incarcarea liniei curente
Rezultatul lui OPEN este o tabela rezultat iar
indicatorul liniei curente este setat la prima.
Prin instructiunea FETCH:
§
Se
incarca linia curenta intr-o multime de variabile sau o singura variabila
inregistrare (avand mai multe campuri). Corespondenta este pozitionala
§
Se
incrementeaza indicatorul liniei curente.
§
Daca
nu mai sunt linii %FOUND intoarce FALSE
Sintaxa instructiunii este:
FETCH nume_cursor INTO
lista_variabile |
variabila_inregistrare
In cazul in care incarcarea se face intr-o lista de
variabile trebuie ca:
§
numarul
de variabile din lista sa fie acelasi cu numarul de coloane din rezultatul
cererii SELECT
§
tipurile
variabilelor din lista sa fie aceleasi cu tipurile coloanelor din rezultatul
cererii SELECT
In cazul in care incarcarea se face intr-o variabila
inregistrare trebuie ca:
§
numarul
de campuri al variabilei sa fie acelasi cu numarul de coloane din rezultatul
cererii SELECT
§
tipurile
campurilor variabilei sa fie aceleasi cu tipurile coloanelor din rezultatul
cererii SELECT
§
Se
poate defini o variabila de acelasi tip cu liniile rezultatului folosind
constructia nume_cursor%rowtype.
Atributelor cursorului pot fi consultate dupa ce s-a
facut primul FETCH si ele au urmatoarele valori:
§
nume_cursor%ROWCOUNT:
numarul de linii incarcate cu FETCH de la deschiderea cursorului.
§
nume_cursor%FOUND:
TRUE daca ultimul FETCH a incarcat o noua linie si FALSE altfel.
§
nume_cursor%NOTFOUND:
TRUE daca ultimul FETCH nu a incarcat o noua linie si FALSE altfel.
§
nume_cursor%ISOPEN:
TRUE daca acel cursor e deschis si FALSE altfel
Tabelul urmator arata dependenta valorilor unor
atribute de starea cursorului:
|
Folosit |
|
%FOUND |
%ISOPEN |
%NOTFOUND |
%ROWCOUNT |
|
inainte de |
OPEN |
exceptie |
FALSE |
exceptie |
exceptie |
|
dupa |
OPEN |
NULL |
TRUE |
NULL |
0 |
|
inainte de |
Primul FETCH |
NULL |
TRUE |
NULL |
0 |
|
dupa |
Primul FETCH |
TRUE |
TRUE |
FALSE |
1 |
|
inainte de |
Urmatoarele FETCH |
TRUE |
TRUE |
FALSE |
1 |
|
dupa |
Urmatoarele FETCH |
TRUE |
TRUE |
FALSE |
depinde de date |
|
inainte de |
Ultimul FETCH |
TRUE |
TRUE |
FALSE |
depinde de date |
|
dupa |
Ultimul FETCH |
FALSE |
TRUE |
TRUE |
depinde de date |
|
inainte de |
CLOSE |
FALSE |
TRUE |
TRUE |
depinde de date |
|
dupa |
CLOSE |
exceptie |
FALSE |
exceptie |
exceptie |
Nota: Ultimul FETCH este cel care nu mai intoarce o
linie din rezultat.
Exemple:
1.
Listarea
numelor angajatilor din departamentul 30. Parcurgerea rezultatului se face cu
un ciclu LOOP si iesirea este determinata de atributul %NOTFOUND:
declare
-- declarare cursor
cursor angajati is
SELECT * FROM EMP WHERE DEPTNO = 30;
-- variabila inregistrare pentru incarcare linie
om angajati%rowtype;
begin
-- deschidere cursor
open angajati;
loop
-- incarcarea liniei curente
fetch angajati into om;
-- iesire cand nu mai sunt linii
exit when angajati%notfound;
dbms_output.put_line(angajati%rowcount||
'
Angajatul: '||om.ename);
end loop;
-- inchidere cursor
close angajati;
end;
Rezultatul executiei este:
1 Angajatul: ALLEN
2 Angajatul: WARD
3 Angajatul: MARTIN
4 Angajatul: TURNER
5 Angajatul: CLARK
6 Angajatul: JAMES
2.
Rezultate
returnate de ISOPEN, FOUND
declare
cursor angajati is
SELECT * FROM EMP WHERE DEPTNO = 30;
om angajati%rowtype;
begin
-- inca nu e deschis
if angajati%isopen then
dbms_output.put_line('Cursorul e deschis');
else
dbms_output.put_line('Cursorul nu e deschis');
end if;
-- se deschide cursorul
open angajati;
if angajati%isopen then
dbms_output.put_line('Cursorul e deschis');
else
dbms_output.put_line('Cursorul nu e deschis');
end if;
-- este deschis, se incarca o
linie
fetch angajati into om;
if angajati%found then
dbms_output.put_line('Exista rezultat');
else
dbms_output.put_line('Nici macar o linie');
end if;
-- inchidere cursor
close angajati;
if angajati%isopen then
dbms_output.put_line('Cursorul e deschis');
else
dbms_output.put_line('Cursorul nu e deschis');
end if;
end;
Rezultatul executiei blocului de mai sus este:
Cursorul nu e deschis
Cursorul e deschis
Exista rezultat
Cursorul nu e deschis
7.4.
Cicluri FOR pentru cursori
Pentru a usura parcurgerea liniilor tabelei rezultat
asociata unui cursor deschis in PL/SQL exista un ciclu FOR special.
Sintaxa lui este urmatoarea:
FOR nume_inregistrare IN
nume_cursor LOOP
instructiuni;
END LOOP
Observatii:
§
Variabila
inregistrare NU trebuie definita de utilizator, acest lucru facandu-se automat.
§
Domeniul
de valabilitate al acestei variabile este corpul ciclului.
§
OPEN,
FETCH si CLOSE se executa automat
§
Se
executa cate un pas al ciclului pentru fiecare linie a rezultatului
§
Iesirea
din ciclu se face automat la terminarea liniilor.
Exemplu:
declare
cursor angajati is
SELECT * FROM EMP WHERE DEPTNO = 30;
begin
for om in angajati loop --
deschide automat cursorul
-- nu e necesar FETCH
dbms_output.put_line(angajati%rowcount||
' Angajatul: '||om.ename);
end loop; -- inchide automat cursorul
end;
Rezultatul este identic cu cel de la 7.3. Exemplul
1.
Acest tip de ciclu FOR se poate folosi si pentru cursori
impliciti, ca in exemplul de mai jos:
begin
-- nu e necesara
declararea cursorului
for om in (select * from
emp where deptno=30) loop
-- nu e necesar FETCH
dbms_output.put_line(sql%rowcount||
' Angajatul: '||om.ename);
end loop;
-- nu e necesara
inchiderea cursorului
end;
In acest caz returnarea a 0 sau mai multor linii nu duce la ridicarea de exceptii.
7.5.
Cursori cu parametri
Unui cursor i se pot transmite parametri. In acest
caz:
§
La
declarare se specifica parametrii formali si tipul lor (fara sa se specifice
dimensiunea, deci VARCHAR2 si nu VARCHAR2(10) de exemplu). Acesti parametri
sunt folositi in cererea SQL asociata.
§
La
deschidere se specifica parametrii actuali.
Sintaxa declararii unui parametru este:
parametru [IN] tipdedate [{:= | DEFAULT} expression]
Exemplu:
declare
cursor angajati (v_dept number :=10,
v_job varchar2:='CLERK') is
select ename, sal
from emp
where deptno = v_dept and job = v_job;
i number;
begin
for i in 1..5 LOOP -- cod
departament = i*10
dbms_output.put_line('Departamentul
'||i*10);
for om in angajati(i*10,
'MANAGER') loop
-- deschide automat cursorul
-- nu e necesar FETCH
dbms_output.put_line(angajati%rowcount||
' Angajatul: '||om.ename);
end loop; -- inchide automat cursorul
end loop;
dbms_output.put_line('Cu
parametrii impliciti');
for om in angajati loop
-- deschide automat cursorul
-- nu e necesar FETCH
dbms_output.put_line(angajati%rowcount||
' Angajatul: '||om.ename);
end loop; -- inchide automat cursorul
end;
Rezultatul este:
Departamentul 10
1 Angajatul: BLAKE
Departamentul 20
1 Angajatul: JONES
Departamentul 30
1 Angajatul: CLARK
Departamentul 40
Departamentul 50
Cu parametrii impliciti
1 Angajatul: MILLER
7.6.
Cursori cu subcereri
In clauza FROM a cererii SQL care defineste un
cursor poate sa apara o subcerere SQL. Ea se evalueaza o singura data si
rezultatul sau este folosit pentru calculul rezultatului cererii globale.
Exemplu: declaratia cursorului
contine (pe clauza FROM a cererii) o subcerere SQL care returneaza o tabela cu
doua coloane continand numarul departamentului si numarul de angajati pentru
departamentele cu mai mult de 4 angajati.
declare
cursor d is select
t1.deptno, dname, cati
from dept t1, (select
deptno, count(*) cati
from emp group by deptno) t2
where t1.deptno =
t2.deptno AND cati > 4;
begin
for dep in d loop
dbms_output.put_line(d%rowcount||
' Departamentul: '||
dep.deptno||
' '||dep.dname||
' '||dep.cati);
end loop;
end;
Rezultatul
va fi:
1 Departamentul: 20
RESEARCH 5
2 Departamentul: 30 SALES
6
Bineinteles subcererile pot fi prezente si pe clauza
WHERE, fie corelate, fie necorelate.
7.7.
Referinte catre cursori
Aceste referinte (de tip REF CURSOR) sunt similare pointerilor
din limbajele C sau Pascal.
Definirea se face cu:
TYPE nume_tip_pointer IS REF CURSOR [RETURN
tip_inregistrare];
v_pointer_cursor nume_tip_pointer;
Clauza RETURN se foloseste in definirea pachetelor
(package) pentru a specifica tipul de date intors de cursor.
Deschiderea cursorului se face in acest caz cu:
OPEN v_pointer_cursor FOR cerere_sql;
Deci asocierea cererii SQL se face nu la declarare
ci la deschiderea cursorului.
Exemplu:
declare
type Referinta is ref
cursor return emp%rowtype;
c_ref Referinta; -- variabila de tip REF CURSOR
e emp%rowtype; -- variabila pentru incarcarea
-- unei linii
begin
-- deschidere cursor cu
specificarea cererii
open c_ref for select *
from emp where deptno=10;
loop
fetch c_ref into e;
exit when c_ref%notfound;
dbms_output.put_line(c_ref%rowcount||
' Nume: '||
e.ename||
' Salariu '||
e.sal);
end loop;
close c_ref;
-- deschidere cursor cu
alta cerere SQL
open c_ref for select *
from emp where job='CLERK';
loop
fetch c_ref into e;
exit when c_ref%notfound;
dbms_output.put_line(c_ref%rowcount||
' Nume: '||
e.ename||
' Functia '||
e.job);
end loop;
END;
Rezultatul va fi:
1 Nume: BLAKE Salariu 5962
2 Nume: MILLER Salariu
2529
3 Nume: KING Salariu 5600
1 Nume: SMITH Functia
CLERK
2 Nume: ADAMS Functia
CLERK
3 Nume: JAMES Functia
CLERK
4 Nume: MILLER Functia
CLERK
Printre
restrictiile privind variabilele REF CURSOR mentionam:
§
Nu se pot
declara in pachete (package)
§
Nu se pot
compara pentru egalitate, inegalitate sau valoare nula
§
Nu li se poate
asigna o valoare nula
§
Tipul REF CURSOR
nu poate fi folosit in SQL la definirea coloanelor unei tabele sau vederi.
§
Nu poate fi
tipul elementelor unei colectii
§
Nu sunt
interschimbabile cu variabilele tip cursor: de exemplu nu le putem folosi in
cicluri FOR pentru cursori (mai sus am folosit LOOP).
7.8.
Exceptia ROWTYPE_MISMATCH
Cand se incearca incarcarea unei linii intr-o
variabila care nu corespunde ca tip se ridica exceptia ROWTYPE_MISMATCH.
In exemplul de mai jos se incearca incarcarea unei
linii cu mai multe valori intr-o variabila scalara. In acest caz se va ridica
exceptia mentionata:
declare
type Referinta is ref
cursor;
c_ref Referinta; -- variabila de tip REF CURSOR
e varchar2(10); -- variabila pentru incarcarea
-- unei linii. NU E CORECTA!
begin
-- deschidere cursor cu
specificarea cererii
open c_ref for select *
from emp where deptno=10;
loop
fetch c_ref into e; --
aici se ridica exceptia.
exit when c_ref%notfound;
end loop;
close c_ref;
exception
when rowtype_mismatch then
dbms_output.put_line('Exceptie rowtype_mismatch');
end;
7.9.
SELECT . . FOR UPDATE
In cazul in care prin intermediul unui cursor se
executa operatii de actualizare a tabelelor se poate folosi clauza FOR UPDATE
pentru a bloca liniile necesare in momentul in care se deschide cursorul
(implicit Oracle blocheaza o linie in momentul in care aceasta e actualizata).
De asemenea, cand se foloseste CURRENT OF pentru a
modifica liniile unei tabele acestea trebuie blocate inainte cu FOR UPDATE.
Sintaxa este:
DECLARE
CURSOR nume_cursor
[(parametri)] IS
SELECT . . .
. .
.
FOR
UPDATE [OF coloana] [NOWAIT]
In momentul deschiderii cursorului liniile gasite de
cererea SELECT sunt blocate. Ele sunt deblocate la sfarsitul tranzactiei
(COMMIT). Dupa ce modificarile au fost comise nu se mai pot incarca linii
dintr-un cursor definit cu FOR UPDATE.
NOWAIT specifica faptul ca daca liniile respective
sunt blocate in acel moment Oracle sa nu astepte (aceasta fiind actiunea
standard).
Exemplu: liniile tabelei EMP sunt blocate pentru ca
pe parcursul actualizarii sa nu fie modificate de alti utilizatori:
declare
cursor c_ang is select
ename, job, rowid
from emp FOR UPDATE NOWAIT;
v_nume emp.ename%type;
v_job emp.job%type;
v_rowid rowid; v_nr
number;
begin
open c_ang; v_nr := 0;
loop
fetch c_ang into v_nume, v_job, v_rowid;
exit when c_ang%notfound;
dbms_output.put_line(v_nume||' '||v_job||' '||v_rowid);
update emp set sal = sal * 1.1 where rowid = v_rowid;
-- mai usor aici ar fi CURRENT OF c_ang
v_nr := v_nr + 1;
end loop;
dbms_output.put_line('Actualizate:
'||v_nr);
close c_ang;
commit;
exception
when others then
dbms_output.put_line(SQLCODE||' '||SQLERRM);
end;
/
In cazul in care exista linii blocate de alti
utilizatori se va genera o exceptie (din cauza lui NOWAIT). Mesajul va fi:
-54 ORA-00054: resource busy and acquire with NOWAIT
specified
PL/SQL procedure successfully completed.
Daca liniile nu sunt blocare de alt utilizator
rezultatul este:
SMITH CLERK AAAT1wAAEAAABSFAAA
ALLEN SALESMAN AAAT1wAAEAAABSFAAB
WARD SALESMAN AAAT1wAAEAAABSFAAC
MARTIN SALESMAN AAAT1wAAEAAABSFAAD
TURNER SALESMAN AAAT1wAAEAAABSFAAE
JONES MANAGER AAAT1wAAEAAABSFAAF
BLAKE MANAGER AAAT1wAAEAAABSFAAG
CLARK MANAGER AAAT1wAAEAAABSFAAH
ADAMS CLERK AAAT1wAAEAAABSFAAI
JAMES CLERK AAAT1wAAEAAABSFAAJ
MILLER CLERK AAAT1wAAEAAABSFAAK
SCOTT ANALYST AAAT1wAAEAAABSFAAL
FORD ANALYST AAAT1wAAEAAABSFAAM
KING PRESIDENT AAAT1wAAEAAABSFAAN
Actualizate: 14
PL/SQL procedure successfully completed.
De asemenea prin clauza FOR UPDATE se blocheaza linii
dintr-o tabela aflata pe clauza FROM a unei cereri SQL multi-tabela. Pentru
aceasta trebuie specificata coloana care se doreste modificata:
FOR UPDATE OF coloana
Exemplu:
declare
cursor c_ang is select
ename, e.rowid, dname
from emp e,dept d
where e.deptno=d.deptno
and d.dname='ACCOUNTING'
FOR UPDATE of sal nowait;
v_nume emp.ename%type;
v_dname dept.dname%type;
v_rowid rowid; v_nr
number;
begin
open c_ang;
v_nr := 0;
loop
fetch c_ang into v_nume, v_rowid, v_dname;
exit when c_ang%notfound;
dbms_output.put_line(v_nume||' '||v_dname);
update emp set sal = sal * 1.1 where rowid = v_rowid;
-- mai usor aici ar fi CURRENT OF c_ang
v_nr
:= v_nr + 1;
end loop;
dbms_output.put_line('Actualizate:
'||v_nr);
close c_ang;
commit;
exception
when others then
dbms_output.put_line(SQLCODE||' '||SQLERRM);
end;
Rezultat:
BLAKE ACCOUNTING
MILLER ACCOUNTING
KING ACCOUNTING
Actualizate: 3
7.10.
Clauza WHERE CURRENT OF
In cazul in care folosim FOR UPDATE liniile blocate
se pot identifica si prin constructia:
CURRENT OF nume_cursor
folosita in WHERE.
Exemplu:
declare
cursor c_ang is select
ename, job
from emp FOR UPDATE;
v_nume emp.ename%type;
v_job emp.job%type;
v_nr number;
begin
open c_ang; v_nr := 0;
loop
fetch c_ang into v_nume, v_job;
exit when c_ang%notfound;
dbms_output.put_line(v_nume||' '||v_job);
update emp
set sal = sal * 1.1
where CURRENT OF c_ang;
-- aici e folosita constructia
v_nr := v_nr + 1;
commit;
end loop;
dbms_output.put_line('Actualizate:
'||v_nr);
close c_ang;
exception
when others then
dbms_output.put_line(SQLCODE||' '||SQLERRM);
end;
Rezultatul va fi:
SMITH CLERK
ALLEN SALESMAN
WARD SALESMAN
MARTIN SALESMAN
TURNER SALESMAN
JONES MANAGER
BLAKE MANAGER
CLARK MANAGER
ADAMS CLERK
JAMES CLERK
MILLER CLERK
SCOTT ANALYST
FORD ANALYST
KING PRESIDENT
Actualizate: 14
PL/SQL procedure
successfully completed.