Procedure Pl/Sql
Lo schema da seguire per quanto riguarda le dichiarazioni e la scrittura di codice è dato dalle seguenti istruzioni:
DECLARE - (opzionale) dichiarazioni e definizioni di variabili, cursori ed eccezioni definite dall’utente.
BEGIN - inizio di un blocco di codice
CODICE SQL
EXCEPTION - (opzionale) specifica l’azione da fare quando occorre un determinato errore
END; - istruzione di fine blocco (il punto e virgola è obbligatorio)
VARIABILI
E s e m p i o
DECLARE
v1 number(3);
hire_date date; /* inizializzazione implicita con null */
job_title varchar2(80) := ‘Salesman’;
emp_found boolean; /* inizializzazione implicita con null */
salary_incr CONSTANT number(3,2) := 1.5; /* costante */
emp_rec employees%ROWTYPE;
....
BEGIN … END
CURSORE IMPLICITO
La dichiarazione di un cursore specifica un insieme di tuple (come risultato di una query) che possono essere processate individualmente, una alla volta.
S i n t a s si
CURSOR <nome cursore> [(<lista parametri>)] IS <istruzione select>;
Un parametro ha la forma <nome parametro> <tipo parametro>. I possibili parametri sono char, varchar2, number, date e boolean e tutti i corrispondenti sotto-tipi come integer. I parametri sono utilizzati per assegnare valori alle variabili che sono date in un’istruzione select.
E s e m pi o
Vogliamo recuperare i seguenti valori degli attributi dalla tabella EMP in maniera orientata alle tuple:il titolo di lavoro e il nome degli impiegati che sono stati assunti dopo una certa data, e che hanno un manager che lavora in un determinato dipartimento.
CURSOR emp_cur (start_date date , dno date) IS
select job, ename from EMP e
where HIREDATE > start_date
and exists (
select * from EMP
where e.MGR = EMPNO and DEPTNO = dno);
emp_rec emp_cur%ROWTYPE;
Prima che un cursore possa essere usato, deve essere aperto utilizzando l’istruzione open.
OPEN <nome cursore> [(<lista di parametri>)];
L’istruzione select associata viene quindi processata e il cursore punta alla prima tupla selezionata. Le tuple selezionate possono essere processate una alla volta utilizzando il comando fetch.
FETCH <nome cursore> [(<lista di variabili>)];
Il comando fetch assegna i valori degli attributi selezionati dalla tupla corrente alla lista di variabili. Dopo un comando fetch, il cursore avanza alla successiva tupla nell’insieme del risultato dell’istruzione select ottenuto con il comando open. Da notare che le variabili nella lista devono avere lo stesso tipo di dati dei valori delle tuple selezionate. Dopo che tutte le tuple sono state processate, si utilizza il comando close per chiudere e disabilitare il cursore.
CLOSE <nome cursore>;
E s e m p i o
DECLARE
cursor emp_cur is select * from EMP;
emp_rec EMP%ROWTYPE;
emp_sal EMP.SAL%TYPE;
BEGIN
open emp_cur;
loop
fetch emp_cur into emp_rec;
exit when emp_cur%NOTFOUND;
emp_sal := emp_rec.sal;
<sequenza di istruzioni>
end loop;
close emp_cur;
…
END;
Ogni loop può essere completato incondizionatamente usando la clausola exit:
exit [<etichetta blocco>] [when <condizione>]
Usando exit senza un’etichetta di blocco si provoca il completamento del loop che contiene l’istruzione exit. Una condizione può essere un semplice paragone di valori. In molti casi, comunque, la condizione fa riferimento a un cursore.
LOOP FOR
I loop for di cursore vengono utilizzati per semplificare l’utilizzo di un cursore:
[<< <nome etichetta> >>]
FOR <nome record> IN <nome cursore> [(lista di parametri>)]
LOOP
<sequenza di istruzioni>
END LOOP;
Un record utilizzabile per memorizzare una tupla recuperata da un cursore viene implicitamente dichiarato. Inoltre, questo loop implicitamente esegue un fetch ad ogni iterazione, un open prima dell’ingresso nel loop e un close dopo che il loop è terminato. Se ad un’iterazione nessuna tupla viene recuperata, il loop viene automaticamente terminato senza un exit. E’ anche possibile specificare una query al posto di <nome cursore> in un loop for:
for <nome record> in (<istruzione select>) loop
<sequenza di istruzioni>
end loop;
Dunque, un cursore non deve essere specificato prima dell’ingresso nel loop, ma è definito nell’istruzione select.
Esempio:
for sal_rec in (select SAL + COMM total from EMP) loop
…;
end loop;
Mentre il numero di iterazioni di un loop while è sconosciuto fino al momento in cui il loop viene completato, il numero di iterazioni di un loop for può essere specificato usando due interi.
[<< <nome etichetta> >>]
for <indice> in [reverse] <estremo inferiore>..<estremo superiore> loop
<sequenza di istruzioni>
end loop [<nome etichetta>];
WHILE
Un loop while ha la forma
[<< <nome etichetta> >>]
while <condizione> loop
<sequenza di istruzioni>;
end loop [<nome etichetta>];
Un loop può avere un nome. Dare un nome a un loop è utile quando i loop sono nidificati e i primi vengono completati incondizionatamente utilizzando l’istruzione exit <nome etichetta>.
CONDIZIONALE
Per il controllo condizionale, PL/SQL offre costrutti if-then-else nella forma:
if <condizione> then <sequenza di istruzioni>
[elsif] <condizione> then <sequenza di istruzioni>
…
[else] <sequenza di istruzioni> end if;
Partendo con la prima condizione, se risulta true, la corrispondente sequenza di istruzioni viene eseguita, altrimenti il controllo viene passato alla successiva condizione. Quindi, quello che sta dietro a questo tipo di istruzione PL/SQL è del tutto simile alle istruzioni if-then-else dei linguaggi di programmazione classici.
ESEMPIO LOOP E CURSORE
Da notare che in PL/SQL solo l’istruzione select del tipo select <colonna(e)> into è permessa, cioè, i valori degli attributi selezionati possono solo essere assegnati a variabili (finchè l’istruzione select viene utilizzata in una subquery). L’utilizzo dell’istruzione select come nell’SQL causa un errore di sintassi (syntax error). Se le istruzioni update o delete vengono utilizzate in concomitanza con un cursore, questi comandi possono essere ristretti alla tupla attualmente recuperata. In questi casi la clausola where current <nome cursore> viene aggiunta come mostrato nel seguente esempio.
Esempio: Il seguente blocco PL/SQL esegue le seguente modifiche: tutti gli impiegati che hanno ‘KING’ come loro manager avranno un aumento di stipendio del 5%.
declare
manager EMP.MGR%TYPE;
cursor emp_cur (mgr_no number) is
select SAL from EMP
where MGR = mgr_no
for update of SAL;
begin
select EMPNO into manager from EMP
where ENAME = ‘KING’;
for emp_rec in emp_cur(manager) loop
update EMP set SAL = emp_rec.sal*1.05
where current of emp_cur;
end loop;
commit;
end;
Attenzione: da notare che il record emp_rec viene implicitamente definito.
DECLARE - (opzionale) dichiarazioni e definizioni di variabili, cursori ed eccezioni definite dall’utente.
BEGIN - inizio di un blocco di codice
CODICE SQL
EXCEPTION - (opzionale) specifica l’azione da fare quando occorre un determinato errore
END; - istruzione di fine blocco (il punto e virgola è obbligatorio)
VARIABILI
- Le variabili devono essere dichiarate prima del loro utilizzo
- I valori TRUE e FALSE possono essere assegnate a variabili di tipo BOOLEAN
- AND, OR, NOT sonon operatori che possono essere usati per la concatenazione di variabili BOOLEAN
- %TYPE è un attributo usato per definire variabili dello stesso tipo di una colonna di una tabella
- il costrutto TYPE…. IS …. è usato per definite propri tipi
- %ROWTYPE è un attributo usato per definire una variabile che memorizza una tupla completa di una determinata tabella (o risultato di query). Per esempio, il tipo di dati dept%ROWTYPE specifica una record adatto a memorizzare tutti i valori degli attributi di una riga completa dalla tabella DEPT. Tali record sono tipicamente usati in combinazione con un cursore. Si può accedere a un campo di un record utilizzando la sintassi <nome varibile>.<nome tabella>.
E s e m p i o
DECLARE
v1 number(3);
hire_date date; /* inizializzazione implicita con null */
job_title varchar2(80) := ‘Salesman’;
emp_found boolean; /* inizializzazione implicita con null */
salary_incr CONSTANT number(3,2) := 1.5; /* costante */
emp_rec employees%ROWTYPE;
....
BEGIN … END
CURSORE IMPLICITO
La dichiarazione di un cursore specifica un insieme di tuple (come risultato di una query) che possono essere processate individualmente, una alla volta.
S i n t a s si
CURSOR <nome cursore> [(<lista parametri>)] IS <istruzione select>;
Un parametro ha la forma <nome parametro> <tipo parametro>. I possibili parametri sono char, varchar2, number, date e boolean e tutti i corrispondenti sotto-tipi come integer. I parametri sono utilizzati per assegnare valori alle variabili che sono date in un’istruzione select.
E s e m pi o
Vogliamo recuperare i seguenti valori degli attributi dalla tabella EMP in maniera orientata alle tuple:il titolo di lavoro e il nome degli impiegati che sono stati assunti dopo una certa data, e che hanno un manager che lavora in un determinato dipartimento.
CURSOR emp_cur (start_date date , dno date) IS
select job, ename from EMP e
where HIREDATE > start_date
and exists (
select * from EMP
where e.MGR = EMPNO and DEPTNO = dno);
emp_rec emp_cur%ROWTYPE;
Prima che un cursore possa essere usato, deve essere aperto utilizzando l’istruzione open.
OPEN <nome cursore> [(<lista di parametri>)];
L’istruzione select associata viene quindi processata e il cursore punta alla prima tupla selezionata. Le tuple selezionate possono essere processate una alla volta utilizzando il comando fetch.
FETCH <nome cursore> [(<lista di variabili>)];
Il comando fetch assegna i valori degli attributi selezionati dalla tupla corrente alla lista di variabili. Dopo un comando fetch, il cursore avanza alla successiva tupla nell’insieme del risultato dell’istruzione select ottenuto con il comando open. Da notare che le variabili nella lista devono avere lo stesso tipo di dati dei valori delle tuple selezionate. Dopo che tutte le tuple sono state processate, si utilizza il comando close per chiudere e disabilitare il cursore.
CLOSE <nome cursore>;
E s e m p i o
DECLARE
cursor emp_cur is select * from EMP;
emp_rec EMP%ROWTYPE;
emp_sal EMP.SAL%TYPE;
BEGIN
open emp_cur;
loop
fetch emp_cur into emp_rec;
exit when emp_cur%NOTFOUND;
emp_sal := emp_rec.sal;
<sequenza di istruzioni>
end loop;
close emp_cur;
…
END;
Ogni loop può essere completato incondizionatamente usando la clausola exit:
exit [<etichetta blocco>] [when <condizione>]
Usando exit senza un’etichetta di blocco si provoca il completamento del loop che contiene l’istruzione exit. Una condizione può essere un semplice paragone di valori. In molti casi, comunque, la condizione fa riferimento a un cursore.
LOOP FOR
I loop for di cursore vengono utilizzati per semplificare l’utilizzo di un cursore:
[<< <nome etichetta> >>]
FOR <nome record> IN <nome cursore> [(lista di parametri>)]
LOOP
<sequenza di istruzioni>
END LOOP;
Un record utilizzabile per memorizzare una tupla recuperata da un cursore viene implicitamente dichiarato. Inoltre, questo loop implicitamente esegue un fetch ad ogni iterazione, un open prima dell’ingresso nel loop e un close dopo che il loop è terminato. Se ad un’iterazione nessuna tupla viene recuperata, il loop viene automaticamente terminato senza un exit. E’ anche possibile specificare una query al posto di <nome cursore> in un loop for:
for <nome record> in (<istruzione select>) loop
<sequenza di istruzioni>
end loop;
Dunque, un cursore non deve essere specificato prima dell’ingresso nel loop, ma è definito nell’istruzione select.
Esempio:
for sal_rec in (select SAL + COMM total from EMP) loop
…;
end loop;
Mentre il numero di iterazioni di un loop while è sconosciuto fino al momento in cui il loop viene completato, il numero di iterazioni di un loop for può essere specificato usando due interi.
[<< <nome etichetta> >>]
for <indice> in [reverse] <estremo inferiore>..<estremo superiore> loop
<sequenza di istruzioni>
end loop [<nome etichetta>];
WHILE
Un loop while ha la forma
[<< <nome etichetta> >>]
while <condizione> loop
<sequenza di istruzioni>;
end loop [<nome etichetta>];
Un loop può avere un nome. Dare un nome a un loop è utile quando i loop sono nidificati e i primi vengono completati incondizionatamente utilizzando l’istruzione exit <nome etichetta>.
CONDIZIONALE
Per il controllo condizionale, PL/SQL offre costrutti if-then-else nella forma:
if <condizione> then <sequenza di istruzioni>
[elsif] <condizione> then <sequenza di istruzioni>
…
[else] <sequenza di istruzioni> end if;
Partendo con la prima condizione, se risulta true, la corrispondente sequenza di istruzioni viene eseguita, altrimenti il controllo viene passato alla successiva condizione. Quindi, quello che sta dietro a questo tipo di istruzione PL/SQL è del tutto simile alle istruzioni if-then-else dei linguaggi di programmazione classici.
ESEMPIO LOOP E CURSORE
Da notare che in PL/SQL solo l’istruzione select del tipo select <colonna(e)> into è permessa, cioè, i valori degli attributi selezionati possono solo essere assegnati a variabili (finchè l’istruzione select viene utilizzata in una subquery). L’utilizzo dell’istruzione select come nell’SQL causa un errore di sintassi (syntax error). Se le istruzioni update o delete vengono utilizzate in concomitanza con un cursore, questi comandi possono essere ristretti alla tupla attualmente recuperata. In questi casi la clausola where current <nome cursore> viene aggiunta come mostrato nel seguente esempio.
Esempio: Il seguente blocco PL/SQL esegue le seguente modifiche: tutti gli impiegati che hanno ‘KING’ come loro manager avranno un aumento di stipendio del 5%.
declare
manager EMP.MGR%TYPE;
cursor emp_cur (mgr_no number) is
select SAL from EMP
where MGR = mgr_no
for update of SAL;
begin
select EMPNO into manager from EMP
where ENAME = ‘KING’;
for emp_rec in emp_cur(manager) loop
update EMP set SAL = emp_rec.sal*1.05
where current of emp_cur;
end loop;
commit;
end;
Attenzione: da notare che il record emp_rec viene implicitamente definito.
