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;
BogSoft 1999