Cursori cu parametri

Cursori cu parametri

Parametrii permit trecerea valorilor la un cursor cand acesta este deschis si folosirea acestora intr-un query cand acesta este executat. Acest lucru inseamna ca putem deschide si inchide un cursor explicit de mai multe ori intr-un bloc, returnand un set activ diferit in fiecare situatie. Sintaxa cursorului este:

CURSOR cursor_name

[(parameter_name datatype, . )]

IS

select_statement;

unde:

cursor_name este un numele unui cursor declarat.

parameter_name este numele unui parametru.

select_statement este o instructiune SELECT fara clauza INTO.

Parametrul poate avea urmatoarea sintaxa:

cursor_parameter_name [IN] datatype [ expr]

Fiecare parametru formal din declaratia cursorului trebuie sa aiba un parametru actual corespondent in comanda OPEN.

Exemplu:

DECLARE

CURSOR c1

(v_deptno NUMBER, v_job VARCHAR2) IS

SELECT empno, ename

FROM emp

WHERE deptno = v_deptno

AND job = v_job;

BEGIN

OPEN c1(10, 'CLERK');

.

In urmatorul exemplu, sunt declarate doua variabile si un cursor. Cursorul este definit cu doi parametrii.

DECLARE

job_emp emp.job%TYPE := 'CLERK';

v_ename emp.ename%TYPE;

CURSOR c1 (v_deptno NUMBER, v_job VARCHAR2) IS

SELECT .

Fiecare din urmatoarele declaratii deschid cursorul:

OPEN c1(10, job_emp);

OPEN c1(20, 'ANALYST');

Putem trece parametrii la un cursor utilizand o bucla cursor FOR:

DECLARE

CURSOR c1 (v_deptno NUMBER, v_job NUMBER) IS

SELECT .

BEGIN

FOR emp_record IN c1(10, 'ANALIST') LOOP .

Adaugand clauza FOR UPDATE in query-ul cursorului blocam randurile afectate cat timp cursorul este deschis. Deoarece Oracle Server indeparteaza blocarile la sfarsitul unei tranzactii, nu trebuie sa facem o operatie de COMMIT intr-un cursor explicit daca este folosita clauza FOR UPDATE.

SELECT .

FROM .

FOR UPDATE [OF column_reference] [ NOWAIT]

unde:

column_reference este o coloana din tabelul asupra caruia se executa query-ul

NOWAIT    intoarce o eroare daca randurile sunt blocate de o alta sesiune.

Clauza FOR UPDATE este ultima clauza dintr-o instructiune SELECT. Cand interogam tabele multiple, putem folosi clauza FOR UPDATE pentru blocarea randurilor din tabelele particulare. Randurile dintr-un tabel sunt blocate numai daca clauza FOR UPDATE refera a o coloana din acel tabel.

Exemplu:

DECLARE

CURSOR c1 IS

SELECT empno, ename

FROM emp

FOR UPDATE NOWAIT;

Pentru a referi randul curent dintr-un cursor explicit, folosim clauza WHERE CURRENT OF. Aceasta ne permite sa aplicam modificari si stergeri asupra randului curent adresat, fara a fi nevoie sa ne referim explicit cu ROWID. Sintaxa acestei clauze este:

WHERE CURRENT OF cursor

unde:

cursor este numele cursorului declarat. Cursorul trebuie sa fie declarat cu clauza FOR UPDATE.

Exemplu:   

DECLARE

CURSOR c1 IS

SELECT .

FOR UPDATE NOWAIT;

BEGIN

.

FOR emp_record IN c1 LOOP

UPDATE .

WHERE CURRENT OF c1;

.

END LOOP;

COMMIT;

END;

Cand folosim acesta clauza, cursorul cel referim trebuie sa existe si sa contina clauza FOR UPDATE in query-ul cursorului, altfel va apare o eroare. Aceasta clauza ne permite sa aplicam modificari si stergeri asupra randului curent adresat fara a fi nevoie sa referim pseudo-coloana ROWID.

Exemplu:

DECLARE

CURSOR my_cursor IS

SELECT t1.deptno, dname, STAFF

FROM dept t1, (SELECT deptno, count(*) STAFF

FROM emp

GROUP BY deptno) t2

WHERE t1.deptno = t2.deptno

AND STAFF >= 5;