Ejercicios Resueltos de Procedimientos Almacenados y Cursores en MySQL

Ejercicios Prácticos de Procedimientos Almacenados en MySQL

A continuación, se presentan diferentes ejercicios resueltos que implementan procedimientos almacenados (stored procedures), cursores y estructuras de control en bases de datos relacionales.

Bloque 1: Gestión Hospitalaria

1. Procedimiento CODHOSP

Función/procedimiento que devuelve el código de un hospital a partir de su nombre (1,5 puntos).

CREATE PROCEDURE CODHOSP(IN NOMH VARCHAR(20))
BEGIN
    DECLARE COD INT(2) DEFAULT -1;
    SELECT COALESCE(H_COD, COD) INTO COD FROM HOSPITAL WHERE NOMBRE = NOMH;
    IF COD = -1 THEN
        SET @SALIDA = 'EL HOSPITAL NO EXISTE';
    ELSE
        SET @SALIDA = COD;
    END IF;
END;

-- Ejemplo de llamada:
CALL CODHOSP('UNO');
SELECT @SALIDA;

2. Procedimiento INSEMP

Permite insertar un empleado tomando su apellido, función, hospital y sala. Se le adjudica el número de empleado consecutivo al último registrado, el turno con menor cantidad de personal en ese hospital y sala, y el salario mínimo asignado a dicho turno (3 puntos).

CREATE PROCEDURE INSEMP (
    IN NOMBRE VARCHAR(20),
    IN APELL VARCHAR(20),
    IN FUNC VARCHAR(12),
    IN HOSP INT(2),
    IN SALA INT(2)
)
BEGIN
    DECLARE COD INT(4);
    DECLARE TUR CHAR(2);
    DECLARE SAL INT(10);

    SELECT MAX(EMP_NO) + 1 INTO COD FROM PLANTILLA;
    SELECT TURNO INTO TUR FROM PLANTILLA GROUP BY TURNO ORDER BY COUNT(EMP_NO) ASC LIMIT 1;
    SELECT MIN(SALARIO) INTO SAL FROM PLANTILLA WHERE TURNO = TUR AND H_COD = HOSP AND S_COD = SALA;
    
    INSERT INTO PLANTILLA VALUES (COD, HOSP, SALA, NOMBRE, APELL, FUNC, TUR, SAL);
END;

3. Procedimiento GANAMAS

Visualiza por pantalla los cinco empleados con mayor salario de cada hospital utilizando cursores (2,5 puntos).

CREATE PROCEDURE GANAMAS()
BEGIN
    DECLARE CODHOSP INT(2);
    DECLARE vFIN INT DEFAULT 0;
    DECLARE listaCUR CURSOR FOR SELECT H_COD FROM HOSPITAL;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET vFIN = 1;

    OPEN listaCUR;
    procesarCUR: LOOP
        FETCH listaCUR INTO CODHOSP;
        IF vFIN = 1 THEN
            LEAVE procesarCUR;
        END IF;
        SELECT EMP_NO, APELLIDO FROM PLANTILLA WHERE H_COD = CODHOSP ORDER BY SALARIO DESC LIMIT 5;
    END LOOP procesarCUR;
    CLOSE listaCUR;
END;

Bloque 2: Sistema de Reservas Hoteleras

1. Procedimiento DESPORCAT

Modifica el descuento de una determinada categoría de clientes. Recibe el porcentaje del nuevo descuento y el código de categoría (por ejemplo: VIP, Familiar, etc.).

CREATE PROCEDURE DESPORCAT (
    IN PROCENT INT,
    IN CAT CHAR(3)
)
BEGIN
    UPDATE RESERVAS 
    SET DESCUENTO = PROCENT 
    WHERE NCLIENTE IN (
        SELECT NCLIENTE FROM CLIENTES WHERE CATEGORIA = CAT
    );
END;

2. Procedimiento DATOSRESERVA

Recibe un número de reserva y devuelve la información consolidada del cliente asociado.

CREATE PROCEDURE DATOSRESERVA(IN NRES INT)
BEGIN
    DECLARE NUM INT(10);
    DECLARE VNIF CHAR(9);
    DECLARE NOM VARCHAR(60);
    DECLARE TEL CHAR(9);
    DECLARE CAT CHAR(3);

    SELECT NCLIENTE, NIF, NOMBRE, TFNO, CATEGORIA 
    INTO NUM, VNIF, NOM, TEL, CAT 
    FROM CLIENTES 
    WHERE NCLIENTE IN (
        SELECT NCLIENTE FROM RESERVAS WHERE NRESERVA = NRES
    );

    SET @SALIDA = '<> DATOS RESERVA ';
    SET @SALIDA = CONCAT(@SALIDA, NUM);
    SET @SALIDA = CONCAT(@SALIDA, '; NIF: ', VNIF);
    SET @SALIDA = CONCAT(@SALIDA, '; NOMBRE: ', NOM);
    SET @SALIDA = CONCAT(@SALIDA, '; TFNO: ', TEL);
    SET @SALIDA = CONCAT(@SALIDA, '; CATEGORIA: ', CAT);
END;

3. Procedimiento RESPORCLI

Recibe un número de cliente y dos fechas límite, devolviendo la cantidad de reservas realizadas por dicho cliente en ese intervalo temporal.

CREATE PROCEDURE RESPORCLI(
    IN NCLI INT(10),
    IN FI DATETIME,
    IN FF DATETIME
)
BEGIN
    DECLARE NUM INT;
    SELECT COUNT(NRESERVA) INTO NUM 
    FROM RESERVAS 
    WHERE NCLIENTE = NCLI AND FECHAI >= FI AND FECHAI <= FF;
END;

4. Procedimiento OCUPAHAB

Recibe un número de habitación y genera una cadena de texto de salida con los DNI, nombres y categorías de todos los clientes que la han ocupado.

CREATE PROCEDURE OCUPAHAB (
    IN NH INT(10),
    OUT LISTA VARCHAR(10000)
)
BEGIN
    DECLARE vDNI CHAR(9);
    DECLARE vNOM VARCHAR(60);
    DECLARE vCAT VARCHAR(60);
    DECLARE vTXT VARCHAR(100) DEFAULT '';
    DECLARE vFIN INT DEFAULT 0;
    
    DECLARE listaCUR CURSOR FOR 
        SELECT C.DNI, C.NOMBRE, C.CATEGORIA 
        FROM CLIENTES C, RESERVAS R, RES_HAB RH 
        WHERE RH.NHAB = NH AND C.NCLIENTE = R.NCLIENTE AND R.NRESERVA = RH.NRESERVA;
        
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET vFIN = 1;

    SET LISTA = 'CLIENTES: ';
    OPEN listaCUR;
    procesarCUR: LOOP
        FETCH listaCUR INTO vDNI, vNOM, vCAT;
        IF vFIN = 1 THEN
            LEAVE procesarCUR;
        END IF;
        SET vTXT = CONCAT('DNI: ', vDNI, '; Nombre: ', vNOM, '; Categoría: ', vCAT, ' | ');
        SET LISTA = CONCAT(LISTA, vTXT);
    END LOOP procesarCUR;
    CLOSE listaCUR;
END;