lezione17 SQL DDL proc

LEZIONE17 SQL DDL PROC
PAG. 1 / 4
PROF. ANDREA ZOCCHEDDU
LEZIONE17 SQL DDL PROC
LINGUAGGIO SQL
AMMINISTRAZIONE DI DATABASE
COMANDI DEL DDL
In questa seconda parte della discussione sul DDL vedremo alcuni altri comandi SQL
appena più avanzati. In particolare esamineremo:
Create or Alter
Create or Alter
Create or Alter
Procedure
Group
User
Serve per
Serve per
Serve per
Create or Alter
Grant
Serve per
STORED PROCEDURE
Una procedura è una porzione di programma che deve risolvere un determinato compito
eseguendo comandi raggruppati insieme. Chi ha studiato un linguaggio procedurale (Pascal,
C++) può pensarla simile alle funzioni e quindi occorre discutere di dichiarazione di
parametri e di variabili locali.
In generale la procedura è dichiarata come segue:
CREATE PROCEDURE Nome (parametri)
AS BEGIN
Comandi in linguaggio procedurale | SQL
END;
dove tutte le sezioni sono obbligatorie, tranne i parametri.
Una Stored Procedure è scritta in un linguaggio di programmazione che mette insieme
comandi procedurali e linguaggio SQL ed è archiviata sul Database Server. Il Cliente può
richiamarla in modo semplice specificando il nome e gli eventuali parametri. Il vantaggio di
usare una procedura è che l’elaborazione avviene sul “lato Server” senza gravare sul Client
e riducendo il traffico di rete.
Il Client può ignorare il linguaggio con cui è scritta la Stored Procedure e questo le rende
particolarmente utili quando programmi client sono scritti con linguaggi diversi o lavorano
su sistemi operativi diversi o in ambienti e piattaforme diverse.
PARAMETRI IN, OUT E INOUT
I parametri sono degli identificatori di locazione che servono per passare valori dal
programma chiamante verso la procedura o dalla procedura verso il programma chiamante o
in entrambe le direzioni. Ciascun parametro deve indicare il tipo di dato cui si riferisce.
I parametri di tipo IN servono per passare valori verso la procedura ovvero per dare alla
procedura dei valori con cui lavorare. Per esempio:
CREATE PROCEDURE
Inserisci (IN :Voto INT; IN :IDS CHAR[3]; IN :IDM CHAR[3])
[email protected]
LEZIONE17 SQL DDL PROC
PAG. 2 / 4
PROF. ANDREA ZOCCHEDDU
AS BEGIN
INSERT INTO Verifiche (Data , Matricola, Materia, Voto)
VALUES (Date(), :IDS, :IDM, :Voto)
END;
È una procedura con tre parametri tutti di tipo IN che corrispondono al voto ed alle chiavi
esterne dello studente e della materia di una interrogazione. I nomi dei parametri iniziano
con il simbolo “:” (due punti). Prima del parametro si indica il tipo di passaggio. Dopo il
parametro si specifica il tipo del dato.
I parametri di tipo OUT servono per rendere valori dalla procedura verso il programma
che la invoca ovvero per ottenere dalla procedura informazioni calcolate. Per esempio:
CREATE PROCEDURE UltimoPrestito (OUT :Quando DATE)
AS BEGIN
:Quando := SELECT MAX(Data)
FROM Prestiti ;
END;
È una procedura con un parametro di tipo OUT che prende la data dell’ultimo prestito
erogato.
I parametri di tipo INOUT servono per comunicare in entrambi i sensi tra procedura e
programma esterno.
FUNZIONI
Alcuni linguaggi permettono l’uso di funzioni oltre che di procedure. La funzione
restituisce un dato come accade nei linguaggi procedurali (C++ o Pascal). In generale è
dichiarata come segue:
CREATE FUNCTION Nome (parametri) : Tipo
AS BEGIN
Comandi in linguaggio procedurale | SQL
END;
dove tutte le sezioni sono obbligatorie, tranne i parametri.
Per esempio:
CREATE FUNCTION RecentePrestito (IN :X CHAR[25]) : DATE
AS BEGIN
RESULT := SELECT MAX(Data)
FROM Prestiti
INNER JOIN Libri ON Prestiti.IDL=Libri.IDL;
WHERE Libri.Genere = :X;
END;
È una funzione che restituisce la data dell’ultimo prestito di un libro di genere indicato nel
parametro. La parola chiave RESULT indica il risultato che deve essere restituito dalla
funzione. Un esempio di chiamata potrebbe essere:
miaData := RecentePrestito ('Storia');
LINGUAGGIO DEL SERVER
Oltre al linguaggio SQL il server incorpora anche un ulteriore linguaggio con cui scrivere
le procedure e le funzioni. Il più famoso linguaggio di questo tipo è PL-SQL ma ogni DB
Server ha un suo dialetto. In queste dispense ci riferiremo ad un linguaggio generico, valido
per la gran parte dei prodotti esistenti.
[email protected]
LEZIONE17 SQL DDL PROC
PAG. 3 / 4
PROF. ANDREA ZOCCHEDDU
Le istruzioni che il linguaggio deve prevedere sono almeno le seguenti tre:
•
Assegnazione
•
•
Istruzione decisionale
Istruzione iterativa
ASSEGNAZIONE
L’assegnazione permette di copiare un valore (spesso ottenuto elaborando una espressione
o un comando SQL) in una locazione (una variabile o un parametro OUT). La sintassi
generale è la seguente:
Locazione := Espressione ;
per esempio
Y := SELECT MAX(Stipendio)
FROM Dipendenti
WHERE Mansione =
'Programmatore';
ISTRUZIONE DECISIONALE
L’istruzione decisionale permette di decidere se eseguire una altra istruzione in base al
risultato di una espressione booleana (spesso ottenuta elaborando un comando SQL). La
sintassi generale ha le seguenti due forme:
IF Espressione
THEN Comando ;
e
IF Espressione
THEN Comando
ELSE Comando ;
Se dopo la parola THEN o la parola ELSE occorre specificare più di un comando è
necessario racchiuderli tra le parole BEGIN ... END.
Per esempio
IF NOT EXISTS (
SELECT *
FROM Prestiti
WHERE Libro = :IDL )
AND Restituzione IS NULL )
THEN INSERT INTO Prestiti VALUES (:IDL, :IDP, Oggi()
ELSE INSERT INTO Prenotazioni VALUES (:IDL, :IDP ) ;
)
questo comando verifica se il libro col codice :IDL è in prestito e non ancora reso. Se è
libero allora inserisce il nuovo prestito altrimenti registra solo una prenotazione.
Per esempio
IF EXISTS (
SELECT *
FROM Versamenti
WHERE Socio = :IDS
AND Prodotto = :IDP
AND Data=Oggi())
THEN UPDATE Versamenti SET Quantità = Quantità + :Q
WHERE Socio = :IDS AND Prodotto = :IDP AND Data=Oggi()
ELSE INSERT INTO Versamenti
VALUES (:IDS , :IDP , Oggi() ) ;
[email protected]
LEZIONE17 SQL DDL PROC
PAG. 4 / 4
PROF. ANDREA ZOCCHEDDU
questo comando verifica se in data odierna il socio ha già versato lo stesso prodotto. Se lo
ha già versato allora incrementa la quantità versata con l’ulteriore quantità che sta versando
adesso, altrimenti viene inserito un nuovo versamento.
ISTRUZIONE ITERATIVA
L’istruzione iterativa permette di effettuare un ciclo fino al verificarsi di una condizione
booleana detta guardia (spesso ottenuta elaborando un comando SQL). La sintassi generale
ha la seguente forma:
WHILE Condizione
DO Comando ;
Per esempio
WHILE 100 < (
DO
SELECT SUM(Percentuale)
FROM Composizione
WHERE Prodotto = 'Margherita' )
UPDATE Composizione SET Percentuale = Percentuale + 1
WHERE Prodotto = 'Margherita' AND Ingrediente = 'olio';
questo comando continua ad incrementare la percentuale dell’ingrediente Olio nella pizza
Margherita fino a quando viene raggiunta una composizione pari al 100%.
INDICE DELLA LEZIONE
Stored Procedure ........................................................................................................................ 1
Parametri IN, OUT e INOUT ................................................................................................... 1
Funzioni .................................................................................................................................... 2
Linguaggio del server ................................................................................................................. 2
Assegnazione ............................................................................................................................ 3
Istruzione decisionale ............................................................................................................... 3
Istruzione iterativa.................................................................................................................... 4
[email protected]

Download Report