mirror of
https://github.com/MariaDB/server.git
synced 2026-02-16 07:38:43 +01:00
377 lines
6.5 KiB
Text
377 lines
6.5 KiB
Text
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;
|
|
#
|
|
# MDEV-38451 `SHOW FUNCTION CODE package_func` crashes on the second execution
|
|
#
|
|
CREATE PACKAGE pkg1 AS
|
|
PROCEDURE p1();
|
|
END;
|
|
$$
|
|
CREATE PACKAGE BODY pkg1 AS
|
|
FUNCTION f1() RETURN INT AS
|
|
BEGIN
|
|
RETURN 10;
|
|
END;
|
|
PROCEDURE p1() AS
|
|
BEGIN
|
|
SHOW FUNCTION CODE f1;
|
|
END;
|
|
END;
|
|
$$
|
|
CALL pkg1.p1;
|
|
Pos Instruction
|
|
0 freturn int 10
|
|
CALL pkg1.p1;
|
|
Pos Instruction
|
|
0 freturn int 10
|
|
CALL pkg1.p1;
|
|
Pos Instruction
|
|
0 freturn int 10
|
|
DROP PACKAGE pkg1;
|
|
# End of 10.11 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
|