Debian Codegraph @

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

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