Gestión Avanzada de Bases de Datos MySQL: Procedimientos, Funciones y DML

Introducción a la Gestión de Bases de Datos

Este documento detalla una serie de ejercicios prácticos para la gestión de bases de datos, incluyendo la creación de tablas, la manipulación de datos (DML), y el desarrollo de procedimientos y funciones almacenadas en MySQL. Cada sección aborda un aspecto clave de la administración y programación de bases de datos, optimizando las consultas y la lógica de negocio.

Creación de Base de Datos y Tabla Inicial

Crea la base de datos "animales_mascotas" con la siguiente tabla y muestra su estructura.

CREATE DATABASE animales_mascotas;
USE animales_mascotas;

CREATE TABLE mascotas (
    nombre VARCHAR(20),
    duenio VARCHAR(20),
    especie VARCHAR(20),
    sexo CHAR(1),
    fechaNac DATE,
    PRIMARY KEY (nombre, duenio) -- Clave primaria compuesta por nombre de la mascota y el nombre del dueño
);

DESCRIBE mascotas;

Operaciones DML Fundamentales con Mascotas

Inserción de Múltiples Registros

Inserta tres mascotas con todos sus datos con una única sentencia INSERT:

INSERT INTO mascotas (nombre, duenio, especie, sexo, fechaNac)
VALUES
    ('Bobi', 'Pepe', 'perro', 'm', '2013-03-03'),
    ('Coco', 'Juan', 'gato', 'm', '2013-11-08'),
    ('Blaky', 'Juan', 'perro', 'm', '2014-01-09');

Modificación de Datos (UPDATE)

Modifica el sexo de la mascota COCO cambiándolo a hembra.

UPDATE mascotas
SET sexo = 'f'
WHERE nombre = 'Coco' AND duenio = 'Juan';

Consulta de Datos Específicos

Muestra el contenido de las mascotas cuyo dueño es Juan.

SELECT * FROM mascotas WHERE duenio = 'Juan';

Actualización de Fecha de Nacimiento

Modifica la fecha de nacimiento de la mascota COCO cambiándola a 2022-01-02:

UPDATE mascotas
SET fechaNac = '2022-01-02'
WHERE nombre = 'Coco' AND duenio = 'Juan';

Consulta de Mascotas por Sexo

Muestra los datos de todas las mascotas hembras.

SELECT * FROM mascotas WHERE sexo = 'f';

Inserción Adicional de Mascotas Hembras

Inserta dos mascotas hembras con todos sus datos:

INSERT INTO mascotas (nombre, duenio, especie, sexo, fechaNac)
VALUES
    ('Tuti', 'Lola', 'perro', 'f', '2014-01-09'),
    ('Kena', 'Lola', 'gato', 'f', '2012-04-29');

Visualización Completa de la Tabla

Muestra el contenido de la tabla mascotas.

SELECT * FROM mascotas;

Inserción de una Mascota Adicional

Inserta otra mascota con todos sus datos.

INSERT INTO mascotas (nombre, duenio, especie, sexo, fechaNac)
VALUES ('Piolin', 'Lola', 'canario', 'm', '2014-01-09');

Creación de Tabla de Respaldo

Crea una tabla con el nombre "backupmascotas" y mediante una sentencia SELECT inserta todos los datos de la tabla mascotas.

CREATE TABLE backupmascotas AS
SELECT * FROM mascotas;

Eliminación de Registros Específicos

Elimina de la tabla mascotas la mascota que sea un canario.

DELETE FROM mascotas WHERE especie = 'canario';

Eliminación Condicional de Registros

Elimina de la tabla mascotas todos los perros machos.

DELETE FROM mascotas WHERE especie = 'perro' AND sexo = 'm';

Eliminación Múltiple en Tabla de Respaldo

Elimina de la tabla backupmascotas los datos de todos los perros machos o los gatos hembras:

DELETE FROM backupmascotas
WHERE
    (especie = 'perro' AND sexo = 'm')
    OR (especie = 'gato' AND sexo = 'f');

SELECT * FROM backupmascotas;

Re-inserción y Consulta de Mascota Específica

Muestra los datos de la mascota de nombre BLAKY.

-- Nota: Esta inserción se realiza aquí para asegurar que 'Blaky' exista para las siguientes operaciones,
-- ya que pudo haber sido eliminada por sentencias DELETE anteriores.
INSERT INTO mascotas (nombre, duenio, especie, sexo, fechaNac)
VALUES ('Blaky', 'Juan', 'perro', 'm', '2014-01-09');

SELECT * FROM mascotas WHERE nombre = 'Blaky';

Actualización de Múltiples Campos

Modifica el dueño de Blaky para que sea Teodoro y su fecha de nacimiento sea el mismo día pero en 2016.

UPDATE mascotas
SET duenio = 'Teodoro', fechaNac = '2016-01-09'
WHERE nombre = 'Blaky';

SELECT * FROM mascotas WHERE nombre = 'Blaky';

Consulta de Dueños Únicos

Muestra los dueños de las mascotas.

SELECT DISTINCT duenio FROM mascotas;

Renombrar Dueño

Cambia el nombre del dueño, de Lola a Dolores.

UPDATE mascotas
SET duenio = 'Dolores'
WHERE duenio = 'Lola';

Eliminación de Gatas Hembras

Elimina todas las gatas.

DELETE FROM mascotas WHERE especie = 'gato' AND sexo = 'f';

Desarrollo de Procedimientos Almacenados

Procedimiento para Obtener el Año Actual

Crea un procedimiento que muestre el año actual.

DROP PROCEDURE IF EXISTS SacarFecha;
DELIMITER //
CREATE PROCEDURE SacarFecha()
BEGIN
    DECLARE anio INT;
    SELECT YEAR(NOW()) INTO anio;
    SELECT anio;
END //
DELIMITER ;

CALL SacarFecha();

Procedimiento de Saludo Personalizado

Escribe un procedimiento al que le pases como parámetro de entrada tu nombre, y después el programa te salude, diciendo "Hola tu nombre". Es decir, si te llamas Daniela, tiene que contestar: Hola Daniela. Debes utilizar la función CONCAT para que el mensaje aparezca completo y seguido.

USE test;

DROP PROCEDURE IF EXISTS Saludar;
DELIMITER //
CREATE PROCEDURE Saludar(IN p_nombre VARCHAR(255))
BEGIN
    SELECT CONCAT('Hola ', p_nombre) AS saludo;
END //
DELIMITER ;

CALL Saludar('Ines');

Procedimiento para Contar Empleados por Departamento con Mensaje Condicional

Crea un procedimiento que cuente el número de empleados de un departamento que se le pasa como parámetro de entrada y que almacene en una variable de salida una frase. La frase mostrada variará en función del número de empleados de ese departamento y también mostrará el nombre del departamento correspondiente al código que se pasa como parámetro de entrada. Si el departamento tiene menos de 5 empleados la frase será: "El departamento 'nombreX' tiene pocos empleados". Si el departamento tiene entre 5 y 8 empleados: "El departamento 'nombreX' tiene un número de empleados adecuado". Si el departamento tiene más de 8 empleados: "El departamento 'nombreX' tiene muchos empleados".

USE test_buena;

DROP PROCEDURE IF EXISTS ContarEmpleadosDepartamento;
DELIMITER $$
CREATE PROCEDURE ContarEmpleadosDepartamento(
    IN p_codigo_departamento INT,
    OUT p_mensaje VARCHAR(255)
)
BEGIN
    DECLARE v_num_empleados INT;
    DECLARE v_nombre_departamento VARCHAR(255);

    -- Obtener el nombre del departamento
    SELECT Nombre INTO v_nombre_departamento FROM departamentos WHERE Codigo = p_codigo_departamento;

    -- Contar empleados en el departamento
    SELECT COUNT(DNI) INTO v_num_empleados FROM empleados WHERE Departamento = p_codigo_departamento;

    IF v_num_empleados < 5 THEN
        SET p_mensaje = CONCAT('El departamento \'', v_nombre_departamento, '\' tiene pocos empleados.');
    ELSEIF (v_num_empleados >= 5 AND v_num_empleados <= 8) THEN
        SET p_mensaje = CONCAT('El departamento \'', v_nombre_departamento, '\' tiene un número de empleados adecuado.');
    ELSEIF v_num_empleados > 8 THEN
        SET p_mensaje = CONCAT('El departamento \'', v_nombre_departamento, '\' tiene muchos empleados.');
    END IF;
END $$
DELIMITER ;

CALL ContarEmpleadosDepartamento(77, @mensaje_departamento);
SELECT @mensaje_departamento;

Procedimiento para Buscar Departamento por Presupuesto

Crea un procedimiento en la base de datos "empleadosdepartamentos", que deberá buscar un departamento que tenga un presupuesto igual al indicado como parámetro de entrada y mostrar el nombre del departamento como parámetro de salida. (Utiliza LIMIT 1 por si hay más de un departamento). En caso de que no se encuentre ninguno, deberá mostrar 'NO EXISTE' (utiliza IF-ELSE).

USE test_buena;

DROP PROCEDURE IF EXISTS BuscarDepartamentoPorPresupuesto;
DELIMITER $$
CREATE PROCEDURE BuscarDepartamentoPorPresupuesto(
    IN p_presupuesto DECIMAL(10,0),
    OUT p_nombre_departamento VARCHAR(255)
)
BEGIN
    SELECT Nombre INTO p_nombre_departamento
    FROM departamentos
    WHERE Presupuesto = p_presupuesto
    LIMIT 1;

    IF p_nombre_departamento IS NULL THEN
        SET p_nombre_departamento = 'NO EXISTE';
    END IF;
END $$
DELIMITER ;

CALL BuscarDepartamentoPorPresupuesto(15001, @nombre_departamento_encontrado);
SELECT @nombre_departamento_encontrado;

Procedimiento para Generar Presupuestos Dinámicamente

Crea en la base de datos empleados_departamentos la tabla presupuestos con dos campos: id_presupuesto (VARCHAR(4)), valor (INT). Luego, crea un procedimiento que inserte un número determinado de presupuestos en esta tabla, donde el ID sea 'p' seguido de un número y el valor sea el número multiplicado por 1000.

-- Asumiendo que 'empleados_departamentos' es la base de datos activa o se usará 'test_buena'
USE test_buena;

CREATE TABLE IF NOT EXISTS presupuestos (
    id_presupuesto VARCHAR(4) PRIMARY KEY,
    valor INT
);

TRUNCATE TABLE presupuestos;

DROP PROCEDURE IF EXISTS GenerarPresupuestos;
DELIMITER $$
CREATE PROCEDURE GenerarPresupuestos(IN p_tope INT)
BEGIN
    DECLARE contador INT DEFAULT 1;
    WHILE contador <= p_tope DO
        INSERT INTO presupuestos (id_presupuesto, valor) VALUES (CONCAT('p', contador), contador * 1000);
        SET contador = contador + 1;
    END WHILE;
END $$
DELIMITER ;

CALL GenerarPresupuestos(10);
SELECT * FROM presupuestos;

Procedimiento para Mostrar Primeras Letras en Mayúsculas

Crea un procedimiento que muestre las 5 primeras letras de una cadena en mayúsculas pasada como parámetro de entrada. Antes de devolver nada, el procedimiento debe comprobar la longitud de la cadena y si es menor de 5 mostrará un mensaje por pantalla: "La cadena es demasiado corta". Si es mayor que 5 devuelve las cinco primeras letras de la cadena en mayúsculas. Usa la función LEFT(cadena, longitud) y UPPER(cadena).

USE test_buena;

DROP PROCEDURE IF EXISTS MostrarPrimerasLetrasMayusculas;
DELIMITER $$
CREATE PROCEDURE MostrarPrimerasLetrasMayusculas(IN p_cadena VARCHAR(255))
BEGIN
    IF LENGTH(p_cadena) < 5 THEN
        SELECT 'La cadena es demasiado corta' AS mensaje;
    ELSE
        SELECT UPPER(LEFT(p_cadena, 5)) AS resultado;
    END IF;
END $$
DELIMITER ;

CALL MostrarPrimerasLetrasMayusculas('lalslslldl');
CALL MostrarPrimerasLetrasMayusculas('hola');

Procedimiento para Obtener Departamento con Mayor Presupuesto

Crea un procedimiento que te muestre los datos del departamento de mayor presupuesto, es decir el código, nombre y presupuesto.

DROP PROCEDURE IF EXISTS ObtenerDepartamentoMayorPresupuesto;
DELIMITER //
CREATE PROCEDURE ObtenerDepartamentoMayorPresupuesto()
BEGIN
    DECLARE v_codigo INT;
    DECLARE v_nombre VARCHAR(255);
    DECLARE v_presupuesto DECIMAL(15,2);

    SELECT Codigo, Nombre, Presupuesto
    INTO v_codigo, v_nombre, v_presupuesto
    FROM departamentos
    ORDER BY Presupuesto DESC
    LIMIT 1;

    SELECT v_codigo AS Codigo, v_nombre AS Nombre, v_presupuesto AS Presupuesto;
END //
DELIMITER ;

CALL ObtenerDepartamentoMayorPresupuesto();

Procedimiento para Contar Departamentos (Corregido)

Crea un procedimiento que te cuente el número de departamentos que existen en la base de datos. Modifica el procedimiento anterior y añádele un comentario que describa lo que devuelve el procedimiento.

DROP PROCEDURE IF EXISTS ContarDepartamentos;
DELIMITER //
CREATE PROCEDURE ContarDepartamentos()
BEGIN
    -- Este procedimiento devuelve el número total de departamentos en la tabla 'departamentos'.
    DECLARE v_num_departamentos INT;
    SELECT COUNT(*) INTO v_num_departamentos FROM departamentos;
    SELECT v_num_departamentos AS TotalDepartamentos;
END //
DELIMITER ;

-- Añadir comentario al procedimiento (si el motor de DB lo soporta directamente o para documentación)
ALTER PROCEDURE ContarDepartamentos COMMENT 'Devuelve el número de departamentos existentes.';

CALL ContarDepartamentos();

Procedimiento para Contar Empleados del Departamento 77

Crea un procedimiento que muestre el número de empleados del departamento 77.

DROP PROCEDURE IF EXISTS ContarEmpleadosDepartamento77;
DELIMITER //
CREATE PROCEDURE ContarEmpleadosDepartamento77()
BEGIN
    DECLARE v_num_empleados_77 INT;
    SELECT COUNT(DNI) INTO v_num_empleados_77 FROM empleados WHERE Departamento = 77;
    SELECT v_num_empleados_77 AS EmpleadosDepartamento77;
END //
DELIMITER ;

CALL ContarEmpleadosDepartamento77();

Procedimiento para Incrementar Presupuesto de Departamento

Crea un procedimiento de nombre "IncrementoPresupuesto" al que se le envíe como parámetros el identificador de un departamento (parámetro de entrada) y una cantidad que representa el dinero que se le va a aumentar al presupuesto del departamento (parámetro de entrada/salida).

DROP PROCEDURE IF EXISTS IncrementoPresupuesto;
DELIMITER //
CREATE PROCEDURE IncrementoPresupuesto(
    IN p_codigo_departamento INT,
    INOUT p_cantidad_incremento DECIMAL(15,2)
)
BEGIN
    -- Actualiza el presupuesto del departamento sumando la cantidad de incremento.
    UPDATE departamentos
    SET Presupuesto = Presupuesto + p_cantidad_incremento
    WHERE Codigo = p_codigo_departamento;

    -- Opcional: Si se desea que p_cantidad_incremento devuelva el nuevo presupuesto total del departamento.
    SELECT Presupuesto INTO p_cantidad_incremento
    FROM departamentos
    WHERE Codigo = p_codigo_departamento;
END //
DELIMITER ;

SET @departamento_id = 14;
SET @incremento_monto = 5000.00;

-- Antes de la llamada, @incremento_monto contiene el valor a incrementar.
-- Después de la llamada, @incremento_monto contendrá el nuevo presupuesto total del departamento 14.
CALL IncrementoPresupuesto(@departamento_id, @incremento_monto);
SELECT @departamento_id AS DepartamentoID, @incremento_monto AS NuevoPresupuestoTotal;

Procedimiento Modificado para Contar Empleados por Departamento (con OUT)

Modifica el procedimiento de "la primera tanda de ejercicios de procedimientos" que te devolvía el número de empleados de un determinado departamento concreto (el departamento 77), de forma que ahora le pases como parámetro de entrada el código del departamento y almacene el número de empleados de ese departamento en una variable de salida. Llámalo con otro nombre para que no pierdas el código del procedimiento anterior.

USE empleadosdepartamentos;

DROP PROCEDURE IF EXISTS ContarEmpleadosPorDepartamentoOut;
DELIMITER //
CREATE PROCEDURE ContarEmpleadosPorDepartamentoOut(
    IN p_codigo_departamento INT,
    OUT p_num_empleados INT
)
BEGIN
    SELECT COUNT(DNI) INTO p_num_empleados
    FROM empleados
    WHERE Departamento = p_codigo_departamento;
END //
DELIMITER ;

CALL ContarEmpleadosPorDepartamentoOut(14, @total_empleados_departamento);
SELECT @total_empleados_departamento AS EmpleadosEnDepartamento;

Procedimiento para Listar Usuarios con Permisos de Ejecución (Conceptual)

Crea un procedimiento de nombre "Procedimiento_getUsersExecute" que muestre los nombres de los usuarios (concatena los campos User y Host) que tienen permiso para ejecutar los procedimientos (de la base de datos empleados_departamentos) ordenados por nombre.

Nota del profesor: La determinación precisa de usuarios con permiso de ejecución sobre procedimientos específicos en una base de datos puede ser compleja y depende de la configuración de privilegios de MySQL (privilegios globales, de base de datos, de objeto, etc.). El siguiente ejemplo muestra cómo listar usuarios del sistema MySQL, concatenando su nombre y host, que es una interpretación común para este tipo de ejercicio. Para una verificación exhaustiva de permisos de ejecución, se requeriría un análisis más profundo de las tablas de privilegios de MySQL o el uso de SHOW GRANTS para cada usuario.

USE mysql;

DROP PROCEDURE IF EXISTS Procedimiento_getUsersExecute;
DELIMITER //
CREATE PROCEDURE Procedimiento_getUsersExecute()
BEGIN
    SELECT CONCAT(User, '@', Host) AS UsuarioConHost
    FROM user
    -- Se podría añadir una condición para filtrar por privilegios si se tuviera una tabla de permisos más granular
    -- Por ejemplo, WHERE Select_priv = 'Y' OR Execute_priv = 'Y' si existieran columnas directas para ello en 'user'
    -- Para procedimientos, los permisos suelen ser a nivel de base de datos o global.
    ORDER BY User;
END //
DELIMITER ;

CALL Procedimiento_getUsersExecute();

Procedimiento para Concatenar y Convertir a Mayúsculas

Crea un procedimiento al que le pases como parámetro de entrada dos cadenas, y te las muestre concatenadas y en mayúsculas.

USE test;

DROP PROCEDURE IF EXISTS ConcatenarCadenasMayusculas;
DELIMITER //
CREATE PROCEDURE ConcatenarCadenasMayusculas(
    IN p_cadena1 VARCHAR(100),
    IN p_cadena2 VARCHAR(100)
)
BEGIN
    DECLARE v_cadena_final VARCHAR(200);
    SET v_cadena_final = CONCAT(UPPER(p_cadena1), UPPER(p_cadena2));
    SELECT v_cadena_final AS CadenaConcatenadaMayusculas;
END //
DELIMITER ;

CALL ConcatenarCadenasMayusculas('primera', 'segunda');

Creación y Uso de Funciones Personalizadas

Función para Calcular la Hipotenusa

Crea una función para calcular la hipotenusa de un triángulo. Le pasamos como argumentos de entrada el valor de los dos catetos del triángulo. Debes de comprobar que los dos valores de los catetos sean mayor de cero (utiliza AND en la condición); en caso de que no se cumpla esta condición debe mostrar mensaje de error.

Nota del profesor: Las funciones en SQL están diseñadas para devolver un valor. Para "mostrar un mensaje de error" y luego devolver un valor, la práctica común es devolver NULL o un valor especial para indicar un error, o bien, usar un procedimiento si la intención es imprimir mensajes directamente. Aquí se opta por devolver NULL para catetos no positivos.

USE test;

DROP FUNCTION IF EXISTS CalcularHipotenusa;
DELIMITER $$
CREATE FUNCTION CalcularHipotenusa(
    p_cateto1 INT,
    p_cateto2 INT
) RETURNS DECIMAL(10,2)
BEGIN
    DECLARE v_hipotenusa DECIMAL(10,2);

    IF p_cateto1 <= 0 OR p_cateto2 <= 0 THEN
        -- Si los catetos no son positivos, se devuelve NULL para indicar un error o valor inválido.
        RETURN NULL;
    ELSE
        SET v_hipotenusa = ROUND(SQRT(POW(p_cateto1, 2) + POW(p_cateto2, 2)), 2);
        RETURN v_hipotenusa;
    END IF;
END $$
DELIMITER ;

SELECT CalcularHipotenusa(3, 5) AS HipotenusaCalculada;
SELECT CalcularHipotenusa(0, 5) AS HipotenusaConError;

Función para Obtener Equipo de Jugador

Crea una función "GetEquipoJugador" que muestre el nombre del equipo en que juega un jugador concreto (parámetro de la función). Probar la función con el jugador "Corey Brever".

DROP FUNCTION IF EXISTS GetEquipoJugador;
DELIMITER $$
CREATE FUNCTION GetEquipoJugador(p_nombre_jugador VARCHAR(30))
RETURNS VARCHAR(20)
BEGIN
    DECLARE v_nombre_equipo VARCHAR(20);
    SELECT nombre_equipo INTO v_nombre_equipo FROM jugadores WHERE nombre = p_nombre_jugador;
    RETURN v_nombre_equipo;
END $$
DELIMITER ;

SELECT GetEquipoJugador('Corey Brever') AS EquipoDelJugador;