Comprendiendo Procedures y Functions en MySQL

En este artículo, exploraremos la creación y el uso de procedimientos almacenados y funciones en MySQL, utilizando como ejemplo una base de datos de una comisaría.

Introducción a la Base de Datos «Comisaria»

Primero, creamos una base de datos llamada Comisaria y varias tablas que representan agentes, ciudadanos, casos y reportes. Aquí está el script inicial:

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

Un procedimiento almacenado es un conjunto de instrucciones SQL que pueden ser almacenadas y ejecutadas en el servidor de base de datos. Son útiles para realizar operaciones repetitivas y complejas.

Creación de un Procedimiento Almacenado

A continuación, crearemos un procedimiento almacenado que añade un nuevo agente a la base de datos.

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');

Funciones en MySQL

Una función es similar a un procedimiento almacenado, pero siempre devuelve un valor. Las funciones son útiles para cálculos que se necesitan reutilizar en diferentes consultas.

Creación de una Función

Vamos a crear una función que calcule 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;

Conclusión

Los procedimientos almacenados y las funciones en MySQL son herramientas poderosas para gestionar y manipular datos de manera eficiente. Los procedimientos almacenados permiten realizar tareas repetitivas y complejas, mientras que las funciones permiten encapsular cálculos y lógica reutilizable.

Estos elementos son esenciales para cualquier base de datos bien estructurada y pueden mejorar significativamente el rendimiento y la mantenibilidad de las aplicaciones que interactúan con la base de datos.