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