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