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

Las funciones en MySQL son esenciales para realizar cálculos y operaciones reutilizables que pueden integrarse directamente en las consultas SQL. A continuación, exploraremos cómo crear y utilizar varias funciones dentro de la base de datos de una comisaría para optimizar y automatizar diversas tareas.

Creación de la Base de Datos y Tablas

Para comenzar, 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 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');

Funciones en MySQL

1. Función para Calcular el Salario Anual de un Agente

Esta función calcula el salario anual de un agente basado en su salario mensual.

DELIMITER //

CREATE FUNCTION CalcularSalarioAnual(
    p_salario DECIMAL(10, 2)
) RETURNS DECIMAL(10, 2)
BEGIN
    RETURN p_salario * 12;
END //

DELIMITER ;

Para usar esta función en una consulta, haríamos lo siguiente:

SELECT nombre, CalcularSalarioAnual(salario) AS salario_anual
FROM Agentes;

2. Función para Obtener la Antigüedad de un Agente

Esta función calcula la antigüedad de un agente en años desde su fecha de ingreso.

DELIMITER //

CREATE FUNCTION ObtenerAntiguedad(
    p_fecha_ingreso DATE
) RETURNS INT
BEGIN
    RETURN TIMESTAMPDIFF(YEAR, p_fecha_ingreso, CURDATE());
END //

DELIMITER ;

Para usar esta función en una consulta, haríamos lo siguiente:

SELECT nombre, ObtenerAntiguedad(fecha_ingreso) AS antiguedad
FROM Agentes;

3. Función para Contar el Número de Casos por Estado

Esta función cuenta el número de casos en un estado específico.

DELIMITER //

CREATE FUNCTION ContarCasosPorEstado(
    p_estado VARCHAR(20)
) RETURNS INT
BEGIN
    DECLARE v_count INT;
    SELECT COUNT(*) INTO v_count FROM Casos WHERE estado = p_estado;
    RETURN v_count;
END //

DELIMITER ;

Para usar esta función en una consulta, haríamos lo siguiente:

SELECT ContarCasosPorEstado('Abierto') AS casos_abiertos;

4. Función para Obtener el Nombre del Ciudadano a Partir de su ID

Esta función devuelve el nombre de un ciudadano basado en su ID.

DELIMITER //

CREATE FUNCTION ObtenerNombreCiudadano(
    p_id_ciudadano INT
) RETURNS VARCHAR(50)
BEGIN
    DECLARE v_nombre VARCHAR(50);
    SELECT nombre INTO v_nombre FROM Ciudadanos WHERE id_ciudadano = p_id_ciudadano;
    RETURN v_nombre;
END //

DELIMITER ;

Para usar esta función en una consulta, haríamos lo siguiente:

SELECT ObtenerNombreCiudadano(1) AS nombre_ciudadano;

Conclusión

Las funciones en MySQL son una herramienta poderosa para realizar cálculos y operaciones reutilizables dentro de consultas SQL. En este artículo, hemos creado varias funciones utilizando la base de datos de una comisaría, demostrando cómo calcular el salario anual de un agente, obtener su antigüedad, contar el número de casos por estado y obtener el nombre de un ciudadano basado en su ID. Estas funciones no solo mejoran la eficiencia y la claridad de las consultas, sino que también aseguran la consistencia y la reutilización del código en toda la base de datos.