Implementación de Procedimientos PL/SQL y Control de Transacciones en Bases de Datos

Documentación y Desarrollo de Lógica de Base de Datos

A continuación, se detalla la estructura de datos y los requerimientos para el desarrollo de un procedimiento PL/SQL y un trigger asociado, enfocados en la gestión de inversiones y el control transaccional.

Estructura de Datos Inicial

Se definen las siguientes entidades:

• Cliente (CL): Contiene DNI, NombreC, Dirección.
• Invierte (I): Contiene DNI, NombreE, Cantidad, Tipo.
• Compras_XXXXX (CO): Tabla de compras específica para cada cliente (XXXXX es el DNI del cliente). Contiene NumT, NumFac, Fecha, Tienda, Importe.

a) Procedimiento PL/SQL: EJ3

Se requiere implementar un procedimiento en PL/SQL llamado EJ3 que utilice un cursor y cumpla con las siguientes condiciones:

Requisitos del Procedimiento

1. Filtrado Inicial: Procesar únicamente a los clientes cuya suma total de inversiones supere los 50.000 €.
2. Operación Principal (Actualización): Para cada cliente filtrado, incrementar la Cantidad de cada una de sus inversiones en 100 €.
3. Control de Integridad con Savepoints:
   • Si, tras la actualización (Operación 1), la suma total de las inversiones del cliente excede 1.000.000 €, se debe deshacer la actualización anterior utilizando un SAVEPOINT.
   • Si la suma total es inferior a 100.000 €, se debe registrar una nueva compra en su tabla correspondiente (Compras_XXXXX) con los siguientes valores:
     • NumT = 'particular'
     • NumFac = 0
     • Fecha = Fecha del sistema (SYSDATE)
     • Importe = El 10% de la suma obtenida (en valor negativo)
     • Tienda = 'bonus'
4. Aislamiento de Transacción: Utilizar el nivel de aislamiento adecuado para asegurar que las actualizaciones no sean visibles para otras transacciones hasta que finalice la transacción principal.
5. Manejo de Errores: Implementar un bloque EXCEPTION para capturar cualquier error inesperado y mostrar un mensaje de aviso.

Código del Procedimiento EJ3

CREATE OR REPLACE PROCEDURE EJ3 AS
    TDNI Invierte.dni%TYPE;
    Tsuma Number;
    plsql_block VARCHAR2(2000);
    -- Variables para manejo de excepción
    Tcoderror NUMBER;
    Ttexterror VARCHAR2(100);

    -- Cursor para seleccionar clientes con inversión total > 50000
    Cursor CBonus is
        SELECT dni
        FROM invierte
        GROUP BY dni
        HAVING SUM(Cantidad) > 50000;

BEGIN
    -- Se establece el nivel de aislamiento más estricto para evitar lecturas no confirmadas
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    -- Nota: El comentario '/* read commited también*/' se mantiene, aunque SERIALIZABLE es el requerido.

    OPEN CBonus;
    LOOP
        FETCH CBonus INTO TDNI;
        EXIT WHEN CBonus%NOTFOUND;

        -- 1. Establecer punto de guardado para posible rollback
        SAVEPOINT deshace;

        -- 2. Operación 1: Sumar 100 a cada inversión
        UPDATE invierte
        SET cantidad = cantidad + 100
        WHERE dni = TDNI;

        -- Obtener la nueva suma total
        SELECT SUM(cantidad) INTO Tsuma FROM invierte WHERE DNI = TDNI;

        -- 3. Control de límites
        IF Tsuma > 1000000 THEN
            -- Deshacer la actualización si supera el límite superior
            ROLLBACK TO SAVEPOINT deshace;
        ELSE -- Implica que Tsuma <= 1000000
            IF Tsuma < 100000 THEN
                -- Crear nueva compra si está por debajo del límite inferior
                plsql_block := 'BEGIN
                    INSERT INTO compras_' || TDNI || ' (NumT, NumFac, Fecha, Tienda, Importe) 
                    VALUES (''particular'', 0, SYSDATE, ''bonus'', (:suma * -0.1));
                END;';
                
                -- Ejecución dinámica, pasando Tsuma como parámetro para evitar inyección de código en el valor numérico
                EXECUTE IMMEDIATE plsql_block USING Tsuma;
            END IF;
        END IF;
    END LOOP;

    CLOSE CBonus;
    
EXCEPTION
    WHEN OTHERS THEN
        Tcoderror := SQLCODE;
        Ttexterror := SUBSTR(SQLERRM, 1, 100);
        DBMS_OUTPUT.PUT_LINE('Sale por una excepción: ' || Tcoderror || ' -- ' || Ttexterror );
END EJ3;

b) Trigger de Auditoría (log_invierte)

Se requiere un trigger que registre cualquier modificación (INSERT, UPDATE, DELETE) en la tabla invierte en una tabla de log llamada log_invierte. Es crucial que este registro se mantenga incluso si la transacción principal (EJ3) realiza un ROLLBACK sobre las actualizaciones.

Características del Trigger

• Activación: AFTER INSERT OR DELETE OR UPDATE sobre invierte.
• Granularidad: FOR EACH ROW.
• Autonomía: Debe ser una PRAGMA AUTONOMOUS_TRANSACTION para asegurar que su COMMIT no se vea afectado por el ROLLBACK de la transacción principal.
• Datos Registrados: Debe incluir el tipo de operación ('insert', 'update', 'delete'), la fecha, y todos los datos de la fila afectada (usando :new y :old).

Código del Trigger trigger_EJ3

CREATE OR REPLACE TRIGGER trigger_EJ3
AFTER INSERT OR DELETE OR UPDATE ON invierte
FOR EACH ROW
PRAGMA AUTONOMOUS_TRANSACTION
BEGIN
    IF DELETING THEN
        INSERT into log_invierte VALUES ('DELETE', SYSDATE, :old.DNI, :old.NombreE, :old.Cantidad, :old.Tipo, 'DELETE');
    ELSIF INSERTING THEN
        INSERT into log_invierte VALUES ('INSERT', SYSDATE, :new.DNI, :new.NombreE, :new.Cantidad, :new.Tipo, 'INSERT');
    ELSE -- UPDATE
        -- Se registra la operación como 'UPDATE' independientemente de si la fila se revierte después
        INSERT into log_invierte VALUES ('UPDATE', SYSDATE, :new.DNI, :new.NombreE, :new.Cantidad, :new.Tipo, 'UPDATE');
    END IF;
    COMMIT; -- Confirma la transacción autónoma del trigger
END;

c) Recomendación del Nivel de Aislamiento SERIALIZABLE

El nivel de aislamiento SERIALIZABLE garantiza la máxima consistencia, simulando que las transacciones se ejecutan secuencialmente. Se recomienda su uso en situaciones específicas donde la integridad de los datos es crítica y se pueden tolerar problemas de rendimiento o bloqueos.

Características de Situaciones Típicas para SERIALIZABLE

Se recomienda SERIALIZABLE cuando:

Criterios de Aplicación

• Entorno de BD y Transacciones: Se aplica en bases de datos grandes donde las transacciones (T's) son cortas y modifican un número reducido de filas.
• Probabilidad de Colisión Baja: Existe una baja probabilidad de que dos transacciones intenten modificar la misma fila simultáneamente.
• Carga de Lectura Pesada: La mayoría de las transacciones de larga duración son predominantemente de lectura (SELECT).

Beneficios de Consistencia

SERIALIZABLE protege contra anomalías de consistencia más allá de las cubiertas por niveles inferiores (como READ COMMITTED):

• Lecturas Fantasma e Irrepetibles: Garantiza que si una transacción lee un rango de filas dos veces, el conjunto de filas devuelto será idéntico en ambas lecturas.
• Consultas con Subconsultas: Asegura la consistencia total si una transacción incluye consultas complejas con subconsultas que dependen de los datos leídos previamente dentro de la misma transacción.

Consideraciones de Rendimiento

NO es recomendable para entornos con alta concurrencia donde se produzcan muchos conflictos de escritura, ya que esto incrementa significativamente la probabilidad de deadlocks, forzando múltiples ROLLBACK y reintentos de las transacciones afectadas.