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# ============================================================================== 196let $message= Section 3.1.2 - Syntax checks for the stored procedure-specific 197programming statements BEGIN/END, DECLARE, SET, SELECT/INTO, OPEN, FETCH, CLOSE:; 198--source include/show_msg80.inc 199 200 201# ------------------------------------------------------------------------------ 202let $message= Testcase 3.1.2.8:; 203--source include/show_msg.inc 204let $message= 205Ensure that the scope of each BEGIN/END compound statement within a stored 206procedure definition is properly applied; 207--source include/show_msg80.inc 208 209--disable_warnings 210DROP PROCEDURE IF EXISTS sp1; 211--enable_warnings 212 213delimiter //; 214SET STATEMENT sql_mode = '' FOR 215CREATE PROCEDURE sp1( ) 216begin_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; 250END begin_label// 251delimiter ;// 252 253CALL sp1(); 254 255#cleanup 256DROP PROCEDURE IF EXISTS sp1; 257 258 259# ------------------------------------------------------------------------------ 260let $message= Testcase 3.1.2.26:; 261--source include/show_msg.inc 262let $message= 263Ensure that the initial value of every variable declared for a stored procedure 264is either NULL or its DEFAULT value, as appropriate.; 265--source include/show_msg80.inc 266 267--disable_warnings 268DROP PROCEDURE IF EXISTS sp1; 269--enable_warnings 270 271set @v1=0; 272set @v2=0; 273 274delimiter //; 275CREATE PROCEDURE sp1( ) 276BEGIN 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; 290END// 291delimiter ;// 292 293CALL sp1(); 294 295# cleanup 296DROP PROCEDURE sp1; 297 298 299# ------------------------------------------------------------------------------ 300let $message= Testcase 3.1.2.30:; 301--source include/show_msg.inc 302let $message= 303Ensure that, when a stored procedure is called/executed, every variable always 304uses the correct value: either the value with which it is initialized or the 305value to which it is subsequently SET or otherwise assigned, as appropriate.; 306--source include/show_msg80.inc 307 308--disable_warnings 309DROP PROCEDURE IF EXISTS sp1; 310--enable_warnings 311 312delimiter //; 313CREATE PROCEDURE sp1( IN invar INT, OUT outvar INT ) 314BEGIN 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; 332END// 333delimiter ;// 334 335SET @invar = 100; 336SET @outvar = @invar; 337SET @f = 10; 338 339SELECT @x, @y, @z, @invar, @outvar; 340 341CALL sp1( @invar, @outvar ); 342 343SELECT @x, @y, @z, @invar, @outvar; 344 345# cleanup 346DROP PROCEDURE sp1; 347 348 349# ------------------------------------------------------------------------------ 350let $message= Testcase 3.1.2.31:; 351--source include/show_msg.inc 352let $message= 353Ensure that the SELECT ... INTO statement properly assigns values to the 354variables in its variable list.; 355--source include/show_msg80.inc 356# also tested in a lot of other testcases 357 358--disable_warnings 359DROP PROCEDURE IF EXISTS sp1; 360--enable_warnings 361 362delimiter //; 363CREATE PROCEDURE sp1( ) 364BEGIN 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; 370END// 371delimiter ;// 372 373CALL sp1(); 374 375# cleanup 3.1.2.31 376DROP PROCEDURE sp1; 377 378 379# ------------------------------------------------------------------------------ 380let $message= Testcase 3.1.2.32:; 381--source include/show_msg.inc 382let $message= 383Ensure that a SELECT ... INTO statement that retrieves multiple rows is 384rejected, with an appropriate error message.; 385--source include/show_msg80.inc 386 387--disable_warnings 388DROP PROCEDURE IF EXISTS sp1; 389--enable_warnings 390 391delimiter //; 392CREATE PROCEDURE sp1( ) 393BEGIN 394 declare x integer; declare y integer; 395 set @x=x; 396 set @y=y; 397 SELECT f4, f3 into @x, @y from t2; 398END// 399delimiter ;// 400 401# Error: SQLSTATE: 42000 (ER_TOO_MANY_ROWS) 402# Message: Result consisted of more than one row 403--error ER_TOO_MANY_ROWS 404CALL sp1(); 405 406# cleanup 3.1.2.32 407DROP PROCEDURE sp1; 408 409 410# ------------------------------------------------------------------------------ 411let $message= Testcase 3.1.2.33:; 412--source include/show_msg.inc 413let $message= 414Ensure that a SELECT ... INTO statement that retrieves too many columns for the 415number of variables in its variable list is rejected, with an appropriate error 416message.; 417--source include/show_msg80.inc 418 419--disable_warnings 420DROP PROCEDURE IF EXISTS sp1; 421--enable_warnings 422 423delimiter //; 424CREATE PROCEDURE sp1( ) 425BEGIN 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; 430END// 431delimiter ;// 432 433--error ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT 434CALL sp1(); 435 436# cleanup 3.1.2.33 437DROP PROCEDURE sp1; 438 439 440# ------------------------------------------------------------------------------ 441let $message= Testcase 3.1.2.34:; 442--source include/show_msg.inc 443let $message= 444Ensure that a SELECT ... INTO statement that retrieves too few columns for the 445number of variables in its variable list is rejected, with an appropriate error 446message.; 447--source include/show_msg80.inc 448 449--disable_warnings 450DROP PROCEDURE IF EXISTS sp1; 451--enable_warnings 452 453delimiter //; 454CREATE PROCEDURE sp1( ) 455BEGIN 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; 461END// 462delimiter ;// 463 464--error ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT 465CALL sp1(); 466 467# cleanup 3.1.2.34 468DROP PROCEDURE sp1; 469 470 471# ------------------------------------------------------------------------------ 472let $message= Testcase 3.1.2.38:; 473--source include/show_msg.inc 474let $message= 475Ensure that the scope of every condition declared is properly applied.; 476--source include/show_msg80.inc 477 478--disable_warnings 479DROP PROCEDURE IF EXISTS h1; 480DROP TABLE IF EXISTS res_t1; 481--enable_warnings 482 483create table res_t1(w char unique, x char); 484 485insert 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 492delimiter //; 493CREATE PROCEDURE h1 () 494BEGIN 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; 533END// 534delimiter ;// 535 536CALL h1(); 537 538# and a 2nd test 539--disable_warnings 540DROP TABLE IF EXISTS tnull; 541DROP PROCEDURE IF EXISTS sp1; 542--enable_warnings 543 544CREATE TABLE tnull(f1 int); 545 546delimiter //; 547CREATE PROCEDURE sp1() 548BEGIN 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); 556END// 557delimiter ;// 558 559CALL sp1(); 560 561# cleanup 3.1.2.38 562DROP PROCEDURE h1; 563drop table res_t1; 564DROP PROCEDURE sp1; 565DROP TABLE tnull; 566 567 568# ------------------------------------------------------------------------------ 569let $message= Testcase 3.1.2.43:; 570--source include/show_msg.inc 571let $message= 572Ensure that the DECLARE ... HANDLER FOR statement can not declare any handler 573for a condition declared outside of the scope of the handler.; 574--source include/show_msg80.inc 575 576--disable_warnings 577DROP PROCEDURE IF EXISTS h1; 578DROP PROCEDURE IF EXISTS h2; 579drop table IF EXISTS res_t1; 580--enable_warnings 581 582create table res_t1(w char unique, x char); 583insert into res_t1 values ('a', 'b'); 584 585delimiter //; 586--error ER_SP_COND_MISMATCH 587CREATE PROCEDURE h1 () 588BEGIN 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; 612END// 613 614CREATE PROCEDURE h2 () 615BEGIN 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; 651END// 652delimiter ;// 653 654CALL h2(); 655SELECT * FROM res_t1; 656 657# cleanup 3.1.2.43 658DROP PROCEDURE h2; 659drop table res_t1; 660 661 662# ------------------------------------------------------------------------------ 663let $message= Testcase 3.1.2.44:; 664--source include/show_msg.inc 665let $message= 666Ensure that the DECLARE ... HANDLER FOR statement cannot declare a handler for 667any invalid, or undeclared, condition.; 668--source include/show_msg80.inc 669 670--disable_warnings 671DROP PROCEDURE IF EXISTS h1; 672--enable_warnings 673 674delimiter //; 675# Error: SQLSTATE: 42000 (ER_SP_COND_MISMATCH) 676# Message: Undefined CONDITION: %s 677--error ER_SP_COND_MISMATCH 678CREATE PROCEDURE h1 () 679BEGIN 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; 695END// 696 697# Error: SQLSTATE: 42000 (ER_PARSE_ERROR) 698# Message: %s near '%s' at line %d 699--error ER_PARSE_ERROR 700CREATE PROCEDURE h1 () 701BEGIN 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; 707END// 708 709# Error: SQLSTATE: 42000 (ER_SP_BAD_SQLSTATE) 710# Message: Bad SQLSTATE: '%s' 711--error ER_SP_BAD_SQLSTATE 712CREATE PROCEDURE h1 () 713BEGIN 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; 719END// 720delimiter ;// 721 722# cleanup 3.1.2.44 723#DROP PROCEDURE h1; 724 725 726# ------------------------------------------------------------------------------ 727let $message= Testcase 3.1.2.45 + 3.1.2.50:; 728--source include/show_msg.inc 729let $message= 73045. Ensure that the scope of every handler declared is properly applied. 73150. 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 740DROP PROCEDURE IF EXISTS p1; 741DROP PROCEDURE IF EXISTS p1undo; 742DROP PROCEDURE IF EXISTS h1; 743DROP PROCEDURE IF EXISTS sp1; 744drop table IF EXISTS res_t1; 745--enable_warnings 746 747--echo ==> 'UNDO' is still not supported. 748delimiter //; 749--error ER_PARSE_ERROR 750create procedure p1undo () 751begin 752 declare undo handler for sqlexception select '1'; 753 select * from tqq; 754 SELECT 'end of 1'; 755end;// 756 757create procedure p1 () 758begin 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'; 774end;// 775 776call p1()// 777delimiter ;// 778 779create table res_t1(w char unique, x char); 780insert into res_t1 values ('a', 'b'); 781 782delimiter //; 783CREATE PROCEDURE h1 () 784BEGIN 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; 807END// 808delimiter ;// 809 810CALL h1(); 811 812--echo This will fail, SQLSTATE 00000 is not allowed 813--ERROR ER_SP_BAD_SQLSTATE 814delimiter //; 815CREATE 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// 826delimiter ;// 827 828--echo Verify SP wasn't created 829--ERROR ER_SP_DOES_NOT_EXIST 830CALL sp1(); 831 832# cleanup 3.1.2.45+50 833DROP PROCEDURE p1; 834DROP PROCEDURE h1; 835--disable_warnings 836DROP PROCEDURE IF EXISTS sp1; 837--enable_warnings 838DROP TABLE res_t1; 839 840 841# ------------------------------------------------------------------------------ 842let $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 851DROP PROCEDURE IF EXISTS sp1; 852DROP PROCEDURE IF EXISTS sp2; 853--enable_warnings 854 855delimiter //; 856CREATE PROCEDURE sp1 (x int, y int) 857BEGIN 858 set @y=0; 859END// 860delimiter ;// 861 862delimiter //; 863CREATE PROCEDURE sp2 () 864BEGIN 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; 871END// 872delimiter ;// 873 874CALL sp2(); 875 876# cleanup 877DROP PROCEDURE sp1; 878DROP PROCEDURE sp2; 879 880 881# ------------------------------------------------------------------------------ 882let $message= Testcase 3.2.2.51:; 883--source include/show_msg.inc 884let $message= 885Ensure that an EXIT handler causes the execution of the stored procedure to 886terminate, within its scope, once the handler action statement has been 887executed.; 888--source include/show_msg80.inc 889# also tested in 3.1.2.45 890 891--disable_warnings 892DROP PROCEDURE IF EXISTS sp1; 893DROP PROCEDURE IF EXISTS sp2; 894--enable_warnings 895 896delimiter //; 897CREATE PROCEDURE sp1 (x int, y int) 898BEGIN 899 set @x=0; 900END// 901delimiter ;// 902 903delimiter //; 904CREATE PROCEDURE sp2 () 905BEGIN 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; 913END// 914delimiter ;// 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 919CALL sp1(1); 920CALL sp2(); 921SELECT '-3-', @x2, @x; 922 923# cleanup 3.1.2.51 924DROP PROCEDURE sp1; 925DROP PROCEDURE sp2; 926 927 928# ------------------------------------------------------------------------------ 929let $message= Testcase 3.1.2.52:; 930--source include/show_msg.inc 931let $message= 932Ensure that an EXIT handler does not cause the execution of the stored procedure 933to terminate outside of its scope.; 934--source include/show_msg80.inc 935# tested also above in 936 937--disable_warnings 938DROP PROCEDURE IF EXISTS sp1; 939DROP PROCEDURE IF EXISTS sp2; 940--enable_warnings 941 942delimiter //; 943CREATE PROCEDURE sp1 (x int, y int) 944BEGIN 945 set @x=0; 946END// 947delimiter ;// 948 949delimiter //; 950CREATE PROCEDURE sp2() 951BEGIN 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; 966END// 967delimiter ;// 968 969CALL sp2(); 970 971# cleanup 3.1.2.52 972DROP PROCEDURE sp1; 973DROP PROCEDURE sp2; 974 975 976# ------------------------------------------------------------------------------ 977let $message= Testcase 3.1.2.54:; 978--source include/show_msg.inc 979let $message= 980Ensure 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 982condition.; 983--source include/show_msg80.inc 984 985--disable_warnings 986DROP PROCEDURE IF EXISTS sp0; 987DROP PROCEDURE IF EXISTS sp1; 988DROP PROCEDURE IF EXISTS sp2; 989DROP PROCEDURE IF EXISTS sp3; 990DROP PROCEDURE IF EXISTS sp4; 991DROP TABLE IF EXISTS temp; 992--enable_warnings 993 994CREATE TABLE temp( f1 CHAR, f2 CHAR); 995 996delimiter //; 997# 0 - without handler 998SET STATEMENT sql_mode = '' FOR 999CREATE PROCEDURE sp0() 1000BEGIN 1001 set @done=0; 1002 set @x=0; 1003 insert into temp values('xxx', 'yy'); 1004 set @x=1; 1005END// 1006 1007# 1st one with SQLSTATE + CONTINUE 1008SET STATEMENT sql_mode = '' FOR 1009CREATE PROCEDURE sp1() 1010BEGIN 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; 1016END// 1017 1018# 2nd one with SQLWARNING + CONTINUE 1019SET STATEMENT sql_mode = '' FOR 1020CREATE PROCEDURE sp2() 1021BEGIN 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; 1027END// 1028 1029# 3 with SQLSTATE + EXIT 1030SET STATEMENT sql_mode = '' FOR 1031CREATE PROCEDURE sp3() 1032BEGIN 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; 1038END// 1039 1040# 4 with SQLWARNING + EXIT 1041SET STATEMENT sql_mode = '' FOR 1042CREATE PROCEDURE sp4() 1043BEGIN 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; 1049END// 1050delimiter ;// 1051 1052INSERT INTO temp VALUES('0', NULL); 1053CALL sp0(); 1054SELECT @done, @x; 1055 1056INSERT INTO temp VALUES('1', NULL); 1057CALL sp1(); 1058SELECT @done, @x; 1059 1060INSERT INTO temp VALUES('2', NULL); 1061CALL sp2(); 1062SELECT @done, @x; 1063 1064INSERT INTO temp VALUES('3', NULL); 1065CALL sp3(); 1066SELECT @done, @x; 1067 1068INSERT INTO temp VALUES('4', NULL); 1069CALL sp4(); 1070SELECT @done, @x; 1071 1072SELECT * FROM temp; 1073 1074# cleanup 3.1.2.54 1075DROP PROCEDURE sp1; 1076DROP PROCEDURE sp2; 1077DROP PROCEDURE sp3; 1078DROP PROCEDURE sp4; 1079DROP TABLE temp; 1080 1081 1082# ------------------------------------------------------------------------------ 1083let $message= Testcase 3.1.2.56:; 1084--source include/show_msg.inc 1085let $message= 1086Ensure 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 1088condition.; 1089--source include/show_msg80.inc 1090 1091--disable_warnings 1092DROP PROCEDURE IF EXISTS sp0; 1093DROP PROCEDURE IF EXISTS sp1; 1094DROP PROCEDURE IF EXISTS sp2; 1095DROP PROCEDURE IF EXISTS sp3; 1096DROP PROCEDURE IF EXISTS sp4; 1097--enable_warnings 1098 1099delimiter //; 1100# 0 - wihtout handler 1101CREATE PROCEDURE sp0() 1102BEGIN 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; 1113END// 1114 1115# 1st one with SQLSTATE + CONTINUE 1116CREATE PROCEDURE sp1() 1117BEGIN 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; 1129END// 1130 1131# 2nd one with NOT FOUND + CONTINUE 1132CREATE PROCEDURE sp2() 1133BEGIN 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; 1145END// 1146 1147# 3 with SQLSTATE + EXIT 1148CREATE PROCEDURE sp3() 1149BEGIN 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; 1161END// 1162 1163# 4 with NOT FOUND + EXIT 1164CREATE PROCEDURE sp4() 1165BEGIN 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; 1177END// 1178delimiter ;// 1179 1180--error ER_SP_FETCH_NO_DATA 1181CALL sp0(); 1182SELECT @done, @x; 1183 1184CALL sp1(); 1185SELECT @done, @x; 1186 1187CALL sp2(); 1188SELECT @done, @x; 1189 1190CALL sp3(); 1191SELECT @done, @x; 1192 1193CALL sp4(); 1194SELECT @done, @x; 1195 1196# cleanup 3.1.2.56 1197DROP PROCEDURE sp0; 1198DROP PROCEDURE sp1; 1199DROP PROCEDURE sp2; 1200DROP PROCEDURE sp3; 1201DROP PROCEDURE sp4; 1202 1203 1204# ------------------------------------------------------------------------------ 1205let $message= Testcase 3.1.2.58:; 1206--source include/show_msg.inc 1207let $message= 1208Ensure that a handler with a condition defined with an SQLSTATE that begins with 1209anything other that “01“ or “02“ is always exactly equivalent in action to a 1210handler 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 1221DROP PROCEDURE IF EXISTS sp0; 1222DROP PROCEDURE IF EXISTS sp1; 1223DROP PROCEDURE IF EXISTS sp2; 1224DROP PROCEDURE IF EXISTS sp3; 1225DROP PROCEDURE IF EXISTS sp4; 1226--enable_warnings 1227 1228delimiter //; 1229# 0 - without handler 1230CREATE PROCEDURE sp0() 1231BEGIN 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; 1247END// 1248 1249# 1 - SQLSTATEs - CONTINUE 1250CREATE PROCEDURE sp1() 1251BEGIN 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; 1270END// 1271 1272# 2 - SQLEXCEPTION matches 2 of 3 conditions - CONTINUE 1273CREATE PROCEDURE sp2() 1274BEGIN 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; 1292END// 1293 1294# 3 - SQLSTATEs - EXIT 1295CREATE PROCEDURE sp3() 1296BEGIN 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; 1315END// 1316 1317# 4 - SQLEXCEPTION matches 2 of 3 conditions - EXIT 1318CREATE PROCEDURE sp4() 1319BEGIN 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; 1338END// 1339delimiter ;// 1340 1341CALL sp0(); 1342SELECT '-0-', @x; 1343 1344CALL sp1(); 1345SELECT '-1-', @x; 1346 1347CALL sp2(); 1348SELECT '-2-', @x; 1349 1350CALL sp3(); 1351SELECT '-3-', @x; 1352 1353CALL sp4(); 1354SELECT '-4-', @x; 1355 1356# cleanup 3.1.2.58 1357DROP PROCEDURE sp0; 1358DROP PROCEDURE sp1; 1359DROP PROCEDURE sp2; 1360DROP PROCEDURE sp3; 1361DROP PROCEDURE sp4; 1362 1363 1364# ------------------------------------------------------------------------------ 1365let $message= Testcase 3.1.2.65:; 1366--source include/show_msg.inc 1367let $message= 1368Ensure that FETCH <cursor name> returns the first row of the cursor_s result set 1369the first time FETCH is executed, that it returns each subsequent row of the 1370cursor_s result set each of the subsequent times FETCH is executed, and that it 1371returns a NOT FOUND warning if it is executed after the last row of the cursor_s 1372result set has already been fetched.; 1373--source include/show_msg80.inc 1374 1375--disable_warnings 1376DROP PROCEDURE IF EXISTS sp1; 1377DROP TABLE IF EXISTS temp; 1378--enable_warnings 1379 1380CREATE TABLE temp( 1381 cnt INT, 1382 f1 CHAR(20), 1383 f2 CHAR(20), 1384 f3 INT, 1385 f4 CHAR(20), 1386 f5 INT); 1387 1388INSERT 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. 1392delimiter //; 1393CREATE PROCEDURE sp1( ) 1394BEGIN 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; 1411END// 1412delimiter ;// 1413 1414--error ER_SP_FETCH_NO_DATA 1415CALL sp1(); 1416 1417SELECT * FROM temp; 1418 1419# cleanup 3.1.2.65 1420DROP TABLE temp; 1421DROP PROCEDURE sp1; 1422 1423 1424# ------------------------------------------------------------------------------ 1425let $message= Testcase 3.1.2.68:; 1426--source include/show_msg.inc 1427let $message= 1428Ensure that FETCH <cursor name> fails with an appropriate error message if the 1429number of columns to be fetched does not match the number of variables specified 1430by the FETCH statement.; 1431--source include/show_msg80.inc 1432 1433--disable_warnings 1434DROP PROCEDURE IF EXISTS sp1; 1435DROP PROCEDURE IF EXISTS sp2; 1436--enable_warnings 1437 1438delimiter //; 1439--echo --> not enough columns in FETCH statement 1440CREATE PROCEDURE sp1( ) 1441BEGIN 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; 1451END// 1452 1453--echo --> too many columns in FETCH statement 1454CREATE PROCEDURE sp2( ) 1455BEGIN 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; 1466END// 1467delimiter ;// 1468 1469--echo --> not enough columns in FETCH statement 1470--error ER_SP_WRONG_NO_OF_FETCH_ARGS 1471CALL sp1(); 1472 1473--echo --> too many columns in FETCH statement 1474--error ER_SP_WRONG_NO_OF_FETCH_ARGS 1475CALL sp2(); 1476 1477# cleanup 3.1.2.68 1478DROP PROCEDURE sp1; 1479DROP PROCEDURE sp2; 1480 1481 1482# ------------------------------------------------------------------------------ 1483let $message= Testcase 3.1.2.75:; 1484--source include/show_msg.inc 1485let $message= 1486Ensure that, for nested compound statements, a cursor that was declared and 1487opened during an outer level of the statement is not closed when an inner level 1488of a compound statement ends.; 1489--source include/show_msg80.inc 1490 1491--disable_warnings 1492DROP TABLE IF EXISTS temp1; 1493DROP PROCEDURE IF EXISTS sp1; 1494--enable_warnings 1495 1496create 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 1501SELECT f1, f2, f4, f5 from t2 order by f4; 1502 1503delimiter //; 1504CREATE PROCEDURE sp1( ) 1505BEGIN 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; 1543END// 1544delimiter ;// 1545 1546CALL sp1(); 1547 1548SELECT * from temp1; 1549 1550# cleanup 3.1.2.75 1551DROP PROCEDURE sp1; 1552drop table temp1; 1553 1554 1555# ------------------------------------------------------------------------------ 1556let $message= Testcase 3.1.2.76:; 1557--source include/show_msg.inc 1558let $message= 1559Ensure that all cursors operate asensitively, so that there is no concurrency 1560conflict between cursors operating on the same, or similar, sets of results 1561during execution of one or more stored procedures.; 1562--source include/show_msg80.inc 1563 1564--disable_warnings 1565DROP PROCEDURE IF EXISTS sp1; 1566drop table IF EXISTS temp1; 1567drop table IF EXISTS temp2; 1568--enable_warnings 1569 1570create table temp1( f0 char(10), cnt int, f1 char(20), f2 char(20), f3 date, f4 integer ); 1571create table temp2( f0 char(10), cnt int, f1 char(20), f2 char(20), f3 date, f4 integer ); 1572 1573delimiter //; 1574CREATE PROCEDURE sp_inner( ) 1575BEGIN 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; 1607END// 1608 1609CREATE PROCEDURE sp_outer( ) 1610BEGIN 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; 1643END// 1644delimiter ;// 1645 1646CALL sp_outer(); 1647 1648SELECT * FROM temp1; 1649SELECT * FROM temp2; 1650 1651# cleanup 3.1.2.75 1652DROP PROCEDURE sp_outer; 1653DROP PROCEDURE sp_inner; 1654DROP TABLE temp1; 1655DROP 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# ============================================================================== 1667