Elementi di PL/SQL
Pl/Sql
Il PL/SQL (Procedural Language/Structured Query
Language) è un linguaggio di programmazione
procedurale di Oracle che costituisce un’estensione
dell‘SQL. Esso consente di creare store procedures e
funzioni, ma anche di creare i triggers. Infatti
l’azione di un trigger è spesso definita mediante un
codice PL/SQL.
Il PL/SQL supporta le variabili, condizioni e gestisce
le eccezioni.
Struttura di un programma
PL/SQL
I programmi PL/SQL sono strutturati a blocchi.
I blocchi hanno questa forma generale:
declare -- Blocco di dichiarazione (opzionale)
begin -- Codice da eseguire
exception -- Gestione eccezioni(opzionale)
end
Struttura dei programmi
PL/SQL
La sezione DECLARE specifica i tipi di dato delle
variabili, delle costanti e i tipi definiti dal
programmatore.
Il blocco tra BEGIN ed END specifica il codice da
eseguire.
Le eccezioni possono essere di due tipi:
eccezioni predefinite
eccezioni definite dal programmatore.
Si possono sollevare le eccezioni definite dal
programmatore in modo esplicito
In Oracle esistono numerose eccezioni predefinite.
Dichiarazioni
La sezione dichiarativa inizia con la parola chiave declare
ed è seguita da un elenco di definizioni di variabili e
cursori.
Le variabili possono essere definite come valori costanti,
mediante la parola chiave constant, e possono essere
inizializzate a un valore in fase di dichiarazione.
I tipi di base che possono essere utilizzati sono gli stessi
che possono essere utilizzati nella definizione delle
colonne.
L’assegnazione dei valori alle variabili viene fatta
mediante il simbolo := come in Pascal. I valori costanti
possono anche essere assegnati mediante la parola chiave
default.
Esempio
Aree
Raggio Area
Il seguente listato calcola l’area di un cerchio con raggio
fissato e l’inserisce nella tabella aree
DECLARE
pi constant number(9,7) :=3.1415927;
raggio integer(5);
area number(14,2);
BEGIN
raggio :=3;
area := pi*power(raggio,2);
Insert into Aree values (raggio, Area);
end
Raggio Area
3
28.27
Reperire valori da una tabella
Supponiamo che i valori del raggio che ci interessano
siano già presenti in una tabella VALORI_RAGGIO di
una sola colonna Raggio. Vogliamo inserire nella tabella
AREE i valori dei raggi contenuti nella tabella
VALORI_RAGGIO insieme alle rispettive aree.
Per fare questo abbiamo bisogno di un cursore.
Valori Raggio
Raggio
7
16
5
9
2
Aree
Raggio Area
Cursori
Un Cursore è essenzialmente una variabile di tipo tupla
che varia su tutte le tuple che risultano da qualche
query.
Un cursore ha il compito di conservare i risultati di una
query affinchè possano essere elaborati da altri
comandi all’interno del blocco PL/SQL.
In PL/SQL un cursore si dichiara mediante l’istruzione:
CURSOR <Nome> is <query>;
Per prelevare una tupla dal cursore C si usa
FETCH C INTO <Variabile>
Variabili ancorate a colonne
E’ possibile definire delle variabili il cui tipo corrisponde
a quello di una determinata colonna, di cui si specifica il
nome. La variabile viene dichiarata nel seguente modo:
<nomevariabile> <tabella.attributo>%TYPE
Esempio:
X articoli.art_descrizione%TYPE
Vantaggi: 1. non occorre conoscere il tipo esatto della
colonna. 2. se si cambia la definizione della colonna dal
database, la variabile cambia tipo in accordo con essa
Variabili ancorate a righe
E’ possibile definire delle variabili di tipo tupla legate
alla definizione delle righe di una tabella. Esse
costituiscono di fatto dei record, in cui le definizioni
dei campi sono in corrispondenza con gli attributi di una
tabella. Tali variabili sono definite nel seguente modo:
<nomevar> <nometabella>%ROWTYPE
Assegna a nomevar il tipo delle tuple della tabella.
nomevar potrà essere trattata come una tupla e
nomevar.a fornirà il valore dell’attributo a nella tupla
nomevar
Variabile ancorata a cursore
In particolare un cursore “contiene” il risultato di
una select.
Dunque, allo stesso modo in cui il tipo di una variabile
si può ancorare a una riga di una tabella, esso si può
ancorare anche a un cursore. La variabile ancorata a
un cursore è dichiarata così:
<nomevariabile> <nomecursore>%ROWTYPE
Cursori, attivazione
Un cursore per essere utilizzato deve essere attivato
mediante l’istruzione
OPEN <nomecursore>
Quando non serve più viene disattivato mediante
l’istruzione
CLOSE <nomecursore>
Con l’istruzione open, il cursore viene inizializzato
automaticamente alla prima tupla del risultato della
select che la definisce.
Reperire valori da una
tabella: Esempio
Torniamo al nostro problema iniziale: supponiamo che i
valori del raggio che ci interessano siano già presenti
in una tabella VALORI_RAGGIO di una sola colonna
Raggio. Vogliamo inserire nella tabella AREE i valori
dei raggi contenuti nella tabella VALORI_RAGGIO
insieme alle rispettive aree.
Valori Raggio
Raggio
7
16
5
9
2
Aree
Raggio Area
Cursori, Esempio
DECLARE
Dichiarazione del
pi constant number(9,7) :=3.1415927;
cursore rag_cursore
area number (14,2);
Cursor rag_cursore is
Definizione della
Select * from Valori_raggio;
variabile val_rag,
val_rag rag_cursore%Rowtype;
ancorata al cursore
BEGIN
Attiva cursore
OPEN rag_cursore;
FETCH rag_cursore into val_rag;
Inserisce il contenuto
area := pi*power(val_rag.raggio,2);
del cursore nella
Insert into Aree values (val_rag.raggio, Area);
variabile val_rag
CLOSE rag_cursore;
end
Disattiva il cursore
Esempio: Effetto
Valori Raggio
Raggio
7
Aree
Raggio
Area
7
153.94
16
5
9
2
Inserisce solo il primo valore della tabella Valori_Raggio
e la relativa area.
Come si fa a inserirle tutte?
Cicli
Come in altri linguaggi di programmazione procedurali,
in PL/SQL sono definiti dei costrutti per effettuare i
cicli. Esistono tre principali tipi
Cicli semplici: si ripetono fino al raggiungimento di
un’istruzione exit o exit when
Cicli FOR: si ripetono un numero fissato di volte
Cicli WHILE: si ripetono fintantoché una condizione è
soddisfatta
Cicli Semplici
Sintassi:
LOOP
<istruzioni>
EXIT WHEN <condizione>
END LOOP
Esempio:
Inserire nella tabella aree tutte le aree dei cerchi quando
il raggio assume valori interi crescenti a partire da 3, fino
a quando non si trova la prima area maggiore di 100.
Ciclo semplice, Esempio
DECLARE
pi constant number(9,7) :=3.1415927;
raggio integer (5);
area number (14,2);
BEGIN
raggio :=3;
LOOP
area := pi*power(raggio,2);
Insert into Aree values (raggio, Area);
raggio:=raggio+1;
EXIT WHEN area>100;
END LOOP;
end
Aree
Raggio
Area
3
28.27
4
50.27
5
78.54
6
113.1
Cicli semplici a cursore
Nel nostro problema iniziale, ci interessava far variare
i valori del cursore su tutta la tabella target della
query. Questo si può realizzare mediante i cicli.
Le condizioni per uscire dal ciclo sono spesso condizioni
sullo stato del cursore. Questo stato può essere
individuato mediante i valori di certi attributi:
%FOUND significa che il cursore può trasmettere un
record
%NOT FOUND il cursore non può trasmettere record
%ISOPEN il cursore è stato aperto
%ROWCOUNT numero di righe trasmesse dal cursore
fino a questo momento
Cicli semplici a Cursore,
Esempio
DECLARE
pi constant number(9,7) :=3.1415927;
area number (14,2);
Cursor rag_cursore is
Select * from Valori_raggio;
val_rag rag_cursore%Rowtype;
BEGIN
OPEN rag_cursore;
LOOP
FETCH rag_cursore into val_rag;
EXIT WHEN rag_cursore%NOTFOUND;
area := pi*power(val_rag.raggio,2);
Insert into Aree values (val_rag.raggio, Area);
END LOOP;
CLOSE rag_cursore;
end
Aree
Raggio
Area
7
153.94
9
254.47
5
78.54
16
804.25
2
12.57
Condizione di
uscita: quando il
cursore non
trova più tuple
Ciclo FOR
Come negli altri linguaggi di programmazione il ciclo
FOR serve a ripetere un certo numero di istruzioni un
numero preciso di volte. La variabile contatore viene
incrementata automaticamente e la condizione di
uscita è data contestualmente all’istruzione di ciclo
Sintassi:
For <Variabile> IN <estremo1>..<estremo2>
LOOP
<istruzioni>
END LOOP;
Ciclo FOR, Esempio
DECLARE
pi constant number(9,7) :=3.1415927;
raggio integer (5);
area number (14,2);
BEGIN
FOR raggio IN 1..7
LOOP
area := pi*power(raggio,2);
Insert into Aree values (raggio, Area);
END LOOP;
end
Aree
Raggio
Area
1
3.14
2
12.57
3
28.27
4
50.27
5
78.54
6
113.1
7
153.94
Ciclo for a cursore
In un ciclo for a cursore i risultati di una query
servono per stabilire in modo dinamico il numero di
esecuzioni del ciclo. Il ciclo verrà eseguito un numero
di volte uguale al numero di righe del cursore.
In questi cicli l’apertura,la trasmissione e la chiusura
dei cursori sono eseguite automaticamente. Non
devono quindi essere specificate dalle corrispondenti
istruzioni
Sintassi:
For <variabile_cursore> in <nome_cursore>
Cicli FOR a cursore, esempio
DECLARE
pi constant number(9,7) :=3.1415927;
area number (14,2);
Viene svolto per
Cursor rag_cursore is
ogni riga della
Select * from Valori_raggio;
tabella
val_rag rag_cursore%Rowtype;
BEGIN
For val_rag in rag_cursore
LOOP
Aree
area := pi*power(val_rag.raggio,2);
Raggio Area
Insert into Aree values (val_rag.raggio, Area);
END LOOP;
7
153.94
end
Nota la mancanza
9
254.47
delle istruzioni Open,
5
78.54
Fetch e Close, che
16
804.25
sono sottointese
2
12.57
Cicli WHILE
In un ciclo While le istruzioni del ciclo vengono svolte
fintantochè una certa condizione è verificata.
Sintassi:
WHILE <condizione>
LOOP
<istruzioni>
END LOOP
Ciclo WHILE, Esempio
DECLARE
pi constant number(9,7) :=3.1415927;
raggio integer (5);
area number (14,2);
BEGIN
raggio :=3;
WHILE raggio<=10
LOOP
area := pi*power(raggio,2);
Insert into Aree values (raggio, Area);
raggio:=raggio+1;
END LOOP;
end
Esercizio
Creare una tabella Prezzi (articolo, p_netto, p_lordo)
contenente il nome degli articoli, seguiti dal loro
prezzo netto e dal prezzo lordo, calcolato mediante un
blocco PL/SQL.
Esercizio (ciclo for)
Create table Prezzi
(articolo articoli.varchar2(50), p_lordo number(6,2), p_netto
number(6,2))
DECLARE
l articoli.art_prezzo%Type;
Cursor prezzo_cursore is
Select art_descrizione, art_prezzo, art_IVA from Articoli;
val_art prezzo_cursore%Rowtype;
BEGIN
For val_art in prezzo_cursore
LOOP
l := val_art.art_prezzo*(1+val_art.art_IVA/100);
Insert into Prezzi values (val_art.art_descrizione,
val_art.art_prezzo, l);
END LOOP;
end
Esercizio (ciclo semplice)
DECLARE
l articoli.art_prezzo%Type;
Cursor prezzo_cursore is
Select art_descrizione, art_prezzo, art_IVA from Articoli;
val_art prezzo_cursore%Rowtype;
BEGIN
Operazioni necessarie
Open prezzo_cursore;
se si usa il ciclo semplice
LOOP
FETCH val_art prezzo_cursore;
Exit when prezzo_cursore%NOTFOUND;
l := val_art.art_prezzo*(1+val_art.art_IVA/100);
Insert into Prezzi values (val_art.art_descrizione,
val_art.art_prezzo, l);
END LOOP;
Close prezzo_cursore;
end
Istruzioni condizionali
In PL/SQL è possibile utilizzare i comandi if, elsif, else,
per controllare il flusso delle istruzioni.
Sintassi:
IF <cond_1>
THEN <istruzioni_1>
ELSIF <cond_2>
THEN <istruzioni_2>
…
ELSIF <cond_n>
THEN <istruzioni_n>
ELSE <istruzioni_(n+1)>
END IF;
Istruzioni condizionali,
esempio
DECLARE
pi constant number(9,7) :=3.1415927;
area number (14,2);
Cursor rag_cursore is
Select * from Valori_raggio;
val_rag rag_cursore%Rowtype;
BEGIN
OPEN rag_cursore;
FETCH rag_cursore into val_rag;
area := pi*power(val_rag.raggio,2);
if area>30 then
Insert into Aree values (val_rag.raggio, Area);
end if;
CLOSE rag_cursore;
end
Esercizio
Definire la tabella Armadi e la tabella Tavoli
Inserire rispettivamente nella tabella Armadi e nella
tabella Tavoli denominazione e prezzo degli armadi
(risp. tavoli) presenti nella tabella articoli
Create table Tavoli
(art_descrizione varchar(30) not null,
art_prezzo number (8,2))
Create table Armadi
(art_descrizione varchar(30) not null,
art_prezzo number (8,2))
Declare
Cursor articoli_cur is
Select art_descrizione, art_prezzo From Articoli;
X articoli_cur%rowtype;
Begin
for X in articoli_cur
LOOP
if X.art_descrizione like ‘%Tavolo%’ then
insert into Tavoli values(X.art_descrizione, X.art_prezzo)
elsif X.art_descrizione like ‘%Armadio%’ then
insert into Armadi values(X.art_descrizione, X-art_prezzo)
endif
END LOOP
Gestione delle eccezioni
Quando nell’esecuzione di un blocco PL/SQL si trovano
delle eccezioni (errori), il controllo passa (se esiste) alla
sezione in cui si gestiscono le eccezioni, che è collocata
alla fine dei comandi eseguibili, dopo la parola chiave
exception.
All’interno di questa sezione sono contenute una o più
clausole del tipo
When <nome_eccezione> then <soluzione>
che permettono di dire quale soluzione dare nel caso in
cui un’eccezione si verifica.
Gestione delle eccezioni
Se nell’esecuzione delle istruzioni viene sollevata
un’eccezione, il flusso dei comandi passa alla sezione
exception e verifica se l’errore verificatosi è compreso
tra quelli previsti in questa sezione.
Se si, viene applicata la soluzione suggerita e il
programma termina.
Se percaso si verifica un errore e l’eccezione non è
contemplata nella sezione exception, potrebbero esserci
degli effetti indesiderati, come per esempio la
cancellazione di tutti i dati che sono stati inseriti fino a
quel punto dal programma in una tabella.
Esempio
DECLARE
pi constant number(9,7) :=3.1415927;
raggio integer (5);
area number (14,2);
X number (14,2);
BEGIN
raggio :=3;
LOOP
x:=1/(raggio-4);
area := pi*power(raggio,2);
Insert into Aree values (raggio, Area);
raggio:=raggio+1;
EXIT WHEN area>100;
END LOOP;
end
Quando il programma
entra nel loop per la
prima volta, tutte le
istruzioni vengono
eseguite, mentre la
seconda volta raggio=4
fa si che il valore
assegnato alla
variabile x è
indefinito. Allora il
programma termina e
si esegue il rollback (i
valori che erano stati
inseriti nella tabella
aree vengono
cancellati)
Soluzione
Per evitare il rollback (annullamento delle operazioni di
inserimento nella tabella già effettuate), la soluzione è
proprio la gestione delle eccezioni. Nell’esempio
precedente il caso in cui il denominatore è uguale a zero
viene esaminato a parte.
Questo non impedisce che il programma termini, perché
una volta che ci si trova nella sezione exception non è
possibile tornare al codice del programma, però questo
evita che i valori già inseriti vengano cancellati.
La sezione che gestisce le eccezioni è definita alla fine del
programma , preceduta dalla parola chiave exception.
Precede la parola end
Esempio
DECLARE
pi constant number(9,7) :=3.1415927;
raggio integer (5);
area number (14,2);
X number (14,2);
BEGIN
raggio :=3;
LOOP
x:=1/(raggio-4)
area := pi*power(raggio,2);
Insert into Aree values (raggio, Area);
raggio:=raggio+1;
EXIT WHEN area>100;
END LOOP;
Exception
when ZERO_DIVIDE then insert into aree values (0,0)
end
Eccezioni predefinite
Molte eccezioni sono predefinite in Oracle come per
esempio ZERO_DIVIDE (il programma cerca di
dividere un numero per 0), CASE_NOT FOUND
(nessuno dei casi del CASE è verificato e non esiste
l’else) etc.
Un elenco si trova nel manuale oracle a cap.42, Pagina
997.
E’ anche possibile da parte del programmatore, definire
delle eccezioni personalizzate mediante il comando
RAISE.
Personalizzare le condizioni di
errore
I numeri e i messaggi di errore visualizzati dall’utente
sono impostati mediante la procedura
RAISE_APPLICATION_ERROR
Che può essere chiamata all’interno di ogni segmento
PL/SQL.
Raise_Application_Error è una procedura che prende in
input due parametri
Il numero dell’errore (che deve essere un numero
compreso tra -20001 e -20999)
Il messaggio di errore da visualizzare
Esempio
Considerata la tabella valori raggio, contenenti i
possibili raggi di cerchio, si inseriscano il valore del
raggio e la corrispondente area nella tabella Aree, e si
sollevi un eccezione che dà un messaggio di errore
quando si trova un raggio fuori dal range consentito
[0, 20]
DECLARE
pi constant number(9,7) :=3.1415927;
area number (14,2);
Cursor rag_cursore is
Select * from Valori_raggio;
val_rag rag_cursore%Rowtype;
Out_range_error Exception ;
Dichiarazione
dell’eccezione
BEGIN
For val_rag in rag_cursore
LOOP
if val_rag.cursore>20 or val_rag.cursore<0 then
RAISE Out_range_error
area := pi*power(val_rag.raggio,2);
Insert into Aree values (val_rag.raggio, Area);
END LOOP;
Solleva l’errore.
Rimanda alla
sezione Exception.
Definizione
dell’eccezione
EXCEPTION
When Out_range_error then Raise_application_error
(-20001, ‘Il valore del raggio è fuori dal range concesso’)
End