CAPITOLUL 24
VIATA SECRETA A CURSOARELOR
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 identificator[( parameter details)] 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 [(lista argumente)] ;
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
De observat ca pseudo-coloana 'rowid' este valida ca articol selectabil, si dec
i va avea un cimp corespunzator in inregistrarea 'emp_record'.
Exemplul de mai sus arata de asemenea utilizarea FOR UPDATE intr-o cerere cu cu
rsor.
Aceasta inseamna ca rindurile intoarse de cerere sint blocate exclusiv cind ins
tructiunea 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 fo
losi WHERE CURSOR OF, cu numele cursorului.
Aceasta permite actualizarea sau stergera in punctul in care ne aflam, fara a f
i necesara folosirea explicita a Rowid.
Trebuie totusi sa includeti FOR UPDATE in cererea cursorului, astfel incit rind
urile 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 != 'NE
W 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-u
n 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 cadr
ul
cursorului, si pot fi tratate ca variabile PL/SQL.
Urmatorul exemplu arata numarul departamentului si tipul jobului care sint tran
smise 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 pa
rametrii in mod pozitional.
VAlorile din PL/SQL sau variabile host pot fi utilizate, si de asemenea literal
i.
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 i
n cursorul explicit.
Intr-un ciclu FOR cursor, un cursor declarat este deschis, se efectueaza extrag
eri 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 [ (parameters)] IS query-expresion;
BEGIN
FOR record-name IN cursor-name [ (parameters) ]
LOOP
--procesare valori din rindul curent.
.
END LOOP;
Inregistrarea numita in instructiunea FOR este declarata intern in ciclu si val
abilitatea
expira cind se termina ciclul.
Fiecare iteratie provoaca extragerea rindului urmator din inregistrare.
Deoarece cursorul este declarat explicit in bloc, atributele sale sint disponib
ile 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 c
ursorului in instructiunea FOR.
CURSOR c1 (Param1 DATE ) IS
SELECT ename, job FROM emp
WHERE hiredate
Sub expresii select in ciclurile FOR
Am vazut ca ciclurile FOR cu cursor sint un mod convenabil de a prelucra rindur
i 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 d
isponibile 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
O atribuire ar putea evita 'SELECT SUM' la fiecare iteratie, calculind cit
de mult totalul ar fi afectat de ultima actualizare:
WHILE total
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 ma
i 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 efectue
ze o singura trecere prin tabela.
Scrieti un rind in MESSAGES cu numarul de angajati actualizati, si suma salarii
lor la sfirsitul blocului.
Nota: Blocul poate actualiza toate rindurile inainte de a atinge totalul de 350
00. 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
BogSoft 1999