In MySQL, le funzioni definite dall'utente (in inglese user-defined functions o UDF) sono dei programmi eseguibili che vengono archiviati all'interno del DBMS che possono essere utilizzati per operazioni di manipolazione dei dati che non sono previste nel Database server.
Si tratta in pratica di un mix tra applicazioni esterne ed istruzioni SQL che possono essere integrate in MySQL e richiamate quando i soli comandi messi a disposizione dal linguaggio o le funzioni native non sono sufficienti.
In questo breve articolo vedremo come definire questa particolare tipologia di funzioni, quando e come utilizzarle e che procedura seguire per la loro eliminazione.
Una semplice funzione
Uno dei vantaggi che risiedono nella creazione di funzioni per MySQL sta nel fatto che si tratta di costrutti alternativi a quelli messi a disposizione dai linguaggi di scripting e di programmazione.
Spesso infatti gli stessi risultati raggiungibili con le funzioni definite dagli utenti potrebbero essere ottenute sfruttando le potenzialità di un linguaggio come PHP; in questo caso però abbiamo un limite legato al sorgente della nostra applicazione che dovrà essere modificato (a volte anche in modo sostanziale) in caso di aggiornamenti o per l'adattamento a finalità inizialmente non previste.
Le funzioni definite dall'utente ci consentono invece di agire soltanto su di esse in caso di updates, questo ci permetterà di non dover alterare il listato delle nostre applicazioni; in pratica più lavoro sarà lasciato al DBMS minore sarà quello che lo sviluppatore dovrà impiegare per la modifica degli script.
Per descrivere un semplice esempio di funzione definita dall'utente creeremo innanzitutto un database con i relativi record in tabella che utilizzeremo per i successivi esempi:
CREATE DATABASE esempio;
USE esempio;
CREATE TABLE agenda(ag_id INT(5) NOT NULL,
nome VARCHAR(20),
con_id INT(5),
PRIMARY KEY(ag_id));
CREATE TABLE contatti(con_id INT(5) NOT NULL,
descrizione varchar(20),
PRIMARY KEY(con_id))
INSERT INTO agenda (ag_id,nome,con_id)
VALUES (1, 'Eolo', 1),(2, 'Dotto', 2),(3, 'Pisolo', 2)
INSERT INTO contatti (con_id, descrizione)
VALUES (1, 'amici'),(2, 'lavoro')
Se dobbiamo scrivere una funzione è bene tenere presente la questione dei "caratteri di delimitazione"; l'esecutore del programma interpreta infatti il punto e virgola (;) come simbolo per la delimitazione di un'istruzione, ma per la definizione delle funzioni dovremmo introdurre un ulteriore delimitatore composto da due slash scritti di seguito senza spazi (//).
Se per esempio dovessimo andare a richiamare una funzione denominata semplicemente funzione() dovremmo utilizzare una semplice sintassi come la seguente:
SELECT funzione() //
Ma andiamo con ordine, definiremo ora la nostra prima funzione lato utente; non sarà nulla di complesso visto che partiremo dall'esempio base che viene utilizzato per ogni linguaggio, andremo infatti a creare una semplice funzione in grado di restituire in output l'universalmente conosciuto "Ciao Mondo".
Ecco il codice della nostra prima funzione:
CREATE FUNCTION CiaoMondo() RETURNS VARCHAR(15) RETURN 'Ciao Mondo'; //
La sintassi utilizzata per la definizione di CiaoMondo() è quella standard prevista da MySQL per la creazione di questo tipo di costrutti:
CREATE FUNCTION nome_funzione ([argomenti[,.. [RETURNS type] [charatteristiche ...] routine body
Una volta definita, la funzione viene memorizzata e risulta disponibile, quindi sarà pronta per essere eseguita con una semplice SELECT:
CiaoMondo() // +--------------+ | CiaoMondo() | +--------------+ | CiaoMondo | +--------------+ 1 row in set (0.00 sec)
Funzioni definite dall'utente e routine body
La parte relativa al routine body all'interno di una funzione definita dall'utente e quella che mette in atto la procedura di manipolazione dei dati; nella funzione CiaoMondo() utilizzata per l'esempio precedente abbiamo utilizzato una sola riga di codice, ma se dovessimo estendere il listato sarebbe necessario delimitare le istruzioni tra i costrutti BEGIN ed END.
BEGIN ed END possono essere utilizzati anche quando si vuole delimitare una sola riga di istruzioni, in questo modo sarà più pratico l'inserimento di aggiornamenti successivi.
Quindi, sempre riferendoci a CiaoMondo(), avremmo potuto definire la stessa funzione in questo modo:
CREATE FUNCTION CiaoMondo() RETURNS VARCHAR(15) BEGIN RETURN 'Ciao Mondo'; END //
Se volessimo testare l'esempio appena esposto, sarebbe bene per prima cosa eliminare la funzione omonima precedentemente definita, per far questo abbiamo a disposizione il comando dedicato DROP FUNCTION che verrà utilizzato passandogli come argomento il nome della funzione da eliminare:
DROP FUNCTION CiaoMondo
DROP FUNCTION accetta la clausola IF EXISTS, essa si rivela particolarmente utile in quanto ci permette di eliminare una funzione solo se memorizzata dal Database Manager, cercare di cancellare una funzione inesistente porterebbe infatti alla notifica di un errore.
Quindi il codice necessario alla (eventuale) cancellazione di una funzione preesistente, alla definizione di una nuova funzione e all'esecuzione della stessa potrebbe essere simile al seguente:
USE esempio DROP FUNCTION IF EXISTS CiaoMondo CREATE FUNCTION CiaoMondo() RETURNS VARCHAR(15) BEGIN RETURN 'Ciao Mondo e ciao a tutti'; END //
Funzioni definite dall'utente: variabili e parametri
Come in tutti i linguaggi di scripting o di programmazione le variabili sono anche per MySQL dei "contenitori di informazioni", cioè aree di memoria dedicate all'archiviazione di un determinato valore.
La dichiarazione delle variabili all'interno delle funzioni definite dall'utente avviene in questo modo:
DECLARE nome_variabile tipo_di_dato
Se per esempio all'interno della nostra funzione CiaoMondo() volessimo definire una variabile in grado di contenere come valore la stringa Ciao Mondo dovremmo procedere con la seguente sintassi:
CREATE FUNCTION CiaoMondo() RETURNS VARCHAR(15) BEGIN DECLARE result VARCHAR(15) SET result = 'Ciao Mondo'; RETURN result; END //
In questo caso abbiamo utilizzato l'istruzione SET per associare alla variabile il valore previsto, ma è possibile anche settare una variabile associando ad esse un valore di default:
CREATE FUNCTION CiaoMondo() RETURNS VARCHAR(15) BEGIN DECLARE result VARCHAR(15) DEFAULT 'Ciao Mondo'; RETURN result; END //
Nello stesso modo sarà possibile anche possibile dichiarare simultaneamente più di una variabile se il tipo di dato è il medesimo:
DECLARE result, nome, contatto VARCHAR;
Un discorso attinente a quello delle variabile è riferito ai parametri che ci permettono di passare alla variabile un valore definito esternamente alla funzione; facciamo un esempio:
CREATE FUNCTION CiaoMondo(p_inparam VARCHAR(15)) RETURNS VARCHAR(15) BEGIN DECLARE result VARCHAR(15); SET result = p_inparam; RETURN result; END //
come è semplice notare in questo caso alla variabile non viene associata una stringa ma un parametro che dovrà contenere (o meglio, che sarà) un dato di tipo stringa; infatti nel momento in cui andremo a richiamare la funzione potremmo eseguire un'istruzione simile alla seguente:
SELECT CiaoMondo(' Ciao Mondo') //
Dove la stringa ' Ciao Mondo' è il parametro che passiamo come valore alla funzione.
Funzioni definite dall'utente e procedure
Un discorso affine a quello delle funzioni (definite dall'utente o no) è quello riguardante le procedure; i due costrutti presentano però delle differenze che vale la pena di sottolineare.
Le funzioni sono degli script che se vengono richiamati restituiscono un valore, che siano composte da una sola riga di codice o da più righe il loro compito è quello; una volta definite possono essere riutilizzate con un semplice richiamo all'interno di istruzioni SQL.
Proponiamo un esempio basato sulle tabelle create all'inizio di questo articolo:
SELECT CONCAT(nome, ' ',con_id) FROM agenda // +--------------------------+ | concat(nome, ' ',con_id) | +--------------------------+ | Eolo 1 | | Dotto 2 | | Pisolo 2 | +--------------------------+ 3 rows in set (0.00 sec)
Dal semplice codice proposto, basato sull'utilizzo della funzione nativa CONCAT(), si può notare come sia semplice immergere una funzione all'interno delle istruzioni SQL.
Le procedure invece sono dei costrutti più avanzati rispetto alle funzioni, esse possono non restituire alcun valore o generarne in output più di uno, ma a differenza delle funzioni non possono essere utilizzate all'interno di istruzioni SQL; viceversa è possibile includere comandi SQL all'interno delle procedure.
Per il resto però le procedure possono essere definite utilizzando una sintassi molto simile a quella adoperata per le funzioni; nell'esempio che segue creeremo una procedura chiamata "CiaoMondo" molto simile all'omonima funzione definita in precedenza:
DROP PROCEDURE IF EXISTS CiaoMondo // CREATE PROCEDURE CiaoMondo() BEGIN SELECT 'Ciao Mondo' ; END // CALL CiaoMondo() //
Similitudini e differenze saltano immediatamente all'occhio: in questo caso DROP PROCEDURE sostituisce DROP FUNCTION ma viene utilizzata per la stessa finalità così come la clausola IF EXISTS.
Stesso discorso per CREATE PROCEDURE, omologo di CREATE FUNCTION, per la delimitazione effettuata da // e per il contenitore definito da BEGIN ed END. Da notare però come in questo caso l'output richiesto sia l'esecuzione di un comando SQL e non la stampa di una stringa.
Infine, notiamo come la procedura venga richiamata non soltanto specificandone il nome, come avviene per le funzioni, ma quale argomento dell'apposito comando CALL che appunto richiama la procedura.
