11.Vederi
Ce este o vedere?
O vedere este o tabela logica care extrage date dintr-o tabela propriu-zisa sau dintr-o alta
vedere. O vedere nu are date proprii, ci este ca o fereastra prin care datele din tabele pot fi
actualizate sau vizualizate. O vedere poate sa contina toate datele dintr-o tabela (sau mai
multe) sau anumite coloane.
De ce folosim vederile?
Vederile se folosesc pentru ca ofera :
- Acces restrictionat la date (vederile pot selectiona anumite coloane dintr-o tabela)
- Posibilitatea lucrarii cu interogari complexe
- Independenta datelor
- Posibilitatea prezentarii de date identice in diferite vederi
Avantajele utilizarii vederilor
- Vederile pot fi folosite in cereri SQL simple cand de fapt ele intorc rezultatul unor cereri
SQL complexe. Deci putem utiliza vederile in interogarea mai multor tabele fara a stii ce
este un join de tabele.
- O vedere poate fi folosita pentru a obtine date din mai multe tabele
- Vederile ofere drepturi de acces pentru grupuri de utilizatori
Vederile se clasifica in vederi simple si vederi complexe.
Pentru inceput prezentam vederile simple.
Crearea vederilor
Vederile simple sunt acele vederi care :
- Interogheaza o singura tabela
- Nu contin functii
- Nu contin grupuri de date
- Ofera posibilitatea efectuarii de operatii DML (INSERT, UPDATE, DELETE)
NOTA : O vedere are un nume si este generata de o cerere SQL.
Sintaxa
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[ (alias [, alias]...) ]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint] ]
[WITH READ ONLY [CONSTRAINT constraint] ];
|
Semnificatia argumentelor sintaxei :
- OR REPLACE : determina inlocuirea vederii daca aceasta exista deja
- FORCE : crearea vederii indiferent daca tabela din care provin datele nu exista
- NOFORCE : crearea vederii numai daca tabela exista (valoarea implicita)
- view : numele vederii
- alias : specifica numele expresiilor selectate de catre cererea din vedere (numele
coloanelor selectate). Numarul alias-urilor trebuie sa fie egal cu numarul coloanelor
selectate.
- subquery : cererea SELECT care genereaza vederea
- WITH CHECK OPTION : numai randurile accesibile vederii pot fi actualizate
- constraint: numele constrangerii asociate cu WITH CHECK OPTION
- WITH READ ONLY : asigura ca nici o operatie DML (INSERT, UPDATE, DELETE) nu va fi
executata prin vedere
Exemplu
Sa cream o vedere emp20 care va contine persoanele departamentul 20.
Mai intai trebuie sa ne asiguram ca vederea emp20 nu exista deja.
Aceasta vedere nu exista deci putem sa o cream.
Pentru a vedea faptul ca vederea a fost creata intr-adevar folosim aceeasi sintaxa
ca mai sus, deci vom interoga tabela de obiecte USER_OBJECTS.
Pentru a vizualiza continutul vederii folosim cererea SQL SELECT.
Indrumari in crearea vederilor :
- Subcererea care defineste vederea poate contine cereri SELECT complexe incluzand
join-uri, grupuri sau alte subcereri.
- Subcererea principala a vederii nu trebuie sa contina clauza ORDER BY. Aceasta se
utilizeaza cand se extrag date din vedere.
- Daca nu se specifica un nume pentru constrangerea WITH CHECK OPTION, sistemul
atribuie in mod implicit un nume
- Cu ajutorul optiunii OR REPLACE vederea este alterata fara a o distruge, a o
recrea si a reacorda drepturi asupra ei.
Crearea unei vederi cu alias-uri de coloana
Exemplu
Sa cream o vedere emp10 care va contine persoanele din departamentul 10.
Mai intai trebuie sa ne asiguram ca vederea emp10 nu exista deja.
Aceasta vedere nu exista deci putem sa o cream.
Pentru a verifica ca vederea a fost creata utilizam comanda de interogare a tabelei USER_OBJECTS.
Pentru a vizualiza continutul vederii folosim cererea SQL SELECT.
Atentie : trebuiesc selectati alias-urile de coloana. Exercitiu.
O alternativa la alias-urile utilizate in cererea SQL sunt alias-urile plasate dupa CREATE
inaintea cererii SELECT. De data aceasta vom crea o vedere cu persoanele din departamentul 30.
Exercitiu
Vizualizati numele si salariul anual al persoanelor din vederea emp30.
Extragere de date dintr-o vedere
Extragerea de date se face exact ca in cazul unei tabele. Se pot selecta anumite randuri sau toata vederea.
Interogarea unei vederi de catre serverul ORACLE se face in felul urmator :
- Extragere definitie vedere din tabela dictionarului de date USER_VIEWS
- Verificare drepturi acces pentru tabela vederii
- Convertire interogare vedere intr-o interogare echivalenta a tabelei
vederii. Deci datele sunt extrase, inserate din tabele propriu-zise.
Modificarea unei vederi
Pana acum am verificat daca vederea exista sau nu si numai in cazul in care nu exista o cream.
Avem posibilitatea de a crea o vedere chiar daca ea exista, inlocuind-o pe cea veche cu o noua
versiune. In acest scop se foloseste clauza OR REPLACE. Vom inlocui vederea emp20 cu alta.
Exercitiu
Vizualizati continutul vederii cu o cerere SELECT.
Crearea unei vederi complexe
Vom utiliza functii de grup pentru a afisa o vedere complexa asupra a 2 tabele.
NOTA : Cand o coloana a vederii este o functie sau o expresie trebuie folosit alias de coloana.
Continutul vederii este afisat in acelasi mod ca pana acum.
Exercitiu
Afisati continutul vederii dept_salariu.
Efectuarea operatiilor DML asupra vederilor
Se pot efectua operatii DML asupra vederilor, dar numai in anumite conditii.
Nu se poate sterge un rand dintr-o vedere care contine :
- functii de grup
- GROUP BY
- DISTINCT
- ROWNUM
De asemenea nu se pot modifica date printr-o vedere daca aceasta verifica conditiile
de mai sus si daca exista coloane definite prin expresii cum ar fi SAL*12.
Adaugarea de date printr-o vedere este imposibila daca vederea contine elementele
enumerate mai sus si daca exista coloane NOT NULL in tabela neselectate
de catre vedere. Prin adaugare de date se modifica de fapt tabela propriu-zisa
deci in coloanele neselectate se va adauga NULL ceea ce incalca constrangerea.
Exemplu
Crearea vederii dept_sal utilizand clauza OR REPLACE.
Verificam crearea vederii:
Exercitiu
Vizualizati continutul vederii cu o cerere SELECT.
Modificati vederea dept_sal utilizand comanda UPDATE.
Clauza WITH CHECK OPTION
Se foloseste pentru a ne asigura ca operatia care se efectueaza pe o
vedere respecta domeniul vederii, adica nu va introduce modificari care
vor determina ca liniile inserate sa nu mai fie vazute de vedere.
Exemplu
Sa cream vederea emp20 utilizand clauza OR REPLACE
Incercam sa modificam deptno intr-un rand al vederii, adica sa trecem
cu ajutorul vederii o persoana din departamentul 20 in alt departament. Ca sa trecem efectiv o persoana dintr-un
departament in altul ar trebui sa ne asiguram ca aceea persoana exista deci de aceea se vizualizeaza
continutul vederii emp20. Am ales empno=7844 dar ar putea fi oricare alt numar.
Nota :Se observa ca nu se poate schimba codul departamentului pentru ca atunci vederea nu
ar mai vedea respectiva persoana. Deci cu ajutorul clauzei WITH CHECK OPTION
vederea permite vizualizarea persoanelor din departamentul 20 si nu
permite schimbarea codului pentru persoanele selectate in vedere.
Clauza WITH READ ONLY
Cu ajutorul clauzei READ ONLY putem interzice operatiile DML printr-o vedere.
Exemplu
Sa cream o vedere emp10 utilizand clauza OR REPLACE
Incercam sa modificam vederea emp10 alegand un angajat cu empno=7369 dar acesta poate fi schimbat.
Orice incercare de a sterge sau modifica un rand al vederii se sfarseste prin eroare.
Stergerea unei vederi
Syntaxa :
Exemplu
Distrugerea vederii emp10.
Verificam ca vederea nu mai exista.
Inline views
O vedere integrata (inline view) este o subcerere avand un alias care poate fi folosit intr-o
cerere SQL. Subcererea defineste o sursa de date care poate fi apelata in cererea
principala.
Nota : Inline views merg numai in Oracle9!
Exemplu
Vederea m intoarce pentru fiecare departament salariul minim.
Cererea principala intoarce numele, salariul, codul departamentului si salariul
minim pentru cei care castiga mai mult decat salariul minim.
Cereri Top N
Cererile Top N cauta cele mai mari n sau cele mai mici valori dintr-o coloana.
Folosind cereri Top N putem afla de exemplu:
- primii 3 salariati dintr- companie
- cele mai bine vandute 3 produse ale unei companii
Sintaxa
SELECT [ column_list ], ROWNUM
FROM (SELECT [column_list]
FROM table
ORDER BY Top-N column)
WHERE ROWNUM <= N;
|
Sintaxa contine :
- O subcerere care intoarce elemente sortate si care contine
clauza ORDER BY. Ca sa avem cea mai mare valoare pe prima linie folosim DESC.
- O cerere care selecteaza un anumit numar de linii (N) ca rezultat
final. Contine :
- ROWNUM care afecteaza o valoare incepand le la 1
fiecarei linii intoarse de subcerere.
- WHERE care conditioneaza numarul de linii intoarse
Exemplu
Cele mari bine remunerate persoane
Persoanele cu vechimea cea mai mare :
Exercitii recapitulative
1. Creati o vedere EMPLOYEE care sa contina codurile, numele, si departamentul angajatilor
din tabela EMP. Schimbati capul de coloana EMPNO cu ANGAJAT. Vizualizati continutul vederii.
2. Creati o vedere INFO_DEPT care sa contina numarul departamentului, salariul mediu (MEDIA), salariul maxim
(MAXIM), salariul minim (MINIM), suma salariilor (SUMA), numarul de salarii (NO_SAL), numarul
de comisioane (NO_COM) din tabela EMP.
3. Creati o vedere DEPT20 continand codul, numele si cod departament
ale persoanelor din departamentul 20.
Etichetati coloanele cu COD, PERSOANA, DEPTNO.
Nu permiti ca prin vedere o persoana sa fie transferata
intr-o alta facultate.
Afisati continutul vederii.
Incercati sa mutatiun angajat in alt departament.
Pentru a vedea cheia exercitiilor apasati aici .