Corso diBasi di Dati

10. Esercitazioni in SQL:Complementi

Guido Pezzini

A.A. 2016–2017

Guido Pezzini Basi di Dati I

Funzioni condizionali

Vediamo qualche altro comando utile di SQL.

Il comando coalesce ammette come argomento una sequenza diespressioni e restituisce il primo valore non nullo. La funzione puoquindi essere usata per convertire valori nulli in valori espliciti.

Esempio: La sequente query estrae dalla tabella Impiegati(Nome,Dipart, Stipendio) i nomi degli impiegati ed i dipartimenti cuiafferiscono gli impiegati, usando la stringa ”Ignoto” nel caso in cuinon si conosca il dipartimento.

select Nome, coalesce(Dipart,"Ignoto")

from Impiegati

Guido Pezzini Basi di Dati I

Funzioni condizionali

Il comando nullif fa il contrario: ammette come argomento unaespressione e un valore costante; se l’espressione e pari al valorecostante, la funzione restituisce NULL, altrimenti restituisce ilvalore dell’espressione.

Esempio: La sequente query estrae dalla tabella Impiegati(Nome,Dipart, Stipendio) i nomi degli impiegati ed i dipartimenti cuiafferiscono gli impiegati, mettendo il dipartimento al valore NULLse il dipartimento e la stringa ”Ignoto”.

select Nome, nullif(Dipart,"Ignoto")

from Impiegati

Guido Pezzini Basi di Dati I

Funzioni condizionali

Il comando case case permette di specificare strutture condizionali,il cui risultato dipende dalla valutazione del contenuto delle tabelle.La sintassi ammette due varianti. La prima riguarda una singolaespressione:

case espressionecase when valoredellespressione then risultatocase when altrovaloredellespressione then altrorisultatocase . . .case else altrorisultatoend

Guido Pezzini Basi di Dati I

Funzioni condizionali

Esempio: consideriamo la tabella Veicoli(Targa, Tipo, KWatt).Calcoliamo le tasse di circolazione: per le auto, e 2.58∗KWatt,mentre per le moto e (22.00 + 1.00∗KWatt). Per altri tipi,mettiamo NULL. Creiamo il risultato con le colonne Targa, Tasse.

select Targa,

case Tipo

case when ”auto” then 2.58∗KWattcase when ”moto” then (22.00 + 1.00∗KWatt)case else NULL

end as Tasse

from Veicoli

Guido Pezzini Basi di Dati I

Funzioni condizionali

La seconda e piu generica:

case

case when criterio then espressionecase when altrocriterio then altraespressionecase . . .case else altraespressioneend

Guido Pezzini Basi di Dati I

Funzioni condizionali

Esempio: nella tabella Impiegati(Nome, Ufficio, Dipart,Stipendio), aumentiamo lo stipendio:

del 10%, se l’impiegato lavora in amministrazione, ufficio 10;

del 20%, se l’impiegato lavora in amministrazione, nonnell’ufficio 10;

del 15%, se l’impiegato lavora in produzione, non nell’ufficio10.

Guido Pezzini Basi di Dati I

Funzioni condizionali

update Impiegati set Stipendio =

case

when (Dipart = "Amministrazione" and Ufficio = 10)

then Stipendio * 1.1

when (Dipart = "Amministrazione" and Ufficio <> 10)

then Stipendio * 1.2

when Dipart = "Produzione"

then Stipendio * 1.15

else Stipendio

end

Guido Pezzini Basi di Dati I

Transazioni

La sintassi per specificare transazioni, cioe sequenze di operazioniin SQL, e la seguente:

start transaction;

singole operazioni...commit work;

Esempio:

start transaction;

update ContoCorrente

set Ammontare = Ammontare + 10

where NumConto = 12202;

update ContoCorrente

set Ammontare = Ammontare - 10

where NumConto = 42177;

commit work;

Guido Pezzini Basi di Dati I

Transazioni

Osservazioni:

SQL include la possibilita di comandi procedurali (if, goto,ecc...) all’interno delle transazioni, comandi che non vedremo.

Anche un comando per interrompere l’intera transazioneevitando ogni modifica ai dati: rollback work.

Il DBMS e organizzato in modo che l’esecuzione di unatransazione sia indipendente dalla contemporanea esecuzionedi altre transazioni. In particolare:

il risultato dell’esecuzione contemporanea di piu transazionideve essere uguale al risultato che le stesse transazioniotterrebbero qualora ciascuna di esse fosse eseguita da sola,l’esecuzione di un rollback di una transazione non deve causareil rollback di altre transazioni, eventualmente generando unareazione a catena. Questo sia in caso di rollback “volontario”,cioe dato come comando in una transazione, sia in caso dirollback “forzato”, cioe causato da un errore.

Guido Pezzini Basi di Dati I

Procedure

SQL permette la definizione di procedure, che normalmentevengono memorizzate all’interno della base di dati come parti delloschema.

Come accade nei linguaggi di programmazione (ad es. C), leprocedure permettono di associare un nome ad una sequenza diistruzioni SQL, con la possibilita di specificare dei parametri(argomenti).

Una volta che la procedura e definita, essa e utilizzabile come sefacesse parte dell’insieme dei comandi SQL predefiniti.

Guido Pezzini Basi di Dati I

Procedure

Esempio: consideriamo la tabella Dipartimenti(Nome, Citta).Creiamo una procedura che accetti come argomenti il nome di undipartimento ed una citta, e che assegni la citta specificata aldipartimento specificato.

procedure AssegnaCitta(:Dipartimento varchar(20),

:Citta varchar(20))

begin

update Dipartimenti

set Citta = :Citta

where Nome = :Dipartimento;

end;

Osservazione: la sintassi puo variare a seconda del DBMS.

Guido Pezzini Basi di Dati I

Procedure

Una volta definita questa procedura, si potra usarla come uncomando SQL. Ad esempio, per assegnare la citta ”Roma” aldipartimento di nome ”Matematica”, useremo:

mysql>AssegnaCitta("Matematica", "Roma");

Osservazione: non e necessario sapere in quale tabella sonoimmagazzinati questi dati, o in che modo. Basta sapere che labase di dati e dotata di questa procedura.

Guido Pezzini Basi di Dati I

Procedure

Le procedure possono coinvolgere un numero qualsiasi di tabelle edi colonne.

Esempio: consideriamo le tabelle Impiegati(Nome, Citta) eDipartimenti(NomeDip, Citta). Definiamo una procedura checambia la citta in entrambe le tabelle, da una “:VecchiaCitta” aduna “:NuovaCitta”.

procedure Trasloco(:VecchiaCitta varchar(20),

:NuovaCitta varchar(20))

begin

update Dipartimenti

set Citta = :NuovaCitta

where Citta = :VecchiaCitta;

update Impiegati

set Citta = :NuovaCitta

where Citta = :VecchiaCitta;

end;

Guido Pezzini Basi di Dati I

Trigger

SQL mette a disposizione anche i trigger, detti anche regole attive.

Un trigger:

si attiva quando accade uno specifico evento all’interno dellabase di dati

e controlla se e soddisfatta una data condizione.

Se sı, allora esegue un’azione stabilita.

Guido Pezzini Basi di Dati I

Trigger

Sintassi:

create trigger NomeTriggerafter azione da controllarefor each row

when condizioneazione del trigger

Guido Pezzini Basi di Dati I

Trigger

Esempio:

create trigger ImpiegatiSenzaDip

after insert into Impiegati

for each row

when (new.Dipart is null)

update Impiegato

set Dipart = "NuoviArrivati"

where Matr = new.Matr

Il trigger viene attivato tutte le volte che il sistema rileval’inserimento di righe all’interno della tabella Impiegati.

Se la condizione che segue when e soddisfatta, ovvero se la rigainserita ha NULL sulla colonna Dipart, l’azione del trigger vieneeseguita: assegna all’attributo Dipart del nuovo impiegato il valore”Nuovi Arrivati”.

Guido Pezzini Basi di Dati I

Trigger

Ogni trigger e sensibile a un solo evento.

L’azione da controllare puo solo riguardare operazioni SQL diaggiornamento dello stato della base di dati, ovvero insert edelete su tabelle, e update su tabelle o singoli attributi.

Nel criterio dopo when e possibile utilizzare le variabilipredefinite new e old, che si riferiscono alla nuova e allavecchia versione della riga aggiunta o modificata.

L’azione del trigger, e rappresentata da un singolo comando ouna procedura SQL.

Guido Pezzini Basi di Dati I

Trigger

Un’applicazione tipica dei trigger e l’introduzione di vincolid’integrita “personalizzati”.

Va pero tenuto sotto controllo il rischio di trigger “a catena”: essireagiscono a modifiche della base di dati, e possono a loro voltamodificare la base di dati. Quindi l’attivazione di un trigger puocausare l’attivazione di altri trigger, con il rischio di attivazioni“cicliche” che non terminano.

Guido Pezzini Basi di Dati I