Procedure e Funzioni
Procedure
In Oracle è possibile scrivere delle procedure, dette
stored procedure, che permettono di manipolare i
database direttamente nel server piuttosto che da
remoto.
Fare agire delle procedure dall’interno del DBMS,
comporta una maggiore efficienza nella gestione di
oggetti memorizzati nel server. In questo modo il codice
“esterno” che le utilizza diventa più coerente e facile da
gestire.
Una volta che la procedura è definita, è utilizzabile come
se facesse parte dell’insieme dei comandi SQL
predefiniti
Utilità delle stored
Procedures
Le stored Procedures sono utili in quanto spostano certe
operazioni dal client al server. Il lavoro “in locale” è
molto più efficiente.
Inoltre il codice memorizzato all’interno del Database è
piuttosto statico. L’area SQL condivisa memorizza le
versioni analizzate dei comandi eseguiti. Quindi una
procedura eseguita la seconda volta può sfruttare
l’analisi svolta in precedenza, e ciò comporta un grosso
miglioramento delle prestazioni.
Inoltre il fatto che alcune funzioni siano memorizzate
nel database consente di evitare di ripetere la loro
definizione in ogni applicazione.
Privilegi per la creazione di
procedure
Per potere creare una procedura occorre avere il
privilegio di crearla, ossia il privilegio CREATE
PROCEDURE
Privilegi per l’esecuzione
Normalmente una procedura Oracle opera degli accessi e delle
modifiche alle tabelle del database.
Queste tabelle sono normalmente accessibili al “proprietario” della
procedura, ma non necessariamente agli altri utenti. In questo caso
i privilegi possono essere di due tipi:
• Se la procedura è creata con i privilegi del proprietario, significa
che chiunque la esegua può accedere a tutte le tabelle a cui ha
accesso il proprietario. Non è quindi necessario concedere
esplicitamente all’utente che la esegue l’accesso alle tabelle
utilizzate dalla procedura.
• Se la procedura è creata con i privilegi dell’utente chiamante
(possibile da Oracle 9 in poi) l’utente deve avere accesso a tutte le
tabelle utilizzate dalla procedura
Privilegi di esecuzione
Per consentire ad un altro utente la possibilità di
eseguire una procedura bisogna concedergli il privilegio
execute mediante la seguente istruzione
Grant EXECUTE on <procedura> to <utente>
Esecuzione
Per eseguire una procedura da linea di comando bisogna
far seguire alla parola chiave EXECUTE il nome della
procedura seguita dall’elenco dei parametri attuali tra
parentesi.
Execute <nome procedura(lista parametri)>
Invece se invocata da un’altra procedura, può essere
eseguita mediante la semplice chiamata del nome della
procedura con la lista dei parametri
<nome procedura(lista parametri)>
Eseguire procedure di altri
proprietari
Se si fa riferimento ad una procedura di un altro utente,
bisogna specificare il nome dell’utente
Execute <proprietario>.<nomeprocedura(parametri)
In alternativa si può definire un sinonimo
Create synonim <nome sinonimo> for
<proprietario>.<nomeprocedura(parametri)
E a quel punto la procedura si può richiamare col nome
del sinonimo
Execute <nomesinonimo(parametri)>
Esecuzione di procedure
remote
Per eseguire procedure remote, bisogna far riferimento al
link del database mediante la seguente istruzione
Execute <procedura>@<connessione database>(parametri)
In alternativa, anche in questo caso si può creare un
sinonimo:
Create synonym <nome sinonimo> for
<procedura>@<connessione database>
E utilizzare direttamente il nome del sinonimo
Creazione Procedura, sintassi
Create [or replace] procedure <nomeprocedura>
[(argomento1 [in|out|in out] tipodati_1,…
… argomento_n[in|out|in out] tipodati_n)]
[authid {current_user|definer}]
{is|as} {corpo sottoprogramma pl/sql};
Procedure, sintassi
Create [or replace] procedure <nomeprocedura>
[(argomento1 [in|out|in out] tipodati_1,…
… argomento_n[in|out|in out] tipodati_n)]
[authid {current_user|definer}]
{is|as} {corpo sottoprogramma pl/sql}
Con create procedure si definisce una nuova procedura,
dal nome scelto. L’istruzione replace sostituisce la
definizione di una procedura con una nuova con lo stesso
nome, mantenendo la definizione dei privilegi della
procedura precedente
Procedure, sintassi
Create [or replace] procedure <nomeprocedura>
[(argomento1 [in|out|in out] tipodati_1,…
… argomento_n[in|out|in out] tipodati_n)]
[authid {current_user|definer}]
{is|as} {corpo sottoprogramma pl/sql}
La procedura è seguita da una lista di argomenti.
Ogni argomento è seguito dalla parola chiave in o out o in
out, secondo se si tratta di un parametro per un input da
inserire, per l’output o per entrambi. Il default è in.
Infine viene specificato il tipo del parametro.
Procedure, sintassi
Create [or replace] procedure <nomeprocedura>
[(argomento1 [in|out|in out] tipodati_1,…
… argomento_n[in|out|in out] tipodati_n)]
[authid {current_user|definer}]
{is|as} {corpo sottoprogramma pl/sql}
Quest’istruzione individua il tipo di identificazione.
Current_user è l’utente che esegue la procedura e definer
è il proprietario. Il default è definer.
Procedure, sintassi
Create [or replace] procedure <nomeprocedura>
[(argomento1 [in|out|in out] tipodati_1,…
… argomento_n[in|out|in out] tipodati_n)]
[authid {current_user|definer}]
{is|as} {corpo sottoprogramma pl/sql}
La procedura è definita per default da un programma
pl/sql preceduto dalla parola chiave as o is.
Esempio
La seguente procedura aggiorna il campo città nella
tabella Dipartimento di un dipartimento il cui nome è
passato come parametro.
Create procedure assegnacittà(dip varchar(20),
newcittà varchar(20)) is
Begin
Update Dipartimenti
Set Città=newcittà
Where nome=dip;
End;
Esempio
La seguente procedura assegna all’attributo città il valore
newcittà per tutte le righe delle tabelle Dipartimento ed
Impiegato in cui l’attributo ha valore oldcittà
Procedure cambiacittà(newcittà varchar(20)
oldcittà varchar(20));
Begin
update Dipartimento
set città =newcittà
where città = oldcittà;
update Impiegato
set città =newcittà
where città = oldcittà;
End;
Esempio
La seguente procedura permette di attribuire all’attributo
città il valore newcittà per tutte le righe della Tabella
DIPARTIMENTO che hanno nome nomedip, e se non trova
un elemento da modificare, inserisce l’elemento in una
tabella ERRORIDIP.
Procedure cambiacittadip(nomedip varchar(20),
newcittà varchar(20)) is
Begin
If (select * from DIPARTIMENTO
where nome=nomedip)=NULL
Insert into ERRORIDIP values (nomedip)
Else update DIPARTIMENTO
set città=newcittà
where nome=nomedip;
End if;
End;
Procedure, esempio
Scrivere una procedura per incrementare lo stipendio
di tutti gli impiegati che lavorano nel dipartimento
fornito dal parametro della procedura
create procedure aumenta_salario
(dno number, percentuale number DEFAULT 0.5) is
DEFINE
cursor emp_cur (dept_no number) is
select SAL from EMP where DEPTNO = dept_no
for update of SAL;
empsal number(8);
begin
open emp_cur(dno);
loop
fetch emp_cur into empsal;
exit when emp_cur%NOTFOUND;
update EMP set SAL = empsal*((100+percentuale)/100)
where current of emp_cur;
end loop;
close emp_cur;
commit;
end aumenta_salario;
create procedure aumenta_salario
(dno number, percentuale number DEFAULT 0.5) is
DEFINE
cursor emp_cur (dept_no number) is
select SAL from EMP where DEPTNO = dept_no
for update of SAL;
empsal number(8);
begin
open emp_cur(dno);
loop
fetch emp_cur into empsal;
exit when emp_cur%NOTFOUND;
update EMP set SAL = empsal*((100+percentuale)/100)
where current of emp_cur;
end loop;
close emp_cur;
commit;
end aumenta_salario;
create procedure aumenta_salario
(dno number, percentuale number DEFAULT 0.5) is
DEFINE
cursor imp_cur (dept_no number) is
select SAL from EMP where DEPTNO = dept_no
for update of SAL;
Il cursore verrà utilizzato per
empsal number(8);
un update della colonna SAL
begin
open emp_cur(dno);
loop
fetch emp_cur into empsal;
exit when emp_cur%NOTFOUND;
update EMP set SAL = empsal*((100+percentage)/100)
where current of emp_cur;
end loop;
close emp_cur;
commit;
end aumenta_salario;
create procedure aumenta_salario
(dno number, percentuale number DEFAULT 0.5) is
DEFINE
cursor emp_cur (dept_no number) is
select SAL from EMP where DEPTNO = dept_no
for update of SAL;
empsal number(8);
begin
La select che definisce il cursore sarà
open emp_cur(dno);
effettuata sul parametro attuale dno
loop
fetch emp_cur into empsal;
exit when emp_cur%NOTFOUND;
update EMP set SAL = empsal*((100+percentage)/100)
where current of emp_cur;
Quest’espressione consente di
end loop;
accedere alla tupla corrente di un
close emp_cur;
cursore per effettuare un update o un
commit;
delete . Si usa solo nella clausola where
end aumenta_salario;
e quando la query associata al cursore
non esegue un join tra diverse tabelle
Questa procedura cancella dalla tabella IMP i dati
di un impiegato che viene licenziato, e solleva
un’eccezione se il codice dell’impiegato della
select non è contenuto nella tabella
Si riferisce al fallimento
della select precedente
cancella l’istruzione
che si riferisce a un
imp_id non
valido
procedure licenza_imp(imp_id number) is
Declare
Non_impiegato EXCEPTION;
begin
delete from IMP where impno = imp_id;
if SQL%NOTFOUND then RAISE Non_impiegato;
EXCEPTION
when Non_impiegato then raise_application_error
(-20011,’Impiegato con id ‘||to_char(imp_id)||’ inesistente.’);
end if;
end licenzia_imp;
Esempio
La seguente procedura preleva l’ammontare Prelievo
dal conto con codice CodConto se nel conto è presente
una copertura sufficiente, altrimenti inserisce nella
tabella TransizioniOltreScoperto il codice del conto,
l’ammontare del prelievo e la data del prelievo.
Procedure Addebita(CodConto char(5),Prelievo integer) is
Define
OldAmmontare integer;
NewAmmontare integer;
Soglia integer;
begin
select Ammontare, Scoperto into OldAmmontare, Soglia
from ContoCorrente
where CodiceConto = CodConto;
NewAmmontare := OldAmmontare - Prelievo;
if NewAmmontare > Soglia then update ContoCorrente
set Ammontare = NewAmmontare
where CodiceConto = CodConto;
Else insert into TransazioniOltreScoperto
values(CodConto,Prelievo,sysdate);
end if;
end Addebita;
Procedure Addebita(CodConto char(5),Prelievo integer) is
Define
OldAmmontare integer;
Definizione delle variabili
NewAmmontare integer;
Soglia integer;
begin
select Ammontare, Scoperto into OldAmmontare, Soglia
from ContoCorrente
where CodiceConto = CodConto;
NewAmmontare := OldAmmontare - Prelievo;
if NewAmmontare > Soglia then update ContoCorrente
set Ammontare = NewAmmontare
where CodiceConto = CodConto;
Else insert into TransazioniOltreScoperto
values(CodConto,Prelievo,sysdate);
end if;
end Addebita;
Procedure Addebita(CodConto char(5),Prelievo integer) is
Define
OldAmmontare integer;
NewAmmontare integer;
Soglia integer;
begin
select Ammontare, Scoperto into OldAmmontare, Soglia
Ass. Valori
from ContoCorrente
alle variabili
where CodiceConto = CodConto;
NewAmmontare := OldAmmontare - Prelievo;
if NewAmmontare > Soglia then update ContoCorrente
set Ammontare = NewAmmontare
where CodiceConto = CodConto;
Else insert into TransazioniOltreScoperto
values(CodConto,Prelievo,sysdate);
end if;
end Addebita;
Procedure Addebita(CodConto char(5),Prelievo integer) is
Define
OldAmmontare integer;
NewAmmontare integer;
Soglia integer;
begin
select Ammontare, Scoperto into OldAmmontare, Soglia
from ContoCorrente
Nuovo valore
where CodiceConto = CodConto;
dell’ammontare
NewAmmontare := OldAmmontare - Prelievo;
del conto
if NewAmmontare > Soglia then update ContoCorrente
set Ammontare = NewAmmontare
where CodiceConto = CodConto;
Else insert into TransazioniOltreScoperto
values(CodConto,Prelievo,sysdate);
end if;
end Addebita;
Procedure Addebita(CodConto char(5),Prelievo integer) is
Define
OldAmmontare integer;
NewAmmontare integer;
Soglia integer;
begin
select Ammontare, Scoperto into OldAmmontare, Soglia
from ContoCorrente
Aggiornamento del
where CodiceConto = CodConto;
conto corrente
NewAmmontare := OldAmmontare - Prelievo;
if NewAmmontare > Soglia then update ContoCorrente
set Ammontare = NewAmmontare
where CodiceConto = CodConto;
Else insert into TransazioniOltreScoperto
values(CodConto,Prelievo,sysdate);
end if;
end Addebita;
Procedure Addebita(CodConto char(5),Prelievo integer) is
Define
OldAmmontare integer;
NewAmmontare integer;
Soglia integer;
begin
select Ammontare, Scoperto into OldAmmontare, Soglia
from ContoCorrente
where CodiceConto = CodConto;
NewAmmontare := OldAmmontare - Prelievo;
if NewAmmontare > Soglia then update ContoCorrente
set Ammontare = NewAmmontare
where CodiceConto = CodConto;
Else insert into TransazioniOltreScoperto
values(CodConto,Prelievo,sysdate);
end if;
Se non è possibile effettuare il
end Addebita;
prelievo, si registra la transazione
nella tabella
TransizioniOltreScoperto
Funzioni
In Oracle è anche possibile definire delle funzioni, ossia
delle procedure che restituiscono un valore. La sintassi è
la seguente:
Create or replace function nome_funz
[(argomento_1 [in|out|in out] tipo_1
…
argomento_n [in|out|in out] tipo_n)]
Return tipodato
{is|as} {corpo funzione Pl/Sql}
Funzioni, sintassi
Create or replace function nome_funz
[(argomento_1 [in|out|in out] tipo_1
…
argomento_n [in|out|in out] tipo_n)]
Return tipodato
{is|as} {corpo funzione Pl/Sql}
Crea una funzione o ne rimpiazza una esistente
ereditandone i privilegi, e le assegna un nome.
Funzioni, sintassi
Create or replace function nome_funz
[(argomento_1 [in|out|in out] tipo_1
…
argomento_n [in|out|in out] tipo_n)]
Return tipodato
{is|as} {corpo funzione Pl/Sql}
La funzione può contenere uno o più argomenti. Le
specifiche in, out e in out indicano se l’argomento è un
dato di input, o un valore che deve essere restituito, o
entrambe le cose.
Funzioni, sintassi
Create or replace function nome_funz
[(argomento_1 [in|out|in out] tipo_1
…
argomento_n [in|out|in out] tipo_n)]
Return tipodato
{is|as} {corpo funzione Pl/Sql}
Return tipodato indica il tipo di valore che la funzione
deve restituire. Deve essere un qualunque tipo dato
valido in Pl/Sql. Ogni funzione deve contenere la
clausola return.
Funzioni, sintassi
Create or replace function nome_funz
[(argomento_1 [in|out|in out] tipo_1
…
argomento_n [in|out|in out] tipo_n)]
Return tipodato
{is|as} {corpo funzione Pl/Sql}
Alle parole chiavi is o as, segue il corpo della funzione
espresso da un blocco Pl/Sql.
Funzioni, esempio
La seguente funzione inserisce nella tabella imp
un nuovo impiegato, generandone un nuovo
codice, e restituisce tale codice.
function assumi_imp (nome in varchar2, compito in varchar2,
mgr in number, giorno_assunzione in date, sal in number,
comm in number default 0, dip_no in number)
return number is
DECLARE
new_impno number(10);
begin
select imp_sequence.nextval into new_impno from dual;
insert into imp values(new_impno, nome, compito, mgr,
giorno_assunzione, sal, comm, dip_no);
return new_impno;
end assumi_imp;
function assumi_imp (nome in varchar2, compito in
varchar2, mgr in number, giorno_assunzione in date, sal in
number, comm in number default 0, dip_no in number)
return number is
È definita la funzione con
DECLARE
tutti i suoi parametri
new_impno number(10);
begin
select imp_sequence.nextval into new_impno from dual;
insert into imp values(new_impno, nome, compito, mgr,
giorno_assunzione, sal, comm, dip_no);
return new_impno;
end assumi_imp;
function assumi_imp (nome in varchar2, compito in
varchar2, mgr in number, giorno_assunzione in date, sal in
number, comm in number default 0, dip_no in number)
return number is
Restituisce un numero
DECLARE
new_impno number(10);
begin
select imp_sequence.nextval into new_impno from dual;
insert into imp values(new_impno, nome, compito, mgr,
giorno_assunzione, sal, comm, dip_no);
return new_impno;
end assumi_imp;
function assumi_imp (nome in varchar2, compito in
varchar2, mgr in number, giorno_assunzione in date, sal in
number, comm in number default 0, dip_no in number)
return number is
DECLARE
Si dichiara una nuova variabile
destinata a contenere la nuova
new_impno number(10);
immatricolazione
begin
select imp_sequence.nextval into new_impno from dual;
insert into imp values(new_impno, nome, compito, mgr,
giorno_assunzione, sal, comm, dip_no);
return new_impno;
end assumi_imp;
function assumi_imp (nome in varchar2, compito in
varchar2, mgr in number, giorno_assunzione in date, sal in
number, comm in number default 0, dip_no in number)
return number is
Genera un nuovo valore della sequenza
DECLARE
imp_sequence e l’assegna alla variabile
new_impno number(10);
new_impno
begin
select imp_sequence.nextval into new_impno from dual;
insert into imp values(new_impno, nome, compito, mgr,
giorno_assunzione, sal, comm, dip_no);
return new_impno;
Assegna al campo matricola questo valore e agli
end assumi_imp;
altri campi i parametri passati dalla funzione
Restituisce il valore della matricola
Packages
E’ essenziale per un buon stile di programmazione che i
blocchi, procedure e funzioni logicamente correlati
vengano combinati in moduli, e che ogni modulo
fornisca un’interfaccia che permetta agli utenti e
sviluppatori di utilizzarne le funzionalità.
PL/SQL supporta il concetto di modularizzazione
grazie al quale i moduli e altri costrutti possono
essere organizzati in packages.
Package
Un package è costituito da una dichiarazione di package e
da un corpo di package. La dichiarazione del package
definisce l’interfaccia che è visibile ai programmatori di
applicazione, e il corpo del package implementa la
dichiarazione del package
Di seguito viene dato un package per la gestione del
personale, e che include la procedure e funzioni viste in
precedenza.
create package gestione_personale as
function assumi_imp (nome in varchar2, compito in varchar2, mgr in number, giorno_assunzione in date, sal in number,
comm in number default 0, dip_no in number)
return number i
procedure licenza_imp(imp_id number)
Dichiarazione del package
procedure aumenta_salario
end gestione _personale;
create package body manage_employee as
function assumi_imp (nome in varchar2, compito in varchar2, mgr in number, giorno_assunzione in date, sal in number,
comm in number default 0, dip_no in number)
return number is
DECLARE
new_impno number(10);
begin
select imp_sequence.nextval into new_impno from dual;
insert into imp values(new_impno, nome, compito, mgr, giorno_assunzione, sal, comm, dip_no);
return new_impno;
end assumi_imp;
procedure licenza_imp(imp_id number) is
begin
delete from IMP where impno = imp_id;
if SQL%NOTFOUND then raise_application_error(-20011,’Impiegato con id ‘||to_char(imp_id)||’ inesistente.’);
end if;
end licenzia_imp;
create procedure aumenta_salario (dno number, percentuale number DEFAULT 0.5) is
DEFINE
cursor emp_cur (dept_no number) is select SAL from EMP where DEPTNO = dept_no for update of SAL;
empsal number(8);
begin
open emp_cur(dno);
loop
fetch emp_cur into empsal;
exit when emp_cur%NOTFOUND;
update EMP set SAL = empsal*((100+percentuale)/100) where current of emp_cur;
end loop;
close emp_cur;
commit;
end raise_salary;
end manage_employee;
Dichiarazione di Package
create package gestione_personale as
function assumi_imp (nome in varchar2, compito in
varchar2, mgr in number, giorno_assunzione in date, sal
in number, comm in number default 0, dip_no in number)
return number i
procedure licenza_imp(imp_id number)
create procedure aumenta_salario
Tutte le procedure e le
end gestione_personale;
funzioni che fanno parte
del package vengono
dichiarate nella sezione
di dichiarazione del
package. Vengono invece
implementate nel corpo
del package.