Funciones almacenadas en SQL

Las funciones almacenadas son una herramienta muy potente de SQL que permite crear bloques de código reutilizables para hacer cálculos y transformar datos. A diferencia de las funciones integradas, las funciones almacenadas las crean los propios desarrolladores para resolver tareas específicas.

Estructura general de una función almacenada

CREATE FUNCTION nombre_funcion([parametro tipo [, ...]])
RETURNS tipo_resultado
BEGIN
    -- lógica de la función
    RETURN resultado;
END;

CREATE OR REPLACE FUNCTION nombre_funcion([parametro tipo [, ...]])
RETURNS tipo_resultado
LANGUAGE plpgsql
AS $$
BEGIN
    -- lógica de la función
    RETURN resultado;
END;
$$;

Ejemplo sencillo de función

Vamos a crear una función que determine si una persona es mayor de edad a partir de su fecha de nacimiento:

MySQL 8.4
CREATE FUNCTION is_adult(birth_date DATE)
RETURNS BOOLEAN
BEGIN
    RETURN TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) >= 18;
END;

MySQL 8.4
CREATE OR REPLACE FUNCTION is_adult(birth_date DATE)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birth_date)) >= 18;
END;
$$;

Ahora puedes usar esta función en cualquier consulta:

MySQL 8.4
-- Creamos la función
CREATE FUNCTION is_adult(birth_date DATE)
RETURNS BOOLEAN
BEGIN
    RETURN TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) >= 18;
END;

-- Usamos la función
SELECT
    is_adult('2010-05-15') AS child_status,
    is_adult('2000-03-20') AS adult_status;

MySQL 8.4
-- Creamos la función
CREATE OR REPLACE FUNCTION is_adult(birth_date DATE)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birth_date)) >= 18;
END;
$$;

-- Usamos la función
SELECT
    is_adult('2010-05-15') AS child_status,
    is_adult('2000-03-20') AS adult_status;

Uso de funciones en consultas a tablas

Las funciones almacenadas son especialmente útiles al trabajar con datos reales. Por ejemplo, podemos usar nuestra función para filtrar a los estudiantes por edad:

MySQL 8.4
-- Creamos la función
CREATE FUNCTION is_adult(birth_date DATE)
RETURNS BOOLEAN
BEGIN
    RETURN TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) >= 18;
END;

-- Usamos la función en una consulta a una tabla
SELECT
    first_name,
    last_name,
    birthday,
    is_adult(birthday) AS is_adult
FROM Student
WHERE is_adult(birthday) = true
LIMIT 5;

MySQL 8.4
-- Creamos la función
CREATE OR REPLACE FUNCTION is_adult(birth_date DATE)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birth_date)) >= 18;
END;
$$;

-- Usamos la función en una consulta a una tabla
SELECT
    first_name,
    last_name,
    birthday,
    is_adult(birthday) AS is_adult
FROM Student
WHERE is_adult(birthday) = true
LIMIT 5;

Funciones con consultas a la base de datos

Las funciones almacenadas pueden ejecutar consultas SQL en su interior para obtener los datos que necesitan:

MySQL 8.4
CREATE FUNCTION get_student_lessons_count(student_id INT, target_date DATE)
RETURNS INT
BEGIN
    DECLARE lessons_count INT;

    SELECT COUNT(*) INTO lessons_count
    FROM Schedule s
    INNER JOIN Student_in_class sic ON s.class = sic.class
    WHERE sic.student = student_id
      AND s.date = target_date;

    RETURN lessons_count;
END;

MySQL 8.4
CREATE OR REPLACE FUNCTION get_student_lessons_count(student_id INT, target_date DATE)
RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
    lessons_count INT;
BEGIN
    SELECT COUNT(*) INTO lessons_count
    FROM Schedule s
    INNER JOIN Student_in_class sic ON s.class = sic.class
    WHERE sic.student = student_id
      AND s.date = target_date;

    RETURN lessons_count;
END;
$$;

Esta función cuenta el número de clases que tiene un estudiante concreto en un día determinado:

MySQL 8.4
SELECT get_student_lessons_count(1, '2019-09-01') AS lessons_today;

MySQL 8.4
SELECT get_student_lessons_count(1, '2019-09-01') AS lessons_today;

Análisis del ejemplo con variables

Hasta ahora no habíamos trabajado con variables en SQL, pero son un concepto importante a la hora de crear funciones y procedimientos almacenados. Así que vamos a analizar el ejemplo anterior paso a paso:

MySQL 8.4
DECLARE lessons_count INT;

Esta línea declara la variable lessons_count de tipo INT. La variable guardará el resultado de nuestra consulta.

MySQL 8.4
SELECT COUNT(*) INTO lessons_count
FROM Schedule s
INNER JOIN Student_in_class sic ON s.class = sic.class
WHERE sic.student = student_id
  AND s.date = target_date;

Aquí se guarda el resultado de la consulta en la variable:

• SELECT COUNT(*): cuenta el número de registros
• INTO lessons_count: guarda el resultado en la variable lessons_count
• El resto es una consulta SQL normal con JOIN y condiciones

MySQL 8.4
RETURN lessons_count;

Devolvemos el valor de la variable como resultado de la función.

Gestión de funciones almacenadas

• Ver las funciones existentes

  MySQL 8.4
  SHOW FUNCTION STATUS WHERE Db = 'your_database_name';
  

  MySQL 8.4
  SELECT routine_name, routine_type
  FROM information_schema.routines
  WHERE routine_type = 'FUNCTION' AND routine_schema = 'public';
  

• Eliminar una función

  MySQL 8.4
  DROP FUNCTION IF EXISTS is_adult;
  

  MySQL 8.4
  DROP FUNCTION IF EXISTS is_adult(DATE);
  

• Modificar una función

  Para modificar una función en MySQL, primero hay que eliminar la versión antigua y después crear una nueva:

  MySQL 8.4
  DROP FUNCTION IF EXISTS is_adult;
  -- Crear la nueva versión de la función
  CREATE FUNCTION is_adult(birth_date DATE) ...
  

  En PostgreSQL puedes usar CREATE OR REPLACE FUNCTION:

  MySQL 8.4
  CREATE OR REPLACE FUNCTION is_adult(birth_date DATE)
  RETURNS BOOLEAN
  -- nueva implementación
  

Las funciones almacenadas son una herramienta muy potente para crear lógica de negocio reutilizable directamente en la base de datos. ¡Ayudan a centralizar los cálculos y a garantizar la coherencia de los datos en toda la aplicación! 🚀