Implementación de Procedimientos y Funciones en PL/SQL para Gestión de Datos

Enviado por Programa Chuletas y clasificado en Informática y Telecomunicaciones

Escrito el 11 de Enero de 2026 en español con un tamaño de 26,48 KB

Ejercicios Prácticos de Programación PL/SQL

1. Cálculo de Diferencia de Tiempo del Sistema

Realizar un programa que obtenga la fecha del sistema, en segundos, dos veces y calcule la diferencia entre las dos horas. El procedimiento debe visualizar la diferencia en segundos del tiempo transcurrido entre la 1ª y segunda hora.

CREATE OR REPLACE PROCEDURE calcular_tiempo_transcurrido (v_fecha DATE)
IS
    f_inicio NUMBER(6);
    f_fin NUMBER(6);
    tiempo_transcurrido NUMBER(6);
BEGIN
    -- Obtener el tiempo en segundos de la hora del sistema (inicio)
    SELECT TO_CHAR(SYSDATE, 'SSSSS') INTO f_inicio FROM DUAL;
    
    -- Obtener el tiempo en segundos de la fecha pasada como parámetro (fin)
    SELECT TO_CHAR(v_fecha, 'SSSSS') INTO f_fin FROM DUAL;
    
    tiempo_transcurrido := f_fin - f_inicio;
    
    DBMS_OUTPUT.PUT_LINE('El tiempo transcurrido es: ' || tiempo_transcurrido || ' segundos');
END;

2. Alta de Nuevo Departamento

Crea un procedimiento que permita dar de alta un nuevo departamento en la tabla DEPART.

CREATE OR REPLACE PROCEDURE insertar_departamento ( 
    dept_n IN DEPART.dept_no%TYPE, 
    dept_nom IN DEPART.dnombre%TYPE, 
    localidad IN DEPART.loc%TYPE 
)
IS
BEGIN
    INSERT INTO DEPART (dept_no, dnombre, loc) 
    VALUES (dept_n, dept_nom, localidad);
    COMMIT;
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        DBMS_OUTPUT.PUT_LINE('Error: El número de departamento ya existe.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error al insertar departamento: ' || SQLERRM);
END;

Ejecutar el procedimiento:

EXECUTE insertar_departamento(50, 'CONTABILIDAD', 'OVIEDO');

3. Cálculo de Cociente con Manejo de División por Cero

Realizar un procedimiento que acepte dos números y realice el cociente del primero entre el segundo. Si el divisor es cero debe aparecer el siguiente mensaje de error: “No se puede dividir por 0”.

CREATE OR REPLACE PROCEDURE calcular_cociente (num_a NUMBER, num_b NUMBER)
IS
    resultado NUMBER;
BEGIN
    resultado := num_a / num_b;
    DBMS_OUTPUT.PUT_LINE('Resultado: ' || resultado);
EXCEPTION
    WHEN ZERO_DIVIDE THEN 
        DBMS_OUTPUT.PUT_LINE('No se puede dividir por 0');
END;

4. Función para Obtener Precio de Producto

Crea una función que nos dé el precio de cualquier producto de la tabla productos. Si no lo encuentra nos devolverá un 0.

CREATE OR REPLACE FUNCTION obtener_precio (v_id_producto PRODUCTOS.id_producto%TYPE)
RETURN NUMBER
IS
    valor NUMBER;
BEGIN
    SELECT precio INTO valor FROM productos WHERE id_producto = v_id_producto;
    RETURN valor;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN 0;
END;

Ejemplo de uso:

BEGIN
    DBMS_OUTPUT.PUT_LINE('Precio: ' || obtener_precio(1));
END;

5. Obtener Dirección y Fecha de Alta por Apellido de Empleado

Realizar un procedimiento que a partir del nombre de un empleado nos devuelva su dirección y fecha de alta. Incluir excepciones para los siguientes casos:

El nombre introducido no existe.
Hay más de un empleado con ese nombre.

CREATE OR REPLACE PROCEDURE obtener_datos_empleado (nombre IN EMPLE.apellido%TYPE)
IS
    v_dir EMPLE.dir%TYPE;
    v_fecha DATE;
BEGIN
    SELECT dir, fecha_alt INTO v_dir, v_fecha 
    FROM EMPLE 
    WHERE apellido = nombre;
    
    DBMS_OUTPUT.PUT_LINE('Dirección: ' || v_dir || ', Fecha de alta: ' || TO_CHAR(v_fecha, 'DD/MM/YYYY'));
EXCEPTION
    WHEN NO_DATA_FOUND THEN 
        DBMS_OUTPUT.PUT_LINE('Error: El nombre introducido no existe.');
    WHEN TOO_MANY_ROWS THEN 
        DBMS_OUTPUT.PUT_LINE('Error: Hay más de un empleado con ese nombre.');
END;

6. Función para Calcular Años entre Dos Fechas

Diseñar una función que nos devuelva el número de años que hay entre dos fechas que se pasan como argumento.

CREATE OR REPLACE FUNCTION calcular_años_diferencia (v_fecha1 DATE, v_fecha2 DATE)
RETURN NUMBER
IS
    v_diferencia NUMBER(6);
BEGIN
    -- Calcula la diferencia en meses y la convierte a años, truncando decimales
    v_diferencia := ABS(TRUNC(MONTHS_BETWEEN(v_fecha2, v_fecha1) / 12));
    RETURN v_diferencia;
END;

7. Función para Calcular Trienios

Escribir una función, que haciendo uso de la función anterior, devuelva los trienios que hay entre dos fechas.

CREATE OR REPLACE FUNCTION calcular_trienios (v_fecha1 DATE, v_fecha2 DATE)
RETURN NUMBER
AS
    v_trienio NUMBER(6);
BEGIN
    -- Un trienio son 3 años
    v_trienio := TRUNC(calcular_años_diferencia(v_fecha1, v_fecha2) / 3);
    RETURN v_trienio;
END;

8. Incrementar Salario por Número de Empleado

Realizar un procedimiento que incremente en una cantidad dada, el salario de un empleado conocido su emp_no.

CREATE OR REPLACE PROCEDURE incrementar_salario (v_num_emple EMPLE.emp_no%TYPE, v_cantidad NUMBER)
IS
    v_comprobar NUMBER(10);
BEGIN
    -- Verificar si el empleado existe antes de intentar la actualización
    SELECT salario INTO v_comprobar FROM EMPLE WHERE emp_no = v_num_emple;
    
    UPDATE EMPLE 
    SET salario = salario + v_cantidad 
    WHERE emp_no = v_num_emple;
    
    COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN 
        DBMS_OUTPUT.PUT_LINE('No se encuentra el empleado con número: ' || v_num_emple);
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error al actualizar salario: ' || SQLERRM);
        ROLLBACK;
END;

9. Borrar Empleado por Número

Escribir un procedimiento que permita borrar un empleado cuyo número se pasará en la llamada.

CREATE OR REPLACE PROCEDURE borrar_empleado (v_num_emple EMPLE.emp_no%TYPE)
IS
    v_comprobar NUMBER(10);
BEGIN
    -- Verificar existencia
    SELECT emp_no INTO v_comprobar FROM EMPLE WHERE emp_no = v_num_emple;
    
    DELETE FROM EMPLE WHERE emp_no = v_num_emple;
    COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN 
        DBMS_OUTPUT.PUT_LINE('No se encuentra el empleado con número: ' || v_num_emple);
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error al borrar empleado: ' || SQLERRM);
        ROLLBACK;
END;

10. Clasificación de Saldo Numérico

Escribir un procedimiento que acepte un valor numérico, correspondiente al saldo de un usuario, y nos diga:

“Saldo mayor de lo esperado” si es > 90.
“Saldo menor de lo esperado” si está entre 1 y 90 (inclusive).
“Saldo negativo” si es < 0.

CREATE OR REPLACE PROCEDURE clasificar_saldo (numero NUMBER)
IS
BEGIN
    IF numero > 90 THEN 
        DBMS_OUTPUT.PUT_LINE('Saldo mayor de lo esperado');
    ELSIF numero BETWEEN 1 AND 90 THEN 
        DBMS_OUTPUT.PUT_LINE('Saldo menor de lo esperado');
    ELSIF numero < 0 THEN 
        DBMS_OUTPUT.PUT_LINE('Saldo negativo');
    ELSE -- Caso número = 0
        DBMS_OUTPUT.PUT_LINE('Saldo es cero');
    END IF;
END;

11. Actualización de Salario Basada en Antigüedad

Actualizar el salario de un empleado conocido su ID_EMP. El incremento del salario será según la fecha de alta:

Más de 15 años: incremento 1,02.
Entre 10-15 años: incremento 1,025.
Menos de 10 años: incremento 1,03.

NOTA: Se asume la existencia de la función calcular_años_diferencia (ejercicio 6).

CREATE OR REPLACE PROCEDURE aplicar_incremento_salario (v_emp_no EMPLE.emp_no%TYPE)
AS
    annos NUMBER(2);
    f_alta DATE;
    incremento NUMBER(4,3);
BEGIN
    SELECT fecha_alta INTO f_alta FROM EMPLE WHERE emp_no = v_emp_no;
    
    -- Usamos la función del ejercicio 6
    annos := calcular_años_diferencia(SYSDATE, f_alta);
    
    IF annos > 15 THEN
        incremento := 1.02;
    ELSIF annos >= 10 AND annos <= 15 THEN -- Clarificando el rango 10-15
        incremento := 1.025;
    ELSE -- Menos de 10 años
        incremento := 1.03;
    END IF;
    
    UPDATE EMPLE 
    SET salario = salario * incremento 
    WHERE emp_no = v_emp_no;
    
    COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Empleado ' || v_emp_no || ' no encontrado.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error en la actualización de salario: ' || SQLERRM);
        ROLLBACK;
END;

12. Modificación de Salario según Personal a Cargo

Programa que modifique el salario de un empleado especificado, en función del número de empleados que tiene a su cargo (DIR).

Si no tiene empleados a su cargo: subida 50€.
Si tiene un empleado: subida 80€.
Si tiene dos empleados: subida 100€.
Si tiene tres o más empleados: subida 110€.
Además, si el empleado es 'PRESIDENTE', se incrementará el salario en 30€ adicionales.

CREATE OR REPLACE PROCEDURE aplicar_aumento_por_subordinados (v_emp_no EMPLE.emp_no%TYPE)
AS
    v_cantidad_subordinados NUMBER;
    v_aumento NUMBER := 0;
    v_oficio EMPLE.oficio%TYPE;
BEGIN
    -- 1. Determinar aumento base por oficio
    SELECT oficio INTO v_oficio FROM EMPLE WHERE emp_no = v_emp_no;
    
    IF v_oficio = 'PRESIDENTE' THEN
        v_aumento := 30;
    END IF;
    
    -- 2. Contar subordinados (donde DIR apunta al empleado)
    SELECT COUNT(*) INTO v_cantidad_subordinados 
    FROM EMPLE 
    WHERE dir = v_emp_no;
    
    -- 3. Determinar aumento por cantidad de subordinados
    IF v_cantidad_subordinados = 0 THEN 
        v_aumento := v_aumento + 50;
    ELSIF v_cantidad_subordinados = 1 THEN 
        v_aumento := v_aumento + 80;
    ELSIF v_cantidad_subordinados = 2 THEN 
        v_aumento := v_aumento + 100;
    ELSE -- 3 o más
        v_aumento := v_aumento + 110;
    END IF;
    
    -- 4. Actualizar salario
    UPDATE EMPLE 
    SET salario = salario + v_aumento 
    WHERE emp_no = v_emp_no;
    
    DBMS_OUTPUT.PUT_LINE('El valor del aumento total ha sido: ' || v_aumento || '€');
    COMMIT;
END;

13. Función Lógica para Evaluación de Alumnos

Escribir una función que nos devuelva un valor lógico (BOOLEAN) según la actitud y la nota de los alumnos:

Si la actitud es “A” y la nota >= 4, TRUE.
Si la actitud es “B o C” y la nota >= 5, TRUE.
Si la nota >= 7, TRUE.
En cualquier otro caso, FALSE.

CREATE OR REPLACE FUNCTION evaluar_alumno (v_nota NUMBER, v_actitud VARCHAR2)
RETURN BOOLEAN
AS
BEGIN
    RETURN CASE
        WHEN v_actitud = 'A' AND v_nota >= 4 THEN TRUE
        WHEN v_actitud IN ('B', 'C') AND v_nota >= 5 THEN TRUE
        WHEN v_nota >= 7 THEN TRUE
        ELSE FALSE
    END;
END;

14. Bloque para Escribir Números del 1 al 10 (Bucle LOOP)

Realizar un bloque que escriba los números del uno al 10.

DECLARE
    contador NUMBER := 1;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE(contador);
        contador := contador + 1;
        EXIT WHEN contador > 10;
    END LOOP;
END;

15. Procedimiento para Escribir Números Siguientes

Escribir un procedimiento que escriba los 15 números siguientes a uno dado.

CREATE OR REPLACE PROCEDURE escribir_siguientes (v_inicio NUMBER)
AS
    contador NUMBER := v_inicio;
BEGIN
    LOOP
        contador := contador + 1;
        DBMS_OUTPUT.PUT_LINE(contador);
        EXIT WHEN contador = v_inicio + 15;
    END LOOP;
END;

16. Procedimiento para Mostrar Números hasta un Límite

Crear un procedimiento PL/SQL que muestra los números desde el 1 hasta un valor pasado como parámetro.

CREATE OR REPLACE PROCEDURE mostrar_hasta_limite (v_limite NUMBER)
AS
    contador NUMBER := 0;
BEGIN
    LOOP
        contador := contador + 1;
        DBMS_OUTPUT.PUT_LINE(contador);
        EXIT WHEN contador = v_limite;
    END LOOP;
END;

17. Procedimiento para Mostrar Números con Salto

Realizar un procedimiento para que muestre números desde un valor inferior, hasta uno superior con cierto salto, valores que pasamos al procedimiento como parámetros.

CREATE OR REPLACE PROCEDURE mostrar_con_saltos (v_inicial NUMBER, v_final NUMBER, v_salto NUMBER DEFAULT 1)
AS
    contador NUMBER := v_inicial - v_salto;
BEGIN
    LOOP
        contador := contador + v_salto;
        DBMS_OUTPUT.PUT_LINE(contador);
        EXIT WHEN contador >= v_final;
    END LOOP;
END;

18. Números del 1 al 10 Usando WHILE

Escribir los números del 1 al 10 utilizando la sentencia WHILE.

DECLARE
    contador NUMBER := 1;
BEGIN
    WHILE contador <= 10 LOOP
        DBMS_OUTPUT.PUT_LINE(contador);
        contador := contador + 1;
    END LOOP;
END;

19. Extracción del Primer Apellido (While y Loop)

Supongamos que queremos analizar una cadena que contiene dos apellidos. Queremos obtener el primer apellido en una variable v_1ape. Suponemos que el primer apellido termina cuando encontramos cualquier carácter distinto de los alfabéticos (“A”…”Z”), y que el apellido está en mayúsculas.

19.a) Usando la sentencia WHILE

CREATE OR REPLACE PROCEDURE extraer_primer_apellido_while (v_cadena_apellidos VARCHAR2)
AS
    v_1ape VARCHAR2(25) := '';
    v_posicion NUMBER := 1;
    v_caracter CHAR(1);
BEGIN 
    v_caracter := SUBSTR(v_cadena_apellidos, v_posicion, 1);
    
    WHILE v_caracter BETWEEN 'A' AND 'Z' LOOP
        v_1ape := v_1ape || v_caracter;
        v_posicion := v_posicion + 1;
        v_caracter := SUBSTR(v_cadena_apellidos, v_posicion, 1);
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('1er APELLIDO (While): ' || v_1ape);
END;

19.b) Usando un bucle LOOP...END LOOP

CREATE OR REPLACE PROCEDURE extraer_primer_apellido_loop (v_cadena_apellidos VARCHAR2)
AS
    v_1ape VARCHAR2(25) := '';
    v_posicion NUMBER := 1;
    v_caracter CHAR(1);
BEGIN 
    LOOP
        v_caracter := SUBSTR(v_cadena_apellidos, v_posicion, 1);
        
        IF v_caracter BETWEEN 'A' AND 'Z' THEN
            v_1ape := v_1ape || v_caracter;
            v_posicion := v_posicion + 1;
        ELSE
            EXIT; -- Sale si no es letra mayúscula
        END IF;
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('1er APELLIDO (Loop): ' || v_1ape);
END;

20. Número de Veces Divisible por Dos

Programa (un bloque) que lea un número por teclado y diga el número de veces que es divisible por dos.

DECLARE
    v_count BINARY_INTEGER := 0;
    v_num NUMBER := &introduce_numero; -- Entrada de usuario
    c_par CONSTANT NUMBER := 2;
BEGIN
    WHILE MOD(v_num, c_par) = 0 AND v_num != 0 LOOP
        v_count := v_count + 1;
        v_num := v_num / c_par;
    END LOOP;
    
    IF v_num = 0 THEN
        DBMS_OUTPUT.PUT_LINE('El número introducido es 0, divisible infinitas veces.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Es divisible ' || v_count || ' veces por dos.');
    END IF;
END;

21. Cuenta Atrás y Cuenta Adelante

Programa que pida un número del 1 al 10 por teclado y escriba la cuenta atrás desde dicho número.

21.a) Cuenta Atrás

DECLARE
    v_num NUMBER := &numero;
BEGIN
    IF v_num BETWEEN 1 AND 10 THEN
        FOR v_cont IN REVERSE 1..v_num LOOP
            -- Muestra desde el número introducido hasta el 1
            DBMS_OUTPUT.PUT_LINE(v_cont);
        END LOOP;
        DBMS_OUTPUT.PUT_LINE('¡YA ESTÁ!');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Número fuera de rango (1-10).');
    END IF;
END;

21.b) Cuenta Adelante (del 1 hasta el número introducido)

DECLARE
    v_num NUMBER := &numero;
BEGIN
    IF v_num BETWEEN 1 AND 10 THEN
        FOR v_cont IN 1..v_num LOOP
            -- Muestra desde 1 hasta el número introducido
            DBMS_OUTPUT.PUT_LINE(v_cont);
        END LOOP;
        DBMS_OUTPUT.PUT_LINE('¡YA ESTÁ!');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Número fuera de rango (1-10).');
    END IF;
END;

22. Creación de Tabla Piloto Renault

Crea, mediante una sentencia SQL, una tabla llamada piloto_renault que nos permita guardar los siguientes datos:

ID_piloto (clave primaria) NUMBER
Nombre VARCHAR2
País VARCHAR2
Edad NUMBER

CREATE TABLE piloto_renault
(
    ID_piloto NUMBER PRIMARY KEY,
    nombre VARCHAR2(30),
    pais VARCHAR2(15),
    edad NUMBER(3)
);

23. Procedimiento para Insertar Pilotos Renault

Realiza un procedimiento que inserte, en la tabla piloto_renault, los datos de los pilotos de la escudería RENAULT (asumiendo tablas PILOTO y EQUIPOF1 existentes).

CREATE OR REPLACE PROCEDURE cargar_pilotos_renault
AS
    V_id_piloto PILOTO.id_piloto%TYPE;
    V_nombre PILOTO.nombre%TYPE;
    V_pais PILOTO.pais%TYPE;
    V_edad PILOTO.edad%TYPE;
    V_equipo_id EQUIPOF1.id_equipo%TYPE;
BEGIN
    -- Obtener el ID del equipo RENAULT
    SELECT id_equipo INTO V_equipo_id 
    FROM EQUIPOF1
    WHERE nombre LIKE 'RENAULT';
    
    -- Cursor implícito para recorrer los pilotos del equipo
    FOR registro IN (SELECT id_piloto, nombre, pais, edad 
                     FROM PILOTO 
                     WHERE id_equipo = V_equipo_id)
    LOOP
        BEGIN
            -- Insertar en la tabla destino
            INSERT INTO piloto_renault (ID_piloto, nombre, pais, edad)
            VALUES (registro.id_piloto, registro.nombre, registro.pais, registro.edad);
        EXCEPTION
            WHEN DUP_VAL_ON_INDEX THEN
                DBMS_OUTPUT.PUT_LINE('Piloto ' || registro.id_piloto || ' ya existe en piloto_renault.');
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('Error al insertar piloto ' || registro.id_piloto || ': ' || SQLERRM);
        END;
    END LOOP;
    
    COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Equipo RENAULT no encontrado.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error general en el procedimiento: ' || SQLERRM);
        ROLLBACK;
END;

24. Invertir Cadena 'HOLA'

Escribir un bloque PL/SQL que escriba la cadena 'HOLA' al revés. Utilizar la sentencia FOR y luego una alternativa con WHILE.

24.a) Usando FOR (Reverse)

DECLARE
    r_cadena VARCHAR2(10) := '';
    cadena_original VARCHAR2(4) := 'HOLA';
BEGIN
    FOR i IN REVERSE 1..LENGTH(cadena_original) LOOP
        r_cadena := r_cadena || SUBSTR(cadena_original, i, 1);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Cadena invertida (FOR): ' || r_cadena);
END;

24.b) Usando WHILE

DECLARE
    r_cadena VARCHAR2(10) := '';
    i NUMBER;
    cadena_original VARCHAR2(4) := 'HOLA';
BEGIN
    i := LENGTH(cadena_original);
    WHILE i >= 1 LOOP
        r_cadena := r_cadena || SUBSTR(cadena_original, i, 1);
        i := i - 1;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Cadena invertida (WHILE): ' || r_cadena);
END;

25. Visualizar Apellido y Fecha de Alta (Cursor)

Desarrollar un procedimiento que visualice el apellido y la fecha de alta de todos los empleados ordenados por apellido.

CREATE OR REPLACE PROCEDURE ver_empleados_ordenados
AS
    -- Declaración del cursor explícito
    CURSOR c_emple IS
        SELECT apellido, fecha_alt 
        FROM EMPLE 
        ORDER BY apellido;
        
    V_apellido EMPLE.apellido%TYPE;
    V_fecha DATE;
BEGIN
    OPEN c_emple;
    
    LOOP
        -- Obtener el siguiente registro
        FETCH c_emple INTO v_apellido, v_fecha;
        
        -- Salir si no hay más registros
        EXIT WHEN c_emple%NOTFOUND;
        
        -- Mostrar datos
        DBMS_OUTPUT.PUT_LINE(v_apellido || ' * ' || TO_CHAR(v_fecha, 'DD/MM/YYYY'));
    END LOOP;
    
    CLOSE c_emple;
END;

26. Top 5 Empleados por Salario

Escribir un procedimiento que visualice el apellido y el salario de los cinco empleados que tienen el salario más alto.

CREATE OR REPLACE PROCEDURE mostrar_top_5_salarios
AS
    -- Cursor que ordena por salario descendente
    CURSOR c_emp IS
        SELECT apellido, salario 
        FROM EMPLE
        ORDER BY salario DESC;
        
    Vr_emp c_emp%ROWTYPE;
    i NUMBER := 1;
BEGIN
    OPEN c_emp;
    
    LOOP
        FETCH c_emp INTO vr_emp;
        
        -- Condición de salida: si no hay más registros O si ya mostramos 5
        EXIT WHEN c_emp%NOTFOUND OR i > 5;
        
        DBMS_OUTPUT.PUT_LINE(RPAD(vr_emp.apellido, 15) || ' * ' || vr_emp.salario);
        
        i := i + 1;
    END LOOP;
    
    CLOSE c_emp;
END;

27. Número de Empleados por Departamento

Realiza un procedimiento que obtenga el nombre de departamento junto con el número de empleados del mismo, formateando la salida.

CREATE OR REPLACE PROCEDURE contar_empleados_por_departamento
AS
    -- Cursor que une EMPLE y DEPART y agrupa
    CURSOR cursor_departamento IS
        SELECT d.dnombre, COUNT(e.emp_no) AS num_empleados
        FROM DEPART d
        LEFT JOIN EMPLE e ON e.dept_no = d.dept_no
        GROUP BY d.dnombre;
        
    v_nombre DEPART.dnombre%TYPE;
    v_num_empleados NUMBER;
BEGIN
    DBMS_OUTPUT.PUT_LINE(RPAD('NOMBRE DE DEPARTAMENTO', 30) || ' NÚMERO DE EMPLEADOS');
    DBMS_OUTPUT.PUT_LINE(RPAD('------------------------------', 30) || ' ---------------');
    
    OPEN cursor_departamento;
    LOOP
        FETCH cursor_departamento INTO v_nombre, v_num_empleados;
        EXIT WHEN cursor_departamento%NOTFOUND;
        
        -- Formateo de la salida
        DBMS_OUTPUT.PUT_LINE(RPAD(v_nombre, 30) || RPAD(v_num_empleados, 15));
    END LOOP;
    CLOSE cursor_departamento;
END;

28. Aumento Salarial Bajo la Media

Aumentar un 10% el salario de aquellos empleados cuyo salario esté por debajo de la media. Recalcular la media y visualizarla si es mayor de 5000€. Manejar excepciones.

CREATE OR REPLACE PROCEDURE actualizar_salario_bajo_media
IS
    salario_med NUMBER;
BEGIN
    -- 1. Calcular media inicial
    SELECT AVG(salario) INTO salario_med FROM EMPLE;
    
    -- 2. Actualizar salarios por debajo de la media inicial
    UPDATE EMPLE 
    SET salario = salario * 1.1
    WHERE salario < salario_med;
    
    -- 3. Recalcular la media de salarios
    SELECT AVG(salario) INTO salario_med FROM EMPLE;

    -- 4. Visualizar si es mayor de 5000€
    IF salario_med > 5000 THEN     
        DBMS_OUTPUT.PUT_LINE('La actualización se completó. El nuevo salario medio es: ' || TO_CHAR(salario_med, 'FM99,999.00'));
    ELSE
        DBMS_OUTPUT.PUT_LINE('La actualización se completó. El nuevo salario medio no supera los 5000€.');
    END IF;
    
    COMMIT; -- Validar cambios
    
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error en la actualización: ' || SQLERRM);
        ROLLBACK; -- Deshacer cualquier cambio
END;

29. Inserción de Nuevos Departamentos con Número Secuencial

Desarrollar un procedimiento que permita insertar nuevos departamentos. Se pasará el nombre y la localidad. El procedimiento insertará la fila nueva asignando como número de departamento la decena siguiente al número mayor de la tabla.

CREATE OR REPLACE PROCEDURE insertar_departamento_secuencial (v_dnombre DEPART.dnombre%TYPE, v_loc DEPART.loc%TYPE)
AS
    v_dept_no_max NUMBER;
    v_nuevo_dept_no NUMBER;
BEGIN
    -- Captura del último número y cálculo del siguiente múltiplo de 10
    SELECT MAX(dept_no) INTO v_dept_no_max FROM DEPART;
    
    -- Calculamos la decena siguiente al máximo (ej: si max es 40, el siguiente es 50. Si es 41, el siguiente es 50)
    v_nuevo_dept_no := CEIL((v_dept_no_max + 1) / 10) * 10;
    
    -- Inserción de la nueva fila 
    INSERT INTO DEPART (dept_no, dnombre, loc) 
    VALUES (v_nuevo_dept_no, v_dnombre, v_loc);
    
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Departamento insertado con número: ' || v_nuevo_dept_no);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error en la inserción: ' || SQLERRM);
        ROLLBACK;
END;

30. Subida de Salario por Departamento y Porcentaje

Codificar un procedimiento que reciba como parámetros un número de departamento y un porcentaje; y suba el salario a todos los empleados del departamento indicado en la llamada.

CREATE OR REPLACE PROCEDURE subir_salario_por_departamento (v_num_dep NUMBER, v_por NUMBER)
AS
BEGIN
    UPDATE EMPLE 
    SET salario = salario + salario * (v_por / 100) -- Equivalente a salario * (1 + v_por/100)
    WHERE dept_no = v_num_dep;
    
    COMMIT;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' empleados actualizados en el departamento ' || v_num_dep);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error en el update: ' || SQLERRM);
        ROLLBACK;
END;

31. Empleados con Salario Mayor que su Director

Realizar un procedimiento que nos muestre los códigos de empleados (emp_no) de aquellos donde su salario es mayor que el de su director.

CREATE OR REPLACE PROCEDURE mostrar_salario_mayor_que_director
AS
    -- Cursor para recorrer todos los empleados
    CURSOR c_emple IS 
        SELECT emp_no, salario, dir 
        FROM EMPLE
        WHERE dir IS NOT NULL; -- Solo consideramos empleados que tienen director
        
    v_registro_emple c_emple%ROWTYPE;
    v_salario_director EMPLE.salario%TYPE;

BEGIN
    OPEN c_emple;
    LOOP
        FETCH c_emple INTO v_registro_emple;
        EXIT WHEN c_emple%NOTFOUND;
        
        -- Obtener el salario del director (cuyo emp_no es el campo dir del empleado)
        BEGIN
            SELECT salario 
            INTO v_salario_director 
            FROM EMPLE 
            WHERE emp_no = v_registro_emple.dir;
            
            -- Comparar salarios
            IF v_registro_emple.salario > v_salario_director THEN
                DBMS_OUTPUT.PUT_LINE('Empleado con mayor salario que su director: ' || v_registro_emple.emp_no);
            END IF;
            
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                -- Esto ocurre si el 'dir' no existe como 'emp_no' en la tabla EMPLE
                DBMS_OUTPUT.PUT_LINE('Advertencia: No existe jefe (EMP_NO: ' || v_registro_emple.dir || ') para el empleado ' || v_registro_emple.emp_no);
            -- No es necesario manejar TOO_MANY_ROWS ya que emp_no es clave primaria.
        END;
        
    END LOOP;
    CLOSE c_emple;
END;

Entradas relacionadas:

Etiquetas: