Viste visione di tabelle virtuali -AD9

Viste

Le viste sono delle QUERY memorizzate con un proprio nome che possono essere considerate simili a tabelle virtuali. Le analogie tra tabelle e viste sono parecchie, ma dobbiamo considerare le limitazioni ed i requisiti necessari per poter utlizzare le viste. Esse offrono la “visione” di tabelle virtuali Sono Classificate in:
– semplici (selezione e proiezione su una sola tabella)
– complesse
La Sintassi è:
create view NomeVista [ (ListaAttributi) ] as SelectSQL
[ with [ local | cascaded ] check option ]<

Come si crea una view?

Creare una view è una cosa  semplice, esiste una istruzione CREATE VIEW appositamente usata per questo scopo. Vediamo adesso la sintassi ed  un esempio:

CREATE VIEW [ < nome_database > . ] [ < proprietario > . ] nome_vista [ ( colonna [ ,…n ] ) ]
[ WITH < attributi_vista > [ ,…n ] ]
AS
istruzione_SELECT
[ WITH CHECK OPTION ]

Viste
• Le viste in SQL-2 possono contenere nella definizione altre viste precedentemente definite, ma non vi può essere mutua dipendenza (la ricorsione è stata introdotta in SQL:1999)
• Le viste possono essere usate per formulare query complesse
– Le viste decompongono il problema e producono una soluzione più leggibile.
• Le viste sono talvolta necessarie per esprimere alcune query:

– query che combinano e nidificano diversi operatori aggregati
– query che fanno un uso sofisticato dell’operatore di unione.

 Composizione delle viste con le query

• Vista:

create view OrdiniPrincipali as
select *
from Ordine
where Importo > 10000

• Query:

select CodCli
from OrdiniPrincipali

• Composizione della vista con la query:

select CodCli
from Ordine
where Importo > 10000

Viste e query

• Estrarre il cliente che ha generato il massimo fatturato (senza usare le viste):

select CodCli
from Ordine
group by CodCli
having sum(Importo) >= all

(select sum(Importo)
from Ordine
group by CodCli)

• Questa soluzione può non essere riconosciuta da tutti i sistemi SQL.

Viste e query

• Estrarre il cliente che ha generato il massimo fatturato (usando le viste):

create view CliFatt(CodCli,FattTotale) as
select CodCli, sum(Importo)
from Ordine
group by CodCli

select CodCli
from CliFatt
where FattTotale = (select max(FattTotale)
from CliFatt)

Viste e query

• Estrarre il numero medio di ordini per cliente:

– Soluzione scorretta (SQL non permette di applicare gli operatori aggregati in cascata):

select avg(count(*))
from Ordine
group by CodCli

– Soluzione corretta (usando una vista):

create view CliOrd(CodCli,NumOrdini) as
select CodCli, count(*)
from Ordine
group by CodCli

select avg(NumOrdini)
from CliOrd

Esempio di vista semplice

Ordini di importo superiore a 10.000

create view OrdiniPrincipali as
select *
from Ordine

where Importo > 10000

viste database

 

 

Viste semplici in cascata

create view ImpiegatoAmmin
(Matr,Nome,Cognome,Stipendio) as
select Matr, Nome, Cognome, Stipendio
from Impiegato
where Dipart = ’Amministrazione’
and Stipendio > 10

create view ImpiegatoAmminJunior as
select *
from ImpiegatoAmmin
where Stipendio < 50
with check option

Modifiche tramite le viste

• Vista:

create view OrdiniPrincipali as
select *
from Ordine
where Importo > 10000

• Modifica:

update OrdiniPrincipali
set Importo = Importo * 1.05
where CodCli = ’45’

• Composizione della vista con la modifica:

update Ordine
set Importo = Importo * 1.05
where CodCli = ’45’
and Importo > 10000

Check option

La check option interviene quando viene aggiornato il contenuto di una vista, per verificare che la tupla inserita/modificata appartenga alla vista
• Se l’opzione è local, il controllo viene fatto solo rispetto alla vista su cui viene invocato il comando
• Se l’opzione è cascaded, il controllo viene fatto su tutte le viste coinvolte
• Es.:

create view OrdiniPrinc70 as
select *
from OrdiniPrincipali
where CodCli = ’70’
with local check option

Check option

update OrdiniPrinc70
set CodCli = ’71’
where CodOrd = ’754’

viene rifiutato con check option local e cascaded

update OrdiniPrinc70
set Importo = 5000
where CodOrd = ’754’

viene accettato dalla local, rifiutato dalla cascaded.

Esempio di vista complessa

create view CliPro(Cliente,Prodotto) as
select CodCli, CodProd
from Ordine join Dettaglio
on Ordine.CodOrd = Dettaglio.CodOrd

Vista complessa (JOIN)

viste complesse

Interrogazione sulla vista complessa

• Query:
select Cliente
from CliProd
where Prodotto = ’45’

• Composizione della vista con la query:

select CodCli
from Ordine join Dettaglio
on Ordine.CodOrd = Dettaglio.CodOrd
where CodProd = ’45’

Modifiche sulla vista complessa

• Non è possibile modificare le tabelle di base tramite la vista perché l’interpretazione è ambigua

• Es.: update CliProd
set Prodotto = ’42’
where Cliente = ’12’

• Due alternative per la realizzazione sulle tabelle di base

– il cliente ha cambiato l’ordine
– il codice del prodotto è cambiato

Vista complessa (JOIN)

viste complesse

Ricorsione in SQL:1999

with recursive Raggiungibile (Orig,Dest,Costo) as
( select Orig, Dest, Costo
from Volo where Orig = ‘Milano’
union
select V.Orig, R.Dest, V.Costo+R.Costo
from Volo V join Raggiungibile R
on V.Dest = R.Orig )

select distinct Dest, Costo
from Raggiungibile R
where Costo = (select min(Costo)
from Raggiungibile R1
where R.Dest = R1.Dest)