Gestiunea view-urilor

Gestiunea view-urilor

Un view este o tabela virtuala, care nu contine date, dar ii confera utilizatorului posibilitatea de vizualizare a datelor din baza de date dintr-o anumita perspectiva. Pentru definirea unui view se foloseste o cerere SQL.

View -urile pot fi folosite in urmatoarele scopuri:

limiteaza accesul unor categorii de utilizatori la anumite date, mai mult sau mai putin confidentiale;

permite simplificarea viziunii utilizatorilor asupra bazei de date;

asigura securitatea si confidentialitatea datelor. Fiecarui utilizator al bazei de date ii sunt asignate anumite drepturi (GRANT/REVOKE) ceea ce determina o limitare a accesului la baza de date, la obiectele bazei de date si la efectuarea unor operatii asupra obiectelor bazei de date.

Asupra unui view se pot efectua ulterior aceleasi operatii ca si asupra unei tabele, dar cu deosebirea ca view-ul nu contine date, ci este o reflectare dinamica a unei tabele. Astfel, o modificare a datelor dintr-o tabela este automat reflectata in view-urile definite pe acea tabela. Un view se recreeaza de fiecare data cand este apelat, pe baza definitiei sale care se afla stocata in dictionarul bazei de date.

Operatiile aplicate asupra view-urilor sunt:

crearea unui view cu comanda CREATE VIEW;

interogarea view-urilor - prin folosirea unei fraze SELECT;

actualizarea datelor dintr-un view folosind comanda UPDATE:

adaugarea de noi date folosind comanda INSERT;

stergerea datelor dintr-un view cu comanda DELETE.

Crearea unui view - permite definirea unei 'ferestre' prin care se pot consulta datele stocate in tabele.

CREATE VIEW nume-view [<lista atribute>]

AS  SELECT secventa-select

[WITH CHECK OPTION]

Se poate observa in sintaxa comenzii ca utilizatorului i se permite:

specificarea denumirii view-ului si optional a denumirii atributelor din view, in cazul in care se doreste redenumirea atributelor specificate in instructiunea SELECT;

specificarea interogarii (fraza SELECT);

specificarea optionala a unei conditii suplimentare impuse view-ului, astfel incat sa poata fi realizata actualizarea sau inserarea datelor in view (prin utilizarea clauzei WITH CHECK OPTION).

Definirea unui view este stocata in dictionarul de date al sistemului.

Odata definit view-ul, drepturile de acces ale utilizatorilor decurg pe de o parte, din privilegiile acordate utilizatorilor asupra relatiilor pentru care este definit view-ul si, pe de alta parte, din posibilitatea de actualizare a view-ului.

Crearea unui view simplu, care va permite selectarea unei multimi de tupluri dintr-o tabela, se realizeaza prin comanda:

CREATE VIEW FZ1 AS

SELECT CODFZ, DENFZ, ADRESA FROM FURNIZOR WHERE ADRESA='BUCURESTI';

View created

Sursa de date o reprezinta tabela Furnizor iar rezultatul returnat consta in afisarea codului, denumirii si adresei furnizorilor bucuresteni. Cand un view este creat, e afisat un mesaj 'View Created'. Ulterior, view-ul poate fi vizualizat ca orice tabela:

SELECT* FROM FZ1;

unde FZ1 reprezinta view-ul creat prin comanda anterioara.

Crearea unui view complex presupune utilizarea unor functii agregat in fraza Select:

CREATE VIEW STOC_3

(DEPOZIT, STOCJ/IEDIU, STOC.MAX, STOCJ/IIN) AS

SELECT COD_DEP, AVG(CANTITATE), MAX(CANTITATE), MIN  (CANTITATE) FROM STOCURI

GROUP BY COD_DEP;

In acest caz coloanele view-ului au primit nume distincte de numele atributelor existente in tabela Stocuri, atribuirea valorilor facandu-se in ordinea definirii atributelor in view.

In urmatorul exemplu se exemplifica folosirea mai multor fraze SELECT imbricate, rezultatul returnat reprezentand clientii care au solicitat comenzi depozitelor alimentare:

CREATE VIEW CLIENT_A

AS SELECT* FROM CLIENT

WHERE CODCLI IN (SELECT CODCLI FROM COMANDA

WHERE CODDEP IN (SELECT CODDEP FROM DEPOZIT

WHERE DENDEP LIKE '*ALIM*'));

In general, fraza SELECT din definitia unui view poate avea orice forma, exceptie facand clauzele ORDER BY si UNION, care nu pot aparea in comanda CREATE VIEW. Acestea se pot regasi insa in fraza SELECT, care face referire la view-ul creat.

A. Nu poate fi utilizata clauza ORDER BY;

Exemplu: Nu se poate defini un astfel de view.

CREATE VIEW PROD1

AS SELECT CODPROD, DENPROD, PRET

FROM PRODUSE WHERE PRET BETWEEN 50 AND 300

ORDER BY DENPROD;

Clauza ORDER BY poate fi utilizata in instructiunea de interogare a view-ului:

• crearea view-ului:

CREATE VIEW PROD1

AS SELECT CODPROD, DENPROD, PRET

FROM PRODUSE

WHERE PRET BETWEEN 50 AND 300;

• interogarea view-ului PROD1:

B. Nu se poate utiliza clauza UNION

Exemplu: nu se poate defini un astfel de view.

CREATE VIEW PROD_TV

AS SELECT CODPROD, DENPROD FROM PRODUSE WHERE DENPRQD='TELEVIZOR' . ,

UNION

SELECT CODPROD, DENPROD FROM PRODUSE WHERE DENPROD="VIDEOCASETOFON";

Clauza UNION poate fi utilizata astfel:

• se definesc 2 view-uri PROD_T si PROD_V

CREATE VIEW PROD_T

AS SELECT CODPROD, DENPROD FROM PRODUSE WHERE DENPROD="TELEVIZOR";

CREATE VIEW PROD_V

AS SELECT CODPROD, DENPROD

FROM PRODUSE

WHERE DENPROD="VIDEOCASETOFQN";

• se combina cele 2 view-uri prin specificarea clauzei UNION intr-o interogare:

SELECT* FROM PROD_T

UNION

SELECT* FROM PROD_V;

Utilizarea optiunii WITH CHECK OPTION

Aceasta optiune asigura faptul ca in cazul unei operatii INSERT sau UPDATE - nu va fi adaugat sau actualizat nici un tuplu care nu se incadreaza in conditiile specificate la definirea view-ului.

Exemplu:

CREATE VIEW SAL1

AS SELECT MARCA, NUME, SAL FROM SALARIAT

WHERE SAL BETWEEN 1500 AND 3000

WITH CHECK OPTION;

Asupra view-urilor pot fi efectuate aceleasi operatii ca asupra tabelelor pe baza carora au fost definite, cu unele restrictii.

Interogarea view-rilor:

Restrictiile sunt:

a) Clauza WHERE nu poate contine un atribut rezultat din aplicarea unei functii agregat.

CREATE VIEW DEP1 (DEPOZIT, TOTAL).

AS SELECT COD_DEP, SUM(CANTITATE) FROM STOCURI GROUP BY COD_DEP;

Nu se poate specifica atributul TOTAL al vizualizarii DEP1 in clauza WHERE a unei interogari de tipul:

SELECT' FROM DEP1 WHERE TOTAL>200;

Atributul TOTAL poate fi apelat numai in clauza ORDER BY.

SELECT DEPOZIT, TOTAL FROM DEP1 ORDER BY TOTAL;

b) Un atribut al view-ului rezultat in urma aplicarii unei functii agregat, nu poate fi utilizata ulterior ca argument al unei alte functii agregat. Nu va putea fi executata o cerere de forma :

SELECT AVG(TOTAL) FROM DEP1

Actualizarea view-urilor

Numele unui view poate fi invocat nu numai in interogari ci si in comenzile DELETE, INSERT si UPDATE, caz in care relatiile de baza din definitia view-ului pot suferi modificari, in acest sens se impun restrictiile:

a)     View-ul nu trebuie sa fie rezultatul unei operatii JOIN (instructiunile nu pot fi aplicate asupra mai multor relatii ci numai asupra uneia la un moment dat);

b)     Fraza SELECT ce defineste un view nu trebuie sa contina clauza GROUP BY, optiunea DISTINCT, functii agregat sau o subinterogare;

A. Adaugarea de noi tupluri la view-ul creat

creare view:

CREATE VIEW MARFUR11

AS SELECT CODMF, DENMF,. PRET FROM MARFURI WHERE PRET IS NOT NULL; 

adaugare de tupluri:

INSERT INTO MARFURI1

VALUES(333;"CAIETE", 5000)

B. Modificarea unor tupluri

UPDATE MARFURI1

SET PRET=PRET*0.5

C. Stergerea unor tupluri

DELETE MARFURI1

WHERE PRET BETWEEN 3000 AND 150000;

Stergerea view-urilor

DROP VIEW MARFURI1

Aceasta comanda determina pe de o parte stergerea definitiei view-ului din dictionarul de date, iar pe de alta parte stergerea oricarui view definit pe baza ei.

Un view este sters implicit in cazul stergerii relatiilor pe baza carora este definit.