MySQL - Capitulo 5

Una guía através del camino de las bases de datos con MySQL uno de los sistemas de gestión de bases de datos más conocidos y utilizados en el mundo de la programación.

Síguenos con el tutorial

En QuéPasalinux MySQL - Stored Procedures en MySQL un vídeo práctico sobre MySQL y Docker

Volver a Página Principal

Ver Codigo en GitLab

📚 Curso de Capacitación de MySQL

🚀 Módulo 5: Procedimientos Almacenados (Stored Procedures) ✨ ¡La Caja de Herramientas de tu Base de Datos!

¡Hola de nuevo! 👋 ¡Bienvenidos al Módulo 5! Hemos recorrido un largo camino. Hemos aprendido a estructurar, manipular y consultar datos de forma avanzada usando JOINs, vistas y funciones. Ahora, es momento de unir todo ese conocimiento en una de las herramientas más poderosas y prácticas de MySQL: los Procedimientos Almacenados (Stored Procedures).

Piensa en un procedimiento almacenado como un “robot” 🤖 que guardas en tu base de datos. Este robot contiene una secuencia de pasos (comandos SQL y lógica de programación) para realizar una tarea específica, como crear un nuevo cliente, procesar una venta o generar un reporte. En lugar de ejecutar múltiples comandos desde tu aplicación, simplemente “llamas” al robot, y la base de datos se encarga de todo el trabajo de forma eficiente. ¡Es la clave para automatizar y estandarizar tus operaciones! 🚀

5.1. ¿Qué es un Procedimiento Almacenado y por qué lo necesitas? 🤔

Un procedimiento almacenado es un conjunto de sentencias SQL y lógica de programación (variables, condicionales, bucles) que se compila y se almacena en la base de datos.

La diferencia clave con una función almacenada (que vimos en el Módulo 4) es que un procedimiento:

Puede ejecutar múltiples sentencias DML (INSERT, UPDATE, DELETE) en una sola llamada.
Puede no devolver un valor (aunque puede retornar resultados a través de parámetros o de una consulta SELECT).

¿Por qué usar procedimientos almacenados? 👇

Eficiencia (Rendimiento): Una vez que un procedimiento se compila y almacena, las llamadas futuras son mucho más rápidas porque no hay necesidad de enviar y procesar múltiples comandos SQL por la red. Menos tráfico de red = mejor rendimiento. ⚡
Encapsulación de Lógica: Agrupa lógica de negocio compleja en un solo lugar. Si la lógica cambia, solo actualizas el procedimiento, y todas las aplicaciones que lo usan se benefician del cambio sin necesidad de modificar su código. 🔄
Seguridad: Puedes dar a los usuarios permiso para ejecutar un procedimiento sin darles acceso directo a las tablas subyacentes. Esto te permite tener un control muy granular sobre qué acciones pueden realizar los usuarios. 🔒
Reutilización: Escribes el código una vez y lo llamas desde cualquier aplicación, script o herramienta que se conecte a tu base de datos. ✅

5.2. Creando tu Primer Procedimiento: La Sintaxis Mágica ✨

La sintaxis para crear un procedimiento es similar a la de una función, pero con algunas diferencias clave:

DELIMITER // -- Cambiamos el delimitador temporalmente

CREATE PROCEDURE nombre_procedimiento (
    IN parametro_entrada TIPO_DATO,
    OUT parametro_salida TIPO_DATO,
    INOUT parametro_entrada_salida TIPO_DATO
)
BEGIN
    -- Lógica SQL y de programación (INSERT, UPDATE, DELETE, SELECT, IF, etc.)
    -- Aquí pueden ir todas las sentencias SQL que necesites
END //

DELIMITER ; -- Volvemos el delimitador a su valor por defecto

IN: Un parámetro de entrada. El procedimiento puede leer su valor, pero no puede modificarlo. Es el tipo más común.
OUT: Un parámetro de salida. El procedimiento puede asignar un valor a este parámetro, y ese valor puede ser leído por el programa que llamó al procedimiento.
INOUT: Un parámetro de entrada y salida. El procedimiento puede leer y modificar su valor.

5.3. Ejemplos Prácticos: ¡Manos a la Obra! 💻

Vamos a crear algunos procedimientos que te ayudarán con las tareas más comunes que ya has realizado. Utilizaremos las tablas de tu curso (Clients, Ventas, Products, Users, etc.).

Ejemplo 1: Crear un Nuevo Cliente (Procedimiento `CrearCliente`) 🤝

Esta es una tarea común. En lugar de escribir una sentencia INSERT cada vez, creamos un procedimiento que la encapsula. Incluiremos lógica para validar si el cliente ya existe por su email.

DELIMITER //

CREATE PROCEDURE CrearCliente (
    IN p_nombre VARCHAR(100),
    IN p_apellidos VARCHAR(100),
    IN p_email VARCHAR(100),
    OUT p_resultado VARCHAR(100)
)
BEGIN
    -- Declaramos una variable para contar si el email ya existe
    DECLARE email_existente INT DEFAULT 0;

    -- Contamos cuántos clientes tienen este email
    SELECT COUNT(*) INTO email_existente
    FROM Clients
    WHERE email = p_email;

    -- Si el email ya existe, devolvemos un mensaje de error
    IF email_existente > 0 THEN
        SET p_resultado = 'Error: El email ya existe.';
    ELSE
        -- Si no existe, insertamos el nuevo cliente
        INSERT INTO Clients (
            id,
            nombre,
            apellidos,
            email,
            created_date,
            updated_date
        ) VALUES (
            CONCAT('CLI-', MD5(NOW())), -- Genera un ID único basado en la fecha
            p_nombre,
            p_apellidos,
            p_email,
            NOW(),
            NOW()
        );
        SET p_resultado = 'Cliente creado exitosamente.';
    END IF;
END //

DELIMITER ;

Llamando al procedimiento:

CALL CrearCliente('María', 'García', 'maria@ejemplo.com', @salida_mensaje);

-- Para ver el resultado:
SELECT @salida_mensaje;

5.4. Gestión Integral de Ventas con Procedimientos Almacenados

Este es un caso de uso ideal. Una venta es una transacción que implica múltiples pasos (crear la venta, añadir productos, registrar pagos). La lógica se encapsula en varios procedimientos que trabajan juntos. ![Flowchart for a sales process]

1. Procedimiento `CreateSale`: Crear una Nueva Venta

Este procedimiento inicializa una nueva venta en la tabla Ventas con un monto total de 0, que será actualizado posteriormente.

DELIMITER //

CREATE PROCEDURE CreateSale(
    IN p_id_usuario INT,    -- ID del usuario (vendedor)
    IN p_id_cliente INT,    -- ID del cliente
    IN p_id_modified INT    -- ID del usuario que crea/modifica (para auditoría)
)
BEGIN
    DECLARE v_id_venta VARCHAR(50);
    DECLARE v_cliente_existe INT DEFAULT 0;
    DECLARE v_usuario_existe INT DEFAULT 0;

    -- Validar que el cliente exista
    SELECT COUNT(*) INTO v_cliente_existe FROM Clients WHERE id = p_id_cliente;
    IF v_cliente_existe = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'El ID de cliente no existe.';
    END IF;

    -- Validar que el usuario exista
    SELECT COUNT(*) INTO v_usuario_existe FROM Users WHERE id = p_id_usuario;
    IF v_usuario_existe = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'El ID de usuario no existe.';
    END IF;

    -- Generar un ID de venta único
    SET v_id_venta = CONCAT('VTA-', REPLACE(UUID(), '-', ''));

    INSERT INTO Ventas (
        id_venta,
        id_usuario,
        id_cliente,
        monto_total,
        created_date,
        last_modified,
        id_modified
    )
    VALUES (
        v_id_venta,
        p_id_usuario,
        p_id_cliente,
        0.00,
        NOW(),
        NOW(),
        p_id_modified
    );

    SELECT v_id_venta AS new_sale_id;

END //
DELIMITER ;

2. Procedimiento `UpdateSaleTotal`: Actualizar el Monto de la Venta

Este es un procedimiento auxiliar que recalcula el monto total de la venta cada vez que se añade o elimina un producto. No se llama directamente, sino que se invoca desde otros procedimientos.

DELIMITER //

CREATE PROCEDURE UpdateSaleTotal(
    IN p_id_venta VARCHAR(50)
)
BEGIN
    DECLARE v_new_total DECIMAL(10, 2);

    -- Calcular el nuevo monto total sumando los subtotales
    SELECT COALESCE(SUM(subtotal), 0.00)
    INTO v_new_total
    FROM Ventas_Productos
    WHERE id_venta = p_id_venta;

    -- Actualizar el monto_total en la tabla Ventas
    UPDATE Ventas
    SET monto_total = v_new_total,
        last_modified = NOW()
    WHERE id_venta = p_id_venta;

END //
DELIMITER ;

3. Procedimiento `AddSaleProduct`: Agregar Productos a una Venta

Este procedimiento añade un producto a una venta existente. Incluye validaciones para asegurar que los datos sean correctos y actualiza el monto total de la venta automáticamente.

DELIMITER //
CREATE PROCEDURE AddSaleProduct(
    IN p_id_venta VARCHAR(50),
    IN p_id_producto INT,
    IN p_cantidad INT,
    IN p_id_modified INT
)
BEGIN
    DECLARE v_precio_producto DECIMAL(10, 2);
    DECLARE v_subtotal_item DECIMAL(10, 2);
    DECLARE v_venta_existe INT DEFAULT 0;
    DECLARE v_producto_existe INT DEFAULT 0;

    -- Validar que la cantidad sea positiva
    IF p_cantidad <= 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'La cantidad del producto debe ser mayor que cero.';
    END IF;

    -- Validar que la venta exista
    SELECT COUNT(*) INTO v_venta_existe FROM Ventas WHERE id_venta = p_id_venta;
    IF v_venta_existe = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'El ID de la venta no existe.';
    END IF;

    -- Obtener el precio actual del producto
    SELECT sell_price INTO v_precio_producto FROM Products WHERE id = p_id_producto;

    -- Validar si el producto existe
    IF v_precio_producto IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'El ID de producto especificado no existe.';
    END IF;

    -- Calcular el subtotal para este item
    SET v_subtotal_item = v_precio_producto * p_cantidad;

    -- Insertar el detalle del producto
    INSERT INTO Ventas_Productos (
        id_venta,
        id_producto,
        cantidad,
        precio_venta,
        subtotal,
        created_date,
        last_modified,
        id_modified
    )
    VALUES (
        p_id_venta,
        p_id_producto,
        p_cantidad,
        v_precio_producto,
        v_subtotal_item,
        NOW(),
        NOW(),
        p_id_modified
    );

    -- Recalcular y actualizar el monto total de la venta
    CALL UpdateSaleTotal(p_id_venta);

    -- Retornar la lista actual de productos y el total de la venta
    SELECT
        V.monto_total AS total_venta_actualizado,
        VP.id AS id_detalle_producto,
        VP.id_producto,
        P.nombre_producto,
        VP.cantidad,
        VP.precio_venta,
        VP.subtotal
    FROM
        Ventas AS V
    INNER JOIN
        Ventas_Productos AS VP ON V.id_venta = VP.id_venta
    INNER JOIN
        Products AS P ON VP.id_producto = P.id
    WHERE
        V.id_venta = p_id_venta;

END //
DELIMITER ;

4. Procedimiento `RemoveSaleProduct`: Eliminar Productos de una Venta

Este procedimiento es una contraparte del anterior y es crucial para mantener la consistencia de los datos.

DELIMITER //
CREATE PROCEDURE RemoveSaleProduct(
    IN p_id_detalle_producto INT,  -- ID del registro en Ventas_Productos
    IN p_id_modified INT           -- ID del usuario que modifica
)
BEGIN
    DECLARE v_id_venta_asociada VARCHAR(50);
    DECLARE v_detalle_existe INT DEFAULT 0;

    -- Validar que el detalle de producto exista
    SELECT COUNT(*), id_venta INTO v_detalle_existe, v_id_venta_asociada
    FROM Ventas_Productos
    WHERE id = p_id_detalle_producto;

    IF v_detalle_existe = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'El ID de detalle de producto no existe.';
    END IF;

    -- Eliminar el registro
    DELETE FROM Ventas_Productos
    WHERE id = p_id_detalle_producto;

    -- Recalcular y actualizar el monto total de la venta
    CALL UpdateSaleTotal(v_id_venta_asociada);

    -- Retornar la lista actualizada de productos y el total de la venta
    SELECT
        V.monto_total AS total_venta_actualizado,
        VP.id AS id_detalle_producto,
        VP.id_producto,
        P.nombre_producto,
        VP.cantidad,
        VP.precio_venta,
        VP.subtotal
    FROM
        Ventas AS V
    INNER JOIN
        Ventas_Productos AS VP ON V.id_venta = VP.id_venta
    INNER JOIN
        Products AS P ON VP.id_producto = P.id
    WHERE
        V.id_venta = v_id_venta_asociada;

END //
DELIMITER ;

5. Procedimiento `AddSalePayment`: Registrar un Pago

Este procedimiento registra un pago para una venta. Incluye validaciones para los montos y los métodos de pago.

DELIMITER //
CREATE PROCEDURE AddSalePayment(
    IN p_id_venta VARCHAR(50),
    IN p_metodo_pago ENUM('Efectivo', 'Tarjeta de Crédito', 'Tarjeta de Débito', 'Transferencia Bancaria'),
    IN p_monto_pago DECIMAL(10, 2),
    IN p_referencia_pago_bancario VARCHAR(100),
    IN p_id_modified INT
)
BEGIN
    DECLARE v_id_pago VARCHAR(50);
    DECLARE v_total_venta DECIMAL(10, 2);
    DECLARE v_total_pagado DECIMAL(10, 2);
    DECLARE v_pago_restante DECIMAL(10, 2);
    DECLARE v_venta_existe INT DEFAULT 0;

    -- Validar que el monto del pago sea positivo
    IF p_monto_pago <= 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'El monto del pago debe ser mayor que cero.';
    END IF;

    -- Validar que la venta exista
    SELECT COUNT(*) INTO v_venta_existe FROM Ventas WHERE id_venta = p_id_venta;
    IF v_venta_existe = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'El ID de la venta no existe.';
    END IF;

    -- Validar que si el método es 'Transferencia Bancaria', la referencia no sea NULL o vacía
    IF p_metodo_pago = 'Transferencia Bancaria' AND (p_referencia_pago_bancario IS NULL OR p_referencia_pago_bancario = '') THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Para Transferencia Bancaria, la referencia de pago es obligatoria.';
    END IF;

    -- Si el método NO es 'Transferencia Bancaria', asegurar que la referencia sea NULL
    IF p_metodo_pago <> 'Transferencia Bancaria' THEN
        SET p_referencia_pago_bancario = NULL;
    END IF;

    -- Generar un ID de pago único
    SET v_id_pago = CONCAT('PAY-', REPLACE(UUID(), '-', ''));

    -- Insertar el registro de pago
    INSERT INTO Ventas_Pagos (
        id_venta,
        id_pago,
        metodo_pago,
        monto_pago,
        referencia_pago_bancario,
        created_date,
        last_modified,
        id_modified
    )
    VALUES (
        p_id_venta,
        v_id_pago,
        p_metodo_pago,
        p_monto_pago,
        p_referencia_pago_bancario,
        NOW(),
        NOW(),
        p_id_modified
    );

    -- Calcular y retornar el pago restante

    -- Obtener el monto total de la venta
    SELECT monto_total INTO v_total_venta FROM Ventas WHERE id_venta = p_id_venta;

    -- Calcular el total ya pagado para esta venta
    SELECT COALESCE(SUM(monto_pago), 0.00)
    INTO v_total_pagado
    FROM Ventas_Pagos
    WHERE id_venta = p_id_venta;

    -- Calcular el monto restante
    SET v_pago_restante = v_total_venta - v_total_pagado;

    -- Retornar el monto restante
    SELECT v_pago_restante AS pago_restante;

END //

DELIMITER ;

5.5. Convertir una Consulta en un Procedimiento (Reporte de Deuda) 📊

Una de las tareas del Módulo 2 era “determinar el monto total de compras por cada cliente”. Podrías convertir esto en un procedimiento para que cualquier persona pueda ejecutarlo sin escribir la consulta compleja.

Método Mejorado (Usando una Vista):

La mejor práctica es encapsular la lógica compleja en una vista y luego usar esa vista en el procedimiento. Esto hace que el código sea más legible y más fácil de mantener.

Primero, necesitas la vista:

CREATE VIEW Clientes_Ventas_Deuda_2 AS
SELECT
    V.id_venta AS ID_VENTA,
    C.Id AS ID_CLIENTE,
    CONCAT(C.Nombre, ' ', C.Apellidos) AS CLIENTE,
    V.monto_total AS TOTAL_VENTA,
    SUM(COALESCE(PP.monto_pago, 0)) AS TOTAL_PAGADO,
    (V.monto_total - SUM(COALESCE(PP.monto_pago, 0))) AS TOTAL_DEUDA
FROM Ventas V
JOIN Clientes C ON C.Id = V.id_cliente
LEFT JOIN Ventas_Pagos PP ON V.id_venta = PP.id_venta
GROUP BY ID_VENTA, ID_CLIENTE, CLIENTE
ORDER BY ID_VENTA DESC;

Ahora, el procedimiento almacenado que utiliza la vista con validaciones y mensajes:

DELIMITER //

-- Este procedimiento toma el ID de un cliente y devuelve el estado de su deuda.
-- Incluye validaciones para gestionar diferentes escenarios.
CREATE PROCEDURE ReporteDeudaClientePorID (
    IN p_id_cliente VARCHAR(255)
)
BEGIN
    -- Declaramos variables para el control de flujo y mensajes
    DECLARE cliente_existe INT DEFAULT 0;
    DECLARE total_deuda DECIMAL(10, 2) DEFAULT 0.00;

    -- Paso 1: Verificamos si el cliente existe
    SELECT COUNT(*) INTO cliente_existe
    FROM Clientes
    WHERE Id = p_id_cliente;

    -- Si el cliente no existe, mostramos un mensaje de error
    IF cliente_existe = 0 THEN
        -- Usamos SELECT para devolver un mensaje informativo
        SELECT 'Error: El ID de cliente no existe.' AS Mensaje;
    ELSE
        -- Paso 2: Verificamos el total de deuda del cliente
        -- Si la deuda es negativa, puede tener notas de crédito
        SELECT SUM(TOTAL_DEUDA) INTO total_deuda
        FROM Clientes_Ventas_Deuda_2
        WHERE ID_CLIENTE = p_id_cliente;

        -- Paso 3: Analizamos el estado de la deuda
        IF total_deuda < 0 THEN
            SELECT CONCAT('El cliente tiene un saldo a favor de ', -total_deuda, ' disponible como nota de crédito.') AS Mensaje;
        ELSEIF total_deuda = 0 THEN
            SELECT 'El cliente no tiene deuda pendiente.' AS Mensaje;
        ELSE
            -- Si la deuda es positiva, mostramos el reporte detallado
            -- Seleccionamos directamente las columnas de la vista que ya hizo los cálculos
            SELECT
                *
            FROM
                Clientes_Ventas_Deuda_2
            WHERE
                ID_CLIENTE = p_id_cliente AND TOTAL_DEUDA > 0
            ORDER BY
                TOTAL_DEUDA DESC;
        END IF;
    END IF;
END //

DELIMITER ;

Llamando al procedimiento:

-- Reemplaza 'ID_DEL_CLIENTE' con el ID real del cliente que quieres consultar
CALL ReporteDeudaClientePorID('ID_DEL_CLIENTE');

5.6. Gestión de Procedimientos Almacenados 🔧

Como con cualquier objeto de la base de datos, necesitas saber cómo gestionar tus procedimientos.

Listar todos los procedimientos: SHOW PROCEDURE STATUS WHERE Db = 'nombre_de_tu_base_de_datos'; Esto te mostrará una lista de todos los procedimientos en tu base de datos, junto con su creador, fecha de creación, y más.
Ver el código de un procedimiento: SHOW CREATE PROCEDURE ReporteDeudaClientePorID; Este comando te mostrará la sentencia CREATE PROCEDURE que usaste para crear el procedimiento. Es útil para auditar o para recordar la lógica interna.
Eliminar un procedimiento: DROP PROCEDURE ReporteDeudaClientePorID; Esto elimina el procedimiento de la base de datos. Una vez eliminado, ya no puedes llamarlo.

5.7. Mejores Prácticas (La clave para un buen desarrollador) 💡

Nombres Significativos: Usa nombres claros que indiquen la acción del procedimiento (ej: CreateSale, AddSaleProduct).
Validación de Entradas: Siempre valida los parámetros de entrada. Usa IF o SIGNAL para verificar si un registro existe o si los datos son válidos.
Manejo de Errores: Usa SIGNAL SQLSTATE para detener la ejecución y devolver un error claro si algo no cumple las validaciones.
Usa Transacciones: Para operaciones que involucran múltiples cambios (como crear una venta y luego añadirle productos), envuelve el código en una transacción para asegurar que todos los pasos se completen o ninguno. Esto es crucial en entornos de producción.
```
START TRANSACTION;
-- Lógica para la transacción
COMMIT; -- Confirma los cambios si todo salió bien
-- Si algo falla, puedes usar ROLLBACK;
```
¡Este tema lo veremos en el siguiente módulo!
Modularidad y Seguridad: Mantén los procedimientos enfocados en una sola tarea. Recuerda que puedes dar permisos para ejecutar un procedimiento sin dar acceso directo a las tablas subyacentes.

🚀 Tarea Práctica del Módulo 5

¡Es hora de poner en práctica lo aprendido!

Validar y Probar:
- Copia los procedimientos del Módulo 5 (CreateSale, AddSaleProduct, RemoveSaleProduct, AddSalePayment y ReporteDeudaClientePorID) en tu base de datos y asegúrate de que funcionen correctamente.
- Intenta llamar a AddSaleProduct con una cantidad negativa o un ID de producto que no existe. Observa cómo el procedimiento maneja el error y no inserta los datos.
Gestionar y Auditar:
- Usa SHOW PROCEDURE STATUS y SHOW CREATE PROCEDURE para familiarizarte con la gestión de tus procedimientos.
Reflexiona:
- ¿Cómo te ayuda el uso de estos procedimientos a simplificar el código en una aplicación que interactúa con la base de datos?
- ¿En qué escenarios específicos ves el mayor potencial para usar procedimientos almacenados en tu trabajo con la base de datos?