IMAGINI (VIEWS)

IMAGINI (VIEWS)

Ce este o Imagine ?

* O imagine este ca o fereastra prin intermediul careia datele din

tebele pot fi vizualizate sau schimbate.

* O imagine este derivata dintr-o alta imagine sau tabela la care

este referita ca tabela de baza a imaginii - o tabela 'reala' cu

date care sunt stocate fizic.

* O imagine este stocata doar ca o secventa SELECT. Este o tabela

virtuala - adica este o tabela care nu exista fizic, dar ii apare

utilizatorului ca si cum ar exista.

* O imagine nu are date proprii. Ea manipuleaza datele din tabela de

baza asociata.

Imaginile sunt utile pentru urmatoarele scopuri :

* Ingradirea accesului la o baza de date. Selectand dintr-o imagine

(VIEW) putem afisa o parte restransa din baza de date.

* Permite utilizatorilor sa faca cereri simple pentru obtinerea de

rezultate din cereri complicate. De exemplu, imaginile permit

utili- zatorilor sa selecteze informatii din tabele multiple fara

cunoas- terea mecanismelor instructiunii JOIN.

* Furnizeaza independenta datelor pentru useri ad-hoc si programele

aplicatiilor. O imagine poate fi utilizata pentru a extrage trans-

parent date din mai multe tebele. De asemenea imaginile permit ca

aceleasi date sa fie vazute de utilizatori diferiti in feluri

dife- rite.

Imagini Simple vs. Imagini Complexe

Clasificarea unei imagini simple :

* furnizeaza date dintr-o singura tabela

* nu contine functii sau grupuri (GROUP) de date.

O imagine complexa poate fi compusa din date care sunt :

* furnizate din mai multe tabele

* contine functii sau grupuri de date.

Comanda CREATE VIEW

CREATE [ OR REPLACE ] [FORCE] VIEW nume-view

[(coloana1, coloana2, )]

AS

SELECT secventa-select

[WITH CHECK OPTION [CONSTRAINT nume_constr]]

Coloana1, coloana2, etc. sunt numele date coloanelor in imaginesi

trebiue sa corespunda elementelor din lista selectata.

Pentru a crea o imagine simpla numita D10EMP, care contine unele

detalii ale angajatilor din Departamentul 10, introduceti :

CREATE VIEW D10EMP

AS

SELECT EMPNO,ENAME,SAL

FROM EMP

WHERE DEPTNO = 10;

Cand o imagine este creata, un mesaj este afisat : 'View created.'

Imaginea poate fi utilizata ca orice tabela :

SELECT *

FROM D10EMP

ODRER BY ENAME;

Pentru a crea o imagine complexa numita DEPT_SUMMARY, care contine

functii de grup si date din doua tabele :

CREATE VIEW DEPT_SUMMARY

( NAME, MINSAL, MAXSAL, AVSAL )

AS

SELECT DNAME, MIN(SAL), MAX(SAL), AVG(SAL)

FROM EMP,DEPT

WHERE EMP.DEPTNO = DEPT.DEPTNO

GROUP BY DNAME;

De notat faptul ca numele coloanelor alternative au fost specificate

pentru imagine. Aceasta este o cerinta pentru cazul cand elementele

SELECTate nu sunt conforme cu conventia denumirilor din tabele /

coloane, sau daca orice coloana din imagine este derivata dintr-o

functie sau expresie.

De notat si faptul ca daca o coloana este utilizata intr-o cerere

(ANNSAL), o imagine a unui alias al coloanei ( nume alternativ pentru

coloana in imagine ) nu e necesar. De exemplu :

CREATE VIEW DEPT20

AS

SELECT ENAME, SAL*12 ANNSAL

FROM EMP

WHERE DEPTNO = 20;

O imagine nu poate contine clauza ORDER BY. Clauza ORDER BY este

specificata cand se executa SELECT din imagine.

Optiunea OR REPLACE

Aceasta optiune permite ca o imagine sa fie creata chiar daca exista

una cu acelasi nume, astfel inlociundu-se vechea varianta a imaginii

pentru proprietarul ei. Aceasta inseamna ca o imagine poate fi

alterata fara a fi abandonata, recreandu-se si redand privilegiile

obiectului.

Optiunea FORCE

Aceasta optiune creaza o imagine chiar daca tabela de baza nu exista,

sau sunt prea putine drepturi pentru tabela respectiva. Oricum, tabela

trebiue sa existe inainte ca imaginea sa poata fi utilizata.

Utilizarea unei Imagini cu Operatii DML

Imaginile sunt facilitati puternice deoarece ele ne permit sa executam

verificari de integritate referentiale asupra datelor modificate prin

utilizarea imaginilor.

Clauza WITH CHECK OPTION specifica faptul ca INSERT si UPDATE

executate pein intermediul imaginii nu permit crearea de linii noi pe

care imaginea nu le poate selecta, si de aceea mermite constrangerile

de integritate si vreificarile validarilor de date sa fie fortate

asupra datelor ce vor fi inserate sau actualizate.

In urmatorul exeplu, o imagine numita D10EMP este creata cu WITH CHECK

OPTION. Aceasta inseamna ca doar linii care au DEPTNO = 10 pot fi

inserate, actualizate sau sterse. Adica nici o secventa DML nu poate

sa adreseze imaginea decat daca conditiile clauzei imaginii sunt

indepli- nite.

CREATE VIEW D10EMP

AS

SELECT EMPNO, ENAME, DEPTNO

FROM EMP

WHERE DEPTNO = 10

WITH CHECK OPTION;

Daca exista o incercare de a executa actiuni DML asupra liniilor pe

care imaginea nu le poate selecta intr-o claiza WHERE, se afiseaza

eroare :

'ORA_01402 VIEW WITH CHECK OPTION

WHERE_Clause voilation.'

Urmatorul exemplu va valida intratea oricarei comenzi INSERT/UPDATE

astfel incat numarul SAL sa fie intre 1000 si 2000, MGR va trebui sa

aibe campul EMPNO valid, si DEPTNO sa existe in tabela DEPT.

CREATE VIEW EMP_DATA

AS

SELECT EMPNO, ENAME, JOB, MGR, SAL, DEPTNO

FROM EMP

WHERE SAL BETWEEN 1000 AND 2000

AND MGR IN ( SELECT DISTINCT EMPNO

FROM EMP )

AND DEPTNO IN ( SELECT DEPTNO

FROM DEPT )

WITH CHECK OPTION;

Pentru a crea o imagine care sa restranga accesul la detaliile

personale ale unui angajat valid in tabela EMP, si timpul de acces la

orele de servici, atunci introduceti urmatoarea comanda :

CREATE VIEW EMP_DETAILS

AS

SELECT EMPNO, ENAME, JOB, DEPTNO

FROM EMP

WHERE ENAME = USER

AND TO_CHAR(SYSDATE,'HH24') BETWEEN 9 AND 17

AND TO_CHAR(SYSDATE,'D') BETWEEN 2 AND 6

WITH CHECK OPTION;

Cand creati o imagine cu WITH CHECK OPTION, trebiue s-i atribuiti un

nume restrictiei WITH CHECK OPTION.

CREATE VIEW EMPLOYEES( ID_NUMBER, NAME, DEPARTMENT)

AS

SELECT EMPNO, ENAME, JOB, DEPTNO

FROM EMP

WHERE DEPTNO IN ( SELECT DISTINCT DEPTNO FROM DEPT)

WITH CHECK OPTION CONSTRAINT DEPT_CHECK;

Aceasta restrictie previne inserarea unei coloane in tabela EMP cu un

numar care nu exista in tabela DEPT.

Cand comanda CREATE VIEW este executata in seventa SELECT care urmeaza

AS nu e executata; in loc este doar depozitata in dictionarul de date.

Cand accesati date, via o imagine, ORACLE executa urmatoarele operatii

:

* recupereaza definitia imaginii din dictionarul de date

* verifica drepturile de acces

* converteste cererea imaginii intr-o operatie echivalenta asupra

tabelei sau tabelelor asociate. Cu alte cuvinte, datele sunt recu-

perate din, sau o actualizare este facuta asupra, tabelei(lor) de

baza.

Odata ca imaginea a fost creata, trebuie sa se execute o cerere la

Tabela Dictionarului de Date numita USER_VIEWS ca sa se vada conanda

view.

Acesti parametrii SETabili vor influienta afisarea imaginii text :

* Maxdata

* Arraysize

* Long

Imaginea text este memorata intr-o coloana de tipul de date Long si ar

trebui setat ARRAYSIZE la o valoare mica ( de ex. 1 ) pentru a

vizualiza textul.

Modificarea Datelor prin Imagini

Dupa cum am discutat, restrictiile pot fi impuse cu ajutirul clauzei

WITH CHECK OPTION cand o imagine este creata. Mai sunt si alte

constrangeri de care trebiue sa fiti atenti.

DELETE este interzis cand o imagine contine :

1. conditie Join

2. functii de grup

3. clauza GROUP BY

4. comanda DISTINCT

5. ROWNUM (nr de ordine al randului dupa ce acesta a fost adus din

baza de date).

UPDATE este interzis cand :

1. Oricare de mai sus

2. Coloane definite prin expresii ( de ex. SAL*12 )

INSERT este interzis cand :

1. Oricare de mai sus

2. Orice NOT NULL coloane nu sunt selectate de imagine ( ex. EMPNO )

Comanda DROP VIEW

Utilizati comanda DROP pentru a sterge o imagine. Aceasta comanda

elimina definitia imaginii din baza de date. Coloanele si liniile nu

sunt afectate deoarece sunt stocate in tabelele de unde a derivat

fiecare ima- gine. Imaginile sau alte aplicatii bazate pe o imagine

stearsa devin in- valide.

De notat ca o imagine poate fi derivata dintr-o tabela sau de fapt

dintr-o alta imagine !

Sintaxa DROP este :

DROP VIEW nume_imagine;

Doar creatorul imaginii poate sa o stearga.

Exercitii - CAP 16 - Crearea si Utilizarea Imaginilor

1. Definiti o imagine care sa produca urmatoarea iesire cand este

referita intr-o cerere. Rulati o cerere pentru a testa imaginea.

DEPTNO AVERAGE MAXIMUM MINIMUM SUM NO_SALS NO_COMMS

------ ---------- ------- ------- ----- -------- --------

2196.66667 5000 1300 8750 3 0

2175 3000 800 10875 5 0

1566.66667 2850 950 9400 6 4

2. Utilizand imaginea de mai sus, extrageti urmatoarea informatie.

Nuarul angajatului trebuie introdus la rulare.

EMPNO ENAME JOB SAL HIREDATE MINIMUM MAXIMUM AVERAGE

----- --------- ------- ---- --------- ------- ------- -------

7902 FORD ANALYST 3000 05-DEC-83 800 3000 2175

3. Creati o imagine care sa impuna urmatoarele restrictii la

insrearea datelor in tabela ASSIGNMENTS.

1. Projid trebuie sa fie mai mic de 2000.

2. Data de sfarsit trebiue sa fie dupa data de inceput.

3. Tiputile valide pentru Assign_type sunt PF, WT sau ED.

4. Bill_rate trebuie sa fie mai mic decat 50.00 pentru

Assign_type PF, mai mic de 60.00 pentru WT si mai mic de

70.00 pentru ED.

5. EMPNO trebuie sa fie valid.

6. Retineti clauza WITH CKECK OPTION.

SOLUTII - CAP 16

1.

CREATE VIEW AGGREGATES

( DEPTNO, AVERAGE, MAXIMUM, MINIMUM, SUM,

NO_SALS, NO_COMMS )

AS

SELECT DEPTNO,

AVG(SAL),

MAX(SAL),

MIN(SAL),

COUNT(SAL),

COUNT(COMM),

FROM EMP

GROUP BY DEPTNO;

2.

SELECT EMP.DEPTNO,

ENAME,

JOB,

SAL,

HIREDATE,

MINIMUM,

MAXIMUM,

AVERAGE

FROM EMP, AGGREGATES AGG

WHERE EMP.DEPTNO = AGG.DEPTNO

AND EMP.EMPNO = & EMPNO;

3.

CREATE VIEW ASG_VAL

AS

SELECT PROJID,

EMPNO,

A_START_DATE,

A_END_DATE,

BILL_RATE,

ASSIGN_TYPE,

HOURS,

FROM ASSIGNMENTS

WHERE A_START_DATE