Se hai già avuto modo di lavorare su database Oracle, è possibile che tu abbia sentito parlare di PL-SQL.

Acronimo di Procedural Language SQL, il PL-SQL è un vero e proprio linguaggio procedurale messo a disposizione da Oracle per estendere e completare le funzionalità di SQL all’interno del suo famoso database.

Il concetto di linguaggio procedurale non è insolito e quasi tutti i database più famosi ne supportano una qualche versione. Ad esempio, in SQL Server Microsoft c’è il Transact-SQL (o T-SQL).

Ma perché il PL-SQL, ed in generale i linguaggi procedurali, sono così utili?

Per capirlo è bene soffermarci su una caratteristica peculiare del linguaggio SQL, ovvero il fatto che da solo non permette di costruire dei programmi, ovvero delle procedure che siano in grado di effettuare operazioni complesse e correlate tra di loro.

Mentre il classico SQL viene generalmente utilizzato per eseguire operazioni atomiche come modificare e interrogare i dati del database, il PL-SQL viene a colmare le carenze di SQL migliorandone le caratteristiche.

Come la maggior parte dei linguaggi procedurali, il PL-SQL mette a disposizione, oltre a tutti i costrutti dell’SQL, anche i controlli condizionali, i cicli iterativi, la gestione delle variabili e delle costanti, la gestione delle eccezioni, delle procedure, delle funzioni, ecc.

In questa guida al pl-sql per principianti, desidero spiegarti quali sono le funzionalità più importanti che devi conoscere per essere in grado di creare da zero un programma PL-SQL completo e funzionante.

Prima di iniziare

Non essendo un corso approfondito, non tratterò ogni singolo aspetto o funzionalità del linguaggio PL-SQL.

Piuttosto, mi soffermerò su quei concetti (pochi, non spaventarti) che sono fondamentali per acquisire una competenza minima ma sufficiente per fare la maggior parte delle cose.

L’unico prerequisito è una conoscenza di base del linguaggio SQL e dei suoi concetti principali.

Nel giro di un paio d’ore, se mi seguirai con attenzione, sarai in grado di sviluppare il tuo primo programma PL-SQL e potrai creare applicazione Oracle APEX belle e funzionali.

Devi sapere, infatti, che il PL-SQL è una delle competenze fondamentali che devi conoscere se vuoi creare applicazioni complesse in Oracle APEX.

Introduzione

Quando si è alle prese con la creazione di applicazioni web basate sui dati (data-centric web applications) è normale porre una certa attenzione alla progettazione dello schema logico e, allo stesso tempo, all’implementazione di tutte quelle funzionalità necessarie per leggere e manipolare i dati.

Con Oracle APEX creare applicazioni in grado di inserire, modificare e cancellare records dal database è un gioco da ragazzi.

Tuttavia, potresti aver bisogno di progettare applicazioni più complesse che siano in grado di svolgere operazioni sui dati che vanno oltre il semplice INSERT o UPDATE.

I modi che hai a disposizione per assolvere a questo compito sono molteplici e dipendono, ovviamente, dalla piattaforma sulla quale sviluppi.

Se stai creando una applicazione basata su database Oracle puoi usare il PL/SQL che mette a disposizione, oltre a tutti i costrutti dell’SQL, anche i controlli condizionali, i cicli iterativi, la gestione di variabili e la gestione delle eccezioni.

Inoltre, è possibile raggruppare il codice in particolari oggetti (procedure e funzioni) che a loro volta possono essere raccolte in packages per creare delle vere e proprie librerie.

Non spaventarti, tra poco tutti questi concetti saranno più chiari.

Se non hai un database Oracle a disposizione non devi preoccuparti: Oracle Cloud Free Tier ti permette di sviluppare, testare e implementare applicazioni su Oracle Cloud, gratuitamente.

Per quanto riguarda l’ambiente di sviluppo puoi usare SQL Developer (preferibile a mio avviso) oppure lavorare direttamente in Oracle APEX usando la funzionalità SQL Workshop.

Struttura di Base di un blocco PL/SQL

Un concetto centrale del linguaggio PL/SQL è quello di blocco al cui interno è possibile scrivere le diverse istruzioni SQL nonché i vari costrutti “accessori” messi a disposizione da questo linguaggio di programmazione.

Un blocco può contenere la dichiarazione di costanti e variabili, uno o più controlli condizionali utili per indirizzare il lavoro delle nostre query, cicli iterativi con i quali “scorrere” i record delle nostre tabelle, la gestione delle eccezioni, i cursori (che tratteremo specificatamente in seguito), chiamate ad altri blocchi, ecc.

I blocchi a loro volta possono essere contenuti in altri blocchi, piuttosto che in store procedures e functions. I packages sono delle librerie di procedure e funzioni e vengono solitamente usati per raggruppare logicamente queste ultime.

Ad esempio ci sono packages predefiniti di Oracle come quelli per la manipolazione di files, le routines di input e output ecc.).

Ogni blocco PL/SQL inizia con la parola chiave BEGIN e termina con la parola chiave END ed ogni istruzione compresa all’interno del blocco deve terminare con il punto e virgola.

Avremo quindi una sintassi di questo tipo

DECLARE
     dichiarazioni_variabile_1;
     dichiarazioni_variabile_1;
     dichiarazione_cursore_1;
BEGIN  
     istruzione_1;
     istruzione_2;
EXCEPTION
     istruzione_3;
END;

La struttura standard di un blocco PL-SQL è costituita da 3 parti ben distinte:

• DECLARE: sezione di dichiarazione, dove andrai a dichiarare variabili e i cursori (tra poco capirai cosa sono) che userai nel programma.
• BEGIN: sezione di esecuzione. In questo punto andrai a scrivere le istruzioni che il tuo programma deve eseguire.
• EXCEPTION: dove andrai a scrivere il codice per gestire le eccezioni e gli errori che vengono lanciati

Le sezioni DECLARE e EXCEPTIONS sono facoltative, mentre la fine del blocco viene indicata con la parola chiave END.

Puoi annidare dei blocchi, ovvero definire un blocco che a sua volta contiene altri blocchi PL-SQL.

Potremo quindi avere qualcosa del tipo:

DECLARE
   istruzioni;
BEGIN
   istruzioni;
   istruzioni;
   /* inizio blocco annidato*/
   DECLARE
     istruzioni;
   BEGIN
     istruzioni;
     istruzioni;
   EXCEPTION
       istruzioni;
   END;
   /* fine blocco annidato*/
EXCEPTION
   istruzioni;
END;

All’interno di un blocco possono essere presenti dei commenti. Se il commento si estende su di una sola riga potremo scrivere la nostra riga preceduta da 2 trattini come di seguito:

--commento su una sola riga

Se invece il commento si estende su più righe, piuttosto che far precedere ogni riga dai trattini come sopra, scriveremo tutto l’insieme tra le coppie /* e */ come di seguito:

/* questo è un commento  piuttosto lungo che si sviluppa su più righe */

Hello Word in PL-SQL

Prima di andare avanti, credo possa essere utile mettere da subito in pratica quanto hai imparato fino a questo momento creando la tua prima applicazione PL-SQL.

Scoprirai che non c’è nulla di complicato.

• Collegati al tuo database Oracle usando SQL Developer
• Apri un foglio di lavoro
• Da menù Visualizza seleziona Output DBMS: serve per visualizzare un finestra in cui andremo a stampare dei messaggi a video, utile soprattutto per il debug dei programmi.
• Clicca il pulsante Abilitare DBMS_OUTPUT per la Connessione

• Vai nel foglio di lavoro ed inserisci il seguente codice PL-SQL

declare
     l_message varchar2(500);
 begin
     l_message := 'Hello Word!';
     dbms_output.put_line(l_message);
end;

• Clicca esegui per eseguire il programma

• Nella finestra Output Script dovresti leggere il messaggio “Procedura PL/SQL completata correttamente” mentre nella finestra Output DBMS dovresti leggere la stringa “Hello Word!”

Complimenti, hai realizzato il tuo primo programma in PL-SQL!

Prima di andare avanti vale la pena soffermarsi un minuto su quello che abbiamo appena fatto.

• Quello che hai scritto è un blocco PL-SQL dove sono facilmente riconoscibili le sezioni DECLARE e BEGIN.

• Nella sezione DECLARE abbiamo dichiarato una variabile di nome l_message e ne abbiamo specificato il tipo ossia varchar2(500).
• Nella sezione BEGIN abbiamo assegnato alla variabile l_message un valore statico, ossia la stringa Hello Word! e usando l’istruzione dbms_output.put_line abbiamo stampato il valore della variabile l_message.

Nei paragrafi successivi andremo ad approfondire e estendere questi concetti.

Sezione delle dichiarazioni

Dichiarazione delle Variabili

Tutte le variabili e le costanti (così come anche i cursori che vedremo tra poco) che vuoi utilizzare all’interno di in un blocco PL/SQL devono essere dichiarate nella sezione di DECLARE del blocco.

Ricorda che una variabile dichiarata può essere usata solo nel blocco all’interno del quale è stata dichiarata ed eventualmente nei sotto-blocchi annidati.

La sintassi generale per la dichiarazione delle variabili è la seguente, dove la dichiarazione del tipo può essere opzionalmente seguita da una assegnazione di inizializzazione.

<nome_variabile> [costant] <tipo di dati> [not null] [:=<espressione>];

Ecco alcuni esempi standard di dichiarazioni di variabili.

DECLARE
  data_nascita DATE;
  nome VARCHAR2(80);
  stage	BOOLEAN;
  ruolo VARCHAR2(20) := 'Impiegato';
BEGIN
  ...
  ...
  ...
END;

La variabile data_nascita è di tipo DATE mentre la variabile nome è di tipo VARCHAR2 con una dimensione massima di 80 byte. La variabile ruolo è stata inizializzata con un valore di default.

Finora abbiamo visto delle variabili definite con tipo dato standard (DATE, VARCHAR2, BOOLEAN).

Tuttavia è bene sapere che puoi definire delle variabili con tipi dati complessi, che dipendono dinamicamente dal tipo di dato estratto delle tabelle.

Facciamo un esempio pratico: supponiamo che tu abbia definito nel database una tabella di anagrafica prodotti strutturata come segue.

CREATE TABLE PRODOTTO(
   ID NUMBER,
   DESCRIZIONE VARCHAR(20),
   FORNITORE VARCHAR(20),
   COSTO NUMBER
);

Se nel tuo programma PL-SQL vuoi salvare all’interno di una variabile i dati recuperati da una query che interroga questa tabella devi scrivere un programma simile a questo:

DECLARE
  descrizione_prodotto VARCHAR2(80);
  fornitore_prodotto VARCHAR2(80);
  costo_prodotto NUMBER;
BEGIN
  SELECT descrizione 
    INTO descrizione_prodotto /* questa è una assegnazione */
    FROM prodotto
   WHERE id = 1;
END;

Ti faccio notare che le variabili che hai dichiarato devono avere lo stesso datatype delle colonne della tabella, ovvero dovremo assicurarci che il tipo dato del campo e quello della nostra variabile siano compatibili se non equivalenti e che nel tempo non cambi. In caso contrario il rischio è che il programma possa andare in errore.

Il modo più sicuro di fare ciò è quello di utilizzare le cosiddette dichiarazioni ancorate che sono dichiarazioni in cui si fa esplicito riferimento al tipo di dato della colonna interessata.

Nel nostro caso, quindi, il programma può essere scritto in questo modo

DECLARE
  descrizione_prodotto PRODOTTO.DESCRIZIONE%TYPE;
  fornitore_prodotto PRODOTTO.FORNITORE%TYPE;
  costo_prodotto PRODOTTO.COSTO%TYPE;
BEGIN
  SELECT descrizione 
    INTO descrizione_prodotto /* questa è una assegnazione */
    FROM prodotto
   WHERE id = 1;
END;

Possiamo anche dichiarare una variabile in grado di memorizzare tutto il record di una tabella.

DECLARE
  descrizione_prodotto PRODOTTO.DESCRIZIONE%TYPE;
  fornitore_prodotto PRODOTTO.FORNITORE%TYPE;
  costo_prodotto PRODOTTO.COSTO%TYPE;
  record_prodotto PRODOTTO%ROWTYPE;
BEGIN
  SELECT descrizione 
    INTO descrizione_prodotto /* questa è una assegnazione */
    FROM prodotto
   WHERE id = 1;
  SELECT * 
    INTO record_prodotto  /* questa è una assegnazione */
    FROM prodotto
   WHERE id = 1;
END;

Dichiarazione dei Cursori

Un cursore è una struttura che permette di scorrere le righe restituite da una query. Essi possono essere di sola lettura o, se l’implementazione lo consente, possono essere usati per modificare o cancellare le righe.

Per poter utilizzare un cursore in un programma PL-SQL va prima di tutto dichiarato nella sezione DECLARE usando la sintassi seguente:

CURSOR <nome_cursore> IS
<SQL_statement>;

Facciamo un esempio. Facendo sempre riferimento alla tabella PRODOTTO, supponiamo di voler fare delle operazioni sui prodotti che hanno un costo maggiore o uguale a 50 EUR.

La definizione de cursore sarà qualcosa di simile a questo:

DECLARE
  CURSOR prodotti IS
  SELECT *
    FROM PRODOTTO
   WHERE COSTO >= 50;
BEGIN
  FOR prodotto IN prodotti LOOP
     ... istruzioni
  END LOOP;
END;

Nell’esempio avrai notato una istruzione FOR: è uno dei modi che Oracle mette a disposizione per navigare un cursore.

Non è l’unico ma è sicuramente uno dei più comuni ed immediati; in ogni caso ne parleremo tra poco.

Blocco di Programma

Dopo aver visto come dichiarare una variabile ed un cursore, possiamo concentrare la nostra attenzione sulla sezione di esecuzione di un blocco PL-SQL, ovvero dove andrai a scrivere le istruzioni che il tuo programma deve eseguire.

Prima di andare avanti è bene fare una precisazione.

In PL-SQL, come in altri linguaggi di programmazione, esistono centinaia di istruzioni che combinate tra di loro permettono di fare praticamente qualsiasi tipo di elaborazione.

Vederle tutte in questo momento sarebbe impensabile. D’altro canto questa è una Guida Facile al PL-SQL per Principianti, no?

Per cui se vuoi apprendere al meglio ogni suo aspetto ti consiglio di consultare le guide e i manuale che Oracle mette a tua disposizione.

Una punto di partenza è il portale che Oracle mette a disposizione degli sviluppatori, raggiungibile a questo link.

Assegnazioni ed espressioni

Partiamo dalla cosa più semplice da fare, ovvero come assegnare un valore ad una variabile.

L’istruzione è molto semplice è può essere fatta in due modi.

Il primo metodo consiste con l’operazione di assegnazione diretta, tipicamente usata per assegnare un valore di default, usando l’operatore :=

Nel seguente esempio ho dichiarato la variabile l_costo_prodotto e l’ho inizializzata con il valore 0

DECLARE
   l_costo_prodotto NUMBER;
BEGIN
   l_variabile := 0; /* assegnazione diretta */
END;

Tuttavia, spesso dovrai assegnare ad una variabile un valore usando una query. In questo caso la sintassi è la seguente

DECLARE
   l_costo_prodotto NUMBER;
BEGIN
   l_costo_prodotto := 0; /* assegnazione diretta */
   SELECT costo
     INTO l_costo_prodotto /* assegnazione tramite query */
     FROM prodotto
    WHERE id = 1;
END;

Così facendo la nostra variable l_costo_prodotto avrà come valore il costo del prodotto identificato dalla query.

Per fare un operazione del genere dovremo essere certi, però, che la query ritorni un unico valore, altrimenti la nostra assegnazione andrà in errore generando una eccezione che causerebbe l’interruzione del programma.

Per questo motivo, potrebbe aver senso gestire nel blocco le eccezioni che potrebbero essere generate dal programma quando in una operazione di assegnazione non viene restituito un solo valore.

Ecco come fare:

DECLARE
    l_costo_prodotto NUMBER;
BEGIN
    l_costo_prodotto  := 0;
   
    SELECT costo
      INTO l_costo_prodotto 
      FROM prodotto
     WHERE id = 1;
EXCEPTION
    WHEN no_data_found THEN
        /* se la query non restituisce righe, vengono eseguite queste istruzioni */
        l_costo_prodotto := NULL;
    WHEN too_many_rows THEN
        /* se la restituisce più di una riga, vengono eseguite queste istruzioni */
        l_costo_prodotto := NULL;
    WHEN others THEN
        l_costo_prodotto := NULL;
END;

Nel frammento di codice che ti ho mostrato è stato inserito la sezione opzionale EXCEPTION che permette di specificare quali istruzioni il programma deve eseguire qualora venga generato un errore.

Nello specifico, ho gestito due casistiche particolari di errore:

• no_data_found: la query non restituisce record
• too_many_rows: la query restituisce più di un record

In realtà esistono tanti altri tipi di errore che un programma PL-SQL può scatenare. Per questo motivo ho messo una terza casistica (others) che serve a specificare cosa deve fare il programma in tutti gli altri casi diversi da quelli già gestiti.

Per concludere, quando assegni una variabile puoi usare delle espressioni, costanti e operazioni aritmetiche in generale.

Alcuni esempi:

• counter := 0;
• counter := counter * 1.25;
• counter := counter + 1;
• diff_costo := abs(costo_1 – costo_2)/Costo1;
• nome_prodotto := ‘xyx’ ||’abc’

Uso dei Cursori

Supponiamo tu voglia creare un programma che deve stampare a video l’elenco di tutti i prodotti che hanno un costo maggiore o uguale a 100 EUR.

Non puoi sapere a priori quali sono i records da stampare; inoltre, questa informazione può cambiare nel tempo quindi è necessario progettare un programma che sia in grado di assolvere a questo compito, indipendentemente dai dati.

Il PL/SQL mette a disposizione diversi strumenti che consentono di iterare una serie di records: quello più comune è il cursore dati.

Vediamo qualche esempio di utilizzo.

Cursori Semplici

Esistono due tipologie principali di cursore: i cursori semplici, che vediamo in questo paragrafo, ed i cursori parametrici che tratteremo nel prossimo.

Procediamo con calma.

Prima di tutto, un cursore va dichiarato nella sezione DECLARE assegnandogli un nome.

La sintassi per dichiarare un cursore è questa:

DECLARE
  CURSOR <nome_cursore> IS
  <query>;
BEGIN
  <istruzione>;
  ...;
END;

Quindi, il cursore che useremo per stampare a video i prodotti che hanno un costo superiore a 100 EUR sarà definito in questo modo:

DECLARE
  CURSOR elenco_prodotti IS
  SELECT descrizione, fornitore, costo
    FROM PRODOTTO
   WHERE costo >= 100;
   rec_elenco_prodotti elenco_prodotti%ROWTYPE;
BEGIN
  <istruzione>;
END;

Ti faccio notare che oltre alla dichiarazione del cursore ho inserito un’altra istruzione di dichiarazione

rec_elenco_prodotti elenco_prodotti%ROWTYPE;

Questa non è altro che la dichiarazione di una variabile di nome rec_elenco_prodotti che ci servirà successivamente per scorrere il cursore.

Per essere utilizzato, un cursore deve essere aperto, valorizzato ed infine chiuso.

Aprire un cursore comporta l’esecuzione della query che abbiamo scritto nella sua definizione. Per eseguire questa operazione scriveremo questa istruzione

OPEN elenco_prodotti;

A seguito dell’istruzione di OPEN, il cursore sarà inizializzato e possiamo scorrere le righe in questo modo:

LOOP

    FETCH elenco_prodotti INTO rec_elenco_prodotti;
    <istruzione>;
    ...;

END LOOP;

L’istruzione LOOP / END LOOP serve per gestire un ciclo iterativo.

L’istruzione FETCH serve per salvare il record corrente nella variabile rec_elenco_prodotti.

Una volta utilizzato, il cursore dovrà essere chiuso nel seguente modo:

CLOSE elenco_prodotti ;

Ricapitolando, il nostro blocco di codice sarà qualcosa del tipo.

DECLARE
  /* dichiarazione del cursore */
  CURSOR elenco_prodotti IS
  SELECT descrizione, fornitore, costo
    FROM PRODOTTO
   WHERE costo >= 100;
   
   /* dichiarazione della variabile per recuperare la riga */
   rec_elenco_prodotti elenco_prodotti%ROWTYPE;
BEGIN
  /* apertura del cursore */
  OPEN elenco_prodotti;
  /* ciclo sul cursore */
  LOOP
    FETCH elenco_prodotti INTO rec_elenco_prodotti;
      dbms_output.put_line('Prodotto: '||rec_elenco_prodotti.descrizione);
  END LOOP;
  
  /* chiusura del cursore */
  CLOSE elenco_prodotti;
END;

Cursori Parametrici

Quali e quanti record vengono restituiti da un cursore può dipendere da valori costanti oppure da parametri.

Nell’esempio che abbiamo fatto poche righe sopra, la soglia dei 100 EUR è un valore costante fissato nella query del cursore.

Tuttavia, se volessimo sviluppare un programma che stampa quali sono i prodotti che hanno un costo superiore ad un valore non stabilito a priori, dovremmo utilizzare un cursore parametrico.

Facciamo un esempio.

DECLARE
  /* variabili del programma */
  l_soglia NUMBER;
  
  /* dichiarazione del cursore */
  CURSOR elenco_prodotti(p_soglia NUMBER) IS
  SELECT descrizione, fornitore, costo
    FROM PRODOTTO
   WHERE costo >= p_soglia;
   
   /* dichiarazione della variabile per recuperare la riga */
   rec_elenco_prodotti elenco_prodotti%ROWTYPE;
BEGIN
  l_soglia := 150;
  /* apertura del cursore */
  OPEN elenco_prodotti(l_soglia);
  /* ciclo sul cursore */
  LOOP
    FETCH elenco_prodotti INTO rec_elenco_prodotti;
      dbms_output.put_line('Prodotto: '||rec_elenco_prodotti.descrizione);
  END LOOP;
  /* chiusura del cursore */
  CLOSE elenco_prodotti;
END;

Nello script PL-SQL che ho riportato la query del cursore contiene questa condizione

WHERE costo >= p_soglia

p_soglia è un parametro, dichiarato nella definizione del cursore

CURSOR elenco_prodotti(p_soglia NUMBER) IS
  SELECT descrizione, fornitore, costo
    FROM PRODOTTO
   WHERE costo >= p_soglia;

Ancora sui Cursori

Negli esempio che abbiamo fatto fino a questo punto, per usare un cursore è necessario inserire le istruzioni per inizializzare il cursore, navigarlo ed infine chiuderlo: OPEN, FETCH e CLOSE.

Se il tuo programma PL-SQL ha molte righe di codice, potrebbe essere interessante riuscire ad ottimizzarne la scrittura.

Ebbene, devi sapere che esiste un modo più smart di navigare un cursore, usando in maniera opportuna l’istruzione FOR.

Ecco un esempio:

DECLARE
  /* dichiarazione del cursore */
  CURSOR elenco_prodotti IS
  SELECT descrizione, fornitore, costo
    FROM PRODOTTO
   WHERE costo >= 100;
BEGIN
  /* apertura del cursore */
  FOR rec_elenco_prodotti IN elenco_prodotti;
  /* ciclo sul cursore */
  LOOP
      dbms_output.put_line('Prodotto: '||rec_elenco_prodotti.descrizione);
  END LOOP;
END;

Il ciclo FOR dichiara implicitamente una variabile di tipo record che ha la stessa struttura del cursore, apre il cursore, legge i valori e, infine, chiude il cursore quando sono state processate tutte le righe.

In pratica il ciclo FOR si occupa di tutte eseguire quelle operazioni di servizio (OPEN, FETCH e CLOSE) che altrimenti dovrebbero essere dichiarate in maniera esplicita.

Funzioni, Procedure e Packages

Creare una procedura in PL-SQL

Fino a questo punto abbiamo parlato di programmi PL-SQL ma di fatto abbiamo sempre scritto degli script che per eseguire le operazioni previste devono essere eseguiti nella loro interezza.

Normalmente, però, quello che fanno i programmatori è scrivere il codice all’interno di programmi che hanno un nome, dei parametri e che possono essere eseguiti richiamandone il nome.

Una procedura PL-SQL è un blocco di codice, identificato da un nome univoco, che viene salvata (in gergo si dice compilata) direttamente nel database Oracle.

Una procedura PL-SQL può essere richiamata da altri blocchi PL/SQL, da altre procedure e da altre funzioni utilizzando direttamente il suo nome, rendendola di fatto riutilizzabile dove più ci piace.

In questo modo se la tua APP Oracle APEX deve richiamare da più punti lo stesso programma PL-SQL, non dovrai riscrivere ogni volta il codice nella sua totalità. Sarà sufficiente compilare una sola volta la procedura e richiamarla attraverso il suo nome dove serve.

La sintassi per creare una procedura PL-SQL è la seguente:

CREATE OR REPLACE PROCEDURE <nome_procedura> (<lista di parametri>) IS
<dichiarazione>
BEGIN
  <sequenza di istruzioni>
EXCEPTION
  <gestione eccezione>
END <nome procedura>;

Andiamo ad analizzarla un po’ più nel dettaglio

• CREATE PROCEDURE, obbligatoria, indica che stiamo definendo una procedura;
• La clausola OR REPLACE, opzionale, serve per ricreare la procedura nel caso in cui essa sia già stata definita in precedenza;
• <nome_procedura>, obbligatoria, indica il nome con cui la procedura sarà identificata;
• <lista parametri>, opzionale, consente di inserire una serie di parametri che possono essere passati alla procedura.

Volendo fare subito un esempio concreto, la procedura che stampa a video gli articoli con un costo superiore ad un determinato valore sarà:

CREATE OR REPLACE PROCEDURE stampa_articoli_costosi(p_soglia IN NUMBER) IS
  /* dichiarazione del cursore */
  CURSOR elenco_prodotti IS
  SELECT descrizione, fornitore, costo
    FROM PRODOTTO
   WHERE costo >= p_soglia ;
BEGIN
  FOR rec_elenco_prodotti IN elenco_prodotti;
  LOOP
      dbms_output.put_line('Prodotto: '||rec_elenco_prodotti.descrizione);
  END LOOP;
END;

I parametri di una procedura vanno dichiarati con questa sintassi

<nome_parametro> [IN | OUT | IN OUT] <tipo_dato>

dove tipo_dato indentifica il tipo di parametro che la procedura si aspetta.

Valori classici sono VARCHAR2 oppure NUMBER (possono essere usati anche tipi dato complessi ma per il momento direi che va bene così).

L’unico punto di attenzione nella definizione dei parametri è che non va specificata la dimensione o la precisione (nel caso dei NUMBER).

Ad esempio, se scrivessimo come tipo dato VARCHAR2(30) avremmo un errore in fase di compilazione: si deve specificare semplicemente VARCHAR2.

La clausola IN, OUT, IN OUT, opzionale, specifica il modo nel quale il parametro è utilizzato. Per default, l’utilizzo di un parametro è in modalità IN.

• IN implica che il parametro può essere utilizzato nella procedura ma non può essere modificato;
• OUT implica che ad esso può essere assegnato un valore nella procedura ma non può essere utilizzato;
• IN OUT implica che il parametro può essere utilizzato sia in lettura che scrittura.

La clausola IS sostituisce, in sostanza, la DECLARE vista in precedenza. Di qui in avanti, vale quanto già visto nella trattazione dei blocchi.

Una procedura può essere richiamata utilizzando il comando CALL nel seguente modo:

CALL nome_procedura(<lista parametri>);

oppure all’interno di un altro blocco PL-SQL in questo modo:

BEGIN
   nome_procedura(<lista parametri>);
END;

Ad esempio, questo è un modo per richiamare la nostra procedura stampa_articoli_costosi.

BEGIN
   /* stampa i prodotti costosi */
   stampa_articoli_costosi(50);
   
   /* stampa i prodotti molto costosi */
   stampa_articoli_costosi(100);
END;

Una procedura può essere eliminata tramite il comando DROP nel seguente modo:

DROP nome_procedura;

Creare una funzione in PL-SQL

La dico in modo semplice perché di fatto è così: una funzione PL-SQL si differenzia da una procedura solo per la presenza di una clausola RETURN che specifica il tipo di dato restituito.

A differenza di una procedura, infatti, una funzione restituisce sempre un valore.

La sintassi della definizione di una funzione è la seguente:

CREATE OR REPLACE FUNCTION <nome_funzione> (<lista di parametri>) RETURN <tipo dato> IS
  <dichiarazione>
BEGIN
  <sequenza di istruzioni>
  RETURN <variabile>
EXCEPTION
  <gestione dell'eccezione>
END <nome funzione>;

Supponiamo di voler scrivere un programma PL-SQL che conta il numero di articoli che hanno un valore superiore ad un determinato importo e, allo stesso tempo, vogliamo stampare a video questa lista.

Possiamo utilizzare una funzione PL-SQL definita così:

CREATE OR REPLACE FUNCTION conta_articoli_costosi(p_soglia IN NUMBER) RETURN NUMBER IS
  /* dichiarazione del cursore */
  CURSOR elenco_prodotti IS
  SELECT descrizione, fornitore, costo
    FROM PRODOTTO
   WHERE costo >= p_soglia ;
  
  /* dichiarazione variabili */
  l_counter NUMBER := 0;
BEGIN
  l_counter := 0;
  FOR rec_elenco_prodotti IN elenco_prodotti;
  LOOP
      dbms_output.put_line('Prodotto: '||rec_elenco_prodotti.descrizione);
      l_counter := l_counter + 1;
  END LOOP;
  /* restituisci il valore al blocco chiamante */
  RETURN l_counter;
END;

Come puoi vedere, le differenze tra procedura e funzione sono davvero pochissime.

• CREATE FUNCTION, obbligatoria, indica che stiamo definendo una funzione.
• La clausola OR REPLACE, opzionale, serve per ricreare la funzione nel caso in cui essa è già stata definita in precedenza.
• La clausola <nome_funzione>, obbligatoria, indica il nome con cui la funzione sarà identificata.
• La clausola [(<lista parametri>)], opzionale, consente di inserire una serie di parametri che possono essere passati alla funzione.

Anche una funzione PL-SQL può essere richiamata da un blocco PL-SQL. Un esempio è questo:

DECLARE
   l_numero_articoli NUMBER;
BEGIN
   /* stampa i prodotti costosi */
   l_numero_articoli := conta_articoli_costosi(50);
   dbms_output.put_line('Gli articoli più costosi di 50 EUR sono '||l_numero_articoli );
   /* stampa i prodotti molto costosi */
   l_numero_articoli := conta_articoli_costosi(100);
   dbms_output.put_line('Gli articoli più costosi di 100 EUR sono '||l_numero_articoli );
END;

Creare un package in PL-SQL

Un package PL-SQL non introduce funzionalità particolari. Lo puoi considerare altresì come una raccolta di oggetti PL-SQL come funzioni, procedure, variabili, ecc.

L’utilità di un package sta nel fatto che consente di organizzare concettualmente i singoli programmi PL-SQL in una sorta di libreria.

Per creare un package PL-SQL dovremo prima di tutto salvare le sue specifiche (specification) e successivamente potremo compilarne il corpo (body).

Questa è la sintassi per dichiarare le specifiche di un package:

CREATE OR REPLACE PACKAGE <nome_package> IS
   PROCEDURE <nome_procedura>;
   FUNCTION <nome_funzione> RETURN <tipo_dato>;
END <nome_package>

Le specifiche contengono la parte pubblica del package, quindi accessibile a tutte le applicazioni.

Il body, invece, nasconde la parte implementativa, le variabili e gli oggetti privati. Questa è la sintassi per dichiarare il corpo di un package:

CREATE OR REPLACE PACKAGE BODY <nome_package> IS
   PROCEDURE <nome_procedura> IS
      <variabili_procedura>
   BEGIN
      <codice_procedura>
   END;
   
   FUNCTION <nome_funzione> RETURN <tipo_dato> IS
      <variabili_funzione>   
   BEGIN
      <codice_funzione>
   END;
END <nome_package>

Nel body del package andrai a scrivere il codice del programma così come avresti fatto nel caso di una procedura standalone.

Ciò che devi tenere a mente è che la dichiarazione della funzione o procedura nel body deve essere esattamente identica a quella delle specifiche e che tutte le procedure e funzioni dichiarata nelle specifiche deve essere implementate nel body.

Facciamo un esempio pratico.

Supponiamo, ad esempio, di voler creare un package per la gestione dell’anagrafica prodotti e di voler includere 3 procedure. Una procedura che consente di creare un nuovo prodotto, una procedura che consente di aggiornare le informazioni di un prodotto ed una procedura che consente di eliminare un prodotto.

Questa è la definizione delle specifiche

CREATE or REPLACE PACKAGE product_api IS
   PROCEDURE create_product(p_descrizione IN  VARCHAR2, 
                            p_costo       IN  NUMBER, 
                            p_fornitore   IN  VARCHAR2,
                            p_id          IN  NUMBER);
   PROCEDURE update_product(p_descrizione IN  VARCHAR2, 
                            p_costo       IN  NUMBER, 
                            p_fornitore   IN  VARCHAR2,
                            p_id          IN  NUMBER);
   PROCEDURE delete_product(p_id IN  NUMBER);
END product_api;

Questo è il body del package

CREATE or REPLACE PACKAGE product_api IS
   PROCEDURE create_product(p_descrizione IN  VARCHAR2, 
                            p_costo       IN  NUMBER, 
                            p_fornitore   IN  VARCHAR2,
                            p_id          IN  NUMBER)
   IS
   BEGIN
      INSERT INTO PRODOTTO (id, descrizione, fornitore, costo) 
      VALUES (p_id, p_descrizione , p_fornitore, p_costo);
      COMMIT;
   END;
   PROCEDURE update_product(p_descrizione IN  VARCHAR2, 
                            p_costo       IN  NUMBER, 
                            p_fornitore   IN  VARCHAR2,
                            p_id          IN  NUMBER)
   IS
   BEGIN
      UPDATE PRODOTTO SET
       descrizione = p_descrizione, 
       fornitore   = p_fornitore, 
       costo       = p_costo        
      WHERE id = p_id;
      COMMIT;
   END;
   PROCEDURE delete_product(p_id IN  NUMBER)
   IS
   BEGIN
      DELETE FROM PRODOTTO WHERE id = p_id;
      COMMIT;
   END;
END product_api;

Per fare riferimento ad un oggetto (pubblico) che si trova all’interno del package è necessario anteporre al nome dell’oggetto quello del package, separati da un punto.

Questo vuol dire che per richiamare la procedura create_product dichiarata nelle specifiche del package dovrai scrivere qualcosa di simile a questo:

BEGIN
   product_api.create_product('DESCRIZIONE PRODOTTO',1000, 'Fornitore di test',1);
END;

Strutture condizionali in PL/SQL

In tutti gli esempi che abbiamo visto finora i programmi che abbiamo creato eseguono le istruzioni in rigida sequenza.

Eppure il PL-SQL permette di scrivere del codice molto più complesso ed efficiente.

In questo paragrafo vedremo alcuni degli operatori condizionali più comuni, utili per eseguire delle istruzioni piuttosto che altre a seconda dei valori assunti da alcune condizioni.

Questi costrutti (detti costrutti condizionali) vengono utilizzati per controllare il flusso di elaborazione della nostra sequenza di istruzioni indirizzandolo in un modo, piuttosto che in un altro, a seconda del verificarsi o meno di certe condizioni.

Il più semplice e più intuitivo, anche per chi ha una piccola esperienza di programmazione, è il costrutto IF.

Esso si compone delle seguenti parole chiave: If, Then, Else/Elsif, End If.
La forma più semplice in cui lo si andrà ad utilizzare è:

IF <condizione>
  THEN
    <istruzione>
END IF;

Vediamo di comprendere il significato di questo frammento di codice.

In sostanza, se la condizione è verificata (TRUE) viene eseguita l’istruzione (o il blocco di istruzioni) che si trovano tra la clausola THEN e la clausola END IF.

Leggermente più complessa è la forma del tipo:

IF <condizione>
  THEN
    <istruzione 1>
  ELSE
    <istruzione 2>
END IF;

Rispetto al primo esempio, vediamo che si è aggiunta una nuova clausola: ELSE.

Se la condizione è verificata (TRUE) verrà eseguita l’istruzione (blocco di istruzioni) che si trovano dopo la clausola THEN, altrimenti, se la condizione non si verifica (FALSE) oppure è NULL, verrà eseguita l’istruzione (blocco di istruzioni) che si trovano dopo la clausola ELSE.

In tutti i casi, il flusso riprende normalmente al primo comando successivo alla clausola END IF.

Di seguito, un piccolo esempio:

DECLARE
 var_nome VARCHAR2;
BEGIN
 SELECT Nome INTO var_nome FROM Anagrafica WHERE Cognome = 'Rossi';
 IF var_nome = 'Mario'
  THEN
   INSERT INTO Log (Messaggio) VALUES ('Il nome del Signor Rossi è Mario');
  ELSE
   INSERT INTO Log (Messaggio) VALUES ('Il nome del Signor Rossi NON è Mario');
 END IF;
END;

Vediamo ora una versione maggiormente articolata che sfrutta anche la clausola ELSIF:

IF <condizione 1> THEN
  <istruzione 1>
ELSIF <condizione 2> THEN
  <istruzione 2>
ELSIF <condizione 3> THEN
  <istruzione 3>
ELSIF <condizione 4> THEN
  <istruzione 4>
END IF;

Il significato di questo codice e facilmente intuibile sulla base di quanto detto fino a questo momento: se si verifica la <condizione 1> viene eseguita l’<istruzione 1> se si verifica la <condizione 2> viene eseguita l’<istruzione 2> e così via.

Sulla stessa falsariga di quanto appena visto è il costrutto CASE. La sintassi è del tipo:

CASE <variabile>
  WHEN <condizione 1> THEN
    <istruzione 1>

  WHEN <condizione 2> THEN
    <istruzione 2>

  WHEN <condizione 3> THEN
    <istruzione 3>

END CASE;

Sebbene possa sembrare alquanto differente, in sostanza esso non fa che prendere in esame una variabile su cui poi valuta le condizioni.

Verrà eseguita l’istruzione (il blocco di istruzioni) che si trova dopo la condizione che risulta verificata.

Gestire le eccezioni in PL/SQL

Quando all’interno del nostro blocco PL/SQL si verifica un errore viene scatenata un’eccezione. Le eccezioni possono essere distinte in due tipi:

• Eccezioni di sistema
• Eccezioni utente

Eccezioni di sistema

Gli errori più generici che si possono verificare durante l’esecuzione di blocchi PL/SQL vengono gestiti dalle eccezioni di sistema.

Esse si scatenano automaticamente quando si verificano specifici errori previsti da Oracle. Le eccezioni utente invece devono essere esplicitamente definite e poi attivate dal programmatore.

Vediamo di seguito la sintassi delle eccezioni:

BEGIN
  <istruzione>
  EXCEPTION
    WHEN <tipo_eccezione_1> THEN
      <istruzione>
    WHEN <tipo_eccezione_1> THEN
      <istruzione>
    WHEN OTHERS THEN
      <istruzione>
END;

Abbiamo una sezione del blocco dedicata alle eccezioni; ogni qualvolta si verifica una eccezione, tramite le clausole WHEN viene individuata quella del tipo verificatosi in modo tale da eseguire il codice più opportuno alla sua gestione.

Dato che non sempre è possibile determinare tutte le eccezioni che si possono verificare a seguito dell’esecuzione del nostro codice, il PL/SQL mette a disposizione la clausola WHEN OTHERS la quale gestisce, appunto, tutte le eccezioni non esplicitamente trattate dalle clausole che la precedono.

Una clausola WHEN può anche gestire più eccezioni, avremo quindi qualcosa del genere:

WHEN tipo_eccezione1 OR tipo_eccezione2 THEN......

Di seguito, un piccolo elenco delle eccezioni di sistema più comuni:

• NO_DATA_FOUND – un comando SELECT o FETCH non ha restituito nessuna tupla
• TOO_MANY_ROWS – una SELECT INTO ha restituito più di una tupla
• ZERO_DIVIDE – è stata tentata una divisione per 0

Vediamo adesso un semplice esempio che fa uso dell’eccezione NO_DATA_FOUND:

DECLARE
 var_nome VARCHAR2;
BEGIN
  SELECT Nome INTO var_nome FROM Anagrafica WHERE Cognome = 'TOPOLINO';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      INSERT INTO Log (Messaggio) VALUES ('Occorrenza non trovata.');
    WHEN OTHERS THEN
      null;
END;

Eccezioni utente

Come già accennato in precedenza, PL/SQL permette di definire delle exception proprie dell’utente.

Come per qualsiasi altra dichiarazione, essa deve avvenire nella sezione di dichiarazione. Avremo quindi:

DECLARE
  mia_eccezione	EXCEPTION;
BEGIN
  <istruzione>
END;

Le eccezione definite dall’utente non scattano automaticamente ma devono essere scatenate tramite il comando RAISE seguito dal nome dell’eccezione, quindi, nel nostro caso avremo:

RAISE mia_eccezione;

Vediamo un esempio completo:

DECLARE
  mia_eccezione	EXCEPTION;
BEGIN
  <istruzione>
  IF <condizione> THEN
    RAISE mia_eccezione;
  END IF;
EXCEPTION
  WHEN <tipo_eccezione_1> THEN <istruzione>;
  WHEN <tipo_eccezione_2> THEN <istruzione>;
  WHEN mia_eccezione THEN <istruzione>;
  WHEN OTHERS THEN <istruzione>;
END;

Conclusioni

Bene, siamo arrivati alla fine di questo breve (ma decisamente intenso) excursus sul linguaggio PL-SQL.

Mi auguro di averti aiutato a comprendere un po’ meglio le sue caratteristiche. Adesso dovresti avere in mano gli strumenti e le conoscenze di base necessarie per iniziare a sviluppare.

Essendo un importante linguaggio di programmazione, è ampiamente utilizzato dagli sviluppatori nello sviluppo di applicazioni moderne.

Condividi questo articolo se ti è piaciuto e se hai domande o hai bisogno di qualche approfondimento, fammelo sapere nei commenti!

Un abbraccio

Daniele