Details
-
Technical task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
10.2.2-3, 10.2.2-1, 10.2.2-2, 10.2.2-4, 10.1.18
Description
Implement the RAISE statement:
RAISE [exception_name];
|
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.