Diferencia entre revisiones de «BD UD7 Transacciones en procedimientos y funciones»

De MediaWiki
Ir a la navegación Ir a la búsqueda
Línea 339: Línea 339:
 
: Indicar que cuando capturamos 'tipos de error', por ejemplo, cuando intentamos añadir una clave primaria duplicada, no podremos determinar directamente cual es la tabla que provoca el error. Podemos analizar el mensaje del error y ver a que tabla hace referencia.
 
: Indicar que cuando capturamos 'tipos de error', por ejemplo, cuando intentamos añadir una clave primaria duplicada, no podremos determinar directamente cual es la tabla que provoca el error. Podemos analizar el mensaje del error y ver a que tabla hace referencia.
  
−
::<syntaxhighlight lang="sql" enclose="div" highlight="16,11,18-22,24,33" line>
+
::<syntaxhighlight lang="sql" enclose="div" highlight="14,19,21-25,27,34">
 
USE `CIRCO`;
 
USE `CIRCO`;
 
DROP procedure IF EXISTS `artistas_add`;
 
DROP procedure IF EXISTS `artistas_add`;
Línea 382: Línea 382:
  
 
: Vamos ahora a provocar que haya una excepción añadiendo una orden INSERT (que no tiene sentido, sólo es para provocar la excepción):
 
: Vamos ahora a provocar que haya una excepción añadiendo una orden INSERT (que no tiene sentido, sólo es para provocar la excepción):
−
::<syntaxhighlight lang="sql" enclose="div" highlight="33,35">
+
::<syntaxhighlight lang="sql" enclose="div" highlight="34,35">
 
USE `CIRCO`;
 
USE `CIRCO`;
 
DROP procedure IF EXISTS `artistas_add`;
 
DROP procedure IF EXISTS `artistas_add`;

Revisión del 11:44 4 may 2020

Introducción

Vamos a realizar diferentes ejercicios (algunos los modificaremos, de los que llevamos realizados hasta ahora) en los que es necesario el uso de transacciones.
Dejaremos el nivel de aislamiento por defecto que suele ser el más adecuado para las operaciones que se realizan habitualmente contra una base de datos.


Recordar que en las secciones anteriores vimos como el control de errores lo podemos realizar en el 'cliente' o en el 'procedimiento/función'.
Si el procedimiento/función realiza varias operaciones lo normal es que lleve asociado una transacción y un control de errores capturando las posibles excepciones.
A nivel de cliente (aplicación realizada en Java, Php, ASP.NET,...que hace uso de los procedimientos/funciones creados) si la aplicación llama a varios procedimientos o realiza varias operaciones sobre la base de datos, debe llevar también una transacción y un control de errores (sección try...catch) para que pueda realizar el rollback (cancelación) de la transacción.


  • Cuando creamos el procedimiento/función, puede que nos interese capturar excepciones concretas para informar al usuario de ese error en concreto e informarlo con un mensaje más personalizado del tipo de error. Pero normalmente querremos capturar todos los posibles errores, informar al usuario de que se ha producido un error y que sea el administrador el que revise el código de error concreto.
Para capturar cualquier tipo de error SQL, podemos hacer uso de la orden DECLARE HANDLER como ya vimos en esta WIKI, utilizando esta sintaxis: 
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET rollback = 1;
Fijarse en FOR SQLEXCEPION. En el ejemplo se asigna un valor a una variable, pero el tratamiento que le deis a la excepción dependerá de vuestro procedimiento.


  • IDEA:
A nivel de administrador de la base de datos, podríamos registrar en una tabla, el usuario, nombre del procedimiento, fecha-hora, tipo de error y mensaje de error.
A dicha tabla sólo podría acceder el administrador y la persona que se encarga de implementar los procedimientos/funciones y así comprobar que errores se ha producido.
Para ello habría que capturar cualquier posible excepción y obtener el texto de la misma. Debemos de hacer uso de la orden GET DIAGNOSTIC de la forma: 
DECLARE EXIT HANDLER FOR SQLEXCEPTION    
BEGIN

GET DIAGNOSTICS CONDITION 1
    @p2 = MESSAGE_TEXT;
    INSERT INTO LOG_ERRORS (error_message) 
END;
En el ejemplo, se sale del procedimiento/función y se añade una fila a la tabla LOGS_ERRORS. A mayores del texto, yo pondría el nombre del procedimiento, usuario y fecha-hora.



  • Aviso: Cuidado con utilizar la orden START TRANSACTION dentro de un bucle ya que estaríais creando una transacción por cada orden SQL y eso no se debe hacer.



Ejercicios propuestos Transacciones

Añadir una transacción y controlar los posibles errores.


Crea una nueva versión de este procedimiento y llámale animales_deleteV2.
Este ejercicio estaba pensado para que el control de errores se hiciera desde donde se llamara al procedimiento (desde el programa cliente) y por eso se lanzaba la excepción.
Ahora vamos a capturar todas las excepciones, por lo que en función de v_anos actúa en consecuencia enviando al cliente un código con el SELECT en vez de lanzar una excepción.
Añadir una transacción y controlar los posibles errores.


En este caso vamos a mantener la captura de excepciones y devolver un valor diferente en p_resultado (como está hecho ahora).
Devuelve en p_resultado el valor -3 en caso de error no controlado.
Añadir una transacción (por lo tanto tendréis que hacer un ROLLBACK en todos los tipos de errores capturados).
Indicar que cuando capturamos 'tipos de error', por ejemplo, cuando intentamos añadir una clave primaria duplicada, no podremos determinar directamente cual es la tabla que provoca el error. Podemos analizar el mensaje del error y ver a que tabla hace referencia.





Soluciones Ejercicios propuestos Transacciones

Añadir una transacción y controlar los posibles errores.
USE `CIRCO`;
DROP procedure IF EXISTS `animales_AddAforoPorTipo`;

DELIMITER $$
USE `CIRCO`$$

CREATE PROCEDURE `animales_AddAforoPorTipo`(p_tipo varchar(9), p_incAforo smallint)
    COMMENT 'Hace uso del método animales_AddAforo para incrementar el aforo de las pistas donde trabajan los animales del tipo indicado'
BEGIN
    -- Declaración de variables
    DECLARE v_final INTEGER DEFAULT 0;
    DECLARE v_nombrePista VARCHAR(50);
    DECLARE v_nuevoAforo SMALLINT;

    -- Declaración del cursor
    DECLARE c_pistas CURSOR FOR 
        SELECT DISTINCT nombre_pista
        FROM ANIMALES
        WHERE tipo = p_tipo;
        
  
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_Final = TRUE;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        SELECT -1;   -- Informamos a quien llamó de que algo fue mal
        DROP TEMPORARY TABLE T_TEMPORAL;
        CLOSE c_pistas;
        ROLLBACK;	-- CANCELA LA TRANSACCIÓN EN CASO DE ERROR
    END;

    START TRANSACTION;	-- INICIO DE LA TRANSACCIÓN

    CREATE TEMPORARY TABLE T_TEMPORAL (nombrePista varchar(50), nuevoAforo SMALLINT);
    OPEN c_pistas;


    read_loop: LOOP
        FETCH c_pistas INTO v_nombrePista;
        IF v_final THEN
            LEAVE read_loop;
        END IF;
        
        SET v_nuevoAforo = p_incAforo;
        CALL pistas_addAforo(v_nombrePista,v_nuevoAforo);	-- En v_nuevoAforo está el aforo incrementado
        
        INSERT INTO T_TEMPORAL (nombrePista,nuevoAforo)
        VALUES (v_nombrePista,v_nuevoAforo);
    
    END LOOP;

    SELECT nombrePista,nuevoAforo
    FROM T_TEMPORAL
    ORDER BY nombrePista;

    CLOSE c_pistas;   
    
    DROP TEMPORARY TABLE T_TEMPORAL;
    
    COMMIT;	    -- CONFIRMA LA TRANSACCIÓN

END$$

DELIMITER ;
Ahora todas las operaciones están dentro de una transacción y en caso de error se hará el ROLLBACK.
Vamos a provocar que después del bucle haya una excepción:
USE `CIRCO`;
DROP procedure IF EXISTS `animales_AddAforoPorTipo`;

DELIMITER $$
USE `CIRCO`$$

CREATE PROCEDURE `animales_AddAforoPorTipo`(p_tipo varchar(9), p_incAforo smallint)
    COMMENT 'Hace uso del método animales_AddAforo para incrementar el aforo de las pistas donde trabajan los animales del tipo indicado'
BEGIN
    -- Declaración de variables
    DECLARE v_final INTEGER DEFAULT 0;
    DECLARE v_nombrePista VARCHAR(50);
    DECLARE v_nuevoAforo SMALLINT;

    -- Declaración del cursor
    DECLARE c_pistas CURSOR FOR 
        SELECT DISTINCT nombre_pista
        FROM ANIMALES
        WHERE tipo = p_tipo;
        
  
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_Final = TRUE;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        SELECT -1;   -- Informamos a quien llamó de que algo fue mal
        DROP TEMPORARY TABLE T_TEMPORAL;
        CLOSE c_pistas;
        ROLLBACK;	-- CANCELA LA TRANSACCIÓN EN CASO DE ERROR
    END;

    START TRANSACTION;	-- INICIO DE LA TRANSACCIÓN

    CREATE TEMPORARY TABLE T_TEMPORAL (nombrePista varchar(50), nuevoAforo SMALLINT);
    OPEN c_pistas;


    read_loop: LOOP
        FETCH c_pistas INTO v_nombrePista;
        IF v_final THEN
            LEAVE read_loop;
        END IF;
        
        SET v_nuevoAforo = p_incAforo;
        CALL pistas_addAforo(v_nombrePista,v_nuevoAforo);	-- En v_nuevoAforo está el aforo incrementado
        
        INSERT INTO T_TEMPORAL (nombrePista,nuevoAforo)
        VALUES (v_nombrePista,v_nuevoAforo);
    
    END LOOP;

    INSERT INTO PISTAS  -- PROVOCA UNA EXCEPCIÓN DE CLAVE PRIMARIA DUPLICADA
    VALUES ('LATERAL1',100);

    SELECT nombrePista,nuevoAforo
    FROM T_TEMPORAL
    ORDER BY nombrePista;

    CLOSE c_pistas;   
    
    DROP TEMPORARY TABLE T_TEMPORAL;
    
    COMMIT;	    -- CONFIRMA LA TRANSACCIÓN

END$$

DELIMITER ;
Si ahora llamamos al procedimiento podemos comprobar como los aforos de las pistas no se actualizan (ninguno de ellos) y el procedimiento devuelve el valor -1.
Quitar del procedimiento el INSERT añadido después de probar.



Crea una nueva versión de este procedimiento y llámale animales_deleteV2.
Este ejercicio estaba pensado para que el control de errores se hiciera desde donde se llamara al procedimiento (desde el programa cliente) y por eso se lanzaba la excepción.
Ahora vamos a capturar todas las excepciones, por lo que en función de v_anos actúa en consecuencia enviando al cliente un código con el SELECT en vez de lanzar una excepción.
Añadir una transacción y controlar los posibles errores.
USE `CIRCO`;
DROP procedure IF EXISTS `animales_deleteV2`;

DELIMITER $$
CREATE PROCEDURE `animales_deleteV2`(p_nombreAnimal VARCHAR(50))
    COMMENT 'Da de baja un animal siempre que su edad sea superior a 2 años'
bloque_proc:BEGIN
    DECLARE v_anos TINYINT default -1;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        SELECT -3;   -- Informamos a quien llamó de que algo fue mal
        ROLLBACK;	-- CANCELA LA TRANSACCIÓN EN CASO DE ERROR
    END;
   
    START TRANSACTION;

    SELECT anhos
    INTO v_anos
    FROM ANIMALES
    WHERE nombre = p_nombreAnimal;
    
    CASE 
        WHEN v_anos = -1 THEN
            SELECT -1;
            LEAVE bloque_proc;    
        WHEN v_anos < 2 THEN
            SELECT -2;
            LEAVE bloque_proc;    
         ELSE BEGIN END;
    END CASE;

    -- BORRAMOS LOS ANIMALES. Necesitaríamos una transacción. 
    DELETE FROM ANIMALES_ARTISTAS
    WHERE nombre_animal = p_nombreAnimal;
    
    DELETE FROM ANIMALES
    WHERE nombre = p_nombreAnimal;

    COMMIT;
END$$

DELIMITER ;
Vamos ahora a provocar que haya una excepción añadiendo una orden INSERT (que no tiene sentido, sólo es para provocar la excepción): 
USE `CIRCO`;
DROP procedure IF EXISTS `animales_deleteV2`;

DELIMITER $$
CREATE PROCEDURE `animales_deleteV2`(p_nombreAnimal VARCHAR(50))
    COMMENT 'Da de baja un animal siempre que su edad sea superior a 2 años'
bloque_proc:BEGIN
    DECLARE v_anos TINYINT default -1;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        SELECT -3;   -- Informamos a quien llamó de que algo fue mal
        ROLLBACK;	-- CANCELA LA TRANSACCIÓN EN CASO DE ERROR
    END;
   
    START TRANSACTION;

    SELECT anhos
    INTO v_anos
    FROM ANIMALES
    WHERE nombre = p_nombreAnimal;
    
    CASE 
        WHEN v_anos = -1 THEN
            SELECT -1;
            LEAVE bloque_proc;    
        WHEN v_anos < 2 THEN
            SELECT -2;
            LEAVE bloque_proc;    
         ELSE BEGIN END;
    END CASE;

    -- BORRAMOS LOS ANIMALES. Necesitaríamos una transacción. 
    DELETE FROM ANIMALES_ARTISTAS
    WHERE nombre_animal = p_nombreAnimal;
    
    DELETE FROM ANIMALES
    WHERE nombre = p_nombreAnimal;

    INSERT INTO PISTAS
    VALUES ('LATERAL1',100);

    COMMIT;
END$$

DELIMITER ;
Si ahora llamáis podéis comprobar como no se borra ningún animal...
Quitar del procedimiento el INSERT añadido después de probar.



En este caso vamos a mantener la captura de excepciones y devolver un valor diferente en p_resultado (como está hecho ahora).
Devuelve en p_resultado el valor -3 en caso de error no controlado.
Añadir una transacción (por lo tanto tendréis que hacer un ROLLBACK en todos los tipos de errores capturados).
Indicar que cuando capturamos 'tipos de error', por ejemplo, cuando intentamos añadir una clave primaria duplicada, no podremos determinar directamente cual es la tabla que provoca el error. Podemos analizar el mensaje del error y ver a que tabla hace referencia.
USE `CIRCO`;
DROP procedure IF EXISTS `artistas_add`;

DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `artistas_add`(p_nif CHAR(9),p_apellidos varchar(100), p_nombre varchar(50),p_nifJefe char(9),p_nombreAnimal varchar(50), OUT p_resultado tinyint)
    COMMENT 'Añade un nuevo artista'
BEGIN
    DECLARE ex_artista_duplicado CONDITION FOR 1062;
    DECLARE ex_animal_no_existe CONDITION FOR SQLSTATE '45000';
    
    DECLARE EXIT HANDLER FOR ex_artista_duplicado
    BEGIN
        SET p_resultado = -1;
        ROLLBACK;	-- CANCELA LA TRANSACCIÓN EN CASO DE ERROR
    END;
    DECLARE EXIT HANDLER FOR ex_animal_no_existe
    BEGIN
        SET p_resultado = -2;
        ROLLBACK;	-- CANCELA LA TRANSACCIÓN EN CASO DE ERROR
    END;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        SET p_resultado = -3;   -- Informamos a quien llamó de que algo fue mal
        ROLLBACK;	-- CANCELA LA TRANSACCIÓN EN CASO DE ERROR
    END;

    START TRANSACTION;
    
    INSERT INTO ARTISTAS (nif, apellidos, nombre, nif_jefe)
    VALUES (p_nif, p_apellidos, p_nombre, p_nifJefe);

    CALL animales_addArtista(p_nombreAnimal,p_nif);

    COMMIT;
    
    SET p_resultado = 0;
END$$
DELIMITER ;


Vamos ahora a provocar que haya una excepción añadiendo una orden INSERT (que no tiene sentido, sólo es para provocar la excepción): 
USE `CIRCO`;
DROP procedure IF EXISTS `artistas_add`;

DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `artistas_add`(p_nif CHAR(9),p_apellidos varchar(100), p_nombre varchar(50),p_nifJefe char(9),p_nombreAnimal varchar(50), OUT p_resultado tinyint)
    COMMENT 'Añade un nuevo artista'
BEGIN
    DECLARE ex_artista_duplicado CONDITION FOR 1062;
    DECLARE ex_animal_no_existe CONDITION FOR SQLSTATE '45000';
    
    DECLARE EXIT HANDLER FOR ex_artista_duplicado
    BEGIN
        SET p_resultado = -1;
        ROLLBACK;	-- CANCELA LA TRANSACCIÓN EN CASO DE ERROR
    END;
    DECLARE EXIT HANDLER FOR ex_animal_no_existe
    BEGIN
        SET p_resultado = -2;
        ROLLBACK;	-- CANCELA LA TRANSACCIÓN EN CASO DE ERROR
    END;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        SET p_resultado = -3;   -- Informamos a quien llamó de que algo fue mal
        ROLLBACK;	-- CANCELA LA TRANSACCIÓN EN CASO DE ERROR
    END;

    START TRANSACTION;
    
    INSERT INTO ARTISTAS (nif, apellidos, nombre, nif_jefe)
    VALUES (p_nif, p_apellidos, p_nombre, p_nifJefe);

    CALL animales_addArtista(p_nombreAnimal,p_nif);

    INSERT INTO ANIMALES_ARTISTAS
    VALUES ('NO EXISTE','NO EXISTE');
    
    COMMIT;
    
    SET p_resultado = 0;
END$$
DELIMITER ;
Si ahora llamáis podéis comprobar como no añade ningún artista...y devuelve el valor -3.
Quitar del procedimiento el INSERT añadido después de probar.




Enlace a la página principal de la UD7

Enlace a la página principal del curso




-- Ángel D. Fernández González -- (2020).

Obtenido de «https://wiki.cifprodolfoucha.es/index.php?title=BD_UD7_Transacciones_en_procedimientos_y_funciones&oldid=10443»