MySQL Stored Procedures

A partire dalla versione 5.0, MySQL ha colmato, tra le altre cose, una delle più grandi carenze che gli venivano imputate, ossia la mancanza delle Stored Procedures. Grazie all’introduzione delle cosidette "procedure archiviate", ora anche in MySQL è possibile creare – all’interno della stessa base dati – delle specie di "programmi" grazie ai quali è possibile eseguire operazioni complesse sui dati con un minor dispendio di risorse ed energie.

Possiamo quindi dire che, grazie alle Stored Procedures, il linguaggio SQL – per sua natura dichiarativo – viene "esteso" in senso procedurale (o imperativo).

I vantaggi dell’utilizzo delle Stored Procedures in MySQL sono molteplici:

contribuiscono ad una maggior efficenza in sede di sviluppo (grazie al principio del ri-utilizzo del codice);
migliorano l’astrazione (chi invoca la procedura può ignorarne i dettagli implementativi);
semplificano ed ottimizzano i rapporti tra il client (l’applicativo che utilizza i dati) ed il server (la base dati), riducendo, inoltre, lo scambio di dati in quanto il primo non dovrà inviare lunghe e complesse query ma, semplicemente, dovrà far riferimento al nome della procedura che desidera invocare;
aumentano il livello di sicurezza complessivo in quanto talune procedure sono eseguite direttamente all’interno del database (e non a livello dell’applicativo) ed è, pertanto, possibile studiare logiche di sicurezza più restrittive in merito all’accesso diretto ai dati.

In realtà, l’utilizzo di questi strumenti porta con se anche alcuni svantaggi, tra cui – soprattutto – un considerevole aumento nel consumo di risorse (CPU e memoria) a livello del database server.

Infine una puntualizzazione: se è vero, come abbiamo detto, che grazie alle Stored Procedures è possibile inserire all’interno del nostro database MySQL delle specie di "sub-programmi", è importante ricordarsi che questi non devono mai implementare funzionalità che non abbiano direttamente a che fare con i dati! Lo scopo delle Stored Procedures, infatti, non è quello di sostituirsi ai comuni linguaggi di programmazione, ma semplicemente quello di ottimizzare taluni processi di elaborazione strettamente legati ai dati.

Procedure e Funzioni

Le Stored Procedures di MySQL possono essere suddivise in due macro-gruppi:

Procedure: (Stored Procedures in senso stretto) ammettono dei parametri di input e/o di output ma non producono alcun valore di ritorno;
Funzioni: (anche dette Stored Functions) ammettono dei parametri di input e producono un valore di ritorno.

In questa lezione ci occuperemo esclusivamente delle prime (Stored Procedures in senso stretto) rinviando alla prossima lezione l’analisi delle Stored Functions.

Creare la prima stored procedure in MySQL

Per creare una procedura si utilizza il comando CREATE PROCEDURE seguito da nome della procedura che vogliamo creare. Vediamo di seguito, passo passo, come creare una prima Stored Procedure in MySQL.

Per prima cosa effettuiamo l’accesso, tramite linea di comando, al client mysql e selezioniamo il database all’interno del quale desideriamo creare la nostra Stored Procedure:

USE nomedatabase;

Ricordiamo, infatti, che una procedura deve "appartenere" ad uno specifico database.

Una volta selezionato il DB sul quale desideriamo lavorare, digitiamo:

DELIMITER $$;

Attraverso questa istruzione andiamo a cambiare provisoriamente il delimitatore da punto e virgola (;) a doppio dollaro ($$). La scelta del doppio dollaro è assolutamente arbitraria, potete scegliere quello che ritenete più opportuno.

La sostituzione del delimitatore ordinario si rende necessaria nella scrittura della nostra Stored Procedure al fine di poter utilizzare, al suo interno, il punto e virgola senza che l’interprete MySQL lo interpreti – mentre digitiamo – come la fine di un istruzione.

CREATE PROCEDURE TuttiGliAmici()
BEGIN
  SELECT *  FROM amici;
END $$

Attraverso questo codice abbiamo creato, attraverso lo statement CREATE PROCEDURE, una procedura col nome TuttiGliAmici. Le istruzioni della nostra procedura vengono inseriti tra le keyword BEGIN e END che delimitano, appunto il body della nostra procedura.

Si noti che la keyword END è seguita dal doppio dollaro ($$) che indica il termine dell’istruzione.

Per finire, ricordiamoci di ripristinare il delimitatore di default (;):

DELIMITER ;

La clausola DEFINER nella creazione di una Stored Procedure in MySQL

Mediante l’opzione DEFINER (non obbligatoria) è possibile specificare l’utente creatore (e perciò "proprietario") della Stored Procedure. In mancanza, il DBMS ne assegnerà la titolarità all’utente corrente (è come se avessimo dichiarato esplicitamente DEFINER = CURRENT_USER).

Vediamo un esempio:

CREATE DEFINER=`nomeutente`@`localhost` PROCEDURE ...

Nel nostro esempio abbiamo creato una nuova procedura accessibile per l’utente "nomeutente" connesso mediante localhost.

Invocare una Stored Procedure

Fino ad ora abbiamo visto come creare una semplice Stored Procedures, vediamo adesso come utilizzarla. Per richiamare una Stored Procedures si utilizza lo statement CALL seguito dal nome della procedura, ad esempio:

CALL TuttiGliAmici();

Nel nostro caso, questa linea di codice restituirà il recordset generato dalla nostra procedura che, come abbiamo visto, effettua una semplice query di SELECT all’interno della tabella "amici" del nostro database.

Utilizzo di parametri all’interno di una Stored Procedure

Nella definizione di una Stored Procedures in MySQL è anche possibile prevedere il passaggio di parametri. Questi possono essere di tre tipi:

IN: indica un argomento in ingresso nella routine; questo viene definito al momento dell’invocazione della procedura e non può essere modificato al suo interno;
OUT: indica un argomento il cui valore viene determinato all’uscita dalla procedura; tali valori saranno poi utilizzabili dagli utenti all’esterno della procedura;
INOUT: indica che un dato argomento è, allo stesso tempo, un valore in ingresso ed in uscita (e pertanto il suo valore è mutevole all’interno della procedura).

Vediamo ora un semplicissimo esempio di creazione di una procedura in MySQL avente un parametro IN, partendo dall’esempio visto in precedenza:

DELIMITER $$;

CREATE PROCEDURE AmiciNellaProvincia(IN prov VARCHAR(255))
BEGIN
  SELECT * FROM amici WHERE provincia = prov;
END $$

DELIMITER ;

Come credo sia piuttosto intuitivo, abbiamo creato una procedura che – attraverso un parametro in ingresso – seleziona solo gli amici residenti in una data provincia. Per introdurre il parametro in ingresso abbiamo utilizzato, tra le parentesi tonde, questa sintasi:

IN nome_parametro TIPO DI DATO

Vediamo ora come implementare una Stored Procedure con un parametro in uscita. Questo l’esempio:

DELIMITER $$;

CREATE PROCEDURE QuantiAmici(OUT totale INT)
BEGIN
  SELECT COUNT(*) INTO totale FROM amici;
END $$

DELIMITER ;

In questo caso, per richiamare la nostra procedura, utilizzeremo il seguente codice:

CALL QuantiAmici(@tot);
SELECT @tot;

In pratica abbiamo definito, come argomento di ritorno, una variabile @tot e, successivamente, ne abbiamo estratto il valore mediante SELECT.

Volendo avremmo potutro combinare entrambi gli esempi visiti sopra in questo modo:

DELIMITER $$;

CREATE PROCEDURE QuantiAmiciNellaProvincia(IN prov VARCHAR(255), OUT totale INT)
BEGIN
  SELECT COUNT(*) INTO totale FROM amici WHERE provincia = prov;
END $$

DELIMITER ;

Nell’esempio qui sopra, abbiamo definito una Stored Procedure con due parametri: il primo in ingresso ed il secondo in uscita. Per richiamare questa procedura:

CALL QuantiAmiciNellaProvincia('Milano',@tot);
SELECT @tot;

Per finire vediamo come implementare, in un tipico esempio di scuola, un parametro di tipo INOUT:

DELIMITER $$;

CREATE PROCEDURE NumeroAlQuadrato(INOUT n INT)
BEGIN
  SET n = n * n;
END $$

DELIMITER ;

Questa semplice procedura ammette un solo parametro numerico (di tipo INOUT) restituendono il valore al quadrato:

SET @n = 4;
CALL NumeroAlQuadrato(@n);
SELECT @n;

Il risultato sarà:

Come avete notato il valore di n non è cristallizzato, ma varia all’interno della procedura.

Conoscere tutte le Stored Procedures collegate ad un DB MySQL

Per conoscere l’elenco delle Stored Procedures collegate al nostro database dovremo digitare semplicemente:

SHOW PROCEDURE STATUS;

Eliminare una Stored Procedure in MySQL

Una volta create, le Stored Procedures possono essere eliminate mediante il comando DROP PROCEDURE in questo modo:

DROP PROCEDURE nome_procedura;

E’ anche possibile aggiungere la clausola IF EXISTS per verificare che la procedura esista effettivamente, in questo modo:

DROP PROCEDURE IF EXISTS nome_procedura;