Pl sql
This presentation is the property of its rightful owner.
Sponsored Links
1 / 49

PL/SQL PowerPoint PPT Presentation


  • 41 Views
  • Uploaded on
  • Presentation posted in: General

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 descrizionevarchar(255);

Download Presentation

PL/SQL

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Pl sql

PL/SQL

Procedural Language/SQL

estensione procedurale del linguaggio SQL


Blocco di codice

Blocco di codice

declare

<sezione dichiarazioni>

begin

<comandi eseguibili>

exception

<gestione delle eccezioni>

end;

.

/


Sezione dichiarazioni variabili

Sezione dichiarazioni: variabili

declare

descrizionevarchar(255);

pi constant number(9,7) := 3.1415926;

finitoboolean;

matricolachar(6) not null default ‘000000’;

costonumber;

ivanumber := costo * 0.2;

c_nettonumber := costo - iva;


Pl sql

%type

declare

matricolachar(6) not null default ‘000000’;

codicematricola%type;

stip IMPIEGATI.STIPENDIO%type;


Rowtype

%rowtype

declare

imp IMPIEGATI%rowtype;

ruoloIMPIEGATI.LAVORO%type;

begin

select * into imp

from IMPIEGATI where INO=7369;

...

ruolo := imp.LAVORO;

...

end;


Sezione dichiarazioni cursori

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

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

Attributi dei cursori

%foundil cursore ha ancora record da trasmettere

%notfoundin cursore non ha più record da trasmettere

%isopenil cursore è stato aperto

%rowcountnumero di righe trasmesse dal cursore fino a quel momento


Parametri dei cursori

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

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;


Pl sql

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

Sezione comandi eseguibili

begin

assegnazioni

istruzioni condizionali

cicli


Istruzioni condizionali

Istruzioni condizionali

if <condizione> then <blocco_istruzioni>

{elsif <condizione> then <blocco_istruzioni>}

[else < blocco_istruzioni>]

end if;


Pl sql

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

Ciclo semplice

loop

<blocco_istruzioni>

[exit | exit when<condizione>]

end loop;


Pl sql

  • 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

    Ciclo for

    for <contatore> in [reverse] [<min>..<max>|<cursore>]loop

    <istruzioni>

    end loop;


    Pl sql

    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;


    Pl sql

    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

    Ciclo while

    while <condizione> loop

    <istruzioni>

    end loop;


    Pl sql

    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

    Gestione delle eccezioni

    exception

    when <eccezione> then <azione>;


    Eccezioni di sistema

    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

    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

    Eccezioni definite dall’utente

    raise <eccezione>

    raise_application_error

    (<numero>, <testo>)


    Pl sql

    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;


    Pl sql

    if imp.stipendio *1.2 > 4000

    then

    raise_application_error(-20001,’aumento di stipendio per ’ || imp.inome || ‘ troppo elevato’);


    Procedure

    Procedure

    create [or replace] procedure <nome procedura> [(<lista di parametri>)] is

    <blocco>;

    drop procedure <nome procedura>;

    execute <nome_Procedura>[(parametri attuali)];


    Pl sql

    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;


    Pl sql

    execute aumenta_salario(10,3);


    Funzioni

    Funzioni

    create [or replace] function <nome funzione> [(<lista di parametri>)]

    return <tipo di dato> is <nome> <tipo>

    <blocco>;


    Specifica dei parametri

    Specifica dei parametri

    <nome del parametro> [in | out | in out] <tipo di dato>

    [{ := | default}] <espressione>]


    Pl sql

    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

    Chiamata di una funzione

    variable <nome_var> <tipo>;

    execute :<nome_var> := <funzione>[(parametri)];

    drop function <nome funzione>;


    Pl sql

    variable salario number;

    execute :salario:=salario_dipartimento(20);


    Package specifica

    Package: specifica

    create [or replace] package <nome package>

    as

    <elenco>;


    Pl sql

    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

    Package: corpo

    create [or replace] package body <nome package>

    as

    <corpo package>;


    Pl sql

    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;


    Pl sql

    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

    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

    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

    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>


    Pl sql

    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;


    Pl sql

    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;


    Pl sql

    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;

    DNODIPARTIMENTI.DIPNO%type;

    TOTALESALDIPARTIMENTO.BUDGET%type;

    DIP_SALnumber;

    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

    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

    Uso dei trigger

    • per imporre vincoli di integrità;

    • per monitorare l’accesso a tabelle;

    • per propagare certe modifiche su altre tabelle;


    Abilitazione disabilitazione

    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.


  • Login