SET sql_mode=ORACLE; --echo # --echo # sql_mode=ORACLE: Predefined exceptions: TOO_MANY_ROWS, NO_DATA_FOUND, DUP_VAL_ON_INDEX --echo # --echo # --echo # Testing NO_DATA_FOUND and TOO_MANY_ROWS --echo # CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (10),(20); DELIMITER $$; CREATE PROCEDURE p1(lim INT, res OUT VARCHAR) AS a INT; BEGIN SELECT a INTO a FROM t1 LIMIT lim; EXCEPTION WHEN TOO_MANY_ROWS THEN res:='--- too_many_rows cought ---'; WHEN NO_DATA_FOUND THEN res:='--- no_data_found cought ---'; END; $$ DELIMITER ;$$ SET @res=''; CALL p1(0, @res); SELECT @res; CALL p1(2, @res); SELECT @res; DROP PROCEDURE p1; DROP TABLE t1; --echo # --echo # Testing DUP_VAL_ON_INDEX --echo # CREATE TABLE t1 (a INT PRIMARY KEY); DELIMITER $$; CREATE PROCEDURE p1(res OUT VARCHAR) AS BEGIN INSERT INTO t1 VALUES (10); INSERT INTO t1 VALUES (10); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN res:='--- dup_val_on_index cought ---'; END; $$ DELIMITER ;$$ SET @res=''; CALL p1(@res); SELECT @res; SELECT * FROM t1; DROP PROCEDURE p1; DROP TABLE t1; --echo # --echo # MDEV-10840 sql_mode=ORACLE: RAISE statement for predefined exceptions --echo # --echo # --echo # RAISE outside of an SP context --echo # --error ER_SP_COND_MISMATCH RAISE NO_DATA_FOUND; --error ER_SP_COND_MISMATCH RAISE INVALID_CURSOR; --error ER_SP_COND_MISMATCH RAISE DUP_VAL_ON_INDEX; --error ER_SP_COND_MISMATCH RAISE TOO_MANY_ROWS; --error ER_RESIGNAL_WITHOUT_ACTIVE_HANDLER RAISE; --echo # --echo # RAISE for an undefinite exception --echo # DELIMITER $$; --error ER_SP_COND_MISMATCH CREATE PROCEDURE p1 AS BEGIN RAISE xxx; END; $$ DELIMITER ;$$ --echo # --echo # RAISE for predefined exceptions --echo # DELIMITER $$; CREATE PROCEDURE p1 AS BEGIN RAISE no_data_found; END; $$ DELIMITER ;$$ CALL p1(); DROP PROCEDURE p1; DELIMITER $$; CREATE PROCEDURE p1 AS BEGIN RAISE invalid_cursor; END; $$ DELIMITER ;$$ --error ER_SP_CURSOR_NOT_OPEN CALL p1(); DROP PROCEDURE p1; DELIMITER $$; CREATE PROCEDURE p1 AS BEGIN RAISE dup_val_on_index; END; $$ DELIMITER ;$$ --error ER_DUP_ENTRY CALL p1(); DROP PROCEDURE p1; DELIMITER $$; CREATE PROCEDURE p1 AS BEGIN raise too_many_rows; END; $$ DELIMITER ;$$ --error ER_TOO_MANY_ROWS CALL p1(); DROP PROCEDURE p1; --echo # --echo # RAISE with no exception name (resignal) --echo # DELIMITER $$; CREATE PROCEDURE p1() AS BEGIN RAISE; END; $$ DELIMITER ;$$ --error ER_RESIGNAL_WITHOUT_ACTIVE_HANDLER CALL p1(); DROP PROCEDURE p1; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (10),(20); DELIMITER $$; CREATE PROCEDURE p1(lim INT) AS a INT; BEGIN SELECT a INTO a FROM t1 LIMIT lim; EXCEPTION WHEN TOO_MANY_ROWS THEN RAISE; WHEN NO_DATA_FOUND THEN RAISE; END; $$ DELIMITER ;$$ CALL p1(0); --error ER_TOO_MANY_ROWS CALL p1(2); DROP PROCEDURE p1; DROP TABLE t1; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (10),(20); DELIMITER $$; CREATE PROCEDURE p1(lim INT) AS a INT; BEGIN SELECT a INTO a FROM t1 LIMIT lim; EXCEPTION WHEN OTHERS THEN RAISE; END; $$ DELIMITER ;$$ CALL p1(0); --error ER_TOO_MANY_ROWS CALL p1(2); DROP PROCEDURE p1; DROP TABLE t1; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (10),(20); DELIMITER $$; CREATE PROCEDURE p1() AS a INT; CURSOR c IS SELECT a FROM t1; BEGIN FETCH c INTO a; EXCEPTION WHEN INVALID_CURSOR THEN RAISE; END; $$ DELIMITER ;$$ --error ER_SP_CURSOR_NOT_OPEN CALL p1(); DROP PROCEDURE p1; DROP TABLE t1; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (10),(20); DELIMITER $$; CREATE PROCEDURE p1() AS a INT; CURSOR c IS SELECT a FROM t1; BEGIN FETCH c INTO a; EXCEPTION WHEN OTHERS THEN RAISE; END; $$ DELIMITER ;$$ --error ER_SP_CURSOR_NOT_OPEN CALL p1(); DROP PROCEDURE p1; DROP TABLE t1; --echo # --echo # Testing that warning-alike errors are caught by OTHERS --echo # CREATE TABLE t1 (a INT); DELIMITER $$; CREATE FUNCTION f1 RETURN VARCHAR AS a INT:=10; BEGIN SELECT a INTO a FROM t1; RETURN 'OK'; EXCEPTION WHEN OTHERS THEN RETURN 'Exception'; END; $$ DELIMITER ;$$ SELECT f1() FROM DUAL; DROP FUNCTION f1; DROP TABLE t1; --echo # --echo # End of MDEV-10840 sql_mode=ORACLE: RAISE statement for predefined exceptions --echo # --echo # --echo # MDEV-10587 sql_mode=ORACLE: User defined exceptions --echo # --echo # --echo # Checking that duplicate WHEN clause is not allowed --echo # DELIMITER $$; --error ER_SP_DUP_HANDLER 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; $$ DELIMITER ;$$ --echo # --echo # Checking that raised user exceptions are further caught by name --echo # DELIMITER $$; CREATE FUNCTION f1(c VARCHAR) RETURN VARCHAR AS e EXCEPTION; f EXCEPTION; BEGIN IF c = 'e' THEN RAISE e; END IF; IF c = 'f' THEN RAISE f; END IF; RETURN 'Got no exceptions'; EXCEPTION WHEN e THEN RETURN 'Got exception e'; END; $$ DELIMITER ;$$ SELECT f1(''); SELECT f1('e'); --error ER_SIGNAL_EXCEPTION SELECT f1('f'); DROP FUNCTION f1; --echo # --echo # Checking that raised user exceptions are further caught by OTHERS --echo # DELIMITER $$; CREATE FUNCTION f1(c VARCHAR) RETURN VARCHAR AS e EXCEPTION; f EXCEPTION; BEGIN IF c = 'e' THEN RAISE e; END IF; IF c = 'f' THEN RAISE f; END IF; RETURN 'Got no exceptions'; EXCEPTION WHEN OTHERS THEN RETURN 'Got some exception'; END; $$ DELIMITER ;$$ SELECT f1(''); SELECT f1('e'); SELECT f1('f'); DROP FUNCTION f1; --echo # --echo # Checking that 'WHEN e .. WHEN f' does not produce ER_SP_DUP_HANDLER --echo # 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; $$ DELIMITER ;$$ SELECT f1(''); SELECT f1('e'); SELECT f1('f'); DROP FUNCTION f1; --echo # --echo # Checking that resignaled user exceptions are further caught by name --echo # 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; END; WHEN f THEN BEGIN a:='Got EXCEPTION1/f; '; RAISE; END; END; RETURN 'Got no exceptions'; EXCEPTION WHEN e THEN RETURN a || 'Got EXCEPTION2/e;'; END; $$ DELIMITER ;$$ SELECT f1(''); SELECT f1('e'); --error ER_SIGNAL_EXCEPTION SELECT f1('f'); DROP FUNCTION f1; --echo # --echo # Checking that resignaled user exceptions are further caught by OTHERS --echo # 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; END; WHEN f THEN BEGIN a:='Got EXCEPTION1/f; '; RAISE; END; END; RETURN 'Got no exceptions'; EXCEPTION WHEN OTHERS THEN RETURN a || 'Got EXCEPTION2/OTHERS;'; END; $$ DELIMITER ;$$ SELECT f1(''); SELECT f1('e'); SELECT f1('f'); DROP FUNCTION f1; --echo # --echo # End of MDEV-10587 sql_mode=ORACLE: User defined exceptions --echo # --echo # --echo # MDEV-12088 sql_mode=ORACLE: Do not require BEGIN..END in multi-statement exception handlers in THEN clause --echo # CREATE TABLE t1 (a INT PRIMARY KEY); INSERT INTO t1 VALUES (10),(20),(30); DELIMITER $$; CREATE PROCEDURE p1(a INT) AS BEGIN INSERT INTO t1 (a) VALUES (a); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN a:= a+1; INSERT INTO t1 VALUES (a); WHEN OTHERS THEN NULL; NULL; END; $$ DELIMITER ;$$ CALL p1(30); SELECT * FROM t1; DROP PROCEDURE p1; DROP TABLE t1;