Curso de MariaDB Desde la Consola/StoredFunciones

• Stored Functions
• Sintaxis
• Descripción

Stored Functions

Sintaxis

CREATE [OR REPLACE]
    [DEFINER = {user | CURRENT_USER}]
    FUNCTION func_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...]
    RETURN func_body

func_parameter:
    param_name type

type:
    Any valid MariaDB data type

characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

func_body:
    Valid SQL procedure statement

Descripción

Una stored function es una función definida que se llama desde dentro de una instrucción SQL como una función regular, y devuelve un único valor.

Creación de funciones almacenados

He aquí un ejemplo de esqueleto para ver una función almacenada en acción:

 1 DELIMITER //
 2 
 3 CREATE FUNCTION CuarentayDos() RETURNS TINYINT DETERMINISTIC
 4 BEGIN
 5  DECLARE x TINYINT;
 6  SET x = 42;
 7  Return x;
 8 END
 9 
10 //
11 
12 DELIMITER ;

En primer lugar, el delimitador se cambia, ya que la definición de función contendrá el delimitador de punto y coma regular. A continuación, la función se llama CuarentayDos y definido para devolver un tinyint. La palabra clave DETERMINISTIC no es necesario en todos los casos (aunque si el log binario está activado, dejando fuera generará un error), y es ayudar al optimizador de consultas elige un plan de consulta. Una función determinista es aquel que, teniendo en cuenta los mismos argumentos, siempre devolverá el mismo resultado.

A continuación, el cuerpo de la función se coloca entre BEGIN y END declaraciones. Se declara un tinyint, x, que simplemente se establece en 42, y este es el resultado devuelto.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| Prueba1            |
| PythonClase        |
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.04 sec)

MariaDB [(none)]> use Prueba1;
Database changed
MariaDB [Prueba1]> DELIMITER //
MariaDB [Prueba1]> 
MariaDB [Prueba1]> CREATE FUNCTION CuarentayDos() RETURNS TINYINT DETERMINISTIC
                   BEGIN
                     DECLARE x TINYINT;
                     SET x = 42;
                     Return x;
                     END //
Query OK, 0 rows affected (0.08 sec)

MariaDB [Prueba1]> DELIMITER ;
MariaDB [Prueba1]> show function status;
+---------+--------------+----------+----------------+---------------------+---------------------+---------------+---------+
| Db      | Name         | Type     | Definer        | Modified            | Created             | Security_type | Comment |
+---------+--------------+----------+----------------+---------------------+---------------------+---------------+---------+
| Prueba1 | CuarentayDos | FUNCTION | root@localhost | 2016-10-17 13:13:17 | 2016-10-17 13:13:17 | DEFINER       |         |
+---------+--------------+----------+----------------+---------------------+---------------------+---------------+---------+

+----------------------+----------------------+--------------------+
| character_set_client | collation_connection | Database Collation |
+----------------------+----------------------+--------------------+
| utf8                 | utf8_general_ci      | utf8_unicode_ci    |
+----------------------+----------------------+--------------------+
1 row in set (0.05 sec)

MariaDB [Prueba1]> show create function CuarentayDos;
+--------------+----------+
| Function     | sql_mode |
+--------------+----------+
| CuarentayDos |          |
+--------------+----------+

+------------------------------------------------------------------------------------+
| Create Function                                                                    |
+------------------------------------------------------------------------------------+
!                                                                                    |
!  CREATE DEFINER=`root`@`localhost` FUNCTION `CuarentayDos`() RETURNS tinyint(4)    |
!     DETERMINISTIC                                                                  |
! BEGIN                                                                              |
!  DECLARE x TINYINT;                                                                |
!  SET x = 42;                                                                       |
!  Return x;                                                                         |
! END                                                                                |
+------------------------------------------------------------------------------------+

+----------------------+----------------------+--------------------|
| character_set_client | collation_connection | Database Collation |
+----------------------+----------------------+--------------------|
| utf8                 | utf8_general_ci      | utf8_unicode_ci    |
|----------------------+----------------------+--------------------+
1 row in set (0.00 sec

MariaDB [Prueba1]> select CuarentayDos();
+----------------+
| CuarentayDos() |
+----------------+
|             42 |
+----------------+
1 row in set (0.00 sec)

Por supuesto, una función que no tiene ningún argumento es de poca utilidad.

MariaDB [Prueba1]> drop function CuarentayDos;
Query OK, 0 rows affected (0.08 sec)

MariaDB [Prueba1]> show function status;
Empty set (0.00 sec)

He aquí un ejemplo más complejo:

 1 DELIMITER //
 2 
 3 CREATE FUNCTION VatCents( precio DECIMAL(10,2) ) RETURNS INT DETERMINISTIC
 4 BEGIN
 5   DECLARE x INT;
 6   SET x = precio * 114;
 7   Return x;
 8 END //
 9 Query OK, 0 filas afectadas (0,04 seg)
10 DELIMITER ;

Esta función toma un argumento, el precio que se define como un decimal, y devuelve un int.

Para encontrar las funciones almacenadas se están ejecutando en el servidor, use SHOW FUNCTION STATUS.

MariaDB [Prueba1]> show function status \G
*************************** 1. row ***************************
                  Db: Prueba1
                Name: VatCents
                Type: FUNCTION
             Definer: root@localhost
            Modified: 2016-10-17 14:47:15
             Created: 2016-10-17 14:47:15
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_unicode_ci
1 row in set (0.00 sec)

o consultar la tabla de rutinas en la base de datos directamente INFORMATION_SCHEMA:

MariaDB [Prueba1]>  SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='FUNCTION';
+--------------+
| ROUTINE_NAME |
+--------------+
| VatCents     |
+--------------+
1 row in set (0.21 sec)

Para saber lo que hace la función almacenada, use SHOW CREATE FUNCTION.

MariaDB [Prueba1]> 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: utf8_unicode_ci
1 row in set (0.00 sec)

MariaDB [Prueba1]> select VatCents(100);
+---------------+
| VatCents(100) |
+---------------+
|         11400 |
+---------------+
1 row in set (0.00 sec)

Para cambiar las características de una stored function, utilice la función ALTER. Tenga en cuenta que no puede cambiar los parámetros o el cuerpo de una stored function utilizando esta directivo; para hacer tales cambios, tendrá que quitar y volver a crear la función usando DROP FUNCTION y CREATE FUNCTION.