Implementación de Paquetes PL/SQL para Gestión Salarial y Auditoría con Triggers en Oracle

Requerimientos del Ejercicio

El objetivo de este ejercicio es desarrollar componentes de base de datos en PL/SQL para gestionar aumentos salariales y auditar los cambios realizados en la tabla employees.

1. Paquete PL/SQL: Crear un paquete llamado aumento_sueldo que contenga:
   • Función subida_empleado: Recibe el ID del empleado y el porcentaje de subida. Actualiza el salario del empleado específico. Retorna un booleano indicando el éxito de la operación.
   • Procedimiento subida_sueldo: Recibe un salario base y un porcentaje de subida. Este procedimiento realizará la subida del porcentaje a todos los sueldos que superen el salario pasado como parámetro.
2. Disparador (Trigger) de Auditoría: Se creará un disparador que audite las modificaciones realizadas en la columna salary, registrando en una tabla de auditoría los siguientes datos: usuario, salario antiguo, salario nuevo, fecha, nombre del empleado.

Implementación en PL/SQL

1. Desarrollo del Paquete aumento_sueldo

1.1. Especificación del Paquete

Se define la interfaz pública del paquete, declarando la función y el procedimiento requeridos.

CREATE OR REPLACE PACKAGE aumento_sueldo IS
    FUNCTION subida_empleado (porcentaje NUMBER, id_empleado employees.employee_id%TYPE) RETURN BOOLEAN;
    PROCEDURE subida_sueldo (porcentaje NUMBER, salario employees.salary%TYPE);
END aumento_sueldo;

1.2. Cuerpo del Paquete (PACKAGE BODY)

Se implementa la lógica de negocio para la actualización salarial.

Función subida_empleado

CREATE OR REPLACE PACKAGE BODY aumento_sueldo IS
    FUNCTION subida_empleado (porcentaje NUMBER, id_empleado employees.employee_id%TYPE) RETURN BOOLEAN IS
        salida BOOLEAN := TRUE;
    BEGIN
        UPDATE employees
        SET salary = salary * (1 + porcentaje / 100)
        WHERE employee_id = id_empleado;

        IF SQL%NOTFOUND THEN
            salida := FALSE;
        END IF;

        RETURN salida;
    END subida_empleado;

Procedimiento subida_sueldo

Dado que la subida afecta a múltiples empleados, se utiliza un cursor explícito para iterar sobre aquellos que cumplen la condición salarial.

    PROCEDURE subida_sueldo (porcentaje NUMBER, salario employees.salary%TYPE) IS
        -- Declaración del cursor para seleccionar empleados con salario superior o igual al umbral
        CURSOR c_almacen IS
            SELECT employee_id, salary, first_name
            FROM employees
            WHERE salary >= salario;

        -- Variable para almacenar los valores de cada fila del cursor
        vr_almacen c_almacen%ROWTYPE;
        salida BOOLEAN := FALSE;
    BEGIN
        OPEN c_almacen;
        FETCH c_almacen INTO vr_almacen;

        -- Recorrido del cursor mediante %FOUND
        WHILE (c_almacen%FOUND) LOOP
            -- Llamada a la función para actualizar el salario
            salida := subida_empleado(porcentaje, vr_almacen.employee_id);

            -- Si la actualización fue exitosa, se muestra un mensaje
            IF (salida) THEN
                dbms_output.put_line('Modificado: ' || vr_almacen.first_name || ' (ID: ' || vr_almacen.employee_id || ')');
            END IF;

            -- Recuperación de la siguiente fila
            FETCH c_almacen INTO vr_almacen;
        END LOOP;

        CLOSE c_almacen;
    END subida_sueldo;
END aumento_sueldo;
-- Fin del cuerpo del paquete

2. Implementación de la Auditoría (Tabla y Trigger)

2.1. Creación de la Tabla de Auditoría

Se crea la tabla auditar para registrar los cambios en la columna salary.

CREATE TABLE auditar (
    usuario VARCHAR2(30),
    my_date DATE,
    old_sal NUMBER(8,2),
    new_salary NUMBER(8,2),
    nombre VARCHAR2(30)
);

2.2. Creación del Disparador (Trigger)

El disparador se activa después de cualquier actualización en la columna salary, registrando los valores antiguos y nuevos por cada fila modificada.

CREATE OR REPLACE TRIGGER mitrigger
AFTER UPDATE OF salary ON employees
FOR EACH ROW -- El trigger se ejecuta por cada registro que se modifique
BEGIN
    INSERT INTO auditar (usuario, my_date, old_sal, new_salary, nombre)
    VALUES (USER, SYSDATE, :OLD.salary, :NEW.salary, :OLD.first_name);
END;

Pruebas y Verificación

1. Prueba de Ejecución del Paquete

Se habilita la salida de servidor y se ejecuta el procedimiento para aplicar un aumento del 10% a todos los empleados con salario igual o superior a 10000.

SET SERVEROUTPUT ON
EXECUTE aumento_sueldo.subida_sueldo(10, 10000);

2. Verificación de la Auditoría

2.1. Comprobación inicial de la tabla de auditoría

Verificamos el estado inicial de la tabla auditar.

SELECT * FROM AUDITAR;

2.2. Ejecución del Paquete (Segunda Ejecución)

Se ejecuta nuevamente el procedimiento para generar las modificaciones y activar el trigger, registrando los cambios en la tabla de auditoría.

SET SERVEROUTPUT ON
EXECUTE aumento_sueldo.subida_sueldo(10, 10000);

2.3. Verificación final de la tabla de auditoría

Se comprueba que la tabla auditar se ha llenado con los registros correspondientes a las modificaciones salariales.

SELECT * FROM AUDITAR;