Obiecte complexe in SQL. Vederi.

Obiecte complexe in SQL. Vederi.

O vedere (VIEW). Este un obiect al BD care poate fi considerat ca fiind o tabele virtuala adica o tabele pentru care se pastreaza in BD doar schema nu si datele. Poate fi utilizata in orice interogare ca si un tabel. Cu anumite restrictii dependente de SGBD ele pot fi utilizate si in op. de actualizare.

Din punct de vedere al SQL orice vedere este alcatuita dintr-o instructiune SELECT pastrata permanent ca un obiect dinstinct al BD.

O vedere este de obicei folosita pentru a incapsula o operatie de jonctiune mai complicata sau o interogare utilizata frecvent. Poate fi utilizata pentru a oferi unui utilizator numai datele de care are nevoie sau la care are drept de acces. Aceasta inseamna ca atunci cand se atribuie drepturile de acces pentru diversi utilizatori se vor atribui drepturi de acces la diverse vederi dar nu si la tabelele pe baza carora se construieste vederea.

Coloanele unei vederi pot fi coloane extrase dintr-o tabela, din alta vedere sau coloane calculate. Se observa ca o vedere poate sa faca apel la o alta vedere. In final trebuie sa se ajunga la tbl de baza sau expresii care nu utilizeaza tabele ci functii.

Ex: GETDATE ( ) à returneaza ora, minutele, secundele, data.

SQL Server 2000 nu suporta decat vederi statice. Pentru a simula o vedere temporala valabila doar pe durata unei sesiuni de lucru se creaza o vedere statica in BD predefinita tempbd.

Crearea unei vederi in SQL Server 2000 e posibila numai in BD pentru care e definite. Crearea vederii se realizeaza cu ajutorul comenzii: CREATE VIEW.

Sintaxa comenzii este:

CREATE VIEW[proprietar]nume_vedere[(lista_coloane)]

[WITH <atribute_vedere>[,..n]]

AS

Comanda_SELECT

[WITH CHECK OPTIONS]

<atribute_vedere>::=

Parametrii comenzii au urmatoarele semnificatii:

nume vedere = numele vederii care trebuie sa respecte toate regulile impuse identific

lista coloane = parametru obtional cu numele coloanei dintr-o vedere. Se va utiliza doar daca numele numele unora dintre coloanele vederii difera de numele coloanei corespunzatoare din tabloul de baza sau exista coloane calculate carora trebuie sa le dam un nume. In acest ultim caz putem da nume coloanei calculate in comanda SELECT folosind aliasul de coloana. Daca lista lipseste, numele coloanei e identic cu cel din comanda SELECT prin care definim vederea.

cuvantul cheie AS = cuvantul prin care se introduce comanda SELECT de definire a vederii.

Comanda SELECT e o comanda SELET prin care se defineste structura si continutul vederii create. O vedere poate fi creata utilizand una sau mai multe tablouri si vederi deja definite si asupra carora cel care creaza vederea are drept de acces.

Exista cateva restrictii privind comanda SELECT. Principalele sunt:

nu se accepta includerea clauzei ORDER BY decat in cazul in care la inceputul comenzii SELECT apare optinea TOPn, care specifica faptul ca vor fi selectate numai primele n elemente din lista. In general, utilizarea ordonarii in interiorul comenzii SELECT de definitie nu se justifica deoarece utilizatorul trebuie sa poata ordona informatia in functie de necesitati. Exista cazuri in care este necesar sa se selecteze de la inceput informatia intro-a aunmita ordine.

nu se accepta includerea cuvintelor cheie INTO, deoarece aceasta ar insemna crearea unei copii fizice a continutului selectat.

nu se accepta referirea la tablourile temporale sau la variabile tablou.

Obtiunea WITH CHECK OPTION obliga ca in cazul in care se folosesc comenzi de actualizare, toate operatiile de actualizare a datelor executate asupra unei vederi trebuie sa respecte regulile de integritate impuse de comanda SELECT din vedere. Aceasta este necesar, deoarece in lipsa unei optiuni o linie introdusa fara sa respecte clauza impusa de SELECT va dispare de pe ecran dupa actualizare. Daca se utilizeaza aceasta obtine, toate liniile introduse vor apare pe ecran.

Optiunea WITH ENCRIPTION asigura criptarea textului comenzii de creare a vederii pentru ca acesta sa nu poata fi vizualizat. Nu se recomada cecat intr-o veriune finala.

Optiunea WITH SCHEMA BINDING asigura integritatea structurala a unei vederi in sensul ca nici o tbl. si nici o vedere care participa la realizarea unei vederi cu aceasta clauza nu vor putea fi eliminate si nu modifica structura pana cand vederea afectata nu e eliminata.

Cand introducerea BD se realizeaza printr-o vedere, sistemul verifica daca toate obiectele referite exista, daca sunt valide si daca modificarea datelor nu violeaza regimul de integritate a datelor. Daca cel putin o verificare esueaza, interogarea este abandonata si se returneaza un mesaj de eroare. Daca toate verificarile s-au finalizat cu succes, interogarea asupra vederii va fi transformata automat intr-un set de interogari asupra continutului tabloului de baza.

In cazul in are un tabel sau o vedere care intra in def vederii interogate este eliminat si apoi recreat, vederea va putea fi utilizata ca si cand nimic nu s-ar fi intamplat. Daca tabelul sters e recreat cu o structura diferita care poate afecta structura vederii, vederea va trebui stearsa si apoi recreata.

Exemplul 1:

CREATE VIEW vwAdrese_Student1

AS

SELECT Nume, Initiala, Prenume, Tara, TipAdresa, Localitate, Sector,

Strada, Numar, Bloc, Scara, Etaj, Apartament

FROM dbo.tblStudent INNER JOIN

Dbo.tblAdresa CN dbo.tblAdresa.Nrleg = dbo.tblStudent.Nrleg

GO

Pentru a crea vederea din exemplu este necesar ca intai sa ne asiguram ca a fost deschisa BD in care dorim sa fie creata vederea:

USE dbSecretariat 2004

In cazul in care comanda de creare a unei vederi face parte dintr-o succesiune de comenzi SQL ce vor fi lansate impreuna in executie, comanda CREATE VIEW trebuie sa fie prima din lista de comenzi. Pentru a elimina aceasta cerinta vom utiliza comanda GO. Intre 2 comenzi GO se considera o secventa de comenzi independente de celelalte.

OBS: Aceasta cerinta nu e valabila in toate SGBD. Vederea nu va fi creata decat in cazul in care tblStudent si tblAdresa au fost deja create. Nu are importanta daca tbl contine sau nu date.

Exemplul 2:

CREATE VIEW vwAdresaStabila_Student

AS

SELECT Nume, Initiala, Prenume, Tara, TipAdresa, Localitate, Sector,

Strada, Numar, Bloc, Scara, Etaj, Apartament

FROM dbo.tblStudent INNER JOIN

dbo.tblAdresa CN dbo.tblAdresa.Nrleg = dbo.tblStudent.Nrleg

WHERE TipAdresa = 'S'

GO

Spre deosebire de prima vedere care afisa toate adresele, aceasta vedere permite vizualizarea numai pentru adrese stabile. Si cu prima vedere se puteau obtine numai adrese stabile daca plasam clauza WHERE in comanda SELECT de afisare:

SELECT *FROM vwAdrese_Student1

WHERE TipAdresa = 'S'

In al doilea caz putem sa afisam numai adrese stabile folosind comanda:

SELECT *FROM vwAdresStabila_Student

Se oberva imediat ca utilizarea vederilor poate sa simplifice mult scrierea unor aplicatii, in special daca utilizatorii au conostinte slabe de SQL.

OBS: Vederile permit refacerea continutului initial al unui tabel care in procesul de normalizare a fost descompus in mai multe tabele. Astfel se pune la dispozitia beneficiarului un tablou virtual cu o structura convenabila. In felul acesta procesul de normalizare devine transparent din punct de vedere al utilizatorului si simpplifica utilizarea datelor.

Ordonarea datelor afisate se va rezolva de asemenea in comanda SELECT care foloseste vederea:

SELECT *FROM vwAdresStabila_Student

ORDER BY Nume, Prenume, Initiala

Vederile din exemplele precedente pot fi actualizate fara probleme, deoarece nu contin decat coloane luate direct din 2 tabele.

Deoarece nu exista obtiunea WITH CHECK OPTION putem introduce in a II-a vedere si adrese flotante cu Tip_Adresa = 'F', dar dupa introducere aceste nu vor mai apare in continutul vederii desi exista in BD. Aceasta poate provoca deruta utilizatorului.

Exemplul 3:

Daca dorim sa cream o vedere care sa ofere datele ordonate dupa un criteriu impus, ba trebuie sa utilizam o comanda de forma:

CREATE VIEW vwAdrese_StudentOrdonat

AS

SELECT TOP 2000 Nume, Initiala, Prenume, Tara, TipAdresa,

Localitate, Sector, Strada, Numar, Bloc, Scara, Etaj,

Apartament

FROM dbo.tblStudent INNER JOIN

dbo.tblAdresa CN dbo.tblAdresa.Nrleg = dbo.tblStudent.Nrleg

ORDER BY Nume, Prenume, Initiala

Daca nu apare obtiunea TOP, vederea nu va fi putea fi creata. Utilizarea vederilor este posibila doar daca comanda SELECT nu are functi de agregare, nu contine cuv. TOP, GROUP BY, UNION sau DISTINCT, nu contine coloanele calculate.

O comanda DELETE poate referi doar o vedere actualizabila care refera exact un tabel in clauza FROM.Cu alte cuvinte nu putem elimina, folosind vederile, o linie din mai multe tabele.

O comanda INSERT sau UPDATE poate fi utilizata doar daca afecteaza datele intr-un singur tabel din lista de tbl a clauzei FROM din comanda SELECT pentru crearea vedrii.

Avand in vedere aceste resctrictii se recomanda pe cat posibil evitarea actualizarii datelor prin utilizarea vederilor.