Vederi

Vederi

O vedere este un "tabel logic", fiind de asemenea organizata in randuri si coloane. Ea preia rezultatul unei interogari si il trateaza ca pe un tabel, de unde si numele de tabel logic. De exemplu, daca din tabelul salariat se doreste vizualizarea doar a cinci coloane (cod_salariat, nume, prenume, data_nastere, cod_dept) si numai a randurilor pentru care cod_tara = 40, se poate crea o vedere care contine numai aceste linii si coloane, vezi figura 1.

tabel de baza salariat

vedere salariat_40

Figura 1: Un exemplu de vedere

O vedere poate fi construita din una sau mai multe tabele sau chiar alte vederi si permite ca datele din mai multe tabele sa fie rearanjate, reunite logic sau ca noi date sa fie calculate pe baza acestora. Din punct de vedere al aplicatiei, vederile au acelasi comportament ca si tabelele: vederile pot si interogate si, cu anumite exceptii care vor fi mentionate mai tarziu, asupra vederilor se pot efectua operatii DML (INSERT, DELETE, UPDATE). Vederea este un instrument foarte puternic pentru dezvoltatorul de aplicatii. Ea poate fi bazata pe mai multe tabele sau vederi care se pot gasi pe masini diferite sau pot apartine unor utilizatori diferiti, acestea fiind prezentate ca si cum ar fi un singur tabel logic.

Spre deosebire de tabel, vederea nu stocheaza date si nici nu are alocat vreun spatiu de stocare; vederea doar extrage sau deriva datele din tabelele la care aceasta se refera. Aceste tabele poarta numele de tabele de baza ale vederii. Acestea pot fi tabele sau pot fi ele insele vederi. Oracle stocheaza definitia vederii in dictionarul de date sub forma textului interogarii care defineste vederea, de aceea o vedere poate fi gandita ca o "interogare stocata". Pentru vizualizarea definitiilor vederilor se poate folosi coloana TEXT a vederilor ALL_VIEWS DBA_VIEW si USER_VIEW din dictionarul de date.

Vederile pot fi interogate exact la fel ca tabelele, folosind comanda SELECT. Cand o interogare SQL se refera la o vedere, Oracle combina aceasta interogare cu interogarea care defineste vederea.

In general, vederile sunt create pentru urmatoarele scopuri:

Asigurarea unui nivel mai mare de securitate a bazei de date prin limitarea accesului la un numar mai restrans de linii si coloane ale unui tabel.

Simplificarea interogarilor SQL, permitand vizualizarea unor date care in mod normal necesita interogari SQL destul de complicate. De exemplu, o vedere poate permite utilizatorilor vizualizarea datelor din mai multe tabele fara ca acestia sa fie obligati sa foloseasca un SELECT pe mai multe tabele.

Prezentarea diferita a datelor fata de cea din tabelele de baza. De exemplu, coloana unei vederi poate avea alt nume decat coloana corespunzatoare din tabelul de baza, acest lucru neafectand in nici un fel tabelul de baza.

Efectuarea unor interogari care nu ar putea fi efectuate fara existenta unei vederi. De exemplu, este posibila definirea unei vederi care realizeaza un join intre o vedere care include clauza GROUP BY si un alt tabel; acest lucru nu poate fi facut intr-o singura interogare.

Pentru a mentine calcule mai complicate. Interogarea care defineste vederea poate efectua calcule complicate aspra datelor dintr-un tabel; prin mentinerea acestei interogari ca o vedere, calculele pot fi efectuate de fiecare data cand se face referire la vedere.

Asigurarea transparenta a datelor pentru anumiti utilizatori si aplicatii. O vedere poate contine date din mai multe tabele, care pot fi proprietatea mai multor utilizatori.

1 Crearea vederilor

O vedere este creata folosind comanda SQL create view. De exemplu, pentru vederea de mai sus vom avea:

create view salariat_40

as select cod_salariat, nume,    prenume, salariu, cod_dept

from salariat

where cod_tara = 40;

O sintaxa simplificata a comenzii create view este urmatoarea:

CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW nume_vedere

alias alias

AS subinterogare

[WITH READ ONLY]

[WITH CHECK OPTION [CONSTRAINT nume_cosntrangere

unde

OR REPLACE recreeaza vederea daca ea exista deja. Aceasta optiune poate fi folosita pentru a schimba definitia unei vederi existente fara a o distruge in prealabil. Avantajul recrearii vederii prin optiunea REPLACE este ca in acest caz se pastreaza toate privilegiile acordate asupra acestei vederi. De exemplu, sa presupunem ca dupa crearea unei vederi, au fost acordate privilegii asupra vederii pentru anumite roluri sau pentru anumiti utilizatori. Daca dupa aceea vederea este distrusa si recreata, atunci toate privilegiile asupra vederii au fost pierdute si trebuie acordate din nou. Daca vederea este insa recreata folosind optiunea OR REPLACE, atunci privilegiile acordate sunt pastrate si nu mai este necesara acordarea lor inca o data.

FORCE este o optiune care permite crearea vederii indiferent daca tabelele de baza si coloanele la care se face referire exista sau nu, sau daca utilizatorul poseda sau nu privilegiile corespunzatoare in legatura cu tabelele respective. Optiunea opusa, NOFORCE, creeaza vederea numai daca tabelele de baza exista si daca utilizatorul poseda privilegiile corespunzatoare in legatura cu tabelele respective; NOFORCE este optiunea implicita. Daca se foloseste optiunea FORCE si un tabel de baza nu exista sau una dintre coloane nu este valida, atunci Oracle va crea vederea cu erori de compilare. Daca mai tarziu tabelul in cauza este creat sau coloana este corectata, atunci vederea poate fi folosita, Oracle recompiland-o dinamic inainte de folosire.

alias specifica numele expresiilor selectate de interogarea vederii. Numarul alias-urilor trebuie sa fie acelasi cu numarul de expresii selectate de catre interogarea vederii. Un alias trebuie sa fie unic in cadrul unei interogari. Daca sunt omise alias-urile, Oracle va folosi denumirile coloanelor din interogare. Atunci cand interogarea vederii contine si expresii, nu doar simple coloane, trebuie folosite alias-uri.

AS indica interogarea vederii. Aceasta poate fi orice instructiune SELECT care nu contine clauzele ORDER BY si FOR UPDATE

optiunea WITH READ ONLY asigura ca nici o operatie DML (inserare, stergere, modificare) nu va fi asigurata asupra vizualizarii.

WITH CHECK OPTION este o constrangere care arata ca toate actualizarile efectuate prin intermediul vederii vor afecta tabelele de baza numai daca actualizarile respective vor avea ca rezultat numai randuri care pot fi vizualizate prin intermediul vederii. CONSTRAINT furnizeaza un nume pentru constrangerea CHECK OPTION. Asupra acestor optiuni vom reveni putin mai tarziu.

Exemplul urmator ilustreaza crearea unei vederi care contine codul, numele, prenumele, salariul si sporul salarial pentru toti salariatii din departamentul 1 si tara cu codul 40.

create or replace view salariat_1

(cod, nume, prenume, salariu, spor_salariu)

as select cod_salariat, nume,    prenume, salariu, salariu*0.1

from salariat

where cod_dept = 1

and cod_tara = 40;

Daca datele din tabelul salariat sunt cele din figura 1, atunci vederea salariat_1 va contine urmatoarele date:

salariat_1

Figura 2

2 Operatii DML asupra vederilor

In momentul in care in tabelele de baza sunt adaugate noi date sau sunt actualizate sau sterse cele existente, aceste modificari se reflecta corespunzator in vederile bazate pe aceste tabele. Acest lucru este adevarat si viceversa, cu singura mentiune ca exista anumite restrictii la inserarea, actualizarea sau stergerea datelor dintr-o vedere. Aceste restrictii sunt redate pe scurt in continuare:

Nu pot fi inserate, sterse sau actualizate datele din vederi care contin una dintre urmatoarele:

operatorul DISTINCT (pentru eliminarea duplicatelor);

clauzele GROUP BY HAVING START WITH CONNECT BY

pseudo-coloana ROWNUM (aceasta pseudo-coloana contine un numar ce indica ordinea in care Oracle selecteaza inregistrarile dintr-un tabel);

functiile de grup (COUNT SUM MAX MIN AVG STDDEV VARIANCE GLB

operatorii de multimi (UNION UNION ALL INTERSECT MINUS

Nu pot fi inserate sau actualizate valorile coloanelor care rezulta prin calcul, de exemplu coloana spor_salariu de mai sus. De asemenea nu se pot efectua operatii DML asupra valorilor coloanelor care au fost calculate folosind functia DECODE

Nu pot fi inserate sau actualizate date care ar incalca constrangerile din tabele de baza. De exemplu, daca in tabela salariat coloana nume este definita ca NOT NULL, atunci in orice vedere bazata pe acest tabel care nu contine coloana nume (de exemplu salariat_exemplu definita mai jos) nu va fi posibila inserarea de date deoarece aceasta ar duce la incalcarea constrangerii NOT NULL

create view salariat_exemplu

as select cod_salariat, prenume, data_nastere

from salariat;

In versiunea Oracle 7 nu pot fi inserate, sterse sau actualizate datele din vederi bazate pe mai multe tabele; in versiunea Oracle 8 acest lucru este posibil, insa cu anumite exceptii, care vor fi discutate intr-una dintre sectiunile urmatoare.

In plus fata de regulile de mai sus, la crearea unei vederi se poate utiliza clauza WITH CHECK OPTION care impune ca singurele date care pot fi inserate sau actualizate prin intermediul vederii sa fie numai acelea care pot fi vizualizate de aceasta.

Pentru a clarifica acest aspect, sa consideram vederea salariat_2000 definita mai jos si posibilitatile existente de a insera randuri in aceasta vedere.

CREATE VIEW salariat_2000

AS SELECT cod_salariat, nume, prenume, data_nastere, salariu

FROM salariat

WHERE salariu > 2000;

Prima comanda SQL de mai jos va duce la inserarea unui rand in tabela de baza salariat, care poate fi vizualizat si prin intermediul vederii salariat_2000, deoarece valoarea corespunzatoare coloanei salariu este mai mare decat 2000. Pe de alta parte, a doua comanda SQL va duce la inserarea unui rand in tabelul de baza care nu poate fi insa vizualizat prin intermediul vederii.

insert into salariat_2000

(cod_salariat, nume, prenume, data_nastere, salariu)

values (106, 'Ionescu', 'Vasile', '11-JUL-60' , 3000);

insert into salariat_2000

(cod_salariat, nume, prenume, data_nastere, salariu)

values (107, 'Popescu', 'Viorel', '22-JAN-69' , 1000);

De exemplu, daca inaintea executarii acestor comenzi in tabelul salariat exista datele din figura 1, atunci dupa executarea acestor comenzi, datele din tabelul salariat si vederea salariat_2000 vor fi urmatoarele:

tabel de baza salariat

vedere salariat_2000

Figura 3

Sa presupunem acum ca aceeasi vedere, salariat_2000, este creata folosind clauza WITH CHECK OPTION

create view salariat_2000

as select cod_salariat, nume,    prenume, data_nastere, salariu

from salariat

where salariu > 2000

with check option

Si in acest caz, inserarea in vedere a unui rand pentru care valoarea coloanei salariu este mai mare decat 2000 se face fara probleme. Pe de alta parte insa, orice incercare de a insera in vedere randuri pentru care salariul este mai mic sau egal cu 2000 va produce o eroare indicand incalcarea constrangerii WITH CHECK OPTION, comanda nemodificand tabelul de baza. De exemplu, executarea celor doua comenzi de INSERT de mai sus va avea in acest caz ca rezultat urmatoarele date din salariat si salariat_2000

tabel de baza salariat

vedere salariat_2000

Figura 4.

In cazul folosirii constrangerii CHECK OPTION, acesteia i se poate atribui un nume folosind optiunea CONSTRAINT din cadrul comenzii CREATE VIEW. De exemplu:

create view salariat_2000

as select cod_salariat, nume,    prenume, data_nastere, salariu

from salariat

where salariu > 2000

with check option CONSTRAINT salariu_2000;

Daca optiunea CONSTRAINT este omisa, Oracle atribuie in mod automat constrangerii CHECH OPTION un nume de forma "SYS_Cn" unde n este un intreg care face ca numele constrangerii sa fie unic in baza de date.

3 Operatii DML asupra vederilor bazate pe mai multe tabele (Join-Views)

Asa cum am mentionat mai sus, in versiunea Oracle8 este posibila inserarea, actualizarea sau stergerea datelor dintr-o vedere bazata pe mai multe tabele, cu anumite restrictii insa. Alaturi de restrictiile generale, aplicabile tuturor vederilor, prezentate in sectiunea precedenta, exista si restrictii specifice numai vederilor bazate pe mai multe tabele. Acestea sunt redate de urmatoarele reguli:

Regula generala: Orice operatie de INSERT UPDATE sau DELETE pe o vedere bazata pe mai multe vederi poate modifica datele din doar unul dintre tabelele de baza.

Inainte de a enunta regulile specifice pentru fiecare dintre operatiile INSERT UPDATE sau DELETE este necesara definirea conceptului de tabel protejat de cheie (key-preserved table). Data fiind o vedere bazata pe mai multe tabele, un tabel de baza al vederii este protejat prin cheie daca orice cheie selectata a tabelului este de asemenea si cheie a vederii. Deci, un tabel protejat prin cheie este un tabel ale carui chei se pastreaza si la nivel de vedere. Trebuie retinut ca, pentru a fi protejat prin cheie, nu este necesar ca un tabel sa aiba toate cheile selectate in vedere. Este suficient ca, atunci cand cheia tabelului este selectata, aceasta sa fie si cheie a vederii. Proprietatea unui tabel de a fi protejat prin cheie nu este o proprietate a datelor din tabel, ci o proprietate a schemei. In exemplul de mai jos, daca pentru fiecare combinatie (cod_tara cod_dept) ar exista un singur salariat, atunci combinatia (cod_tara cod_dept) din tabelul departament ar fi unica pentru datele din vederea rezultata, dar tabelul departament tot nu ar fi protejat prin cheie.

Pentru a ilustra aceasta notiune cat si regulile urmatoare, sa consideram o definitie simplificata a tabelelor departament si salariat in care pastram doar constrangerile de cheie primara si integritate referentiala.

create table departament(

cod_dept number(10),

cod_tara number(10),

nume_dept varchar2(10),

primary key(cod_dept, cod_tara));

create table salariat(

cod_salariat number(10) primary key,

nume varchar2(10),

prenume varchar2(10),

data_nastere date,

manager number(10) REFERENCES salariat(cod_salariat),

salariu number(10),

cod_dept number(10),

cod_tara number(10),

foreign key(cod_dept, cod_tara) references departament(cod_dept, cod_tara));

Sa mai consideram si urmatoarea vedere bazata pe aceste doua tabele:

create view sal_dept

(cod_salariat, nume, prenume, salariu, cod_dept, cod_tara, nume_dept)

as select s.cod_salariat, s.nume, s.prenume, s.salariu, s.cod_dept, s.cod_tara, d.nume_dept

from salariat s, departament d

where s.cod_dept = d.cod_dept

and s.cod_tara = d.cod_tara;

In exemplul de mai sus, tabelul salariat este protejat prin cheie. Regulile specifice pentru fiecare dintre operatiile DML (INSERT UPDATE sau DELETE) sunt redate in continuare:

Reguli de actualizare (UPDATE):

Toate coloanele care pot fi actualizate printr-o vedere trebuie sa corespunda coloanelor dintr-un tabel protejat prin cheie. Daca o coloana provine dintr-o tabela neprotejata prin cheie, atunci Oracle nu va putea identifica in mod unic inregistrarea care va trebui actualizata. De exemplu, comanda SQL de mai jos se va executa cu succes.

UPDATE sal_dept

SET salariu = salariu + 100

WHERE cod_tara = 40;

Pe de alta parte, comanda urmatoare va esua:

UPDATE sal_dept

SET nume_dept = 'IT'

WHERE cod_dept = 1

AND cod_tara = 40;

Comanda de mai sus va esua pentru ca ea incearca sa actualizeze o coloana din tabelul departament, tabel care nu este protejat prin cheie.

Daca vederea este definita folosind clauza WITH CHECK OPTION, atunci toate coloanele de jonctiune si toate coloanele tabelelor repetate nu pot fi modificate. De exemplu, daca vederea sal_dept ar fi fost definita folosind clauza WITH CHECK OPTION, atunci comanda urmatoare va esua deoarece incearca modificarea unei coloane de jonctiune.

UPDATE sal_dept

SET cod_dept = 2

WHERE cod_salariat = 101

AND cod_tara = 40;

Reguli de inserare (INSERT):

O comanda INSERT nu poate sa se refere in mod explicit sau implicit la coloane dintr-un tabel care nu este protejat prin cheie. De exemplu, daca in tabelul departament exista o linie cu cod_dept si cod_tara = 40, atunci urmatoarea comanda SQL va fi executata cu succes:

INSERT INTO sal_dept

(cod_salariat, nume, cod_dept, cod_tara)

VALUES(110, 'Marinescu', 3, 40);

In caz contrar, comanda va esua, fiind incalcata constrangerea de integritate referentiala. Pe de alta parte, comanda urmatoare va esua deoarece ea incearca inserarea de date in mai multe tabele.

INSERT INTO sal_dept

(cod_salariat, nume, nume_dept)

VALUES(111, 'Georgescu', 'IT');

Daca o vedere este definita folosind clauza WITH CHECK OPTION, atunci nu se pot executa comenzi INSERT in acea vedere.

Reguli de stergere (DELETE)

Randurile dintr-o vedere pot fi sterse numai daca in jonctiune exista un tabel protejat prin cheie si numai unul. Daca ar exista mai multe tabele, Oracle nu ar sti din care tabel sa stearga randul. De exemplu, comanda SQL de mai jos se va executa cu succes deoarece ea poate fi tradusa intr-o operatie de stergere pe tabelul salariat

DELETE FROM sal_dept

WHERE nume = 'Popescu';

Pe de alta parte, daca se incearca executarea unei comenzi DELETE pe vederea de mai jos, ea va esua deoarece ambele tabele de baza, s1 si s2, sunt protejate prin cheie:

CREATE VIEW emp_emp AS

SELECT s1.nume, sprenume

FROM salariat s1, salariat s2

WHERE s1.cod_salariat = scod_salariat;

Daca vederea este definita folosind clauza WITH CHECK OPTION, atunci nu pot fi sterse randuri din vedere. De exemplu, nu se poate executa o instructiune DELETE pe vederea de mai jos deoarece ea este definita ca auto-jonctiune a unui tabel protejat prin cheie.

CREATE VIEW emp_manag AS

SELECT s1.nume, snume nume_manager

FROM salariat s1, salariat s2

WHERE s1.manager = scod_salariat

WITH CHECK OPTION;

Vederile ALL_UPDATABLE_COLUMNS DBA_UPDATABLE_COLUMNS si USER_UPDATABLE_COLUMNS ale dictionarului de date contin informatii care arata care dintre coloanele vederilor existente pot fi actualizate. Vederile care nu pot fi actualizate direct pot fi actualizate folosind triggere INSTEAD OF, vezi sectiunea 9.1

4 Recompilarea vederilor

Recompilarea unei vederi permite detectarea eventualelor erori referitoare la vederea respectiva inaintea executarii vederii. Dupa fiecare modificare a tabelelor de baza este recomandabil ca vederea sa se recompileze. Acest lucru se poate face folosind comanda SQL alter view compile

alter view salariat_2000 compile;

5 Distrugerea vederilor

Pentru distrugerea unei vederi se foloseste comanda drop view

drop view salariat_2000;

Utilizarea unei vederi este cateodata o sabie cu doua taisuri. Desi vederile constituie un instrument extrem de convenabil pentru dezvoltatorul de aplicatii, ele pot avea un impact negativ asupra performantei acestora. Daca pentru vederi simple (de exemplu simple copii ale unui tabel) impactul asupra performantei nu este sesizabil, pentru vederi complexe, care cuprind interogari pe mai multe tabele, acest impact poate fi foarte sever. De aceea folosirea vederilor trebuie planificata cu grija, pentru a se vedea daca avantajul creat de simplitatea in manipulare a acestora compenseaza impactul negativ asupra performantei.