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.