Cómo crear y usar una función almacenada MySQL

GuíasSin categoríaBases de datosMySQLProgramacionSQL

En esta guía vamos a ver cómo crear y configurar funciones almacenadas de MySQL. Estas funciones pueden ser utilizadas para un gran número de tareas y permiten mejorar la integridad de datos y la seguridad de los mismos, además de mejorar el rendimiento en comparación con el uso de otros métodos. También mejoran la legibilidad del código.

Qué es una función almacenada

Las funciones almacenadas de MySQL nos permiten procesar y manipular datos de forma procedural de un modo muy eficiente. Podrás usarlas en las sentencias SQL independientemente del lenguaje de programación del servidor sobre el que se ejecuten las consultas.

La sintaxis de una función almacenada es la siguiente:

CREATE FUNCTION nombre_funcion(param1, param2,…)
RETURNS datatype
[NOT] DETERMINISTIC
sentencias_sql

Para crear una función almacenada basta con que tengas permisos INSERT y DELETE sobre la base de datos.

Configuración de la base de datos

A modo de ejemplo, vamos a configurar una base de datos sobre la cual crearemos luego una función almacenada. Usaremos la línea de comandos, pero también puedes crear una base de datos MySQL de muchas otras formas.

Para crear la base de datos, abre la terminal de comandos o conéctate a tu servidor mediante SSH. Luego ejecuta MySQL con este comando, reemplazando usuario por tu nombre de usuario.

mysql -u usuario -p

Vamos a crear una base de datos de nombre base_ejemplo. Para ello sigue estos pasos:

  1. Usa la sentencia create para crear la base de datos:
    CREATE DATABASE base_ejemplo;
  2. Ahora usa el siguiente comando para seleccionar la base de datos:
    USE base_ejemplo;

    Si usas phpMyAdmin, sencillamente debes hacer clic en el nombre de la base de datos, que en nuestro caso es base_ejemplo.

  3. Ahora crea una tabla en la base de datos a la que llamaremos productos. Para hacerlo, ejecuta el siguiente comando:
    CREATE TABLE productos (
        id INT NOT NULL AUTO_INCREMENT,
        nombre VARCHAR(20) NOT NULL,
        coste FLOAT NOT NULL DEFAULT 0.0,
        precio FLOAT NOT NULL DEFAULT 0.0,
        PRIMARY KEY(id)
    );
  4. Ahora vamos a insertar algunos datos de prueba en la tabla productos:
    INSERT INTO productos (nombre, coste, precio) VALUES ('Producto A', 4, 8), ('Producto B', 1, 1.5),('Producto C', 50, 80);

Ya tenemos al base de datos creada. Ahora vamos a crear un procedimiento almacenado.

Cómo crear una función almacenada

Ahora que hemos configurado una base de datos y una tabla, vamos a crear una función almacenada. Haremos una que calcule el beneficio que se obtiene por cada producto, que se llamará calcularBeneficio. Esta función aceptará dos parámetros, que como no podría ser de otro modo son el precio de compra (coste) y el de venta (precio) de un producto. El resultado de al función simplemente será la resta del precio de venta y el de compra, dando como resultado el beneficio obtenido con su venta.

Para crear la función, ejecuta estas sentencias SQL:

DELIMITER $$
CREATE FUNCTION calcularBeneficio(coste FLOAT, precio FLOAT) RETURNS DECIMAL(9,2)
BEGIN
    DECLARE beneficio DECIMAL(9,2);
    SET beneficio = precio - coste;
    RETURN beneficio;
END$$
DELIMITER

Te estarás preguntando que qué es lo que hace MySQL con esta función o por qué no la ejecuta directamente. La respuesta está en la sentencia DELIMITER $$, que frena la ejecución de MySQL para así poder definir la función. Cuando MySQL se encuentra de nuevo con la sentencia DELIMITER, será cuando continúe la ejecución normalmente.

El nombre de las funciones y su tipo se almacenan en la tabla del sistema func, en la propia base de datos mysql. No podrás definir una función dos veces. En caso de que quieras redefinirla, tendrás que eliminarla con la sentencia DROP tal y como veremos más adelante, y luego volver a definirla.

Cómo usar una función almacenada

Una vez hayas creado la función, podrás usarla directamente en cualquier consulta. A modo de ejemplo, vamos a ejecutar esta consulta sobre la tabla productos de la base de datos base_ejemplo:

SELECT *, calcularBeneficio(coste, precio) AS beneficio FROM productos;

Esta consulta SQL devolverá los siguientes resultados:

+---------+--------------+---------+---------+-----------+
| id      | nombre       | coste   | precio  | beneficio |
+---------+--------------+---------+---------+-----------+
|       1 | Producto A   |    4.00 |    8.00 |      4.00 |
|       2 | Producto B   |    1.00 |    1.50 |      0.50 |
|       3 | Producto C   |   50.00 |   80.00 |     30.00 |
+---------+--------------+---------+---------+-----------+

Como puedes ver en los resultados, la función almacenada calcula el beneficio automáticamente para cada producto, siendo éste la resta del precio menos el coste.

Cómo borrar una función almacenada

Puedes borrar una función almacenada haciendo uso de la sentencia DROP FUNCTION. Por ejemplo, si quieres eliminar la función calcularBeneficio del ejemplo anterior, tendrás que ejecutar esta sentencia:

DROP FUNCTION calcularBeneficio;

Y esto es todo. Si quieres, puedes consultar más detalles acerca de las funciones almacenadas en la documentación oficial de MySQL.