2 files changed, 204 insertions, 0 deletions
diff --git a/mysql-test/suite/compat/oracle/t/sp-code.test b/mysql-test/suite/compat/oracle/t/sp-code.test
index ff78410540c..dffd1a19fd2 100644
--- a/mysql-test/suite/compat/oracle/t/sp-code.test
+++ b/mysql-test/suite/compat/oracle/t/sp-code.test
@@ -2,6 +2,10 @@
 
 SET sql_mode=ORACLE;
 
+--echo #
+--echo # Testing exceptions in the top-level blocks
+--echo #
+
 --echo # No HANDLER declarations, no exceptions
 DELIMITER /;
 CREATE FUNCTION f1 RETURN INT
@@ -143,3 +147,175 @@ END;
 DELIMITER ;/
 SHOW PROCEDURE CODE p1;
 DROP PROCEDURE p1;
+
+
+--echo #
+--echo # Testing EXCEPTIONS in internal blocks
+--echo #
+
+--echo # No HANDLER declarations, no code, no exceptions
+DELIMITER /;
+CREATE PROCEDURE p1 (v IN OUT INT)
+IS
+BEGIN
+  v:=123;
+  BEGIN
+  END;
+END;
+/
+DELIMITER ;/
+SHOW PROCEDURE CODE p1;
+SET @v=10;
+CALL p1(@v);
+SELECT @v;
+DROP PROCEDURE p1;
+
+--echo # No HANDLER declarations, no code, some exceptions
+DELIMITER /;
+CREATE PROCEDURE p1 (v IN OUT INT)
+IS
+BEGIN
+  v:=123;
+  BEGIN
+  EXCEPTION
+    WHEN 20002 THEN v:=335;
+  END;
+END;
+/
+DELIMITER ;/
+SHOW PROCEDURE CODE p1;
+SET @v=10;
+CALL p1(@v);
+SELECT @v;
+DROP PROCEDURE p1;
+
+--echo # No HANDLER declarations, some code, no exceptions
+DELIMITER /;
+CREATE PROCEDURE p1 (v IN OUT INT)
+IS
+BEGIN
+  v:=123;
+  BEGIN
+    v:=223;
+  END;
+END;
+/
+DELIMITER ;/
+SHOW PROCEDURE CODE p1;
+SET @v=10;
+CALL p1(@v);
+SELECT @v;
+DROP PROCEDURE p1;
+
+--echo # No HANDLER declarations, some code, some exceptions
+DELIMITER /;
+CREATE PROCEDURE p1 (v IN OUT INT)
+IS
+BEGIN
+  v:=123;
+  BEGIN
+    v:=223;
+  EXCEPTION
+    WHEN 20002 THEN v:=335;
+  END;
+END;
+/
+DELIMITER ;/
+SHOW PROCEDURE CODE p1;
+SET @v=10;
+CALL p1(@v);
+SELECT @v;
+DROP PROCEDURE p1;
+
+--echo # Some HANDLER declarations, no code, no exceptions
+DELIMITER /;
+CREATE PROCEDURE p1 (v IN OUT INT)
+IS
+BEGIN
+  v:=123;
+  DECLARE
+    EXIT HANDLER FOR 1000
+    BEGIN
+      v:=323;
+    END;
+  BEGIN
+  END;
+END;
+/
+DELIMITER ;/
+SHOW PROCEDURE CODE p1;
+SET @v=10;
+CALL p1(@v);
+SELECT @v;
+DROP PROCEDURE p1;
+
+--echo # Some HANDLER declarations, no code, some exceptions
+DELIMITER /;
+CREATE PROCEDURE p1 (v IN OUT INT)
+IS
+BEGIN
+  v:=123;
+  DECLARE
+    EXIT HANDLER FOR 1000
+    BEGIN
+      v:=323;
+    END;
+  BEGIN
+  EXCEPTION
+    WHEN 20002 THEN v:=335;
+  END;
+END;
+/
+DELIMITER ;/
+SHOW PROCEDURE CODE p1;
+SET @v=10;
+CALL p1(@v);
+SELECT @v;
+DROP PROCEDURE p1;
+
+--echo # Some HANDLER declarations, some code, no exceptions
+DELIMITER /;
+CREATE PROCEDURE p1 (v IN OUT INT)
+IS
+BEGIN
+  v:=123;
+  DECLARE
+    EXIT HANDLER FOR 1000
+    BEGIN
+      v:=323;
+    END;
+  BEGIN
+   v:= 324;
+  END;
+END;
+/
+DELIMITER ;/
+SHOW PROCEDURE CODE p1;
+SET @v=10;
+CALL p1(@v);
+SELECT @v;
+DROP PROCEDURE p1;
+
+--echo # Some HANDLER declarations, some code, some exceptions
+DELIMITER /;
+CREATE PROCEDURE p1 (v IN OUT INT)
+IS
+BEGIN
+  v:=123;
+  DECLARE
+    EXIT HANDLER FOR 1000
+    BEGIN
+      v:=323;
+    END;
+  BEGIN
+    v:= 324;
+  EXCEPTION WHEN 2002 THEN v:= 325;
+  END;
+END;
+/
+DELIMITER ;/
+SHOW PROCEDURE CODE p1;
+SET @v=10;
+CALL p1(@v);
+SELECT @v;
+DROP PROCEDURE p1;
diff --git a/mysql-test/suite/compat/oracle/t/sp.test b/mysql-test/suite/compat/oracle/t/sp.test
index a838a74d2da..6b47ab4f7d6 100644
--- a/mysql-test/suite/compat/oracle/t/sp.test
+++ b/mysql-test/suite/compat/oracle/t/sp.test
@@ -497,3 +497,31 @@ CALL sp1(@v, 30001);
 CALL sp1(@v, 30002);
 SELECT @v;
 DROP PROCEDURE sp1;
+
+
+DELIMITER /;
+CREATE PROCEDURE sp1 (v IN OUT INT, error IN INT)
+IS
+BEGIN
+  BEGIN
+    BEGIN
+      SIGNAL SQLSTATE '45000' SET MYSQL_ERRNO=error, MESSAGE_TEXT='User defined error!';
+      v:= 223;
+    EXCEPTION
+      WHEN 30001 THEN
+      BEGIN
+        v:= 113;
+      END;
+    END;
+  END;
+END;
+/
+DELIMITER ;/
+SET @v=10;
+CALL sp1(@v, 30001);
+SELECT @v;
+SET @v=10;
+--error 30002
+CALL sp1(@v, 30002);
+SELECT @v;
+DROP PROCEDURE sp1;