MDEV-10411 Providing compatibility for basic PL/SQL constructs

Part 7: variable declarations

1 files changed, 98 insertions, 0 deletions

diff --git a/mysql-test/suite/compat/oracle/r/sp.result b/mysql-test/suite/compat/oracle/r/sp.result
index aa52cda9937..53ca49c8ef1 100644
--- a/mysql-test/suite/compat/oracle/r/sp.result
+++ b/mysql-test/suite/compat/oracle/r/sp.result
@@ -252,3 +252,101 @@ CREATE TABLE begin (begin INT);
 DROP TABLE begin;
 CREATE TABLE end (end INT);
 DROP TABLE end;
+# Testing top-level declarations
+CREATE PROCEDURE p1 (p1 OUT VARCHAR2(10))
+AS
+p2 VARCHAR(10);
+BEGIN
+p2:='p1new';
+p1:=p2;
+END;
+/
+SET @p1='p1';
+CALL p1(@p1);
+SELECT @p1;
+@p1
+p1new
+DROP PROCEDURE p1;
+CREATE FUNCTION f1 (p1 VARCHAR2(10)) RETURNS VARCHAR(20)
+AS
+p2 VARCHAR(10);
+BEGIN
+p2:='new';
+RETURN CONCAT(p1, p2);
+END;
+/
+SET @p1='p1';
+SELECT f1(@p1);
+f1(@p1)
+p1new
+DROP FUNCTION f1;
+# Testing non-top declarations
+CREATE PROCEDURE p1 (p1 OUT VARCHAR2(10))
+AS
+BEGIN
+DECLARE
+p2 VARCHAR(10);
+BEGIN
+p2:='p1new';
+p1:=p2;
+END;
+DECLARE
+t1 VARCHAR(10);
+t2 VARCHAR(10);
+BEGIN
+END;
+END;
+/
+SET @p1='p1';
+CALL p1(@p1);
+SELECT @p1;
+@p1
+p1new
+DROP PROCEDURE p1;
+CREATE FUNCTION f1 (p1 VARCHAR2(10)) RETURNS VARCHAR(20)
+AS
+BEGIN
+DECLARE
+p2 VARCHAR(10);
+BEGIN
+p2:='new';
+RETURN CONCAT(p1, p2);
+END;
+DECLARE
+t1 VARCHAR(10);
+t2 VARCHAR(10);
+BEGIN
+END;
+END;
+/
+SET @p1='p1';
+SELECT f1(@p1);
+f1(@p1)
+p1new
+DROP FUNCTION f1;
+# Testing BEGIN NOT ATOMIC with no declarations
+BEGIN NOT ATOMIC
+SELECT 1 AS a;
+END
+/
+a
+1
+# Testing BEGIN NOT ATOMIC with declarations
+# DECLARE starts a new block and thus must be followed by BEGIN .. END
+BEGIN NOT ATOMIC
+DECLARE
+i INT DEFAULT 5;
+x INT DEFAULT 10;
+BEGIN
+<<label>>
+WHILE i > 3 DO
+i:= i - 1;
+SELECT i;
+END WHILE label;
+END;
+END
+/
+i
+4
+i
+3