CAPITOLUL 21
                                       
   
   
                           INTERACTIUNEA CU ORACLE
                                       
   
   
   In acest unit se va trata modalitatea de access la baza de date si de
   controlarea tranzactiilor, prin instructiuni SQL in PL/SQL. De
   asemenea veti vedea cum erorile cauzate de SQL pot fi tratate de catre
   rutinele de tratare a exceptiilor.
   
     * Comenzi SQL in PL/SQL
       
     PL/SQL ofera citeva instructiuni procedurale pentru manipularea si
     testarea datelor, de multe ori fara sa avem nevoie sa apelam comenzi
     SQL. Aceasta metoda este preferabila, deoarece SQL face access la
     baza de date.
     
     Oricum, cind vrem sa extragem informatii din baza de date, sau sa
     facem modificari atunci trebuie sa folosim SQL. PL/SQL suporta toate
     facilitatile DML si toate comenzile de control al tranzactiilor din
     SQL. Si, bineinteles, instructiunea SELECT poate fi folosita pentru
     a atribui variabilelor valori din liniile in tabele.
     
     Iata citeva puncte mai inportante care trebuie amintite:
          + Fiecare comanda SQL trebuie terminata de ';'
          + Un bloc PL/SQL nu este o unitate de tranzactie - COMMIT si
            ROLLBACK sunt implementate independent de blocuri, dar pot
            face parte din ele.
          + Comenzile DDL nu sunt permise in PL/SQL
          + Comanda SELECT care nu intoarce un singur rind cauzeaza o
            eroare
          + Comenzile DML pot procesa mai multe linii
   
     * Referirile PL/SQL in comenzi SQL
       
     Valorile memorate in variabile si constante PL/SQL pot fi pasate
     direct comenzilor SQL, unde ele sunt procesate de modulele PL/SQL pe
     masura ce blocul este executat. Atentie mare ar trebui acordate
     pentru a nu referi variabile PL/SQL in SQL unde variabilele au
     acelasi nume cu coloana in tabela.
     
     Exemplul urmator ilustreaza aceasta problema.
     Exemplu:


        DECLARE
            empno NUMBER(4) := 7788;
        BEGIN
            UPDATE emp SET sal = 9000;
            WHERE  empno = empno;
                -- unde coloana este egala cu ea insasi

        ......
        END;


     
     
     
     In exemplul anterior fiecare linie din tabela 'emp' trebuie
     reactualizata. Fiecare adresare in instructiunea UPDATE catre
     'empno' este tratata ca nume de coloana. De aceea, alegeti nume de
     variabile PL/SQL care nu intra in conflict cu numele coloanelor din
     tabela pe care doriti sa o folositi.
     
     De asemenea, retineti ca atributele si functiile PL/SQL care sunt
     unice PL/SQL nu pot fi adresate direct din comenzi SQL. Acestea
     includ SQLCODE si SQLERRM, care vor fi discutate mai tirziu in acest
     unit.
     
COMENZI PENTRU MANIPULAREA DATELOR

     


        INSERT
        UPDATE
        DELETE
        [LOCK TABLE]


     
     Exemple:


        DECLARE
         v_empno NUMBER(4) := 7788 ;
        BEGIN
         UPDATE emp SET sal = 9000 ;
         WHERE  empno = v_empno ;
         ...
        END;


     
     Nota) Atentie la numele variabilelor !
   
     * Comenzi pentru manipularea datelor ( DML )
       
     Comenzile DML, INSERT UPDATE, DELETE pot fi folosite fara restrictii
     in PL/SQL. Tabela sau linia apare ca un rezultat al acestor comenzi
     si apar la sfirsitul tranzactiilor DML, in maniera obisnuita:
     
     Exemplu:


        DECLARE
         v_empno NUMBER (4) := 7788;
        BEGIN
         UPDATE emp SET val = 9000;
         WHERE empno = v_empno;
         ...
        END;


     
     
     Exemplul de mai sus tinteste liniile pentru UPDATE care au valoarea
     'empno' de 7788. Daca zero sau mai multe linii satisfac conditia,
     comanda este inca incheiata cu succes.
   
     * Cursorul implicit - Testarea rezultatelor comenzilor SQL
       
     Ori de cite ori sunt executate comenzi SQL, o zona de memorie este
     deschisa in care comanda poate fi interpretata si executata. Un
     cursor este un identificator pentru aceasta zona.
     
     Comenzile SQL ce fac parte din partea executabila a unui bloc sunt
     executate in 'cursorul implicit' care are identificatorul 'SQL'.
     Acest cursor este gestionat automat pentru utilizatori de catre
     PL/SQL.
     
     PL/SQL ofera citeva 'atribute' care ne permit sa determinam ce se
     intimpla cind cursorul implicit a fost utilizat ultima data. Acestea
     sunt:

        SQL%ROWCOUNT    - numarul de linii procesate de instructiuni
                          SQL ( valoare intreaga)
        SQL%FOUND       - TRUE daca cel putin o linie a fost procesata,
                          altfel FALSE ( valoare booleana)
        SQL%NOTFOUND    - TRUE daca nu a fost procesata nici o linie
                          altfel FALSE ( valoare booleana)

     
     
     Aceste atribute pot fi folosite in aceeasi maniera ca functiile in
     comenzi PL/SQL, dar nu in comenzi SQL.
     
     Atributele pot fi folosite in sectiune EXCEPTION a unui bloc pentru
     a evalua rezultatul unei instructiuni SELECT esuate (cereia i se
     permite sa intoarca doar o linie), dar probabil ca este mult mai
     folositor sa evaluam rezultatul operatiei DML.
     
     Exemplu:

        DECLARE
            rows_deleted NUMBER;
        BEGIN
            DELETE FROM dept WHERE deptno = 50;
            rows_deleted := SQL%ROWCOUNT ;

            INSERT INTO del_history VALUES ('DEPT', rows_deleted,SYSDATE);
        END;

   
       
   Controlul Tranzactiilor 
       
     La fel ca si ORACLE in general, tranzactiile DML vor incepe la prima
     comanda ce urmeaza lui COMMIT sau ROLLBACK, si sfirsitul urmatorului
     COMMIT sau ROLLBACK terminat cu succes. Aceste actiuni pot avea loc
     intr-un bloc PL/SQL sau ca rezultat al evenimentelor din mediu
     masinii.
     
     Urmatoarele comenzi de control al Tranzactiilor sunt valide in
     PL/SQL, dar variabilele de mediu ale masinii pot impune unele
     restrictii de utilizare:

        COMMIT          [ WORK ]
        ROLLBACK        [TO savepoint]
        SAVEPOINT
        SET TRANSACTION

     
     
     Comenzi de blocare, care dureaza pina la sfirsitul tranzactiei pot
     fi de asemenea include in bloc. Acestea sunt : LOCK TABLE, SELECT ..
     FOR, UPDATE.
     
     Exemple:
     
     
     In exemplul urmator, este prezentat un "rollback" din variabilele de
     mediu ale masinii ( de exemplu SQL*Plus) pentru a anula orice
     actiune DML aplicata in bloc de la punctul salvat(savepoint).

        BEGIN
          ...
          SAVEPOINT ok_so_far;
          ...
        END;

        ROLLBACK    TO  ok_so_far;


     
     
     Exemplul urmator "rools back" toate toate tranzactiile, incluzind
     orice modificare facute in exteriorul blocului, faca functia UPDATE
     nu gaseste nici o linie. Altfel, se transmit toate schimbarile in
     tranzactie.

        DELETE FROM emp WHERE job = 'CLERK';

        BEGIN
          UPDATE emp SET sal = 8000 WHERE job = 'CAPTAIN' ;
          IF SQL%NOTFOUND
                THEN  ROLLBACK;
                ELSE  COMMIT;
          END IF;
        END;


     
     
     Retineti ca SQL*Plus trateaza un bloc PL/SQL ca o singura
     instructiune. Daca optiunea AUTOCOMMIT este activa (ON), actiunile
     realizate in bloc nu sunt realizate pina cind nu este procesat si
     sfirsitul blocului. In mod opus, daca o actiune DML cauzeaza o
     exceptie netratata atunci blocul se va termina cu esec si practic se
     vor anula actiunile DML din cadrul blocului.
   
     * Instructiunea SELECT in PL/SQL
       
     SELECT se foloseste ca instructiune executabila in cadrul blocurilor
     PL/SQL si i se aplica urmatoarea regula:

        Interogarile  trebuie sa intoarca o coloana si numai una, altfel
        o eroare va fi generata.


     
     
     De acea, SELECT-ul care nu intoarce nici o linie, sau mai mult de o
     linie cauzeaza una din urmatoarele erori:

        ORA-01403  No Data Found (ANSI error 100)
                   Data negasita
        ORA-01422  Exact fetch returns more than requested
                   number of rows

     
     
     PL/SQL trateaza aceste erori prin tratarea exceptiilor, care pot fi
     trasate in sectiunea EXCEPTION a blocului. Dar despre aceste
     tratatea acestor exceptii vom vorbi mai tirziu in cadrul acestui
     unit. In mod normal ar trebui folosit SELECT pentru a obtine o
     singura linie. Urmatoarele clauze sunt permise:

        SELECT          articol, articol, ...
        INTO            variabila, variabila, ...
        FROM            tabela, tabela, ...
        [WHERE          conditie(ii) ]
        [GROUP BY       articol, articol, ... ]
        [HAVING         conditie(ii)]
        [FOR UPDATE];
   
     * Atributele %TYPE
       
     Cind variabile PL/SQL sunt declarate pentru incarcare ulterioara cu
     valoarea coloanei, trebuie sa va asigurati ca variabila este
     compatibila ca tip de data cu coloana si ca este suficient de mare
     pentru a stoca valoarea de pe coloana. Daca nu este atunci PL/SQL va
     genera eroare.
     
     Decit sa incercati sa ghiciti tipul si precizia pentru o variabila,
     puteti sa va bazati pe definitia coloaneo din Data Dictionary. Acest
     lucru se face folosind atributul %TYPE. Atributul este prefixat cu
     numele tabelei si al coloanei, si folosit cind este nevoie de tipul
     datei in declaratia variabilei:

        identificator    numetabela.coloana%TYPE

     
     
     Folosind aceasta metoda, tipul de data si dimensiunea sunt
     determinate atunci cind blocul este compilat.
     
     Exemplu:

        DECLARE
          v_deptno      dept.deptno%TYPE;
          v_loc         dept,loc%TYPE;

        BEGIN
          SELECT        deptno,   loc
          INTO          v_deptno, v_loc
          FROM          dept ...

        END;


     
     
     
     
INTRODUCERE IN TRATAREA EXCEPTIILOR (ERORILOR)

     
          + Ce sunt exceptiile ?
               o Conditii care vor termina un bloc.
          + Doua tipuri principale
               o Predefinite
               o Definite de USER
          + Tratarea exceptiilor
               o Previne erorile de a se propaga in afara blocului
               o Defineste actiunile ce se vor face cind exceptia apare
                 
     

        WHEN    identificator_exceptie THEN actiuni;
   
     * Introducere in tratarea exceptiilor
       
     Asa cum am vazut pe scurt mai inainte, exceptiile sunt
     identificatori in PL/SQL care pot dispare in timpul executiei unui
     bloc pentru a termina partea principala a actiunii. Un bloc se va
     termina INTOTDEAUNA cind apare o exceptie, dar se poate specifica un
     mod de tratare pentru a realiza actiunea finala inainte ca blocul sa
     se termine. daca exceptia este tratata, atunci exceptia nu se
     propaga si in afara blocului.
     
     Sunt doua clase principale de exceptii:
     
                Predefinite
                
        Acestea sunt predefinite de PL/SQL si au asociate coduri
                specifice de eroare.
                Definite de utilizator
                
        Sunt declarate in bloc de utilizator. Acestea vor fi folosite
                numai daca se cere acest lucru, dar pot fi de asemenea
                asociate cu coduri de eroare daca se doreste acest lucru.
                
     
     
     In acest unit ne vom concentra asupra exceptiilor interne
     predefinite. Acestea sunt urmatoarele:

        Numele exceptiei                Codul de eroare ORACLE
        -----------------               ------------------------------
        DUP_VA_ON_INDEX                 -1
        INVALID_CURSOR                  -1001
        INVALID_NUMBER                  -1722
        LOGIN_DENIED                    -1017
        NO_DATA_FOUND                   -1403 (ANSI +100)
        NOT_LOGGED_ON                   -1012
        PROGRAM_ERROR                   -6501
        STORAGE_ERROR                   -6500
        TIMEOUT_IN_RESPONSE             -51
        TOO_MANY_ROWS                   -1422
        VALUE_ERROR                     -6502
        ZERO_DEVIDE                     -1476
        CURSOR_ALREADY_OPEN             -6511
        TRANSACTION_BACKED_OUT          -61

   
   
Identificatori exceptie

   
   
   Daca orice tip de exceptie este generat controlul este trecut
   sectiunii EXCEPTION a blocului in care exceptia apare. Daca exceptia
   nu e minuita aici sau daca nu exista nici o sectiune EXCEPTION atunci
   blocul se termina cu o exceptie `Unhandled` care poate avea
   repercursiuni in mediul de inchidere.


EXEMPLU:

        BEGIN

        INSERT INTO dept (deptno, dname) VALUES (50, `CLEANING`);

        INSERT INTO dept (deptno, dname) VALUES (50, `TRAINING`);

        -- Exception DUP_VAL_ON_INDEX raised here



        ...



        END;

          -- Block terminates which Unhandled Exception - ORA -00001

   
   
   Pentru a prinde asemenea evenimente si a preveni propagarea
   exceptiilor catre mediile de inchidere sau blocuri se poate defini
   identificatorul exceptie in sectiunea EXCEPTION.


Sintaxa:

        WHEN exception-identifier THEN actions;

   unde `actions` poate fi una sau mai multe declaratii PL/SQL sau SQL
   fiecare terminata cu ':'. Actiunile unui identificator exceptie sint
   delimitate fie de sfirsitul blocului (END) fie de inceputul unui alt
   identificator exceptie (WHEN).
   
   Principalele exceptii care pot apare ca un rezultat al declaratiei
   SELECT sint NO_DATA_FOUND (fara linii returnate) si TOO_MANY_ROWS (mai
   mult decit o linie returnata).


Exemplu:

        DECLARE

        v_ename emp.ename%TYPE;

        v_job emp.job%TYPE;

        BEGIN

        SELECT ename, job

        INTO v_ename, v_job

        FROM emp

        WHERE hiredate BETWEEN '1-JAN-92' AND '31-DEC-92';

         ...

        EXCEPTION

        WHEN no_data_found THEN

                           INSERT INTO error_tab

                           VALUES ('Nobody in 92');

        WHEN too_many_rows THEN

                           INSERT INTO error_tab

                           VALUES ('More then one person in 92');

        END;

   
   
   Notati ca, citiva identificatori exceptie pot fi definiti pentru bloc
   fiecare cu setul propriu de actiuni. Totusi cind o exceptie apare
   numai un identificator va fi procesat inainte de a parasi blocul.
   
Identificatorii exceptie 'WHEN OTHERS'

   
   
   Desi sectiunea EXCEPTION din exemplul de sus va prinde cele doua
   exceptii specificate alte tipuri de exceptie se vor strecura in retea
   mai bine decit definirea unui identificator separat pentru fiecare tip
   de exceptie se poate folosi identificatorul exceptie 'WHEN OTHERS'
   care minuieste toate erorile neminuite inca in bloc. Daca e folosit
   'WHWN OTHERS' trebuie sa fie codat dupa orice alt identificator
   exceptie din bloc.


Exemplu:

        BEGIN

        SAVEPOINT  so_far_so_good;

        INSERT INTO statistics_tab VALUES (18, 25,91);

        EXCEPTION

        WHEN dup_val_on_index THEN

                                  INSERT INTO error_tab VALUES (`Error

during block`);

END;

   
   
Functii pentru prinderea erorii

   
   
   Cind o exceptie a aparut, veti dori evaluarea codului erorii asociate
   sau mesajul eroare.Aceasta poate fi important in minuirea exceptiilor
   folosind `WHEN OTHERS`, pina cind veti dori a decide ce actiune sa fie
   dupa eroare. PL/SQL furnizeaza 2 functii in acest scop :
   
   SQLCODE
          intoarce nr. erorii asociata cu exceptia care a aparut (in
          cazul lui NO_DATA_FOUND acesta va fi codul ANSI +100).Daca e
          folosit un singur identificator EXCEPTION, functia va intoarce
          0.
          
   SQLERRM
          intoarce mesajul complet de eroare asociat cu exceptia
          (incluzind nr. erorii)
          
   
   
   SQLCODE intoarce date numerice, si poate fi asignat unei variabile
   NUMBER cu precizie implicita.SQLERRM intoarce date caracter.Este
   recomandat a trunchia valoarea la o lungime cnoscuta inainte de a
   incerca sa o scriem intro variabila.


Exemplu:

DECLARE

 error-message CHAR (100);

 error-code  NUMBER;

BEGIN

...

EXCEPTION

WHEN OTHERS  THEN

              error_message :=SUBSTR(SQLERM,1,100);

              error_code :=SQLCODE;

              INSERT INTO errors VALUES (error_message, error_code);

              END;

   
   
   SQLERRM are un parametru optional care accepta numarul erorii a carui
   mesaj e returnat.
   
   NOTA: SQLERRM si SQLCODE nu pot fi folosite direct ca parti ale unei
   declaratii INSERT; valorile lor trebuie sa fie trecute prima data unei
   variabile. 
   
Cum se propaga exceptiile?

   
   
   Cind un subbloc minuieste o exceptie se termina normal si controlul va
   fi rezumat in blocul e inchidere imediat dupa END-ul subblocului.
   
   Totusi cind o exceptie este generata si blocul curent nu are un
   identificator pentru el exceptia se propaga. Aceasta inseamna ca
   exceptia se reproduce ea insasi in blocuri de inchidere succesive pina
   cind un identificator e gasit. Daca nici unul dintre blocuri nu
   minuieste exceptia atunci o exceptie Unhandled este produsa in mediul
   gazda.
   
   Aceasta saritura intre sectiunile EXCEPTION de la blocurile interne
   catre cele externe produce actiunile executabile ramase in aceste
   blocuri sa fie trecute (actiunile intre END-ul unui subbloc si
   keyword-ul EXCEPTION al blocului de inchidere nu sint procesate).
EXEMPLU:

        BEGIN

        ...

        BEGIN

        ...

        --Exception X raised at this point

        EXCEPTION

        --No handler that deals whith exception X

        END;

        ...

        EXCEPTION

        --Handler for X present here (i.e. The Buck stops here!)

        END;

   
   
   Un avantaj al acestei comportari este ca anumite declaratii care cer
   minuirea propriilor erori poate fi inclus in propriul bloc lasind
   minuirea exceptiilor generale blocurilor de inchidere.


EXEMPLU:

        DECLARE

          e_mess CHAR(80)

        BEGIN

                DECLARE

                  v1 NUMBER(4);

                BEGIN

                  SELECT empno INTO v1 FROM emp WHERE job = 'PRESIDENT';

                EXCEPTION

                  WHEN too_many_rows THEN

                        INSERT INTO job_errors VALUES ('More than one

                                        President!');

                END;



                DECLARE

                  v1 NUMBER (4);

                BEGIN

                  SELECT empno INTO v1 FROM emp WHERE job = 'MANAGER';

                EXCEPTION

                  WHEN too_many_rows THEN

                        INSERT INTO job_errors VALUES ('More than one

                                                Manager');

                END;

                EXCEPTION

                        WHEN OTHERS THEN

                                e_mess := SUBSTR(SQLERRM, 1, 80);

                                INSERT INTO general VALUES (e_mess);

                END;

   
   
Exercitii

   
   
   Alterati blocul dvs. produs la Cap. 20 Ex. 2.Redefiniti variabila
   PL/SQL ca NUMBER(1).Ce se intimpla daca 2 valori de intrare are
   valorile 4 si 2?
   
   Adaugati un Identificator Exceptie la blocul care inregistreaza un
   mesaj explicit in Messages pentru orice tip de exceptie care poate
   apare. Apoi lansati blocul din nou.
   
   Scrieti un script PL/SQL care primeste la rulare un singur parammetru,
   unde este furnizt un tip de slujba.


Ex.   @UNI3_FILE MANAGER

   
   
   Blocul PL/SQL din fisier trebuie sa SELECT-ezr rinduri din tabela EMP
   care are tipul job-ului in coloana JOB. (Va referiti la parametru ca
   '&1'). Trimiteti un mesaj in tabela MESSAGES, in functie de faptul ca
   se returneaza unul,niciunul sau mai multe rinduri.
Ex.

'Jobtype found once'

'Jobtype not found'

'Jobtype found more than once'

   Inregistrati Jobtype si in tabela MESSAGES, s executati tranzactia,
   indiferent de mesajul produs. 
   
Solutii.

   
   
   O exceptie VALUE_ERROR este lansata daca valoarea este prea mare pt.
   variabila.


      DECLARE

              V_RESULT NU MBER(9.2) ;

              V_MESSAGE CHAR(60);

      BEGIN

            V_RESULT := &main_var ** &exponent ;

            -- or POWER (&main_var, &exponent);

            INSERT INTO messages (numcol1) VALUES (V_RESULT);

      EXCEPTION

                WHEN OTHERS THEN V_MESSAGE := SUBSTR(SQLERRM,1,60);

                                 INSERT INTO messages (charcol1);

                                 VALUES (V_MESSAGE);

      END;







      DECLARE

              JOBTYPE emp.job%TYPE;

      BEGIN

            SELECT job INTO JOBTYPE

            FROM emp WHERE job = '&1' ;

            INSERT INTO messages (charcol1, charcol2)

                 VALUES ('&1', 'Jobtype found once');

            COMMIT;

      EXCEPTION

            WHEN no_data_found THEN

                 INSERT INTO messages (charcol1, charcol2)

                 VALUES ('&1', 'Jobtype not found');

            COMMIT;

            WHEN too_many_rows THEN

                 INSERT INTO messages (charcol1, charcol2)

                 VALUES ('&1', 'Jobtype found more than once');

            COMMIT;

            END;

Inapoi la cuprins.

BogSoft 1999