Panoramica sulle Stored Function

Le Stored Function sono funzioni definite dall'utente che possono essere chiamate nelle istruzioni SQL come normali funzioni, e restituiscono un singolo valore.

Creare le Stored Function

Ecco un esempio di come creare una Stored Function:

DELIMITER //

CREATE FUNCTION QuarantaDue() RETURNS TINYINT DETERMINISTIC
BEGIN
 DECLARE x TINYINT;
 SET x = 42;
 RETURN x;
END 

//

DELIMITER ;

Per prima cosa si modifica il delimitatore, perché la definizione della funzione contiene il punto e virgola, che è il normale delimitatore. Poi la funzione viene chiamata QuarantaDue e si dichiara che restituisce un TINYINT. La parola chiave DETERMINISTIC non è sempre necessaria (però nel caso in cui si usa il binary logging lasciarla fuori genera un errore), e serve ad aiutare l'ottimizzatore delle query a scegliere un piano di esecuzione appropriato. Una funzione deterministica è una funzione che, ricevendo gli stessi argomenti, darà sempre lo stesso risultato.

Poi viene specificato il corpo della funzione tra BEGIN e END. Al suo interno si dichiara un tinyint, X, che viene semplicemente impostato a 42, che è anche il risultato restituito.

MariaDB [test]> SELECT QuarantaDue();
+------------+
| FortyTwo() |
+------------+
|         42 |
+------------+

Di solito una funzione che non accetta argomenti serve a poco. Ecco un esempio più complesso:

DELIMITER //
CREATE FUNCTION VatCents(price DECIMAL(10,2)) RETURNS INT DETERMINISTIC
BEGIN
 DECLARE x INT;
 SET x = price * 114;
 RETURN x;
END //
Query OK, 0 rows affected (0.04 sec)
DELIMITER ;

Questa funzione prende un argomento, price, che è definito come DECIMAL, e restituisce un INT.

Si veda CREATE FUNCTION per ulteriori dettagli.

Visualizzare le Stored Function e le loro definizioni

Per sapere quali Stored Function sono in esecuzione sul server, si può usare SHOW FUNCTION STATUS.

MariaDB [(none)]> SHOW FUNCTION STATUS\G
*************************** 1. row ***************************
                  Db: test
                Name: VatCents
                Type: FUNCTION
             Definer: root@localhost
            Modified: 2013-06-01 12:40:31
             Created: 2013-06-01 12:40:31
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

O interrogare direttamente la tabella ROUTINES nel database INFORMATION_SCHEMA:

MariaDB [(none)]> SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='FUNCTION';
+--------------+
| ROUTINE_NAME |
+--------------+
| VatCents     |
+--------------+

Per sapere cosa fa la Stored Function, si può usare SHOW CREATE FUNCTION.

MariaDB [test]> SHOW CREATE FUNCTION VatCents\G
*************************** 1. row ***************************
            Function: VatCents
            sql_mode: 
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `VatCents`(price DECIMAL(10,2)) RETURNS int(11)
    DETERMINISTIC
BEGIN
 DECLARE x INT;
 SET x = price * 114;
 RETURN x;
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

Eliminare e modificare le Stored Function

Per eliminare una Stored Function, si può usare l'istruzione DROP FUNCTION.

DROP FUNCTION QuarantaDue;

Per modificare le caratteristiche di una Stored Function, si può usare ALTER FUNCTION. Si noti che non è possibile modificare i parametri o il corpo di una Stored Function utilizzando questa istruzione; per effettuare cambiamenti di questo genere, occorre eliminare e ricreare la Stored Function tramite DROP FUNCTION e CREATE FUNCTION.

I permessi delle Stored Function

Si veda la pagina I privilegi delle Stored Routine.