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 ?
  - Conditii care vor termina un bloc.
- Doua tipuri principale
  - Predefinite
  - Definite de USER
- Tratarea exceptiilor
  - Previne erorile de a se propaga in afara blocului
  - 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;