Procedimientos y Funciones Avanzadas en MySQL con la Base de Datos de Comisaría

En este artículo, exploraremos técnicas avanzadas para el uso de procedimientos almacenados y funciones en MySQL, utilizando la base de datos de una comisaría como ejemplo. Abordaremos temas como el manejo de transacciones, el control de errores y el uso de cursores.

Creación de la Base de Datos y Tablas

Primero, creamos la base de datos y las tablas necesarias:

CREATE DATABASE Comisaria;
USE Comisaria;

-- Tabla de agentes
CREATE TABLE Agentes (
    id_agente INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(50) NOT NULL,
    rango VARCHAR(30),
    salario DECIMAL(10, 2) NOT NULL,
    fecha_ingreso DATE
);

-- Tabla de ciudadanos
CREATE TABLE Ciudadanos (
    id_ciudadano INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(50) NOT NULL,
    direccion VARCHAR(100),
    telefono VARCHAR(15)
);

-- Tabla de casos
CREATE TABLE Casos (
    id_caso INT AUTO_INCREMENT PRIMARY KEY,
    descripcion TEXT,
    fecha_inicio DATE,
    estado VARCHAR(20),
    id_agente INT,
    FOREIGN KEY (id_agente) REFERENCES Agentes(id_agente)
);

-- Tabla de reportes
CREATE TABLE Reportes (
    id_reporte INT AUTO_INCREMENT PRIMARY KEY,
    id_caso INT,
    id_ciudadano INT,
    descripcion TEXT,
    fecha_reporte DATE,
    FOREIGN KEY (id_caso) REFERENCES Casos(id_caso),
    FOREIGN KEY (id_ciudadano) REFERENCES Ciudadanos(id_ciudadano)
);

-- Insertar datos
INSERT INTO Agentes (nombre, rango, salario, fecha_ingreso) VALUES
('Juan Pérez', 'Sargento', 45000.00, '2015-06-23'),
('Ana Gómez', 'Teniente', 50000.00, '2018-09-15'),
('Carlos López', 'Cabo', 40000.00, '2020-01-10');

INSERT INTO Ciudadanos (nombre, direccion, telefono) VALUES
('Pedro Martínez', 'Calle Falsa 123', '555-1234'),
('María Fernández', 'Avenida Siempre Viva 742', '555-5678'),
('Luis García', 'Boulevard Central 456', '555-8765');

INSERT INTO Casos (descripcion, fecha_inicio, estado, id_agente) VALUES
('Robo en tienda', '2022-03-10', 'Abierto', 1),
('Asalto a mano armada', '2022-04-22', 'Cerrado', 2),
('Vandalismo en parque', '2022-05-15', 'En Proceso', 3);

INSERT INTO Reportes (id_caso, id_ciudadano, descripcion, fecha_reporte) VALUES
(1, 1, 'Robo de mercancía en tienda', '2022-03-10'),
(2, 2, 'Asalto a mano armada en banco', '2022-04-22'),
(3, 3, 'Daños en parque público', '2022-05-15');

Procedimientos Avanzados

Manejo de Transacciones

Un procedimiento almacenado puede incluir el manejo de transacciones para asegurar que un conjunto de operaciones se realicen de manera atómica.

DELIMITER //

CREATE PROCEDURE TransferirCaso(
    IN p_id_caso INT,
    IN p_nuevo_agente INT
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    -- Actualizar el agente asignado al caso
    UPDATE Casos
    SET id_agente = p_nuevo_agente
    WHERE id_caso = p_id_caso;

    -- Registrar el cambio en un log (por ejemplo, tabla LogCambios)
    INSERT INTO LogCambios (id_caso, id_agente_anterior, id_agente_nuevo, fecha_cambio)
    VALUES (p_id_caso, (SELECT id_agente FROM Casos WHERE id_caso = p_id_caso), p_nuevo_agente, NOW());

    COMMIT;
END //

DELIMITER ;

Este procedimiento transfiere un caso a un nuevo agente, asegurándose de que ambas operaciones (actualización del caso y registro del cambio) se realicen de manera atómica.

Uso de Cursores

Los cursores permiten recorrer los resultados de una consulta fila por fila. A continuación, un procedimiento que utiliza cursores para enviar un reporte a cada ciudadano involucrado en un caso específico.

DELIMITER //

CREATE PROCEDURE EnviarReportes(
    IN p_id_caso INT
)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_id_ciudadano INT;
    DECLARE v_nombre VARCHAR(50);
    DECLARE v_direccion VARCHAR(100);
    DECLARE v_telefono VARCHAR(15);

    DECLARE cur1 CURSOR FOR
        SELECT c.id_ciudadano, c.nombre, c.direccion, c.telefono
        FROM Ciudadanos c
        INNER JOIN Reportes r ON c.id_ciudadano = r.id_ciudadano
        WHERE r.id_caso = p_id_caso;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur1;

    read_loop: LOOP
        FETCH cur1 INTO v_id_ciudadano, v_nombre, v_direccion, v_telefono;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Aquí podríamos insertar lógica para enviar un reporte (simulado con un INSERT)
        INSERT INTO ReportesEnviados (id_ciudadano, nombre, direccion, telefono, fecha_envio)
        VALUES (v_id_ciudadano, v_nombre, v_direccion, v_telefono, NOW());
    END LOOP;

    CLOSE cur1;
END //

DELIMITER ;

Funciones Avanzadas

Control de Errores

Las funciones en MySQL también pueden beneficiarse del manejo de errores y la validación de datos.

DELIMITER //

CREATE FUNCTION CalcularTiempoServicio(
    p_fecha_ingreso DATE
) RETURNS INT
BEGIN
    DECLARE v_anios INT;

    IF p_fecha_ingreso IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'La fecha de ingreso no puede ser NULL';
    END IF;

    SET v_anios = TIMESTAMPDIFF(YEAR, p_fecha_ingreso, CURDATE());

    RETURN v_anios;
END //

DELIMITER ;

Esta función calcula los años de servicio de un agente basado en la fecha de ingreso, y genera un error si la fecha de ingreso es NULL.

Conclusión

El uso avanzado de procedimientos almacenados y funciones en MySQL permite una gestión más eficiente y robusta de las operaciones de la base de datos. El manejo de transacciones asegura la integridad de los datos, los cursores facilitan el procesamiento fila por fila y el control de errores mejora la fiabilidad de las funciones y procedimientos.

Estos conceptos son cruciales para el desarrollo de aplicaciones de bases de datos complejas y escalables.