Procedimientos almacenados y funciones en MySQL y PostgreSQL

Cuando una lógica de negocio se repite en múltiples consultas, o cuando necesitas ejecutar varias operaciones SQL como una sola unidad atómica, los procedimientos almacenados y las funciones son la herramienta del propio motor de base de datos para resolverlo. En lugar de mover datos de ida y vuelta entre tu aplicación y la base de datos para procesarlos, puedes ejecutar la lógica directamente donde viven los datos.

En esta guía vas a ver cómo crear y usar procedimientos almacenados y funciones tanto en MySQL como en PostgreSQL, con ejemplos reales y las diferencias de sintaxis entre ambos motores.

El esquema de ejemplo

CREATE TABLE clientes (
  id      INT PRIMARY KEY AUTO_INCREMENT,
  nombre  VARCHAR(100) NOT NULL,
  email   VARCHAR(100) UNIQUE NOT NULL,
  saldo   DECIMAL(10, 2) DEFAULT 0
);

CREATE TABLE productos (
  id     INT PRIMARY KEY AUTO_INCREMENT,
  nombre VARCHAR(100) NOT NULL,
  precio DECIMAL(10, 2) NOT NULL,
  stock  INT DEFAULT 0
);

CREATE TABLE pedidos (
  id          INT PRIMARY KEY AUTO_INCREMENT,
  cliente_id  INT REFERENCES clientes(id),
  producto_id INT REFERENCES productos(id),
  cantidad    INT NOT NULL,
  total       DECIMAL(10, 2) NOT NULL,
  fecha       TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Procedimiento almacenado vs función: la diferencia clave

Aunque a veces se usan como sinónimos, hay una diferencia fundamental entre ambos:

• Una función siempre devuelve un valor y se puede usar dentro de una consulta SELECT, igual que SUM() o NOW(). No puede modificar datos en PostgreSQL si está marcada como STABLE o IMMUTABLE (aunque sí puede en MySQL y en funciones VOLATILE de PostgreSQL).
• Un procedimiento almacenado no tiene por qué devolver un valor (aunque puede tener parámetros de salida) y se ejecuta con CALL, no dentro de una consulta. Está pensado para ejecutar acciones: insertar, actualizar, gestionar transacciones completas.

En PostgreSQL la diferencia es más estricta y reciente (los procedimientos como objeto independiente se introdujeron en PostgreSQL 11). En MySQL, los procedimientos almacenados existen desde siempre y las funciones también, con sintaxis muy similar entre ambos.

Procedimientos almacenados en MySQL

Sintaxis básica

DELIMITER //

CREATE PROCEDURE obtener_clientes_activos()
BEGIN
    SELECT id, nombre, email
    FROM clientes
    WHERE saldo > 0;
END //

DELIMITER ;

-- Ejecutar el procedimiento
CALL obtener_clientes_activos();

El DELIMITER // es necesario porque dentro del cuerpo del procedimiento usamos punto y coma para separar instrucciones, y MySQL necesita un delimitador distinto para saber dónde termina la definición completa del procedimiento.

Procedimientos con parámetros

DELIMITER //

-- Parámetro de entrada (IN, el valor por defecto si no se especifica)
CREATE PROCEDURE clientes_por_saldo_minimo(IN saldo_minimo DECIMAL(10,2))
BEGIN
    SELECT nombre, email, saldo
    FROM clientes
    WHERE saldo >= saldo_minimo
    ORDER BY saldo DESC;
END //

DELIMITER ;

CALL clientes_por_saldo_minimo(100.00);

DELIMITER //

-- Parámetro de salida (OUT): el procedimiento "devuelve" un valor a través de él
CREATE PROCEDURE contar_pedidos_cliente(
    IN  id_cliente INT,
    OUT total_pedidos INT
)
BEGIN
    SELECT COUNT(*) INTO total_pedidos
    FROM pedidos
    WHERE cliente_id = id_cliente;
END //

DELIMITER ;

-- Usar el procedimiento con una variable de sesión para capturar el OUT
CALL contar_pedidos_cliente(5, @resultado);
SELECT @resultado;

DELIMITER //

-- Parámetro INOUT: se pasa con un valor y se modifica dentro del procedimiento
CREATE PROCEDURE aplicar_descuento(INOUT precio DECIMAL(10,2), IN porcentaje INT)
BEGIN
    SET precio = precio - (precio * porcentaje / 100);
END //

DELIMITER ;

SET @precio_original = 100.00;
CALL aplicar_descuento(@precio_original, 20);
SELECT @precio_original;   -- 80.00

Lógica de control: condicionales y bucles

DELIMITER //

CREATE PROCEDURE clasificar_cliente(IN id_cliente INT)
BEGIN
    DECLARE saldo_actual DECIMAL(10, 2);
    DECLARE categoria VARCHAR(20);

    SELECT saldo INTO saldo_actual FROM clientes WHERE id = id_cliente;

    IF saldo_actual >= 1000 THEN
        SET categoria = 'Premium';
    ELSEIF saldo_actual >= 500 THEN
        SET categoria = 'Gold';
    ELSEIF saldo_actual >= 100 THEN
        SET categoria = 'Standard';
    ELSE
        SET categoria = 'Básico';
    END IF;

    SELECT id_cliente AS cliente_id, saldo_actual AS saldo, categoria;
END //

DELIMITER ;

CALL clasificar_cliente(3);

DELIMITER //

-- Bucle WHILE para procesar varias filas
CREATE PROCEDURE aplicar_descuento_masivo(IN porcentaje INT)
BEGIN
    DECLARE contador INT DEFAULT 0;
    DECLARE total_productos INT;

    SELECT COUNT(*) INTO total_productos FROM productos;

    WHILE contador < total_productos DO
        UPDATE productos
        SET precio = precio - (precio * porcentaje / 100)
        LIMIT 1;
        SET contador = contador + 1;
    END WHILE;
END //

DELIMITER ;

-- En la práctica, esto se haría mejor con un UPDATE directo:
-- UPDATE productos SET precio = precio * (1 - porcentaje/100);
-- El bucle es solo para ilustrar la sintaxis de control de flujo

Manejo de errores en MySQL

DELIMITER //

CREATE PROCEDURE crear_pedido(
    IN id_cliente INT,
    IN id_producto INT,
    IN cant INT,
    OUT mensaje VARCHAR(255)
)
BEGIN
    DECLARE precio_unitario DECIMAL(10, 2);
    DECLARE stock_disponible INT;

    -- Handler para capturar cualquier error SQL y hacer rollback
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET mensaje = 'Error: la operación falló y se revirtió';
    END;

    START TRANSACTION;

    SELECT precio, stock INTO precio_unitario, stock_disponible
    FROM productos WHERE id = id_producto;

    IF stock_disponible < cant THEN
        SET mensaje = 'Error: stock insuficiente';
        ROLLBACK;
    ELSE
        INSERT INTO pedidos (cliente_id, producto_id, cantidad, total)
        VALUES (id_cliente, id_producto, cant, precio_unitario * cant);

        UPDATE productos SET stock = stock - cant WHERE id = id_producto;

        COMMIT;
        SET mensaje = 'Pedido creado correctamente';
    END IF;
END //

DELIMITER ;

CALL crear_pedido(1, 3, 2, @resultado);
SELECT @resultado;

Funciones en MySQL

DELIMITER //

CREATE FUNCTION calcular_descuento(precio DECIMAL(10,2), porcentaje INT)
RETURNS DECIMAL(10, 2)
DETERMINISTIC   -- indica que con los mismos inputs siempre da el mismo output
BEGIN
    RETURN precio - (precio * porcentaje / 100);
END //

DELIMITER ;

-- Las funciones se usan DENTRO de un SELECT, no con CALL
SELECT calcular_descuento(100, 20);   -- 80.00

SELECT
  nombre,
  precio,
  calcular_descuento(precio, 15) AS precio_con_descuento
FROM productos;

DELIMITER //

CREATE FUNCTION nivel_cliente(saldo_actual DECIMAL(10,2))
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    DECLARE resultado VARCHAR(20);

    IF saldo_actual >= 1000 THEN
        SET resultado = 'Premium';
    ELSEIF saldo_actual >= 500 THEN
        SET resultado = 'Gold';
    ELSE
        SET resultado = 'Standard';
    END IF;

    RETURN resultado;
END //

DELIMITER ;

-- Usar la función directamente en consultas, incluso en WHERE o GROUP BY
SELECT nombre, saldo, nivel_cliente(saldo) AS categoria
FROM clientes
ORDER BY saldo DESC;

SELECT nivel_cliente(saldo), COUNT(*)
FROM clientes
GROUP BY nivel_cliente(saldo);

Procedimientos almacenados en PostgreSQL

PostgreSQL usa el lenguaje PL/pgSQL por defecto (aunque soporta otros como PL/Python o PL/Perl). La sintaxis es diferente a MySQL: en lugar de DELIMITER, se usan los signos $$ para delimitar el cuerpo de la función o procedimiento.

Procedimientos con CREATE PROCEDURE

CREATE OR REPLACE PROCEDURE crear_pedido(
    p_cliente_id  INT,
    p_producto_id INT,
    p_cantidad    INT
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_precio DECIMAL(10, 2);
    v_stock  INT;
BEGIN
    SELECT precio, stock INTO v_precio, v_stock
    FROM productos WHERE id = p_producto_id;

    IF v_stock < p_cantidad THEN
        RAISE EXCEPTION 'Stock insuficiente: disponible %, solicitado %', v_stock, p_cantidad;
    END IF;

    INSERT INTO pedidos (cliente_id, producto_id, cantidad, total)
    VALUES (p_cliente_id, p_producto_id, p_cantidad, v_precio * p_cantidad);

    UPDATE productos SET stock = stock - p_cantidad WHERE id = p_producto_id;

    RAISE NOTICE 'Pedido creado correctamente';
END;
$$;

-- Ejecutar con CALL
CALL crear_pedido(1, 3, 2);

En PostgreSQL, los procedimientos (a diferencia de las funciones) pueden gestionar transacciones explícitamente con COMMIT y ROLLBACK dentro de su cuerpo, lo que es muy útil para procesos largos que necesitan confirmar pasos intermedios.

CREATE OR REPLACE PROCEDURE procesar_lote_pedidos(lote_size INT)
LANGUAGE plpgsql
AS $$
DECLARE
    procesados INT := 0;
    pedido_actual RECORD;
BEGIN
    FOR pedido_actual IN
        SELECT id FROM pedidos WHERE total > 1000
    LOOP
        UPDATE pedidos SET total = total * 0.95 WHERE id = pedido_actual.id;
        procesados := procesados + 1;

        -- Confirmar cada lote de N registros para no mantener una transacción gigante
        IF procesados % lote_size = 0 THEN
            COMMIT;
            RAISE NOTICE 'Confirmados % registros', procesados;
        END IF;
    END LOOP;

    COMMIT;   -- confirmar el resto
END;
$$;

CALL procesar_lote_pedidos(100);

Funciones en PostgreSQL

CREATE OR REPLACE FUNCTION calcular_descuento(precio DECIMAL, porcentaje INT)
RETURNS DECIMAL
LANGUAGE plpgsql
IMMUTABLE   -- el resultado solo depende de los argumentos, nunca cambia
AS $$
BEGIN
    RETURN precio - (precio * porcentaje / 100.0);
END;
$$;

SELECT calcular_descuento(100, 20);   -- 80.00

SELECT nombre, precio, calcular_descuento(precio, 15) AS precio_con_descuento
FROM productos;

Funciones con sintaxis SQL pura (más simples y eficientes)

Cuando la lógica es una sola expresión SQL sin necesidad de control de flujo, PostgreSQL permite escribir la función directamente en SQL, que el motor puede optimizar mejor que el código PL/pgSQL.

CREATE OR REPLACE FUNCTION nivel_cliente(saldo_actual DECIMAL)
RETURNS VARCHAR
LANGUAGE sql
IMMUTABLE
AS $$
    SELECT CASE
        WHEN saldo_actual >= 1000 THEN 'Premium'
        WHEN saldo_actual >= 500  THEN 'Gold'
        ELSE 'Standard'
    END;
$$;

SELECT nombre, saldo, nivel_cliente(saldo) AS categoria
FROM clientes
ORDER BY saldo DESC;

Funciones que devuelven múltiples filas (RETURNS TABLE)

CREATE OR REPLACE FUNCTION pedidos_de_cliente(p_cliente_id INT)
RETURNS TABLE (
    pedido_id INT,
    producto  VARCHAR,
    cantidad  INT,
    total     DECIMAL
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT p.id, pr.nombre, p.cantidad, p.total
    FROM pedidos p
    JOIN productos pr ON pr.id = p.producto_id
    WHERE p.cliente_id = p_cliente_id
    ORDER BY p.fecha DESC;
END;
$$;

-- Se consulta como si fuera una tabla
SELECT * FROM pedidos_de_cliente(5);
SELECT * FROM pedidos_de_cliente(5) WHERE total > 100;

Manejo de errores en PostgreSQL

CREATE OR REPLACE FUNCTION transferir_saldo(
    origen_id  INT,
    destino_id INT,
    cantidad   DECIMAL
)
RETURNS VARCHAR
LANGUAGE plpgsql
AS $$
DECLARE
    saldo_origen DECIMAL;
BEGIN
    SELECT saldo INTO saldo_origen FROM clientes WHERE id = origen_id;

    IF saldo_origen IS NULL THEN
        RAISE EXCEPTION 'El cliente origen % no existe', origen_id;
    END IF;

    IF saldo_origen < cantidad THEN
        RAISE EXCEPTION 'Saldo insuficiente: % disponible, % solicitado',
            saldo_origen, cantidad;
    END IF;

    UPDATE clientes SET saldo = saldo - cantidad WHERE id = origen_id;
    UPDATE clientes SET saldo = saldo + cantidad WHERE id = destino_id;

    RETURN 'Transferencia completada correctamente';

EXCEPTION
    WHEN OTHERS THEN
        RETURN 'Error: ' || SQLERRM;
END;
$$;

SELECT transferir_saldo(1, 2, 50.00);

Tabla comparativa de sintaxis: MySQL vs PostgreSQL

Triggers: ejecutar procedimientos automáticamente

Una de las aplicaciones más comunes de las funciones es activarlas automáticamente cuando ocurre un evento en una tabla, usando triggers.

-- MySQL: trigger que actualiza el stock automáticamente al insertar un pedido
DELIMITER //

CREATE TRIGGER reducir_stock_tras_pedido
AFTER INSERT ON pedidos
FOR EACH ROW
BEGIN
    UPDATE productos
    SET stock = stock - NEW.cantidad
    WHERE id = NEW.producto_id;
END //

DELIMITER ;

-- Ahora cada INSERT en pedidos reduce el stock automáticamente
INSERT INTO pedidos (cliente_id, producto_id, cantidad, total)
VALUES (1, 3, 2, 199.98);

-- PostgreSQL: requiere definir una función y luego asociarla con un trigger
CREATE OR REPLACE FUNCTION fn_reducir_stock()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE productos
    SET stock = stock - NEW.cantidad
    WHERE id = NEW.producto_id;

    RETURN NEW;
END;
$$;

CREATE TRIGGER reducir_stock_tras_pedido
AFTER INSERT ON pedidos
FOR EACH ROW
EXECUTE FUNCTION fn_reducir_stock();

Cuándo usar procedimientos y funciones, y cuándo evitarlos

Úsalos cuando:

• Necesitas que varias operaciones se ejecuten como una unidad atómica con control de transacciones explícito.
• La lógica se repite en muchas consultas y quieres centralizarla en un único lugar.
• Quieres reducir el número de viajes de ida y vuelta entre la aplicación y la base de datos (mover el procesamiento de varios pasos a un único CALL).
• Necesitas reglas de negocio que deben cumplirse sin importar qué aplicación o cliente acceda a la base de datos.

Evítalos cuando:

• La lógica de negocio cambia con frecuencia: los procedimientos almacenados son más difíciles de versionar y de probar que el código en tu aplicación con un framework de testing maduro.
• Quieres mantener tu base de datos lo más "tonta" posible y centralizar toda la lógica en la capa de aplicación, lo que facilita escalar horizontalmente y portar a otro motor de base de datos en el futuro.
• Tu equipo no tiene experiencia con PL/pgSQL o SQL/PSM: el código se vuelve difícil de mantener si nadie en el equipo lo entiende bien.

La decisión depende mucho de la cultura del equipo y del proyecto. Equipos con fuerte tradición de bases de datos (banca, sistemas legados, ERPs) suelen apoyarse mucho en procedimientos almacenados. Equipos con arquitecturas modernas orientadas a microservicios suelen preferir mantener la lógica de negocio en el código de la aplicación y usar la base de datos solo para persistencia.

Resumen

• Las funciones devuelven siempre un valor y se usan dentro de consultas SELECT. Los procedimientos no necesariamente devuelven nada y se ejecutan con CALL.
• En MySQL se usa DELIMITER para definir el cuerpo y DECLARE/SET para variables. En PostgreSQL se usa $$ ... $$ y la sintaxis := para asignación.
• PostgreSQL permite que los procedimientos gestionen transacciones explícitamente con COMMIT y ROLLBACK dentro de su cuerpo, ideal para procesos por lotes largos.
• RETURNS TABLE en PostgreSQL permite que una función devuelva múltiples filas y se consulte como si fuera una tabla.
• Los triggers ejecutan procedimientos o funciones automáticamente ante eventos como INSERT, UPDATE o DELETE.
• Úsalos para lógica de negocio que debe ser consistente sin importar el cliente que acceda a los datos. Evítalos si prefieres mantener toda la lógica versionada y testeada en el código de la aplicación.