PL/SQL

Procedural Language/SQL estensione procedurale del linguaggio

SQL

Blocco di codicedeclare<sezione dichiarazioni>

begin<comandi eseguibili>

exception<gestione delle eccezioni>

end;./

Sezione dichiarazioni: variabili

declaredescrizione 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;

%rowtypedeclareimp IMPIEGATI%rowtype;ruolo IMPIEGATI.LAVORO%type;

beginselect * into impfrom 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>;

Esempiodeclare

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

%found 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

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.nofor update of STIPENDIO;

beginselect INO into manager from IMPIEGATIwhere INOME=’KING’;for imp_rec in imp_cur(manager) loop

update IMPIEGATI set STIPENDIO=imp_rec.STIPENDIO*1.5where 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;

declareimp IMPIEGATI%rowtype;beginselect * into impfrom IMPIEGATI where INO=7369;if imp.stipendio < 1000 then

update IMPIEGATI set STIPENDIO=(STIPENDIO*1.2) where INO=7369;

elsif imp.stipendio > 2000 thenupdate 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;

declarecursor 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 thenupdate 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;

declarecursor imp_cur is select * from IMPIEGATI;imp IMPIEGATI%rowtype;

beginopen imp_cur;fetch imp_cur into imp;while imp_cur%foundloop

if imp.stipendio < 1000 then update IMPIEGATI set STIPENDIO=(STIPENDIO*1.2) where INO=imp.INO;

elsif imp.stipendio > 2000 thenupdate 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 sistemacursor_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>)

declareimp IMPIEGATI%rowtype;begin

select * into impfrom IMPIEGATI where INO=7369;if imp.stipendio < 1000 then update IMPIEGATI set STIPENDIO=(STIPENDIO*1.2) where INO=7369;elsif imp.stipendio < 2000 thenupdate IMPIEGATI set STIPENDIO=(STIPENDIO*1.15) where INO=7369;

else raise TROPPO_ALTO;end if;exception

when TROPPO_ALTO then insert into STIPENDIALTIvalues (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) isselect STIPENDIO from IMPIEGATI where DIPNO = dip_numfor 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 asfunction 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) isbegin

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) isbegin 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 asuser_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) isbegin 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_accessofrom 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 triggercreate [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_IMPIEGATIafter insert or update of STIPENDIO, LAVORO on IMPIEGATIfor each rowwhen (new.LAVORO != ‘DIRIGENTE’) –- restrizione del triggerdeclare

minstip, maxstip SALARI.MINSAL%type;begin

-- ricerca del minimo e massimo stipendio per il nuovo lavoroselect MINSAL, MAXSAL into minstip, maxstip from SALARIwhere LAVORO = :new.LAVORO;-- se il nuovo lavoro è stato diminuito, oppure-- non rientra nell’intervallo, lancia un’eccezioneif (: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_SALARIbefore update or delete on SALARIfor each rowwhen (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 IMPIEGATIwhere 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 IMPIEGATIwhere 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_IMPIEGATIafter insert or update of STIPENDIO, DIPNO on IMPIEGATIdeclare

cursor DIP_CUR isselect DIPNO, BUDGET from DIPARTIMENTI;

DNO DIPARTIMENTI.DIPNO%type;TOTALESALDIPARTIMENTO.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 IMPIEGATIwhere DIPNO = DNO;if DIP_SAL > TOTALESAL thenraise_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 tabellese i vincoli possono essere controllati a livello di riga, allora

se le righe controllate sono modificate nel trigger, allora

usa trigger di riga beforealtrimenti 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.