INDICE LINGUAGGIO SQL
1.01 - Database Relazionali
1.02 - Creare il database
1.03 - Popolare il database
1.04 - SELECT, interrogare il database
1.05 - Ricerca, filtraggio e ordinamento
1.06 - GROUP BY e funzioni di aggregazione
1.07 - UPDATE: modificare i dati
1.08 - DELETE, eliminare i record dalle tabelle
1.09 - Subquery
1.10 - Le transazioni
1.11 - Trigger
1.12 - Funzioni e procedure
1.13 - Modificare la struttura del database
1.14 - Utilizzo multiutente di un database
Dal sito HTML.it Guida linguaggio SQL
Chi ha necessità di utilizzare database sul proprio server non può prescindere dalla conoscenza di SQL (acronimo che
sta per Structured Query Language) il linguaggio per la creazione di database relazionali
Ritorna all'indice
1.01 - Database Relazionali
La struttura fondamentale del modello relazionale e’ appunto la relazione, cioe’ una tabella bidimensionale costituita
da righe (tuple) e colonne (attributi). Le relazioni rappresentano le entità che si ritiene essere interessanti nel database
Ogni istanza dell’entità trovera posto in una riga (tupla) della relazione, mentre gli attributi della relazione rappresente-
ranno le proprietà dell’entità.
Una relazione è la definizione della struttura della tabella, cioè il suo nome e l'elenco delle colonne (attributi) che la
compongono.
Ogni colonna (attributo) di una relazione è caratterizzato da un nome e da un dominio.
Il dominio indica quali valori possono essere assunti da una colonna della relazione (ad es. per il campo SESSO saranno
F, M). Una chiave esterna (foreign key) è una combinazione di attributi di una relazione che sono chiave primaria per
un’altra relazione.
Caratteristica fondamentale dei valori presenti in una chiave esterna è che, a meno che non siano null, devono corrispon-
dere a valori esistenti nella chiave primaria della relazione a cui si riferiscono.
Questa proprieta’ va sotto il nome di integrità referenziale (referential integrity)
Tutte le manipolazioni possibili sulle relazioni sono ottenibili grazie alla combinazione di cinque soli operatori:
REStrICT, PROJECT, TIMES, UNION e MINUS (operatori dell'algebra relazionale)
Per comodita’ sono stati anche definiti tre operatori addizionali che comunque possono essere ottenuti applicando i soli
cinque operatori fondamentali:
JOIN, INTERSECT e DIVIDE
Gli operatori relazionali ricevono come argomento una relazione o un insieme di relazioni
e restituiscono una singola relazione come risultato.
Vediamo brevemente questi otto operatori:
REStrICT: restituisce una relazione contenente un sottoinsieme delle tuple della relazione a cui
viene applicato. Gli attributi rimangono gli stessi.
PROJECT: restituisce una relazione con un sottoinsieme degli attributi della relazione a cui viene
applicato. Le tuple della relazione risultato vengono composte dalle tuple della relazione originale in modo che continuino
ad essere un insieme in senso matematico.
TIME: viene applicato a due relazioni ed effettua il prodotto cartesiano delle tuple.
Ogni tupla della prima relazione viene concatenata con ogni tupla della seconda.
JOIN: vengono concatenate le tuple di due relazioni in base al valore di un insieme dei loro attibuti.
UNION: applicando questo operatore a due relazioni compatibili, se ne ottiene una contenente le
tuple di entrambe le relazioni. Due relazioni sono compatibili se hanno lo stesso numero di attributi e gli attributi corrispon-
denti nelle due relazioni hanno lo stesso dominio.
MINUS: applicato a due relazioni compatibili, ne restituisce una terza contenente le tuple
che si trovano solo nella prima relazione.
INTERSECT: applicato a due relazioni compatibili, restituisce una relazione contenente le
tuple che esistono in entrambe le relazioni.
DIVIDE: applicato a due relazioni che abbiano degli attributi comuni, ne restituisce una
terza contenente tutte le tuple della prima relazione che possono essere fatte corrispondere a tutti i valori della seconda
relazione.
Nelle seguenti tabelle, a titolo di esempio, sono raffigurati i risultati dell’applicazione di alcuni operatori relazionali alle
relazioni Persone e Figli. Come nomi per le relazioni risultato si sono utilizzate le espressioni che le producono.
Persone
Figli
REStrICT (Persone)
sesso=’M’
PROJECT sesso (Persone)
JOIN (Persone, Figli)
Persone(numero_persona)=Figli(numero_persona)
I database relazionali compiono tutte le operazioni sulle tabelle utilizzando l’algebra relazionale, anche se normalmente
non permettono all’utente di utilizzarla.
L’utente interagisce con il database attraverso un’interfaccia differente, il linguaggio SQL, un linguaggio dichiarativo
che permette di descrivere insiemi di dati. Le istruzioni SQL vengono scomposte dal DBMS in una serie di operazioni
relazionali.
Ritorna all'indice
1.02 - Creare il database
Un database in un sistema relazionale e’ composto da un’insieme di tabelle, che corrispondono alle relazioni del modello
relazionale. Nella terminologia usata nell’SQL non si fa accenno alle relazioni, cosi’ come non viene usato il termine attributo,
ma viene usata la parola colonna, e non si parla di tupla, ma di riga.
Nel seguito verranno usate indifferentemente le due terminologie, quindi tabella varra’ per relazione, colonna per attributo, riga
per tupla, e viceversa.
In pratica la creazione del database consiste nella creazione delle tabelle che lo compongono.
In realta’ prima di poter procedere alla creazione delle tabelle normalmente occorre creare in effetti il database, il che di solito
significa definire uno spazio dei nomi separato per ogni insieme di tabelle.
In questo modo per un DBMS e’ possibile gestire piu’ database indipendenti contemporaneamente, senza che ci siano dei conflitti
con i nomi che vengono utilizzati in ciascuno di essi. Il sistema previsto dallo standard per creare degli spazi dei nomi separati
consiste nell’utilizzo dell’istruzione SQL CREATE SCHEMA. Di solito tale sistema non viene utilizzato (o almeno non con gli
scopi ed il significato previsti dallo standard), ma ogni DBMS prevede una procedura proprietaria per creare un database.
Normalmente viene esteso il linguaggio SQL introducendo un’istruzione non prevista nello standard: CREATE DATABASE.
La sintassi utilizzata da PostgreSQL, ma anche dai piu’ diffusi DBMS, e’ la seguente:
CREATE DATABASE nome_database
Una volta creato il database e’ possibile creare le tabelle che lo compogono. L’istruzione SQL preposta a questo scopo e’:
CREATE table nome_tabella (
nome_colonna tipo_colonna [ clausola_default ] [ vincoli_di_colonna ]
[ , nome_colonna tipo_colonna [ clausola_default ] [ vincoli_di_colonna ] … ]
[ , [ vincolo_di tabella] … ] )
Ad esempio se in Microsoft Access creo una nuova query e scrico il sequente codice:
CREATE table TAB1 (
COL1 CHAR,
COL2 INTEGER
)
avremo:
nome_colonna: e’ il nome della colonna che compone la tabella. Sarebbe meglio non esagerare con la lunghezza degli
identificatori di colonna, dal momento che l’SQL Entry Level prevede nomi non piu’ lunghi di 18 caratteri. Si consulti
comunque la documentazione dello specifico database. I nomi devono iniziare con un carattere alfabetico.
tipo_colonna: e’ l’indicazione del tipo di dato che la colonna potra’ contenere. I principali tipi previsti dallo standard
SQL sono:
CHARACTER(n): una stringa a lunghezza fissa di esattamente n caratteri. CHARACTER puo’ essere abbreviato
con CHAR
CHARACTER VARYING(n): una stringa a lunghezza variabile di al massimo n caratteri. CHARACTER VARYING
puo’ essere abbreviato con VARCHAR o CHAR VARYING.
INTEGER: un numero intero con segno. Puo’ essere abbreviato con INT. La precisione, cioe’ la grandezza del
numero intero che puo’ essere memorizzato in una colonna di questo tipo, dipende dall’implementazione del
particolare DBMS.
SMALLINT: un numero intero con segno con precisione non superiore a INTEGER.
FLOAT(p): un numero a virgola mobile, con precisione p. Il valore massimo di p dipende dall’implementazione
del DBMS. E’ possibile usare FLOAT senza indicazione della precisione, utilizzando quindi la precisione di default,
anch’essa dipendente dall’implementazione. REAL e DOUBLE PRECISION sono dei sinonimi per un FLOAT con
una particolare precisione. Anche in questo caso le precisioni dipendono dall’implementazione, con il vincolo che la
precisione del primo non sia superiore a quella del secondo.
DECIMAL(p,q): un numero a virgola fissa di almeno p cifre e segno, con q cifre dopo la virgola. DEC e’ un
abbreviazione per DECIMAL. DECIMAL(p) e’ un abbreviazione per DECIMAL(p,0).
Il valore massimo di p dipende dall’implementazione.
INTERVAL: un periodo di tempo (anni, mesi, giorni, ore, minuti, secondi e frazioni di secondo).
DATE, TIME e TIMESTAMP: un preciso istante temporale. DATE permette di indicare l’anno, il mese e il giorno.
Con TIME si possono specificare l’ora, i minuti e i secondi. TIMESTAMP e’ la combinazione dei due precedenti.
I secondi sono un numero con la virgola, permettendo cosi’ di specificare anche frazioni di secondo.
clausola_default: indica il valore di default che assumera’ la colonna se non gliene viene assegnato uno esplicitamente nel
momento della creazione della riga. La sintassi da utilizzare e’ la seguente:
DEFAULT { valore | NULL }
dove, valore e’ un valore valido per il tipo con cui la colonna e’ stata definita.
vincoli_di_colonna: sono vincoli di integrita’ che vengono applicati al singolo attributo. Sono:
NOT NULL, che indica che la colonna non puo’ assumere il valore NULL.
PRIMARY KEY, che indica che la colonna e’ la chiave primaria della tabella.
una definizione di riferimento, con cui si indica che la colonna e’ una chiave esterna verso la tabella e i campi
indicati nella definizione. La sintasi e’ la seguente:
REFERENCES nome_tabella [ ( colonna1 [ , colonna2 ... ] ) ]
[ ON DELETE { CASCADE | SET DEFAULT | SET NULL } ]
[ ON UPDATE { CASCADE | SET DEFAULT | SET NULL } ]
Le clausole ON DELETE e ON UPDATE indicano quale azione deve essere compiuta nel caso in cui una riga (tupla)
nella tabella referenziata venga eliminata o aggiornata. Infatti in tali casi nella colonna referenziante (che e’ quella
che si sta definendo) potrebbero esserci dei valori inconsistenti. Le azioni possono essere:
CASCADE: eliminare la tupla contenente la colonna referenziante (nel caso di ON DELETE) o aggiornare anche
la colonna referenziante (nel caso di ON UPDATE).
SET DEFAULT: assegnare alla colonna referenziante il suo valore di default.
SET NULL: assegnare alla colonna referenziante il valore NULL.
un controllo di valore, con il quale si permette o meno l’assegnazione di un valore alla colonna, in base al risultato di
un espressione. La sintassi da usare e’:
CHECK (espressione_condizionale)
dove espressione_condizionale e’ un’espressione che restituisce vero o falso.
Ad esempio, se stiamo definendo la colonna COLONNA1, definendo il seguente controllo:
CHECK ( COLONNA1 < 1000 )
in tale colonna potranno essere inseriti solo valori inferiori a 1000.
vincoli_di_colonna: sono vincoli di integrita’ che possono riferirsi a piu’ colonne della tabella. Sono:
la definizione della chiave primaria:
PRIMARY KEY ( colonna1 [ , colonna2 ... ] )
Si noti che in questo caso, a differenza della definizione della chiave primaria come vincolo di colonna, essa puo’ essere
formata da piu’ di un attributo.
le definizioni delle chiavi esterne:
FOREIGN KEY ( colonna1 [ , colonna2 ... ] ) definizione_di_riferimento
La definizione_di_riferimento ha la stessa sintassi e significato di quella che puo’ comparire come vincolo di colonna.
un controllo di valore, con la stessa sintassi e significato di quello che puo’
essere usato come vincolo di colonna.
Per chiarire meglio l’utilizzo dell’istruzione CREATE table, esaminiamo alcuni comandi che implementano il database
bibliografico di esempio.
CREATE table Publication (
ID INTEGER PRIMARY KEY,
type CHAR(18) NOT NULL
);
In Access avremo:
La precedente istruzione crea la tabella Publication, formata dalle due colonne ID di tipo INTEGER, e type di tipo CHAR(18).
ID e’ la chiave primaria della relazione. Sull’attributo type e’ posto un vincolo di non nullita’.
CREATE table Book (
ID INTEGER PRIMARY KEY REFERENCES Publication(ID),
title VARCHAR(160) NOT NULL,
publisher INTEGER NOT NULL REFERENCES Publication(ID),
volume VARCHAR(16),
series VARCHAR(160),
edition VARCHAR(16),
pub_month CHAR(3),
pub_year INTEGER NOT NULL,
nota VARCHAR(255)
);
In Access avremo la creazione della nuova tabelle e della relazione tra book e Pubblication:
Creiamo anche:
CREATE table Person (
ID INTEGER PRIMARY KEY,
Names CHAR(18) NOT NULL,
Surname CHAR(18) NOT NULL,
Address CHAR(32),
CAP INTEGER,
City CHAR(18),
Country CHAR(2)
);
Crea la relazione Book, formata da nove attributi. La chiave primaria e’ l’attributo ID, che e’ anche una chiave esterna
verso la relazione Publication. Sulle colonne (attributi) title, publisher e pub_year sono posti dei vincoli di non nullità.
Inoltre l’attributo publisher e’ una chiave esterna verso la tabella Publisher.
CREATE table Author (
publicationID INTEGER REFERENCES Publication(ID),
personID INTEGER REFERENCES Person(ID),
PRIMARY KEY (publicationID, personID)
);
Crea la relazione Author, composta da due attributi: publicationID e personID. La chiave primaria in questo caso è
formata dalla combinazione dei due attributi, come indicato dal vincolo di tabella PRIMARY KEY. PublicationID è
una chiave esterna verso la relazione Publication, mentre personID lo e’ verso la relazione Person.
Ritorna all'indice
1.03 - Popolare il database
INSERT, inserire una riga in una tabella
L’istruzione SQL che effettua l’inserimento di una nuova riga in una tabella è INSERT. La sintassi con cui essa viene usata
piu’ comunemente è:
INSERT INTO nome_tabella [ ( elenco_campi ) ] VALUES ( elenco_valori )
nome_tabella: è il nome della tabella in cui deve essere inserita la nuova riga;
elenco_campi: è l’elenco dei nomi dei campi a cui deve essere assegnato un valore, separati fra loro da una virgola.
I campi non compresi nell’elenco assumeranno il loro valore di default o NULL se non hanno un valore di default.
È un errore non inserire nell’elenco un campo che non abbia un valore di default e non possa assumere il valore NULL.
Se l’elenco non viene specificato dovranno essere specificati i valori di tutti i campi della tabella.
elenco_valori: è l’elenco dei valori che saranno assegnati ai campi della tabella, nell’ordine e numero specificati
dall’elenco_campi o in quello della definizione della tabella (se elenco_campi non viene specificato).
I valori possono essere un’espressione scalare del tipo appropriato per il campo o le keyword DEFAULT o NULL, se il campo
prevede un valore di default o ammette il valore NULL.
Il precedente esempio di inserimento viene quindi eseguito tramite le seguenti istruzioni SQL (una alla volta in ACCESS):
INSERT INTO Person VALUES ( 1, 'Agosti', 'Maristella' );
INSERT INTO Person VALUES ( 2, 'Benfante', 'Lucio' );
INSERT INTO Person VALUES ( 3, 'Melucci', 'Massimo' );
INSERT INTO Person VALUES ( 4, 'Spaccapietra', 'S.' );
INSERT INTO Person VALUES ( 5, 'Maryansky', 'F.' );
INSERT INTO Institution ( ID, name, city, country )
VALUES ( 1, '7th IFIP 2.6 Working Conference on Database Semantics (DS-7)',
'Leysin', 'Switzerland' );
INSERT INTO Publication VALUES ( 1, 'Proceedings' );
INSERT INTO Publication VALUES ( 2, 'InProceedings' );
INSERT INTO Proceedings ( ID, title, organization, pub_month, pub_year )
VALUES ( 1, 'Searching for Semantics: Data Mining, Reverse Engineering',
1, 'Oct', 1997 );
INSERT INTO InProceedings ( ID, proceedingsID, title, pages )
VALUES ( 2, 1,
'OFAHIR: "On-the-Fly" Automatic Authoring of Hypertexts for Information Retrieval',
'129-154' );
INSERT INTO Author VALUES ( 2, 1 );
INSERT INTO Author VALUES ( 2, 2 );
INSERT INTO Author VALUES ( 2, 3 );
INSERT INTO Editor VALUES ( 1, 4 );
INSERT INTO Editor VALUES ( 1, 5 );
Un’altra forma abbastanza utilizzata dell’istruzione INSERT segue la seguente sintassi:
INSERT INTO nome_tabella [ ( elenco_campi ) ]
istruzione_select
L’unica differenza con la precedente sintassi consiste nella sostituzione della clausola VALUES con un istruzione SELECT.
SELECT permette di estrarre dalle tabelle del database dei dati che vengono organizzati in una nuova relazione.
La precedente istruzione INSERT permette quindi di inserire nella tabella e nei campi specificati dati provenienti da altre tabelle.
Ovviamente, affinché l’istruzione venga eseguita con successo, i dati prodotti dall’istruzione SELECT dovranno essere compatibili
con i vincoli ed i domini dei campi della tabella in cui si sta effettuando l’inserimento.
Ritorna all'indice
1.04 - SELECT, interrogare il database
La SELECT è l'istruzioni che ci permettono di estrarre i dati che ci interessano.
La sintassi completa è la seguente:
SELECT [DISTINCT ] lista_elementi_selezione
FROM lista_riferimenti_tabella
[ WHERE espressione_condizionale ]
[ GROUP BY lista_colonne ]
[ HAVING espressione_condizionale ]
[ ORDER BY lista_colonne ]
Le uniche clausole obbligatorie sono SELECT e FROM.
Quest’ultima, detta clausola di selezione, stabilisce da quale tabella estrarre i dati, mentre la prima è detta
clausola di proiezione e stabilisce quali colonne devono essere riportate nel risultato finale.
La clausola WHERE definisce invece un filtro sulle righe che verranno analizzate, mentre ORDER BY indica l’ordinamento da
applicare al risultato finale. Se WHERE non viene specificata, non viene applicato alcun filtro sulle righe, che quindi vengono
esaminate tutte. Se invece non viene specificato alcun ordinamento, le righe vengono restituite senza un ordine definito,
generalmente così come vengono trovate in tabella.
Per quanto riguarda selezione e la proiezione, di cui ci occupiamo in questa lezione, vediamo subito un esempio:
SELECT Person.surname FROM Person
Questa query estrae dalla tabella Person tutti gli elementi, restituendo solamente la colonna surname.
Si possono indicare più colonne separandole con una virgola:
SELECT Person.surname, Person.names FROM Person
La sintassi completa prevede di specificare la colonna con la tabella di appartenenza;
quest’ultima, inoltre, si può omettere se non ci sono ambiguità:
SELECT surname, names FROM Person
Si può utilizzare l’asterisco (*) per indicare che si vogliono analizzare tutte le colonne di una tabella. Ad esempio:
SELECT Person.* FROM Person
che, se non ci sono ambiguità, equivale alla forma più comunemente utilizzata:
SELECT * FROM Person
È possibile anche specificare dei nomi alternativi (alias) per le colonne restituite come risultato, usando la parola chiave AS:
SELECT surname, names AS Allias_Names FROM Person
Il risultato produrrà due colonne, la prima denominata surname, e la seconda Allias_Names (in virtù dell’alias definito).
L’uso degli alias è particolarmente comodo quando si usano le espressioni: infatti, è possibile utilizzare espressioni SQL nella
clausola di proiezione per effettuare calcoli o elaborazioni sui valori presenti nei campi.
Ad esempio, la seguente interrogazione restituisce il cognome e l’iniziale del nome di ogni persona:
SELECT surname, MID(names, 1, 1) AS Iniziale FROM Person
PS: SUBSTRING in Access è sostituita da MID
Le possibili espressioni utilizzabili sono moltissime e dipendono in gran parte dal database utilizzato, quindi spesso le stesse
funzioni hanno nomi o sintassi diverse in MySQL, Oracle o SQL Server. Purtroppo ciò limita la portabilità delle espressioni tra i
vari database. Per i dettagli è quindi necessario ricorrere alla documentazione ufficiale dei singoli database.
Ci sono funzioni per lavorare con le date, con i numeri, con le stringhe di testo o per gestire i valori NULL.
Ad esempio, la funzione COALESCE restituisce il primo valore non nullo tra le espressioni indicate:
SELECT COALESCE(surname, given_names, 'Senza nome') AS name FROM Person
Esistono anche funzioni condizionali, come ad esempio CASE, che restituisce un valore in base al soddisfacimento di un certo predicato.
Per esempio, la seguente query calcola l’iniziale solo quando (CASE WHEN) il nome della persona è più lungo (LENGTH) di cinque caratteri:
SELECT CASE WHEN LENGTH(given_names) > 5
THEN CONCAT(SUBSTRING(given_names, 1, 1), '.')
ELSE given_names
END AS Abbrev
FROM Person
Si possono concatenare più espressioni CASE WHEN, esattamente come un’espressione else if del C o degli altri linguaggi imperativi.
Nel prossimo capitolo vedremo una particolare classe di funzioni, dette di aggregazione, che possono essere usate per eseguire operazioni
quali conteggi, somme, medie.
DISTINCT, eliminare i duplicati
La prima query che abbiamo visto restituirebbe righe duplicate nel caso in cui nella tabella fossero presenti persone con lo stesso cognome.
Per evitare ciò occorre specificare l’opzione DISTINCT:
SELECT DISTINCT surname FROM Person
Questa opzione fa sì che nel risultato non ci siano righe uguali tra loro, ed è utile soprattutto quando si lavora con i join.
Un join (congiunzione) è una funzione che stabilisce come combinare righe di due tabelle diverse per ottenere una tabella unica.
Si distinguono generalmente tre tipi di join, che vediamo di seguito.
Inner join
Questa operazione, che in SQL è implementata dalla clausola INNER JOIN o semplicemente JOIN, combina solo le righe delle due tabelle che
soddisfano un certo predicato di confronto, come in un’operazione di intersezione.
Si possono effettuare confronti tra date o numeri; ad esempio per avere tutti i libri usciti dopo la data di pubblicazione di ogni articolo:
SELECT Book.title, Article.title
FROM Book
JOIN Article on Article.pub_year > Book.pub_year
Comunque nella maggior parte dei casi questo predicato è un’uguaglianza tra ID:
SELECT surname, given_names, title
FROM Person
JOIN Author on person.ID = Author.personID
JOIN Book on Book.ID = Author.publicationID
Questa query restituisce i nominativi e i titoli dei libri pubblicati.
Outer join
La query precedente restituirà solo i libri e le persone per cui esiste un collegamento.
Quindi non restituirà né le persone che non hanno scritto libri né i libri senza autore.
A seconda se vogliamo anche questi avremo rispettivamente un LEFT OUTER o un RIGHT OUTER JOIN. Ad esempio:
SELECT surname, given_names, title
FROM Person
LEFT JOIN Author on person.ID = Author.personID
LEFT JOIN Book on Book.ID = Author.publicationID
Come si vede la parola chiave OUTER è opzionale.
In questo caso, per le persone che non hanno pubblicato un libro, la colonna title avrà valore NULL.
MySQL non supporta nativamente il FULL OUTER JOIN, che permette di fare un join destro e sinistro simultaneamente, per ottenere,
nel nostro caso, oltre agli autori e ai libri anche i libri pubblicati anonimamente e le persone che non hanno scritto libri:
SELECT surname, given_names, title
FROM Person
FULL JOIN Author on person.ID = Author.personID
FULL JOIN Book on Book.ID = Author.publicationID
Unioni
L’operazione sistemistica di unione si può fare in SQL utilizzando la parola chiave UNION.
Ad esempio, per effettuare qualcosa di simile ad un FULL JOINcon MySQL si può scrivere:
SELECT surname, given_names, title FROM Person
LEFT JOIN Author on person.ID = Author.personID
LEFT JOIN Book on Book.ID = Author.publicationID
UNION
SELECT surname, given_names, title FROM Person
RIGHT JOIN Author on person.ID = Author.personID
RIGHT JOIN Book on Book.ID = Author.publicationID
Nell’uso di UNION bisogna fare in modo che le colonne delle varie SELECT da concatenare abbiano lo stesso numero e siano dello
stesso tipo altrimenti si avrà un errore.
Proprio come l’operazione sistemistica di unione, questa ignora le righe duplicate. Quindi, come per DISTINCT, è necessario prestare
attenzione nell’utilizzo perché se le righe restituite sono in grande quantità, l’eliminazione dei duplicati può impegnare molto tempo
il server. Per evitare la ricerca dei duplicati su deve usare l’istruzione UNION ALL:
SELECT surname, given_names, title FROM Person
JOIN Author on person.ID = Author.personID
JOIN Book on Book.ID = Author.publicationID
UNION ALL
SELECT surname, given_names, title FROM Person
JOIN Author on person.ID = Author.personID
JOIN Article on Article.ID = Author.publicationID
Ritorna all'indice
1.05 - Ricerca, filtraggio e ordinamento
Continuiamo con l’istruzione SELECT e stavolta vediamo le clausole WHERE e ORDER BY.
Filtraggio
Già nella lezione precedente, quando abbiamo parlato di inner join, abbiamo visto un particolare tipo di filtraggio, l’intersezione tra insiemi.
Ora vediamo invece come, con la clausola WHERE, possiamo filtrare le nostre righe in modo molto preciso sulla base dei valori che assumono i
campi.
In generale, la sintassi di questa clausola è un espressione che può essere o vera o falsa per una certa riga del nostro insieme di dati.
L’espressione più semplice ovviamente è l’eguaglianza:
SELECT *
FROM Person
WHERE surname = 'Knuth'
In questo caso verranno restituite le persone aventi un certo cognome. Usando l’operatore AND possiamo combinare due espressioni per richiedere
di restituire le righe che soddisfano entrambe le condizioni:
SELECT * FROM Person
WHERE surname = 'Knuth' AND given_names = 'Donald'
In questo caso, nel nostro database di esempio, non avremo alcun risultato perché l’operatore di uguaglianza (=) viene soddisfatto solo se i
valori sono esattamente uguali, mentre ‘Donald E.‘ è diverso da ‘Donald‘. Con l’operatore LIKE, utilizzabile solamente con i dati di tipo testuale
come VARCHAR, possiamo invece cercare i valori che corrispondono ad un certo pattern. Si possono utilizzare due caratteri jolly:
- il carattere % verrà soddisfatto da qualsiasi sequenza di caratteri trovata;
- il carattere _ verrà soddisfatto da qualsiasi carattere (singolo) trovato.
Stavolta, quindi, questa interrogazione restituirà esattamente il record di Donald E. Knuth:
SELECT * FROM Person
WHERE surname = 'Knuth' AND given_names LIKE 'Donald%'
Se non viene specificato alcun carattere jolly, LIKE si comporta esattamente come un’uguaglianza semplice.
Si noti che MySQL supporta anche la parola chiave REGEXP, che effettua un confronto tramite espressioni regolar, molto più potenti della
ricerca tramite LIKE. Oracle supporta la funzione REGEXP_LIKE. Ad esempio, con la seguente interrogazione MySQL si ottengono i libri che
nel titolo contengono prima il testo “Object” poi qualsiasi carattere, quindi il testo “Oriented” e infine un testo che può essere o
"Databases" o "Concepts":
SELECT * FROM Book
WHERE title REGEXP 'Object.*Oriented.*(Databases|Concepts)'
In Oracle la sintassi è leggermente diversa:
REGEXP_LIKE(title, 'Object.*Oriented.*(Databases|Concepts)')
Per dettagli sulle espressioni regolari supportate dai database, rimandiamo alle guide ufficiali dei rispettivi database.
Oltre all’operatore AND, le espressioni si possono combinare con l’operatore OR, soddisfatto quando almeno una delle espressioni è vera:
SELECT * FROM Book WHERE pub_year = 1983 OR pub_year = 1993 OR pub_year = 1980
Questo tipo di filtri si può scrivere in maniera più compatta e più comoda con l’operatore IN:
SELECT * FROM Book WHERE pub_year IN (1983, 1993, 1980)
L’operato IN permette di indicare un elenco di possibili valori. Non solo: è possibile specificare anche query secondarie:
SELECT * FROM Book WHERE pub_year IN (SELECT pub_year FROM Article)
Nel combinare espressioni AND e OR, dobbiamo ricordare che AND ha la precedenza nella valutazione. Quindi (come per la maggior parte dei
linguaggi) dobbiamo usare le parentesi per modificare l’ordine degli operatori, altrimenti, ad esempio, questa interrogazione potrebbe dare
risultati inaspettati:
SELECT * FROM Book
WHERE (pub_year = 1983 OR pub_year = 1993) AND publisher = 1
Infatti, eseguendola senza le parentesi, otterremmo anche i libri pubblicati nel 1983, indipendentemente dall’editore, oltre ai libri
pubblicati nel 1993 dall’editore avente ID uguale a 1.
Un altro operatore logico (stavolta unario) importante è il NOT, utilizzato per negare una condizione:
SELECT * FROM Book WHERE NOT(volume = 1)
Contrariamente a quanto si possa pensare, questa query non restituirà né i volumi diversi dal primo, ma neanche i libri aventi il
campo volume impostato a NULL. Questo perché in SQL il valore NULL è speciale nel senso che le espressioni che contengono valori nulli
vengono valutate come NULL, quindi richiedono i seguenti operatori speciali:
- IS NULL
- IS NOT NULL
Possiamo quindi riscrivere la query dell’esempio così:
SELECT * FROM Book WHERE volume IS NULL OR NOT(volume = 1)
Oppure possiamo utilizzare la funzione COALESCE che abbiamo visto nella lezione precedente:
SELECT * FROM Book WHERE NOT(COALESCE(volume, 0) = 1)
Naturalmente, si può testare anche la disuguaglianza, con gli appositi operatori:
disuguaglianza semplice: < >
minore < e minore o uguale: < =
maggiore > e maggiore o uguale: > =
Nel caso dei valori testuali, come CHAR e VARCHAR, l’ordinamento è quello alfabetico in base alla codifica scelta per il database.
L’operatore BETWEEN è comodo per cercare valori in un intervallo, ad esempio di date, ma può essere usato anche con gli altri tipi.
L’intervallo si intende chiuso (con gli estremi); quindi con la seguente interrogazione avremo anche i libri pubblicati nel 1980 e nel 1993:
SELECT title, pub_year
FROM Book
WHERE pub_year BETWEEN 1980 AND 1993
Ordinamento
Per ordinare i risultati di una query si usa la clausola ORDER BY, seguita dalle espressioni da valutare per effettuare l’ordinamento.
Ad esempio:
SELECT title, series, pub_year FROM Book
ORDER BY title
Questa query restituisce i libri pubblicati in ordine di titolo; se li volessimo ordinati prima per anno di pubblicazione e poi per titolo,
basterebbe modificare la clausola in questo modo:
SELECT title, series, pub_year FROM Book
ORDER BY pub_year DESC, title
Abbiamo semplicemente separato i due campi da una virgola. Con la parola chiave DESC abbiamo specificato che vogliamo un ordinamento
discendente, ossia mettendo prima i più recenti. La parola chiave ASC, che indica l’ordinamento ascendente, è opzionale perché corrisponde
ll’ordinamento di default.
Il comportamento di ORDER BY con i valori nulli dipende dal tipo di database utilizzato. Ad esempio MySQL e SQL Server li mettono sempre
in testa, mentre Oracle li mette in coda. Molti database, come Oracle e PostgreSQL hanno le parola chiave NULLS FIRST e NULLS LAST per
indicare come trattare i valori nulli; la seguente query, in Oracle, restituisce prima i libri ordinati per collana, mettendo in testa i libri
che non appartengono ad una collana:
SELECT title, series FROM Book ORDER BY series NULLS FIRST
Considerato che nella clausola di ordinamento possiamo usare tutte le funzioni che abbiamo già visto (non quelle di aggregazione che vedremo
nella prossima lezione), un modo portabile per ottenere lo stesso effetto è di utilizzare la funzione COALESCE. Infatti, anche con MySQL
possiamo ottenere lo stesso effetto scrivendo:
SELECT title, series FROM Book ORDER BY COALESCE(series, '0')
In questo modo i valori nulli verranno considerato come una stringa contenente il solo zero.
Ritorna all'indice
1.06 - GROUP BY e funzioni di aggregazione
Con la parola chiave DISTINCT, nella lezione 10, abbiamo visto la forma più semplice di aggregazione: quella che distingue le righe uguali
riportandole quindi una sola volta. L’esempio ivi riportato è, infatti, riscrivibile utilizzando la clausola GROUP BY; in altre parole,
le seguenti due istruzioni sono equivalenti:
SELECT DISTINCT surname FROM Person
SELECT surname FROM Person GROUP BY surname
Se le eseguiamo nel nostro database di esempio, otteniamo dunque gli stessi risultati. La clausola GROUP BY serve a specificare quali sono i
campi sui cui effettuare i raggruppamenti: il motore di query, per ogni riga esaminerà tali campi e la classificherà nel gruppo corrispondente.
Si possono specificare calcoli da effettuare per ogni gruppo. Ad esempio la query seguente restituisce, per ogni gruppo (surname), il numero di
occorrenze trovate, cioè quante persone hanno quel cognome:
SELECT surname, COUNT(*)
FROM Person
GROUP BY surname
In questo caso il motore raggruppa le righe in base al cognome, e per ogni gruppo effettua il conteggio di quanti elementi sono presenti nel
gruppo.
Da quanto detto, possiamo desumere una naturale limitazione di SQL: se viene specificata una clausola GROUP BY, allora nella clausola SELECT
deve esserci:
- o un campo specificato nella clausola GROUP BY;
- oppure una funzione di aggregazione.
Questo perché quando il motore aggrega le righe deve sapere come comportarsi per ogni campo da restituire.
Funzioni di aggregazione
Le funzioni di aggregazione sono particolari funzioni che operano su più righe. In generale, non tutti i database supportano le stesse
funzioni, per cui è bene riferirsi alle guide ufficiali. Ad esempio MySql fornisce la funzione GROUP_CONCAT, che non è invece supportata su
Oracle.
Le funzioni più comuni sono:
- COUNT per effettuare conteggi nel gruppo;
- SUM per le somme;
- MAX e MIN;
- AVG per calcolare la media.
La funzione COUNT
Questa funzione può essere invocata in tre modi. Il più semplice COUNT(*) l’abbiamo già visto: effettua il conteggio di tutte le righe presenti
nel gruppo, indipendentemente dai valori assunti. Può essere usato anche senza GROUP BY, per calcolare le righe totali presenti in una tabella:
SELECT COUNT(*) FROM Person
Se tra le parentesi specifichiamo un’espressione, verranno contate solo le righe che hanno quell’espressione non nulla. Ad esempio la seguente
query raggruppa i libri per anno di pubblicazione, contando quanti sono e quanti sono stati suddivisi per volume.
SELECT pub_year, COUNT(*), COUNT(volume)
FROM Book
GROUP BY pub_year
Se prima dell’espressione indichiamo la parola chiave DISTINCT verranno conteggiate solo le espressioni non nulle e distinte. In questo caso,
in cui abbiamo due tabelle in join, avremo come risultato il numero di redattori per ogni libro. Avremo vedremo uno 0 nel caso in cui il libro
non ha specificato nessun redattore nel database.
SELECT Book.title, COUNT(DISTINCT personID)
FROM Book
LEFT JOIN Editor ON book.ID = Editor.publicationID
GROUP BY Book.id, Book.title
La funzione SUM
Questa funzione somma i valori dei campi trovati nel gruppo. I valori nulli vengono ignorati, contrariamente a quanto farebbe una somma
semplice.
SELECT Person.surname, SUM(Book.pages)
FROM Book
JOIN Editor ON book.ID = Editor.publicationID
JOIN Person ON Editor.personID = Person.ID
GROUP BY Person.ID, Person.surname
Questa interrogazione restituisce il numero di pagine totale editate da ogni redattore.
Le funzioni MIN, MAX e AVG
Con queste funzioni si possono ottenere i valori massimo e minimo di una colonna, in base al criterio di ordinamento predefinito
(ad esempio per il testo verrà usato un ordinamento alfabetico). I valori nulli vengono ignorati.
Naturalmente più funzioni possono essere usate nella stessa query. La seguente restituisce, per ogni anno di pubblicazione, il primo titolo
in ordine alfabetico, il numero di libri pubblicati, la media del numero di pagine e il numero di pagine del libro più grande:
SELECT Book.pub_year, MIN(title), COUNT(*), AVG(pages), MAX(pages)
FROM Book
GROUP BY pub_year
Filtraggio sul raggruppamento
A differenza di WHERE, che agisce a livello di singola riga, la parola chiave HAVING permette di effettuare un filtraggio sul ragguppamento.
Questa clausola si inserisce subito dopo la GROUP BY.
Il criterio di filtraggio può contenere qualsiasi funzione di raggruppamento. Supponiamo di volere vedere in quali abbiamo pubblicato almeno
100 libri:
SELECT Book.pub_year, COUNT(*), AVG(pages)
FROM Book
GROUP BY pub_year HAVING COUNT(*) > 100
Analisi dei dati con ROLLUP e CUBE
Le direttive ROLLUP e CUBE, restituiscono nel risultato ulteriori righe per rappresentare aggregazioni trasversali, ad esempio per calcolare
subtotali. Vediamo un esempio concreto:
SELECT Book.pub_year, publisher, COUNT(*), SUM(pages)
FROM Book
GROUP BY pub_year, publisher WITH ROLLUP
Se eseguissimo questa query senza WITH ROLLUP otterremmo, per ogni anno e per ogni casa editrice, il numero di libri pubblicati e il totale
delle pagine. Ma se volessimo vedere anche l’aggregazione per anno indipendentemente dall’editore dovremmo effettuare un’altra query.
Utilizzando invece il rollup, possiamo vedere anche tali raggruppamenti trasversali, come vediamo dalle ultime tre righe restituite dalla query:
Come si vede, sono stati fatti anche i raggruppamenti per anno, indipendentemente dall’editore e i totali generali (ultima riga).
Non sono stati fatti invece i raggruppamenti per editore indipendentemente dall’anno. Per avere tutte le possibili combinazioni di raggruppamenti
si utilizza la direttiva WITH CUBE, per ottenere anche righe fatte così:
Ritorna all'indice
1.07 - UPDATE: modificare i dati
Dopo aver visto le modalità per inserire dati nel database e per interrogarlo, passiamo in rassegna le istruzioni per modificarne i dati.
Il comando UPDATE
Il comando UPDATE serve a modificare dati già inseriti nelle tabelle del database. La sua sintassi è la seguente:
UPDATE nome_tabella
SET nomeCampo1 = 1.08 - DELETE, eliminare i record dalle tabelle
L’istruzione DELETE permette di eliminare i record di una tabella che soddisfano un certo predicato:
DELETE FROM nome_tabella
[ WHERE predicato ]
Anche in questo caso non ci dilunghiamo sulla clausola WHERE, per la quale vale la considerazione fatta sopra: se viene omessa, l’eliminazione
avverrà su tutte le righe della tabella. Quindi occorre prestare particolare attenzione con la specificazione della clausola, anche per verificare
che si specifichino solo i record che si vogliono eliminare.
Se sussistono delle chiavi esterne sui record che vengono specificati, e se nei vincoli non sono state specificate azioni in caso di cancellazione
con la clausola ON DELETE (come visto nella lezione sulla creazione del database), allora l’annullamento fallirà. Ad esempio questa query fallirà
a causa del riferimento del record nella tabella Book:
DELETE FROM Publisher WHERE ID = 1
Se invece, ad esempio, fosse stata specificata la clausola ON DELETE CASCADE nella definizione della tabella Book, anche i libri che avessero
riferimento a questo editore sarebbero stati cancellati nella stessa transazione.
Ritorna all'indice
1.09 - Subquery
Nelle precedenti lezioni abbiamo visto come eseguire le principali operazione su un database relazionale tramite SQL, descrivendo il funzionamento
e la sintassi delle istruzioni INSERT, SELECT, UPDATE e DELETE.
Alcune situazioni più complesse, inoltre, possono essere gestite sfruttando le cosiddette subquery, cioè inserendo l’istruzione SELECT all’interno
di query che ne contengono già una, o che contengono altre istruzioni comele precedenti.
Nella lezione sull’istruzione UPDATE, abbiamo già visto un esempio di subquery, che riportiamo qui di seguito:
UPDATE Book SET note= 'editore: ' + (SELECT name FROM Publisher WHERE Publisher.ID = Book.publisher)
In questo caso, all’interno di una query di modifica dei dati, abbiamo avuto bisogno di selezionare una serie di record sfruttando un’intera query di
selezione.
Di seguito approfondiremo più nel dettaglio come e quando ha senso l’uso di una subquery, con alcuni esempi pratici di utilizzo.
Operazioni su subquery
Per capire in che modo utilizzare una subquery, è necessario innanzitutto soffermarsi sui possibili valori di ritorno di una query di selezione
(ovvero di una query che utilizza l’istruzione SELECT). Lo faremo sfruttando alcuni esempi, ognuno dei quali utilizza un diverso tipo di subquery,
e che ci porterà a discutere alcuni costrutti interessanti del linguaggio SQL.
La parola chiave IN
Iniziamo con la query seguente:
SELECT *
FROM Clienti
WHERE id IN (SELECT idCliente FROM Incassi WHERE importo > 1000)
La subquery interna ritorna un insieme di id di clienti, e più precisamente quelli per i quali il campo importo supera il valore 1000.
Se osserviamo ora l’intera query, noteremo la parola chiave IN: dal momento che è inserito all’interno di una clausola WHERE, questo costrutto ci
permette di selezionare tutte e sole le righe della tabella Clienti tali che il campo id sia presente tra i risultati della subquery.
Utilizzando NOT IN otterremmo, ovviamente, l’esatto contrario: selezioneremmo tutti quei clienti il cui id NON è presente tra i risultati della subquery.
Le parole chiave SOME, ANY ed ALL
Altre possibilità offerte da SQL sono rappresentate dalle parole chiave SOME, ANY ed ALL. Immaginiamo di volere selezionare una riga della tabella
Incassi solo se la colonna importo è maggiore di almeno uno dei valori ritornati da una subquery. Possiamo implementare questa logica con la parola
chiave SOME (nonchè con la parola chiave ANY, che è in tutto e per tutto equivalente):
SELECT *
FROM Incassi
WHERE importo > SOME (SELECT costo FROM Commesse WHERE tipologia = 'commessaSemplice')
Modificare il funzionamento di un operatore di confronto come > ci permette di verificare se un valore di un campo (in questo caso importo) è maggiore di
almeno uno degli elementi di un insieme di valori (ovvero il risultato della subquery).
Modifichiamo ora la precedente query utilizzando la parola chiave ALL al posto di SOME:
SELECT *
FROM Incassi
WHERE importo > ALL (SELECT costo FROM Commesse WHERE tipologia = 'commessaSemplice')
Con ALL ci assicureremo che il valore di importo sia maggiore di tutti i valori contenuti nell’insieme dei risultati ritornato dalla subquery.
EXISTS e NOT EXISTS
In stretta correlazione con gli operatori IN e NOT IN ci sono anche EXISTS e NOT EXISTS: essi permettono, infatti, di verificare se una subquery ritorna
una o più righe, ovvero se essa produce un risultato vuoto. La parola chiave EXISTS, in particolare, ritornerà un valore booleano pari a TRUE se e solo se
la subquery successiva seleziona almeno una riga. Vediamo un esempio:
SELECT DISTINCT idCitta
FROM Citta
WHERE EXISTS (SELECT * FROM Citta_SquadreCalcio WHERE Citta.idCitta = Citta_SquadreCalcio.idCitta)
La query, come si evince, ritorna gli id delle città per le quali esiste almeno una squadra di calcio all’interno della tabella Citta_SquadreCalcio.
Mentre l’uso di EXISTS è facilmente comprensibile, ciò che può risultare meno intuitivo è il modo in cui la subquery è correlata con la query principale: all’interno della clausola WHERE, infatti, abbiamo utilizzato il campo idCitta della tabella Citta. Ciò implica che la subquery dovrà essere eseguita con un parametro diverso per ogni riga estratta dalla query esterna. Il risvolto pratico è un significato degrado delle prestazioni: in questi casi è sempre meglio preferire soluzioni alternative, considerato anche che spesso le subquery correlate possono essere trasformate in operazioni di JOIN o simili. Lo stesso risultato della query precedente, ad esempio, si poteva ottenere come segue:
SELECT DISTINCT idCitta
FROM Citta, Citta_SquadreCalcio
WHERE Citta.idCitta = Citta_SquadreCalcio.idCitta
Subquery e funzioni di aggregazione
Un altro possibile uso delle subquery è esemplificabile come segue:
SELECT *
FROM Studenti
WHERE voto > (SELECT AVG(voto) FROM Studenti)
In questo caso, e a differenza di quanto visto finora, la subquery non ritorna una serie di risultati, bensì esattamente un valore (in questo caso la
media dei voti di un insieme di studenti). È quindi chiaro in che modo è possibile utilizzare una subquery che sfrutta una funzione di aggregazione
(in questo caso AVG) all’interno di una clausola WHERE.
Subquery all’interno di FROM
Per concludere questa lezione, osserviamo che tutte le subquery viste finora sono state utilizzate, con gli opportuni operatori, all’interno di una clausola WHERE, per lo più in qualità di operandi su operazioni di confronto.
È altresì possibile prevedere l’uso delle subquery all’interno di una clausola FROM, indipendentemente dal fatto che essa faccia parte di una query di selezione (SELECT) piuttosto che di eliminazione (DELETE), inserimento (INSERT) o aggiornamento (UPDATE). Consideriamo l’esempio che segue:
UPDATE Tabella1
SET colonna1 = T.colonna1
FROM (SELECT * FROM Tabella2) AS SubQuery INNER JOIN Tabella1 ON Tabella1.colonnaX = SubQuery.colonnaY
In questa operazione di UPDATE abbiamo effettuato un INNER JOIN tra la Tabella1 ed il risultato di una subquery: il contenuto ritornato dalla subquery è
utilizzato come una vera e propria tabella, all’interno della clausola FROM. L’unica accortezza che diventa necessaria in questi casi è la necessità di
utilizzare la parola chiave AS per dare un nome temporaneo al risultato della subquery (e poterne quindi richiamare i campi all’interno delle clausole
WHERE o per effettuare un JOIN).
Ritorna all'indice
1.10 - Le transazioni
Le singole istruzioni INSERT, UPDATE e DELETE sono atomiche nel senso che o hanno successo totale (su tutte le righe coinvolte) o falliscono totalmente
senza alcun effetto sul database.
È possibile combinare più istruzioni in una singola transazione atomica. Ad esempio, in Oracle o in MySQL:
START TRANSACTION;
DELETE FROM Book WHERE Publisher = 1;
DELETE FROM Publisher WHERE ID = 1
COMMIT;
Con questo script viene eliminato l’editore con ID pari a 1, ma prima vengono eliminati i libri pubblicati da esso. Racchiudendo il blocco tra
START TRANSACTION e COMMIT TRANSACTION, si fa in modo di rendere tutto il blocco atomico: o avvengono con successo entrambe
le istruzioni oppure in caso di errore tutto resta invariato. Con l’istruzione ROLLBACK, invece, si forza il fallimento della transazione,
lasciando il database allo stato consistente.
Livello di isolamento delle transazioni
A proposito delle transazioni, soprattutto se esse sono lunghe, è importante sapere quale livello di isolamento stiamo usando.
Il livello di isolamento stabilisce come transazioni contemporanee si comportano rispetto ai dati. Ogni RDBMS ha un livello di isolamento di default e
generalmente si può stabilire un livello differente per sessione o per transazione. Lo standard ANSI/ISO stabilisce quattro livelli di isolamento,
generalmente implementati da tutti i RDBMS:
1. Serializable. È il massimo livello di isolamento: ogni transazione, dall’inizio alla fine, non vede le modifiche fatte ai dati acceduti.
Il vantaggio è che la transazione può lavorare sul database assumendo di essere la sola transazione in corso sul database. Un modo di realizzare questo
livello è l’approccio ottimistico: ogni transazione lavora in isolamento, poi, se accadono problemi di concorrenza, la transazione che tenta di agire su
un dato modificato da altre transazioni fallirà con un errore e con conseguente rollback. Lo svantaggio è che ci possono essere molte scritture fallite
se ci sono tante transazioni che interessano gli stessi dati.
2. Repeatable Read. Con questo livello si fa in modo che i dati letti durante la transazione in corso non possono essere modificati da altre transazioni
per tutta la durata della transazione in corso. I vari RDBMS gestiscono questo livello utilizzando i lock in lettura sulle righe lette durante la transazione.
L’unico problema che può succedere con questo livello consiste nel verificarsi delle cosiddette letture fantasma: se rieseguo la stessa query durante la
transazione, potrei trovare righe in più di quelle che ho letto in precedenza, ma mai in meno o modificate. Lo svantaggio di questo livello è una penalizzazione
delle prestazioni se ci sono molte transazioni concorrenti che agiscono sulle stesse tabelle. Questo è il livello di default per MySQL.
3. Read Committed. Utilizzando questo livello, invece, si evitano i lock in lettura sulle tabelle che sono molto onerosi dal punto di vista prestazionale.
Lo svantaggio è che, oltre al fenomeno delle letture fantasma, si verifica anche quello delle letture non ripetibili: in pratica, rieseguendo due volte la
stessa SELECT nel corso di una transazione, potrei ottenere dati diversi se altre transazioni sono terminate nel tempo intercorso tra le due letture.
Questo è il livello di default per Oracle e per Microsoft Sql Server.
4. Read Uncommitted. Questo è il livello più basso, in pratica nessun isolamento. Con questo livello si possono avere letture sporche: nella transazione
corrente si possono leggere dati che qualche altra transazione sta scrivendo in quel momento senza aver ancora fatto COMMIT, quindi può capitare di leggere
chiavi violate, dati inconsistenti, eccetera.
Ovviamente non esiste il livello migliore di isolamento, generalmente il livello di default è valido nella maggior parte dei contesti.
Gli RDBMS supportano gli altri livelli per gestire casi particolari di utilizzo in concorrenza.
Ritorna all'indice
1.11 - Trigger
La maggior parte dei database relazionali permette di estendere le funzionalità che abbiamo visto fin qui per aggiungere comportamenti o addirittura nuovi
tipi di dati. In questa lezione approfondiremo proprio questi aspetti.
Trigger
Un trigger è un’azione che deve essere intrapresa quando succede un certo tipo di evento nel database.
La sintassi di creazione di un trigger è definita nello standard ISO, sebbene essa non venga rispettata da tutti i database.
Ad esempio Microsoft SQL Server ha una sintassi abbastanza personalizzata di CREATE TRIGGER, al contrario di MySQL che invece rispetta abbastanza
lo standard. Consideriamo la seguente istruzione:
CREATE TRIGGER ins_month_if_year
BEFORE INSERT ON Book
FOR EACH ROW SET NEW.pub_month = COALESCE(NEW.pub_month, 'JAN');
Ciò comporterà, ad ogni inserimento nella tabella Book, l’impostazione automatica del valore JAN tutte le volte in cui non viene specificato un mese di
pubblicazione.
Diamo un’occhiata alla sintassi: dopo la clausola CREATE TRIGGER viene specificato il nome del trigger da creare. Invece, con BEFORE INSERT ON
indichiamo che vogliamo eseguire un’azione prima dell’inserimento nella tabella Book. Quando viene specificata la parola chiave BEFORE non si dovrebbero
fare modifiche al database, piuttosto si dovrebbe intervenire sui dati che stanno per essere inseriti, altrimenti si rischia di perdere un po’ la visione
di quello che sta succedendo.
Si noti che non tutti i database supportano l’uso della parola chiave BEFORE: su Microsoft Sql Server, ad esempio, si può implementare un comportamento
analogo con il trigger di alternativa all’esecuzione standard (INSTEAD OF).
Con la clausola FOR EACH ROW specifichiamo che l’istruzione che segue sarà eseguita per ogni riga da inserire. Infatti, è possibile inserire più righe
con una sola istruzione INSERT, come abbiamo visto nella lezione 9.
Ovviamente avremmo potuto ottenere lo stesso effetto impostando un valore di default, ma con TRIGGER la forzatura avverrà solamente in fase di inserimento
e, per come abbiamo realizzato il trigger, anche per le INSERT che tentassero di impostare esplicitamente il valore NULL nel campo:
INSERT INTO Book(ID, title, publisher, pub_year, pub_month) values(2, 'year', 1, 2015, NULL);
Ovviamente si può introdurre un trigger analogo anche in aggiornamento, sostituendo BEFORE INSERT ON con BEFORE UPDATE ON.
I trigger di tipo BEFORE sono comodi quando ci si vuole assicurare una certa coerenza tra diversi dati di una stessa riga.
Esempi sono i casi in cui una riga contiene un campo che deve essere la somma di altri due, oppure che non può assumere certi valori in certe condizioni,
che non possono essere espresse con un semplice vincolo di colonna (CHECK).
È possibile fare in modo che il trigger venga eseguito dopo una certa operazione, ad esempio per inizializzare tabelle di relazione, per allineare dati
o ricalcolare dati aggregati. Possiamo anche usare i trigger per loggare le modifiche: nell’esempio che segue, salveremo con un log le modifiche fatte
alla tabella Person, salvandole nella nuova tabella LogPerson.
CREATE TABLE LogPerson(IdPerson int NOT NULL,surname varchar(160) NOT NULL,ts TIMESTAMP);
CREATE TRIGGER log_updates AFTER UPDATE ON Person
FOR EACH ROW INSERT INTO LogPerson(ts, IdPerson, surname)
VALUES(CURRENT_TIMESTAMP, NEW.id, NEW.surname);
Oracle: id auto-incrementali
I trigger sono molto utili in Oracle per ottenere una funzionalità presente in altri database come MySQL (AUTO_INCREMENT) e SQL Server (IDENTITY):
i valori auto-incrementali.
Mentre su MySQL è possibile creare un campo numerico che si incrementa ad ogni inserimento tramite la parola chiave AUTO_INCREMENT, su Oracle la
medesima funzionalità si può ottenere con una sequenza, utilizzandola come descritto nella Guida Oracle ma dentro un trigger:
CREATE SEQUENCE PersonSeq;
CREATE OR REPLACE TRIGGER Person_inc
BEFORE INSERT ON Person
FOR EACH ROW
BEGIN
SELECT PersonSeq.NEXTVAL INTO :NEW.id FROM dual;
END;
Questo trigger, all’inserimento di una persona, calcola il valore successivo della sequenza (NEXTVAL) e lo inserisce nel campo id della riga da inserire.
Ritorna all'indice
1.12 - Funzioni e procedure
Oltre ai trigger, un altro modo per estendere le funzionalità del linguaggio SQL “tradizionale” è la definizione di funzioni e procedure. In questa lezione vedremo come fare.
Funzioni
Nella lezione sul comando SELECT abbiamo visto come si possono usare le funzioni nelle interrogazioni per operare con valori numerici, testuali o booleani.
Quasi tutti i database SQL permettono di estendere l’insieme delle funzioni utilizzabili creandone di nuove.
Ad esempio, per il nostro database, possiamo creare una funzione per verificare se il codice ISBN è corretto.
In Oracle possiamo usare la sintassi seguente, che verifica se la cifra di controllo del codice è valida, restituendo Y in caso affermativo,
o altrimenti il codice di controllo calcolato:
CREATE OR REPLACE FUNCTION CHECK_ISBN ( ISBN IN VARCHAR2 )
RETURN VARCHAR2 IS
ACC INTEGER := 0;
CURR_CHAR VARCHAR2(2);
BEGIN
FOR I IN 1..9 LOOP
CURR_CHAR := SUBSTR(ISBN,I,1);
ACC := ACC + (i) * TO_NUMBER(CURR_CHAR);
END LOOP;
ACC := MOD(ACC, 11);
IF ACC = 10 THEN CURR_CHAR := 'X'; ELSE CURR_CHAR := TO_CHAR(ACC); END IF;
IF CURR_CHAR = SUBSTR(ISBN,LENGTH(ISBN),1) THEN RETURN 'Y'; ELSE RETURN CURR_CHAR; END IF;
END CHECK_ISBN;
Il lavoro viene svolto tra le istruzioni BEGIN ed END: qui viene eseguito un ciclo sui caratteri presenti nel codice e viene calcolata la cifra di controllo.
Nella penultima riga si verifica se l’ultimo carattere del codice ISBN corrisponde al valore calcolato. Questo blocco di codice è stato scritto in PL/SQL,
l’estensione di SQL realizzata per Oracle proprio per la programmabilità: per altri database la sintassi sarebbe stata diversa.
Una volta creata, si può invocare la funzione in una normale istruzione interrogazione:
SELECT title, CHECK_ISBN(isbncode) FROM Books
Procedure
Una procedura, a differenza di una funzione, non può essere usata in una semplice istruzione SELECT, ma può essere invocata con una sintassi ad hoc, ed è usata,
generalmente, per incapsulare del codice che ha qualche effetto collaterale, ad esempio inserire, modificare o eliminare righe dal database.
Per mostrare un esempio, la seguente procedura, scritta per SQL Server (quindi in T-SQL), inserisce simultaneamente oltre al libro, anche il suo autore ed il suo
editore, se non esistono nel databas; altrimenti li collega:
CREATE PROCEDURE insert_book
@title varchar(200),
@author_surname varchar(32),
@author_given_names varchar(32),
@publisher varchar(64)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @idPerson AS INT;
DECLARE @idPublisher AS INT;
DECLARE @idBook AS INT;
SELECT @idPerson = ID From Person
WHERE surname = @author_surname AND given_names = @author_given_names;
IF @@ROWCOUNT = 0 BEGIN
SELECT @idPerson = MAX(ID) + 1 FROM Person;
INSERT INTO Person(ID, surname, given_names)
VALUES(@idPerson, @author_surname, @author_given_names);
END
SELECT @idPublisher = ID From Publisher WHERE name = @publisher;
IF @@ROWCOUNT > 0 BEGIN
SELECT @idPublisher = COALESCE(MAX(ID)+1, 1) FROM Publisher;
INSERT INTO Publisher(ID, name) VALUES(@idPublisher, @publisher);
END
SELECT @idBook = COALESCE(MAX(ID)+1, 1) FROM Publication;
INSERT INTO Publication(ID, type) VALUES(@idBook, 'Book');
INSERT INTO Book(ID, title, publisher, pub_year)
VALUES (@idBook, @title, @idPublisher, YEAR(getdate()));
INSERT INTO Author(personID, publicationID)
VALUES (@idPerson, @idBook);
END
Tipi definiti dall’utente
Oracle (ma anche PostgreSQL) permette di creare tipi definiti dall’utente che incapsulano dati e procedure proprio come nella programmazione ad oggetti.
Si tratta di una funzionalità molto potente perché, oltre a dare maggior chiarezza al database, evita il moltiplicarsi di campi simili in diverse tabelle,
e stesse logiche in varie procedure.
Il seguente esempio in PL/SQL mostra la definizione di un oggetto contenente coordinate di localizzazione e una funzione per calcolare la distanza
(in questo caso per semplicità il calcolo viene effettuato come se si trattasse di coordinate piane):
CREATE OR REPLACE TYPE geoloc AS OBJECT (
latit NUMBER,
longt NUMBER,
MEMBER FUNCTION distanza(altra geoloc) RETURN NUMBER
);
/
CREATE TYPE BODY geoloc IS
MEMBER FUNCTION distanza(altra geoloc) RETURN NUMBER IS
BEGIN
RETURN SQRT(POWER(altra.latit-latit,2)+POWER(altra.longt-longt,2));
END;
END;
Si può notare che con la parola chiave MEMBER si dichiara solo la firma della funzione. Il corpo delle funzioni, invece, va definito a parte all’interno
di un blocco CREATE TYPE BODY. Una volta definito, il tipo si può usare nella creazione delle tabelle, e le sue funzioni possono essere usate nelle interrogazioni,
come in questi esempi:
CREATE TABLE Indirizzi(ID INT, indirizzo VARCHAR2(500), posizione geoloc);
INSERT INTO Indirizzi(ID, indirizzo, posizione)
VALUES(1, 'Lungomare Vanvitelli', geoloc(43.6251419,13.5049253);
SELECT ind.indirizzo FROM Indirizzi ind
WHERE ind.posizione.distanza(geoloc(43.6224239,13.5071353)) < 100;
Ritorna all'indice
1.13 - Modificare la struttura del database
Nel corso della precedente lezione si e’ visto come modificare i dati gia’ presenti nel database.
A volte pero’ non e’ sufficiente modificare i dati, ma occorre aggiornare la struttura stessa del database per far si’ che possano essere rappresentate nuove informazioni.
Dal momento che la struttura del detabase e’ data sostanzialmente dall’insieme delle tabelle che lo compongono, il suo aggiornamento corrisponde all’eliminazione di tabelle
o alla modifica delle loro caratteristiche.
Per eliminare una tabella da un database il comando SQL da utilizzare e’ DROP table:
DROP table nome_tabella { REStrICT | CASCADE }
nome_tabella è il nome della tabella che deve essere eliminata.
Se si specifica la clausola CASCADE vengono automaticamente eliminati i vincoli di integrita’ e le viste (view) in cui la tabella e’ coinvolta. Viceversa, se si specifica
la clausola REStrICT ed esistono dei vincoli di integrita’ o delle viste che si riferiscono alla tabella, l’operazione fallisce.
Ad esempio, si siano definite le seguenti due tabelle:
CREATE table Prova1 (
Id INTEGER PRIMARY KEY,
Nome VARCHAR(50)) CREATE table Prova2 (
Id INTEGER PRIMARY KEY,
Nome VARCHAR(50),
toProva1 INTEGER REFERENCES Prova1(Id))
Volendo eliminare la tabella Prova1, l’istruzione:
DROP table Prova1 REStrICT
fallirà dal momento che esiste un vincolo di integrità che lega una chiave esterna della tabella Prova2 con la tabella Prova1.
Invece l'istruzione:
DROP table Prova1 CASCADE
verrà eseguita con successo e produrrà anche l’eliminazione del vincolo di integrità referenziale presente sulla tabella Prova2.
Nel caso in cui si voglia modificare una tabella esistente nel database, l’istruzione da utilizzare è ALTER table.
Dal momento che la sintassi di questa istruzione è piuttosto complessa, essa verrà spiegata scomponendola in base alle funzionalità che si
vogliono ottenere:
Aggiunta di una nuova colonna nella tabella
ALTER table nome_tabella ADD [ COLUMN ] definizione_colonna
nome_tabella è il nome della tabella che si vuole modificare.
La definizione della colonna segue la stessa sintassi vista nella lezione "Creare il database" nella spiegazione dell’istruzione CREATE table.
Dovranno quindi essere specificati il nome della colonna, il suo tipo ed eventualmente il suo valore di default e i vincoli imposti sulla colonna.
La parola chiave COLUMN può essere omessa (qui e in tutti i casi successivi).
Eliminazione di una colonna nella tabella
ALTER table nome_tabella
DROP [ COLUMN ] nome_colonna { REStrICT | CASCADE }
nome_colonna è il nome della colonna che si vuole eliminare.
Le clausole RESStrIC e CASCADE si comportano esattamente come nell’istruzione DROP table vista precedentemente.
L’istruzione fallirà, oltre che nei casi già visti per REStrICT, se si tenta di eliminare una colonna che è l’unica colonna di una tabella.
Modifica del valore di default di una colonna
ALTER table nome_tabella
ALTER [ COLUMN ] nome_colonna { SET clausola_default | DROP DEFAULT }
La sintassi ed il significato della clausola che definisce il nuovo valore di default sono identici a quelli della clausola_default che si usa nel
comando CREATE table.
Eliminazione di un vincolo della tabella
ALTER table nome_tabella
DROP CONStrAINT nome_vincolo { REStrICT | CASCADE }
Elimina il vincolo identificato dal nome specificato.
L’operazione fallisce se è stata specificata la clausola REStrICT ed esistono altri vincoli che dipendono da quello che si intende eliminare.
Specificando la clausola CASCADE l’operazione verrà sempre completata con successo, cancellando inoltre i vincoli dipendenti da quello eliminato.
Spesso si vuole eliminare un vincolo a cui non è stato assegnato un nome esplicitamente, facendo precedere la definizione del vincolo dalla clausola
opzionale [CONStrAINT nome_vincolo].
In tal caso, dal momento che il DBMS avrà comunque assegnato un nome al vincolo per poterlo identificare, occorrerà interrogare le tabelle di sistema
del DBMS ed ottenere da esse il suo nome. La particolare interrogazione richiesta dipende dallo specifico DBMS utilizzato.
Aggiunta di un vincolo alla tabella
ALTER table nome_colonna
ADD vincolo_di_tabella
La sintassi da utilizzare per la definizione del vincolo è la stessa utilizzata nel comando CREATE table per i vincoli di tabella.
Ritorna all'indice
1.14 - Utilizzo multiutente di un database
Fino ad ora abbiamo esaminato le caratteristiche del linguaggio SQL che riguardano la definizione e la manipolazione dei dati presenti in un database,
senza preoccuparci del fatto che normalmente l’accesso a tali dati avviene in maniera concorrente da parte di piu’ utenti contemporaneamente.
I meccanismi a sostegno di tale metodo di accesso riguardano principalmente la sicurezza dei dati, la gestione delle transazioni e la possibilita’ di
definire delle viste sulle tabelle del database.
1. Sicurezza
L’esecuzione di un operazione sui dati del database da parte di un utente e’ subordinata al possesso da parte dell’utente dei necessari privilegi per la
particolare operazione eseguita sullo specifico insieme di dati.
In generale i privilegi vengono attribuiti nella seguente maniera:
- Un utente che crea una tabella o qualunque altro oggetto del database ne e’ il proprietario e gli vengono automaticamente garantiti tutti i privilegi
applicabili a tale oggetto, con la possibilita’ di impostare anche ad altri utenti tali privilegi (privilegio di concessione).
- Un utente che abbia un privilegio ed abbia in oltre su di esso il privilegio di concessione puo’ assegnare tale privilegio ad un altro utente e passare
ad esso anche il privilegio di concessione.
- I privilegi sono concessi da chi ne abbia il permesso (cioe’ dal proprietario dell’oggetto e da chi abbia il privilegio di concessione) mediante il
comando GRANT e revocati mediante il comando REVOKE.
La sintassi del comando GRANT e’ la seguente:
GRANT elenco_privilegi ON oggetto TO elenco_utenti [ WITH GRANT OPTION ]
Esso assegna all’utente i privilegi presenti nell’elenco_privilegi sull’oggetto specificato.
I privilegi assegnabili sono i seguenti (con le relative sintassi):
USAGE
Privilegio per usare uno specifico dominio o altro oggetto del database.
SELECT
Privilegio per accedere a tutte le colonne di una tabella o di una vista.
INSERT [ (nome_colonna) ]
Se viene specificata l’opzione nome_colonna, e’ il privilegio per inserire valori nella colonna indicata di una tabella o di una vista.
Senza il nome_colonna e’ il privilegio per inserire valori in tutte le colonne, comprese quelle che saranno aggiunte in seguito.
UPDATE [ (nome_colonna) ]
Se viene specificata l’opzione nome_colonna, e’ il privilegio per aggiornare il valore nella colonna indicata di una tabella o di una vista.
In caso contrario permette di aggiornare il valore di tutte le colonne, comprese quelle che saranno aggiunte in seguito.
DELETE
Privilegio per eliminare righe da una tabella o da una vista.
REFERENCES [ (nome_colonna) ]
Se viene specificata l’opzione nome_colonna, e’ il privilegio di riferirsi alla colonna indicata di una tabella o di una vista nella definizione di
un vincolo di integrita’. Senza l’opzione concede tale privilegio per tutte le colonne, comprese quelle aggiunte in seguito.
L’oggetto a cui il privilegio si riferisce e’ generalmente una tabella o una vista. La sintassi per la sua specificazione e’ in tal caso:
[table] nome_tabella
Nel caso di altri oggetti segue la sintassi:
tipo_oggetto nome_oggetto
dove tipo_oggetto puo’ essere DOMAIN, CHARACTER SET, COLLATION o trANSLATION.
Nel caso di oggetti diversi da tabelle o viste, l’unico privilegio applicabile e’ quello di USAGE.
L’elenco_utenti e’ un elenco di identificativi di utenti o gruppi di utenti.
Puo’ anche essere utilizzata la parola chiave PUBLIC, che indica tutti gli utenti e i gruppi conosciuti nel sistema.
Se e’ presente l’opzione [ WITH GRANT OPTION ], viene assegnato inoltre il privilegio di concessione, che permette agli utenti di trasferire ulteriormente
i privilegi loro assegnati.
Ad esempio:
GRANT SELECT, INSERT, UPDATE(nome) ON persona TO benfante WITH GRANT OPTION
assegna all’utente benfante i privilegi di SELECT e INSERT su tutte le colonne della tabella persona e quello di UPDATE sulla sola colonna
nome di tale tabella.
Gli viene inoltre garantito il privilegio di assegnare tali permessi ad altri utenti.
Per togliere i privilegi agli utenti si usa invece REVOKE:
REVOKE [ GRANT OPTION FOR ] elenco_privilegi ON oggetto FROM elenco_utenti { REStrIC | CASCADE }
elenco_privilegi, oggetto ed elenco_utenti hanno le stesso significato delle corrispondenti opzioni di GRANT.
L’opzione GRANT OPTION FOR revoca il privilegio di concessione.
Se viene specificata la clausola REStrICT, il comando REVOKE puo’ fallire nel caso in cui l’utente a cui vengono revocati i privilegi
li abbia ulteriormente concessi ad altri utenti.
Se e’ presente invece la clausola CASCADE, l’istruzione verra’ sempre completata con successo e verranno revocati i privilegi anche di quegli utenti e
di tutti gli utenti a cui essi li hanno concessi (…e cosi’ via, finche’ non ci saranno piu’ permessi “abbandonati”, cioe’ concessi senza che chi li ha concessi
ne sia ancora in possesso). Verranno inoltre distrutti gli oggetti del database costruiti grazie a tali permessi.
2. Gestione delle transazioni
Le transazioni SQL sono insiemi di istruzioni che devono essere trattati come delle unita’ atomiche, cioe’ non scomponibili nelle singole istruzioni da
cui sono formate.
Grazie a tale atomicita’ le transazioni permettono di eseguire operazioni complesse sul database mantenendone l’integrita’. Infatti una transazione viene
eseguita con successo se e solo se tutte le operazioni che la compongono terminano con successo.
In caso contrario, cioe’ se una delle operazioni fallisce o se la transazione viene esplicitamente annullata, tutte le operazioni precedenti vengono
annullate anch’esse.
Le operazioni di una transazione non hanno alcun effetto sul database fino a quando la transazione non viene completata con successo.
Dal momento che ad un database possono accedere piu’ utenti contemporanamente, in ogni istante potremmo avere piu’ transazioni che manipolano il
database in maniera concorrente.
Lo standard SQL prevede che normalmente le transazioni debbano essere eseguite nel “livello di isolamento serializzabile” (isolation level SERIALIZABLE),
cioe’ in una modalita’ di esecuzione che garantisca la “serializzabilita'” delle transazioni. Il fatto che le transazioni siano serializzabili significa
che il loro effetto complessivo sul database e’ quello che si otterrebbe se esse venissero eseguite in maniera non concorrente l’una di seguito all’altra.
Nel linguaggio SQL standard non esiste un’istruzione che faccia iniziare esplicitamente una transazione. Le istruzioni vengono divise in due classi:
quelle che possono iniziare una transazione e quelle che non la fanno iniziare.
Nel momento in cui si cerca di eseguire un’istruzione della prima classe, se non e’ gia’ in corso una transazione, ne viene cominciata una.
La transazione continua fino a quando una delle istruzioni fallisce, causando l’annullamento dell’intera transazione, o se vengono eseguite le istruzioni
COMMIT WORK o ROLLBACK WORK.
L’istruzione COMMIT WORK termina la transazione confermandola, rendendo quindi definitivi gli effetti delle sue istruzioni sul database.
L’istruzione ROLLBACK WORK invece la termina annullandola.
Spesso i DBMS che si trovano in commercio implementano la gestione delle transazioni in maniera differente da quanto previsto dallo standard (almeno nelle
loro impostazioni di default).
In tal caso, di solito e’ previsto un comando che inizia esplicitamente una transazione (BEGIN trANSACTION, START WORK, o altro). Se una transazione non e’
stata iniziata esplicitamente, le singole istruzioni ne compongono una ciascuna.
Per capire meglio quali potrebbero essere le conseguenze della manipolazione concorrente dei dati di un database senza l’utilizzo delle transazioni,
vediamone un’esempio. Supponiamo di avere un database con il quale gestiamo gli ordini dei prodotti che vendiamo. In particolare, quando un cliente ci
sottopone una richiesta per un prodotto, ne verifichiamo la disponibilita’ e nel caso in cui possiamo soddisfare l’ordine, sottraiamo alla quantita’ in
giacenza la quantita’ che ci e’ stata richiesta. traducendo tutto cio’ in SQL, otteniamo la quantita’ in giacenza con l’istruzione (istruzione A):
SELECT giacenza FROM prodotti
WHERE prodottoID=1453
L’aggiornamento della giacenza, una volta verificata la disponibilita’, e’ ottenuta dalla seguente istruzione (istruzione B):
UPDATE prodotti
SET giacenza=giacenza-1
WHERE prodottoID=1453
Se due utenti cercano di eseguire questa operazione, senza che le due istruzioni che la compongono siano state raggruppate in una transazione, potrebbe
accadere che le istruzioni vengano eseguite nell’ordine e con i risultati seguenti :
- Istruzione A eseguita dall’utente 1: viene restituita una giacenza del prodotto pari a 1, quindi l’ordine verra’ approvato.
- Istruzione A eseguita dall’utente 2: come prima la giacenza e’ 1 e anche in questo caso l’ordine verra’ approvato.
- Istruzione B eseguita dall’utente 1: a questo punto nel database la giacenza per il prodotto vale 0.
- Istruzione B eseguita dall’utente 2: ora la giacenza vale -1, che e’ ovviamente un valore errato.
Come si vede il risultato finale e’ che uno dei due clienti non potra’ ricevere (almeno non subito) la merce, dato che non ne avevamo in giacenza una quantita’
sufficiente per entrambi i clienti.
Se le due istruzioni fossero state inserite in una transazione, il problema non sarebbe sorto, dato che la transazione del secondo utente non avrebbe
potuto leggere il valore della giacenza fino a quando non fosse stata completata la transazione del primo utente.
A quel punto, la giacenza avrebbe avuto valore 0 e l’ordine non sarebbe stato erratamente approvato.
3. Viste
Fino ad ora le uniche tabelle con cui abbiamo avuto a che fare sono state quelle definite con il comando CREATE table.
Il linguaggio SQL mette anche a disposizione la possibilita’ di definire delle tabelle “virtuali”, le viste, calcolate a partire da altre tabelle.
Esse sono virtuali nel senso che non occupano spazio su disco, ma sono il risultato di interrogazioni su altre tabelle e quindi sempre allineate
con i valori contenuti in tali tabelle.
L’istruzione SQL per definire una vista e’ la seguente:
CREATE VIEW nome_vista [ ( elenco_nomi_colonne ) ]
AS espressione_tabella
Essa crea una vista chiamata nome_vista definita dall’espressione_tabella.
Tipicamente espressione_tabella e’ un’instruzione select che produrra’ la tabella che interessa. l’elenco_nomi_colonne puo’ essere usata per assegnare
dei nomi alle colonne della vista.
Cio’ e’ utile nel caso in cui le colonne derivanti dall’espressione tabella siano il risultato di un calcolo (ad esempio COUNT(nome_colonna)) e non
abbiano quindi un nome esplicito.
Una volta creata, una vista puo’ essere utilizzata come una normale tabella. Le uniche limitazioni riguardano le operazioni che modificano i dati in
essa contenuti. Infatti, non tutte le viste sono aggiornabili.
Le regole che discriminano fra una vista aggiornabile e una non aggiornabile sono piuttosto complesse e non e’ questa la sede per descriverle
(si vedano i libri in bibliografia, in particolare quello di C.J. Date).
Qui ci limiteremo a cercare di capire, mediante un esempio, perche’ questo accade.
Proviamo ad utilizzare la seguente vista sul nostro database bibliografico:
CREATE VIEW book_publisher89
AS SELECT B.title, P.name
FROM Book B, Publisher P
WHERE B.publisher = P.ID
AND B.pub_year=1989
Essa ci permette di eseguire la query che la definisce semplicemente utilizzando l’istruzione:
SELECT * FROM book_publisher89
Possiamo anche impostare ulteriori condizioni (o fare in modo che il risultato sia ordinato secondo una particolare colonna della vista, ecc…):
SELECT title FROM book_publisher89
WHERE name = “ACM Press”
Quest’ultima interrogazione ci fornisce l’elenco dei titoli dei libri pubblicati dall’ACM Press nel 1989.
Come si vede per quanto riguarda operazioni di interrogazione una vista si comprta come una normale tabella.
Le differenze sorgono quando si cerca di applicare ad una vista delle operazioni di aggiornamento.
Ad esempio, se cerchiamo di eseguire la seguente istruzione:
INSERT INTO book_publisher89
VALUES( “Nuovo libro”, “publisher”)
Il DBMS non riuscira’ ad eseguirla, restituendo un errore tipo “No INSERT permission”.
Il motivo e’ che non e’ in grado di creare le righe corrispondendi al nostro nuovo record nelle due tabelle “reali” da cui la vista e’ originata
(i problemi sono vari: deve creare solo una righa nella tabella Book e collegarla ad una particolare riga nella tabella Publisher, o creare una riga
in entrambe le tabelle; come decidere quali valori assegnare alle chiavi primarie degli eventuali nuovi record; quali valori assegnare agli altri campi
delle due tabelle; ecc…)
Grazie alle viste (e all’assegnazione oculata dei permessi agli utenti) e’ possibile fare in modo che utenti diversi abbiano una percezione della struttura del database anche molto diversa da quella che ha realmente e impedire che particolari categorie di utenti possano accedere ad informazioni che non competono loro. Ad esempio, supponiamo di avere una tabella in cui sono memorizzati i dati anagrafici dei dipendenti di una ditta e l’ammontare del loro stipendio mensile. Ovviamente si vorrebbe impedire la consultazione dei dati relativi agli stipendi a tutti gli utenti, eccetto a quelli che si devono occupare dell’erogazione/amministrazione degli stessi. Un sistema per fare cio’ e’ quello di definire una vista contenente solo le colonne dei dati anagrafici. In questo modo tutti gli utenti autorizzati ad accedere ai dati anagrafici, ma non a quelli degli stipendi, lo potranno fare solo attraverso tale vista. Ulteriori partizionamenti potrebbero essere fatti in senso orizzontale, creando ad esempio un vista che contiene solo le informazioni sui dirigenti ed una che contiene i dati degli altri dipendenti. Inoltre, le viste spesso contribuiscono a facilitare quella indipendenza fra applicazioni e struttura dei dati, che rende i database degli strumenti tanto utili. Infatti se ad un certo punto fosse necessario cambiare la struttura del database (scomponendo, ad esempio, una tabella in due tabelle per motivi di efficienza), non sarebbe necessario modificare tutte le applicazioni adattandole alla nuova struttura, ma sarebbe sufficiente creare delle opportune view, in modo che dal punto di vista delle applicazioni niente sia stato cambiato.