summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/suite/compat/oracle/r/sp-code.result221
-rw-r--r--mysql-test/suite/compat/oracle/r/sp.result28
-rw-r--r--mysql-test/suite/compat/oracle/t/sp-code.test176
-rw-r--r--mysql-test/suite/compat/oracle/t/sp.test28
4 files changed, 453 insertions, 0 deletions
diff --git a/mysql-test/suite/compat/oracle/r/sp-code.result b/mysql-test/suite/compat/oracle/r/sp-code.result
index 42e0fb41aaa..8e6d6d940e4 100644
--- a/mysql-test/suite/compat/oracle/r/sp-code.result
+++ b/mysql-test/suite/compat/oracle/r/sp-code.result
@@ -1,4 +1,7 @@
SET sql_mode=ORACLE;
+#
+# Testing exceptions in the top-level blocks
+#
# No HANDLER declarations, no exceptions
CREATE FUNCTION f1 RETURN INT
AS
@@ -175,3 +178,221 @@ Pos Instruction
11 jump 6
12 hpop 3
DROP PROCEDURE p1;
+#
+# Testing EXCEPTIONS in internal blocks
+#
+# No HANDLER declarations, no code, no exceptions
+CREATE PROCEDURE p1 (v IN OUT INT)
+IS
+BEGIN
+v:=123;
+BEGIN
+END;
+END;
+/
+SHOW PROCEDURE CODE p1;
+Pos Instruction
+0 set v@0 123
+1 jump 5
+SET @v=10;
+CALL p1(@v);
+SELECT @v;
+@v
+123
+DROP PROCEDURE p1;
+# No HANDLER declarations, no code, some exceptions
+CREATE PROCEDURE p1 (v IN OUT INT)
+IS
+BEGIN
+v:=123;
+BEGIN
+EXCEPTION
+WHEN 20002 THEN v:=335;
+END;
+END;
+/
+SHOW PROCEDURE CODE p1;
+Pos Instruction
+0 set v@0 123
+1 jump 2
+2 hpush_jump 5 1 EXIT
+3 set v@0 335
+4 hreturn 0 5
+5 hpop 1
+SET @v=10;
+CALL p1(@v);
+SELECT @v;
+@v
+123
+DROP PROCEDURE p1;
+# No HANDLER declarations, some code, no exceptions
+CREATE PROCEDURE p1 (v IN OUT INT)
+IS
+BEGIN
+v:=123;
+BEGIN
+v:=223;
+END;
+END;
+/
+SHOW PROCEDURE CODE p1;
+Pos Instruction
+0 set v@0 123
+1 set v@0 223
+2 jump 6
+SET @v=10;
+CALL p1(@v);
+SELECT @v;
+@v
+223
+DROP PROCEDURE p1;
+# No HANDLER declarations, some code, some exceptions
+CREATE PROCEDURE p1 (v IN OUT INT)
+IS
+BEGIN
+v:=123;
+BEGIN
+v:=223;
+EXCEPTION
+WHEN 20002 THEN v:=335;
+END;
+END;
+/
+SHOW PROCEDURE CODE p1;
+Pos Instruction
+0 set v@0 123
+1 jump 4
+2 set v@0 223
+3 jump 7
+4 hpush_jump 2 1 EXIT
+5 set v@0 335
+6 hreturn 0 7
+7 hpop 1
+SET @v=10;
+CALL p1(@v);
+SELECT @v;
+@v
+223
+DROP PROCEDURE p1;
+# Some HANDLER declarations, no code, no exceptions
+CREATE PROCEDURE p1 (v IN OUT INT)
+IS
+BEGIN
+v:=123;
+DECLARE
+EXIT HANDLER FOR 1000
+BEGIN
+v:=323;
+END;
+BEGIN
+END;
+END;
+/
+SHOW PROCEDURE CODE p1;
+Pos Instruction
+0 set v@0 123
+1 hpush_jump 4 1 EXIT
+2 set v@0 323
+3 hreturn 0 4
+4 hpop 1
+SET @v=10;
+CALL p1(@v);
+SELECT @v;
+@v
+123
+DROP PROCEDURE p1;
+# Some HANDLER declarations, no code, some exceptions
+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;
+/
+SHOW PROCEDURE CODE p1;
+Pos Instruction
+0 set v@0 123
+1 hpush_jump 4 1 EXIT
+2 set v@0 323
+3 hreturn 0 7
+4 hpush_jump 7 1 EXIT
+5 set v@0 335
+6 hreturn 0 7
+7 hpop 2
+SET @v=10;
+CALL p1(@v);
+SELECT @v;
+@v
+123
+DROP PROCEDURE p1;
+# Some HANDLER declarations, some code, no exceptions
+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;
+/
+SHOW PROCEDURE CODE p1;
+Pos Instruction
+0 set v@0 123
+1 hpush_jump 4 1 EXIT
+2 set v@0 323
+3 hreturn 0 5
+4 set v@0 324
+5 hpop 1
+SET @v=10;
+CALL p1(@v);
+SELECT @v;
+@v
+324
+DROP PROCEDURE p1;
+# Some HANDLER declarations, some code, some exceptions
+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;
+/
+SHOW PROCEDURE CODE p1;
+Pos Instruction
+0 set v@0 123
+1 hpush_jump 6 1 EXIT
+2 set v@0 323
+3 hreturn 0 9
+4 set v@0 324
+5 jump 9
+6 hpush_jump 4 1 EXIT
+7 set v@0 325
+8 hreturn 0 9
+9 hpop 2
+SET @v=10;
+CALL p1(@v);
+SELECT @v;
+@v
+324
+DROP PROCEDURE p1;
diff --git a/mysql-test/suite/compat/oracle/r/sp.result b/mysql-test/suite/compat/oracle/r/sp.result
index 42416b6ca33..d0fb26b04ae 100644
--- a/mysql-test/suite/compat/oracle/r/sp.result
+++ b/mysql-test/suite/compat/oracle/r/sp.result
@@ -465,3 +465,31 @@ SELECT @v;
@v
113
DROP PROCEDURE sp1;
+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;
+/
+SET @v=10;
+CALL sp1(@v, 30001);
+SELECT @v;
+@v
+113
+SET @v=10;
+CALL sp1(@v, 30002);
+ERROR 45000: User defined error!
+SELECT @v;
+@v
+10
+DROP PROCEDURE sp1;
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;
View Lorry Controller Status