Uso Avanzado de Cursores en MySQL utilizando la Base de Datos «Comisaria»

Los cursores en MySQL son herramientas potentes que permiten procesar filas individuales de un conjunto de resultados de una manera controlada. En este artículo, exploraremos cómo crear y utilizar múltiples cursores en 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');

Uso de Cursores en Procedimientos Almacenados

Creación de un Procedimiento con Múltiples Cursores

Vamos a crear un procedimiento almacenado que utiliza múltiples cursores para procesar datos de diferentes tablas. Este procedimiento generará un informe detallado de todos los casos y los ciudadanos involucrados.

DELIMITER //

CREATE PROCEDURE GenerarInformeCasos()
BEGIN
    DECLARE done1 INT DEFAULT FALSE;
    DECLARE done2 INT DEFAULT FALSE;
    DECLARE v_id_caso INT;
    DECLARE v_descripcion TEXT;
    DECLARE v_fecha_inicio DATE;
    DECLARE v_estado VARCHAR(20);
    DECLARE v_id_agente INT;
    DECLARE v_id_ciudadano INT;
    DECLARE v_nombre_ciudadano VARCHAR(50);
    DECLARE v_direccion_ciudadano VARCHAR(100);
    DECLARE v_telefono_ciudadano VARCHAR(15);

    -- Cursor para casos
    DECLARE cursor_casos CURSOR FOR
        SELECT id_caso, descripcion, fecha_inicio, estado, id_agente FROM Casos;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = TRUE;

    -- Cursor para ciudadanos
    DECLARE cursor_ciudadanos 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 = v_id_caso;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done2 = TRUE;

    OPEN cursor_casos;
    read_casos: LOOP
        FETCH cursor_casos INTO v_id_caso, v_descripcion, v_fecha_inicio, v_estado, v_id_agente;
        IF done1 THEN
            LEAVE read_casos;
        END IF;

        -- Procesar cada caso
        SELECT CONCAT('Caso: ', v_id_caso, ', Descripción: ', v_descripcion, ', Fecha: ', v_fecha_inicio, ', Estado: ', v_estado) AS InfoCaso;

        OPEN cursor_ciudadanos;
        read_ciudadanos: LOOP
            FETCH cursor_ciudadanos INTO v_id_ciudadano, v_nombre_ciudadano, v_direccion_ciudadano, v_telefono_ciudadano;
            IF done2 THEN
                LEAVE read_ciudadanos;
            END IF;

            -- Procesar cada ciudadano involucrado en el caso
            SELECT CONCAT('    Ciudadano: ', v_nombre_ciudadano, ', Dirección: ', v_direccion_ciudadano, ', Teléfono: ', v_telefono_ciudadano) AS InfoCiudadano;
        END LOOP;
        CLOSE cursor_ciudadanos;
    END LOOP;
    CLOSE cursor_casos;
END //

DELIMITER ;

Explicación del Procedimiento

1. Declaración de Variables: Se declaran variables para almacenar los datos de los casos y ciudadanos.
2. Declaración de Cursores: Se definen dos cursores, uno para recorrer la tabla Casos y otro para recorrer la tabla Ciudadanos asociada a cada caso.
3. Handlers de Continuación: Se utilizan handlers para gestionar el fin del conjunto de resultados de cada cursor.
4. Loop Principal: El loop principal recorre cada caso utilizando el cursor_casos.
5. Sub-loop para Ciudadanos: Para cada caso, se abre un cursor secundario cursor_ciudadanos para recorrer los ciudadanos involucrados en ese caso.
6. Procesamiento de Datos: Dentro de cada loop, se procesan y seleccionan los datos necesarios para el informe.

Llamada al Procedimiento

Para generar el informe, simplemente llamamos al procedimiento:

CALL GenerarInformeCasos();

Conclusión

El uso de múltiples cursores en MySQL permite procesar y manejar datos complejos de manera eficiente. En este artículo, hemos demostrado cómo utilizar cursores para generar informes detallados en una base de datos de una comisaría, facilitando así la gestión y el análisis de la información almacenada. Con estas técnicas avanzadas, se pueden realizar operaciones más sofisticadas y precisas en bases de datos MySQL.