PL/SQL
Uso di un linguaggio procedurale per un database
Roberto Basili
Corso di Basi Di Dati 1
a.a. 2000-2001
Abstract
Questa breve dispensa descrive piu’ etesamente i contenuti della esercitazione del
20.11.2000. Gli esempi proposti si propongono di elucidare l’uso dell’ambiente di
programmazione di una componente procedurale di interazione con un database. In
particolare PL/SQL, il linguaggio procedurale reso disponibile nell’ambiente Oracle
8, verra utilizzato come strumento esemplificativo. Questo documento rappresenta
la prima versione di questa dispensa. Si rimanda inoltre alle dispense di riferimento
consigliate nel corso.
Si prega di notificare all’autore eventuail errori o inconsistenze che molto probabilmente abitano le seguenti pagine ...
1
Introduzione
Nella costruzione di un database spesso l’attenzione e’ concentrata sui dettagli concettuali e logici dello schema al fine di ottimizzare lo sfruttamento delle potenzialita’ deli
strumenti standard di manipolazione e interrogazione dei dati. E’ spesso vero pero’ che
SQL da solo non garantisce le caratteristiche di proceduralita’ necessarie per la implementazione di operazione complesse sui dati. Un esempio e’ la generazione di raggruppamenti
dei dati sulla base di uguaglianze per intervalli (ad es. ”Generare rispetto al campo intero
A, tutti le k classi di equivalenza che riuniscono i gruppi di record secondo la condizione:
tabella.A mod k”).
Per implementare tali procedure PL/SQL fornisce una sintassi per la programmazione
strutturata, che consente, oltre alle tipiche operazioni di un linguaggio di programmazione, la interazione con un database mediante costrutti SQL e l’utilizzo di astrazioni
funzionali in procedure/funzioni e packages.
PL/SQL rappresenta un linguaggio di programmazione interpretato per la macchina
astratta definita dal sottostante interprete SQLplus.
Dato un programma PL/SQL, memorizzato in un file PLSQLprog.sql, la sua attivazione e’ possibile mediante il seguente comando SQLplus:
SQLplusPrompt> start PLSQLprog
2
Struttura di Base di un blocco PL/SQL
Un blocco PL/SQL e’ caratterizzato dalla seguente struttura fondamentale.
[Header]
[DECLARE
<Costanti>
1
<Variabili>
<Cursori>
<Gestori di eccezioni/errori> ]
BEGIN
<Istruzioni PL/SQL>
[ EXCEPTIONS
<Gestione eccezioni/errori> ]
END
/
La struttura quindi contiene sezioni opzionali (Header, Declare) e una notazione
standard per la definizione del blocco funzionale (BEGIN ... END). Costanti e variabli
sono analoghe ai correspondenti usi di essi nella programmazione strutturata. I Cursori
sono utilizzati per le interazioni con il database e fornisco una astrazione funzionale delle
tabelle ottenute mediante interrogazioni SQL al database. Le loro caratteristiche verrano
descritte nella sezione 3.2.
E’ da osservare che le regole di visibilita’ degli identificatori seguono analogamente le
regole usate in un ambiente di programmazione a scoping statico (ad es. il Pascal).
3
Sezione delle dichiarazioni
3.1
Dichiarazione delle Variabili
La sintassi generale per la dichiarazione delle variabili e’ la seguente:
<nome variabile> <tipo variabile> [:= valore legale];
dove la dichiarazione del tipo puo’ essere opzionalmente seguita da una assegnazione
di inizializzazione. Esempi di dichiarazioni di variabili sono le seguenti:
DECLARE
counter number(9) := 0;
Ricavo number (15,3);
ProdID Prodotto.PId%TYPE;
De Prodotto.Descrizione%TYPE;
Forn Prodotto.Fornitore%TYPE;
Cst Prodotto.Costo%TYPE;
Prd Prodotto%ROWTYPE;
La variabile counter viene utilizzata come un numero a 9 cifre (intero) ed e’ inzializzata al valore 0. Ricavo invece e’ un numero di 15 cifre significative, di cui 3 costituiscono la parte decimale. L’arrotondamento viene in genere garantito per le operazioni
di assegnazione, manipolazione e stampa a video.
Le definizioni di ProdID, De, Forn, Cst seguono una sintassi piu’ ricca: il loro
tipo infatti viene derivato dal tipo dei campi corrispondenti, PId, Descrizione, Fornitore
e Costo, della relazione/tabella Prodotto. Se la seguente e’ la istruzione SQL per la
costruzione della tabella Prodotto:
2
CREATE TABLE Prodotto(
PId
integer,
Descrizione CHAR(20),
Fornitore CHAR(20),
Costo number(15,3)
PRIMARY KEY (PId) );
allora le due sequenti dichiarazioni sono equivalenti:
Cst
Prodotto.Costo%TYPE;
Cst
number(15,3);
E’ da osservare pero’ che la prima rappresenta una forma piu’ flessibile perche’ la correttezza del codice PL/SQL risultante viene preservata anche a fronte di modifiche del
database sottostante. Cambiamenti della definizione della tabella Prodotto infatti possono rendere illegale la seconda dichiarazione ma non la prima delle due: essa continua
ad essere valida poiche’ la sintassi Table name.Field%TYPE viene ricalcolata alla attivazione del blocco , preservando quindi la coerenza tra il codice PL/SQL ed il database
sottostante. Lo stesso vale naturalemente per la dichiarazione
Prd Prodotto%ROWTYPE;
che rappresenta una variabile la cui struttura interna e’ identica ad una tupla/record
nella relazione/tabella prodotto.
3.2
Dichiarazione dei cursori
La dichiarazione di un cursore e’ la seguente:
CURSOR <cursor_name> IS
<SQL_statement>;
<cursor name> e’ l’identificatore del cursore e <SQL statement> e’ l’operazione che
sui dati si intende effettuare. Esempi di dichiarazione di cursori sono:
CURSOR Tabella1_Cursor IS
SELECT e, f
FROM Tabella1
WHERE e < f;
CURSOR CURSOR_NAME is
select *
from Prodotto
where Costo >= 50.000;
Per usi piu’ complessi (e quindi dichiarazioni) si rimanda alla sezione 5.2.
3
4
Blocco di programma
4.1
Assegnazioni ed espressioni
Assegnazioni ed espressioni su valori o variabili dichiarate nel blocco di definizione
seguono regole sintattiche analoghe a quelle di un linguaggio di programmazione strutturato.
Esempi sono:
(1)
(2)
(3)
(4)
(n1)
(n2)
counter := 0;
Ricavo := Cst * 1.25;
counter := counter + 1;
Diff_Costo := abs(Costo1 - Costo2)/Costo1;
...
Cst := Prd.Costo;
De := Prd.Descrizione;
L’uso di costanti, operazioni aritmetiche e funzioni e’ esemplificato dagli esempi (1)(4). I casi (n1) ed (n2) dimostrano l’uso di assegnazioni a partire da campi di strutture
complesse: Prd e’ dichiarato come
Prd Prodotto%ROWTYPE;
e Cst, De come
Cst Prodotto.Costo%TYPE;
DE Prodotto.Descrizione%TYPE;
4
5
Uso dei cursori
5.1
Cursori Semplici: Schemi di Iterazione
Dato un cursore l’utilizzo di esso e’ esemplificato dalla seguente sequenza di istruzioni:
DECLARE
...
CURSOR cursor_name is
SELECT *
FROM Prodotto
WHERE Costo >= 50.000;
...
BEGIN
...
open cursor_name; /* Apertura del cursore
Viene calcolata la query SQL e viene allocata
la memoria intermedia per le tuple in uscita */
counter := 0;
loop
fetch cursor_name into Prd;
/* Viene caricata una tuple in Prd */
exit when cname%NOTFOUND;
/* condizione di uscita dal ciclo */
ProdId := Prd.PId;
/* uso dei valori recuperati dalla tabella prodotto */
Cst := Prd.Costo;
De := Prd.Descrizione;
Ricavo := Cst * 1.25; /* aggornamento di altre variabili
nello spazio di indirizzamento
del blocco */
counter := counter + 1; /* contatore delle iterazioni, i.e. tuple */
end loop;
close cursor_name;
/* deattivazione del cursore */
...
END
/
5.2
Cursori Parametrici
Un cursore come cursor name della sezione precedente puo’ dipendere da alcune costanti
(nell’esempio il valore 50.000). Per rendere piu’ generale la nozione di un cursore ad esso
possono essere associati uno o piu’ parametri rispetto ai quali attivazioni diverse possono
operare su valori diversi. La astrazione ottenuta e’ simile quindi a quela di una astrazione
funzionale come la procedura in un ambiente di progammazione strutturata. Tali cursori
detti parametrici hanno una dichiarazione piu’ complessa, che include il nome, il tipo ed
il numero di parametri. Ad es.
cursor Parametric_Cursor( Soglia Prodotto.Costo%TYPE ) is
select *
from Prodotto
where Costo >= Soglia;
In questo caso la condizione WHERE nella query SQL puo’ assumere configurazioni
diverse a fronte di diversi valori del parametro Soglia. Invocazioni diverse (cioe’ con
risultati diversi sulla stessa istanza del database) dello stesso cursore sono quindi possibili:
open Parametric_Cursor( 30.000 );
5
....
open Parametric_Cursor( 50.000 );
6
6
Output in PL/SQL
6.1
Uso della Libreria DBMS Output
La stampa a video dei risultati dei programmi PL/SQL e’ utile soprattutto in fase di
debugging. A tale proposito e’ resa disponibile la libreria DBMS OUTPUT che in genere
risiede in
$ORACLE_HOME\Rdbms\Admin\
. La libreria rende disponibili alcune funzioni standard di uscita, tra cui:
• PUT( buffer char() ), per la stampa del buffer a video
• PUT LINE( buffer char() ), per la stampa del buffer a video con automatico
ritorno a capo
• NEW LINE, per la chiusura con un a capo di un buffer di stampa prodotto da precedenti PUT
Per utilizzare il video come standard output e’ necesario configurare l’ambiente
SQLplus mediante la istruzione:
SQLplus> set serveroutput on
che consente la visualizzazione in output. Un analogo effetto ha la seguente istruzione
PL/SQL:
...
DBMS OUTPUT.enable;
...
L’uso della libreria DBMS OUTPUT e’ esemplificato dal codice PL/SQL di seguito:
BEGIN
loop
fetch cname into Prd;
exit when cname%NOTFOUND;
ProdId:=Prd.PId;
Cst:=Prd.Costo;
De:=Prd.Descrizione;
Ricavo := Cst * 1.25;
DBMS_OUTPUT.PUT_LINE(’
Il Prodotto: ’ || De
|| ’ ha un prezzo di ’ || to_char(Ricavo) );
counter := counter + 1;
end loop;
close cname;
DBMS_OUTPUT.PUT_LINE(’
Sono stati analizzati ’
|| to_char(counter) || ’ prodotti ’ );
END;
/
Da osservare l’accodamento di piu’ stringhe nel buffer grazie all’operatore || e l’uso
della funzione standard to char() per la trasformazione di quantita’ numeriche in stringhe.
Poiche’ le funzioni in DBMS OUTPUT assumono un default di 120 caratteri nel
buffer usato per l’ouput a video, stringhe piu’ grandi producono messaggi di errore. Per
estendere il buffer e’ possibile usare il comando DBMS OUPUT.ENABLE con un parametro
in piu’ che defiisca la taglia del buffer in byte. La seguente istruzione PL/SQL
7
DBMS OUPUT.ENABLE(20000)
determina un buffer di 20k. Un programma completo per il calcolo del prezzo di prodotti
e’ fornito com eesemplificazione di seguito.
/* Calcolo del Prezzo
Nov 2000
Argomenti:
- Uso di cursori
- Uso delle funzioni
- Uso delle librerie di stampa
*********************************** */
DECLARE
ProdID Prodotto.PId%TYPE;
De Prodotto.Descrizione%TYPE;
Forn Prodotto.Fornitore%TYPE;
Cst Prodotto.Costo%TYPE;
Prezzo number (38);
counter number(9);
cursor cname( Thresh Prodotto.Costo%TYPE ) is
select *
from Prodotto
where Costo >= Thresh;
Prd cname%ROWTYPE;
BEGIN
commit;
counter := 0;
for Prd in cname(60000)
loop
ProdId := Prd.PId;
Cst := Prd.Costo;
De := Prd.Descrizione;
Prezzo := Cst * 1.25;
DBMS_OUTPUT.PUT_LINE(’
Il Prodotto: ’ || trim(De) ||
’ ha un prezzo di ’ || to_char(Prezzo) );
counter := counter + 1;
end loop;
DBMS_OUTPUT.PUT_LINE(’
Sono stati analizzati
END;
/
8
’ ||
to_char(counter) || ’ prodotti ’ );
7
FILE I/O in PL/SQLi
7.1
Uso della Libreria utl file
La libreria utl file consente la lettura e la scrittura su file. Per una trattazione completa
si rimanda alle dispense consigliate del corso. Alcune osservazioni ed esempi sono state
inseriti in questa sezione.
La prima osservazione e’ che qualsiasi file si intenda manipolare e’ necessario:
• ottenere i diritti di accesso dal database corrente, cioe’ dichiarare le directory accessibili per operazioni di I/O nella cofigurazione del DB stesso.
• descrivere tali file in termini di una directory del file system locale e di un nome
file. tali informazioni sono in genere disgiunte per i motivi di seguito illustrati
La prima informzione e’ parte della configurazione del database ed e’ determinata
all’atto della sua attivazione. Tale informazione quindi e’ parte dei paranetri di inizializzazione che risiedono i genere in
$ORACLE_HOME\ORA_DATA\DATABASE_NAME\init.ora
In tale file quindi vanno inserite le righe di dichiarazione della variabile di ambiente
utl file dir per ogni directory che si intende utilizzare per operazioni di I/O. Tali
dichiarazioni non supportano discese ricorsive attraverso il corrente file system, ma debbono eventualmente essere enumerate. Un esempio di dichiarazione (in ambiente Windows) e’ il seguente.
FILE init.ora
utl_file_dir = c:\Database\datiUtente
utl_file_dir = c:\Database\datiUtente\datiScambio
...
Per utilizzare un file e’ necessario completare le operazioni di apertura e chiusura
del file stesso analoghe alle funzionalita’ disponibili in un ambiente di programmazione
strutturata. Le operazioni di apertura e chiusura del database sono garantite dalle funzioni
OutFileHandler := utl_file.fopen(OutDirectoryName, OutFileName,OpenMode);
e
utl_file.fclose(OutFileHandler);
rispettivamente. OpenMode assume rispettivamente i valori di ’W’ (write), ’R’ (read) ed
’A’ Append. Le funzioni sono sono illustrate dal seguente frammento di programma.
...
Outdir char(200);
Outfilename char(25);
Outf UTL_FILE.FILE_TYPE;
Outdir := ’c:\Database\datiScambio’;
Outfilename := ’Mio.dat’;
DBMS_OUTPUT.PUT_LINE (’Trying to open < || trim(Outdir) ||
’/ || trim(Outfilename) );
Outf := utl_file.fopen(Outdir, Outfilename,’W’);
.... /uso del file */
utl_file.fclose(Outf);
...
9
Un esempio completo con l’uso delle funzioni di scruittura su file e’ riportato di
seguito.
/* Stampa dei prezzi su FILE
Argomento: Uso dei file */
DECLARE
Outdir char(200);
Outfilename char(25);
Outf UTL_FILE.FILE_TYPE;
De Prodotto.Descrizione%TYPE;
Forn Prodotto.Fornitore%TYPE;
Cst Prodotto.Costo%TYPE;
Ricavo number (12);
counter number(9) := 0;
cursor cname( Thresh Prodotto.Costo%TYPE ) is
select *
from Prodotto
where Costo >= Thresh;
Prd cname%ROWTYPE;
BEGIN
commit;
open cname(30000);
DBMS_OUTPUT.enable(200000);
Outdir := ’C:\Documenti\Robertob\Didattica\BasidiDati\Esempio’;
Outfilename := ’Mio.dat’;
DBMS_OUTPUT.PUT_LINE (’Trying to open <’ || trim(Outdir) ||
’\’ || Outfilename );
Outf := utl_file.fopen(Outdir, Outfilename,’W’);
loop
fetch cname into Prd;
exit when cname%NOTFOUND;
Cst := Prd.Costo;
De := Prd.Descrizione;
Ricavo := Cst * 1.25;
DBMS_OUTPUT.PUT_LINE(’
Il Prodotto: ’ || trim(De) ||
’ ha un prezzo di ’ || to_char(Ricavo) );
utl_file.putf( Outf, ’Il prodotto <%s> ha un prezzo di Lt. %s\n’,
trim(De),
to_char(Ricavo) );
utl_file.fflush(Outf);
counter := counter + 1;
end loop;
close cname;
DBMS_OUTPUT.PUT_LINE(’
Sono stati analizzati ’ ||
to_char(counter) || ’ prodotti ’ );
10
utl_file.fclose(Outf);
EXCEPTION
WHEN utl_file.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE (’Path <<’ || trim(Outdir) || ’>> Wrong’ );
WHEN utl_file.WRITE_ERROR THEN
DBMS_OUTPUT.PUT_LINE(’Write error !! ’);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (’File <<’ || Outfilename || ’>> not Found’ );
END;
/
11
8
Uso complesso dei cursori
Il seguente esempio illustra l’uso annidato di cursori. Il programma si propone di raggruppare diversi prodotti in fasce di prezzo. La fascia di prezzo e’ centrata sul prezzo X
di un prodotto contiene tutti i prodotti che costano X piu’ o meno il 2O% di X.
/* Raggruppamenti per Fascia di Prezzo
Nov 2000
Argomento: Uso di cursori annidati
*********************************** */
DECLARE
emp_found boolean := FALSE;
ProdID Prodotto.PId%TYPE;
De Prodotto.Descrizione%TYPE;
Forn Prodotto.Fornitore%TYPE;
Cst Prodotto.Costo%TYPE;
Ricavo number (38);
counter number(9);
counter2 number(9);
cursor cname( Thresh Prodotto.Costo%TYPE ) is
select *
from Prodotto
where Costo >= Thresh
ORDER BY Costo;
Prd cname%ROWTYPE;
cursor pippo is
select *
from Prodotto;
Prd1 cname%ROWTYPE;
Prd2 pippo%ROWTYPE;
Cost_Diff number(10,2);
BEGIN
commit;
DBMS_OUTPUT.enable(200000);
counter := 0;
for Prd in cname(30000)
loop
Cst := Prd.Costo;
if Cst*1.25 > 50000 then
De := Prd.Descrizione;
Ricavo := Cst * 1.25;
ProdId := Prd.PId;
DBMS_OUTPUT.PUT_LINE(’Analizzo il Prodotto: ’ || trim(De) || ’ (’ ||
trim(Prd.Fornitore) || ’) Ricavo: ’ || Ricavo );
DBMS_OUTPUT.NEW_LINE;
counter2 := 0;
for Prd2 in pippo
loop
Cost_Diff := abs(Prd2.Costo - Cst)/Prd2.Costo;
if Cost_Diff <= 0.20 then
12
DBMS_OUTPUT.PUT_LINE(’
Simile a: ’ || trim(Prd2.Descrizione) ||
’ (’ || trim(Prd2.Fornitore) || ’)’);
counter2 := counter2 + 1;
end if;
end loop;
DBMS_OUTPUT.PUT_LINE(’Raggruppati <’ || to_char(counter2) || ’> prodotti ’);
DBMS_OUTPUT.PUT_LINE(’------------------------------’);
end if;
counter := counter + 1;
end loop;
DBMS_OUTPUT.PUT_LINE(’
Sono stati analizzati
END;
/
13
’ || to_char(counter) || ’ prodotti ’);
