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.