Basi di Dati e Sistemi
Informativi
Il Linguaggio SQL
Home page del corso:
http://www.cs.unibo.it/~difelice/dbsi/
Il Linguaggio SQL
SQL (Structured Query Language) e’ il linguaggio di
riferimento per le basi di dati relazionali.
Diverse versioni del linguaggio:







SQL-86  Costrutti base
SQL-89  Integrita’ referenziale
SQL-92 (SQL2)  Modello relazionale, struttura a livelli
SQL:1999 (SQL3)  Modello ad oggetti
SQL:2003 (SQL3)  Nuove parti: SQL/JRT, SQL/XML
SQL:2006 (SQL3)  Estensione di SQL/XML
SQL:2008 (SQL3)  Lievi aggiunte
Il Linguaggio SQL
Oltre ad i costrutti base di SQL2 visti fin qui,
esistono molti costrutti avanzati (i) definiti in
SQL3 e/o (ii) dipendenti dallo specifico DBMS.
 Procedure (Stored Procedures)
 Trigger
 Permessi
 Transazioni
Il Linguaggio SQL
Stored Procedures  Frammenti di codice SQL,
con la possibilita’ di specificare un nome, dei
parametri in ingresso e dei valori di ritorno.
Procedure ModificaStipendio (:MatricolaNew:
varchar(20), : StipendioNew smallint)
update Impiegati
set Stipendio= : StipendioNew
where Matricola = :MatricolaN
Ogni DBMS offre estensioni procedurali differenti …
Il Linguaggio SQL
SQL
DB
TABELLE
APPLICAZIONE
ESTERNA
MODELLO senza
STORED PROCEDURE
DATI
MODELLO con
STORED PROCEDURE
 Efficienza
 Maggiore espressivita’
 …
NOME PROCEDURA
+ PARAMETRI
APPLICAZIONE
ESTERNA
DB
TABELLE
PROCEDURE
DATI
Il Linguaggio SQL
Esempio: definizione di funzioni in MySQL:
CREATE FUNCTION function_name
RETURNS type_return
… List of SQL routine statements
CREATE FUNCTION echo(s CHAR(20))
RETURNS CHAR(50)
RETURN(s)
mysql>> SELECT echo(“Hello”);
Il Linguaggio SQL
Esempio: definizione di funzioni in PostgreSQL:
CREATE FUNCTION add_three_values(v1
anyelement, v2 anyelement, v3 anyelement)
RETURNS anyelement AS $$
DECLARE
result ALIAS for $0
BEGIN
result:=v1 + v2 +v3;
RETURN result
END
Il Linguaggio SQL
Generalmente, le estensioni procedurali consentono di:
 Creare funzioni e procedure trigger-based.
 Aggiungere strutture di controllo al linguaggio SQL (es.
cicli, strutture condizionali if then else, etc).
 Dichiarare variabili e tipi di dato user-defined.
 Definire funzioni avanzate ed ottimizzate, che sono
ritenute “sicure” dal DBMS.
Il Linguaggio SQL
Ogni DBMS offre una sua estensione procedurale:
 PL/SQL  Linguaggio di Oracle Server
 SQL PL  Linguaggio di IBM DB2
 PL/pgSQL  Linguaggio di Postgres
…
Il Linguaggio SQL
Costrutti procedurali in PostgreSQL:
 Costrutti condizionali
if <Condizione> then … else … endif
IF user_id <> 0 THEN
UPDATE USERS
SET USERS.email=v_email
WHERE (USERS.user_id = user_id)
Il Linguaggio SQL
Costrutti procedurali in PostgreSQL:
 Costrutti iterativi (while)
while (Expression) LOOP
statements
END LOOP
WHILE ncycle>0 LOOP
UPDATE SALARY
SET SALARY.amount=SALARY.amount -100
ncycle:=ncycle -1;
END LOOP
Il Linguaggio SQL
Costrutti procedurali in PostgreSQL:
 Costrutti iterativi (for)
For record_or_row IN query LOOP
statements
END LOOP
FOR Studente IN SELECT * FROM STUDENTI LOOP
UPDATE ESAMI
SET Voto=30
WHERE (Corso=“Basi di Dati”)
END LOOP
Il Linguaggio SQL
Oltre ad i costrutti base di SQL2 visti fin qui,
esistono molti costrutti avanzati (i) definiti in
SQL3 e/o (ii) dipendenti dallo specifico DBMS.
 Procedure (Stored Procedures)
 Trigger
 Permessi
 Transazioni
Il Linguaggio SQL
ORDINE
Nome
Codice
Quantita
Xbee Radio Shield
123
3
Arduino Uno Shield
5565
2
Arduino Ethernet
14354
1
Vorrei implementare un comportamento del tipo:
 Ogni volta che viene inserito/modificato un nuovo ordine
con piu’ di 5 quantita’ nel DB viene inviata una mail al
Titolare dell’azienda ..
Il Linguaggio SQL
ORDINE
MAGAZZINO
ACQUISTO
Nome
Codice
Quantita
Codice
Quantita
Codice
Q.a
Data
Xbee Radio Shield
123
3
123
0
123
3
Arduino Uno
Shield
5565
2
5565
2
1/2/
2012
Arduino Ethernet
14354
1
14354
1
Vorrei implementare un comportamento del tipo:
 Ogni volta in cui l’utente fa un’ordine, si aggiorna la tabella
Magazzino, e nel caso non ci siano piu’ prodotti di quel
tipo, si aggiorni anche la tabella Acquisti …
Il Linguaggio SQL
Trigger (o regole attive)  meccanismi di gestione
della base di dati basati sul paradigma ECA
(Evento/Condizione/Azione).
 Evento: primitive per la manipolazione dei dati
(insert, delete, update)
 Condizione: Predicato booleano
 Azione: sequenza di istruzioni SQL, talvolta
procedure SQL specifiche del DBMS.
Il Linguaggio SQL
Trigger (o regole attive)  meccanismi di gestione
della base di dati basati sul paradigma ECA
(Evento/Condizione/Azione).
A che servono i Trigger?
1. Garantire il soddifacimento di vincoli di
integrita’ referenziale, e/o specificare meccanismi
di reazione ad hoc in caso di violazione dei vincoli!
Il Linguaggio SQL
CORSI
ESAMI
Nome
Codice
Crediti
Corso
Studente
Voto
Basi di dati
6464
12
0121
4324235245
30L
Programmazione
1213
12
1213
4324235245
25
Sistemi Operativi
1455
6
1213
9854456565
18
Q. Che accade se un valore nella tabella esterna viene
cancellato o viene modificato?
A. Il vincolo di integrita’ referenziale nella tabella interna
potrebbe non essere piu’ valido! Cosa fare?
Il Linguaggio SQL
Trigger (o regole attive)  meccanismi di gestione
della base di dati basati sul paradigma ECA
(Evento/Condizione/Azione).
A che servono i Trigger?
2. Specificare regole aziendali (business rules),
ossia vincoli generici sulo schema della base di dati
(es. Un impiegato non puo’ avere un aumento di stipendio
superiore al 10%, pena annullamento della transazione).
Il Linguaggio SQL
Trigger (o regole attive)  meccanismi di gestione
della base di dati basati sul paradigma ECA
(Evento/Condizione/Azione).
SINTASSI SQL3
Create trigger Nome
Modo Evento on Tabella
[referencing Referenza]
[for each Livello]
[when (IstruzioneSQL)]
Istruzione/ProceduraSQL
Il Linguaggio SQL
 Modo  before/after
 Evento  insert/delete/update
 Referencing  qui possono essere inserite
variabili globali…
 Livello  row (Il trigger agisce a livello di righe/
statement (Il trigger agisce globalmente a livello
di tabella)
I trigger possono avere modalita’ immediata oppure differita.
Il Linguaggio SQL
Esempio di Trigger in SQL3
CREATE TRIGGER CHECKAUMENTO
BEFORE UPDATE OF CONTO ON IMPIEGATO
FOR EACH ROW
WHEN (NEW.STIPENDIO > OLD.STIPENDIO * 1.2)
SET NEW.STIPENDIO=OLD.STIPENDIO * 1.2
 Modo e’ definito come before.
 Evento e’ definito come update.
 Livello e’ definito come row.
Il Linguaggio SQL
Oltre ad i costrutti base di SQL2 visti fin qui,
esistono molti costrutti avanzati (i) definiti in
SQL3 e/o (ii) dipendenti dallo specifico DBMS.
 Procedure (Stored Procedures)
 Trigger
 Permessi
 Transazioni
Il Linguaggio SQL
SQL2/SQL3 prevede meccanismi di controllo di
accesso alle risorse del DB (tabelle, viste, domini,
etc).
Di default, ogni risorsa appartiene all’utente che
l’ha definita … Su ciascuna risorsa sono definiti
dei privilegi (grant):




insert/update/delete  tabelle/viste
select  tabelle/viste
references tabelle/attributi
usage  domini
Il Linguaggio SQL
Il comando grant consente di assegnare privilegi
su una certa risorsa ad utenti specifici.
grant Privilegio on Risorsa/e to Utente/i
[with grant option]
L’opzione with grant option consente di
propagare il privilegio ad altri utenti del sistema…
grant select on Impiegati to Marco with grant option
grant delete on Impiegati, Salari to Marco, Michele
Il Linguaggio SQL
Il comando revoke consente di revocare privilegi
su una certa risorsa ad utenti specifici.
revoke Privilegio on Risorsa/e from Utente/i
[cascade|restrict]
L’opzione cascade agisce ricorsivamente sui
privilegi eventualmente concessi da quell’utente …
revoke select on Impiegati to Marco cascade
revoke delete on Impiegati, Salari to Marco, Michele
Il Linguaggio SQL
Oltre ad i costrutti base di SQL2 visti fin qui,
esistono molti costrutti avanzati (i) definiti in
SQL3 e/o (ii) dipendenti dallo specifico DBMS.
 Procedure (Stored Procedures)
 Trigger
 Permessi
 Transazioni
Il Linguaggio SQL
Le transazioni rappresentano unita’ di lavoro
elementare che modificano il contenuto di una base
di dati.
start transaction
update SalariImpiegati
set conto=conto*1.2
where (CodiceImpiegato = 123)
commit work
Le transazioni
sono comprese
tra una start
transaction ed
una commit/
rollback
Il Linguaggio SQL
Le transazioni rappresentano unita’ di lavoro
elementare che modificano il contenuto di una base
di dati.
start transaction
update SalariImpiegati
set conto=conto-10
where (CodiceImpiegato = 123)
if conto >0 commit work;
else rollback work
Le transazioni
sono comprese
tra una start
transaction ed
una commit/
rollaback
Il Linguaggio SQL
Ogni DBMS deve garantire le seguenti proprieta’
delle transazioni (proprieta’ acide):
Atomicita’  La transazione deve essere eseguita con la
regola del “tutto o niente”
Consistenza  La transazione deve lasciare il DB in uno
stato consistente, eventuali vincoli di integrita’ non devono
essere violati.
Isolamento  L’esecuzione di una transazione deve essere
indipendente dalle altre.
Persistenza  L’effetto di una transazione che ha fatto
commit work non deve essere perso.
Il Linguaggio SQL
Gestione delle transazioni
Gestione della concorrenza
Gestione dell’affidabilita’
Gestore dell’affidabilita’  garantisce atomicita’ e persistenza
… COME? Usando log e checkpoint.
Gestore della concorrenza  garantisce l’isolamento in caso di
esecuzione concorrente di piu’ transazioni.
Il Linguaggio SQL
In un sistema reale, le transazioni vengono eseguite in
parallelo per ragioni di efficienza / scalabilita’ …
… Tuttavia, l’esecuzione concorrente determina un insieme di
problematiche che devono essere gestite …
T1= Read(x); x=x+1; Write(x); Commit Work
T2= Read(x); x=x+1; Write(x); Commit Work
Se x=3, al termine delle due transazioni x vale 5 (esecuzione
sequenziale) … cosa accade in caso di esecuzione concorrente?
Il Linguaggio SQL
Problema1: Perdita di Aggiornamento
Transazione1 (T1)
Transazione2 (T2)
Read(x)
x=x+1
Read(x)
x=x+1
Write(x)
Commit work
T1
scrive 4
Write(x)
Commit work
T2
scrive 4
Il Linguaggio SQL
Problema2: Lettura sporca
Transazione1 (T1)
Transazione2 (T2)
Read(x)
x=x+1
Write(x)
Read(x)
Commit work
Rollback work
T2
legge 4!
Il Linguaggio SQL
Problema3: Letture incosistenti
T1
legge 3!
Transazione1 (T1)
Transazione2 (T2)
Read(x)
Read(x)
x=x+1
Write(x)
Commit work
T1
legge 4!
Read(x)
Commit work
Il Linguaggio SQL
Problema4: Aggiornamento Fantasma
Vincolo:
x+y+z
deve
essere =
a 1000
Transazione1 (T1)
Transazione2 (T2)
Read(x)
Read(y)
Read(y)
y=y-100
Read(z)
z=z+100
Write(y), Write(z)
Commit work
Vincolo
violato!!
Read(z)
s=x+y+z; commit work