Viste

Cos’è una vista?

• è possibile creare un subset logico di dati o una combinazione di dati

• una vista è una tabella logica basata su una tabella o su un’altra vista

• una tabella su cui è basata la vista è chiamata “base table”

Perché usare una vista?

• per avere l’accesso ad un insieme ristretto di dati perché la vista può visualizzare una selezione di colonne di una tabella

• utile per fare query semplici ottenendo risultati di query complesse, per esempio una vista può essere usata per ottenere informazioni da tabelle multiple senza che l’utente sappia come scrivere il join

• una vista può essere usata per interrogare diverse tabelle, così ogni utente può creare la propria vista per prendere dati dalle stesse tabelle ma con criteri differenti

Viste semplici e viste complesse

Vista semplice: - deriva da una sola tabella - non contiene funzioni - si possono effettuare operazioni DML tramite la vista

Vista complessa: - deriva da più tabelle - può contenere funzioni - non sempre si possono effettuare operazioni DML tramite la vista

Creare una vista

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW nome [(alias [,alias]…)]AS subquery[WITH CHECK OPTION [CONSTRAINT nome_cons][WITH READ ONLY [CONSTRAINT nome_cons]];

OR REPLACE ricrea la vista se già esistente

FORCE crea la vista controllando se esiste la base table

NOFORCE default – crea la vista solo se la base table esiste

ALIAS specifica i nomi delle espressioni selezionate dalla

vista. Il numero di alias deve essere uguale al numero di espressioni

selezionate dalla vista

WITH CHECK OPTION specifica che solo le righe accessibili dalla vista

possono

essere inserite o modificate

WITH READ ONLY indica che non possono essere effettuate operazioni

DML sulla vista

Creare una vista

Creare una vista che contenga gli impiegati del dipartimento 80

CREATE VIEW empvu80 AS SELECT employee_id, last_name, salary FROM employees WHERE department_id = 80;

È possibile vedere la struttura della vista con il comando DESCRIBE usato per le tabelle.

DESCRIBE empvu80

• La subquery che definisce una vista non può contenere la clausola ORDER BY. Verrà usata nella SELECT di interrogazione della vista.

Creare una vista

CREATE VIEW salvu50 AS SELECT employee_id ID_NUMBER, last_name NAME, salary*12 ANN_SALARY FROM employees WHERE department_id = 50;

È possibile inserire gli alias dopo la CREATE VIEW ma prima della subquery

CREATE VIEW salvu50 (ID_NUMBER, NAME, ANN_SALARY) AS SELECT employee_id, last_name, salary*12 FROM employees WHERE department_id = 50;

Il numero di alias deve coincidere con il numero di campi nella SELECT list

Interrogare una vista

SELECT *FROM salvu50;

È possibile interrogare una vista esattamente come una tabella, visualizzando tutti i valori o specificando le colonne e le righe da visualizzare

Cosa succede quando si interroga una vista?

SELECT *FROM salvu50;

1)Oracle server verifica la definizione della vista nella tabella del dizionario dati USER_VIEWS (all’interno della tabella la SELECT che genera la vista è memorizzata in una colonna di tipo LONG)

2) Oracle Server controlla i privilegi sulla base table

3) converte la query in un’operazione sulla base table. In pratica i dati sono restituiti o modificati direttamente sulla base table

Modificare una vista

CREATE OR REPLACE VIEW empvu80 (id_number, name, sal, department_id) AS SELECT employee_id, first_name||’ ‘||last_name, salary, department_id FROM employees WHERE department_id = 80;

Modificare la vista usando la clausola CREATE OR REPLACE, in modo che creando la vista la sostituirà ad una eventualmente già esistente. Questo significa che la vista può essere creata senza doverla cancellare, ricreare e ridare le grant.

Creare una vista complessa

CREATE VIEW dept_sum_vu (name, minsal, maxsal, avgsal) AS SELECT d.department_name, MIN(e.salary), MAX(e.salary), AVG(e.salary) FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY d.department_name;

Gli alias sulle colonne sono obbligatori quando nella select list ci sono funzioni o espressioni

Regole per effettuare operazioni DML sulle viste

• è possibile effettuare operazioni DML sulle viste semplici

• non è possibile cancellare una riga se la vista contiene :

- funzioni di gruppo

- una clausola GROUP BY

- DISTINCT

- la pseudocolonna ROWID

Regole per effettuare operazioni DML sulle viste

• non è possibile modificare dati tramite la vista se la contiene :

- funzioni di gruppo

- una clausola GROUP BY

- DISTINCT

- la pseudocolonna ROWID

- colonne definite da un’espressione (esempio SALARY*12)

Regole per effettuare operazioni DML sulle viste

• non è possibile aggiungere dati tramite la vista se la contiene :

- funzioni di gruppo

- una clausola GROUP BY

- DISTINCT

- la pseudocolonna ROWID

- colonne definite da un’espressione (esempio SALARY*12) - colonne NOT NULL nella base table che non sono state selezionate con la vista

WITH CHEK OPTION

• la clausola specifica che INSERT e UPDATE effettuati tramite la vista non possano creare o modificare dati che la vista non possa selezionare. In questo modo viene forzato un constraint sull’integrità e la validazione (check) dei dati prima di essere inseriti o modificati.

CREATE OR REPLACE VIEW empvu2 AS SELECT * FROM employees WHERE department_id = 20 WITH CHECK OPTION CONSTRAINT empvu20_ck;

UPDATE empvu20 SET department_id = 10 WHERE employee_id = 201;

Restituisce errore perché la vista può vedere solo gli impiegati del dipartimento 20.

Cancellare una vista

DROP VIEW nome;

• lo statement DROP rimuove la definizione della vista dal database

• cancellando la vista non ci sono effetti sulla base table

• viste o altre applicazioni basate sulla vista cancellata diventano invalide

• solo il creatore o un utente dotato di privilegio DROP ANY VIEW può cancellare una vista

Inline view

• è una vista creata mettendo una subquery nella clausola FROM e definendo un ALIAS per la subquery

• la subquery diventa quindi la sorgente dati per la vista e può essere usata nello statement SQL

• una inline view non è uno schema object

Inline view

SELECT a.last_name, a.salary, a.department_id, b.maxsalFROM employees a, (SELECT department_id, max(salary) maxsal FROM employees GROUP BY department_id) bWHERE a.department_id = b.department_idAND a.salary < b.maxsal;

Nell’esempio la inline view b restituisce il dettaglio dei numeri di dipartimento e il salario massimo per ogni dipartimento della tabella EMPLOYEES.La query visualizza gli impiegati che guadagnano meno del massimo salario del loro dipartimento.

Top-N Analysis

Le query TOP-N rispondono alle domande:

- quali sono i 10 prodotti più venduti?

- quali sono i 10 prodotti meno venduti?

- i due rappresentanti che hanno venduto il

maggior numero di prodotti

- …

Top-N Analysis

SELECT [column list], ROWNUMFROM (SELECT [column list] FROM tabella ORDER BY Top-N_colonna)WHERE ROWNUM <= N;

• la clausola ORDER BY nella inline view assicura l’ordine di visualizzazione dei dati. Per risultati che considerano il valore più grande, occorre specificare il parametro DESC

• ROWNUM nella select list assegna un valore sequenziale che parte da 1 ad ogni riga restituita dalla query

• la clausola WHERE permette di ritornare solo N righe

Top-N Analysis

SELECT ROWNUM as RANK, last_name, salaryFROM (SELECT last_name, salary FROM employees ORDER BY salary DESC)WHERE ROWNUM <= 3;

l’esempio visualizza i 3 impiegati che hanno lo stipendio più alto.

la subquery restituisce il dettaglio di tutti gli impiegati ordinati secondo il salario in modo discendente.

la clausola WHERE fa in modo che solo i primi 3 record del result set vengano restituiti.