PL/SQL



anonymous blocks
procedure
function
trigger
SQL: funzioni standard

formati
tipi dati
strutture di controllo: for, while
scoping, ricorsione
ottimizzazione
packages
plan
exceptions, debug e trace

prefazione
(aggiornamento al 21-2-03)

Questo documento è a dir poco antico!! eppure continua ad essere consultato decine di volte al giorno (non so da chi.. eppure è così; v. qui); quindi lo lascio, facendo presente che


introduzione



Che cos'ha di particolare Oracle rispetto a database più antiquati (come il DB2 IBM) a cui molti sono abituati?
fondamentalmente una sola cosa: la possibilità di memorizzare, oltre ai dati, dei "programmi" per gestirli.
Il linguaggio utilizzato per questi programmi è il PL/SQL (che sta per Procedural Language SQL), ed è sintatticamente simile al Pascal (o a Java).


tipi di programmi



In oracle esistono 4 tipi di programmi:
  1. anonymous blocks: un blocco di istruzioni PL/SQL
  2. procedure: un blocco di istruzioni PL/SQL identificato da un nome
  3. funzioni: procedure che ritornano un valore
  4. trigger: procedure che vengono eseguite automaticamente da Oracle al verificarsi di determinati eventi (es.: insert, update, delete su una tabella)


blocchi PL/SQL (anonymous blocks)



il modo migliore per cominciare a conoscere il PL/SQL è guardare un esempio di programma:
consideriamo ad es. la tabella tabella1, creata con il seguente statement SQL: 
create table tabella1 (col1 number);
insert into tabella1 (0);
commit;
e il blocco PL/SQL: 
declare
  x number;
begin
  select valore1 from tabella1 into x;
  if x < 100 then
      update tabella1 set col1= x +1;
      commit;
  end if;
end;
eseguendo ripetutamente questo blocco la colonna col1 della tabella assumerà la prima volta il valore 1, la seconda 2 ecc. fino ad arrivare a 100; ulteriori esecuzioni non modificheranno più il valore della colonna.

Nel precedente blocco PL/SQL sono presenti istruzioni SQL (select , update, commit) che utilizzano la normale sintassi dell'SQL;
e un'istruzioni di controllo (if - end if) tipica di un linguaggio di programmazione.


procedure


Civilization advances by extending the number of important operations that we can perform without thinking about them,
Alfred North Whitehead



Le caratteristiche fondamentali di una procedura sono:
- il nome che la identifica
- un elenco di parametri che costituiscono l'interfaccia con l'ambiente esterno alla procedura

ad esempio, riprendendo il blocco precedente, potremmo definire una procedura :
(le differenze sono evidenziate in rosso) 
create procedure aggiorna ( param1 number) is
  x number;
begin
  select valore1 from tab1 into x;
  if x < 100 then
     update tabella1 set col1= x + param1;
     commit;
  end if;
end;

l'istruzione declare è stata sostituita dalla create;
l'esecuzione della procedura , ad es.: 
aggiorna ( 3);
non incrementerà più il valore della colonna di 1 ma di 3; 
aggiorna (x);
incrementerà col1 di qualsiasi valore x venga passato;


function



Un esempio di funzione: 
create function hex2dec (x char ) return number is
  y number;
begin
  if x in ('0','1','2','3','4','5','6','7','8','9') then
      y:= x;
  elsif x in('a','b','c','d','e','f') then
      y:= ascii(x) -87;
  else
      y:= null;
  end if;
  return y;
end;
questa funzione converte una cifra in formato esadecimale (il carattere x ricevuto come parametro in input) nel numero decimale corrispondente.

Sono da notare:
- la parola chiave return nella dichiarazione della funzione, seguita dal tipo dato che restituisce la funzione (in questo caso number)
- la sintassi if - elsif - else - end if
- la funzione ascii(), che ritorna il codice ASCII del carattere
- il return finale, che termina la funzione e assegna il valore di ritorno;

dopo aver creato questa funzione, potremo scrivere (ad es.) 
select hex2dec ('a') from tab1;
il risultato che otterremo sarà 10; 
select hex2dec ('A') from tab1;
in questo caso la funzione ritornerà null (visualizzato come stringa vuota)

avremmo potuto scrivere la stessa funzione in questo modo: 
create function hex2dec (x char ) return number is
begin
  if x in ('0','1','2','3','4','5','6','7','8','9') then
     return x;
  elsif x in('a','b','c','d','e','f') then
     return ascii(x) -87;
  else
     return null;
  end if;
end;
in questo modo la funzione termina appena viene verificata una delle 3 condizioni;
benchè più breve da scrivere (e anche da eseguire ) questo stile di programmazione è da evitare (nel manuale PL/SQL è definito poor programming practice); il punto di uscita da una procedura o funzione deve essere uno solo.


trigger



Come ho già detto prima, i trigger sono procedure che vengono eseguite da Oracle al verificarsi di insert, update o delete su una tabella; sono utilizzati in genere per
- effettuare controlli sulla consistenza di dati presenti in altre tabelle
- inserire o aggiornare dati derivati
- permettere il log di informazioni riguardo alla transazione, come l'utente o l'ora di una certa operazione

considerìamo un esempio: dopo aver aggiunto alla tabella1 una colonna di tipo date: 
alter table tabella1 add (data date);
creiamo il trigger trig1: 
create trigger trig1
before insert or update on tabella1
for each row
begin
  :new.data := sysdate;
end;
I trigger hanno 2 caratteristiche fondamentali e indipendenti, che permettono di classificarli in:
- trigger before e trigger after, a seconda che la procedura specificata dal trigger venga eseguita prima o dopo lo statement di insert, update o delete
- row trigger e statement trigger, a seconda che la procedura specificata dal trigger venga eseguita per ogni riga che è stata interessata dallo statement, o che venga eseguita indipendentemente dal numero di righe; quest'ultimo caso è in genere meno utile del precedente

All'interno della procedura che costituisce il trigger, chiamata trigger body, nel caso dei row triggers, è possibile referenziare i dati della riga attraverso i record denominati con una particolare sintassi :new e :old:
essi rappresentano i valori delle colonne della tabella rispettivamente prima e dopo l'esecuzione dello statement; il record :old è quindi disponibile solo nel caso di trigger after; in questo caso inoltre il record :new non può più essere modificato, ma solo usato in lettura.

Ritorniamo all'esempio precedente:
il trigger trig1 agisce quindi sulla tabella1, aggiornando la colonna date ogni volta che viene modificata o inserita una o più righe;
sysdate è una funzione standard Oracle che restituisce la data di sistema, completa di ore e secondi; in pratica quindi il trigger permette di registrare automaticamente la data di ogni modifica alla tabella.

Questo esempio mostra il minimo che è possibile fare usando un trigger: si possono ovviamente fare select, insert o update su altre tabelle (sulle quali possono essere definiti altri trigger), richiamare function o procedure che eseguono operazioni complesse, e in generale qualsiasi operazione sia possibile fare all'interno di una procedura.

Una volta creato, il trigger può essere eventualmente o temporaneamente disabilitato: 
alter trigger trig1 disable
(o riabilitato:) 
alter trigger trig1 enable
Oracle garantisce che l'esecuzione dei trigger
- avvenga sempre nello stesso ordine, nel caso in cui uno statement provochi l'esecuzione di più trigger (row e statement triggers)
- rispetti tutti gli integrity constraints definiti
- permetta comunque read-consistent queries

Non è possibile quindi usare la commit all'interno del trigger body: la transazione potrà essere committata solo dall'"esterno" del trigger.


SQL, funzioni standard



Le funzioni standard di oracle possono essere raggruppate in funzioni:
  • matematiche
  • che operano su stringhe o caratteri
  • che operano su date
  • di sistema
  • di gestione dei dati
  • di conversione
    • Le funzioni matematiche più utilizzate: abs(x): valore assoluto di x

    mod(x,y): resto di x/y

    sign(x): 1 se x è positivo, 0 se è 0 , altrimenti -1

    round(x[,y]) e trunc(x[,y]): x rispettivamente arrotondato o troncato all'intero più vicino; se è presente un valore y, indica a quanti decimali arrotondare o troncare x
    (qui e nel seguito sono indicati tra parentesi quadre i parametri opzionali)

    esistono poi le più classiche funzioni trigonometriche:
    sin, cos, tan, asin, acos, atan, sinh, cosh, tanh
    o matematiche: ln, exp, log, sqrt, power
    Le principali funzioni che operano su stringhe: ascii(x): il codice ASCII del carattere x (o del primo carattere di x se è una stringa);

    chr(x): il carattere avente codice x (ASCII se è usata questa codifica per il DB)

    lower(x) e upper(x): x in caratteri minuscoli o maiuscoli

    ltrim(x[,z]) e rtrim(x[,z]): x privato di eventuali caratteri z nella parte iniziale (left trim) o finale (right trim); se non presente, il default per z è un carattere blank

    lpad(x,y[,z]) e rpad(x,y[,z]): la stringa x riempita con i caratteri z fino alla posizione y, a sinistra o a destra; come prima, il default per z è blank

    substr(x,y[,z]): la parte della stringa x che comincia alla posizione y ed è lunga z; se z non è presente, si intende fino alla fine di x;

    instr(x,y[,n[,m]]): la posizione della sottostringa y in x, cominciando la ricerca dalla posizione n e considerando la m-esima occorrenza; se non presenti, per n e m il default è 1.

    length(x): la lunghezza in caratteri di x
    Le principali funzioni che operano sulle date: add_months(x,y): la data x aumentata di y mesi

    last_day(x): la data corrispondente all'ultimo giorno del mese per la data x

    sysdate: la data corrente del sistema, compresi ore, minuti e secondi

    months_between(x,y); il numero di mesi intercorrente tra le data x e la data y

    trunc(x,f) e round(x,f): le stesse funzioni utilizzabili per troncare o arrotondare valori numerici, possono essere usate per il tipo date: f deve specificare un formato valido
    Le funzioni di sistema (generalmente poco usate): rowid: un valore che identifica univocamente una riga in una tabella

    rowidtochar(x) e chartorowid(x): convertono un rowid in una stringa visualizzabile (avente un formato standard di 18 caratteri) e viceversa

    rownum: il numero di riga di un result set; usata di solito per limitare la quantità di righe che si vuole ottenere

    hextoraw(x) e rawtohex(x): convertono un valore esadecimale nel byte corrispondente e viceversa

    usere uid: il nome dell'utente e un suo identificativo univoco numerico     Le principali funzioni di gestione dei dati: decode(x,y1,z1[y2,z2[,..,yn,zn]][,def]): se x vale y1 ritorna z1, se vale y2 ritorna z2, ecc. per gli n valori (possono essere presenti fino a 128 coppie di valori); se più yn sono uguali a x, viene ritornato il primo zn corrispondente; se nessuno degli yn è uguala a x viene restituito def, se presente: altrimenti null

    nvl(x,y): (abbreviazione di null value), se x è null ritorna y, altrimenti x

    greatest(x1,x2[,..xn]) e least(x1,x2[,..xn]): rispettivamente il massimo e il minimo della lista di valori     Le funzioni di conversione e formattazione sono tra le più utilizzate ed è fondamentale capirne i meccanismi:
    Oracle converte automaticamente i tipi dati in ogni caso in cui venga utilizzato un tipo che non concorda con l'operazione o la funzione che si vuole applicare: ad es.:
    declare
  x date;
  y number;
  z varchar2(20);
begin
  x:= sysdate;
  y:= 2;
  z:= x+y;
end;
    in questo caso la variabile z, di tipo varchar2, alla fine del blocco PL/SQL assumerà il valore della data corrente del sistema aumentata di 2 giorni e formattata nel formato di default definito per quella sessione;

    tutte le funzioni di conversione hanno, come secondo parametro opzionale, il formato in cui convertire o in cui è espresso il valore: to_char(x,f): converte il numero o data x in una stringa avente il formato f

    to_number(x,f): converte la stringa x, rappresentante un valore numerico nel formato f, nel numero corrispondente

    to_date(x,f): converte la stringa o numero x, avente un formato data valido f, in una data



    Termina qui, per ora, questa introduzione al PL/SQL; gli argomenti trattati permettono di avere una panoramica molto superficiale del PL/SQL; il successivo approfondimento proposto nei link ai manuali on line permette già di cominciare a fare pratica e di orientarsi nel mondo Oracle...