Introduzione
Struttura___
Dichiarazioni
Strutture di controllo___
Il controllo condizionale_
Il controllo iterativo____
Il controllo sequenziale_
Cursori___
La gestione del cursore____
Gli attributi del cursore____
Il cursore implicito___
Gestione degli errori_____
Modularita’
La procedura
La funzione_
Il package__
Il database trigger_____
Tipi di dato
Funzioni___
Tabelle PL/SQL___
Il
PL/SQL e’ un’ implementazione procedurale (Procedural Language) del linguaggio
SQL per lo sviluppo di applicazioni che usano l’RDBMS Oracle.
Il
PL/SQL unisce la potenza del linguaggio SQL nel manipolare i dati sul database
e la potenza dei linguaggi procedurali nel processare i dati dell’
applicazione.
PL/SQL
invia all’RDBMS un unico blocco comprensivo di diverse istruzioni SQL,
riducendo cosi’ il traffico di rete verso l’ RDBMS.
I
blocchi PL/SQL possono inoltre essere compilati e memorizzarti sul database: in
tal modo possono essere eseguiti da tutti gli utenti connessi al database senza
piu’ bisogno di essere compilati.
PL/SQL
e’ usato all’interno di tools Oracle quali SQL*Forms e SQL*ReportWriter o per
creare i Database Trigger e le Procedure.
Il
PL/SQL e’ un linguaggio strutturato a blocchi.
Tutti
i programmi PL/SQL (procedure, funzioni, blocchi anonimi) sono composti di
blocchi logici che possono contenere a loro volta dei sotto-blocchi innestati.
Il
blocco permette di raggruppare le dichiarazioni e i comandi che le usano in
un’unica unita’ logica e di dividere il programma in sottoinsiemi.
Le
dichiarazioni sono locali al blocco e cessano di esistere al di fuori di esso.
Il
blocco PL/SQL e’ composto di tre parti: una dichiarativa, una esecutiva e una
di gestione delle exception (in PL/SQL le condizioni d’errore sono chiamate
exception).
Solo
la parte esecutiva e’ obbligatoria.
[DECLARE
declarations]
BEGIN
statements
[EXCEPTION
handlers]
END;
Gli
oggetti dichiarati nella prima parte sono manipolati nella seconda.
Le
exception verificatesi nella seconda parte sono gestite nella terza.
Si
possono innestare dei sotto-blocchi (sub-block) nella parte esecutiva del
blocco principale (enclosing-block).
Nella
prima parte del blocco devono essere dichiarate:
· le variabili e
le costanti
· i record
· i cursori
espliciti
· le exception definite dall’utente
Le
variabili devono essere dichiarate nel blocco dove saranno usate.
I
tipi di dato delle variabili possono essere quelli esistenti nell’SQL (NUMBER,
VARCHAR2, DATE) o quelli propri del PL/SQL (BOOLEAN, BINARY_INTEGER).
part_no NUMBER(4);
in_stock BOOLEAN;
Si
possono inizializzare le variabili in due modi:
· con l’operatore
di assegnazione (:=)
valid
BOOLEAN := FALSE;
· con la
parola-chiave DEFAULT
valid BOOLEAN
DEFAULT FALSE;
Si
possono assegnare valori alle variabili in due modi:
· con l’operatore
di assegnazione (:=)
bonus := current_salary * 0.10;
valid := TRUE;
valid
:= (count > 500);
· con i comandi
di SELECT o di FETCH
SELECT sal * 0.10 INTO bonus FROM emp WHERE empno =
emp_id;
La
dichiarazione di una costante e’ composta dal nome, dalla parola-chiave
CONSTANT, dal tipo di dato del
valore che non potra’ piu’ essere modificato.
minimum CONSTANT
NUMBER := 10;
L’
attributo %TYPE assegna a una variabile il tipo di dato di una colonna del
database. In tal modo, se cambiera’ il tipo di dato della colonna, cambiera’
automaticamente quello della variabile.
my_title books.title%TYPE;
Il
cursore e’ una struttura definita come il risultato di un comando di SELECT.
CURSOR c1 IS
SELECT ename, sal, NVL(comm,0) comm FROM emp;
Il
record e’ una struttura composta da un insieme di campi.
L’
attributo %ROWTYPE assegna al record i tipi di dato di una tabella del database
o di un cursore.
emp_rec emp%ROWTYPE;
CURSOR c2 IS
SELECT deptno, dname, loc FROM dept;
dept_rec c2%ROWTYPE;
Le
exception definite dall’ utente devono essere dichiarate prima di essere
attivate.
my_exception EXCEPTION;
Le
strutture di controllo sono le piu’ importanti estensioni del PL/SQL rispetto
all’SQL e permettono di processare i dati tramite controlli condizionali,
iterativi e sequenziali.
Il
controllo condizionale permette di compiere operazioni alternative al
verificarsi o meno di una condizione.
Si
puo’ testare l’esistenza di certi dati sul database e, di conseguenza, decidere
se inserire o modificare cancellare altri dati.
Il
controllo condizionale e’ eseguito dal comando IF nelle sue tre forme: IF-THEN,
IF-THEN-ELSE e IF-THEN-ELSIF.
· IF-THEN e’ la
forma piu’ semplice di comando IF e associa una condizione a una serie di
comandi
IF condition
THEN
sequence_of_statements;
END IF;
Esempio:
IF sales >=
quota THEN
...
UPDATE payroll ... ;
END IF;
· IF-THEN-ELSE
aggiunge al comando IF una seconda serie di comandi da eseguire in alternativa
al primo
IF condition
THEN
sequence_of_statements1;
ELSE
sequence_of_statements2;
END IF;
Esempio:
IF acct_balance
>= debit_amt THEN
UPDATE accounts ... ;
ELSE
INSERT INTO temp ... ;
END IF;
· IF-THEN-ELSIF
aggiunge al comando IF una seconda condizione
IF condition1
THEN
sequence_of_statements1;
ELSIF
condition2 THEN
sequence_of_statements2;
ELSE
sequence_of_statements3;
END IF;
Esempio:
IF sales >
50000 THEN
bonus := 1500;
ELSIF sales
> 35000 THEN
bonus := 500;
ELSE
bonus := 100;
END IF;
Il
controllo iterativo permette di eseguire una serie di comandi per un certo
numero di volte o finche’ non si verifica una particolare condizione.
Il
controllo iterativo e’ eseguito dai comandi LOOP, FOR-LOOP e WHILE-LOOP.
· LOOP e’ il piu’
semplice controllo iterativo e ripete i comandi continuamente, finche’ non
interviene qualcosa a interrompere il ciclo (i comandi EXIT o EXIT-WHEN).
Un LOOP
puo’ essere identificato con una label e referenziato da un comando.
LOOP
sequence_of_statements;
END LOOP;
<<outer>>
LOOP
...
LOOP
...
EXIT outer WHEN ... ;
END LOOP;
END LOOP outer;
· FOR-LOOP
permette di specificare un range di numeri interi e ripete i comandi per ogni
numero del range. Il range puo’ essere definito dinamicamente durante l’
esecuzione attraverso una variabile.
Un
FOR-LOOP puo’ essere identificato con una label e referenziato da un comando.
FOR counter IN
[REVERSE] lower..higher LOOP
sequence_of_statements;
END LOOP;
Esempio:
FOR ctr IN
1..10 LOOP
INSERT INTO ... VALUES
(ctr, ...);
...
END LOOP;
· WHILE-LOOP
associa una condizione all’ iterazione e ogni volta, prima di eseguire i
comandi, valuta la veridicita’ della condizione.
WHILE condition
LOOP
sequence_of_statements;
END LOOP;
Esempio:
WHILE salary
< 4000 LOOP
SELECT sal, mgr INTO salary, mgr_num
FROM emp
WHERE empno = mgr_num;
END LOOP;
done := FALSE;
WHILE NOT done
LOOP
...
END LOOP;
· EXIT interrompe
incondizionatamente il loop e passa il controllo al comando successivo. Non
puo’ essere usato per interrompere un blocco PL/SQL.
IF ... THEN ...
EXIT;
END IF;
Esempio:
LOOP
FETCH c1 INTO emp_rec;
total := total +
emp_rec.sal;
IF total > 25000 THEN
EXIT;
END IF;
END LOOP;
· EXIT-WHEN
interrompe condizionatamente il loop e passa il controllo al comando
successivo.
EXIT WHEN ...;
Esempio:
LOOP
FETCH my_cursor INTO
my_rec;
EXIT WHEN
my_cursor%NOTFOUND;
...
END LOOP;
Il
controllo sequenziale e’ eseguito dai comandi GOTO e NULL.
· GOTO permette
di spostarsi incondizionatamente ad una label del programma.
GOTO label;
...
<<label>>
...
Esempio:
IF rating >
90 THEN
GOTO calc_raise;
END IF;
...
<<calc_raise>>
...
· NULL e’ un
comando che non fa niente e, proprio per questo, puo’ essere utilizzato in
punti del programma dove e’ obbligatorio mettere almeno un comando oppure per
migliorare la leggibilita’ del programma.
NULL;
Esempio:
BEGIN
...
IF done THEN
GOTO end_prg;
END IF;
...
<<end_prg>>
NULL;
END;
EXCEPTION
WHEN ZERO_DIVIDE THEN
ROLLBACK;
WHEN OTHERS THEN
NULL;
END;
Il cursore e’ un’area di memoria privata che contiene un comando SQL e le informazioni del dizionario dati necessarie alla sua esecuzione.
Esistono
due tipi di cursori: implicito ed esplicito.
PL/SQL
crea implicitamente un cursore per ogni comando DML che esegue.
Per
le query che estraggono piu’ di una riga e’ opportuno dichiarare esplicitamente
un cursore.
DECLARE
CURSOR c1 IS
SELECT empno, ename, job FROM emp WHERE deptno = 20;
...
Il
set di righe estratte si chiama set attivo e il cursore esplicito puo’ puntare,
ad una ad una, tutte le righe del set.
La
riga corrente e’ la riga attualmente puntata dal cursore e su di essa si
possono fare le operazioni.
|
|
Set Attivo |
|
|
|
7369 SMITH |
CLERK |
|
|
7566 JONES |
MANAGER |
|
riga corrente => |
7788 SCOTT |
ANALYST |
|
|
7876 ADAMS |
CLERK |
|
|
7902 FORD |
ANALYST |
Il
comando OPEN esegue la query associata al cursore, identifica il set attivo e
si posiziona sulla prima riga.
OPEN c1;
Il
comando FETCH estrae la riga corrente e si posiziona sulla successiva.
FETCH c1 INTO
emp_rec;
Il
comando CLOSE disabilita il cursore.
CLOSE c1;
In
alcune situazioni si puo’ semplificare il programma usando il comando FOR-LOOP
su un cursore (invece dei comandi OPEN, FETCH e CLOSE). Questo comando dichiara
implicitamente un indice del loop come un record %ROWTYPE con la stessa
struttura del cursore, apre il cursore, iterativamente scarica le righe di
valori del set attivo nei campi del record e chiude il cursore quando tutte le
righe sono state processate.
DECLARE
CURSOR c1 IS
SELECT empno, sal FROM emp;
salary_total NUMBER;
BEGIN
FOR emp_rec IN c1 LOOP
salary_total := salary_total + emp_rec.sal;
END LOOP;
END;
Esistono
quattro attributi del cursore che ci permettono di avere informazioni
sull’esecuzione della query legata al cursore: %NOTFOUND, %FOUND, %ROWCOUNT e
%ISOPEN.
Gli
attributi del cursore si possono usare nei comandi procedurali ma non nei
comandi SQL.
· %NOTFOUND e’
TRUE quando l’ultima fetch non restituisce nessuna riga perche’ il cursore e’
arrivato alla fine del set attivo (se si verifica sulla prima fetch significa
che il set attivo e’ vuoto).
Esempio:
LOOP
FETCH my_cursor INTO
my_rec;
EXIT WHEN
my_cursor%NOTFOUND;
...
END LOOP;
· %FOUND e’
l’opposto logico di %NOTFOUND: quando il cursore viene aperto e’ NULL, quando
viene eseguita una fetch e’ TRUE o FALSE a seconda che la fetch restituisca o
meno una riga.
Esempio:
LOOP
FETCH my_cursor INTO
my_rec;
IF my_cursor%FOUND THEN
INSERT INTO temp VALUES(...);
ELSE
EXIT;
END IF;
END LOOP;
· %ROWCOUNT e’ il
numero di righe restituite finora dalla fetch: quando il cursore viene aperto
e’ zero.
Esempio:
LOOP
FETCH my_cursor INTO
my_rec;
IF my_cursor%ROWCOUNT >
10 THEN
...
END IF;
...
END LOOP;
· %ISOPEN e’ TRUE
quando il cursore e’ aperto.
Esempio:
IF
my_cursor%ISOPEN THEN
...
ELSE
OPEN my_cursor;
END IF;
Per
le query che estraggono una sola riga e’ possibile usare la SELECT INTO che,
come gli altri comandi DML (INSERT, UPDATE e DELETE), usa un cursore implicito.
Il
cursore implicito e’ gestito da Oracle, pero’ esistono tre attributi che ci
permettono di avere informazioni sull’ esecuzione del comando legato al
cursore: SQL%NOTFOUND, SQL%FOUND e SQL%ROWCOUNT.
Anche
questi attributi, come quelli del cursore esplicito, si possono usare nei
comandi procedurali ma non nei comandi SQL.
Esempio:
UPDATE emp SET
sal = emp_rec.new_sal
WHERE empno =
emp_rec.empno;
IF SQL%NOTFOUND
THEN
INSERT INTO emp VALUES
(...);
END IF;
SELECT sal INTO
my_sal FROM emp WHERE empno = my_empno;
-- se non
trova record attiva l’exception NO_DATA_FOUND
IF SQL%NOTFOUND
THEN
... -- questa azione non e’ mai eseguita
END IF;
PL/SQL
facilita la gestione delle condizioni d’errore predefinite o definite
dall’utente, chiamate exception.
Quando
si verifica un errore o viene attivata una exception, l’esecuzione del blocco
PL/SQL si ferma e il controllo passa alle routine di gestione delle exception
(exception handlers), dopodiche’ il controllo passa al blocco di livello
superiore, se esiste, altrimenti l’esecuzione termina.
Le
exception predefinite sono attivate implicitamente dal sistema al verificarsi
della condizione d’errore (per esempio, se cerchiamo di dividere un numero per
zero, il sistema attiva automaticamente l’exception ZERO_DIVIDE).
Ecco
alcuni esempi di exception predefinite:
|
DUP_VAL_ON_INDEX |
valore duplicato nella
chiave |
|
INVALID_CURSOR |
cursore non aperto |
|
NO_DATA_FOUND |
la select non trova righe |
|
TOO_MANY_ROWS |
la select trova piu’ di
una riga |
|
VALUE_ERROR |
errore di assegnazione valori |
|
ZERO_DIVIDE |
denominatore uguale a zero |
|
OTHERS |
tutti gli errori non gestiti nell’ EXCEPTION |
Le
exception definite dall’utente devono essere dichiarate nella parte
dichiarativa del blocco e devono essere attivate esplicitamente nella parte
esecutiva con il comando RAISE al verificarsi di particolari condizioni.
DECLARE
commission NUMBER(7,2);
comm_missing EXCEPTION;
BEGIN
SELECT comm INTO commission
FROM emp WHERE empno = :emp_id;
IF commission IS NULL THEN
RAISE comm_missing;
ELSE
:bonus := commission * 0.25;
END IF;
EXCEPTION
WHEN comm_missing THEN
...
END;
La
modularita’ e’ la possibilita’ di scomporre un’applicazione in piu’ moduli
ognuno dei quali ha un compito ben preciso ed e’ piu’ facilmente gestibile.
PL/SQL
permette la programmazione modulare attraverso l’uso di due tipi di
sottoprogrammi che possono essere richiamati dal programma principale: le
procedure e le funzioni.
I
sottoprogrammi, oltre alle tre parti di cui sono costituiti i normali blocchi
PL/SQL, hanno un’intestazione in cui viene dichiarato il nome e vengono
elencati i parametri del sottoprogramma.
La
procedura e’ un sottoprogramma che svolge una specifica azione.
PROCEDURE name
[(parameters)] IS
[declarations]
BEGIN
statements
[EXCEPTION
handlers]
END [name];
I
parametri, a differenza delle variabili, possono essere di input (IN), di
output (OUT) o di input-output (IN OUT). Per default sono di input.
Esempio:
PROCEDURE
award_bonus (emp_id NUMBER) IS
bonus
NUMBER;
comm_missing EXCEPTION;
BEGIN
SELECT comm * 0.25 INTO
bonus FROM emp WHERE empno = emp_id;
IF bonus IS NULL THEN
RAISE comm_missing;
ELSE
UPDATE payroll SET pay = pay + bonus WHERE empno = emp_id;
END IF;
EXCEPTION
WHEN comm_missing THEN
...
END
award_bonus;
La
funzione e’ un sottoprogramma che calcola e restituisce un valore.
FUNCTION name
[(arguments)] RETURN datatype IS
[declarations]
BEGIN
statements
[EXCEPTION
handlers]
END [name];
Esempio:
FUNCTION sal_ok
(salary NUMBER, title VARCHAR2)
RETURN BOOLEAN IS
min_sal NUMBER;
BEGIN
SELECT MIN(sal) INTO
min_sal FROM emp
WHERE job = title;
RETURN (salary >=
min_sal);
END sal_ok;
E’
possibile unire piu’ sottoprogrammi correlati fra loro in un’unica struttura
chiamata package.
Il
package consta di due parti:
· una parte
dichiarativa (detta specification), che e’ l’interfaccia del package verso le
applicazioni che lo richiamano
· una parte
esecutiva (detta body), che contiene i sottoprogrammi che compongono il package
PACKAGE
emp_actions IS
PROCEDURE hire_employee
(empno NUMBER, ename CHAR, ...);
PROCEDURE fire_employee
(emp_id NUMBER);
END
emp_actions;
PACKAGE BODY
emp_actions IS
PROCEDURE hire_employee
(empno NUMBER, ename CHAR, ...) IS
BEGIN
INSERT INTO emp VALUES (empno, ename, ...);
END hire_employee;
PROCEDURE fire_employee
(emp_id NUMBER) IS
BEGIN
DELETE FROM emp WHERE empno = emp_id;
END fire_employee;
END
emp_actions;
I sottoprogrammi possono essere compilati e memorizzati nel dizionario
dati (stored procedure): a questo punto sono oggetti del database e possono
essere eseguiti, senza essere ricompilati, da tutti gli utenti e da tutte le
applicazioni connesse al database.
SQL> EXECUTE
create_dept(’FINANCE’,’NEW YORK’);
Il loro uso e’ quindi vantaggioso dal punto di vista delle prestazioni,
del consumo di memoria, consente un comportamento costante delle applicazioni e
riduce i tempi di sviluppo e di test.
Le stored procedure, quando sono riunite in package, vengono chiamate
packaged procedure.
I
database trigger (da non confondere con i SQL*Forms trigger) sono stored
procedure associate a una tabella del DB e automaticamente eseguite quando la
tabella viene modificata.
Sono
eseguite prima o dopo un comando di INSERT, UPDATE o DELETE.
I
database trigger garantiscono che determinate operazioni siano sempre
effettuate, indipendentemente dall’applicazione che modifica la tabella, ed
evita che questo codice debba essere scritto o richiamato in tutte le
applicazioni.
CREATE TRIGGER
audit_sal
AFTER UPDATE OF sal ON emp
FOR EACH ROW
BEGIN
INSERT INTO emp_audit
VALUES ...;
END;
I
tipi di dato accettati dal PL/SQL nella definizione delle variabili e delle
costanti non sono esclusivamente quelli accettati dall’SQL nella definizione
delle colonne del database, e anche nei tipi di dato corrispondenti i limiti
possono essere diversi.
|
Tipo di dato |
Descrizione |
|
BINARY_INTEGER |
Puo’ contenere numeri
binari con segno che, contrariamente al tipo NUMBER, possono essere usati nei
calcoli senza essere convertiti, migliorando le prestazioni. |
|
NUMBER |
Dati di tipo numerico.
Accetta numeri interi o decimali purche’ il numero complessivo di cifre sia
al massimo 38. |
|
NUMBER(p) |
Accetta solo numeri interi
con un numero massimo di p cifre. |
|
NUMBER(p,s) |
p = precision, numero
massimo di cifre s = scale, numero massimo
di cifre decimali |
|
DECIMAL(p,s) |
E’ sinonimo di NUMBER(p,s) |
|
INTEGER |
Sinonimo di NUMBER(38). |
|
SMALLINT |
Sinonimo di NUMBER(38). |
|
CHAR[(n)] |
Stringa fissa di n bytes
(default=1): se la lunghezza effettiva della stringa e’ m < n, Oracle
aggiunge in coda (n - m) caratteri blank in modo da rendere la lunghezza
comunque di n bytes. La dimensione massima e’
32767 bytes, mentre per una colonna di database di tipo CHAR il limite e’ 255
bytes. |
|
VARCHAR2(n) |
Stringa variabile
costituita al massimo da n bytes. La dimensione massima e’
32767 bytes, mentre per una colonna di database di tipo VARCHAR2 il limite e’
2000 bytes. |
|
VARCHAR(n) |
E’ analogo al VARCHAR2. Si
prevede che, nelle future versioni di Oracle, diventi un tipo di dato a se
stante, quindi e’ meglio usare VARCHAR2. |
|
LONG |
Stringa variabile
costituita al massimo da 32760 bytes. |
|
RAW(n) |
Stringa binaria di lunghezza
n (al massimo 32767 bytes). Puo’ essere utilizzato per
contenere caratteri grafici o immagini digitalizzate. |
|
LONG RAW |
Stringa binaria come RAW
di lunghezza massima di 32760 bytes |
|
BOOLEAN |
Puo’ contenere i valori
TRUE e FALSE e il non-valore NULL. Non si puo’ inserire un valore booleano in
una colonna del database e analogamente non si puo’ inserire il valore di una
colonna in una variabile booleana. |
|
DATE |
Usato per contenere
informazioni su data e ora. |
|
ROWID |
Tipo particolare di dato
che e’ restituito dalla pseudo-colonna ROWID che rappresenta l' indirizzo
fisico di una riga del database. In PL/SQL serve a contenere una rowid in
formato leggibile ed e’ quindi un sotto-tipo di CHAR. Per inserire una rowid in
una variabile ROWID bisogna usare la funzione ROWIDTOCHAR che converte il
valore binario in una stringa di 18 bytes con formato BBBBBBBB.RRRR.FFFF (3
numeri esadecimali indicanti Blocco, Riga e File). |
Tutte
le funzioni proprie del linguaggio SQL sono valide anche nel PL/SQL.
Ad
esse si aggiungono alcune funzioni valide soltanto nel PL/SQL.
· CHARTOROWID(str)
converte una stringa str dal tipo CHAR o VARCHAR2 al tipo ROWID.
· ROWIDTOCHAR(bin)
converte un valore binario di tipo ROWID in una stringa di 18 bytes di tipo
VARCHAR2.
· SQLCODE
restituisce il piu recente codice d’ errore Oracle ed e’ significativo solo
nelle routine di gestione delle exception (exception handler), altrimenti
restituisce zero.
· SQLERRM
restituisce il messaggio d’ errore Oracle associato al valore corrente di
SQLCODE. Anch’ esso e’ significativo solo nelle routine di gestione delle
exception.
Esempio:
EXCEPTION
...
WHEN OTHERS THEN
err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM,1,100);
INSERT INTO errors VALUES(err_num,err_msg);
END;
La
tabella PL/SQL e’ un oggetto di tipo array che puo’ contenere una sola colonna
e una chiave d’accesso di tipo BINARY_INTEGER.
La
tabella PL/SQL deve essere dichiarata in due tempi: prima si dichiara il tipo,
poi si dichiara una tabella PL/SQL di quel tipo.
La
tabella PL/SQL deve essere dichiarata nella parte dichiarativa del blocco, del
sottoprogramma o del package.
DECLARE
TYPE EnameTabType IS TABLE OF
emp.ename%TYPE
INDEX BY
BINARY_INTEGER;
ename_tab EnameTabType;
i BINARY_INTEGER :=
0;
BEGIN
FOR emprec IN (SELECT ename FROM
emp) LOOP
i := i
+ 1;
ename_tab(i) := emprec.ename;
END LOOP;
...
END;