Debian Codegraph @

mysql-test/suite/funcs_1/r/innodb_storedproc_02.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.2 - Syntax checks for the stored procedure-specific
70 programming statements BEGIN/END, DECLARE, SET, SELECT/INTO, OPEN, FETCH, CLOSE:
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 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
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.2.8:
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 scope of each BEGIN/END compound statement within a stored
86 procedure definition is properly applied
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 PROCEDURE IF EXISTS sp1;
91 CREATE PROCEDURE sp1( )
92 begin_label: BEGIN
93 declare x char DEFAULT 'x';
94 declare y char DEFAULT 'y';
95 set x = '1';
96 set y = '2';
97 label1: BEGIN
98 declare x char DEFAULT 'X';
99 declare y char DEFAULT 'Y';
100 SELECT f1, f2 into x, y from t2 limit 1;
101 SELECT '1.1', x, y;
102 label2: BEGIN
103 declare x char default 'a';
104 declare y char default 'b';
105 label3: BEGIN
106 declare x char default 'c';
107 declare y char default 'd';
108 label4: BEGIN
109 declare x char default 'e';
110 declare y char default 'f';
111 label5: BEGIN
112 declare x char default 'g';
113 declare y char default 'h';
114 SELECT 5, x, y;
115 END label5;
116 SELECT 4, x, y;
117 END label4;
118 SELECT 3, x, y;
119 END label3;
120 SELECT 2, x, y;
121 END label2;
122 END label1;
123 set @v1 = x;
124 set @v2 = y;
125 SELECT '1.2', @v1, @v2;
126 END begin_label//
127 CALL sp1();
128 1.1 x y
129 1.1 a a
130 5 x y
131 5 g h
132 4 x y
133 4 e f
134 3 x y
135 3 c d
136 2 x y
137 2 a b
138 1.2 @v1 @v2
139 1.2 1 2
140 DROP PROCEDURE IF EXISTS sp1;
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
144 Testcase 3.1.2.26:
145 ------------------
146 Warnings:
147 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.
148 Warnings:
149 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.
150
151 Ensure that the initial value of every variable declared for a stored procedure
152 is either NULL or its DEFAULT value, as appropriate.
153 --------------------------------------------------------------------------------
154 Warnings:
155 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.
156 DROP PROCEDURE IF EXISTS sp1;
157 set @v1=0;
158 set @v2=0;
159 CREATE PROCEDURE sp1( )
160 BEGIN
161 declare x1 char default 'x';
162 declare y1 char;
163 declare x2 tinytext default 'tinytext';
164 declare y2 tinytext;
165 declare x3 datetime default '2005-10-03 12:13:14';
166 declare y3 datetime;
167 declare x4 float default 1.2;
168 declare y4 float;
169 declare x5 blob default 'b';
170 declare y5 blob;
171 declare x6 smallint default 127;
172 declare y6 smallint;
173 SELECT x1, x2, x3, x4, x5, x6, y1, y2, y3, y4, y5, y6;
174 END//
175 CALL sp1();
176 x1 x2 x3 x4 x5 x6 y1 y2 y3 y4 y5 y6
177 x tinytext 2005-10-03 12:13:14 1.2 b 127 NULL NULL NULL NULL NULL NULL
178 DROP PROCEDURE sp1;
179 Warnings:
180 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.
181
182 Testcase 3.1.2.30:
183 ------------------
184 Warnings:
185 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.
186 Warnings:
187 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.
188
189 Ensure that, when a stored procedure is called/executed, every variable always
190 uses the correct value: either the value with which it is initialized or the
191 value to which it is subsequently SET or otherwise assigned, as appropriate.
192 --------------------------------------------------------------------------------
193 Warnings:
194 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.
195 DROP PROCEDURE IF EXISTS sp1;
196 CREATE PROCEDURE sp1( IN invar INT, OUT outvar INT )
197 BEGIN
198 declare x integer;
199 declare y integer default 1;
200 set @x = x;
201 set @y = y;
202 set @z = 234;
203 SELECT f1, f2 into @x, @y from t2 where f1='a`' and f2='a`' limit 1;
204 SELECT @x, @y, @z, invar;
205 BEGIN
206 set @x = 2;
207 SELECT @x, @y, @z;
208 SET outvar = @x * invar + @z * @f;
209 SET invar = outvar;
210 BEGIN
211 set @y = null, @z = 'abcd';
212 SELECT @x, @y, @z;
213 END;
214 END;
215 END//
216 SET @invar = 100;
217 SET @outvar = @invar;
218 SET @f = 10;
219 SELECT @x, @y, @z, @invar, @outvar;
220 @x @y @z @invar @outvar
221 NULL NULL NULL 100 100
222 CALL sp1( @invar, @outvar );
223 @x @y @z invar
224 a` a` 234 100
225 @x @y @z
226 2 a` 234
227 @x @y @z
228 2 NULL abcd
229 SELECT @x, @y, @z, @invar, @outvar;
230 @x @y @z @invar @outvar
231 2 NULL abcd 100 2540
232 DROP PROCEDURE sp1;
233 Warnings:
234 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.
235
236 Testcase 3.1.2.31:
237 ------------------
238 Warnings:
239 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.
240 Warnings:
241 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.
242
243 Ensure that the SELECT ... INTO statement properly assigns values to the
244 variables in its variable list.
245 --------------------------------------------------------------------------------
246 Warnings:
247 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.
248 DROP PROCEDURE IF EXISTS sp1;
249 CREATE PROCEDURE sp1( )
250 BEGIN
251 declare x integer; declare y integer;
252 set @x=x;
253 set @y=y;
254 SELECT f4, f3 into @x, @y from t2 where f4=-5000 and f3='1000-01-01' limit 1;
255 SELECT @x, @y;
256 END//
257 CALL sp1();
258 @x @y
259 -5000 1000-01-01
260 DROP PROCEDURE sp1;
261 Warnings:
262 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.
263
264 Testcase 3.1.2.32:
265 ------------------
266 Warnings:
267 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.
268 Warnings:
269 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.
270
271 Ensure that a SELECT ... INTO statement that retrieves multiple rows is
272 rejected, with an appropriate error message.
273 --------------------------------------------------------------------------------
274 Warnings:
275 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.
276 DROP PROCEDURE IF EXISTS sp1;
277 CREATE PROCEDURE sp1( )
278 BEGIN
279 declare x integer; declare y integer;
280 set @x=x;
281 set @y=y;
282 SELECT f4, f3 into @x, @y from t2;
283 END//
284 CALL sp1();
285 ERROR 42000: Result consisted of more than one row
286 DROP PROCEDURE sp1;
287 Warnings:
288 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.
289
290 Testcase 3.1.2.33:
291 ------------------
292 Warnings:
293 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.
294 Warnings:
295 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.
296
297 Ensure that a SELECT ... INTO statement that retrieves too many columns for the
298 number of variables in its variable list is rejected, with an appropriate error
299 message.
300 --------------------------------------------------------------------------------
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 DROP PROCEDURE IF EXISTS sp1;
304 CREATE PROCEDURE sp1( )
305 BEGIN
306 declare x integer; declare y integer;
307 set @x=x;
308 set @y=y;
309 SELECT f4, f3, f2, f1 into @x, @y from t2;
310 END//
311 CALL sp1();
312 ERROR 21000: The used SELECT statements have a different number of columns
313 DROP PROCEDURE sp1;
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
317 Testcase 3.1.2.34:
318 ------------------
319 Warnings:
320 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.
321 Warnings:
322 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.
323
324 Ensure that a SELECT ... INTO statement that retrieves too few columns for the
325 number of variables in its variable list is rejected, with an appropriate error
326 message.
327 --------------------------------------------------------------------------------
328 Warnings:
329 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.
330 DROP PROCEDURE IF EXISTS sp1;
331 CREATE PROCEDURE sp1( )
332 BEGIN
333 declare x integer; declare y integer; declare z integer;
334 set @x=x;
335 set @y=y;
336 set @z=z;
337 SELECT f4 into @x, @y, @z from t2;
338 END//
339 CALL sp1();
340 ERROR 21000: The used SELECT statements have a different number of columns
341 DROP PROCEDURE sp1;
342 Warnings:
343 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.
344
345 Testcase 3.1.2.38:
346 ------------------
347 Warnings:
348 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.
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 Ensure that the scope of every condition declared is properly applied.
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 DROP PROCEDURE IF EXISTS h1;
357 DROP TABLE IF EXISTS res_t1;
358 create table res_t1(w char unique, x char);
359 insert into res_t1 values('a', 'b');
360 CREATE PROCEDURE h1 ()
361 BEGIN
362 declare x1, x2, x3, x4, x5, x6 int default 0;
363 SELECT '-1-', x1, x2, x3, x4, x5, x6;
364 BEGIN
365 declare condname condition for sqlstate '23000';
366 declare continue handler for condname set x5 = 1;
367 set x6 = 0;
368 insert into res_t1 values ('a', 'b');
369 set x6 = 1;
370 SELECT '-2-', x1, x2, x3, x4, x5, x6;
371 END;
372 begin1_label: BEGIN
373 BEGIN
374 declare condname condition for sqlstate '20000';
375 declare continue handler for condname set x1 = 1;
376 set x2 = 0;
377 case x2
378 when 1 then set x2=10;
379 when 2 then set x2=11;
380 END case;
381 set x2 = 1;
382 SELECT '-3-', x1, x2, x3, x4, x5, x6;
383 begin2_label: BEGIN
384 BEGIN
385 declare condname condition for sqlstate '23000';
386 declare exit handler for condname set x3 = 1;
387 set x4= 1;
388 SELECT '-4a', x1, x2, x3, x4, x5, x6;
389 insert into res_t1 values ('a', 'b');
390 set x4= 2;
391 SELECT '-4b', x1, x2, x3, x4, x5, x6;
392 END;
393 SELECT '-5-', x1, x2, x3, x4, x5, x6;
394 END begin2_label;
395 SELECT '-6-', x1, x2, x3, x4, x5, x6;
396 END;
397 SELECT '-7-', x1, x2, x3, x4, x5, x6;
398 END begin1_label;
399 SELECT 'END', x1, x2, x3, x4, x5, x6;
400 END//
401 CALL h1();
402 -1- x1 x2 x3 x4 x5 x6
403 -1- 0 0 0 0 0 0
404 -2- x1 x2 x3 x4 x5 x6
405 -2- 0 0 0 0 1 1
406 -3- x1 x2 x3 x4 x5 x6
407 -3- 1 1 0 0 1 1
408 -4a x1 x2 x3 x4 x5 x6
409 -4a 1 1 0 1 1 1
410 -5- x1 x2 x3 x4 x5 x6
411 -5- 1 1 1 1 1 1
412 -6- x1 x2 x3 x4 x5 x6
413 -6- 1 1 1 1 1 1
414 -7- x1 x2 x3 x4 x5 x6
415 -7- 1 1 1 1 1 1
416 END x1 x2 x3 x4 x5 x6
417 END 1 1 1 1 1 1
418 DROP TABLE IF EXISTS tnull;
419 DROP PROCEDURE IF EXISTS sp1;
420 CREATE TABLE tnull(f1 int);
421 CREATE PROCEDURE sp1()
422 BEGIN
423 declare cond1 condition for sqlstate '42S02';
424 declare continue handler for cond1 set @var2 = 1;
425 BEGIN
426 declare cond1 condition for sqlstate '23000';
427 declare continue handler for cond1 set @var2 = 1;
428 END;
429 insert into tnull values(1);
430 END//
431 CALL sp1();
432 DROP PROCEDURE h1;
433 drop table res_t1;
434 DROP PROCEDURE sp1;
435 DROP TABLE tnull;
436 Warnings:
437 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.
438
439 Testcase 3.1.2.43:
440 ------------------
441 Warnings:
442 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.
443 Warnings:
444 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.
445
446 Ensure that the DECLARE ... HANDLER FOR statement can not declare any handler
447 for a condition declared outside of the scope of the handler.
448 --------------------------------------------------------------------------------
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 DROP PROCEDURE IF EXISTS h1;
452 DROP PROCEDURE IF EXISTS h2;
453 drop table IF EXISTS res_t1;
454 create table res_t1(w char unique, x char);
455 insert into res_t1 values ('a', 'b');
456 CREATE PROCEDURE h1 ()
457 BEGIN
458 declare x1, x2, x3, x4, x5, x6 int default 0;
459 BEGIN
460 declare cond_1 condition for sqlstate '23000';
461 declare continue handler for cond_1 set x5 = 1;
462 BEGIN
463 declare cond_2 condition for sqlstate '20000';
464 declare continue handler for cond_1 set x1 = 1;
465 BEGIN
466 declare continue handler for cond_2 set x3 = 1;
467 set x2 = 1;
468 END;
469 set x6 = 0;
470 END;
471 BEGIN
472 declare continue handler for cond_1 set x1 = 1;
473 BEGIN
474 declare continue handler for cond_2 set x3 = 1;
475 set x2 = 1;
476 END;
477 set x6 = 0;
478 END;
479 END;
480 SELECT x1, x2, x3, x4, x5, x6;
481 END//
482 ERROR 42000: Undefined CONDITION: cond_2
483 CREATE PROCEDURE h2 ()
484 BEGIN
485 declare x1, x2, x3, x4, x5, x6 int default 0;
486 BEGIN
487 declare condname condition for sqlstate '23000';
488 declare continue handler for condname set x5 = 1;
489 BEGIN
490 declare condname condition for sqlstate '20000';
491 declare continue handler for condname set x1 = 1;
492 BEGIN
493 declare condname condition for sqlstate '42000';
494 declare continue handler for condname set x3 = 1;
495 set x6 = 0;
496 insert into res_t1 values ('a', 'b');
497 set x6 = 1;
498 set x4= 0;
499 CALL sp1();
500 set x4= 1;
501 set x2 = 0;
502 case x2
503 when 1 then set x2=10;
504 when 2 then set x2=11;
505 END case;
506 set x2 = 1;
507 END;
508 set x2 = 0;
509 case x2
510 when 1 then set x2=10;
511 when 2 then set x2=11;
512 END case;
513 set x2 = 1;
514 set x6 = 0;
515 insert into res_t1 values ('a', 'b');
516 set x6 = 1;
517 END;
518 END;
519 SELECT x1, x2, x3, x4, x5, x6;
520 END//
521 CALL h2();
522 x1 x2 x3 x4 x5 x6
523 1 1 1 1 1 1
524 SELECT * FROM res_t1;
525 w x
526 a b
527 DROP PROCEDURE h2;
528 drop table res_t1;
529 Warnings:
530 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.
531
532 Testcase 3.1.2.44:
533 ------------------
534 Warnings:
535 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.
536 Warnings:
537 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.
538
539 Ensure that the DECLARE ... HANDLER FOR statement cannot declare a handler for
540 any invalid, or undeclared, condition.
541 --------------------------------------------------------------------------------
542 Warnings:
543 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.
544 DROP PROCEDURE IF EXISTS h1;
545 CREATE PROCEDURE h1 ()
546 BEGIN
547 declare x1, x2, x3, x4, x5, x6 int default 0;
548 BEGIN
549 declare condname1 condition for sqlstate '23000';
550 BEGIN
551 declare condname2 condition for sqlstate '20000';
552 declare continue handler for condname1 set x3 = 1;
553 declare continue handler for condname2 set x1 = 1;
554 END;
555 END;
556 BEGIN
557 declare condname3 condition for sqlstate '42000';
558 declare continue handler for condname1 set x3 = 1;
559 declare continue handler for condname2 set x5 = 1;
560 declare continue handler for condname3 set x1 = 1;
561 END;
562 END//
563 ERROR 42000: Undefined CONDITION: condname1
564 CREATE PROCEDURE h1 ()
565 BEGIN
566 DECLARE x1 INT DEFAULT 0;
567 BEGIN
568 DECLARE condname1 CONDITION CHECK SQLSTATE '23000';
569 END;
570 DECLARE CONTINUE HANDLER FOR condname1 SET x1 = 1;
571 END//
572 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHECK SQLSTATE '23000';
573 END;
574 DECLARE CONTINUE HANDLER FOR condname1 SET x1 = 1;
575 ' at line 5
576 CREATE PROCEDURE h1 ()
577 BEGIN
578 DECLARE x1 INT DEFAULT 0;
579 BEGIN
580 DECLARE condname1 CONDITION FOR SQLSTATE 'qwert';
581 END;
582 DECLARE CONTINUE HANDLER FOR condname1 SET x1 = 1;
583 END//
584 ERROR 42000: Bad SQLSTATE: 'qwert'
585 Warnings:
586 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.
587
588 Testcase 3.1.2.45 + 3.1.2.50:
589 -----------------------------
590 Warnings:
591 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.
592 Warnings:
593 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.
594
595 45. Ensure that the scope of every handler declared is properly applied.
596 50. Ensure that a CONTINUE handler allows the execution of the stored procedure
597 . to continue once the handler statement has completed its own execution (that
598 . is, once the handler action statement has been executed).
599 --------------------------------------------------------------------------------
600 Warnings:
601 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.
602 DROP PROCEDURE IF EXISTS p1;
603 DROP PROCEDURE IF EXISTS p1undo;
604 DROP PROCEDURE IF EXISTS h1;
605 DROP PROCEDURE IF EXISTS sp1;
606 drop table IF EXISTS res_t1;
607 ==> 'UNDO' is still not supported.
608 create procedure p1undo ()
609 begin
610 declare undo handler for sqlexception select '1';
611 select * from tqq;
612 SELECT 'end of 1';
613 end;//
614 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'undo handler for sqlexception select '1';
615 select * from tqq;
616 SELECT 'end of 1';
617 ' at line 3
618 create procedure p1 ()
619 begin
620 declare exit handler for sqlexception select 'exit handler 1';
621 begin
622 declare exit handler for sqlexception select 'exit handler 2';
623 begin
624 declare continue handler for sqlexception select 'continue handler 3';
625 drop table if exists tqq;
626 select * from tqq;
627 SELECT 'end of BEGIN/END 3';
628 end;
629 drop table if exists tqq;
630 select * from tqq;
631 SELECT 'end of BEGIN/END 2';
632 end;
633 select * from tqq;
634 SELECT 'end of BEGIN/END 1';
635 end;//
636 call p1()//
637 continue handler 3
638 continue handler 3
639 end of BEGIN/END 3
640 end of BEGIN/END 3
641 exit handler 2
642 exit handler 2
643 exit handler 1
644 exit handler 1
645 create table res_t1(w char unique, x char);
646 insert into res_t1 values ('a', 'b');
647 CREATE PROCEDURE h1 ()
648 BEGIN
649 declare x1, x2, x3, x4, x5, x6 int default 0;
650 BEGIN
651 declare continue handler for sqlstate '23000' set x5 = 1;
652 insert into res_t1 values ('a', 'b');
653 set x6 = 1;
654 END;
655 begin1_label: BEGIN
656 BEGIN
657 declare continue handler for sqlstate '23000' set x1 = 1;
658 insert into res_t1 values ('a', 'b');
659 set x2 = 1;
660 begin2_label: BEGIN
661 BEGIN
662 declare exit handler for sqlstate '23000' set x3 = 1;
663 set x4= 1;
664 insert into res_t1 values ('a', 'b');
665 set x4= 0;
666 END;
667 END begin2_label;
668 END;
669 END begin1_label;
670 SELECT x1, x2, x3, x4, x5, x6;
671 END//
672 CALL h1();
673 x1 x2 x3 x4 x5 x6
674 1 1 1 1 1 1
675 This will fail, SQLSTATE 00000 is not allowed
676 CREATE PROCEDURE sp1()
677 begin1_label:BEGIN
678 declare exit handler for sqlstate '00000' set @var1 = 5;
679 set @var2 = 6;
680 begin2_label:BEGIN
681 declare continue handler for sqlstate '00000' set @var3 = 7;
682 set @var4 = 8;
683 SELECT @var3, @var4;
684 END begin2_label;
685 SELECT @var1, @var2;
686 END begin1_label//
687 ERROR 42000: Bad SQLSTATE: '00000'
688 Verify SP wasn't created
689 CALL sp1();
690 ERROR 42000: PROCEDURE db_storedproc.sp1 does not exist
691 DROP PROCEDURE p1;
692 DROP PROCEDURE h1;
693 DROP PROCEDURE IF EXISTS sp1;
694 DROP TABLE res_t1;
695 Warnings:
696 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.
697
698 Testcase 3.1.2.50:
699 ------------------
700 Warnings:
701 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.
702 DROP PROCEDURE IF EXISTS sp1;
703 DROP PROCEDURE IF EXISTS sp2;
704 CREATE PROCEDURE sp1 (x int, y int)
705 BEGIN
706 set @y=0;
707 END//
708 CREATE PROCEDURE sp2 ()
709 BEGIN
710 declare continue handler for sqlstate '42000' set @x2 = 1;
711 set @x=1;
712 SELECT @x2;
713 CALL sp1(1);
714 set @x=2;
715 SELECT @x2, @x;
716 END//
717 CALL sp2();
718 @x2
719 NULL
720 @x2 @x
721 1 2
722 DROP PROCEDURE sp1;
723 DROP PROCEDURE sp2;
724 Warnings:
725 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.
726
727 Testcase 3.2.2.51:
728 ------------------
729 Warnings:
730 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.
731 Warnings:
732 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.
733
734 Ensure that an EXIT handler causes the execution of the stored procedure to
735 terminate, within its scope, once the handler action statement has been
736 executed.
737 --------------------------------------------------------------------------------
738 Warnings:
739 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.
740 DROP PROCEDURE IF EXISTS sp1;
741 DROP PROCEDURE IF EXISTS sp2;
742 CREATE PROCEDURE sp1 (x int, y int)
743 BEGIN
744 set @x=0;
745 END//
746 CREATE PROCEDURE sp2 ()
747 BEGIN
748 declare exit handler for sqlstate '42000' set @x2 = 1;
749 set @x2=0;
750 set @x=1;
751 SELECT '-1-', @x2, @x;
752 CALL sp1(1);
753 SELECT '-2-', @x2, @x;
754 set @x=2;
755 END//
756 CALL sp1(1);
757 ERROR 42000: Incorrect number of arguments for PROCEDURE db_storedproc.sp1; expected 2, got 1
758 CALL sp2();
759 -1- @x2 @x
760 -1- 0 1
761 SELECT '-3-', @x2, @x;
762 -3- @x2 @x
763 -3- 1 1
764 DROP PROCEDURE sp1;
765 DROP PROCEDURE sp2;
766 Warnings:
767 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.
768
769 Testcase 3.1.2.52:
770 ------------------
771 Warnings:
772 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.
773 Warnings:
774 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.
775
776 Ensure that an EXIT handler does not cause the execution of the stored procedure
777 to terminate outside of its scope.
778 --------------------------------------------------------------------------------
779 Warnings:
780 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.
781 DROP PROCEDURE IF EXISTS sp1;
782 DROP PROCEDURE IF EXISTS sp2;
783 CREATE PROCEDURE sp1 (x int, y int)
784 BEGIN
785 set @x=0;
786 END//
787 CREATE PROCEDURE sp2()
788 BEGIN
789 declare continue handler for sqlstate '42000' set @x2 = 2;
790 set @x2 = 1;
791 set @x =20;
792 SELECT '-1-', @x2, @x;
793 BEGIN
794 declare exit handler for sqlstate '42000' set @x2 = 11;
795 SELECT '-2-', @x2, @x;
796 CALL sp1(1);
797 SELECT '-3a', @x2, @x;
798 set @x=21;
799 SELECT '-3b', @x2, @x;
800 END;
801 set @x=22;
802 SELECT '-4-', @x2, @x;
803 END//
804 CALL sp2();
805 -1- @x2 @x
806 -1- 1 20
807 -2- @x2 @x
808 -2- 1 20
809 -4- @x2 @x
810 -4- 11 22
811 DROP PROCEDURE sp1;
812 DROP PROCEDURE sp2;
813 Warnings:
814 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.
815
816 Testcase 3.1.2.54:
817 ------------------
818 Warnings:
819 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.
820 Warnings:
821 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.
822
823 Ensure that a handler with a condition defined with an SQLSTATE that begins with
824 “01“ is always exactly equivalent in action to a handler with an SQLWARNING
825 condition.
826 --------------------------------------------------------------------------------
827 Warnings:
828 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.
829 DROP PROCEDURE IF EXISTS sp0;
830 DROP PROCEDURE IF EXISTS sp1;
831 DROP PROCEDURE IF EXISTS sp2;
832 DROP PROCEDURE IF EXISTS sp3;
833 DROP PROCEDURE IF EXISTS sp4;
834 DROP TABLE IF EXISTS temp;
835 CREATE TABLE temp( f1 CHAR, f2 CHAR);
836 CREATE PROCEDURE sp0()
837 BEGIN
838 set @done=0;
839 set @x=0;
840 insert into temp values('xxx', 'yy');
841 set @x=1;
842 END//
843 CREATE PROCEDURE sp1()
844 BEGIN
845 declare continue handler for sqlstate '01000' set @done = 1;
846 set @done=0;
847 set @x=0;
848 insert into temp values('xxx', 'yy');
849 set @x=1;
850 END//
851 CREATE PROCEDURE sp2()
852 BEGIN
853 declare continue handler for sqlwarning set @done = 1;
854 set @done=0;
855 set @x=0;
856 insert into temp values('xxx', 'yy');
857 set @x=1;
858 END//
859 CREATE PROCEDURE sp3()
860 BEGIN
861 declare exit handler for sqlstate '01000' set @done = 1;
862 set @done=0;
863 set @x=0;
864 insert into temp values('xxx', 'yy');
865 set @x=1;
866 END//
867 CREATE PROCEDURE sp4()
868 BEGIN
869 declare exit handler for sqlwarning set @done = 1;
870 set @done=0;
871 set @x=0;
872 insert into temp values('xxx', 'yy');
873 set @x=1;
874 END//
875 INSERT INTO temp VALUES('0', NULL);
876 CALL sp0();
877 SELECT @done, @x;
878 @done @x
879 0 1
880 INSERT INTO temp VALUES('1', NULL);
881 CALL sp1();
882 SELECT @done, @x;
883 @done @x
884 1 1
885 INSERT INTO temp VALUES('2', NULL);
886 CALL sp2();
887 SELECT @done, @x;
888 @done @x
889 1 1
890 INSERT INTO temp VALUES('3', NULL);
891 CALL sp3();
892 SELECT @done, @x;
893 @done @x
894 1 0
895 INSERT INTO temp VALUES('4', NULL);
896 CALL sp4();
897 SELECT @done, @x;
898 @done @x
899 1 0
900 SELECT * FROM temp;
901 f1 f2
902 0 NULL
903 x y
904 1 NULL
905 x y
906 2 NULL
907 x y
908 3 NULL
909 x y
910 4 NULL
911 x y
912 DROP PROCEDURE sp1;
913 DROP PROCEDURE sp2;
914 DROP PROCEDURE sp3;
915 DROP PROCEDURE sp4;
916 DROP TABLE temp;
917 Warnings:
918 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.
919
920 Testcase 3.1.2.56:
921 ------------------
922 Warnings:
923 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.
924 Warnings:
925 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.
926
927 Ensure that a handler with a condition defined with an SQLSTATE that begins with
928 “02“ is always exactly equivalent in action to a handler with a NOT FOUND
929 condition.
930 --------------------------------------------------------------------------------
931 Warnings:
932 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.
933 DROP PROCEDURE IF EXISTS sp0;
934 DROP PROCEDURE IF EXISTS sp1;
935 DROP PROCEDURE IF EXISTS sp2;
936 DROP PROCEDURE IF EXISTS sp3;
937 DROP PROCEDURE IF EXISTS sp4;
938 CREATE PROCEDURE sp0()
939 BEGIN
940 DECLARE f1_value CHAR(20);
941 DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
942 SET @done = 0;
943 SET @x = 0;
944 OPEN cur1;
945 FETCH cur1 INTO f1_value;
946 SET @x = 1;
947 FETCH cur1 INTO f1_value;
948 SET @x = 2;
949 CLOSE cur1;
950 END//
951 CREATE PROCEDURE sp1()
952 BEGIN
953 DECLARE f1_value CHAR(20);
954 DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
955 declare continue handler for sqlstate '02000' set @done = 1;
956 SET @done = 0;
957 SET @x = 0;
958 OPEN cur1;
959 FETCH cur1 INTO f1_value;
960 SET @x = 1;
961 FETCH cur1 INTO f1_value;
962 SET @x = 2;
963 CLOSE cur1;
964 END//
965 CREATE PROCEDURE sp2()
966 BEGIN
967 DECLARE f1_value CHAR(20);
968 DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
969 declare continue handler for not found set @done = 1;
970 SET @done = 0;
971 SET @x = 0;
972 OPEN cur1;
973 FETCH cur1 INTO f1_value;
974 SET @x = 1;
975 FETCH cur1 INTO f1_value;
976 SET @x = 2;
977 CLOSE cur1;
978 END//
979 CREATE PROCEDURE sp3()
980 BEGIN
981 DECLARE f1_value CHAR(20);
982 DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
983 declare exit handler for sqlstate '02000' set @done = 1;
984 SET @done = 0;
985 SET @x = 0;
986 OPEN cur1;
987 FETCH cur1 INTO f1_value;
988 SET @x = 1;
989 FETCH cur1 INTO f1_value;
990 SET @x = 2;
991 CLOSE cur1;
992 END//
993 CREATE PROCEDURE sp4()
994 BEGIN
995 DECLARE f1_value CHAR(20);
996 DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
997 declare exit handler for not found set @done = 1;
998 SET @done = 0;
999 SET @x = 0;
1000 OPEN cur1;
1001 FETCH cur1 INTO f1_value;
1002 SET @x = 1;
1003 FETCH cur1 INTO f1_value;
1004 SET @x = 2;
1005 CLOSE cur1;
1006 END//
1007 CALL sp0();
1008 ERROR 02000: No data - zero rows fetched, selected, or processed
1009 SELECT @done, @x;
1010 @done @x
1011 0 1
1012 CALL sp1();
1013 SELECT @done, @x;
1014 @done @x
1015 1 2
1016 CALL sp2();
1017 SELECT @done, @x;
1018 @done @x
1019 1 2
1020 CALL sp3();
1021 SELECT @done, @x;
1022 @done @x
1023 1 1
1024 CALL sp4();
1025 SELECT @done, @x;
1026 @done @x
1027 1 1
1028 DROP PROCEDURE sp0;
1029 DROP PROCEDURE sp1;
1030 DROP PROCEDURE sp2;
1031 DROP PROCEDURE sp3;
1032 DROP PROCEDURE sp4;
1033 Warnings:
1034 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.
1035
1036 Testcase 3.1.2.58:
1037 ------------------
1038 Warnings:
1039 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.
1040 Warnings:
1041 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.
1042
1043 Ensure that a handler with a condition defined with an SQLSTATE that begins with
1044 anything other that “01“ or “02“ is always exactly equivalent in action to a
1045 handler with an SQLEXCEPTION condition.
1046 --------------------------------------------------------------------------------
1047 Warnings:
1048 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.
1049 DROP PROCEDURE IF EXISTS sp0;
1050 DROP PROCEDURE IF EXISTS sp1;
1051 DROP PROCEDURE IF EXISTS sp2;
1052 DROP PROCEDURE IF EXISTS sp3;
1053 DROP PROCEDURE IF EXISTS sp4;
1054 CREATE PROCEDURE sp0()
1055 BEGIN
1056 DECLARE f1_value CHAR(20);
1057 DECLARE cv INT DEFAULT 0;
1058 DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
1059 SET @x = 1;
1060 CASE cv
1061 WHEN 2 THEN SET @x = 2;
1062 WHEN 3 THEN SET @x = 3;
1063 END case;
1064 SET @x = 4;
1065 SELECT f1, f2 FROM t2
1066 UNION
1067 SELECT f1, f2,3 FROM t2;
1068 SET @x = 5;
1069 FETCH cur1 INTO f1_value;
1070 SET @x = 6;
1071 END//
1072 CREATE PROCEDURE sp1()
1073 BEGIN
1074 DECLARE f1_value CHAR(20);
1075 DECLARE cv INT DEFAULT 0;
1076 DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
1077 DECLARE continue HANDLER FOR SQLSTATE '20000' SELECT '20000' AS 'SQLSTATE';
1078 DECLARE continue HANDLER FOR SQLSTATE '21000' SELECT '21000' AS 'SQLSTATE';
1079 DECLARE continue HANDLER FOR SQLSTATE '24000' SELECT '24000' AS 'SQLSTATE';
1080 SET @x = 1;
1081 CASE cv
1082 WHEN 2 THEN SET @x = 2;
1083 WHEN 3 THEN SET @x = 3;
1084 END case;
1085 SET @x = 4;
1086 SELECT f1, f2 FROM t2
1087 UNION
1088 SELECT f1, f2,3 FROM t2;
1089 SET @x = 5;
1090 FETCH cur1 INTO f1_value;
1091 SET @x = 6;
1092 END//
1093 CREATE PROCEDURE sp2()
1094 BEGIN
1095 DECLARE f1_value CHAR(20);
1096 DECLARE cv INT DEFAULT 0;
1097 DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
1098 DECLARE continue HANDLER FOR SQLEXCEPTION SELECT 'SQLEXCEPTION' AS 'SQLSTATE';
1099 DECLARE continue HANDLER FOR SQLSTATE '24000' SELECT '24000' AS 'SQLSTATE';
1100 SET @x = 1;
1101 CASE cv
1102 WHEN 2 THEN SET @x = 2;
1103 WHEN 3 THEN SET @x = 3;
1104 END case;
1105 SET @x = 4;
1106 SELECT f1, f2 FROM t2
1107 UNION
1108 SELECT f1, f2,3 FROM t2;
1109 SET @x = 5;
1110 FETCH cur1 INTO f1_value;
1111 SET @x = 6;
1112 END//
1113 CREATE PROCEDURE sp3()
1114 BEGIN
1115 DECLARE f1_value CHAR(20);
1116 DECLARE cv INT DEFAULT 0;
1117 DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
1118 DECLARE EXIT HANDLER FOR SQLSTATE '20000' SELECT '20000' AS 'SQLSTATE';
1119 DECLARE EXIT HANDLER FOR SQLSTATE '21000' SELECT '21000' AS 'SQLSTATE';
1120 DECLARE EXIT HANDLER FOR SQLSTATE '24000' SELECT '24000' AS 'SQLSTATE';
1121 SET @x = 1;
1122 CASE cv
1123 WHEN 2 THEN SET @x = 2;
1124 WHEN 3 THEN SET @x = 3;
1125 END case;
1126 SET @x = 4;
1127 SELECT f1, f2 FROM t2
1128 UNION
1129 SELECT f1, f2,3 FROM t2;
1130 SET @x = 5;
1131 FETCH cur1 INTO f1_value;
1132 SET @x = 6;
1133 END//
1134 CREATE PROCEDURE sp4()
1135 BEGIN
1136 DECLARE f1_value CHAR(20);
1137 DECLARE cv INT DEFAULT 0;
1138 DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
1139 DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT 'SQLEXCEPTION' AS 'SQLSTATE';
1140 DECLARE EXIT HANDLER FOR SQLSTATE '24000' SELECT '24000' AS 'SQLSTATE';
1141 SET @x = 1;
1142 CASE cv
1143 WHEN 2 THEN SET @x = 2;
1144 WHEN 3 THEN SET @x = 3;
1145 END case;
1146 SET @x = 4;
1147 SELECT f1, f2 FROM t2
1148 UNION
1149 SELECT f1, f2,3 FROM t2;
1150 SET @x = 5;
1151 FETCH cur1 INTO f1_value;
1152 SET @x = 6;
1153 CLOSE cur1;
1154 END//
1155 CALL sp0();
1156 ERROR 20000: Case not found for CASE statement
1157 SELECT '-0-', @x;
1158 -0- @x
1159 -0- 1
1160 CALL sp1();
1161 SQLSTATE
1162 20000
1163 SQLSTATE
1164 21000
1165 SQLSTATE
1166 24000
1167 SELECT '-1-', @x;
1168 -1- @x
1169 -1- 6
1170 CALL sp2();
1171 SQLSTATE
1172 SQLEXCEPTION
1173 SQLSTATE
1174 SQLEXCEPTION
1175 SQLSTATE
1176 24000
1177 SELECT '-2-', @x;
1178 -2- @x
1179 -2- 6
1180 CALL sp3();
1181 SQLSTATE
1182 20000
1183 SELECT '-3-', @x;
1184 -3- @x
1185 -3- 1
1186 CALL sp4();
1187 SQLSTATE
1188 SQLEXCEPTION
1189 SELECT '-4-', @x;
1190 -4- @x
1191 -4- 1
1192 DROP PROCEDURE sp0;
1193 DROP PROCEDURE sp1;
1194 DROP PROCEDURE sp2;
1195 DROP PROCEDURE sp3;
1196 DROP PROCEDURE sp4;
1197 Warnings:
1198 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.
1199
1200 Testcase 3.1.2.65:
1201 ------------------
1202 Warnings:
1203 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.
1204 Warnings:
1205 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.
1206
1207 Ensure that FETCH <cursor name> returns the first row of the cursor_s result set
1208 the first time FETCH is executed, that it returns each subsequent row of the
1209 cursor_s result set each of the subsequent times FETCH is executed, and that it
1210 returns a NOT FOUND warning if it is executed after the last row of the cursor_s
1211 result set has already been fetched.
1212 --------------------------------------------------------------------------------
1213 Warnings:
1214 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.
1215 DROP PROCEDURE IF EXISTS sp1;
1216 DROP TABLE IF EXISTS temp;
1217 CREATE TABLE temp(
1218 cnt INT,
1219 f1 CHAR(20),
1220 f2 CHAR(20),
1221 f3 INT,
1222 f4 CHAR(20),
1223 f5 INT);
1224 INSERT INTO temp VALUES(0, 'onip', 'abc', 8760, 'xyz', 10);
1225 CREATE PROCEDURE sp1( )
1226 BEGIN
1227 declare proceed int default 1;
1228 declare count integer default 1;
1229 declare f1_value char(20);
1230 declare f2_value char(20);
1231 declare f5_value char(20);
1232 declare f4_value integer;
1233 declare f6_value integer;
1234 declare cur1 cursor for SELECT f1, f2, f4, f5, f6 from t2
1235 where f4 >=-5000 order by f4 limit 3;
1236 open cur1;
1237 while proceed do
1238 SELECT count AS 'loop';
1239 fetch cur1 into f1_value, f2_value, f4_value, f5_value, f6_value;
1240 insert into temp values (count, f1_value, f2_value, f4_value, f5_value, f6_value);
1241 set count = count + 1;
1242 END while;
1243 END//
1244 CALL sp1();
1245 loop
1246 1
1247 loop
1248 2
1249 loop
1250 3
1251 loop
1252 4
1253 ERROR 02000: No data - zero rows fetched, selected, or processed
1254 SELECT * FROM temp;
1255 cnt f1 f2 f3 f4 f5
1256 0 onip abc 8760 xyz 10
1257 1 a` a` -5000 a` -5000
1258 2 aaa aaa -4999 aaa -4999
1259 3 abaa abaa -4998 abaa -4998
1260 DROP TABLE temp;
1261 DROP PROCEDURE sp1;
1262 Warnings:
1263 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.
1264
1265 Testcase 3.1.2.68:
1266 ------------------
1267 Warnings:
1268 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.
1269 Warnings:
1270 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.
1271
1272 Ensure that FETCH <cursor name> fails with an appropriate error message if the
1273 number of columns to be fetched does not match the number of variables specified
1274 by the FETCH statement.
1275 --------------------------------------------------------------------------------
1276 Warnings:
1277 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.
1278 DROP PROCEDURE IF EXISTS sp1;
1279 DROP PROCEDURE IF EXISTS sp2;
1280 --> not enough columns in FETCH statement
1281 CREATE PROCEDURE sp1( )
1282 BEGIN
1283 declare newf1 char(20);
1284 declare cur1 cursor for SELECT f1, f2 from t2 limit 10;
1285 declare continue handler for sqlstate '02000' SELECT 'HANDLER executed.' AS '';
1286 BEGIN
1287 open cur1;
1288 fetch cur1 into newf1;
1289 SELECT newf1;
1290 close cur1;
1291 END;
1292 END//
1293 --> too many columns in FETCH statement
1294 CREATE PROCEDURE sp2( )
1295 BEGIN
1296 declare newf1 char(20);
1297 declare newf2 char(20);
1298 declare cur1 cursor for SELECT f1 from t2 limit 10;
1299 declare continue handler for sqlstate '02000' SELECT 'HANDLER executed.' AS '';
1300 BEGIN
1301 open cur1;
1302 fetch cur1 into newf1, newf2;
1303 SELECT newf1, newf2;
1304 close cur1;
1305 END;
1306 END//
1307 --> not enough columns in FETCH statement
1308 CALL sp1();
1309 ERROR HY000: Incorrect number of FETCH variables
1310 --> too many columns in FETCH statement
1311 CALL sp2();
1312 ERROR HY000: Incorrect number of FETCH variables
1313 DROP PROCEDURE sp1;
1314 DROP PROCEDURE sp2;
1315 Warnings:
1316 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.
1317
1318 Testcase 3.1.2.75:
1319 ------------------
1320 Warnings:
1321 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.
1322 Warnings:
1323 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.
1324
1325 Ensure that, for nested compound statements, a cursor that was declared and
1326 opened during an outer level of the statement is not closed when an inner level
1327 of a compound statement ends.
1328 --------------------------------------------------------------------------------
1329 Warnings:
1330 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.
1331 DROP TABLE IF EXISTS temp1;
1332 DROP PROCEDURE IF EXISTS sp1;
1333 create table temp1( f0 char(20), f1 char(20), f2 char(20), f3 int, f4 char(20) );
1334 SELECT f1, f2, f4, f5 from t2 order by f4;
1335 f1 f2 f4 f5
1336 a` a` -5000 a`
1337 aaa aaa -4999 aaa
1338 abaa abaa -4998 abaa
1339 acaaa acaaa -4997 acaaa
1340 adaaaa adaaaa -4996 adaaaa
1341 aeaaaaa aeaaaaa -4995 aeaaaaa
1342 afaaaaaa afaaaaaa -4994 afaaaaaa
1343 agaaaaaaa agaaaaaaa -4993 agaaaaaaa
1344 a^aaaaaaaa a^aaaaaaaa -4992 a^aaaaaaaa
1345 a_aaaaaaaaa a_aaaaaaaaa -4991 a_aaaaaaaaa
1346 CREATE PROCEDURE sp1( )
1347 BEGIN
1348 declare count integer;
1349 declare from0 char(20);
1350 declare newf1 char(20);
1351 declare newf2 char(20);
1352 declare newf5 char(20);
1353 declare newf4 integer;
1354 declare cur1 cursor for SELECT f1, f2, f4, f5 from t2 where f4 >= -5000 order by f4 limit 5;
1355 declare cur2 cursor for SELECT f1, f2, f4, f5 from t2 where f4 >= -5000 order by f4 limit 5;
1356 open cur1;
1357 open cur2;
1358 BEGIN
1359 declare continue handler for sqlstate '02000' set count = 1;
1360 fetch cur1 into newf1, newf2, newf4, newf5;
1361 SELECT '-1-', count, newf1, newf2, newf4, newf5;
1362 insert into temp1 values ('cur1_out', newf1, newf2, newf4, newf5);
1363 set count = 4;
1364 BEGIN
1365 while count > 0 do
1366 fetch cur1 into newf1, newf2, newf4, newf5;
1367 SELECT '-2-', count, newf1, newf2, newf4, newf5;
1368 set count = count - 1;
1369 END while;
1370 SELECT '-3-', count, newf1, newf2, newf4, newf4;
1371 END;
1372 BEGIN
1373 fetch cur1 into newf1, newf2, newf4, newf5;
1374 SELECT '-4-', newf1, newf2, newf4, newf5;
1375 insert into temp1 values ('cur1_in', newf1, newf2, newf4, newf5);
1376 END;
1377 fetch cur2 into newf1, newf2, newf4, newf5;
1378 SELECT '-5-', newf1, newf2, newf4, newf5;
1379 insert into temp1 values ('cur2', newf1, newf2, newf4, newf5);
1380 close cur1;
1381 END;
1382 fetch cur2 into newf1, newf2, newf4, newf5;
1383 SELECT '-6-', newf1, newf2, newf4, newf5;
1384 close cur2;
1385 END//
1386 CALL sp1();
1387 -1- count newf1 newf2 newf4 newf5
1388 -1- NULL a` a` -5000 a`
1389 -2- count newf1 newf2 newf4 newf5
1390 -2- 4 aaa aaa -4999 aaa
1391 -2- count newf1 newf2 newf4 newf5
1392 -2- 3 abaa abaa -4998 abaa
1393 -2- count newf1 newf2 newf4 newf5
1394 -2- 2 acaaa acaaa -4997 acaaa
1395 -2- count newf1 newf2 newf4 newf5
1396 -2- 1 adaaaa adaaaa -4996 adaaaa
1397 -3- count newf1 newf2 newf4 newf4
1398 -3- 0 adaaaa adaaaa -4996 -4996
1399 -4- newf1 newf2 newf4 newf5
1400 -4- adaaaa adaaaa -4996 adaaaa
1401 -5- newf1 newf2 newf4 newf5
1402 -5- a` a` -5000 a`
1403 -6- newf1 newf2 newf4 newf5
1404 -6- aaa aaa -4999 aaa
1405 SELECT * from temp1;
1406 f0 f1 f2 f3 f4
1407 cur1_out a` a` -5000 a`
1408 cur1_in adaaaa adaaaa -4996 adaaaa
1409 cur2 a` a` -5000 a`
1410 DROP PROCEDURE sp1;
1411 drop table temp1;
1412 Warnings:
1413 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.
1414
1415 Testcase 3.1.2.76:
1416 ------------------
1417 Warnings:
1418 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.
1419 Warnings:
1420 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.
1421
1422 Ensure that all cursors operate asensitively, so that there is no concurrency
1423 conflict between cursors operating on the same, or similar, sets of results
1424 during execution of one or more stored procedures.
1425 --------------------------------------------------------------------------------
1426 Warnings:
1427 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.
1428 DROP PROCEDURE IF EXISTS sp1;
1429 drop table IF EXISTS temp1;
1430 drop table IF EXISTS temp2;
1431 create table temp1( f0 char(10), cnt int, f1 char(20), f2 char(20), f3 date, f4 integer );
1432 create table temp2( f0 char(10), cnt int, f1 char(20), f2 char(20), f3 date, f4 integer );
1433 CREATE PROCEDURE sp_inner( )
1434 BEGIN
1435 declare proceed int default 1;
1436 declare i_count integer default 20;
1437 declare i_newf1 char(20);
1438 declare i_newf2 char(20);
1439 declare i_newf3 date;
1440 declare i_newf4 integer;
1441 declare i_newf11 char(20);
1442 declare i_newf12 char(20);
1443 declare i_newf13 date;
1444 declare i_newf14 integer;
1445 declare cur1 cursor for SELECT f1, f2, f3, f4 from t2
1446 where f4>=-5000 order by f4 limit 4;
1447 declare cur2 cursor for SELECT f1, f2, f3, f4 from t2
1448 where f4>=-5000 order by f4 limit 3;
1449 declare continue handler for sqlstate '02000' set proceed=0;
1450 open cur1;
1451 open cur2;
1452 set i_count = 10;
1453 while proceed do
1454 fetch cur1 into i_newf1, i_newf2, i_newf3, i_newf4;
1455 IF proceed THEN
1456 insert into temp1 values ('sp_inner', i_count, i_newf1, i_newf2, i_newf3, i_newf4);
1457 fetch cur2 into i_newf11, i_newf12, i_newf13, i_newf14;
1458 IF proceed THEN
1459 insert into temp2 values ('sp_inner', i_count, i_newf11, i_newf12, i_newf13, i_newf14);
1460 END IF;
1461 END IF;
1462 set i_count = i_count - 1;
1463 END while;
1464 close cur1;
1465 close cur2;
1466 END//
1467 CREATE PROCEDURE sp_outer( )
1468 BEGIN
1469 DECLARE proceed INT DEFAULT 1;
1470 DECLARE o_count INTEGER DEFAULT 20;
1471 DECLARE o_newf1 CHAR(20);
1472 DECLARE o_newf2 CHAR(20);
1473 DECLARE o_newf3 DATE;
1474 DECLARE o_newf4 INTEGER;
1475 DECLARE o_newf11 CHAR(20);
1476 DECLARE o_newf12 CHAR(20);
1477 DECLARE o_newf13 DATE;
1478 DECLARE o_newf14 INTEGER;
1479 DECLARE cur1 CURSOR FOR SELECT f1, f2, f3, f4 FROM t2
1480 WHERE f4>=-5000 ORDER BY f4 LIMIT 5;
1481 DECLARE cur2 CURSOR FOR SELECT f1, f2, f3, f4 FROM t2
1482 WHERE f4>=-5000 ORDER BY f4 LIMIT 5;
1483 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET proceed=0;
1484 OPEN cur1;
1485 OPEN cur2;
1486 SET o_count = 1;
1487 WHILE proceed DO
1488 FETCH cur1 INTO o_newf1, o_newf2, o_newf3, o_newf4;
1489 IF proceed THEN
1490 INSERT INTO temp1 VALUES ('_sp_out_', o_count, o_newf1, o_newf2, o_newf3, o_newf4);
1491 CALL sp_inner();
1492 FETCH cur2 INTO o_newf11, o_newf12, o_newf13, o_newf14;
1493 IF proceed THEN
1494 INSERT INTO temp2 VALUES ('_sp_out_', o_count, o_newf11, o_newf12, o_newf13, o_newf14);
1495 END IF;
1496 END IF;
1497 SET o_count = o_count + 1;
1498 END WHILE;
1499 CLOSE cur1;
1500 CLOSE cur2;
1501 END//
1502 CALL sp_outer();
1503 SELECT * FROM temp1;
1504 f0 cnt f1 f2 f3 f4
1505 _sp_out_ 1 a` a` 1000-01-01 -5000
1506 sp_inner 10 a` a` 1000-01-01 -5000
1507 sp_inner 9 aaa aaa 1000-01-02 -4999
1508 sp_inner 8 abaa abaa 1000-01-03 -4998
1509 sp_inner 7 acaaa acaaa 1000-01-04 -4997
1510 _sp_out_ 2 aaa aaa 1000-01-02 -4999
1511 sp_inner 10 a` a` 1000-01-01 -5000
1512 sp_inner 9 aaa aaa 1000-01-02 -4999
1513 sp_inner 8 abaa abaa 1000-01-03 -4998
1514 sp_inner 7 acaaa acaaa 1000-01-04 -4997
1515 _sp_out_ 3 abaa abaa 1000-01-03 -4998
1516 sp_inner 10 a` a` 1000-01-01 -5000
1517 sp_inner 9 aaa aaa 1000-01-02 -4999
1518 sp_inner 8 abaa abaa 1000-01-03 -4998
1519 sp_inner 7 acaaa acaaa 1000-01-04 -4997
1520 _sp_out_ 4 acaaa acaaa 1000-01-04 -4997
1521 sp_inner 10 a` a` 1000-01-01 -5000
1522 sp_inner 9 aaa aaa 1000-01-02 -4999
1523 sp_inner 8 abaa abaa 1000-01-03 -4998
1524 sp_inner 7 acaaa acaaa 1000-01-04 -4997
1525 _sp_out_ 5 adaaaa adaaaa 1000-01-05 -4996
1526 sp_inner 10 a` a` 1000-01-01 -5000
1527 sp_inner 9 aaa aaa 1000-01-02 -4999
1528 sp_inner 8 abaa abaa 1000-01-03 -4998
1529 sp_inner 7 acaaa acaaa 1000-01-04 -4997
1530 SELECT * FROM temp2;
1531 f0 cnt f1 f2 f3 f4
1532 sp_inner 10 a` a` 1000-01-01 -5000
1533 sp_inner 9 aaa aaa 1000-01-02 -4999
1534 sp_inner 8 abaa abaa 1000-01-03 -4998
1535 _sp_out_ 1 a` a` 1000-01-01 -5000
1536 sp_inner 10 a` a` 1000-01-01 -5000
1537 sp_inner 9 aaa aaa 1000-01-02 -4999
1538 sp_inner 8 abaa abaa 1000-01-03 -4998
1539 _sp_out_ 2 aaa aaa 1000-01-02 -4999
1540 sp_inner 10 a` a` 1000-01-01 -5000
1541 sp_inner 9 aaa aaa 1000-01-02 -4999
1542 sp_inner 8 abaa abaa 1000-01-03 -4998
1543 _sp_out_ 3 abaa abaa 1000-01-03 -4998
1544 sp_inner 10 a` a` 1000-01-01 -5000
1545 sp_inner 9 aaa aaa 1000-01-02 -4999
1546 sp_inner 8 abaa abaa 1000-01-03 -4998
1547 _sp_out_ 4 acaaa acaaa 1000-01-04 -4997
1548 sp_inner 10 a` a` 1000-01-01 -5000
1549 sp_inner 9 aaa aaa 1000-01-02 -4999
1550 sp_inner 8 abaa abaa 1000-01-03 -4998
1551 _sp_out_ 5 adaaaa adaaaa 1000-01-05 -4996
1552 DROP PROCEDURE sp_outer;
1553 DROP PROCEDURE sp_inner;
1554 DROP TABLE temp1;
1555 DROP TABLE temp2;
1556 SET sql_mode = default;
1557
1558 --source suite/funcs_1/storedproc/cleanup_sp_tb.inc
1559 --------------------------------------------------------------------------------
1560 DROP DATABASE IF EXISTS db_storedproc;
1561 DROP DATABASE IF EXISTS db_storedproc_1;
1562
1563 . +++ END OF SCRIPT +++
1564 --------------------------------------------------------------------------------