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.

person encoding in laptop
Photo by Lukas on Pexels.com

Deja un comentario

error: Content is protected !!

Descubre más desde InfoGonzalez - Blog de formador e informático

Suscríbete ahora para seguir leyendo y obtener acceso al archivo completo.

Seguir leyendo

Este sitio web utiliza cookies, si necesitas más información puedes visitar nuestra política de privacidad    Ver
Privacidad
Creative Commons License
Except where otherwise noted, the content on this site is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.