Debian Codegraph @

mysql-test/suite/compat/oracle/t/binlog_stm_sp_package.test

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