Funciones Definidas por el Usuario (UDF) en MySQL

¿Qué es una función en MySQL?

Una función en MySQL es un bloque de código SQL que recibe cero o más parámetros de entrada, realiza operaciones y devuelve un único valor. Se puede invocar dentro de expresiones SQL, cláusulas SELECT, WHERE, HAVING y otras, de la misma manera que se usan las funciones nativas del motor.

Las funciones encapsulan lógica reutilizable: en lugar de repetir el mismo cálculo en múltiples consultas, se define una vez y se invoca cuando sea necesario.

Tipos de funciones en MySQL

Funciones escalares (UDF – User Defined Functions)

Operan sobre una sola fila a la vez y devuelven un único valor. Son el objeto de estudio principal de este apunte.

Ejemplo de UDF nativa: UPPER('hola') → devuelve 'HOLA'.

Funciones nativas escalares más comunes

• Matemáticas: ABS(x), ROUND(x, d), CEIL(x), FLOOR(x), SQRT(x), MOD(x, y).
• Cadenas: UPPER(str), LOWER(str), CONCAT(s1, s2), LENGTH(str), TRIM(str), SUBSTRING(str, pos, len).
• Fecha y hora: NOW(), CURDATE(), DATE_ADD(date, INTERVAL n unit), DATEDIFF(d1, d2), YEAR(date), MONTH(date).
• Control de flujo: IF(cond, verdadero, falso), COALESCE(v1, v2, ...), NULLIF(a, b).

Funciones agregadas

Operan sobre un conjunto de filas y devuelven un valor resumen. No pueden definirse como UDF en MySQL; son nativas del motor.

• SUM(col), AVG(col), MAX(col), MIN(col), COUNT(*) / COUNT(col).

Comparativa: Escalares vs Agregadas

Sintaxis básica de una UDF

CREATE FUNCTION nombre_funcion(param1 tipo, param2 tipo, ...)
RETURNS tipo_dato_retorno
[DETERMINISTIC | NOT DETERMINISTIC]
[NO SQL | READS SQL DATA | MODIFIES SQL DATA]
BEGIN
    -- declaración de variables locales (opcional)
    DECLARE variable tipo DEFAULT valor;

    -- lógica de la función
    -- ...

    RETURN expresion_o_variable;
END;

Las dos dimensiones de las características

Las características de una UDF se declaran en dos dimensiones independientes que responden a preguntas distintas:

• ¿Es predecible el resultado? → DETERMINISTIC o NOT DETERMINISTIC
• ¿Accede a datos de tablas? → NO SQL, READS SQL DATA o MODIFIES SQL DATA

Ambas se pueden (y deben) combinar cuando corresponda. Por ejemplo: NOT DETERMINISTIC READS SQL DATA.

Delimitadores

Dado que el cuerpo de la función contiene punto y coma (;), es necesario cambiar el delimitador antes de crear la función para que el cliente MySQL no interprete cada ; interno como el fin del comando:

DELIMITER //

CREATE FUNCTION nombre_funcion(...)
RETURNS tipo
DETERMINISTIC
BEGIN
    DECLARE resultado tipo;
    -- lógica
    RETURN resultado;
END //

DELIMITER ;

Primer ejemplo: UDF determinística simple

La siguiente función calcula el precio con descuento del 10%:

DELIMITER //

CREATE FUNCTION calcular_descuento(precio DECIMAL(7,2))
RETURNS DECIMAL(7,2)
DETERMINISTIC
BEGIN
    DECLARE descuento DECIMAL(7,2);
    SET descuento = precio * 0.10;
    RETURN precio - descuento;
END //

DELIMITER ;

-- Invocación:
SELECT calcular_descuento(1500.00);   -- Resultado: 1350.00
SELECT nombre, precio, calcular_descuento(precio) AS precio_final
FROM productos;

Características de la función: determinismo y acceso a datos

Cuando se declara una UDF, MySQL exige información sobre dos aspectos de su comportamiento. Esta información no es solo formal: el motor la usa para optimizar la ejecución y garantizar la coherencia en entornos con replicación.

Primera dimensión: ¿es predecible el resultado?

Esta dimensión responde a la pregunta: si llamo a la función dos veces con los mismos parámetros, ¿obtengo siempre el mismo valor?

DETERMINISTIC

Se declara DETERMINISTIC cuando, dados los mismos valores de entrada, la función siempre produce el mismo resultado, independientemente del momento en que se ejecute, del estado de las tablas o de variables del sistema. El resultado depende exclusivamente de los parámetros recibidos.

Ejemplos de funciones determinísticas:

• Una función que calcula precio * 0.10: con el mismo precio, siempre devuelve el mismo valor.
• Una función que convierte Celsius a Fahrenheit: pura aritmética, sin estado externo.
• Una función que concatena nombre y apellido con un formato fijo.

CREATE FUNCTION celsius_a_fahrenheit(grados DECIMAL(5,2))
RETURNS DECIMAL(5,2)
DETERMINISTIC          -- mismos grados → mismo resultado, siempre
NO SQL                 -- no toca ninguna tabla
BEGIN
    RETURN (grados * 9/5) + 32;
END;

NOT DETERMINISTIC

Se declara NOT DETERMINISTIC cuando la función puede devolver resultados distintos con los mismos parámetros, porque depende de algo externo que puede cambiar: el reloj del sistema, un valor aleatorio, o el contenido actual de una tabla.

Ejemplos de funciones no determinísticas:

• NOW() y CURDATE(): dependen del reloj en el momento de ejecución.
• RAND(): genera un número distinto en cada llamada.
• Una función que lee el saldo de una cuenta bancaria: si la tabla se modifica entre dos llamadas, el resultado cambia aunque el parámetro sea el mismo ID.

CREATE FUNCTION obtener_timestamp_actual()
RETURNS DATETIME
NOT DETERMINISTIC      -- cada llamada puede devolver un valor diferente
NO SQL
BEGIN
    RETURN NOW();
END;

Segunda dimensión: ¿qué hace la función con las tablas?

Esta dimensión responde a: ¿la función accede a tablas? ¿las lee? ¿las modifica? Hay tres opciones:

NO SQL

La función no contiene ninguna sentencia SQL de ningún tipo. No lee ni escribe tablas, ni siquiera ejecuta un SELECT. Opera únicamente con los parámetros recibidos y expresiones aritméticas o de cadena.

Se combina naturalmente con DETERMINISTIC, ya que si no hay acceso a datos externos, el resultado solo puede depender de los parámetros.

CREATE FUNCTION calcular_iva(precio DECIMAL(10,2), tasa DECIMAL(4,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
NO SQL             -- pura aritmética, sin acceso a tablas
BEGIN
    RETURN precio * tasa;
END;

READS SQL DATA

La función contiene sentencias SELECT que leen datos de tablas, pero no los modifica. No ejecuta INSERT, UPDATE ni DELETE.

Este es el caso típico de funciones que consultan información de la base de datos para realizar un cálculo o buscar un valor. Como el contenido de las tablas puede cambiar entre llamadas, suelen combinarse con NOT DETERMINISTIC, aunque si los datos consultados son estáticos (tablas de configuración que no cambian), podría argumentarse que son determinísticas.

CREATE FUNCTION obtener_precio_lista(p_producto_id INT)
RETURNS DECIMAL(10,2)
NOT DETERMINISTIC      -- el precio en tabla puede cambiar
READS SQL DATA         -- hace un SELECT sobre la tabla productos
BEGIN
    DECLARE precio DECIMAL(10,2);
    SELECT precio_venta INTO precio
    FROM productos
    WHERE producto_id = p_producto_id;
    RETURN IFNULL(precio, 0.00);
END;

MODIFIES SQL DATA

Indica que la función contiene sentencias que modifican datos (INSERT, UPDATE, DELETE, DROP, etc.). En la práctica, MySQL no permite ejecutar DML dentro de funciones que sean llamadas desde un SELECT u otras expresiones SQL, por lo que declarar MODIFIES SQL DATA en una UDF generará un error en tiempo de ejecución si la función efectivamente intenta modificar datos.

Esta característica existe en el estándar SQL y tiene uso real en procedimientos almacenados, pero en funciones UDF su aplicación práctica en MySQL es muy limitada. Se incluye aquí por completitud.

Tabla resumen: combinaciones más frecuentes

Ejemplos de NOT DETERMINISTIC definidas por el usuario

Caso 1 – NOT DETERMINISTIC por uso de NOW() o RAND()

La no determinismo viene de funciones nativas del motor cuyo resultado varía en cada ejecución, no de los parámetros de entrada. La siguiente función genera un código de seguimiento combinando un prefijo recibido como parámetro con la fecha y hora actuales:

DELIMITER //

CREATE FUNCTION generar_codigo_seguimiento(prefijo VARCHAR(10))
RETURNS VARCHAR(30)
NOT DETERMINISTIC   -- NOW() cambia en cada llamada
NO SQL
BEGIN
    -- Aunque el parámetro sea el mismo, el resultado varía
    -- porque NOW() devuelve el instante actual de ejecución.
    -- Ejemplo: 'ORD-20240915-143022', 'ORD-20240915-143055'
    RETURN CONCAT(prefijo, '-', DATE_FORMAT(NOW(), '%Y%m%d-%H%i%s'));
END //

DELIMITER ;

-- Dos llamadas con el mismo parámetro producen resultados distintos:
SELECT generar_codigo_seguimiento('ORD');  -- ORD-20240915-143022
SELECT generar_codigo_seguimiento('ORD');  -- ORD-20240915-143055

Otro ejemplo con RAND(): función que asigna un número de turno aleatorio dentro de un rango:

DELIMITER //

CREATE FUNCTION asignar_turno(min_turno INT, max_turno INT)
RETURNS INT
NOT DETERMINISTIC   -- RAND() produce un valor distinto en cada llamada
NO SQL
BEGIN
    -- FLOOR(RAND() * (max - min + 1)) + min genera un entero en [min, max]
    RETURN FLOOR(RAND() * (max_turno - min_turno + 1)) + min_turno;
END //

DELIMITER ;

SELECT asignar_turno(1, 100);  -- puede devolver 47
SELECT asignar_turno(1, 100);  -- puede devolver 83 (mismo parámetro, distinto resultado)

Caso 2 – NOT DETERMINISTIC por lógica propia de la función (sin NOW ni RAND)

Una función puede ser no determinística sin usar ninguna función variable del motor. El no determinismo surge de la lógica de negocio: el resultado depende del estado actual de una tabla, que puede cambiar entre llamadas.

La siguiente función devuelve la categoría de un cliente según la cantidad de compras que tiene registradas en ese momento:

DELIMITER //

CREATE FUNCTION categorizar_cliente(p_cliente_id INT)
RETURNS VARCHAR(20)
NOT DETERMINISTIC   -- el resultado cambia si el cliente hace nuevas compras
READS SQL DATA      -- consulta la tabla ventas
BEGIN
    DECLARE total_compras INT;
    DECLARE categoria VARCHAR(20);

    SELECT COUNT(*) INTO total_compras
    FROM ventas
    WHERE cliente_id = p_cliente_id;

    IF total_compras = 0 THEN
        SET categoria = 'Nuevo';
    ELSEIF total_compras < 5 THEN
        SET categoria = 'Ocasional';
    ELSEIF total_compras < 20 THEN
        SET categoria = 'Frecuente';
    ELSE
        SET categoria = 'VIP';
    END IF;

    RETURN categoria;
END //

DELIMITER ;

-- Hoy, con 3 compras registradas:
SELECT categorizar_cliente(42);   -- devuelve 'Ocasional'

-- Mañana, si el cliente hizo 2 compras más (total: 5):
SELECT categorizar_cliente(42);   -- devuelve 'Frecuente'
-- Mismo parámetro (42), resultado diferente → NOT DETERMINISTIC

Variables locales y control de flujo en UDFs

Las funciones pueden utilizar variables locales (DECLARE), estructuras condicionales (IF / ELSEIF / ELSE) y expresiones CASE.

Ejemplo: IF / ELSEIF

DELIMITER //

CREATE FUNCTION clasificar_precio(precio DECIMAL(10,2))
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    DECLARE categoria VARCHAR(20);
    IF precio < 500 THEN
        SET categoria = 'Económico';
    ELSEIF precio < 2000 THEN
        SET categoria = 'Intermedio';
    ELSE
        SET categoria = 'Premium';
    END IF;
    RETURN categoria;
END //

DELIMITER ;

-- Uso:
SELECT nombre, precio, clasificar_precio(precio) AS categoria
FROM productos;

Ejemplo: CASE dentro de una función

DELIMITER //

CREATE FUNCTION estado_stock(cantidad INT)
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    RETURN CASE
        WHEN cantidad = 0 THEN 'Sin stock'
        WHEN cantidad < 10 THEN 'Stock bajo'
        WHEN cantidad < 50 THEN 'Stock normal'
        ELSE 'Stock alto'
    END;
END //

DELIMITER ;

Función con múltiples parámetros

El siguiente ejemplo calcula el precio final de una venta aplicando descuento e impuesto:

DELIMITER //

-- Nota: esta función lee de una tabla, por lo que se declara READS SQL DATA
CREATE FUNCTION calcular_precio_final(
    p_venta_id INT,
    p_descuento DECIMAL(5,4),
    p_impuesto  DECIMAL(5,4)
)
RETURNS DECIMAL(10,2)
READS SQL DATA
BEGIN
    DECLARE subtotal DECIMAL(10,2);
    SELECT precio * cantidad INTO subtotal
    FROM ventas
    WHERE venta_id = p_venta_id;
    RETURN subtotal * (1 - p_descuento) * (1 + p_impuesto);
END //

DELIMITER ;

-- Invocación directa:
SELECT calcular_precio_final(10, 0.10, 0.21);  -- 10% descuento, 21% IVA

Integración con procedimientos almacenados

Una UDF puede ser llamada desde dentro de un procedimiento almacenado para delegar cálculos puntuales:

DELIMITER //

CREATE PROCEDURE actualizar_precio_final(
    p_venta_id  INT,
    p_descuento DECIMAL(5,4),
    p_impuesto  DECIMAL(5,4)
)
BEGIN
    UPDATE ventas
    SET precio_final = calcular_precio_final(p_venta_id, p_descuento, p_impuesto)
    WHERE venta_id = p_venta_id;
END //

DELIMITER ;

-- Llamada al procedimiento:
CALL actualizar_precio_final(10, 0.10, 0.21);

Gestión de funciones: consultar, modificar y eliminar

Consultar funciones existentes

-- Ver todas las funciones de la base de datos actual:
SHOW FUNCTION STATUS WHERE Db = 'nombre_base_datos';

-- Ver el código fuente de una función:
SHOW CREATE FUNCTION nombre_funcion;

Modificar una función

MySQL no tiene ALTER FUNCTION para modificar el cuerpo. Se debe eliminar y recrear:

DROP FUNCTION IF EXISTS nombre_funcion;

-- Luego CREATE FUNCTION con la nueva lógica...

Eliminar una función

DROP FUNCTION nombre_funcion;

-- Con verificación previa:
DROP FUNCTION IF EXISTS nombre_funcion;

Otros tipos de funciones en MySQL (referencia)

Además de las UDF escalares, MySQL incluye otras categorías de funciones que conviene conocer:

Comparación: UDF vs Procedimientos Almacenados (SP)

¿Cuándo usar una UDF?

• Cálculos simples o transformaciones reutilizables (descuentos, impuestos, conversiones).
• Validaciones que devuelven un valor booleano o numérico.
• Cuando el resultado debe usarse directamente dentro de una expresión SQL.
• Para encapsular lógica de presentación (formateo de nombres, fechas).

¿Cuándo usar un procedimiento almacenado?

• Operaciones que modifican datos (INSERT / UPDATE / DELETE en una o más tablas).
• Procesos de negocio con múltiples pasos y control de flujo complejo.
• Cuando se necesita manejo de errores (DECLARE ... HANDLER).
• Procesos transaccionales (COMMIT / ROLLBACK).

Relación de las UDF con otros objetos de base de datos