MDEV-10411 Providing compatibility for basic PL/SQL constructs

Part 9: EXCEPTION handlers

EXCEPTION is now supported in inner blocks.

1 files changed, 176 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;