Procedimientos Almacenados en MySQL: Ejercicios Prácticos de Gestión de Datos

Ejercicios de Procedimientos Almacenados en MySQL

1. Listado y Conteo Total de Empleados

Crea un procedimiento almacenado en MySQL llamado total_empleados que liste todos los empleados existentes y que, mediante un parámetro de salida, devuelva el total de empleados. Además, durante la creación del procedimiento, debe añadirse un comentario indicando cuál es el parámetro que devuelve este procedimiento.

USE talleresfaber;

DELIMITER //

CREATE PROCEDURE total_empleados(
    OUT total INT
)
COMMENT 'Procedimiento que devuelve el total de empleados en el parámetro de salida total'
BEGIN
    -- Listar todos los empleados
    SELECT 
        codigo_empleado AS ID,
        CONCAT(nombre, ' ', apellido1, ' ', IFNULL(apellido2, '')) AS Nombre_Completo,
        puesto AS Cargo,
        email
    FROM empleado
    ORDER BY apellido1, apellido2, nombre;

    -- Devolver el total de empleados
    SELECT COUNT(*) INTO total FROM empleado;
END //

DELIMITER ;

-- Llamada al procedimiento
CALL total_empleados(@total);
SELECT @total AS Total_Empleados;

2. Historial Detallado de Vehículos

Crea un procedimiento almacenado en MySQL llamado historial_vehiculo que reciba la matrícula de un vehículo y realice las siguientes tres funciones:

• 1) Escriba las características de ese vehículo (marca, modelo y color). Por ejemplo, para la matrícula ‘1313 DEF’.
• 2) Devuelva, como parámetro de salida, el número de reparaciones que ha sufrido ese automóvil.
• 3) Además, debe mostrar los datos (matrícula, modelo y color) de todos los vehículos de la misma marca.

USE talleresfaber;

DELIMITER //

CREATE PROCEDURE historial_vehiculo(
    IN p_matricula VARCHAR(10),
    OUT num_reparaciones INT
)
BEGIN
    -- 1) Características del vehículo
    SELECT marca, modelo, color 
    FROM vehiculo 
    WHERE matricula = p_matricula;

    -- 2) Conteo de reparaciones
    SELECT COUNT(*) INTO num_reparaciones 
    FROM reparacion 
    WHERE matricula = p_matricula;

    -- 3) Otros vehículos de la misma marca
    SELECT matricula, modelo, color 
    FROM vehiculo 
    WHERE marca = (SELECT marca FROM vehiculo WHERE matricula = p_matricula)
    AND matricula <> p_matricula;
END //

DELIMITER ;

-- Llamada al procedimiento
CALL historial_vehiculo('1313 DEF', @num);
SELECT @num AS Numero_Reparaciones;

3. Listado de Reparaciones por Año

Crea un procedimiento llamado total_reparaciones que cumpla con los siguientes requisitos:

• Obtenga un listado con todas las reparaciones realizadas en un determinado año, el cual se le indica como parámetro de entrada.
• Debe devolver, en un parámetro de salida, el total de reparaciones realizadas en ese año.

Además, crea la llamada para ejecutar el procedimiento anterior (por ejemplo, recibiendo como parámetro de entrada el año 2011) y para mostrar el valor que ha sido devuelto como parámetro de salida. Nota: Puedes necesitar la función YEAR().

USE talleresfaber;

DELIMITER //

CREATE PROCEDURE total_reparaciones(
    IN p_anio INT,
    OUT p_total INT
)
BEGIN
    -- Listado de reparaciones del año indicado
    SELECT 
        codigo_reparacion,
        matricula,
        fecha_entrada,
        reparada
    FROM REPARACIONES
    WHERE YEAR(fecha_entrada) = p_anio;

    -- Cálculo del total de reparaciones
    SELECT COUNT(*) INTO p_total 
    FROM REPARACIONES 
    WHERE YEAR(fecha_entrada) = p_anio;
END //

DELIMITER ;

-- Llamada al procedimiento para el año 2011
CALL total_reparaciones(2011, @total);
SELECT @total AS Total_Reparaciones_2011;

4. Cálculo de Importe de Recambios

Crea un procedimiento llamado ImporteRecambios que reciba como parámetro de entrada el ID de una reparación y devuelva, como parámetro de salida, el importe de los recambios sustituidos en dicha reparación. Ten en cuenta lo siguiente:

• En una determinada reparación se pueden usar varias unidades del mismo recambio.
• En una reparación se pueden usar diferentes tipos de recambios.
• El PrecioReferencia es el precio de una sola unidad de un determinado recambio.

El importe para cada recambio se calculará teniendo en cuenta las unidades usadas y su precio de referencia. Para el importe total de la reparación completa, debe considerarse que se pueden haber usado varios tipos de recambios y, por tanto, tendríamos varios importes sumados.

USE talleresfaber;

DELIMITER //

CREATE PROCEDURE ImporteRecambios(
    IN p_codigo_reparacion INT,
    OUT p_importe_total DECIMAL(12,2)
)
COMMENT 'Devuelve el importe total de los recambios utilizados en una reparación específica'
BEGIN
    -- Cálculo del importe total sumando cantidad por precio de referencia
    SELECT ROUND(SUM(i.cantidad * r.PrecioReferencia), 2) INTO p_importe_total
    FROM Incluyen i
    JOIN RECAMBIOS r ON i.codigo_recambio = r.codigo_recambio
    WHERE i.codigo_reparacion = p_codigo_reparacion;

    -- Control de valores nulos
    IF p_importe_total IS NULL THEN
        SET p_importe_total = 0.00;
    END IF;
END //

DELIMITER ;

-- Llamada al procedimiento para la reparación con ID 1
CALL ImporteRecambios(1, @importe);
SELECT @importe AS Importe_Total_Recambios;