VIEW
Una vista è una tabella logica creata tramite l'istruzione SELECT di una o più tabelle e serve per visualizzare tutti o parte dei dati delle tabelle di partenza.
Può contenere ad esempio solo alcune colonne di una tabella perché non tutti i dati devono essere visibili per ragioni di sicurezza o contenere le stesse colonne ma con nome diverso perché i nomi originari sono troppo lunghi e poco significativi.
La subquery che la definisce può includere join, group e subquery.
Eseguire una DML su una view significa aggiornare la tabelle che la definiscono.
Ci sono due tipi di viste:
CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW [schema.]nameView [(alias,....)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraintname]]
[WITH READ ONLY [CONSTRAINT constraintname]] ;
Nella definizione di una VIEW si inserisce lo schema perché le viste sono "schema objects" e possono contenere dati da tabelle dello stesso schema e non. Se non specificato sarà creato nello schema corrente.
L'opzione OR REPLACE permette di cambiare la definizione di una vista già esistente senza eseguire il drop e ricrearla e riassegnare i privilegi degli oggetti precedentemente assegnati. Se la view già esiste viene automaticamente droppata prima di essere ricreata.
L'opzione FORCE permette di creare la vista anche se le tabelle della subquery non esistono o se l'owner dello schema contenete la vista non ha privilegi sulle suddette tabelle. In questo caso la vista creata non può essere utilizzata finché non vengono create le tabelle base e la vista è compilata con successo.
Oracle restituisce il messaggio seguente (PROVA è il nome della view):
Comando SQL: force view PROVA
Non riuscito: Warning: esecuzione completata con avvertenze
Se si prova ad eseguire l'istruzione Select * from PROVA; riceviamo il messaggio di errore:
ORA-04063: view "HR.PROVA" contiene errori
*Cause: Attempt to execute a stored procedure or use a view that has
errors. For stored procedures, the problem could be syntax errors
or references to other, non-existent procedures. For views,
the problem could be a reference in the view's defining query to
a non-existent table.
Can also be a table which has references to non-existent or
inaccessible types.
*Action: Fix the errors and/or create referenced objects as necessary.
NOFORCE è l'opzione di default e permette di creare la vista solo se esistono le tabelle base e se l'owner dello schema contenete la vista ha privilegi sulle quelle tabelle.
WITH READ ONLY impedisce istruzioni Dml sulla vista. Oracle restituisce il seguente messaggio:
Errore SQL: ORA-42399: impossibile eseguire un'operazione DML su una vista di sola lettura
WITH CHECK OPTION viene usata per le dml. Se la subquery contiene la clausola Where, allora questa opzione impedisce un Update che rende la riga non più estraibile dalla subquery o una Insert di righe che non possono essere estratte dalla subquery, e quindi dalla vista.
Di seguito il messaggio di errore;
Errore SQL: ORA-01402: violazione nella clausola WHERE della vista con WITH CHECK OPTION
01402. 00000 - "view WITH CHECK OPTION where-clause violation"
E s e m p i o
Create Table EMP_2 as
Select EMPLOYEE_ID, LAST_NAME, DEPARTMENT_ID, job_id
From EMPLOYEES;
select * from EMP_2;
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID JOB_ID
----------------------------------------------------------------------------
198 OConnell 50 SH_CLERK
199 Grant 50 SH_CLERK
200 Whalen 10 AD_ASST
201 Hartstein 20 MK_MAN
202 Fay 20 MK_REP
203 Mavris 40 HR_REP
Create View CLERK as
SELECT * FROM EMP_2
WHERE job_id = 'PU_CLERK'
OR JOB_ID = 'SH_CLERK'
OR JOB_ID = 'ST_CLERK'
WITH CHECK OPTION;
update CLERK set JOB_ID = 'PU_MAN' where EMPLOYEE_ID = 198;
ERRORE SQL: ORA-00904: "JOB_ID": IDENTIFICATIVO NON VALIDO
01402. 00000 - "view WITH CHECK OPTION where-clause violation"
insert into CLERKvalues (200,'fran',30,'AC_ACCOUNT');
ERRORE SQL: ORA-00904: "JOB_ID": IDENTIFICATIVO NON VALIDO
01402. 00000 - "view WITH CHECK OPTION where-clause violation"
La seguente dml invece ha esito positivo.
INSERT INTO CLERK VALUES (200, 'fran', 30, 'SH_CLERK' );
CONSTRAINT constraintname è usato per nominare la costraint così da rendere più comprensibile il messaggio di errore.
L'istruzione Alter View è utilizzata per compilare una vista:
ALTER VIEW [schema.]nameView COMPILE;
In generale si usa ogni volta che viene modificata una delle tabelle della subquery.
Non è possibile aggiungere una colonna ad una vista già esistente. Occorre eseguire il drop e ricrearla a meno che non si sia utilizzato l'opzione OR REPLACE.
DROP VIEW [schema.]nameView;
Se vengono droppate le tabelle contenute nella subquery la view esiste ancora ma se viene interrogata restituisce il messaggio:
ORA-04063: view "HR.CLERK" contiene errori
*Cause: Attempt to execute a stored procedure or use a view that has
errors. For stored procedures, the problem could be syntax errors
or references to other, non-existent procedures. For views,
the problem could be a reference in the view's defining query to
a non-existent table.
Can also be a table which has references to non-existent or
inaccessible types.
*Action: Fix the errors and/or create referenced objects as necessary.
Se le tabelle vengono ricreate, la vista restituisce i risultati senza dover essere ricreata.
DML sulle viste
Non è possibile eseguire INSERT ad una vista se la subquery contiene:
1) la clausola GROUP BY
2) funzioni di gruppo
3) la pseudocolonna ROWNUM
4) DISTINCT
5) colonne definite da espressioni
6) colonne NOT NULL definite nelle tabelle basi ma non selezionate dalla vista.
Non è possibile eseguire DELETE in presenza di almeno una delle condizioni 1, 2, 3, 4.
Non è possibile eseguire UPDATE in presenza di almeno una delle condizioni 1, 2, 3, 4, 5.
Per visualizzare il nome delle viste, e la definizione delle stesse, di uno User, eseguire la query:
Select view_name, text
From USER_VIEWS;
Può contenere ad esempio solo alcune colonne di una tabella perché non tutti i dati devono essere visibili per ragioni di sicurezza o contenere le stesse colonne ma con nome diverso perché i nomi originari sono troppo lunghi e poco significativi.
La subquery che la definisce può includere join, group e subquery.
Eseguire una DML su una view significa aggiornare la tabelle che la definiscono.
Ci sono due tipi di viste:
- SIMPLE VIEW creata da una semplice Select e per la quale può essere eseguita una istruzione dml;
- COMPLEX VIEW creata da più tabelle in join, contiene funzioni e aggregazione di dati e per la quale può essere eseguita solo una Select.
CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW [schema.]nameView [(alias,....)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraintname]]
[WITH READ ONLY [CONSTRAINT constraintname]] ;
Nella definizione di una VIEW si inserisce lo schema perché le viste sono "schema objects" e possono contenere dati da tabelle dello stesso schema e non. Se non specificato sarà creato nello schema corrente.
L'opzione OR REPLACE permette di cambiare la definizione di una vista già esistente senza eseguire il drop e ricrearla e riassegnare i privilegi degli oggetti precedentemente assegnati. Se la view già esiste viene automaticamente droppata prima di essere ricreata.
L'opzione FORCE permette di creare la vista anche se le tabelle della subquery non esistono o se l'owner dello schema contenete la vista non ha privilegi sulle suddette tabelle. In questo caso la vista creata non può essere utilizzata finché non vengono create le tabelle base e la vista è compilata con successo.
Oracle restituisce il messaggio seguente (PROVA è il nome della view):
Comando SQL: force view PROVA
Non riuscito: Warning: esecuzione completata con avvertenze
Se si prova ad eseguire l'istruzione Select * from PROVA; riceviamo il messaggio di errore:
ORA-04063: view "HR.PROVA" contiene errori
*Cause: Attempt to execute a stored procedure or use a view that has
errors. For stored procedures, the problem could be syntax errors
or references to other, non-existent procedures. For views,
the problem could be a reference in the view's defining query to
a non-existent table.
Can also be a table which has references to non-existent or
inaccessible types.
*Action: Fix the errors and/or create referenced objects as necessary.
NOFORCE è l'opzione di default e permette di creare la vista solo se esistono le tabelle base e se l'owner dello schema contenete la vista ha privilegi sulle quelle tabelle.
WITH READ ONLY impedisce istruzioni Dml sulla vista. Oracle restituisce il seguente messaggio:
Errore SQL: ORA-42399: impossibile eseguire un'operazione DML su una vista di sola lettura
WITH CHECK OPTION viene usata per le dml. Se la subquery contiene la clausola Where, allora questa opzione impedisce un Update che rende la riga non più estraibile dalla subquery o una Insert di righe che non possono essere estratte dalla subquery, e quindi dalla vista.
Di seguito il messaggio di errore;
Errore SQL: ORA-01402: violazione nella clausola WHERE della vista con WITH CHECK OPTION
01402. 00000 - "view WITH CHECK OPTION where-clause violation"
E s e m p i o
Create Table EMP_2 as
Select EMPLOYEE_ID, LAST_NAME, DEPARTMENT_ID, job_id
From EMPLOYEES;
select * from EMP_2;
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID JOB_ID
----------------------------------------------------------------------------
198 OConnell 50 SH_CLERK
199 Grant 50 SH_CLERK
200 Whalen 10 AD_ASST
201 Hartstein 20 MK_MAN
202 Fay 20 MK_REP
203 Mavris 40 HR_REP
Create View CLERK as
SELECT * FROM EMP_2
WHERE job_id = 'PU_CLERK'
OR JOB_ID = 'SH_CLERK'
OR JOB_ID = 'ST_CLERK'
WITH CHECK OPTION;
update CLERK set JOB_ID = 'PU_MAN' where EMPLOYEE_ID = 198;
ERRORE SQL: ORA-00904: "JOB_ID": IDENTIFICATIVO NON VALIDO
01402. 00000 - "view WITH CHECK OPTION where-clause violation"
insert into CLERKvalues (200,'fran',30,'AC_ACCOUNT');
ERRORE SQL: ORA-00904: "JOB_ID": IDENTIFICATIVO NON VALIDO
01402. 00000 - "view WITH CHECK OPTION where-clause violation"
La seguente dml invece ha esito positivo.
INSERT INTO CLERK VALUES (200, 'fran', 30, 'SH_CLERK' );
CONSTRAINT constraintname è usato per nominare la costraint così da rendere più comprensibile il messaggio di errore.
L'istruzione Alter View è utilizzata per compilare una vista:
ALTER VIEW [schema.]nameView COMPILE;
In generale si usa ogni volta che viene modificata una delle tabelle della subquery.
Non è possibile aggiungere una colonna ad una vista già esistente. Occorre eseguire il drop e ricrearla a meno che non si sia utilizzato l'opzione OR REPLACE.
DROP VIEW [schema.]nameView;
Se vengono droppate le tabelle contenute nella subquery la view esiste ancora ma se viene interrogata restituisce il messaggio:
ORA-04063: view "HR.CLERK" contiene errori
*Cause: Attempt to execute a stored procedure or use a view that has
errors. For stored procedures, the problem could be syntax errors
or references to other, non-existent procedures. For views,
the problem could be a reference in the view's defining query to
a non-existent table.
Can also be a table which has references to non-existent or
inaccessible types.
*Action: Fix the errors and/or create referenced objects as necessary.
Se le tabelle vengono ricreate, la vista restituisce i risultati senza dover essere ricreata.
DML sulle viste
Non è possibile eseguire INSERT ad una vista se la subquery contiene:
1) la clausola GROUP BY
2) funzioni di gruppo
3) la pseudocolonna ROWNUM
4) DISTINCT
5) colonne definite da espressioni
6) colonne NOT NULL definite nelle tabelle basi ma non selezionate dalla vista.
Non è possibile eseguire DELETE in presenza di almeno una delle condizioni 1, 2, 3, 4.
Non è possibile eseguire UPDATE in presenza di almeno una delle condizioni 1, 2, 3, 4, 5.
Per visualizzare il nome delle viste, e la definizione delle stesse, di uno User, eseguire la query:
Select view_name, text
From USER_VIEWS;
