Errores en procedimientos

Manejo de errores en procedimientos de MySQL

Programación de Bases de Datos (6.5.-Manipulación de errores o excepciones en MySQL)

Mostrar error al insertar un departamento en la base de datos de proyectos:

DELIMITER |
CREATE PROCEDURE Manejo()

BEGIN

    /*Declaramos el manipulador de errores*/

    DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' 
        SELECT 'Clave repetida.';

    /*Insertamos un nuevo departamento con clave repetida*/
    INSERT INTO departamento VALUES ('01','Comercial','Jaén');


    /*Mostramos todos los departamentos de la BD*/
    SELECT * FROM departamento; 

END
|
DELIMITER ;

Ejecución

CALL Manejo();

No funciona en phpMyAdmin, pero sí en consola!

Funcionamiento correcto en phpmyadmin:

DELIMITER |
CREATE PROCEDURE Manejo1()

BEGIN

    DECLARE repetido BOOLEAN DEFAULT 0;

    /*Declaramos el manipulador de errores*/
    DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' 
        -- SELECT 'Clave repetida.';
        SET repetido = 1;

    /*Insertamos un nuevo departamento con clave repetida*/
    INSERT INTO departamento VALUES ('01','Comercial','Jaén');

    /*Mostramos todos los departamentos de la BD*/
    IF (repetido) THEN
    	SELECT 'Clave repetida';
    ELSE
    	SELECT * FROM departamento; 
    END IF;
    
END
|
DELIMITER ;

Otra forma de manejar errores:

DELIMITER |
CREATE PROCEDURE Manejador_error1()

BEGIN

    DECLARE mensaje VARCHAR(30) DEFAULT "Departamento introducido OK";

    /*Declaramos el manipulador de errores*/
    DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
        SET mensaje = 'Clave repetida';

    /*Insertamos un nuevo departamento con clave repetida*/
    INSERT INTO departamento VALUES ('01','Comercial','Jaén');

    /*Mostramos el mensaje*/
    SELECT mensaje;

END
|

DELIMITER ;

Ejecución

CALL Manejador_error1();

DELIMITER |
CREATE PROCEDURE Manejador_error2()

BEGIN
    DECLARE mensaje VARCHAR(30) DEFAULT "Departamento introducido OK";

    /*Declaramos el manipulador de errores*/
    DECLARE EXIT HANDLER FOR SQLSTATE '23000'
        SELECT 'Clave repetida';

    /*Insertamos un nuevo departamento con clave repetida*/
    INSERT INTO departamento VALUES ('01','Comercial','Jaén');

    /*Mostramos el mensaje*/
    SELECT mensaje;

END
|
DELIMITER ;

Ejecución

CALL Manejador_error2();

Manejo de errores

Manejo de excepciones en MariaDB/MySQL

Crear tablas

USE test;

CREATE TABLE persona (
    id_persona INT UNSIGNED,
    PRIMARY KEY (id_persona));

CREATE TABLE errores (
    codigo CHAR(5),
    descripcion VARCHAR(150),
    usuario VARCHAR(50),
    fecha TIMESTAMP);

Mostrar el error

DELIMITER $$

CREATE OR REPLACE PROCEDURE demo()
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
    -- DECLARE EXIT HANDLER FOR SQLSTATE '23000'
        SELECT 'Aquí se produce el error';

    INSERT INTO persona VALUES(1);
    SELECT 'Mensaje tras el primer insert';
    INSERT INTO persona VALUES(1);
    SELECT 'Mensaje tras el segundo insert';
    INSERT INTO persona VALUES(2);
    SELECT 'Mensaje tras el tercer insert';
END
$$

DELIMITER ;

Ejecución

CALL demo();

Insertar una persona

DELIMITER $$

CREATE OR REPLACE PROCEDURE insertarPersona1(_id INT)
BEGIN
    DECLARE duplicado BOOLEAN DEFAULT FALSE;
    DECLARE mensaje VARCHAR(50) DEFAULT 'Has introducido un valor duplicado o nulo';

    DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
        SET duplicado :=  TRUE;

    INSERT INTO persona VALUES (_id);

    IF duplicado THEN
        INSERT INTO errores VALUES ('23000', mensaje, CURRENT_USER, NOW());
        SELECT mensaje AS Error;
    ELSE
        SELECT 'Dato introducido OK' AS Info;
    END IF;
END
$$

DELIMITER ;

Ejecución

CALL insertarPersona1(3);
CALL insertarPersona1(3);
CALL insertarPersona1(-5);

Error:

#1264 - Valor fuera de rango para la columna 'id_persona' en la fila 1

Manejar varios errores

DELIMITER $$ 

CREATE OR REPLACE PROCEDURE insertarPersona1(_id INT)

BEGIN
    DECLARE duplicado BOOLEAN DEFAULT FALSE;
    DECLARE mensaje VARCHAR(50) DEFAULT 'Has introducido un valor duplicado o nulo';
    DECLARE codigo CHAR(5);

    DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
    	BEGIN
            SET duplicado :=  TRUE;
            SET codigo := '23000';            
        END;
        
    DECLARE CONTINUE HANDLER FOR 1264
    	BEGIN
            SET duplicado := TRUE;
            SET mensaje := 'El valor no es positivo';
            SET codigo := '1264';
        END;


    INSERT INTO persona VALUES (_id);

    IF duplicado THEN
        INSERT INTO errores VALUES (codigo, mensaje, CURRENT_USER, NOW());
        SELECT mensaje AS Error;
    ELSE
        SELECT 'Dato introducido OK' AS Info;
    END IF;
END
$$

DELIMITER ;

Ejecución

CALL insertarPersona2(4);
CALL insertarPersona2(4);
CALL insertarPersona2(-5);

GET DIAGNOSTICS Statement

DELIMITER $$
CREATE OR REPLACE PROCEDURE insertarPersona2(_id INT)
BEGIN
    DECLARE codigo CHAR(5) DEFAULT NULL;
    DECLARE mensaje VARCHAR(150) DEFAULT 'Dato introducido OK';

    -- DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
        GET DIAGNOSTICS CONDITION 1 codigo = RETURNED_SQLSTATE, mensaje = MESSAGE_TEXT;

    INSERT INTO persona VALUES (_id);
    IF codigo iS NOT NULL THEN
        INSERT INTO errores VALUES (codigo, mensaje, CURRENT_USER, NOW());
        SELECT mensaje AS Error;
    ELSE
        SELECT mensaje AS Info;
    END IF;
END
$$

DELIMITER ;

Ejecución

CALL insertarPersona2(5);
CALL insertarPersona2(5);
CALL insertarPersona2(-5);

Handling Errors

Ejemplo: insertar un empleado en la base de datos proyectosDB

DELIMITER $$
CREATE PROCEDURE do_insert(value VARCHAR(3), name VARCHAR(30))
BEGIN
  -- Declare variables to hold diagnostics area information
  DECLARE code CHAR(5) DEFAULT '00000';
  DECLARE msg TEXT;
  DECLARE nrows INT;
  DECLARE result TEXT;
  DECLARE errorNumber VARCHAR(10);

  -- Declare exception handler for failed insert
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
      GET DIAGNOSTICS CONDITION 1
        code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT, errorNumber = MYSQL_ERRNO;
      SET result = CONCAT('insert failed, error = ', code,', message = ', msg, ', number = ', errorNumber);
    END;

  -- Perform the insert in proyectosDB database
  INSERT INTO empleado (cdemp, nombre) VALUES(value, name);

  -- Check whether the insert was successful
  IF code = '00000' THEN
    GET DIAGNOSTICS nrows = ROW_COUNT;
    SET result = CONCAT('insert succeeded, row count = ', nrows);   
  END IF;

  -- Say what happened
  SELECT result;
END
$$
 
DELIMITER ;

Ejecución del procedimiento do_insert:

SET @p1 = 'B10', @p2 = 'Ana Martín';
 
CALL do_insert(@p1, @p2);

CALL do_insert(@p1, @p2);

Más información:

MySQL 8.0 Error Message Reference

GET DIAGNOSTICS Statement