Nessun risultato. Prova con un altro termine.
Guide
Notizie
Software
Tutorial

Le viste

Cosa sono e quando utilizzarle.
Cosa sono e quando utilizzarle.
Link copiato negli appunti

Cosa sono le views?

Le viste sono delle QUERY memorizzate con un proprio nome che possono essere considerate simili a tabelle virtuali. Sono una via efficace per mostrare informazioni che arrivano da una più tabelle. Le analogie tra tabelle e viste sono parecchie, ma dobbiamo considerare le limitazioni ed i requisiti necessari per poter utlizzare le viste.

Come si crea una view?

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

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

Ora supponiamo di voler creare una vista che ci dia l’elenco di tutti i prodotti per ogni categoria del database Northwind. Per prima cosa costruiamo la query utile a recuperare i dati che ci servono

SELECT 
Categories.CategoryName AS NomeCategoria,
        Products.ProductName AS
NomeProdotto,
      Products.QuantityPerUnit AS
Quantità
FROM Categories
INNER JOIN Products ON Categories.CategoryID = Products.CategoryID

Fatto questo non ci resta che memorizzare la query all’interno di una vista, che chiameremo V_ProdottiPerCategoria.

CREATE VIEW V_ProdottiPerCategoria AS
SELECT 
Categories.CategoryName AS NomeCategoria,
        Products.ProductName AS
NomeProdotto
,
      Products.QuantityPerUnit AS
Quantità

FROM Categories
INNER JOIN Products ON Categories.CategoryID = Products.CategoryID

In questo caso ho specificato il nome delle singole colonne dei dati all’interno della SELECT, ma sarebbe stato possibile fare questo anche a livello dell’intestazione della vista, ad esempio

CREATE VIEW V_ProdottiPerCategoria
(NomeCategoria,NomeProdotto,Quantità)
AS
SELECT 
Categories.CategoryName,
        Products.ProductName,
      Products.QuantityPerUnit
FROM Categories
INNER JOIN Products ON Categories.CategoryID = Products.CategoryID

NB. Ricordate che ogni colonna dati della vista deve avere un
nome ed il nome deve essere univoco
.

Ora che abbiamo creato la vista possiamo accedere molto rapidamente alla lista dei prodotti per categoria ongni qualvolta lo desideriamo, basterà una semplice SELECT del tipo: SELECT * FROM V_ProdottiPerCategoria.

Per modificare una vista esiste un’altra istruzione analoga alla precedente dal nome ALTER VIEW, vediamo la sintassi:

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

Come si può intuire questa istruzione modifica la struttura di una vista precedentemente creata, supponiamo ad esempio di voler aggiungere una colonna dati alla vista che abbiamo precedentemente creato

ALTER VIEW V_ProdottiPerCategoria (NomeCategoria,NomeProdotto,Quantità,InMagazzino)
AS
SELECT 
Categories.CategoryName,
        Products.ProductName,
      Products.QuantityPerUnit,
      Products.UnitsInStock
FROM Categories
INNER JOIN Products ON Categories.CategoryID = Products.CategoryID

Ecco fatto, basterà far girare nel QA il batch per la modifica della vista.

I limiti delle views

Non è possibile usare la clausola ORDER BY in una view

Questa limitazione può sembrare assurda, ma difatti non è possible specificare un ordinamento tramite la clausola ORDER BY per il ROWSET ritornato da una view. Il motivo di tutto ciò e che la view come la tabella ritorna un set di dati che non possiede alcun ordinamento. (Standard ANSI)

Per fare una prova lanciamo una ALTER sulla VIEW precedente provando ad inserire un ordinamento in base al nome della categoria

ALTER VIEW V_ProdottiPerCategoria
(NomeCategoria,NomeProdotto,Quantità,InMagazzino)
AS
SELECT 
    Categories.CategoryName,
        Products.ProductName,
      Products.QuantityPerUnit,
      Products.UnitsInStock
FROM Categories
INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
ORDER BY Categories.CategoryName

Una volta lanciata la modifica, SQL Server ci ritorna un errore di
questo tipo:

Server: Msg 1033, Level 15, State 1, Procedure V_ProdottiPerCategoria, Line 10
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

Il messaggio ci dice che la clausola ORDER BY non può essere
utilizzata in una vista senza che la clausola TOP sia specificata.

Quindi è gioco facile trovare la scappatoia per creare viste
ordinate, sarà sufficiente inserire la clausola TOP all’interno della query, dato che la clausola TOP supporta anche la percentuale di righe per prendere tutto il set di dati sarà necessario specificare TOP 100 PERCENT

ALTER VIEW V_ProdottiPerCategoria
(NomeCategoria,NomeProdotto,Quantità,InMagazzino)
AS
SELECT  TOP 100 PERCENT –non un numero fisso ma la
percentuale di righe 100%
    Categories.CategoryName,
        Products.ProductName,
      Products.QuantityPerUnit,
      Products.UnitsInStock
FROM Categories
INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
ORDER BY Categories.CategoryName

Non è possibile usare una SELECT INTO in una view
Non è possibile usare una clausola COMPUTE o COMPUTE BY
Non è possibile fare riferimento a tabelle temporanee o
variabili tabella
Nascondere la definizione di una vista

Grazie all’opzione WITH ENCRYPTION disponibile con le istruzioni CREATE ed ALTER VIEW, possiamo criptare la definizione delle nostre viste (in buona sostanza la natura della query) da occhi indiscreti.

Difatti normalmente quando creiamo una vista viene aggiunta una riga nella tabella di sistema syscomment la quale contiene il testo della vista, facciamo un esempio

SELECT text FROM dbo.syscomments
      WHERE id = object_id('V_ProdottiPerCategoria')

Con questa semplice SELECT possiamo recuperare la definizione della
vista, per ovviare a questo inconveniente proviamo a modificare la
vista con l’opzione WITH ENCRYPTION

ALTER VIEW dbo.V_ProdottiPerCategoria
(NomeCategoria,NomeProdotto,Quantità,InMagazzino)
WITH ENCRYPTION
AS
SELECT  TOP 100 PERCENT
    Categories.CategoryName,
        Products.ProductName,
      Products.QuantityPerUnit,
      Products.UnitsInStock
FROM dbo.Categories
INNER JOIN dbo.Products ON Categories.CategoryID =
Products.CategoryID
ORDER BY Categories.CategoryName

Ed ora se lanciamo la SELECT precedente per recuperare la definizione dell’oggetto V_ProdottiPerCategoria  vedrete che la definizione è stata criptata e non più accessibile. Solo SQL Server è in grado di risolvere l’algoritmo di criptazione ed eseguire correttamente la query contenuta nella vista.

Le viste come meccanismo di sicurezza

SQL Server gestisce i permessi di accesso ai dati sia a livello di tabella che e livello di colonna. Ma a differenza di altri RDBMS SQL Server non gestisce i permessi di accesso a livello di riga. In questo caso ci vengono in aiuto le viste per implementare meccanismi di sicurezza orizzontale sui dati.

Difatti attraverso le viste possiamo filtrare solo i dati (le righe della tabella) che ci interessano e concedere i relativi permessi solo agli utenti o i gruppi pertinenti, questi infine potranno lavorare con le viste come se fossero tabelle vere e proprie.

Ma per poter concedere tali permessi sulle viste non dobbiamo necessariamente disporre degli opportuni permessi sugli oggetti sottostanti a cui fa riferimento la vista a patto che vengano soddisfatti i seguenti requisiti:

  1. Il proprietario della view e quello degli oggetti sottostanti sono la stessa persona
  2. Se la vista fa riferimento ad un oggetto in un altro database, la login che sta eseguendo la query sulla vista deve avere accesso a quel database.

Se non dovesse essere soddisfatto primo requisito dobbiamo concedere all’utente i permessi a tutti gli oggetti cui la vista fa riferimento.

Facciamo un esempio, supponiamo voler creare una vista per visualizzare gli autori e i libri da loro scritti

CREATE VIEW dbo.V_AutoriPerTitoli AS
  SELECT
    T.title AS "Titolo",
    A.au_lname + ' ' + A.au_fname AS
"Autore libro",
    A.state AS "Stato di
nascita"   
           
  FROM
    dbo.titles T
  INNER JOIN dbo.titleauthor TA ON TA.title_id =
T.title_id
  INNER JOIN dbo.authors A ON A.au_id = TA.au_id

In questo caso la vista  dbo.V_AutoriPerTitoli  fa
riferimento a tre tabelle tutte di proprietà del possessore
della vista, quindi per concedere l’uso di questa vista all’utente gino sarà sufficiente eseguire il comando:

GRANT SELECT ON dbo.V_AutoriPerTitoli TO gino

Osserviamo bene una cosa, non è necessario per accedere ai dati che l’utente gino abbia i permessi sugli oggetti a cui fa riferimento la vista dbo.V_AutoriPerTitoli (le tabelle dbo.titles, dbo.titleauthor, dbo.authors).

Ora vediamo un altro caso, la vista dbo.V_AutoriPerTitoli2
fa riferimento a due oggetti che non sono di proprietà del possessore della vista (dbo).

CREATE VIEW dbo.V_AutoriPerTitoli2 AS
  SELECT
    T.title AS "Titolo",
    A.au_lname + ' ' + A.au_fname AS
"Autore libro",
    A.state AS "Stato di
nascita"   
           
  FROM
    dbo.titles T
  INNER JOIN pippo.titleauthor TA ON TA.title_id =
T.title_id
  INNER JOIN pippo.authors A ON A.au_id = TA.au_id

In questo caso non sarà sufficiente concedere all’utente gino i permessi sulla vista come nel caso precedente, ma dovremo concedere esplicitamente i permessi anche sugli oggetti a cui la vista fa riferimento:

GRANT SELECT ON pippo.titleauthor TO gino

GRANT SELECT ON pippo.authors TO gino

Nella prossima puntata impareremo a sfruttare le potenzialità delle stored procedure.


Ti consigliamo anche