BASES DE DATOS II

Stored Procedures Avanzados

Handlers · SIGNAL · SQLSTATE · Transacciones

Prof: Lic. Adrian Aroca

TUP Magdalena Martins · TUP Agustina Fuentes · TUP Romulo Currao

UTN Mar del Plata
TUP --- 2do Año | Plan 2024

Trabajo Práctico Integrador
Ejercicios conceptuales y prácticos sobre manejo de errores y transacciones

Trabajo Práctico: Stored Procedures Avanzados

Objetivo: Analizar el comportamiento de handlers en MySQL, predecir el flujo de ejecución (ejercicios conceptuales) y aplicar los conceptos en la escritura de procedimientos almacenados con manejo de errores, transacciones, SIGNAL y validaciones de negocio (ejercicios prácticos).

1. Máximas Fundamentales

El handler más interno que coincida es el que se ejecuta.
Un handler solo afecta al bloque donde está definido.
EXIT termina el bloque donde está declarado.
CONTINUE no corta la ejecución.
Error sin handler aborta todo el procedimiento.
SIGNAL lanza una excepción.
RESIGNAL relanza una excepción capturada.
Si un handler captura y no hace RESIGNAL, el error se consume.
Cada procedimiento maneja sus propios errores.

2. Guía para analizar ejercicios conceptuales

Identificar dónde ocurre el error.
Determinar si existe un handler en ese bloque.
Evaluar si es EXIT o CONTINUE.
Verificar si hay RESIGNAL.
Determinar qué bloque finaliza.
Identificar qué código continúa ejecutándose.

PARTE I: EJERCICIOS CONCEPTUALES (1 al 14)

Ejercicio 1

BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SELECT 'Handler general';
    END;

    BEGIN
        SIGNAL SQLSTATE '45000';
    END;

    SELECT 'Sigo en el procedimiento';
END;

¿Qué se imprime?

_________________________________________________________

¿Se corta la ejecución? ¿Dónde?

_________________________________________________________

Ejercicio 2

BEGIN
    BEGIN
        DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
            SELECT 'Handler interno';
        END;

        SIGNAL SQLSTATE '45000';

        SELECT 'Dentro del bloque';
    END;

    SELECT 'Fuera del bloque';
END;

¿Qué se imprime?

_________________________________________________________

¿Se corta la ejecución? ¿Dónde?

_________________________________________________________

Ejercicio 3

BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SELECT 'Handler externo';
    END;

    BEGIN
        DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
            SELECT 'Handler interno';
            RESIGNAL;
        END;

        SIGNAL SQLSTATE '45000';
    END;

    SELECT 'Fin del procedimiento';
END;

¿Qué se imprime?

_________________________________________________________

¿Se corta la ejecución? ¿Dónde?

_________________________________________________________

Ejercicio 4

BEGIN
    BEGIN
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
        BEGIN
            SELECT 'Handler CONTINUE';
        END;

        SIGNAL SQLSTATE '45000';

        SELECT 'Sigo dentro del bloque';
    END;

    SELECT 'Fin del procedimiento';
END;

¿Qué se imprime?

_________________________________________________________

¿Se corta la ejecución? ¿Dónde?

_________________________________________________________

Ejercicio 5

-- hijo
CREATE PROCEDURE hijo()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SELECT 'Error en hijo';
    END;

    SIGNAL SQLSTATE '45000';
END;

-- padre
CREATE PROCEDURE padre()
BEGIN
    CALL hijo();
    SELECT 'Padre sigue';
END;

¿Qué se imprime al llamar a padre()?

_________________________________________________________

¿Se corta la ejecución? ¿Dónde?

_________________________________________________________

Ejercicio 6

BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SELECT 'Handler externo';
    END;

    BEGIN
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
        BEGIN
            SELECT 'Handler interno CONTINUE';
        END;

        SIGNAL SQLSTATE '45000';

        SELECT 'Dentro del bloque';
    END;

    SELECT 'Fin del procedimiento';
END;

¿Qué se imprime?

_________________________________________________________

¿Se corta la ejecución? ¿Dónde?

_________________________________________________________

Ejercicio 7

BEGIN
    BEGIN
        DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
            SELECT 'Handler interno';
        END;

        SIGNAL SQLSTATE '45000';
    END;

    SIGNAL SQLSTATE '45000';

    SELECT 'Fin';
END;

¿Qué se imprime?

_________________________________________________________

¿Se corta la ejecución? ¿Dónde?

_________________________________________________________

Ejercicio 8

BEGIN
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        SELECT 'Handler externo CONTINUE';
    END;

    BEGIN
        DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
            SELECT 'Handler interno EXIT';
        END;

        SIGNAL SQLSTATE '45000';
    END;

    SELECT 'Después del bloque';

    SIGNAL SQLSTATE '45000';

    SELECT 'Fin del procedimiento';
END;

¿Qué se imprime?

_________________________________________________________

¿Se corta la ejecución? ¿Dónde?

_________________________________________________________

Ejercicio 9

-- hijo
CREATE PROCEDURE hijo()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SELECT 'Handler hijo';
        RESIGNAL;
    END;

    SIGNAL SQLSTATE '45000';
END;

-- padre
CREATE PROCEDURE padre()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SELECT 'Handler padre';
    END;

    CALL hijo();

    SELECT 'Padre sigue';
END;

¿Qué se imprime al llamar a padre()?

_________________________________________________________

¿Se corta la ejecución? ¿Dónde?

_________________________________________________________

Ejercicio 10 (SAVEPOINT y ROLLBACK parcial)

BEGIN
    START TRANSACTION;
    INSERT INTO logs (mensaje) VALUES ('Inicio de proceso');

    SAVEPOINT antes_operacion;

    INSERT INTO operaciones (detalle) VALUES ('Operación crítica');

    -- Supongamos que aquí se evalúa una condición de negocio (por ejemplo,
    -- validación de stock, saldo, etc.) y se asigna el valor de @error_detectado
    -- en función de si la operación es válida o no.
    IF @error_detectado = 1 THEN
        ROLLBACK TO SAVEPOINT antes_operacion;   -- Se deshace solo la inserción en operaciones
        SELECT 'Operación cancelada, log preservado' AS resultado;
    ELSE
        COMMIT;   -- Confirma todo (log y operación)
        SELECT 'Todo confirmado' AS resultado;
    END IF;
END;

Nota: La variable @error_detectado representa un flag de error de lógica de negocio que se supone cargada previamente (por ejemplo, después de verificar stock, saldo, etc.).

Si @error_detectado = 1, ¿qué operaciones se confirman al final?

_________________________________________________________

¿Qué comando permite deshacer solo una parte de la transacción?

_________________________________________________________

Ejercicio 11 (ROLLBACK fuera de un handler)

BEGIN
    START TRANSACTION;
    UPDATE cuentas SET saldo = saldo - 100 WHERE id = 1;
    UPDATE cuentas SET saldo = saldo + 100 WHERE id = 2;

    -- Validación de negocio sin handler
    IF (SELECT saldo FROM cuentas WHERE id = 1) < 0 THEN
        ROLLBACK;
        SELECT 'Transferencia cancelada por saldo negativo';
    ELSE
        COMMIT;
        SELECT 'Transferencia exitosa';
    END IF;
END;

¿El ROLLBACK está dentro de un handler?

_________________________________________________________

¿Qué sucede si la primera UPDATE falla por restricción de clave foránea?

_________________________________________________________

Ejercicio 12 (Múltiples transacciones en un mismo SP)

BEGIN
    -- Transacción 1
    START TRANSACTION;
    INSERT INTO auditoria (evento) VALUES ('Inicio proceso');
    COMMIT;

    -- Transacción 2
    START TRANSACTION;
    UPDATE datos SET valor = valor * 2;
    -- Si algo falla aquí, solo afecta a esta transacción
    IF (SELECT COUNT(*) FROM datos) = 0 THEN
        ROLLBACK;
    ELSE
        COMMIT;
    END IF;

    -- Transacción 3
    START TRANSACTION;
    INSERT INTO logs (mensaje) VALUES ('Proceso finalizado');
    COMMIT;
END;

Si la segunda transacción hace ROLLBACK, ¿afecta a la primera o a la tercera?

_________________________________________________________

¿Qué propiedad ACID garantiza que cada transacción sea independiente y no se mezclen?

_________________________________________________________

Ejercicio 13 (Handler NOT FOUND con SELECT...INTO)

BEGIN
    DECLARE v_id INT;
    DECLARE EXIT HANDLER FOR NOT FOUND
    BEGIN
        SELECT 'Socio no encontrado' AS aviso;
    END;

    SELECT id_socio INTO v_id FROM socios WHERE id_socio = 999;
    SELECT 'Socio existe' AS resultado;
END;

¿Qué se imprime si el socio con id 999 no existe?

_________________________________________________________

¿El SELECT final ('Socio existe') se ejecuta?

_________________________________________________________

Ejercicio 14 (SIGNAL dentro de una transacción y handler)

BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'Error: transacción revertida' AS resultado;
    END;

    START TRANSACTION;
    INSERT INTO tabla1 (col) VALUES ('valor1');
    -- @condicion es un flag que se supone cargado previamente según alguna
    -- condición de negocio (por ejemplo, validación de datos)
    IF @condicion = 1 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Error lógico';
    END IF;
    INSERT INTO tabla2 (col) VALUES ('valor2');
    COMMIT;
END;

Nota: La variable @condicion representa una validación de negocio (ej. stock insuficiente, saldo negativo, etc.) que puede activar el error.

Si @condicion = 1, ¿qué se imprime y qué ocurre con los datos?

_________________________________________________________

Si @condicion = 0, ¿qué se imprime?

_________________________________________________________

PARTE II: EJERCICIOS PRÁCTICOS (15 al 19)

En los siguientes ejercicios deberá escribir el código SQL del procedimiento almacenado según las especificaciones dadas. Utilice manejo de errores con handlers, transacciones, SIGNAL y validaciones según corresponda.

Ejercicio 15: eliminarPlanSeguro

Cree un procedimiento almacenado llamado eliminarPlanSeguro que reciba como parámetro p_id_plan INT e intente eliminar el plan de la tabla planes.

Debe manejar errores con SQLEXCEPTION.
Si el plan está relacionado con actividades y no puede borrarse, debe informar el error con un mensaje.
Si la eliminación es exitosa, mostrar un mensaje de éxito.

-- Escriba aquí el procedimiento solicitado:

DELIMITER $$
CREATE PROCEDURE eliminarPlanSeguro(IN p_id_plan INT)
BEGIN

END$$
DELIMITER ;

Prueba de concepto: Llame al procedimiento con un id_plan que tenga actividades asociadas y con otro que no tenga.

(Espacio para comentar el resultado esperado)_________________________________________________________________

Ejercicio 16: ultimaFechaActividadSocio

Cree un procedimiento almacenado llamado ultimaFechaActividadSocio que reciba p_id_socio INT y muestre la fecha más alta de actividad de ese socio.

Si el socio no tiene actividades registradas, debe manejar la situación con SIGNAL mostrando un mensaje de error.
Utilice un handler para capturar errores SQL y mostrar un mensaje genérico.

-- Escriba aquí el procedimiento:

DELIMITER $$
CREATE PROCEDURE ultimaFechaActividadSocio(IN p_id_socio INT)
BEGIN

END$$
DELIMITER ;

¿Qué sucede si se llama con un socio que no existe? ¿Y si existe pero no tiene actividades?

_________________________________________________________________

Ejercicio 17: reprogramarActividad

Desarrolle un procedimiento almacenado llamado reprogramarActividad que reciba como parámetros p_id_actividad INT y p_nueva_fecha DATE.

Verificar que la actividad exista en la tabla actividades.
Obtener la fecha actual registrada para esa actividad y validar que la nueva fecha sea diferente y no sea anterior a la fecha actual del sistema (CURDATE()).
Actualizar la fecha de la actividad indicada.
Si ocurre algún error, realizar ROLLBACK. Si todo sale bien, COMMIT.
Debe devolver mensajes adecuados para cada situación de error y un mensaje de éxito.

-- Escriba aquí el procedimiento:

DELIMITER $$
CREATE PROCEDURE reprogramarActividad(IN p_id_actividad INT, IN p_nueva_fecha DATE)
BEGIN

END$$
DELIMITER ;

Indique qué validaciones de fecha realiza y qué mensajes devuelve cada una.

_________________________________________________________________

Ejercicio 18: reasignarActividadesSocio

Desarrolle un procedimiento almacenado llamado reasignarActividadesSocio que reciba como parámetros p_id_socio_origen INT y p_id_socio_destino INT.

Verificar que ambos socios existan en la tabla socios.
Verificar que los socios sean distintos.
Verificar que el socio origen tenga al menos una actividad registrada en la tabla actividades.
Reasignar todas las actividades del socio origen al socio destino (actualizar id_socio en la tabla actividades).
Si ocurre algún error, realizar ROLLBACK y mostrar mensaje adecuado. Si todo sale bien, COMMIT y mostrar éxito.

-- Escriba aquí el procedimiento:

DELIMITER $$
CREATE PROCEDURE reasignarActividadesSocio(IN p_id_socio_origen INT, IN p_id_socio_destino INT)
BEGIN

END$$
DELIMITER ;

¿Qué sucede si se intenta reasignar actividades de un socio que no tiene actividades? ¿Qué mensaje se muestra?

_________________________________________________________________

Ejercicio 19: cancelarActividadesFuturas

Desarrolle un procedimiento almacenado llamado cancelarActividadesFuturas que reciba como parámetro p_id_socio INT.

Verificar que el socio exista en la tabla socios.
Verificar que el socio tenga actividades con fecha posterior a la fecha actual del sistema (CURDATE()).
Eliminar todas las actividades futuras del socio.
Si ocurre algún error, realizar ROLLBACK y mostrar mensaje de error. Si todo sale bien, COMMIT y mostrar éxito.
Debe manejar explícitamente los casos: socio no existe, socio no tiene actividades futuras, error en DELETE.

-- Escriba aquí el procedimiento:

DELIMITER $$
CREATE PROCEDURE cancelarActividadesFuturas(IN p_id_socio INT)
BEGIN

END$$
DELIMITER ;

Si el socio tiene actividades pasadas y futuras, ¿qué se elimina exactamente?

_________________________________________________________________

3. Errores comunes a evitar

Confundir EXIT con finalización total del procedimiento.
No considerar que CONTINUE permite seguir ejecutando.
Olvidar que RESIGNAL vuelve a lanzar el error.
No detectar errores no manejados.
No identificar correctamente el handler más interno.
Creer que un ROLLBACK sin SAVEPOINT puede revertir transacciones ya cerradas.
Pensar que los handlers solo se pueden usar para errores de SQL y no para lógica de negocio con SIGNAL.
No recordar que NOT FOUND solo se activa con SELECT...INTO.
Olvidar usar START TRANSACTION, COMMIT y ROLLBACK en procedimientos que modifican múltiples tablas.