sql_mode=ORACLE: User defined exceptions

When running sql_mode=ORACLE, MariaDB should support:

• user defined EXCEPTION declaration
• using user defined exceptions in RAISE name (signal)
• using user defined exceptions in RAISE (resignal)
• using user defined exceptions in EXCEPTION WHEN

DROP FUNCTION f1;

CREATE FUNCTION f1 (a INT) RETURN INT

AS

  e1 EXCEPTION;

BEGIN

  IF a < 0 THEN

    RAISE e1;  

  END IF;

  RETURN 0;

EXCEPTION  

  WHEN e1 THEN RETURN 1;

END;

/

User defined exception names should be case insensitive.

Details

MariaDB does not support multiple handlers for the same SQLSTATE. This script returns an error:

SET sql_mode=DEFAULT;

DROP PROCEDURE IF EXISTS p1;

DELIMITER $$

CREATE PROCEDURE p1()

BEGIN

  DECLARE c0 CONDITION FOR SQLSTATE '45000';

  DECLARE c1 CONDITION FOR SQLSTATE '45000';

  DECLARE CONTINUE HANDLER FOR c0 SET @c0= 0;

  DECLARE CONTINUE HANDLER FOR c1 SET @c0= 1;

END;

$$

ERROR 1413 (42000): Duplicate handler declared in the same block

In sql_mode=ORACLE it will be possible to have multiple user-defined exceptions in the same block. All user defined exceptions will be associated with SQLSTATE '45000' and MySQL errno ER_SIGNAL_EXCEPTION

SET sql_mode=ORACLE;

DELIMITER $$;

CREATE FUNCTION f1(c VARCHAR) RETURN VARCHAR

AS

  e EXCEPTION;

  f EXCEPTION;

  a VARCHAR(64):='';

BEGIN

  BEGIN

    IF c = 'e' THEN RAISE e; END IF;

    IF c = 'f' THEN RAISE f; END IF;

  EXCEPTION

    WHEN e THEN BEGIN a:='Got EXCEPTION1/e; '; RAISE e; END;

    WHEN f THEN BEGIN a:='Got EXCEPTION1/f; '; RAISE f; END;

  END;

  RETURN 'Got no exceptions';

EXCEPTION

  WHEN OTHERS THEN RETURN a || 'Got EXCEPTION2/OTHERS;';

END;

$$

Notice, both e and f are associated with the same SQLSTATE and errno, but having WHEN e followed by WHEN f is valid, because e and f are different exceptions.

However, specifying the same exception multiple times in WHEN clause will not be possible:

SET sql_mode=ORACLE;

DELIMITER $$;

CREATE FUNCTION f1() RETURN VARCHAR

AS

  e EXCEPTION;

BEGIN

  RETURN 'Got no exceptions';

EXCEPTION

  WHEN e THEN RETURN 'Got exception e';

  WHEN e THEN RETURN 'Got exception e';

END;

$$

The above script will return the ER_SP_DUP_HANDLER error:

ERROR 42000: Duplicate handler declared in the same block

Name space

In MariaDB variables and exceptions (conditions) are in separate name spaces. Oracle-alike user defined exceptions will be in the same name space with MariaDB conditions.
In Oracle, variables and exceptions (conditions) are in the same name space. Placing exceptions and variables into the same name space will be done separately. See MDEV-11058.