b9t21tn
Acest capitol va arata cum sa declarati si sa controlati explicit cursoarele, care va permit sa multiplicati linii de interogare pentru a fi executate in PL/SQL. Ne vom ocupa de felul in care liniile pot fi aduse de la cursor in interiorul unei bucle prin intermediul cursorului prin bucla.

Ce este un Cursor?
ORACLE foloseste arii de lucru private SQL ,arii care executa declaratii SQL si sa memoreaza informatii de procesare. Cursorul este o constructie PL/SQL ,care iti permite sa denumeste aceste arii de lucru si care acceseaza informatia.

Exista doua tipuri de cursor:

cursor implicit declarand PL/SQL cursor implicit penru toate declaratiile DML si penntru toate interogarile cu un singur rand cursor explicit declarand cursor explicit impreuna cu alti identificatori care sunt folositi in bloc si manipuland prin declaratii specifice si blocuri excutabile de actiune. Cursoarele explicite sunt numai interogari si va permit multiple linii care sa proceseze interogari.
Asa cum vedem , SELECT-uri care apar ca instructiuni separate in PL/SQL poat intoarce o singura linie. Aceasta inseamna ca de fapt PL/SQL incearca sa extraga doua linii de la cursorul implicit :una sa satisfaca interogarea , si a doua care sa vada daca viitoarele linii au fost returnate .

Cursoarele explicite va permit sa evitati aceasta a doua extragere , aceasta imbunatatind eficienta cand o interogare cu un singur rind este ceruta . Cursoarele explicite pot fi utilizate pentru a rezolva multiple extrageri , si pentru a reexecuta cereri trecute din aria respectiva .

Restul acestui capitol va va explica mult mai pe larg aceste probleme , despre cursorul explicit.

Controlul explicit al cursorului -pasii separati.
Cursorul explicit poate fi controlat prin 4 tipuri separate de actiuni:

DECLARE numeste cursorul,si defineste structura interogarii care sa fie efectuate cu el. La acest nivel,interogarea este parcursa(coloane,tabele etc) dar nu este executata.
OPEN exercuta interogarea,legand orice variabila care a fost referentiata. Liniile intoarse de interogare,numita "set-activ" sunt acum disponibile pentru extragere.
FETCH memoreaza valorile din linia curenta in variabile.Linia curenta este linia la care cursorul pointeaza. Fiecare FETCH produce mutarea cursorului sa indice la linia ur - matoare in setul activ,si deci fiecare FETCH va accesa o linie diferita returnata de interogare.
CLOSE porneste un set de lucru de linii produse de OPENul cursorului. Este posibil sa reOPEN (redeschideti) cursorul, stabilind astfel un set de lucru nou.
Instructiunea CURSOR
Este folosit pentru a declara un cursor explicit.Parametrii pot fi definiti pentru a permite substitutia valorilor in interogare cand cursorul este OPEN.Variabilele pot fi deasemeni referite in interogare ,dar trebuie sa fie declarate inaintea instructiunei CURSOR.

Sintaxa:

CURSOR identificatora( parameter details)i IS query-expression;

unde query-expression este o instructiune SELECT care poate include majoritatea clauzelor, dar nu o clauza INTO. Nu trebuie definit NULL ca un obiect SELECT.

Exemplu :

DECLARE

CURSOR c1 IS

SELECT ename, sal, hiredate FROM emp

WHERE deptno= 20 AND job= "ANALYST";

;

;

Instructiunea OPEN
Este utilizata in cadrul actiunilor executabile dintr-un bloc, ai stabileste un set activ de rinduri.
Sintaxa:

OPEN cursor-identif a(lista argumente)i ;

Exemplu:

OPEN c1;

Cursorul va pointa catre primul rind in setul activ, ca de exemplu:

> SCOTT 3000 16-jan-90

FORD 3000 03-dec-81

De observat ca exceptiile nu sint lansate daca cererea nu intoarce nici un rind cind este deschis cursorul. Starea cursorului poate, totusi, sa fie testata dupa un FETCH.

Instructiunea FETCH
Este utilizata pentru a extrage rindul curent intr-o variabila PL/SQL, sau intr-o variabila host, inclusiv cimpurile ecran din SQL*forms. Trebuie amintit ca variabilele host necesita punct-virgula ca prefix, pentru a fi deosebite de variabilele PL/SQL.

Sintaxa:

FETCH cursor-id INTO var,var,... ;

Variabilele trebuie sa fie specificate pentru fiecare cimp selectat in cererea de cursor. O alta posibilitate este definirea unei inregistrari pentru cursor, si transmiterea sa ca o clauza a FETCH.

Exemplu:

FETCH c1 INTO v_ename, v_sat, v_hiredate;

Variabilele incarcate, care au fost declarate inainte de FETCH, pot fi manipulate de alte instructiuni. FETCH-uri ulterioare vor achizitiona alte rinduri individuale din cerere. De notat ca primul FETCH care nu obtine nimic, adica daca nu mai ramin rinduri, nu va cauza o eroare. VAriabilele vor contine valori nule.

Instructiunea CLOSE
Inchide in mod explicit un cursor, permitind redeschiderea sa ulterioara. Aceasta inseamna ca un set activ poate fi restabilit de mai multe ori.

Sintaxa:

CLOSE cursor-identif ;

Atribute explicite pentru cursoare
CA si la cursoarele implicite, exista 4 atribute pentru a obtine informatii de stare despre cursoare. Cind sint utilizate, numele atributului este precedat de identificatorul cursorului.

%FOUND
Evaluat la TRUE daca ultimul FETCH din cursor a obtinut un nou rind, altfel FALSE
%NOTFOUND
Invers decit %FOUND
%ROWCOUNT
Numarul de rinduri preluate de la cursor pina acum
%ISOPEN
TRUE daca cursorul este deschis, FALSE daca a fost inchis sau nu a fost inca deschis.
Citeva exemple mai jos:

Exemple:

1. IF c1%ISOPEN THEN

FETCH c1 INTO v_ename, v_sal, v_hiredate ;

ELSE

OPEN c1;

2. LOOP

FETCH c1 INTO v_ename, v_sal, v_hiredate ;

EXIT WHEN c1%ROWCOUNT >10 ;

.

END LOOP;

Controlul extragerilor multiple din cursoare explicite
In mod normal, cind mai multe rinduri sint prelucrate dintr-un cursor explicit, un ciclu trebuie definit pentru a executa FETCH la fiecare iteratie. Daca acest proces continua, se for prelucra eventual toate rindurile. active. Cind un FETCH esueaza, atributul %NOTFOUND este TRUE, si poate fi testat. Totusi, daca dupa aceasta se efectueaza un nou FETCH apare o eroare:

ORA-1002: Fetch out of sequence

Aceasta eroare va termina blocul, eventual cu o exceptia netratata. Este deci important sa se verifice succesul fiecarui FETCH inainte de alte referiri la cursor. (fie prin alte FETCH sau comenzi SQL)

Exemplu:

OPEN cursor_1 ;

LOOP

FETCH cursor1 INTO a,b,c,d ;

EXIT WHEN cursor1%NOTFOUND ;

.

.

END LOOP;

Cursoarele si inregistrarile
Am vazut deja ca inregistrarile pot fi definite sa se potriveasca cu structura coloanelor unei tabele. Este de asemenea posibil sa definim inregistrari bazat pe lista de coloane selectata explicit de cursor. Aceasta este convenabil pentru prelucrarea rindurilor din setul activ, pentru ca se poate extrage direct in inregistrare, si valorile rindului vor fi incarcate direct in cimpurile corespunzatoare ale inregistrarii.

Exemplu:

DECLARE

CURSOR c1 IS

SELECT empno, sal, hiredate, rowid

FROM emp WHERE deptno = 20 AND job = 'ANALYST'

FOR UPDATE OF sal;

emp_record c1%ROWTYPE;

BEGIN

OPEN c1;

.

FETCH c1 INTO emp_record;

.

IF emp_record.sal <2000 THEN ...

De observat ca pseudo-coloana 'rowid' este valida ca articol selectabil, si deci va avea un cimp corespunzator in inregistrarea 'emp_record'.

Exemplul de mai sus arata de asemenea utilizarea FOR UPDATE intr-o cerere cu cursor. Aceasta inseamna ca rindurile intoarse de cerere sint blocate exclusiv cind instructiunea OPEN este executata. Deoarece blocarile sint eliberate la sfirsitul unei tranzactii, nu trebuie sa executati COMMIT intre extrageri dintr-un cursor explicit daca se foloseste FOR UPDATE.

Folosirea clauzei WHERE CURRENT OF

Cind ne referim la rindul-curent dintr-un cursor explicit, comenzile SQL pot folosi WHERE CURSOR OF, cu numele cursorului. Aceasta permite actualizarea sau stergera in punctul in care ne aflam, fara a fi necesara folosirea explicita a Rowid. Trebuie totusi sa includeti FOR UPDATE in cererea cursorului, astfel incit rindurile sa fie blocate la deschidere.

Exemplu:

FETCH c1 INTO emp_record ;

IF emp_record.ename = ' KING' THEN

DELETE FROM emp WHERE CURRENT OF c1;

;

Sa luam un exemplu comlet. In blocul de mai jos se prelucreaza fiecare rind din tabela 'dep', mutind departamentul SALES in locatia Dallas, si celelalte departamente la New York. De asemenea se tine un contor al departamentelor plasate in fiecare locatie.

DECLARE

CURSOR c1 IS

SELECT dname, loc FROM dept

FOR UPDATE OF loc ;

dept_rec c1%ROWTYPE;

sales_count NUMBER:=0 ;

non_sales NUMBER := 0;

BEGIN

OPEN c1;

LOOP

FETCH c1 INTO dept_rec;

EXIT WHEN c1%NOTFOUND;

IF dept_rec.dname = 'SALES' AND dept_rec.loc != 'DALLAS'

THEN

UPDATE dept SET loc = 'DALLAS' WHERE

CURRENT OF c1;

sales_count := sales_count + 1;

ELSIF dept_rec.dname != 'SALES' AND dept_rec.loc != 'NEW YORK'

THEN

UPDATE dept SET loc = 'NEW YORK' WHERE CURRENT OF c1;

non_sales := non_sales +1 ;

END IF;

END LOOP;

CLOSE c1;

INSERT INTO counts (sales_set, non_sales_set )

VALUES (sales_count, non_sales);

COMMIT;

END;

Cursoare cu parametrii

Parametrii permit transmiterea unor valori unui cursor cind acesta este deschis, si utilizarea in cererile care se executa.

Aceasta inseamna ca un cursor explicit poate fi deschis de mai multe ori intr-un bloc, intorcind seturi de lucru diferite cu fiecare ocazie.

Parametrii sint definite in instructiunea CURSOR astfel:

CURSOR identif ( param-name data-type,

param-name date-type, ... ) IS query-expresion;

Tipurile parametrilor sint aceleasi cu ale variabilelor scalare, dar nu primesc dimensiune.

Numele parametrilor sint pentru referire in cadrul expresiei de cerere din cadrul

cursorului, si pot fi tratate ca variabile PL/SQL.

Urmatorul exemplu arata numarul departamentului si tipul jobului care sint transmise intr-o clauza WHERE

prin parametrii Param1 si Param2:

CURSOR c1 ( Param1 NUMBER,

Param2 CHAR ) IS SELECT ename, sal, hiredate

FROM emp

WHERE deptno = Param1

AND

job = Param2;

Cind cursorul este ulterior deschis, valorile sint transmise pt. fiecare din parametrii in mod pozitional.

VAlorile din PL/SQL sau variabile host pot fi utilizate, si de asemenea literali.

Exemplu:

OPEN c1 (30, 'ANALYST');

OPEN c1 (pl_num, 'CLERK');

OPEN c1 (10, job_type); -- job_type este o variabila PL/SQL

Cicluri LOOP cursor

PL/SQL ofera un tip special de ciclu FOR pentru a prelucra rindurile intoarse in cursorul explicit.

Intr-un ciclu FOR cursor, un cursor declarat este deschis, se efectueaza extrageri si este

inchis automat cind toate rindurile au fost prelucrate.

Fiecare iteratie a ciclului extrage un rind din setul activ intr-o inregistrare,

care este declarata implicit pentru utilizare in cadrul ciclului.

Ciclul este terminat automat la sfirsitul iteratiei pentru ultimul rind extras.

Ciclurile FOR cu cursor sint structurate astfel:

DECLARE

CURSOR cursor_name a (parameters)i IS query-expresion;

BEGIN

FOR record-name IN cursor-name a (parameters) i

LOOP

--procesare valori din rindul curent.

.

END LOOP;

Inregistrarea numita in instructiunea FOR este declarata intern in ciclu si valabilitatea

expira cind se termina ciclul.

Fiecare iteratie provoaca extragerea rindului urmator din inregistrare.

Deoarece cursorul este declarat explicit in bloc, atributele sale sint disponibile in cadrul

ciclului pentru testare, daca este necesar.

Exemplu:

DECLARE

CURSOR c1 IS SELECT n1, n2, n3 FROM data_table;

result NUMBER;

BEGIN

FOR rec IN c1 LOOP

result := rec.n2 / (rec.n1 + rec.n3);

INSERT INTO temp_tab VALUES (result);

END LOOP;

COMMIT;

END;

Cicluri FOR cursor cu parametrii

Daca cursorul pe care il procesati intr-un ciclu FOR cursor a fost definit cu

parametrii, valorile (parametrii actuali) sint date in paranteza, dupa numele cursorului in instructiunea FOR.

CURSOR c1 (Param1 DATE ) IS

SELECT ename, job FROM emp

WHERE hiredate < Param1 ;

BEGIN

FOR emp_record IN c1 ('01-JAN-92')

LOOP

-- vor fi activi angajatii de dupa ian92

END LOOP;

Sub expresii select in ciclurile FOR

Am vazut ca ciclurile FOR cu cursor sint un mod convenabil de a prelucra rinduri dintr-un cursor explicit declarat in program. Ciclurile FOR pot fi de asmenea

utilizate pentru a prelucra cereri multi-rind, care sint definite la inceputul ciclului.

Structura este aratata mai jos:

FOR rec-name IN (query-expresion)

LOOP

.

END LOOP;

Expresia cererii este considerata ca o sub-expresie si este prelucrata intr-un cursor care este intern ciclului FOR.

Deoarece cursoarele nu sint declarate cu nume, atributele cursoarelor nu sint disponibile pentru testare.

In afara ca este mai simplu de scris, aceasta metoda este si mult mai eficienta.

Exemplu:

FOr rec IN (SELECT ename FROM emp WHERE deptno = 10 )

LOOP

IF rec.ename = 'JONES' THEN

.

END LOOP;

CIteva cuvinte despre eficienta

Toti factorii care influenteaza performanta unui program SQL se aplica si la

PL/SQL.

Acestia includ:

Indexarea coloanelor folosite in WHERE si in join

Prefixarea coloanelor cu numele tabelei

In joinuri indexate, referirea la tabela cea mai mica la sfirsit

In plus, PL/SQL mai ofera functionalitate care face instructiuni SQL nenecesare.

Nu accesati baza de date daca nu este necesar.

Daca toate datele de prelucrat sint stocate in variabile, atunci folositi constructii PL/SQL ca atribuirea sau IF. Nu folositi SELECT..FROM SYS.DUAL in mediul PL/SQL,

deoarece produce deschiderea unui cursor si transmiterea unor cereri catre RDBMS.

Efectuati calcule in SELECT, daca este posibil.

DAca aveti de efectuat prelucrari este mult mai eficient sa le efectuati

atunci cind SELECT-ati date, si evitati instructiuni ulterioare care ar fi necesare.

Cursoarele explicite va permite evitarea unui FETCH ulterior.

Atunci cind un singur rind este necesar, cursoarele implicite executa doua extrageri.

Un cursor explicit va permite sa efectuati o singura extragere.

Evitati treceri repetate prin tabele.

Prelucrarea procedurala permite un control puternic si flexibil al rindurilor bazei de date,

dar trebuie sa tineti cont ca fiecare INSERT si UPDATE provoaca re-scanarea tabelei; Daca prelucrati mai multe rinduri dintr-o tabela, incercati sa o faceti intr-un singur pas.

Considerati situatia urmatoare. Dorim sa marim costul fiecarui articol din tabel 'items', pe rind, pina cind suma depaseste 5000. In primul exemplu, o scanare a tabelei este utilizata de fiecare data pentru a recalcula totalul cind un articol este actual

izat:

WHILE total < 5000

LOOP

FETCH item_cursor INTO old_cost;

UPDATE items SET itemcost = itemcost *1.1

WHERE CURRENT OF item_cursor;

SELECT SUM(itemcost) INTO total FROM items;

END LOOP;

O atribuire ar putea evita 'SELECT SUM' la fiecare iteratie, calculind cit

de mult totalul ar fi afectat de ultima actualizare:

WHILE total < 5000

LOOP

FETCH item_cursor INTO old_cost;

UPDATE items SET itemcost = itemcost *1.1

WHERE CURRENT OF item_cursor;

tottal := total + old_cost *0.1;

END LOOP;

O discutie mult mai detaliata despre eficienta poate fi gasita in cartea

'Application Tuning for ORACLE Version 6' (cod AT6).

Exercitii

Exercitiul Demonstratie - utilizarea cursoarelor explicite si a atributelor.

"Fair Deals" s.a. a decis sa recompenseze citiva angajati cu premii.

Ei intentioneaza sa mareasca salariul individual cu 10 % , incepind cu cel mai prost platit muncitor sau grup.

Daca la orice moment suma totala depaseste 35000, nici un alt salariat nu va mai primii cresteri.

Scrieti un bloc PL/SQL care sa efectueze aceste operatii in tabela NEWEMP.

Blocul trebuie sa SELECT-eze suma salariilor doar o singura data, si sa efectueze o singura trecere prin tabela.

Scrieti un rind in MESSAGES cu numarul de angajati actualizati, si suma salariilor la sfirsitul blocului.

Nota: Blocul poate actualiza toate rindurile inainte de a atinge totalul de 35000. Este acceptabil. Puteti rula din nou blocul pentru cresteri ulterioare.

Utilizarea ciclurilor FOR cu cursor si inregistrare

Scrieti un bloc utilizeaza un ciclu FOR cu cursor care sa selecteze primii 5 din tabela EMP, si sa scrie rezultatul in tabela MESSAGES.

REzultatul va fi de genul:

numcol1 numcol2 charcol1

------------------------

7839 5000 KING

...

Solutii

1.

DECLARE

CURSOR cur1 IS select sal from newemp

ORDER BY sal

FOR UPDATE OF sal;

v_count NUMBER := 0 ;

totsal NUMBER (9,2) := 0;

v_sal emp.sal%TYPE;

BEGIN

SELECT SUM(sal) INTO totsal FROM newemp;

OPEN cur1;

WHILE totsal <=35000

LOOP

FETCH cur1 INTO v_sal ;

EXIT WHEN cur1%NOTFOUND;

U