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