Debian Codegraph @

mysql-test/suite/funcs_1/storedproc/storedproc_02.inc

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