Debian Codegraph @

mysql-test/suite/compat/oracle/t/sp-package-code.test

raw
1 -- source include/have_debug.inc
2
3 SET sql_mode=ORACLE;
4
5
6 DELIMITER $$;
7 CREATE PACKAGE pkg1 AS
8 PROCEDURE p1;
9 FUNCTION f1 RETURN INT;
10 PROCEDURE p2show;
11 PROCEDURE p2public;
12 FUNCTION f2public RETURN TEXT;
13 END;
14 $$
15 CREATE PACKAGE BODY pkg1 AS
16 a INT:=10;
17 PROCEDURE p1 AS
18 b INT:=20;
19 BEGIN
20 b:=a;
21 b:=a+1;
22 a:=b;
23 a:=b+1;
24 a:=a+1;
25 SET @a:=@a+2;
26 SELECT f1() FROM DUAL;
27 END;
28 FUNCTION f1 RETURN INT AS
29 BEGIN
30 RETURN a;
31 END;
32 PROCEDURE p2private AS
33 BEGIN
34 SELECT 'This is p2private';
35 END;
36 PROCEDURE p2public AS
37 BEGIN
38 SELECT 'This is p2public';
39 END;
40 FUNCTION f2private RETURN TEXT AS
41 BEGIN
42 RETURN 'This is f2private';
43 END;
44 FUNCTION f2public RETURN TEXT AS
45 BEGIN
46 RETURN 'This is f2public';
47 END;
48 PROCEDURE p2show AS
49 BEGIN
50 SHOW FUNCTION CODE f2public;
51 SHOW FUNCTION CODE f2private;
52 SHOW PROCEDURE CODE p2public;
53 SHOW PROCEDURE CODE p2private;
54 SHOW PROCEDURE CODE p2show;
55 END;
56 BEGIN
57 a:=a+1;
58 DECLARE
59 b INT;
60 BEGIN
61 b:=a;
62 b:=a+1;
63 a:=b;
64 a:=b+1;
65 END;
66 END;
67 $$
68 DELIMITER ;$$
69
70 SHOW PROCEDURE CODE pkg1.p1;
71 SHOW FUNCTION CODE pkg1.f1;
72 SHOW PACKAGE BODY CODE pkg1;
73 CALL pkg1.p2show;
74
75 DROP PACKAGE pkg1;
76
77
78 CREATE TABLE t1 (a INT);
79 DELIMITER $$;
80 CREATE PACKAGE pkg1 AS
81 PROCEDURE p1;
82 END;
83 $$
84 CREATE PACKAGE BODY pkg1 AS
85 a t1.a%TYPE:=10;
86 PROCEDURE p1 AS
87 b t1.a%TYPE:=20;
88 BEGIN
89 b:=a;
90 b:=a+1;
91 b:=b+1;
92 a:=b;
93 a:=b+1;
94 a:=a+1;
95 END;
96 BEGIN
97 a:=a+1;
98 DECLARE
99 b t1.a%TYPE;
100 BEGIN
101 b:=a;
102 b:=a+1;
103 a:=b;
104 a:=b+1;
105 END;
106 END;
107 $$
108 DELIMITER ;$$
109 SHOW PROCEDURE CODE pkg1.p1;
110 SHOW PACKAGE BODY CODE pkg1;
111 DROP PACKAGE pkg1;
112 DROP TABLE t1;
113
114
115 DELIMITER $$;
116 CREATE PACKAGE pkg1 AS
117 PROCEDURE p1;
118 END;
119 $$
120 CREATE PACKAGE BODY pkg1 AS
121 a ROW(a INT,b TEXT):=ROW(10,'x10');
122 PROCEDURE p1 AS
123 b ROW(a INT,b TEXT):=ROW(20,'x20');
124 BEGIN
125 b:=a;
126 a:=b;
127 b.a:=a.a+1;
128 a.a:=b.a+1;
129 a.a:=a.a+1;
130 END;
131 BEGIN
132 a.a:=a.a+1;
133 DECLARE
134 b ROW(a INT,b TEXT):=ROW(30,'x30');
135 BEGIN
136 b:=a;
137 b.a:=a.a+1;
138 a:=b;
139 a.a:=b.a+1;
140 END;
141 END;
142 $$
143 DELIMITER ;$$
144 SHOW PROCEDURE CODE pkg1.p1;
145 SHOW PACKAGE BODY CODE pkg1;
146 DROP PACKAGE pkg1;
147
148
149 CREATE TABLE t1 (a INT, b TEXT);
150 DELIMITER $$;
151 CREATE PACKAGE pkg1 AS
152 PROCEDURE p1;
153 END;
154 $$
155 CREATE PACKAGE BODY pkg1 AS
156 a t1%ROWTYPE:=ROW(10,'x10');
157 PROCEDURE p1 AS
158 b t1%ROWTYPE:=ROW(20,'x20');
159 BEGIN
160 b:=a;
161 a:=b;
162 b.a:=a.a+1;
163 a.a:=b.a+1;
164 a.a:=a.a+1;
165 END;
166 BEGIN
167 a.a:=a.a+1;
168 DECLARE
169 b t1%ROWTYPE:=ROW(30,'x30');
170 BEGIN
171 b:=a;
172 b.a:=a.a+1;
173 a:=b;
174 a.a:=b.a+1;
175 END;
176 END;
177 $$
178 DELIMITER ;$$
179 SHOW PROCEDURE CODE pkg1.p1;
180 SHOW PACKAGE BODY CODE pkg1;
181 DROP PACKAGE pkg1;
182 DROP TABLE t1;
183
184 --echo #
185 --echo # MDEV-38451 `SHOW FUNCTION CODE package_func` crashes on the second execution
186 --echo #
187
188 DELIMITER $$;
189 CREATE PACKAGE pkg1 AS
190 PROCEDURE p1();
191 END;
192 $$
193 CREATE PACKAGE BODY pkg1 AS
194 FUNCTION f1() RETURN INT AS
195 BEGIN
196 RETURN 10;
197 END;
198 PROCEDURE p1() AS
199 BEGIN
200 SHOW FUNCTION CODE f1;
201 END;
202 END;
203 $$
204 DELIMITER ;$$
205 CALL pkg1.p1;
206 CALL pkg1.p1;
207 CALL pkg1.p1;
208 DROP PACKAGE pkg1;
209
210 --echo # End of 10.11 tests
211
212 --echo #
213 --echo # MDEV-36047 Package body variables are not allowed as FETCH targets
214 --echo #
215
216 --source sp-cursor-pkg-01.inc
217 SELECT pkg.f1() FROM DUAL;
218 SHOW FUNCTION CODE pkg.f1;
219 SHOW PACKAGE BODY CODE pkg;
220 DROP PACKAGE pkg;
221
222 --source sp-cursor-pkg-02.inc
223 SELECT pkg.f1() FROM DUAL;
224 SHOW FUNCTION CODE pkg.f1;
225 DROP PACKAGE pkg;
226
227 --source sp-cursor-pkg-03.inc
228 SELECT pkg.f1() FROM DUAL;
229 SHOW FUNCTION CODE pkg.f1;
230 DROP PACKAGE pkg;
231
232 --echo # End of 11.4 tests