PL/SQL
Procedural Language/SQL
estensione procedurale del linguaggio SQL
Blocco di codice
declare
<sezione dichiarazioni>
begin
<comandi eseguibili>
exception
<gestione delle eccezioni>
end;
.
/
Sezione dichiarazioni: variabili
declare
descrizione varchar(255);
pi constant number(9,7) := 3.1415926;
finito boolean;
matricola char(6) not null default
‘000000’;
costo
number;
iva
number := costo * 0.2;
c_netto
number := costo - iva;
%type
declare
matricola char(6) not null default
‘000000’;
codice matricola%type;
stip IMPIEGATI.STIPENDIO%type;
%rowtype
declare
imp
IMPIEGATI%rowtype;
ruolo IMPIEGATI.LAVORO%type;
begin
select * into imp
from IMPIEGATI where INO=7369;
...
ruolo := imp.LAVORO;
...
end;
Sezione dichiarazioni: cursori
cursor <nome_cursore> [<lista parametri>]
[is <query>]
[for update <lista colonne>];
open <nome_cursore>;
fetch <nome_cursore> into <variabile>;
close <nome_cursore>;
Esempio
declare
cursor imp_cur is select * from IMPIEGATI;
imp
IMPIEGATI%rowtype;
begin
open imp_cur;
fetch imp_cur into imp;
... imp.NOME ... imp.INO ...
close imp_cur;
end;
Attributi dei cursori
il cursore ha ancora record da
trasmettere
%notfound in cursore non ha più record da
trasmettere
%isopen
il cursore è stato aperto
%rowcount numero di righe trasmesse dal
cursore fino a quel momento
%found
Parametri dei cursori
cursor impiegati_cur (data date, dno number)
is
select LAVORO, INOME from IMPIEGATI I
where DATA_ASS > data and
exist (select * from IMPIEGATI
where I.CAPO = INO and DIPNO = dno);
impiegati assunti dopo una certa data, il cui capo
lavora in un certo dipartimento.
Modifiche tramite cursore
cursor impiegati_cur (data date, dno number)
is
select LAVORO, INOME from IMPIEGATI I
where DATA_ASS > data and
exist (select * from IMPIEGATI
where I.CAPO = INO and DIPNO = dno)
for update of LAVORO;
declare
manager IMPIEGATI.CAPO%type;
cursor imp_cur (mgr_no number) is
select STIPENDIO from IMPIEGATI where CAPO=mgr.no
for update of STIPENDIO;
begin
select INO into manager from IMPIEGATI
where INOME=’KING’;
for imp_rec in imp_cur(manager) loop
update IMPIEGATI set STIPENDIO=imp_rec.STIPENDIO*1.5
where current of imp_cur;
end loop;
commit;
end;
Sezione comandi eseguibili
begin
assegnazioni
istruzioni condizionali
cicli
Istruzioni condizionali
if <condizione> then
<blocco_istruzioni>
{elsif <condizione> then
<blocco_istruzioni>}
[else < blocco_istruzioni>]
end if;
declare
imp IMPIEGATI%rowtype;
begin
select * into imp
from IMPIEGATI where INO=7369;
if imp.stipendio < 1000 then
update IMPIEGATI set STIPENDIO=(STIPENDIO*1.2)
where INO=7369;
elsif imp.stipendio > 2000 then
update IMPIEGATI set STIPENDIO=(STIPENDIO*1.1)
where INO=7369;
else update IMPIEGATI set
STIPENDIO=(STIPENDIO*1.15)
where INO=7369;
end if;
end;
Ciclo semplice
loop
<blocco_istruzioni>
[exit | exit when <condizione>]
end loop;
declare
cursor imp_cur is select * from IMPIEGATI;
imp
IMPIEGATI%rowtype;
begin
open imp_cur;
loop
fetch imp_cur into imp;
exit when imp_cur%notfound;
if imp.stipendio < 1000 then
update IMPIEGATI set STIPENDIO=STIPENDIO*1.2
where INO=imp.INO;
elsif imp.stipendio > 2000 then
update IMPIEGATI set STIPENDIO=STIPENDIO*1.1
where INO= imp.INO;
else update IMPIEGATI set
STIPENDIO=STIPENDIO*1.15
where INO= imp.INO;
end if;
end loop;
close imp_cur;
end;
Ciclo for
for <contatore> in [reverse]
[<min>..<max>|<cursore>] loop
<istruzioni>
end loop;
for i in 1..10 loop
dbms_output.put_line(‘i = ‘ || i);
sum := sum+i;
end loop;
for i in reverse 1..5 loop
dbms_output.put_line(‘i = ‘ || i);
sum := sum+2*i;
end loop;
declare
cursor imp_cur is select * from IMPIEGATI;
imp
IMPIEGATI%rowtype;
begin
for imp in imp_cur
loop
if imp.stipendio < 1000 then
update IMPIEGATI set STIPENDIO=STIPENDIO*1.2
where INO = imp.INO;
elsif imp.stipendio > 2000 then
update IMPIEGATI set STIPENDIO=STIPENDIO*1.1
where INO = imp.INO;
else update IMPIEGATI set STIPENDIO=STIPENDIO*1.15
where INO = imp.INO;
end if;
end loop;
end;
Ciclo while
while <condizione> loop
<istruzioni>
end loop;
declare
cursor imp_cur is select * from IMPIEGATI;
imp IMPIEGATI%rowtype;
begin
open imp_cur;
fetch imp_cur into imp;
while imp_cur%found
loop
if imp.stipendio < 1000 then
update IMPIEGATI set STIPENDIO=(STIPENDIO*1.2)
where INO=imp.INO;
elsif imp.stipendio > 2000 then
update IMPIEGATI set STIPENDIO=(STIPENDIO*1.1)
where INO= imp.INO;
else update IMPIEGATI set STIPENDIO=(STIPENDIO*1.15)
where INO= imp.INO;
end if;
fetch imp_cur into imp;
end loop;
close imp_cur;
end;
Gestione delle eccezioni
exception
when <eccezione> then <azione>;
Eccezioni di sistema
cursor_already_open – quando si apre un cursore già
aperto;
dup_val_on_index – se si cerca di inserire un duplicato in
una tabella in cui è definito un indice unico;
invalid_cursor – quando si compie un’azione illegale su un
corsore (si tenta di chiudere un cursore, già chiuso, si tenta
di aprire un cursore non definito, …);
invalid_number – quando fallisce la conversione di una
stringa in numero;
no_data_found – quando una select…into non trova dati che
la soddisfino;
Eccezioni di sistema (2)
storage_error – memoria insufficiente o corrotta;
too_many_rows – quando una select…into senza
cursore genera più di una riga;
zero_divide – quando si verifica una divisione per
zero;
others – permette di gestire tutte le eccezioni non
presenti nella sezione; deve essere l’ultima della
lista.
Eccezioni definite dall’utente
raise <eccezione>
raise_application_error
(<numero>, <testo>)
declare
imp IMPIEGATI%rowtype;
begin
select * into imp
from IMPIEGATI where INO=7369;
if imp.stipendio < 1000 then
update IMPIEGATI set STIPENDIO=(STIPENDIO*1.2)
where INO=7369;
elsif imp.stipendio < 2000 then
update IMPIEGATI set STIPENDIO=(STIPENDIO*1.15)
where INO=7369;
else raise TROPPO_ALTO;
end if;
exception
when TROPPO_ALTO then insert into STIPENDIALTI
values (7369, imp.stip);
when no_data_found then insert into INESISTENTI
values (7369);
when others rollback;
end;
if imp.stipendio *1.2 > 4000
then
raise_application_error(-20001,’aumento di
stipendio per ’ || imp.inome || ‘ troppo
elevato’);
Procedure
create [or replace] procedure <nome
procedura> [(<lista di parametri>)]
is
<blocco>;
drop procedure <nome procedura>;
execute <nome_Procedura>[(parametri
attuali)];
create procedure aumenta_salario(dno number,
percent number default 0.5) is
cursor imp_cur (dip_num number) is
select STIPENDIO from IMPIEGATI where DIPNO = dip_num
for update of STIPENDIO;
impstip number(8);
begin
open imp_cur(dno); --qui viene assegnato dno a dip_num
loop
fetch imp_cur into impstip;
exit when imp_cur%notfount;
update IMPIEGATI set
STIPENDIO = impstip*(100+percent)/100)
where current of imp_cur;
end loop;
close imp_cur;
commit;
end aumenta_salario;
execute aumenta_salario(10,3);
Funzioni
create [or replace] function <nome
funzione> [(<lista di
parametri>)]
return <tipo di dato> is <nome>
<tipo>
<blocco>;
Specifica dei parametri
<nome del parametro> [in | out |
in out] <tipo di dato>
[{ := | default}] <espressione>]
create function salario_dipartimento (dno
number)
return number is totale number;
begin
totale := 0;
for imp_stip in (select STIPENDIO from
IMPIEGATI where DIPNO = dno
and STIPENDIO is not null)
loop
totale := totale + imp_stip.STIPENDIO;
end loop;
return totale;
end salario_dipartimento;
Chiamata di una funzione
variable <nome_var> <tipo>;
execute :<nome_var> := <funzione>[(parametri)];
drop function <nome funzione>;
variable salario number;
execute :salario:=salario_dipartimento(20);
Package: specifica
create [or replace] package <nome
package>
as
<elenco>;
create package GESTIONE_IMPIEGATI as
function AssunzioneImpiegato (nome
varchar2, lavoro varchar2, mng number,
assunzione date, stip number, dip
number)
return number;
procedure LicenziamentoImpiegato (imp_id
number);
procedure AumentaStipendio (imp_id
number, stip_incr number);
end GESTIONE_IMPIEGATI;
Package: corpo
create [or replace] package body
<nome package>
as
<corpo package>;
create package body GESTIONE_IMPIEGATI as
function AssunzioneImpiegato (nome varchar2, lavoro varchar2, mng number,
assunzione date, stip number, dip number)
return number is new_imp number(4);
begin
select imp_sequence.nextval into new_imp from dual;
insert into IMPIEGATI values(new_imp, nome, lavoro, mng, assunzione,
stip dip);
return new_imp;
end AssunzioneImpiegato;
procedure LicenziamentoImpiegato (imp_id number) is
begin
delete from IMPIEGATI where ino=imp_id;
if SQL%NOTFOUND then raise_applicatioon_error(-20011 ‘Impiegato con
codice ‘||to_char(imp_id)||’ non esistente.’);
end if;
end LicenziamentoImpiegato;
procedure AumentaStipendio (imp_id number, stip_incr number) is
begin
update IMPIEGATI set stipendio=stipendio+stip_incr
where ino=imp_id;
if SQL%NOTFOUND then raise_applicatioon_error(-20012 ‘Impiegato con
codice ‘||to_char(imp_id)||’ non esistente.’);
end if;
end AumentaStipendio;
end GESTIONE_IMPIEGATI;
create or replace package body GESTIONE_IMPIEGATI as
user_name varchar2;
data_accesso date;
function AssunzioneImpiegato (nome varchar2, lavoro varchar2, mng number,
assunzione date, stip number, dip
number)
return number is
new_imp number(4);
begin
select imp_sequence.nextval into new_imp from dual;
insert into IMPIEGATI values(new_imp, nome, lavoro, mng, assunzione,
stip dip);
return new_imp;
end AssunzioneImpiegato;
...
procedure AumentaStipendio (imp_id number, stip_incr number) is
begin
update IMPIEGATI set stipendio=stipendio+stip_incr
where ino=imp_id;
if SQL%NOTFOUND then raise_applicatioon_error(-20012 ‘Impiegato con
codice ‘||to_char(imp_id)||’ non esistente.’);
end if;
end AumentaStipendio;
begin
select user, sysdate into user_name, data_accesso
from dual;
end GESTIONE_IMPIEGATI;
Trigger


è un meccanismo che esegue
automaticamente un blocco PL/SQL, quando
un determinato evento si verifica su una
tabella;
eventi:


insert, update, delete
livello:


riga
transazione
Definizione di un trigger
create [or replace] trigger <nome trigger>
{before | after | instead of}
{delete | insert | update [of <colonna/e>]}
[or {delete | insert | update [of
<colonna/e>]}]
on <tabella>
[ [ referencing {old [as] <vecchio> | new [as]
<nuovo>}]
for each row
[when (<condizione>)] ]
<blocco pl/sql>
Valori
in un trigger di update si può accedere ai valori
:old.<colonna> e :new.<colonna>
in un trigger insert si può accedere solo ai valori
:new.<colonna>
in un trigger delete si può accedere solo ai valori
:old.<colonna>
create or replace trigger CONTROLLA_STIP_IMPIEGATI
after insert or update of STIPENDIO, LAVORO on IMPIEGATI
for each row
when (new.LAVORO != ‘DIRIGENTE’) –- restrizione del trigger
declare
minstip, maxstip SALARI.MINSAL%type;
begin
-- ricerca del minimo e massimo stipendio per il nuovo lavoro
select MINSAL, MAXSAL into minstip, maxstip from SALARI
where LAVORO = :new.LAVORO;
-- se il nuovo lavoro è stato diminuito, oppure
-- non rientra nell’intervallo, lancia un’eccezione
if (:new.STIPENDIO < :old.STIPENDIO) then
raise_application_error(-20010,’Lo stipendio è stato
diminuito’);
elsif (:new.STIPENDIO < minstip or :new.STIPENDIO > maxstip)
then raise_application_error(-20020,’Lo stipendio è fuori
dal rango consentito’);
elsif (:new.STIPENDIO > 1.1 * :old.STIPENDIO) then
raise_application_error(-20030,’Lo stipendio è stato
aumentato più del 10%’);
end if;
end;
create or replace trigger CONTROLLA_STIP_SALARI
before update or delete on SALARI
for each row
when (new.MINSAL > old.MINSAL or new.MAXSAL < old.MAXSAL)
declare
imp_lav number(3) := 0;
begin
if deleting then
select count(*) into imp_lav from IMPIEGATI
where LAVORO = :old.LAVORO;
if imp_lav != 0 then
raise_application_error(-20040, ‘Esistono degli impiegato
con lavoro ‘ || :old.LAVORO);
end if;
end if;
if updating then
select count(*) into imp_lav from IMPIEGATI
where LAVORO = :new.LAVORO
and STIPENDIO not between :new.MINSAL and :new.MAXSAL;
if imp_lav != 0 then
:new.MINSAL := old.MINSAL;
:new.MAXSAL := old.MAXSAL;
end if;
end if;
end;
create or replace trigger CONTROOLA_BUDGET_IMPIEGATI
after insert or update of STIPENDIO, DIPNO on IMPIEGATI
declare
cursor DIP_CUR is
select DIPNO, BUDGET from DIPARTIMENTI;
DNO DIPARTIMENTI.DIPNO%type;
TOTALESAL
DIPARTIMENTO.BUDGET%type;
DIP_SAL
number;
begin
open DIP_CUR;
loop
fetch DIP_CUR into DNO, TOTALESAL;
exit when DIP_CUR%notfound;
select sum(STIPENDIO) into DIP_SAL from IMPIEGATI
where DIPNO = DNO;
if DIP_SAL > TOTALESAL then
raise_application_error(-20050, ‘Il totale degli stipendi del
dipartimento ‘ || to_char(DNO) ||’ supera il budget’);
end if;
end loop;
close DIP_CUR;
end;
Regole per l’uso dei trigger
Identificare le operazioni e le tabelle che possono essere
critiche relativamente a vincoli di integrità;
Per ciascuna di tali tabelle
se i vincoli possono essere controllati a livello di riga, allora
se le righe controllate sono modificate nel trigger, allora
usa trigger di riga before
altrimenti usa trigger di riga after
altrimenti usa trigger di transazione after.
Uso dei trigger

per imporre vincoli di integrità;

per monitorare l’accesso a tabelle;

per propagare certe modifiche su altre
tabelle;
Abilitazione/disabilitazione

drop <trigger>;

alter trigger <trigger> disable;

alter table <tabella> enable |disable
all trigger;

Le informazioni sui trigger sono memorizzate nella
tabella USER_TRIGGERS del dizionario dei dati.
Scarica

document