APPENDICE Procedure in SQL (1)

APPENDICE
•
•
•
•
Procedure in SQL
Transazioni in SQL
Embedded SQL
Remote Procedure Call
F.Cesarini - Basi Dati
Distribuite
Appendice
1
Procedure in SQL (1)
•
•
•
•
Standard SQL2 permette di definire procedure, associate a singoli
comandi SQL, memorizzate all’interno del database (vanno a far
parte dello schema)
Una procedura associa un nome ad un comando e permette il
passaggio di parametri
Una procedura può essere richiamata dall’interno di un
programma specificando il valore dei parametri
I DBMS permettono in genere la definizione di procedure in modo
più sofisticato di quanto previsto dallo standard SQL2
F.Cesarini - Basi Dati
Distribuite
Appendice
2
1
Procedure in SQL (2)
esempio di procedura standard SQL-2
procedure Assegnacitta (:Dip char(20), :Citta char (20))
update Dipartimento
set Città = :Citta
where Nome = :Dip;
Questa procedura può essere richiamata da un programma
…
nomecitta = ‘Firenze’;
nomedip = ‘sistemi’;
…
Assegnacitta ( :nomedip, :nomecitta)
…
F.Cesarini - Basi Dati
Distribuite
Appendice
3
Procedure in SQL (3)
estensioni allo standard SQL-2
•
Esempi di estensioni sono
– Sequenze di comandi
– Strutture di controllo (if-then-else)
– Definizione di variabili locali
•
•
Ogni sistema da delle possibilità in questo ambito, che però si
diversificano anche nella sintassi
Alcuni sistemi commerciali forniscono una estensione di SQL che è
un vero e proprio linguaggio di programmazione con cui è
possibile scrivere intere applicazioni
– ORACLE: linguaggio PL/SQL
F.Cesarini - Basi Dati
Distribuite
Appendice
4
2
TRANSAZIONI IN SQL (1)
•
Comandi generali per le transazioni
BEGIN TRANSACTION
END TRANSACTION
COMMIT WORK
ROLLBACK WORK
•
•
I comandi begin transaction e end transaction racchiudono le
istruzioni che compongono il corpo della transazione
Il comando commit work segnala al sistema che gli effetti sul
database devono essere resi permanenti
– deve essere eseguita uma operazione di commit
•
Il comando rollback work segnala al sistema che gli effetti delle
istruzioni eseguite fino a quel momento devono essere annullati
– deve essere eseguita una operazione di abort
•
La sintassi SQL non prevede tutti questi comandi espliciti
F.Cesarini - Basi Dati
Distribuite
Appendice
5
TRANSAZIONI IN SQL (2)
• Non esistono i comandi begin transaction e end
transaction
• Esistono invece commit e rollback
• Una istruzione SQL è implicitamente la prima
istruzione di una transazione (transazione corrente) se
non esiste già una transazione corrente
• I comandi commit e rollback segnalano anche la fine
della transazione
• Ogni istruzione SQL che segue un commit o un rollback
è la prima istruzione di una nuova transazione
• Il comando set transaction serve ad eventualmente
definire le caratteristiche della transazione corrente
F.Cesarini - Basi Dati
Distribuite
Appendice
6
3
TRANSAZIONI IN SQL (3)
comando COMMIT
• Operazioni eseguite a seguito di un comando COMMIT
– Viene eseguito un CLOSE implicito per ogni cursore aperto
(cfr. embedded SQL)
– Viene eseguito un SET CONSTRAINTS ALL IMMEDIATE
implicito
• il controllo dei vincoli di integrità può essere INITIALLY
DEFERRED al momento della loro definizione (cfr. cicli nei
vincoli di integrità referenziale)
– Viene eseguito un DELETE FROM T per ogni T temporanea
per cui era stato specificato un ON COMMIT DELETE
– Vengono rese permanenti tutte le modifiche effettuate dalla
transazione
• Se qualcuna di queste azioni fallisce (ad es. il controllo
dei vincoli) la transazione fallisce e va in rollback
F.Cesarini - Basi Dati
Distribuite
Appendice
7
TRANSAZIONI SQL (4)
comando ROLLBACK
• Operazioni eseguite a seguito di un comando
ROLLBACK
– Viene eseguito un CLOSE implicito per ogni cursore aperto
(cfr. embedded SQL)
– Tutte le modifiche effettuate dalla transazione vengono
cancellate (“undo”)
– La transazione è terminata senza successo
F.Cesarini - Basi Dati
Distribuite
Appendice
8
4
TRANSAZIONI SQL (5)
comando SET TRANSACTION (1)
•
Il comando SET TRANSACION viene usato per definire le
caratteristiche della transazione che sta per essere avviata
riguardo al
– Modo di accesso
– Dimensione dell’area diagnostica
– Livello di isolamento
•
Modo di accesso
– READ ONLY (le modifiche sono proibite)
– READ WRITE
•
Dimensione dell’area diagnostica
– DIAGNOSTIC SIZE n (n indica il numero di condizioni che vengono
mantenute)
F.Cesarini - Basi Dati
Distribuite
Appendice
9
TRANSAZIONI SQL (6)
comando SET TRANSACTION (2)
• Livello di isolamento ISOLATION LEVEL
SERIALIZABLE
– Viene garantito che le transazioni concorrenti siano eseguite in
modo serializzabile (modo di default)
– Non può verificarsi nessuna delle seguenti condizioni
• Dirty read (es: T1 aggiorna una riga, T2 legge quella riga, T1 va
in rollback quindi T2 ha un dato non corretto)
• Nonrepeatable read (es: T1 legge una riga, T2 modifica quella
riga, T1 legge di nuovo quella riga quindi ora legge un dato
diverso)
• Phantoms (es: T1 legge un insieme di righe che soddisfano una
certa condizione, T2 inserisce una riga che soddisfa quella stessa
condizione, T1 legge nuovamente l’insieme di righe che soddisfa
quella condizione quindi ora vede un fantasma, cioè una riga che
prima non esisteva)
F.Cesarini - Basi Dati
Distribuite
Appendice
10
5
TRANSAZIONI SQL (7)
comando SET TRANSACTION (3)
•
Livello di isolamento ISOLATION LEVEL REPEATABLE
READ
– Può verificarsi la condizione phantom
•
Livello di isolamento ISOLATION LEVEL READ COMMITTED
– Possono verificarsi le condizioni nonrepeatable read e phantom
•
Livello di isolamento ISOLATION LEVEL READ
UNCOMMITTED
– Possono verificarsi le condizioni dirty read, nonrepeatable read e
phantom
•
•
Un sistema che supporta qualcuno di questi tre livelli, di solito
fornisce anche un qualche comando di LOCK per gestire
autonomamente i possibili conflitti
I comandi di LOCK non sono previsti da SQL-2
F.Cesarini - Basi Dati
Distribuite
Appendice
11
SQL Embedded (1)
•
•
Le istruzioni SQL sono inserite direttamente nel programma sorgente
Una istruzione SQL deve
– essere preceduta da exec SQL
– terminare con ;
•
•
•
La compilazione del programma sorgente deve essere preceduta dal
richiamo di un preprocessore che sostituisce le istruzioni SQL con
opportune chiamate a librerie
Deve quindi essere disponibile un preprocessore opportuno per il
linguaggio utilizzato e il DBMS a cui si vuole accedere
L’integrazione tra SQL e linguaggi di programmazione deve
affrontare il cosiddetto impedance mismatch (conflitto di impedenza)
– SQL opera su intere tabelle, è di tipo set oriented
– i linguaggi di programmazione accedono ad una tabella una riga alla volta,
tuple oriented
F.Cesarini - Basi Dati
Distribuite
Appendice
12
6
SQL Embedded (2)
•
SQL embedded fornisce un approccio “statico” alla
programmazione che accede a database
– Il testo della frase SQL è scritto all’interno del programma
– Può essere cambiato solo riprocessando il sorgente
•
Un approccio più dinamico si può avere utilizzando chiamate di
funzioni, API, Application Programing Interface
– In questo caso però il controllo sui comandi SQL (per es. il controllo
sintattico) può essere fatto solo al momento dell’esecuzione
•
•
SQl embedded usa il concetto di cursore per recuperare le tuple
risultato della select una alla volta
Nel caso venga recuperata una sola tupla può essere usata una
istruzione alternativa, che fa a meno del cursore, e pone i valori
recuperati direttamente in variabili
F.Cesarini - Basi Dati
Distribuite
Appendice
13
SQL Embedded (3)
•
•
Negli esempi facciamo riferimento al linguaggio ospite C
Possono essere definite delle variabili condivise fra il programma
C e i comandi SQL
– Le variabili condivise devono essere dichiarate in una opportuna
sezione: EXEC SQL BEGIN DECLARE SECTION … EXEC SQL
END DECLARE SECTION
– I comandi SQL si riferiscono alle variabili condivise facendole
precedere da :
– SQLCODE e SQLSTATE sono variabili usate dal DBMS per
comunicare condizioni di errore o eccezioni, spesso sono campi del
record SQLCA
F.Cesarini - Basi Dati
Distribuite
Appendice
14
7
Esempio 1 procedura C [?]
Void VisualizzaStipendiDipart (char NomeDip[])
{ exec sql begin declare section;
char Nome[20], Cognome [20]; long int Stipendio;
exec sql end declare section;
exec sql declare ImpDip cursor for
select Nome, Cognome, Stipendio
from Impiegato
where Dipart = :NomeDip;
exec sql open ImpDip;
exec sql fetch ImpDip into :Nome, :Cognome, :Stipendio;
printf (“Dipartimento %s\n”, NomeDip);
while (sqlca.code == 0) {
printf (“L’impiegato %s %s”, Nome, Cognome);
printf (“ha stipendio %d\n”, Stipendio);
exec sql fetch ImpDip into :Nome, :Cognome, :Stipendio; }
exec sql close cursor ImpDip; }
F.Cesarini - Basi Dati
Distribuite
Appendice
15
Esempio 2 segmento codice C
…
prompt (“Immettere il nome del dipartimento: “, nome_d);
EXEC SQL
SELECT Numero_d INTO :numero_d
FROM Dipartimento WHERE Nome_d = :nome_d;
EXEC SQL DECLARE Emp CURSOR FOR
SELECT Ssn, Nome_batt, Iniz_int, Cognome, Stipendio
FROM Impiegato WHERE N_d = :numero_d
FOR UPDATE OF Stipendio;
EXEC SQL OPEN Emp;
EXEC SQL FETCH FROM Emp INTO :ssn, :nome_batt, :iniz_int, :cognome, :stipendio;
while (SQLCODE == 0){
printf (“Il nome dell’impiegato è:”, nome_batt, iniz_int, cognome);
prompt(“Immettere l’importo dell’aumento : “, aumento);
EXEC SQL}
F.Cesarini - Basi Dati
Distribuite
Appendice
16
8
Note all’esempio
Il preprocessore introduce una struttura dati sqlca (SQL
Communication Area) in cui ci sono dati di comunicazione fra il
programma e il DBMS
•
– sqlca.code contiene il codice d’errore dell’ultimo comando SQL inviato al
DBMS ( 0 = OK)
exec sql begin/end declare section delimitano le variabili C da utilizzare
come parametri per i comandi SQL (variabili host)
Un cursore è uno strumento che permette di accedere alle righe di una
tabella una alla volta
•
•
– declare definisce un cursore associato ad una interrogazione
– open manda in esecuzione la query e inizializza il cursore alla prima riga
del risultato
– fetch copia la riga puntata dal cursore nelle variabili e posiziona il cursore
sulla riga successiva
– close chiude il cursore, il risultato della query non è più necessario
F.Cesarini - Basi Dati
Distribuite
Appendice
17
Remote Procedure Call (1)
•
Strumento per costruire software di tipo client/server usando una
astrazione della tradizionale chiamata a procedura
– Quando un client chiama una procedura remota, invia un messaggio
di richiesta al server attraverso la rete
– Dopo aver inviato la richiesta, il client si blocca in attesa della risposta
– Quando il programma remoto (server) riceve una richiesta da un
client, invoca la procedura specificata e manda il risultato indietro al
client
•
Per implementare l’interazione è necessario del software apposito
– Dalla parte del client gestisce l’invio del messaggio sulla rete e l’attesa
della risposta
– Dalla parte del server gestisce la ricezione del messaggio, il richiamo
della procedura specificata e l’invio della risposta
•
Il software aggiuntivo è chiamato communication stub o proxy
F.Cesarini - Basi Dati
Distribuite
Appendice
18
9
Remote Procedure Call (2)
main
prog
main
prog
proc
B
client
stub
for B
proc
B
single
computer
client
server
F.Cesarini - Basi Dati
Distribuite
server
stub
for B
Appendice
19
Remote Procedure Call (3)
•
•
Il programma usa le procedure call come se fossero locali
Se viene fatta una call a una procedura che non è locale
Il communication stub intercetta la procedure call
Raccoglie i valori degli argomenti (operazione di “ marshaling”)
Invia un messaggio al communication stub sul server
Questi usa il tradizionale procedure call per attivare la procedura
specifica e invia il risultato indietro al client stub
– Quando il client stub riceve la risposta, la passa al suo chiamante
esattamente come se fosse una procedura locale
–
–
–
–
•
•
Le call dal main al client stub e dal server stub alla procedura
usano la stessa interfaccia della call convenzionale del main alla
procedura
Ai client stub può essere dato lo stesso nome della procedura che
rimpiazzano, così non viene cambiato il codice del programma
originale
F.Cesarini - Basi Dati
Distribuite
Appendice
20
10