MySQL Stored Functions

Come già detto all’interno della lezione precedente, nella generica famiglia delle Stored Procedures rientrano anche le Stored Functions, anche dette UDF (acronimo di User Defined Functions).

Le Stored Functions differiscono dalle Stored Procedures in senso stretto in quanto le prime, a differenza delle seconde, prevedono un valore di ritorno.

Creare una Stored Function con MySQL

Per creare una Stored Function in MySQL si utilizza il comando CREATE FUNCTION seguito dal nome della funzione (accompagnato da eventuali parametri) e dalla definizione del valore di ritorno, appunto.

Per fare un esempio, immaginiamo di lavorare su una ipotetica tabella "clienti" in cui sono memorizzati i punti fedeltà accumulati dagli stessi all’interno del nostro negozio online. Supponiamo, altresì, di voler calcolare – senza ricorrere ad alcun applicativo ma direttamente all’interno della nostra base dati – il livello dei clienti in base ai punti accumulati:

DELIMITER $$;
 
CREATE FUNCTION LivelloUtente(punti INT) RETURNS VARCHAR(10) DETERMINISTIC
BEGIN
  # definisco una variabile locale (interna alla funzione)
  DECLARE livello VARCHAR(10);
 
  # creo un costrutto condizionale
  IF punti > 10000 THEN
    SET livello = 'VIP';
  ELSEIF (punti <= 10000 AND punti >= 5000) THEN
    SET livello = 'GOLD';
  ELSEIF punti < 5000 THEN
    SET livello = 'SILVER';
  END IF;
 
  # valore di ritono
  RETURN (livello);  
END $$

DELIMITER ;

Come potete vedere la sintassi è molto simile a quella vista per le Stored Procedures, se ppur con quale differenza.

Come detto, per creare la nostra funzione abbiamo utilizzato lo statement CREATE FUNCTION seguito dal nome accompagnato, tra parentesi tonde, dall’elenco dei parametri in entrata. E’ importante sottolineare che nelle Stored Functions tutti i parametri s’intendono in ingresso, pertanto non devono essere utilizzati i modificatori IN, OUT e INOUT che abbiamo visto nelle Stored Procedures!

Sempre sulla stessa linea abbiamo definito il valore di ritorno mediante lo statement RETURNS seguito dal tipo di dato atteso e della keyword DETERMINISTIC. In merito è importante ricordare che in MySQL il risultato di una UDF può essere:

• DETERMINISTIC: la funzione produce sempre lo stesso risultato in presenza dei medesimi valori di input;
• NOT DETERMINISTIC: la funzione può produrre risultati differenti anche in presenza dei medesimi valori di input.

Se lo sviluppatore non lo indica esplicitamente, MySQL imposta di default il valore di ritorno come NOT DETERMINISTIC.

Anche per le Stored Functions è possibile specificare un utente proprietario mediante l’opzione DEFINER (si veda la lezione precedente per la sintassi).

Esattamente come nelle Stored Procedures, indìfine, anche nelle Stored Functions il corpo della procedura è delimitato da BEGIN e END. A differenza delle procedure in senso stretto, nelle Stored Functions deve essere sempre definito il valore di ritono mediante il ricorso a RETURN. E’ importante precisare che, all’interno del corpo della funzione, lo sviluppatore può utilizzare più volte RETURN (ad esempio all’interno di un costrutto condizionale); Quando la funzione esegue il RETURN, questa termina immediatamente (RETURN determina l’uscita dalla funzione).

Utilizzare una Stored Function in MySQL

In merito all’utilizzo delle Stored Functions è importante sottolineare che queste possono essere utilizzate all’interno di comuni query SQL, ad esempio:

SELECT 
  nome, LivelloUtente(punti_fedelta)
FROM
  clienti
ORDER BY nome ASC;

Ovviamente nulla vieta di invocare una Stored Function all’interno di una Stored Procedure, ad esempio:

DELIMITER $$;
 
CREATE PROCEDURE MostraLivello(
  IN  id_cliente INT,
  OUT livello VARCHAR(10)
)
BEGIN
  DECLARE pt INT;
 
  SELECT punti_fedelta INTO pt
  FROM clienti
  WHERE id = id_cliente;
 
  SELECT LivelloUtente(pt) 
  INTO livello; 
END $$

A questo punto per conoscere il livello di uno specifico cliente, utilizzeremo:

CALL MostraLivello(123,@liv);
SELECT @liv;

Conoscere le Stored Function associate ad un dato DB MySQL

Anche in questo caso la sintassi è deltutto simile a quella vista nella lezione precedente:

SHOW FUNCTION STATUS;

Eliminare una Stored Functions in MySQL

Esattamente come per le procedure, anche per le Stored Functions è prevista la possibilità di eliminarle mediante il comando DROP FUNCTION seguito dal nome della funzione che si desidera eliminare. Anche qui, come visto per le Stored Procedures è possibile aggiungere la clausola IF EXISTS in questo modo:

DROP FUNCTION IF EXIST nome_funzione;