Creación y Uso de Procedimientos Almacenados en MySQL utilizando la Base de Datos «Comisaria»

Los procedimientos almacenados en MySQL son una herramienta poderosa que permite la ejecución de secuencias predefinidas de comandos SQL, lo que facilita la automatización y estandarización de tareas repetitivas y complejas. En este artículo, exploraremos la creación de varios procedimientos almacenados utilizando la base de datos de una comisaría como ejemplo.

Creación de la Base de Datos y Tablas

Comenzamos con la creación de la base de datos y las tablas necesarias para nuestro ejemplo:

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 en la tabla Agentes
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');

-- Insertar datos en la tabla Ciudadanos
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');

-- Insertar datos en la tabla Casos
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);

-- Insertar datos en la tabla Reportes
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 Almacenados en MySQL

1. Procedimiento para Agregar un Nuevo Agente

Este procedimiento añade un nuevo agente a la tabla Agentes.

DELIMITER //

CREATE PROCEDURE AddAgente(
    IN p_nombre VARCHAR(50),
    IN p_rango VARCHAR(30),
    IN p_salario DECIMAL(10, 2),
    IN p_fecha_ingreso DATE
)
BEGIN
    INSERT INTO Agentes (nombre, rango, salario, fecha_ingreso) 
    VALUES (p_nombre, p_rango, p_salario, p_fecha_ingreso);
END //

DELIMITER ;

Para llamar a este procedimiento y añadir un nuevo agente, usaríamos:

CALL AddAgente('Laura Torres', 'Capitán', 55000.00, '2021-03-14');

2. Procedimiento para Actualizar el Estado de un Caso

Este procedimiento actualiza el estado de un caso específico en la tabla Casos.

DELIMITER //

CREATE PROCEDURE UpdateEstadoCaso(
    IN p_id_caso INT,
    IN p_nuevo_estado VARCHAR(20)
)
BEGIN
    UPDATE Casos
    SET estado = p_nuevo_estado
    WHERE id_caso = p_id_caso;
END //

DELIMITER ;

Para llamar a este procedimiento y actualizar el estado de un caso, usaríamos:

CALL UpdateEstadoCaso(1, 'Cerrado');

3. Procedimiento para Generar un Informe de Casos por Agente

Este procedimiento genera un informe de todos los casos asignados a un agente específico.

DELIMITER //

CREATE PROCEDURE InformeCasosPorAgente(
    IN p_id_agente INT
)
BEGIN
    SELECT c.id_caso, c.descripcion, c.fecha_inicio, c.estado
    FROM Casos c
    WHERE c.id_agente = p_id_agente;
END //

DELIMITER ;

Para llamar a este procedimiento y generar un informe de casos para un agente, usaríamos:

CALL InformeCasosPorAgente(1);

4. Procedimiento para Eliminar un Reporte

Este procedimiento elimina un reporte específico de la tabla Reportes.

DELIMITER //

CREATE PROCEDURE DeleteReporte(
    IN p_id_reporte INT
)
BEGIN
    DELETE FROM Reportes
    WHERE id_reporte = p_id_reporte;
END //

DELIMITER ;

Para llamar a este procedimiento y eliminar un reporte, usaríamos:

CALL DeleteReporte(1);

Conclusión

Los procedimientos almacenados en MySQL permiten la ejecución de secuencias de comandos SQL de manera automatizada y eficiente. En este artículo, hemos creado varios procedimientos utilizando la base de datos de una comisaría, demostrando cómo se pueden añadir nuevos agentes, actualizar el estado de casos, generar informes y eliminar reportes de manera sencilla y estructurada. Los procedimientos almacenados no solo mejoran la eficiencia de la base de datos sino que también aseguran la consistencia y la integridad de los datos a lo largo del tiempo.