SET sql_mode=ORACLE;
CREATE PACKAGE pkg1 AS
PROCEDURE p1;
FUNCTION f1 RETURN INT;
PROCEDURE p2show;
PROCEDURE p2public;
FUNCTION f2public RETURN TEXT;
END;
$$
CREATE PACKAGE BODY pkg1 AS
a INT:=10;
PROCEDURE p1 AS
b INT:=20;
BEGIN
b:=a;
b:=a+1;
a:=b;
a:=b+1;
a:=a+1;
SET @a:=@a+2;
SELECT f1() FROM DUAL;
END;
FUNCTION f1 RETURN INT AS
BEGIN
RETURN a;
END;
PROCEDURE p2private AS
BEGIN
SELECT 'This is p2private';
END;
PROCEDURE p2public AS
BEGIN
SELECT 'This is p2public';
END;
FUNCTION f2private RETURN TEXT AS
BEGIN
RETURN 'This is f2private';
END;
FUNCTION f2public RETURN TEXT AS
BEGIN
RETURN 'This is f2public';
END;
PROCEDURE p2show AS
BEGIN
SHOW FUNCTION CODE f2public;
SHOW FUNCTION CODE f2private;
SHOW PROCEDURE CODE p2public;
SHOW PROCEDURE CODE p2private;
SHOW PROCEDURE CODE p2show;
END;
BEGIN
a:=a+1;
DECLARE
b INT;
BEGIN
b:=a;
b:=a+1;
a:=b;
a:=b+1;
END;
END;
$$
SHOW PROCEDURE CODE pkg1.p1;
Pos	Instruction
0	set b@0 20
1	set b@0 PACKAGE_BODY.a@0
2	set b@0 PACKAGE_BODY.a@0 + 1
3	set PACKAGE_BODY.a@0 b@0
4	set PACKAGE_BODY.a@0 b@0 + 1
5	set PACKAGE_BODY.a@0 PACKAGE_BODY.a@0 + 1
6	stmt 31 "SET @a:=@a+2"
7	stmt 0 "SELECT f1() FROM DUAL"
SHOW FUNCTION CODE pkg1.f1;
Pos	Instruction
0	freturn int PACKAGE_BODY.a@0
SHOW PACKAGE BODY CODE pkg1;
Pos	Instruction
0	set a@0 10
1	set a@0 a@0 + 1
2	set b@1 NULL
3	set b@1 a@0
4	set b@1 a@0 + 1
5	set a@0 b@1
6	set a@0 b@1 + 1
7	jump 11
CALL pkg1.p2show;
Pos	Instruction
0	freturn blob 'This is f2public'
Pos	Instruction
0	freturn blob 'This is f2private'
Pos	Instruction
0	stmt 0 "SELECT 'This is p2public'"
Pos	Instruction
0	stmt 0 "SELECT 'This is p2private'"
Pos	Instruction
0	stmt 110 "SHOW FUNCTION CODE f2public"
1	stmt 110 "SHOW FUNCTION CODE f2private"
2	stmt 109 "SHOW PROCEDURE CODE p2public"
3	stmt 109 "SHOW PROCEDURE CODE p2private"
4	stmt 109 "SHOW PROCEDURE CODE p2show"
DROP PACKAGE pkg1;
CREATE TABLE t1 (a INT);
CREATE PACKAGE pkg1 AS
PROCEDURE p1;
END;
$$
CREATE PACKAGE BODY pkg1 AS
a t1.a%TYPE:=10;
PROCEDURE p1 AS
b t1.a%TYPE:=20;
BEGIN
b:=a;
b:=a+1;
b:=b+1;
a:=b;
a:=b+1;
a:=a+1;
END;
BEGIN
a:=a+1;
DECLARE
b t1.a%TYPE;
BEGIN
b:=a;
b:=a+1;
a:=b;
a:=b+1;
END;
END;
$$
SHOW PROCEDURE CODE pkg1.p1;
Pos	Instruction
0	set b@0 20
1	set b@0 PACKAGE_BODY.a@0
2	set b@0 PACKAGE_BODY.a@0 + 1
3	set b@0 b@0 + 1
4	set PACKAGE_BODY.a@0 b@0
5	set PACKAGE_BODY.a@0 b@0 + 1
6	set PACKAGE_BODY.a@0 PACKAGE_BODY.a@0 + 1
SHOW PACKAGE BODY CODE pkg1;
Pos	Instruction
0	set a@0 10
1	set a@0 a@0 + 1
2	set b@1 NULL
3	set b@1 a@0
4	set b@1 a@0 + 1
5	set a@0 b@1
6	set a@0 b@1 + 1
7	jump 11
DROP PACKAGE pkg1;
DROP TABLE t1;
CREATE PACKAGE pkg1 AS
PROCEDURE p1;
END;
$$
CREATE PACKAGE BODY pkg1 AS
a ROW(a INT,b TEXT):=ROW(10,'x10');
PROCEDURE p1 AS
b ROW(a INT,b TEXT):=ROW(20,'x20');
BEGIN
b:=a;
a:=b;
b.a:=a.a+1;
a.a:=b.a+1;
a.a:=a.a+1;
END;
BEGIN
a.a:=a.a+1;
DECLARE
b ROW(a INT,b TEXT):=ROW(30,'x30');
BEGIN
b:=a;
b.a:=a.a+1;
a:=b;
a.a:=b.a+1;
END;
END;
$$
SHOW PROCEDURE CODE pkg1.p1;
Pos	Instruction
0	set b@0 (20,'x20')
1	set b@0 PACKAGE_BODY.a@0
2	set PACKAGE_BODY.a@0 b@0
3	set b.a@0[0] PACKAGE_BODY.a.a@0[0] + 1
4	set PACKAGE_BODY.a.a@0[0] b.a@0[0] + 1
5	set PACKAGE_BODY.a.a@0[0] PACKAGE_BODY.a.a@0[0] + 1
SHOW PACKAGE BODY CODE pkg1;
Pos	Instruction
0	set a@0 (10,'x10')
1	set a.a@0[0] a.a@0[0] + 1
2	set b@1 (30,'x30')
3	set b@1 a@0
4	set b.a@1[0] a.a@0[0] + 1
5	set a@0 b@1
6	set a.a@0[0] b.a@1[0] + 1
7	jump 11
DROP PACKAGE pkg1;
CREATE TABLE t1 (a INT, b TEXT);
CREATE PACKAGE pkg1 AS
PROCEDURE p1;
END;
$$
CREATE PACKAGE BODY pkg1 AS
a t1%ROWTYPE:=ROW(10,'x10');
PROCEDURE p1 AS
b t1%ROWTYPE:=ROW(20,'x20');
BEGIN
b:=a;
a:=b;
b.a:=a.a+1;
a.a:=b.a+1;
a.a:=a.a+1;
END;
BEGIN
a.a:=a.a+1;
DECLARE
b t1%ROWTYPE:=ROW(30,'x30');
BEGIN
b:=a;
b.a:=a.a+1;
a:=b;
a.a:=b.a+1;
END;
END;
$$
SHOW PROCEDURE CODE pkg1.p1;
Pos	Instruction
0	set b@0 (20,'x20')
1	set b@0 PACKAGE_BODY.a@0
2	set PACKAGE_BODY.a@0 b@0
3	set b.a@0["a"] PACKAGE_BODY.a.a@0["a"] + 1
4	set PACKAGE_BODY.a.a@0["a"] b.a@0["a"] + 1
5	set PACKAGE_BODY.a.a@0["a"] PACKAGE_BODY.a.a@0["a"] + 1
SHOW PACKAGE BODY CODE pkg1;
Pos	Instruction
0	set a@0 (10,'x10')
1	set a.a@0["a"] a.a@0["a"] + 1
2	set b@1 (30,'x30')
3	set b@1 a@0
4	set b.a@1["a"] a.a@0["a"] + 1
5	set a@0 b@1
6	set a.a@0["a"] b.a@1["a"] + 1
7	jump 11
DROP PACKAGE pkg1;
DROP TABLE t1;
# Start of 11.4 tests
#
# MDEV-36047 Package body variables are not allowed as FETCH targets
#
CREATE PACKAGE pkg AS
FUNCTION f1 RETURN INT;
END;
$$
CREATE PACKAGE BODY pkg AS
vc INT := 0;
FUNCTION f1 RETURN INT AS
CURSOR cur IS SELECT 1 AS c FROM DUAL;
BEGIN
OPEN cur;
FETCH cur INTO vc; -- SHOW CODE should display vc with a "PACKAGE_BODY" prefix
CLOSE cur;
RETURN vc;
END;
BEGIN
DECLARE
CURSOR cur IS SELECT 1 AS c FROM DUAL;
BEGIN
OPEN cur;
FETCH cur INTO vc; -- SHOW CODE should display vc without a prefix
CLOSE cur;
END;
END;
$$
SELECT pkg.f1() FROM DUAL;
pkg.f1()
1
SHOW FUNCTION CODE pkg.f1;
Pos	Instruction
0	cpush cur@0
1	copen cur@0
2	cfetch cur@0 PACKAGE_BODY.vc@0
3	cclose cur@0
4	freturn int PACKAGE_BODY.vc@0
SHOW PACKAGE BODY CODE pkg;
Pos	Instruction
0	set vc@0 0
1	cpush cur@0
2	copen cur@0
3	cfetch cur@0 vc@0
4	cclose cur@0
5	cpop 1
DROP PACKAGE pkg;
CREATE PACKAGE pkg AS
FUNCTION f1 RETURN TEXT;
END;
$$
CREATE PACKAGE BODY pkg AS
vc1 INT := 0;
FUNCTION f1 RETURN TEXT AS
CURSOR cur IS SELECT 1 AS c1, 2 AS c2 FROM DUAL;
vc2 INT := 0;
BEGIN
OPEN cur;
FETCH cur INTO vc1, vc2;
CLOSE cur;
RETURN vc1 || ' ' || vc2;
END;
END;
$$
SELECT pkg.f1() FROM DUAL;
pkg.f1()
1 2
SHOW FUNCTION CODE pkg.f1;
Pos	Instruction
0	set vc2@0 0
1	cpush cur@0
2	copen cur@0
3	cfetch cur@0 PACKAGE_BODY.vc1@0 vc2@0
4	cclose cur@0
5	freturn blob concat(concat(PACKAGE_BODY.vc1@0,' '),vc2@0)
DROP PACKAGE pkg;
CREATE PACKAGE pkg AS
FUNCTION f1 RETURN TEXT;
END;
$$
CREATE PACKAGE BODY pkg AS
vc ROW(p1 INT, p2 INT);
FUNCTION f1 RETURN TEXT AS
CURSOR cur IS SELECT 1 AS c1, 2 AS c2 FROM DUAL;
BEGIN
OPEN cur;
FETCH cur INTO vc;
CLOSE cur;
RETURN vc.p1 || ' ' || vc.p2;
END;
END;
$$
SELECT pkg.f1() FROM DUAL;
pkg.f1()
1 2
SHOW FUNCTION CODE pkg.f1;
Pos	Instruction
0	cpush cur@0
1	copen cur@0
2	cfetch cur@0 PACKAGE_BODY.vc@0
3	cclose cur@0
4	freturn blob concat(concat(PACKAGE_BODY.vc.p1@0[0],' '),PACKAGE_BODY.vc.p2@0[1])
DROP PACKAGE pkg;
# End of 11.4 tests