Debian Codegraph @

mysql-test/suite/funcs_1/r/myisam_storedproc_03.result

raw
1 SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION';
2
3 --source suite/funcs_1/storedproc/load_sp_tb.inc
4 --------------------------------------------------------------------------------
5 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
6
7 --source suite/funcs_1/storedproc/cleanup_sp_tb.inc
8 --------------------------------------------------------------------------------
9 DROP DATABASE IF EXISTS db_storedproc;
10 DROP DATABASE IF EXISTS db_storedproc_1;
11 CREATE DATABASE db_storedproc charset utf8mb4;
12 CREATE DATABASE db_storedproc_1 charset utf8mb4;
13 USE db_storedproc;
14 create table t1(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
15 engine = <engine_to_be_tested>;
16 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t1;
17 create table t2(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
18 engine = <engine_to_be_tested>;
19 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t2;
20 create table t3(f1 char(20),f2 char(20),f3 integer) engine = <engine_to_be_tested>;
21 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t3.txt' into table t3;
22 create table t4(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
23 engine = <engine_to_be_tested>;
24 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t4;
25 USE db_storedproc_1;
26 create table t6(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
27 engine = <engine_to_be_tested>;
28 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6;
29 USE db_storedproc;
30 create table t7 (f1 char(20), f2 char(25), f3 date, f4 int)
31 engine = <engine_to_be_tested>;
32 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t7;
33 Warnings:
34 Warning 1265 Data truncated for column 'f3' at row 1
35 Warning 1265 Data truncated for column 'f3' at row 2
36 Warning 1265 Data truncated for column 'f3' at row 3
37 Warning 1265 Data truncated for column 'f3' at row 4
38 Warning 1265 Data truncated for column 'f3' at row 5
39 Warning 1265 Data truncated for column 'f3' at row 6
40 Warning 1265 Data truncated for column 'f3' at row 7
41 Warning 1265 Data truncated for column 'f3' at row 8
42 Warning 1265 Data truncated for column 'f3' at row 9
43 Warning 1265 Data truncated for column 'f3' at row 10
44 create table t8 (f1 char(20), f2 char(25), f3 date, f4 int)
45 engine = <engine_to_be_tested>;
46 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t8;
47 Warnings:
48 Warning 1265 Data truncated for column 'f3' at row 1
49 Warning 1265 Data truncated for column 'f3' at row 2
50 Warning 1265 Data truncated for column 'f3' at row 3
51 Warning 1265 Data truncated for column 'f3' at row 4
52 Warning 1265 Data truncated for column 'f3' at row 5
53 Warning 1265 Data truncated for column 'f3' at row 6
54 Warning 1265 Data truncated for column 'f3' at row 7
55 Warning 1265 Data truncated for column 'f3' at row 8
56 Warning 1265 Data truncated for column 'f3' at row 9
57 Warning 1265 Data truncated for column 'f3' at row 10
58 create table t9(f1 int, f2 char(25), f3 int) engine = <engine_to_be_tested>;
59 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t9.txt' into table t9;
60 create table t10(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
61 engine = <engine_to_be_tested>;
62 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t10;
63 create table t11(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
64 engine = <engine_to_be_tested>;
65 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t11;
66 SET sql_mode = default;
67 Warnings:
68 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
69
70 Section 3.1.3 - Syntax checks for the stored procedure-specific flow
71 control statements IF, CASE, LOOP, LEAVE, ITERATE, REPEAT, WHILE:
72 --------------------------------------------------------------------------------
73 Warnings:
74 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
75 Warnings:
76 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
77
78 Testcase 3.1.3.7:
79 -----------------
80 Warnings:
81 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
82 Warnings:
83 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
84
85 Ensure that the IF statement acts correctly for all variants, including cases
86 where statements are nested.
87 --------------------------------------------------------------------------------
88 Warnings:
89 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
90 DROP TABLE IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
91 DROP PROCEDURE IF EXISTS sp9;
92 CREATE TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 CHAR(20), f2 VARCHAR(20), f3 SMALLINT);
93 CREATE PROCEDURE sp9( action char(20), subaction char(20) )
94 BEGIN
95 if action = 'action' then
96 if subaction = 'subaction' then
97 insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'subaction' , 1);
98 else
99 insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'none' , 2);
100 END if;
101 else
102 if subaction = 'subaction'
103 then
104 insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'subaction' , 3);
105 elseif subaction = 'subaction1'
106 then
107 BEGIN
108 insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values ('none', 'subaction1', 4);
109 END;
110 else
111 insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'none' , 5);
112 END if;
113 END if;
114 END//
115 CALL sp9( 'action', 'subaction' );
116 SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=1;
117 f1 f2 f3
118 action subaction 1
119 CALL sp9( 'temp', 'subaction' );
120 SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=3;
121 f1 f2 f3
122 none subaction 3
123 CALL sp9( 'temp', 'subaction1' );
124 SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=4;
125 f1 f2 f3
126 none subaction1 4
127 CALL sp9( 'action', 'temp' );
128 SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=2;
129 f1 f2 f3
130 action none 2
131 CALL sp9( 'temp', 'temp' );
132 SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=5;
133 f1 f2 f3
134 none none 5
135 DROP PROCEDURE sp9;
136 DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;
137 Warnings:
138 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
139
140 Testcase 3.1.3.8.:
141 ------------------
142 Warnings:
143 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
144 Warnings:
145 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
146
147 Ensure that the CASE statement acts correctly for all variants, including cases
148 where statements are nested.
149 --------------------------------------------------------------------------------
150 Warnings:
151 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
152 drop table IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
153 DROP PROCEDURE IF EXISTS sp10;
154 create table res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 char(20), f2 varchar(20), f3 smallint);
155 CREATE PROCEDURE sp10( action char(20), subaction char(20) )
156 BEGIN
157 case action
158 when 'action' then
159 case
160 when subaction = 'subaction_1' then
161 insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'subaction_2' , 1);
162 when subaction = 'subaction_2' then
163 insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'subaction_2' , 2);
164 else
165 insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'none' , 3);
166 END case;
167 else
168 case
169 when subaction = 'subaction_1' then
170 insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'subaction_1' , 4);
171 when subaction = 'subaction_2' then
172 insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'subaction_2' , 5);
173 else
174 insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'none' , 6);
175 END case;
176 END case;
177 END//
178 CALL sp10( 'action', 'subaction_1' );
179 SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
180 f1 f2 f3
181 action subaction_2 1
182 delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
183 CALL sp10( 'action', 'subaction_2' );
184 SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
185 f1 f2 f3
186 action subaction_2 2
187 delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
188 CALL sp10( 'temp', 'subaction_1' );
189 SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
190 f1 f2 f3
191 none subaction_1 4
192 delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
193 CALL sp10( 'temp', 'subaction_2' );
194 SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
195 f1 f2 f3
196 none subaction_2 5
197 delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
198 CALL sp10( 'action', 'temp' );
199 SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
200 f1 f2 f3
201 action none 3
202 delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
203 CALL sp10( 'temp', 'temp' );
204 SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
205 f1 f2 f3
206 none none 6
207 DROP PROCEDURE sp10;
208 DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;
209 Warnings:
210 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
211
212 Testcase 3.1.3.9 + 3.1.3.15:
213 ----------------------------
214 Warnings:
215 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
216 Warnings:
217 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
218
219 09. Ensure that the LOOP statement acts correctly for all variants, including
220 . cases where statements are nested.
221 15. Ensure that the LEAVE statement acts correctly for all variants, including
222 . cases where statements are nested.
223 --------------------------------------------------------------------------------
224 Warnings:
225 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
226 DROP TABLE IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
227 DROP PROCEDURE IF EXISTS sp11;
228 CREATE TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 CHAR(20), f2 VARCHAR(20), f3 SMALLINT);
229 CREATE PROCEDURE sp11( )
230 BEGIN
231 declare count1 integer default 1;
232 declare count2 integer default 1;
233 label1: loop
234 if count2 > 3 then leave label1;
235 END if;
236 set count1 = 1;
237 label2: loop
238 if count1 > 4 then leave label2;
239 END if;
240 insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'xyz' , 'pqr', count1);
241 set count1 = count1 + 1;
242 iterate label2;
243 END loop label2;
244 set count2 = count2 + 1;
245 iterate label1;
246 END loop label1;
247 END//
248 CALL sp11();
249 SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
250 f1 f2 f3
251 xyz pqr 1
252 xyz pqr 2
253 xyz pqr 3
254 xyz pqr 4
255 xyz pqr 1
256 xyz pqr 2
257 xyz pqr 3
258 xyz pqr 4
259 xyz pqr 1
260 xyz pqr 2
261 xyz pqr 3
262 xyz pqr 4
263 DROP PROCEDURE sp11;
264 DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;
265 Warnings:
266 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
267
268 Testcase 3.1.3.16:
269 ------------------
270 Warnings:
271 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
272 Warnings:
273 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
274
275 Ensure that the ITERATE statement acts correctly for all variants, including
276 cases where statements are nested.
277 (tests for this testcase are also included in other testcases)
278 --------------------------------------------------------------------------------
279 Warnings:
280 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
281 DROP PROCEDURE IF EXISTS sp31316;
282 CREATE PROCEDURE sp31316( )
283 BEGIN
284 declare count1 integer default 1;
285 declare count2 integer default 1;
286 label1: loop
287 if count2 > 3 then leave label1;
288 END if;
289 set count1 = 1;
290 label2: loop
291 if count1 > 4 then leave label2;
292 END if;
293 insert into temp values( count1, count2);
294 set count1 = count1 + 1;
295 iterate label3;
296 END loop label2;
297 set count2 = count2 + 1;
298 iterate label1;
299 END loop label1;
300 END//
301 ERROR 42000: ITERATE with no matching label: label3
302 Warnings:
303 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
304
305 Testcase 3.1.3.18:
306 ------------------
307 Warnings:
308 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
309 Warnings:
310 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
311
312 Ensure that the REPEAT statement acts correctly for all variants, including
313 cases where statements are nested.
314 --------------------------------------------------------------------------------
315 Warnings:
316 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
317 DROP PROCEDURE IF EXISTS sp17;
318 DROP TABLE IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
319 CREATE TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 CHAR(20), f2 VARCHAR(20), f3 SMALLINT);
320 CREATE PROCEDURE sp17( )
321 BEGIN
322 declare count1 integer default 1;
323 declare count2 integer default 1;
324 repeat
325 set count1 = count1 + 1;
326 set count2 = 1;
327 label1: repeat
328 set count2 = count2 + 1;
329 insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'xyz' , 'pqr', count1);
330 until count2 > 3
331 END repeat label1;
332 until count1 > 3
333 END repeat;
334 END//
335 CALL sp17();
336 SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
337 f1 f2 f3
338 xyz pqr 2
339 xyz pqr 2
340 xyz pqr 2
341 xyz pqr 3
342 xyz pqr 3
343 xyz pqr 3
344 xyz pqr 4
345 xyz pqr 4
346 xyz pqr 4
347 DROP PROCEDURE sp17;
348 DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;
349 Warnings:
350 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
351
352 Testcase 3.1.3.24:
353 ------------------
354 Warnings:
355 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
356 Warnings:
357 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
358
359 Ensure that the WHILE statement acts correctly for all variants, including cases
360 where statements are nested.
361 --------------------------------------------------------------------------------
362 Warnings:
363 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
364 drop table IF EXISTS res_t21;
365 DROP PROCEDURE IF EXISTS sp21;
366 create table res_t21(name text(10), surname blob(20), age_averylongfieldname_averylongname_1234569 smallint);
367 insert into res_t21 values('ashwin', 'mokadam', 25);
368 CREATE PROCEDURE sp21( )
369 BEGIN
370 declare count1 integer default 0;
371 declare count2 integer default 0;
372 while count1 < 3 do
373 BEGIN
374 declare ithisissamevariablename int default 100;
375 SELECT ithisissamevariablename;
376 BEGIN
377 declare ithisissamevariablename int default 200;
378 SELECT ithisissamevariablename;
379 END;
380 set count2 = 0;
381 label1: while count2 < 3 do
382 BEGIN
383 declare count1 integer default 7;
384 set count2 = count2 + 1;
385 insert into res_t21 values( 'xyz' , 'pqr', count2);
386 label2: while count1 < 10 do
387 set count1 = count1 + 1;
388 insert into res_t21 values( 'xyz' , 'pqr', count1);
389 END while label2;
390 END;
391 END while label1;
392 set count1 = count1 + 1;
393 END;
394 END while;
395 END//
396 CALL sp21();
397 ithisissamevariablename
398 100
399 ithisissamevariablename
400 200
401 ithisissamevariablename
402 100
403 ithisissamevariablename
404 200
405 ithisissamevariablename
406 100
407 ithisissamevariablename
408 200
409 SELECT * from res_t21;
410 name surname age_averylongfieldname_averylongname_1234569
411 ashwin mokadam 25
412 xyz pqr 1
413 xyz pqr 8
414 xyz pqr 9
415 xyz pqr 10
416 xyz pqr 2
417 xyz pqr 8
418 xyz pqr 9
419 xyz pqr 10
420 xyz pqr 3
421 xyz pqr 8
422 xyz pqr 9
423 xyz pqr 10
424 xyz pqr 1
425 xyz pqr 8
426 xyz pqr 9
427 xyz pqr 10
428 xyz pqr 2
429 xyz pqr 8
430 xyz pqr 9
431 xyz pqr 10
432 xyz pqr 3
433 xyz pqr 8
434 xyz pqr 9
435 xyz pqr 10
436 xyz pqr 1
437 xyz pqr 8
438 xyz pqr 9
439 xyz pqr 10
440 xyz pqr 2
441 xyz pqr 8
442 xyz pqr 9
443 xyz pqr 10
444 xyz pqr 3
445 xyz pqr 8
446 xyz pqr 9
447 xyz pqr 10
448 DROP PROCEDURE sp21;
449 drop table res_t21;
450 Warnings:
451 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
452
453 Testcase 3.1.3.30:
454 ------------------
455 Warnings:
456 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
457 Warnings:
458 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
459
460 Ensure that multiple cases of all possible combinations of the control flow
461 statements, nested within multiple compound statements within a stored
462 procedure, always act correctly and return the expected result.
463 --------------------------------------------------------------------------------
464 Warnings:
465 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
466 DROP TABLE IF EXISTS res_tbl;
467 DROP PROCEDURE IF EXISTS sp31330;
468 create table res_tbl (f1 int, f2 text, f3 blob, f4 date,
469 f5 set('one', 'two', 'three', 'four', 'five') default 'one');
470 CREATE PROCEDURE sp31330 (path int)
471 BEGIN
472 declare count int default 1;
473 declare var1 text;
474 declare var2 blob;
475 declare var3 date;
476 declare var4 set('one', 'two', 'three', 'four', 'five') DEFAULT 'five';
477 case
478 when path=1 then
479 set var3 = '2000-11-09';
480 set var1 = 'flowing through case 1';
481 label1: loop
482 if count > 5 then
483 if var4=1000 then
484 set var2 = 'exiting out of case 1 - invalid SET';
485 END if;
486 if var4='two' then
487 set var2 = 'exiting out of case 1';
488 END if;
489 insert into res_tbl values (1, var1, var2, var3, (count-2));
490 leave label1;
491 elseif count = 5 then
492 set count= count + 2;
493 set var4='two';
494 iterate label1;
495 else
496 set count= count + 1;
497 END if;
498 set var4='one';
499 END loop label1;
500 when path=2 then
501 set var3 = '1989-11-09';
502 set var1 = 'flowing through case 2';
503 set @count3=0;
504 label2: repeat
505 set count=count + 1;
506 set @count2=1;
507 while @count2 <= 5 do
508 set @count2 = @count2 + 1;
509 END while;
510 SELECT @count2;
511 set @count3=@count3 + @count2;
512 until count > 5
513 END repeat label2;
514 set var2 = 'exiting out of case 2';
515 set var4 = count-3;
516 SELECT @count3;
517 insert into res_tbl values (2, var1, var2, var3, var4);
518 ELSE BEGIN
519 set @error_opt='undefined path specified';
520 SELECT @error_opt;
521 END;
522 END case;
523 END//
524 CALL sp31330();
525 ERROR 42000: Incorrect number of arguments for PROCEDURE db_storedproc.sp31330; expected 1, got 0
526 CALL sp31330(1);
527 SELECT * from res_tbl;
528 f1 f2 f3 f4 f5
529 1 flowing through case 1 exiting out of case 1 2000-11-09 one,three
530 CALL sp31330(2);
531 @count2
532 6
533 @count2
534 6
535 @count2
536 6
537 @count2
538 6
539 @count2
540 6
541 @count3
542 30
543 SELECT * from res_tbl;
544 f1 f2 f3 f4 f5
545 1 flowing through case 1 exiting out of case 1 2000-11-09 one,three
546 2 flowing through case 2 exiting out of case 2 1989-11-09 one,two
547 CALL sp31330(4);
548 @error_opt
549 undefined path specified
550 DROP PROCEDURE sp31330;
551 drop table res_tbl;
552
553 --source suite/funcs_1/storedproc/cleanup_sp_tb.inc
554 --------------------------------------------------------------------------------
555 DROP DATABASE IF EXISTS db_storedproc;
556 DROP DATABASE IF EXISTS db_storedproc_1;
557
558 . +++ END OF SCRIPT +++
559 --------------------------------------------------------------------------------