1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
|
--source include/not_embedded.inc
--source include/have_binlog_format_statement.inc
--disable_query_log
reset master; # get rid of previous tests binlog
--enable_query_log
SET sql_mode=ORACLE;
DELIMITER $$;
CREATE PACKAGE p1 AS
PROCEDURE p1;
FUNCTION f1 RETURN INT;
END;
$$
CREATE PACKAGE IF NOT EXISTS p1 AS
PROCEDURE p1;
FUNCTION f1 RETURN INT;
END;
$$
CREATE PACKAGE BODY p1 AS
PROCEDURE p1 AS
BEGIN
NULL;
END;
FUNCTION f1 RETURN INT AS
BEGIN
RETURN 10;
END;
END;
$$
DELIMITER ;$$
DROP PACKAGE BODY p1;
DROP PACKAGE p1;
DROP PACKAGE IF EXISTS p1;
--echo #
--echo # Creating a package with a COMMENT clause
--echo #
DELIMITER $$;
CREATE PACKAGE p1 COMMENT 'package-p1-comment' AS
PROCEDURE p1;
END;
$$
CREATE PACKAGE BODY p1 COMMENT 'package-body-p1-comment' AS
PROCEDURE p1 AS
BEGIN
NULL;
END;
END;
$$
DELIMITER ;$$
DROP PACKAGE p1;
--echo #
--echo # Creating a package with a different DEFINER
--echo #
DELIMITER $$;
CREATE DEFINER=xxx@localhost PACKAGE p1 AS
PROCEDURE p1;
END;
$$
CREATE DEFINER=xxx@localhost PACKAGE BODY p1 AS
PROCEDURE p1 AS
BEGIN
NULL;
END;
END;
$$
DELIMITER ;$$
DROP PACKAGE p1;
--echo #
--echo # Creating a package with a different DEFINER, with SQL SECURITY INVOKER
--echo #
DELIMITER $$;
CREATE DEFINER=xxx@localhost PACKAGE p1 SQL SECURITY INVOKER AS
PROCEDURE p1;
END;
$$
CREATE DEFINER=xxx@localhost PACKAGE BODY p1 SQL SECURITY INVOKER AS
PROCEDURE p1 AS
BEGIN
NULL;
END;
END;
$$
DELIMITER ;$$
DROP PACKAGE p1;
--echo #
--echo # Creating a new package in a remote database
--echo #
CREATE DATABASE test2;
DELIMITER $$;
CREATE PACKAGE test2.test2 COMMENT 'package-test2-comment' AS
FUNCTION f1 RETURN INT;
PROCEDURE p1;
END
$$
DELIMITER ;$$
DELIMITER $$;
CREATE PACKAGE BODY test2.test2 COMMENT 'package-body-test2-comment' AS
FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
PROCEDURE p1 AS BEGIN SELECT f1(); END;
END;
$$
DELIMITER ;$$
DROP PACKAGE BODY test2.test2;
DROP PACKAGE test2.test2;
DROP DATABASE test2;
--echo #
--echo # MDEV-13139 Package-wide variables in CREATE PACKAGE
--echo #
CREATE TABLE t1 (a INT);
DELIMITER $$;
CREATE PACKAGE p1 AS
PROCEDURE p1;
END;
$$
CREATE PACKAGE BODY p1 AS
a INT:=0;
PROCEDURE p1 AS
BEGIN
INSERT INTO t1 VALUES (a);
a:=a+1;
END;
BEGIN
a:=10;
END;
$$
DELIMITER ;$$
CALL p1.p1();
CALL p1.p1();
SELECT * FROM t1;
--source sp-cache-invalidate.inc
CALL p1.p1();
CALL p1.p1();
SELECT * FROM t1;
DROP PACKAGE p1;
DROP TABLE t1;
--let $binlog_file = LAST
source include/show_binlog_events.inc;
|
