Procedure e Funzioni

1. Procedure e Funzioni

2. 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

3. 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.

4. Privilegi per la creazione di procedure Per potere creare una procedura occorre avere il privilegio di crearla, ossia il privilegio CREATE PROCEDURE

5. 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

6. 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>

7. 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)>

8. 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)>

9. 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

10. 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};

11. 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

12. Procedure, sintassi Create [or replace] procedure <nomeprocedura> [(argomento1 [in|out|inout] tipodati_1,… … argomento_n[in|out|inout] 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.

13. 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.

14. 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.

15. 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;

16. 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;

17. 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;

18. Procedure, esempio Scrivere una procedura per incrementare lo stipendio di tutti gli impiegati che lavorano nel dipartimento fornito dal parametro della procedura

19. create procedure aumenta_salario (dno number, percentuale numberDEFAULT 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;

20. create procedure aumenta_salario (dno number, percentuale numberDEFAULT 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;

21. create procedure aumenta_salario (dno number, percentuale numberDEFAULT 0.5) is DEFINE cursor imp_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+percentage)/100) where current of emp_cur; end loop; close emp_cur; commit; end aumenta_salario; Il cursore verrà utilizzato per un update della colonna SAL

22. create procedure aumenta_salario (dno number, percentuale numberDEFAULT 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+percentage)/100) where current of emp_cur; end loop; close emp_cur; commit; end aumenta_salario; La select che definisce il cursore sarà effettuata sul parametro attuale dno Quest’espressione consente di accedere alla tupla corrente di un cursore per effettuare un update o un delete . Si usa solo nella clausola where e quando la query associata al cursore non esegue un join tra diverse tabelle

23. 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 cancella l’istruzione che si riferisce a un imp_id non valido Si riferisce al fallimento della select precedente 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;

24. 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.

25. 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;

26. 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; Definizione delle variabili

27. 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; Ass. Valori alle variabili

28. 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; Nuovo valore dell’ammontare del conto

29. 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; Aggiornamento del conto corrente

30. 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; Se non è possibile effettuare il prelievo, si registra la transazione nella tabella TransizioniOltreScoperto

31. 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}

32. 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.

33. 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.

34. 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.

35. 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.

36. 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 innumber, 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;

37. function assumi_imp (nome in varchar2, compito in varchar2, mgr in number, giorno_assunzione in date, sal innumber, 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; È definita la funzione con tutti i suoi parametri

38. function assumi_imp (nome in varchar2, compito in varchar2, mgr in number, giorno_assunzione in date, sal innumber, 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; Restituisce un numero

39. function assumi_imp (nome in varchar2, compito in varchar2, mgr in number, giorno_assunzione in date, sal innumber, 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; Si dichiara una nuova variabile destinata a contenere la nuova immatricolazione

40. function assumi_imp (nome in varchar2, compito in varchar2, mgr in number, giorno_assunzione in date, sal innumber, 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; Genera un nuovo valore della sequenza imp_sequence e l’assegna alla variabile new_impno Assegna al campo matricola questo valore e agli altri campi i parametri passati dalla funzione Restituisce il valore della matricola

41. 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.

42. 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.

43. create package gestione_personaleas function assumi_imp (nome in varchar2, compito in varchar2, mgr in number, giorno_assunzione in date, sal innumber, comm in number default 0, dip_no in number) returnnumber i procedure licenza_imp(imp_id number) procedure aumenta_salario end gestione _personale; create package body manage_employeeas function assumi_imp (nome in varchar2, compito in varchar2, mgr in number, giorno_assunzione in date, sal innumber, comm in number default 0, dip_no in number) returnnumberis 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; ifSQL%NOTFOUNDthen raise_application_error(-20011,’Impiegato con id ‘||to_char(imp_id)||’ inesistente.’); end if; end licenzia_imp; create procedure aumenta_salario (dno number, percentuale numberDEFAULT 0.5) is DEFINE cursor emp_cur (dept_no number) is select SAL from EMP where DEPTNO = dept_nofor 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 del package

44. 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 innumber, comm in number default 0, dip_no in number) return number i procedure licenza_imp(imp_id number) create procedure aumenta_salario end gestione_personale; Tutte le procedure e le funzioni che fanno parte del package vengono dichiarate nella sezione di dichiarazione del package. Vengono invece implementate nel corpo del package.