Ejemplo de procedimiento complejo para procesar pedidos: validación de datos, actualización de estado, logging

Hoy vamos a ver cómo construir un procedimiento real para procesar pedidos. Incluye varios pasos: validación de datos, actualización del estado del pedido y también logging. Imagina un restaurante donde el chef, el camarero y el cajero tienen que actuar coordinados. En nuestro procedimiento vamos a implementar una lógica parecida de interacción entre etapas.

Descripción de la tarea del procedimiento

El procedimiento para procesar pedidos debe realizar los siguientes pasos:

1. Comprobar si el producto necesario está disponible en el almacén.
2. Si hay suficiente producto, descontar la cantidad del almacén.
3. Actualizar el estado del pedido para que sea "Procesado".
4. Registrar la información de la operación exitosa en el log.
5. Si ocurre cualquier error, hacer rollback de los cambios al inicio.

Implementación del procedimiento

Paso 1. Creamos el esquema y las tablas para trabajar

Antes de escribir el procedimiento, vamos a crear las tablas con las que va a trabajar.

Tabla orders — pedidos

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_name TEXT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    status TEXT DEFAULT 'Pendiente'
);

Esta tabla guarda los pedidos. Cada pedido tiene un cliente, identificador de producto, cantidad y estado (por defecto "Pendiente").

Tabla inventory — almacén

CREATE TABLE inventory (
    product_id SERIAL PRIMARY KEY,
    product_name TEXT NOT NULL UNIQUE,
    stock INT NOT NULL CHECK (stock >= 0)
);

Tabla con la lista de productos en el almacén. Cada producto tiene stock actual (stock).

Tabla order_logs — log de operaciones

CREATE TABLE order_logs (
    log_id SERIAL PRIMARY KEY,
    order_id INT NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
    log_message TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

El log se usará para guardar información sobre el estado de ejecución de los pedidos.

Paso 2. Estructura del procedimiento

Aquí tienes la estructura del procedimiento de varios pasos:

1. Comprobar si el producto solicitado está en el almacén y si hay suficiente cantidad.
2. Si hay suficiente producto, reducir su cantidad en la tabla inventory.
3. Cambiar el estado del pedido a "Procesado".
4. Registrar el resultado exitoso en la tabla order_logs.
5. Manejar posibles errores con rollback de los cambios.

Paso 3. Escribiendo el procedimiento

Vamos a escribir el procedimiento process_order para realizar los pasos descritos arriba.

CREATE OR REPLACE FUNCTION process_order(order_id INT)
RETURNS VOID AS $$
DECLARE
    v_product_id INT;
    v_quantity INT;
    v_stock INT;
BEGIN
    -- Paso 1: Obtenemos información del pedido
    SELECT product_id, quantity
    INTO v_product_id, v_quantity
    FROM orders
    WHERE order_id = $1;

    -- Comprobamos si el pedido existe
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Pedido con ID % no existe.', $1;
    END IF;

    -- Paso 2: Comprobamos si el producto está en el almacén
    SELECT stock INTO v_stock
    FROM inventory
    WHERE product_id = v_product_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Producto con ID % no existe en el almacén.', v_product_id;
    END IF;

    IF v_stock < v_quantity THEN
        RAISE EXCEPTION 'No hay suficiente stock para el producto ID %. Solicitado: %, Disponible: %.',
            v_product_id, v_quantity, v_stock;
    END IF;

    -- Paso 3: Reducimos la cantidad del producto en el almacén
    UPDATE inventory
    SET stock = stock - v_quantity
    WHERE product_id = v_product_id;

    -- Paso 4: Actualizamos el estado del pedido a 'Procesado'
    UPDATE orders
    SET status = 'Procesado'
    WHERE order_id = $1;

    -- Paso 5: Registramos la ejecución exitosa en el log
    INSERT INTO order_logs (order_id, log_message)
    VALUES ($1, 'Pedido procesado correctamente.');

EXCEPTION
    WHEN OTHERS THEN
        -- Logueamos el error en caso de fallo
        INSERT INTO order_logs (order_id, log_message)
        VALUES ($1, 'Error al procesar el pedido: ' || SQLERRM);

        -- Hacemos rollback de todos los cambios
        RAISE;
END;
$$ LANGUAGE plpgsql;

Vamos a analizar este procedimiento.

1. Etapa de comprobación:

   comprobamos si el pedido indicado existe en la tabla orders. Si no se encuentra el pedido, se lanza una excepción con un mensaje detallado. De forma parecida, comprobamos la existencia y cantidad del producto en el almacén.

2. Etapa de trabajo con el almacén:

   si hay suficiente producto, reducimos su cantidad en el almacén. Esto se hace con UPDATE.

3. Etapa de cambio de estado del pedido:

   cambiamos el estado a "Procesado" para indicar que el pedido se completó correctamente.

4. Etapa de logging:

   después de procesar el pedido con éxito, añadimos un mensaje en la tabla order_logs para guardar la información de la operación.

5. Manejo de excepciones:

   si algo sale mal, capturamos el error en el bloque EXCEPTION, escribimos en el log un mensaje detallado del error y hacemos rollback de todos los cambios.

Ejemplos de uso

Vamos a crear datos de prueba para comprobar cómo funciona nuestro procedimiento.

-- Añadimos productos al almacén
INSERT INTO inventory (product_name, stock)
VALUES ('Laptop', 10), ('Monitor', 5);

-- Añadimos pedidos
INSERT INTO orders (customer_name, product_id, quantity)
VALUES
    ('Alicia', 1, 2),
    ('Beto', 2, 1),
    ('Carlos', 1, 20); -- Este pedido debe lanzar un error

Ahora probamos el procedimiento:

-- Procesamos el pedido de Alicia
SELECT process_order(1);

-- Procesamos el pedido de Beto
SELECT process_order(2);

-- Intentamos procesar el pedido de Carlos (error)
SELECT process_order(3);

Resultados:

• Los pedidos de Alicia y Beto se procesarán correctamente, se registrarán en el log y el stock del almacén disminuirá.
• El pedido de Carlos lanzará un error por falta de stock suficiente, y aparecerá un registro de error en el log.

Comprobamos las tablas después de ejecutar las consultas:

SELECT * FROM inventory; -- Cambios en el stock
SELECT * FROM orders; -- Cambios en los estados de los pedidos
SELECT * FROM order_logs; -- Registros en el log

Errores típicos y consejos

1. Error: olvidaste comprobar NOT FOUND después de SELECT INTO.

   Siempre maneja los casos en los que la consulta devuelve resultado vacío, si no, puede causar excepciones inesperadas.

2. Error: no añadiste el bloque EXCEPTION.

   Si el procedimiento no tiene manejador de errores, una excepción puede dejar la transacción colgada o romper la lógica de ejecución.

3. Consejo: protégete contra SQL injection.

   Usa parámetros fuertemente tipados y evita SQL dinámico si no es necesario.

Ampliando el procedimiento

En la vida real puedes añadir más comprobaciones, por ejemplo:

• Tener en cuenta descuentos o promociones para clientes.
• Comprobar el límite de crédito del cliente antes de procesar el pedido.
• Loguear no solo operaciones exitosas, sino también rollbacks.

2

Tarea

SQL SELF, nivel 54, lección 0

Bloqueada

Procedimiento de procesamiento de pedidos

Procedimiento de procesamiento de pedidos

Abrir