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

Inapoi la cuprins.

BogSoft 1999