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
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
#
# Basic stored PROCEDURE tests
#
#
use test;
--disable_warnings
drop table if exists t1;
--enable_warnings
create table t1 (
id char(16) not null,
data int not null
);
# Single statement, no params.
create procedure foo42()
insert into test.t1 values ("foo", 42);
# Single statement, two IN params.
create procedure bar(x char(16), y int)
insert into test.t1 values (x, y);
# Now for multiple statements...
delimiter |;
# Two statements.
create procedure two(x1 char(16), x2 char(16), y int)
begin
insert into test.t1 values (x1, y);
insert into test.t1 values (x2, y);
end|
# Simple test of local variables and SET.
create procedure locset(x char(16), y int)
begin
declare z1, z2 int;
set z1 = y;
set z2 = z1+2;
insert into test.t1 values (x, z2);
end|
# The peculiar (non-standard) mixture of variables types in SET.
create procedure mixset(x char(16), y int)
begin
declare z int;
set @z = y, z = 666, max_join_size = 100;
insert into test.t1 values (x, z);
end|
# Multiple CALL statements, one with OUT parameter.
create procedure zip(x char(16), y int)
begin
declare z int;
call zap(y, z);
call bar(x, z);
end|
# SET local variables and OUT parameter.
create procedure zap(x int, out y int)
begin
declare z int;
set z = x+1, y = z;
end|
# INOUT test
create procedure iotest(x1 char(16), x2 char(16), y int)
begin
call inc2(x2, y);
insert into test.t1 values (x1, y);
end|
create procedure inc2(x char(16), y int)
begin
call inc(y);
insert into test.t1 values (x, y);
end|
create procedure inc(inout io int)
set io = io + 1|
# Call-by-value test
# The expected result is:
# ("cbv2", 4)
# ("cbv1", 4711)
create procedure cbv1()
begin
declare y int;
set y = 3;
call cbv2(y+1, y);
insert into test.t1 values ("cbv1", y);
end|
create procedure cbv2(y1 int, inout y2 int)
begin
set y2 = 4711;
insert into test.t1 values ("cbv2", y1);
end|
# Minimal tests of the flow control construts
# Just test on 'x'...
create procedure a0(x int)
while x do
set x = x-1;
insert into test.t1 values ("a0", x);
end while|
# The same, but with a more traditional test.
create procedure a(x int)
while x > 0 do
set x = x-1;
insert into test.t1 values ("a", x);
end while|
# REPEAT
create procedure b(x int)
repeat
insert into test.t1 values (repeat("b",3), x);
set x = x-1;
until x = 0 end repeat|
# Check that repeat isn't parsed the wrong way
create procedure b2(x int)
repeat(select 1 into outfile 'b2');
insert into test.t1 values (repeat("b2",3), x);
set x = x-1;
until x = 0 end repeat|
# We don't actually want to call it.
drop procedure b2|
# Btw, this should generate an error
--error 1259
create procedure b3(x int)
repeat
select * from test.t1; # No INTO!
insert into test.t1 values (repeat("b3",3), x);
set x = x-1;
until x = 0 end repeat|
# Labelled WHILE with ITERATE (pointless really)
create procedure c(x int)
hmm: while x > 0 do
insert into test.t1 values ("c", x);
set x = x-1;
iterate hmm;
insert into test.t1 values ("x", x);
end while hmm|
# Labelled WHILE with LEAVE
create procedure d(x int)
hmm: while x > 0 do
insert into test.t1 values ("d", x);
set x = x-1;
leave hmm;
insert into test.t1 values ("x", x);
end while hmm|
# LOOP, with simple IF statement
create procedure e(x int)
foo: loop
if x = 0 then
leave foo;
end if;
insert into test.t1 values ("e", x);
set x = x-1;
end loop foo|
# A full IF statement
create procedure f(x int)
if x < 0 then
insert into test.t1 values ("f", 0);
elseif x = 0 then
insert into test.t1 values ("f", 1);
else
insert into test.t1 values ("f", 2);
end if|
# This form of CASE is really just syntactic sugar for IF-ELSEIF-...
create procedure g(x int)
case
when x < 0 then
insert into test.t1 values ("g", 0);
when x = 0 then
insert into test.t1 values ("g", 1);
else
insert into test.t1 values ("g", 2);
end case|
# The "simple CASE"
create procedure h(x int)
case x
when 0 then
insert into test.t1 values ("h0", x);
when 1 then
insert into test.t1 values ("h1", x);
else
insert into test.t1 values ("h?", x);
end case|
create procedure into_test()
begin
declare x char(16);
declare y int;
set x="aaaaa";
set y=22;
select id,data into x,y from test.t1 limit 2,1;
insert into test.t1 values (x, y);
end|
delimiter ;|
# Now, the CALL tests...
call foo42();
select * from t1;
delete from t1;
call bar("bar", 666);
select * from t1;
delete from t1;
call two("one", "two", 3);
select * from t1;
delete from t1;
call locset("locset", 19);
select * from t1;
delete from t1;
call mixset("mixset", 19);
show variables like 'max_join_size';
select id,data,@z from t1;
delete from t1;
call zip("zip", 99);
select * from t1;
delete from t1;
call iotest("io1", "io2", 1);
select * from t1;
delete from t1;
call cbv1();
select * from t1;
delete from t1;
call a0(3);
select * from t1;
delete from t1;
call a(3);
select * from t1;
delete from t1;
call b(3);
select * from t1;
delete from t1;
call c(3);
select * from t1;
delete from t1;
call d(3);
select * from t1;
delete from t1;
call e(3);
select * from t1;
delete from t1;
call f(-2);
call f(0);
call f(4);
select * from t1;
delete from t1;
call g(-42);
call g(0);
call g(1);
select * from t1;
delete from t1;
call h(0);
call h(1);
call h(17);
select * from t1;
call into_test();
select * from t1;
delete from t1;
drop procedure foo42;
drop procedure bar;
drop procedure two;
drop procedure locset;
drop procedure mixset;
drop procedure zip;
drop procedure zap;
drop procedure iotest;
drop procedure inc2;
drop procedure inc;
drop procedure cbv1;
drop procedure cbv2;
drop procedure a0;
drop procedure a;
drop procedure b;
drop procedure c;
drop procedure d;
drop procedure e;
drop procedure f;
drop procedure g;
drop procedure h;
drop procedure into_test;
drop table t1;