v5h22hb
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

aLOCK TABLEi

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 a WORK i

ROLLBACK aTO savepointi

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, ...

aWHERE conditie(ii) i

aGROUP BY articol, articol, ... i

aHAVING conditie(ii)i

aFOR UPDATEi;

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;