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 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)
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)
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)