|
Implement the RAISE statement:
where exceptions_name is one of those implemented in MDEV-10839 and MDEV-10582:
- NO_DATA_FOUND
- TOO_MANY_ROWS
- DUP_VAL_ON_INDEX
- INVALID_CURSOR
NO_DATA_FOUND
Oracle's NO_DATA_FOUND will be translated to MariaDB warning ER_SP_FETCH_NO_DATA.
The following three scripts do not return any errors, which proves that NO_DATA_FOUND is cought and raised silently and therefore is more like a warning than an error:
DROP PROCEDURE p1;
|
CREATE PROCEDURE p1
|
AS
|
BEGIN
|
RAISE NO_DATA_FOUND;
|
END;
|
/
|
CALL p1();
|
DROP TABLE t1;
|
DROP PROCEDURE p1;
|
CREATE TABLE t1 (a INT);
|
CREATE PROCEDURE p1
|
AS
|
a INT;
|
BEGIN
|
SELECT a INTO a FROM t1;
|
END;
|
/
|
CALL p1();
|
DROP TABLE t1;
|
DROP PROCEDURE p1;
|
CREATE TABLE t1 (a INT);
|
CREATE PROCEDURE p1
|
AS
|
a INT;
|
BEGIN
|
SELECT a INTO a FROM t1;
|
EXCEPTION
|
WHEN NO_DATA_FOUND THEN RAISE;
|
END;
|
/
|
CALL p1();
|
This script demonstrates that NO_DATA_FOUND is actually cought and can be translated to a fatal error using an EXCEPTION..RAISE statement.
DROP TABLE t1;
|
DROP PROCEDURE p1;
|
CREATE TABLE t1 (a INT);
|
CREATE PROCEDURE p1
|
AS
|
a INT;
|
e EXCEPTION;
|
BEGIN
|
SELECT a INTO a FROM t1;
|
EXCEPTION
|
WHEN NO_DATA_FOUND THEN RAISE e;
|
END;
|
/
|
CALL p1();
|
TOO_MANY_ROWS
Oracle's TOO_MANY_ROWS will be translated to MariaDB error TOO_MANY_ROWS.
The following three scripts return an error:
ORA-01422: exact fetch returns more than requested number of rows
|
DROP PROCEDURE p1;
|
CREATE PROCEDURE p1
|
AS
|
BEGIN
|
RAISE TOO_MANY_ROWS;
|
END;
|
/
|
CALL p1();
|
DROP TABLE t1;
|
DROP PROCEDURE p1;
|
CREATE TABLE t1 (a INT);
|
INSERT INTO t1 VALUES (10);
|
INSERT INTO t1 VALUES (20);
|
CREATE PROCEDURE p1
|
AS
|
a INT;
|
BEGIN
|
SELECT a INTO a FROM t1;
|
END;
|
/
|
CALL p1();
|
DROP TABLE t1;
|
DROP PROCEDURE p1;
|
CREATE TABLE t1 (a INT);
|
INSERT INTO t1 VALUES (10);
|
INSERT INTO t1 VALUES (20);
|
CREATE PROCEDURE p1
|
AS
|
a INT;
|
BEGIN
|
SELECT a INTO a FROM t1;
|
EXCEPTION
|
WHEN TOO_MANY_ROWS THEN RAISE;
|
END;
|
/
|
CALL p1();
|
DUP_VAL_ON_INDEX
Oracle's DUP_VAL_ON_INDEX will be translated to MariaDB error ER_DUP_ENTRY.
The following three scripts return an error:
ORA-00001: unique constraint ... violated
|
DROP PROCEDURE p1;
|
CREATE PROCEDURE p1
|
AS
|
BEGIN
|
RAISE DUP_VAL_ON_INDEX;
|
END;
|
/
|
CALL p1();
|
DROP TABLE t1;
|
DROP PROCEDURE p1;
|
CREATE TABLE t1 (a INT PRIMARY KEY);
|
CREATE PROCEDURE p1
|
AS
|
BEGIN
|
INSERT INTO t1 VALUES (10);
|
INSERT INTO t1 VALUES (10);
|
END;
|
/
|
CALL p1();
|
DROP TABLE t1;
|
DROP PROCEDURE p1;
|
CREATE TABLE t1 (a INT PRIMARY KEY);
|
CREATE PROCEDURE p1
|
AS
|
BEGIN
|
INSERT INTO t1 VALUES (10);
|
INSERT INTO t1 VALUES (10);
|
EXCEPTION
|
WHEN DUP_VAL_ON_INDEX THEN RAISE;
|
END;
|
/
|
CALL p1();
|
INVALID_CURSOR
Oracle's INVALID_CURSOR will be translated to MariaDB error ER_SP_CURSOR_NOT_OPEN.
|
[MDEV-10840]