1
#### suite/funcs_1/storedproc/storedproc_02.inc
3
--source suite/funcs_1/storedproc/load_sp_tb.inc
5
# ==============================================================================
6
# (numbering from requirement document TP v1.0, Last updated: 25 Jan 2005 01:00)
8
# 3.1.2 Syntax checks for the stored procedure-specific programming statements BEGIN/END, DECLARE, SET, SELECT/INTO, OPEN, FETCH, CLOSE:
10
#- 1. Ensure that all subclauses that should be supported are supported.
11
#- 2. Ensure that all subclauses that should not be supported are disallowed with an appropriate error message.
12
#- 3. Ensure that all supported subclauses are supported only in the correct order.
13
#- 4. Ensure that an appropriate error message is returned if a subclause is out-of-order in a stored procedure definition.
14
#- 5. Ensure that all subclauses that are defined to be mandatory are indeed required to be mandatory by the MySQL server and tools.
15
#- 6. Ensure that any subclauses that are defined to be optional are indeed treated as optional by the MySQL server and tools.
16
#- 7. Ensure that every BEGIN statement is coupled with a terminating END statement.
17
## 8. Ensure that the scope of each BEGIN/END compound statement within a stored procedure definition is properly applied.
18
#- 9. Ensure that the labels enclosing each BEGIN/END compound statement must match.
19
#- 10. Ensure that it is possible to put a beginning label at the start of a BEGIN/END compound statement without also requiring an ending label at the end of the same statement.
20
#- 11. Ensure that it is not possible to put an ending label at the end of a BEGIN/END compound statement without also requiring a matching beginning label at the start of the same statement.
21
#- 12. Ensure that every beginning label must end with a colon (:).
22
#- 13. Ensure that every beginning label with the same scope must be unique.
23
#- 14. Ensure that the variables, cursors, conditions, and handlers declared for a stored procedure (with the DECLARE statement) may only be properly defined.
24
#- 15. Ensure that the variables, cursors, conditions, and handlers declared for a stored procedure (with the DECLARE statement) may only be defined in the correct order.
25
#- 16. Ensure that every possible type of variable -- utilizing every data type definition supported by the MySQL server in combination with both no DEFAULT subclause and with DEFAULT subclauses that set the variable�s default value to a range of appropriate values -- may be declared for a stored procedure.
26
#- 17. Ensure that the DECLARE statement can declare multiple variables both separately and all at once from a variable list.
27
#- 18. Ensure that invalid variable declarations are rejected, with an appropriate error message.
28
#- 19. Ensure that every possible type of cursor may be declared for a stored procedure.
29
#- 20. Ensure that invalid cursor declarations are rejected, with an appropriate error message.
30
#- 21. Ensure that every possible type of condition may be declared for a stored procedure.
31
# -22. Ensure that invalid condition declarations are rejected, with an appropriate error message.
32
#- 23. Ensure that every possible type of handler may be declared for a stored procedure.
33
#- 24. Ensure that invalid handler declarations are rejected, with an appropriate error message.
34
#- 25. Ensure that the scope of every variable, cursor, condition, and handler declared for a stored procedure (with the DECLARE statement) is properly applied.
35
## 26. Ensure that the initial value of every variable declared for a stored procedure is either NULL or its DEFAULT value, as appropriate.
36
#- 27. Ensure that the SET statement can assign a value to every local variable declared within a stored procedure�s definition, as well as to every appropriate global server variable.
37
#- 28. Ensure that the SET statement can assign values to variables either separately or to multiple variables in a list.
38
#- 29. Ensure that the SET statement may assign only those values to a variable that are appropriate for that variable�s data type definition.
39
## 30. Ensure that, when a stored procedure is called/executed, every variable always uses the correct value: either the value with which it is initialized or the value to which it is subsequently SET or otherwise assigned, as appropriate.
40
## 31. Ensure that the SELECT ... INTO statement properly assigns values to the variables in its variable list.
41
## 32. Ensure that a SELECT ... INTO statement that retrieves multiple rows is rejected, with an appropriate error message.
42
## 33. Ensure that a SELECT ... INTO statement that retrieves too many columns for the number of variables in its variable list is rejected, with an appropriate error message.
43
## 34. Ensure that a SELECT ... INTO statement that retrieves too few columns for the number of variables in its variable list is rejected, with an appropriate error message.
44
#- 35. Ensure that a SELECT ... INTO statement that retrieves column values with inappropriate data types for the matching variables in its variable list is rejected, with an appropriate error message.
45
#- 36. Ensure that the DECLARE ... CONDITION FOR statement can declare a properly-named condition for every possible SQLSTATE and MySQL-specific error code.
46
#- 37. Ensure that no two conditions declared with the same scope may have the same condition name.
47
## 38. Ensure that the scope of every condition declared is properly applied.
48
#- 39. Ensure that every SQLSTATE value declared with a DECLARE ... CONDITION FOR statement is a character string that is 5 characters long.
49
#- 40. Ensure that the DECLARE ... CONDITION FOR statement cannot declare a condition for an invalid SQLSTATE.
50
#- 41. Ensure that the DECLARE ... CONDITION FOR statement cannot declare a condition for the �successful completion SQLSTATE: �00000�.
51
#- 42. Ensure that the DECLARE ... HANDLER FOR statement can declare a CONTINUE, EXIT, and UNDO handler for every condition declared (with a DECLARE ... CONDITION FOR statement), within the scope of the handler, for a stored procedure, as well as for every possible SQLSTATE and MySQL-specific error code, as well as for the predefined conditions SQLWARNING, NOT FOUND, and SQLEXCEPTION.
52
## 43. Ensure that the DECLARE ... HANDLER FOR statement can not declare any handler for a condition declared outside of the scope of the handler.
53
## 44. Ensure that the DECLARE ... HANDLER FOR statement cannot declare a handler for any invalid, or undeclared, condition.
54
## 45. Ensure that the scope of every handler declared is properly applied.
55
#- 46. Ensure that, within the same scope, no two handlers may be declared for the same condition.
56
#- 47. Ensure that every SQLSTATE value declared with a DECLARE ... HANDLER FOR statement is a character string that is 5 characters long.
57
#- 48. Ensure that the DECLARE ... HANDLER FOR statement cannot declare a condition for an invalid SQLSTATE.
58
#- 49. Ensure that the DECLARE ... HANDLER FOR statement cannot declare a condition for the �successful completion SQLSTATE: �00000�.
59
## 50. Ensure that a CONTINUE handler allows the execution of the stored procedure to continue once the handler statement has completed its own execution (that is, once the handler action statement has been executed).
60
## 51. Ensure that an EXIT handler causes the execution of the stored procedure to terminate, within its scope, once the handler action statement has been executed.
61
## 52. Ensure that an EXIT handler does not cause the execution of the stored procedure to terminate outside of its scope.
62
#- 53. Ensure that a handler condition of SQLWARNING takes the same action as a handler condition defined with an SQLSTATE that begins with �01�.
63
## 54. Ensure that a handler with a condition defined with an SQLSTATE that begins with �01� is always exactly equivalent in action to a handler with an SQLWARNING condition.
64
#- 55. Ensure that a handler condition of NOT FOUND takes the same action as a handler condition defined with an SQLSTATE that begins with �02�.
65
## 56. Ensure that a handler with a condition defined with an SQLSTATE that begins with �02� is always exactly equivalent in action to a handler with a NOT FOUND condition.
66
#- 57. Ensure that a handler condition of SQLEXCEPTION takes the same action as a handler condition defined with an SQLSTATE that begins with anything other that �01� or �02�.
67
## 58. Ensure that a handler with a condition defined with an SQLSTATE that begins with anything other that �01� or �02� is always exactly equivalent in action to a handler with an SQLEXCEPTION condition.
68
#- 59. Ensure that no two cursors in a stored procedure can have the same name.
69
#- 60. Ensure that a cursor declaration may not include a SELECT ... INTO statement.
70
#- 61. Ensure that a cursor declaration that includes an ORDER BY clause may not be an updatable cursor.
71
#- 62. Ensure that OPEN <cursor name> fails unless a cursor with the same name has already been declared.
72
#- 63. Ensure that OPEN <cursor name> fails if the same cursor is currently already open.
73
#- 64. Ensure that FETCH <cursor name> fails unless a cursor with the same name is already open.
74
## 65. Ensure that FETCH <cursor name> returns the first row of the cursor�s result set the first time FETCH is executed, that it returns each subsequent row of the cursor�s result set each of the subsequent times FETCH is executed, and that it returns a NOT FOUND warning if it is executed after the last row of the cursor�s result set has already been fetched.
75
#- 66. Ensure that FETCH <cursor name> fails with an appropriate error message if it is executed before the cursor has been opened.
76
#- 67. Ensure that FETCH <cursor name> fails with an appropriate error message if it is executed after the cursor has been closed.
77
## 68. Ensure that FETCH <cursor name> fails with an appropriate error message if the number of columns to be fetched does not match the number of variables specified by the FETCH statement.
78
#- 69. Ensure that FETCH <cursor name> fails with an appropriate error message if the data type of the column values being fetched are not appropriate for the matching FETCH variables to which the data is being assigned.
79
#- 70. Ensure that CLOSE <cursor name> fails unless a cursor with the same name is already open.
80
#- 71. Ensure that all cursors are closed when a transaction terminates with a COMMIT statement.
81
#- 72. Ensure that all cursors are closed when a transaction terminates with a ROLLBACK statement.
82
#- 73. Ensure that the result set of a cursor that has been closed is not longer available to the FETCH statement.
83
#- 74. Ensure that every cursor declared within a compound statement is closed when that compound statement ends.
84
## 75. Ensure that, for nested compound statements, a cursor that was declared and opened during an outer level of the statement is not closed when an inner level of a compound statement ends.
85
## 76. Ensure that all cursors operate asensitively, so that there is no concurrency conflict between cursors operating on the same, or similar, sets of results during execution of one or more stored procedures.
86
# 77. Ensure that multiple cursors, nested within multiple compound statements within a stored procedure, always act correctly and return the expected result.
88
# ==============================================================================
89
let $message= Section 3.1.2 - Syntax checks for the stored procedure-specific
90
programming statements BEGIN/END, DECLARE, SET, SELECT/INTO, OPEN, FETCH, CLOSE:;
91
--source include/show_msg80.inc
94
# ------------------------------------------------------------------------------
95
let $message= Testcase 3.1.2.8:;
96
--source include/show_msg.inc
98
Ensure that the scope of each BEGIN/END compound statement within a stored
99
procedure definition is properly applied;
100
--source include/show_msg80.inc
103
DROP PROCEDURE IF EXISTS sp1;
107
CREATE PROCEDURE sp1( )
109
declare x char DEFAULT 'x';
110
declare y char DEFAULT 'y';
114
declare x char DEFAULT 'X';
115
declare y char DEFAULT 'Y';
116
SELECT f1, f2 into x, y from t2 limit 1;
119
declare x char default 'a';
120
declare y char default 'b';
122
declare x char default 'c';
123
declare y char default 'd';
125
declare x char default 'e';
126
declare y char default 'f';
128
declare x char default 'g';
129
declare y char default 'h';
141
SELECT '1.2', @v1, @v2;
148
DROP PROCEDURE IF EXISTS sp1;
151
# ------------------------------------------------------------------------------
152
let $message= Testcase 3.1.2.26:;
153
--source include/show_msg.inc
155
Ensure that the initial value of every variable declared for a stored procedure
156
is either NULL or its DEFAULT value, as appropriate.;
157
--source include/show_msg80.inc
160
DROP PROCEDURE IF EXISTS sp1;
167
CREATE PROCEDURE sp1( )
169
declare x1 char default 'x';
171
declare x2 tinytext default 'tinytext';
173
declare x3 datetime default '2005-10-03 12:13:14';
175
declare x4 float default 1.2;
177
declare x5 blob default 'b';
179
declare x6 smallint default 127;
181
SELECT x1, x2, x3, x4, x5, x6, y1, y2, y3, y4, y5, y6;
191
# ------------------------------------------------------------------------------
192
let $message= Testcase 3.1.2.30:;
193
--source include/show_msg.inc
195
Ensure that, when a stored procedure is called/executed, every variable always
196
uses the correct value: either the value with which it is initialized or the
197
value to which it is subsequently SET or otherwise assigned, as appropriate.;
198
--source include/show_msg80.inc
201
DROP PROCEDURE IF EXISTS sp1;
205
CREATE PROCEDURE sp1( IN invar INT, OUT outvar INT )
208
declare y integer default 1;
212
SELECT f1, f2 into @x, @y from t2 limit 1;
213
SELECT @x, @y, @z, invar;
217
SET outvar = @x * invar + @z * @f;
220
set @y = null, @z = 'abcd';
228
SET @outvar = @invar;
231
SELECT @x, @y, @z, @invar, @outvar;
233
CALL sp1( @invar, @outvar );
235
SELECT @x, @y, @z, @invar, @outvar;
241
# ------------------------------------------------------------------------------
242
let $message= Testcase 3.1.2.31:;
243
--source include/show_msg.inc
245
Ensure that the SELECT ... INTO statement properly assigns values to the
246
variables in its variable list.;
247
--source include/show_msg80.inc
248
# also tested in a lot of other testcases
251
DROP PROCEDURE IF EXISTS sp1;
255
CREATE PROCEDURE sp1( )
257
declare x integer; declare y integer;
260
SELECT f4, f3 into @x, @y from t2 limit 1;
271
# ------------------------------------------------------------------------------
272
let $message= Testcase 3.1.2.32:;
273
--source include/show_msg.inc
275
Ensure that a SELECT ... INTO statement that retrieves multiple rows is
276
rejected, with an appropriate error message.;
277
--source include/show_msg80.inc
280
DROP PROCEDURE IF EXISTS sp1;
284
CREATE PROCEDURE sp1( )
286
declare x integer; declare y integer;
289
SELECT f4, f3 into @x, @y from t2;
293
#Error: 1172 SQLSTATE: 42000 (ER_TOO_MANY_ROWS) Message: Result consisted of more than one row
301
# ------------------------------------------------------------------------------
302
let $message= Testcase 3.1.2.33:;
303
--source include/show_msg.inc
305
Ensure that a SELECT ... INTO statement that retrieves too many columns for the
306
number of variables in its variable list is rejected, with an appropriate error
308
--source include/show_msg80.inc
311
DROP PROCEDURE IF EXISTS sp1;
315
CREATE PROCEDURE sp1( )
317
declare x integer; declare y integer;
320
SELECT f4, f3, f2, f1 into @x, @y from t2;
331
# ------------------------------------------------------------------------------
332
let $message= Testcase 3.1.2.34:;
333
--source include/show_msg.inc
335
Ensure that a SELECT ... INTO statement that retrieves too few columns for the
336
number of variables in its variable list is rejected, with an appropriate error
338
--source include/show_msg80.inc
341
DROP PROCEDURE IF EXISTS sp1;
345
CREATE PROCEDURE sp1( )
347
declare x integer; declare y integer; declare z integer;
351
SELECT f4 into @x, @y, @z from t2;
362
# ------------------------------------------------------------------------------
363
let $message= Testcase 3.1.2.38:;
364
--source include/show_msg.inc
366
Ensure that the scope of every condition declared is properly applied.;
367
--source include/show_msg80.inc
370
DROP PROCEDURE IF EXISTS h1;
371
DROP TABLE IF EXISTS res_t1;
374
create table res_t1(w char unique, x char);
376
insert into res_t1 values('a', 'b');
378
# Error: 1339 SQLSTATE: 20000 (ER_SP_CASE_NOT_FOUND) Message: Case not found for CASE statement
379
# Error: 1022 SQLSTATE: 23000 (ER_DUP_KEY) Message: Can't write; duplicate key in table '%s'
382
CREATE PROCEDURE h1 ()
384
declare x1, x2, x3, x4, x5, x6 int default 0;
385
SELECT '-1-', x1, x2, x3, x4, x5, x6;
387
declare condname condition for sqlstate '23000';
388
declare continue handler for condname set x5 = 1;
390
insert into res_t1 values ('a', 'b');
392
SELECT '-2-', x1, x2, x3, x4, x5, x6;
396
declare condname condition for sqlstate '20000';
397
declare continue handler for condname set x1 = 1;
400
when 1 then set x2=10;
401
when 2 then set x2=11;
404
SELECT '-3-', x1, x2, x3, x4, x5, x6;
407
declare condname condition for sqlstate '23000';
408
declare exit handler for condname set x3 = 1;
410
SELECT '-4a', x1, x2, x3, x4, x5, x6;
411
insert into res_t1 values ('a', 'b');
413
SELECT '-4b', x1, x2, x3, x4, x5, x6;
415
SELECT '-5-', x1, x2, x3, x4, x5, x6;
417
SELECT '-6-', x1, x2, x3, x4, x5, x6;
419
SELECT '-7-', x1, x2, x3, x4, x5, x6;
421
SELECT 'END', x1, x2, x3, x4, x5, x6;
429
DROP TABLE IF EXISTS tnull;
430
DROP PROCEDURE IF EXISTS sp1;
433
CREATE TABLE tnull(f1 int);
436
CREATE PROCEDURE sp1()
438
declare cond1 condition for sqlstate '42S02';
439
declare continue handler for cond1 set @var2 = 1;
441
declare cond1 condition for sqlstate '23000';
442
declare continue handler for cond1 set @var2 = 1;
444
insert into tnull values(1);
457
# ------------------------------------------------------------------------------
458
let $message= Testcase 3.1.2.43:;
459
--source include/show_msg.inc
461
Ensure that the DECLARE ... HANDLER FOR statement can not declare any handler
462
for a condition declared outside of the scope of the handler.;
463
--source include/show_msg80.inc
466
DROP PROCEDURE IF EXISTS h1;
467
DROP PROCEDURE IF EXISTS h2;
468
drop table IF EXISTS res_t1;
471
create table res_t1(w char unique, x char);
472
insert into res_t1 values ('a', 'b');
476
CREATE PROCEDURE h1 ()
478
declare x1, x2, x3, x4, x5, x6 int default 0;
480
declare cond_1 condition for sqlstate '23000';
481
declare continue handler for cond_1 set x5 = 1;
483
declare cond_2 condition for sqlstate '20000';
484
declare continue handler for cond_1 set x1 = 1;
486
declare continue handler for cond_2 set x3 = 1;
492
declare continue handler for cond_1 set x1 = 1;
494
declare continue handler for cond_2 set x3 = 1;
500
SELECT x1, x2, x3, x4, x5, x6;
503
CREATE PROCEDURE h2 ()
505
declare x1, x2, x3, x4, x5, x6 int default 0;
507
declare condname condition for sqlstate '23000';
508
declare continue handler for condname set x5 = 1;
510
declare condname condition for sqlstate '20000';
511
declare continue handler for condname set x1 = 1;
513
declare condname condition for sqlstate '42000';
514
declare continue handler for condname set x3 = 1;
516
insert into res_t1 values ('a', 'b');
523
when 1 then set x2=10;
524
when 2 then set x2=11;
530
when 1 then set x2=10;
531
when 2 then set x2=11;
535
insert into res_t1 values ('a', 'b');
539
SELECT x1, x2, x3, x4, x5, x6;
544
SELECT * FROM res_t1;
551
# ------------------------------------------------------------------------------
552
let $message= Testcase 3.1.2.44:;
553
--source include/show_msg.inc
555
Ensure that the DECLARE ... HANDLER FOR statement cannot declare a handler for
556
any invalid, or undeclared, condition.;
557
--source include/show_msg80.inc
560
DROP PROCEDURE IF EXISTS h1;
564
#Error: 1319 SQLSTATE: 42000 (ER_SP_COND_MISMATCH) Message: Undefined CONDITION: %s
566
CREATE PROCEDURE h1 ()
568
declare x1, x2, x3, x4, x5, x6 int default 0;
570
declare condname1 condition for sqlstate '23000';
572
declare condname2 condition for sqlstate '20000';
573
declare continue handler for condname1 set x3 = 1;
574
declare continue handler for condname2 set x1 = 1;
578
declare condname3 condition for sqlstate '42000';
579
declare continue handler for condname1 set x3 = 1;
580
declare continue handler for condname2 set x5 = 1;
581
declare continue handler for condname3 set x1 = 1;
585
#Error: 1064 SQLSTATE: 42000 (ER_PARSE_ERROR) Message: %s near '%s' at line %d
587
CREATE PROCEDURE h1 ()
589
DECLARE x1 INT DEFAULT 0;
591
DECLARE condname1 CONDITION CHECK SQLSTATE '23000';
593
DECLARE CONTINUE HANDLER FOR condname1 SET x1 = 1;
596
#Error: 1407 SQLSTATE: 42000 (ER_SP_BAD_SQLSTATE) Message: Bad SQLSTATE: '%s'
598
CREATE PROCEDURE h1 ()
600
DECLARE x1 INT DEFAULT 0;
602
DECLARE condname1 CONDITION FOR SQLSTATE 'qwert';
604
DECLARE CONTINUE HANDLER FOR condname1 SET x1 = 1;
612
# ------------------------------------------------------------------------------
613
let $message= Testcase 3.1.2.45 + 3.1.2.50:;
614
--source include/show_msg.inc
616
45. Ensure that the scope of every handler declared is properly applied.
617
50. Ensure that a CONTINUE handler allows the execution of the stored procedure
618
. to continue once the handler statement has completed its own execution (that
619
. is, once the handler action statement has been executed).;
620
--source include/show_msg80.inc
622
# RefMan: For an EXIT handler, execution of the current BEGIN...END compound
623
# statement is terminated.
626
DROP PROCEDURE IF EXISTS p1;
627
DROP PROCEDURE IF EXISTS p1undo;
628
DROP PROCEDURE IF EXISTS h1;
629
DROP PROCEDURE IF EXISTS sp1;
630
drop table IF EXISTS res_t1;
633
--echo ==> 'UNDO' is still not supported.
636
create procedure p1undo ()
638
declare undo handler for sqlexception select '1';
643
create procedure p1 ()
645
declare exit handler for sqlexception select 'exit handler 1';
647
declare exit handler for sqlexception select 'exit handler 2';
649
declare continue handler for sqlexception select 'continue handler 3';
650
drop table if exists tqq;
652
SELECT 'end of BEGIN/END 3';
654
drop table if exists tqq;
656
SELECT 'end of BEGIN/END 2';
659
SELECT 'end of BEGIN/END 1';
665
create table res_t1(w char unique, x char);
666
insert into res_t1 values ('a', 'b');
669
CREATE PROCEDURE h1 ()
671
declare x1, x2, x3, x4, x5, x6 int default 0;
673
declare continue handler for sqlstate '23000' set x5 = 1;
674
insert into res_t1 values ('a', 'b');
679
declare continue handler for sqlstate '23000' set x1 = 1;
680
insert into res_t1 values ('a', 'b');
684
declare exit handler for sqlstate '23000' set x3 = 1;
686
insert into res_t1 values ('a', 'b');
692
SELECT x1, x2, x3, x4, x5, x6;
700
CREATE PROCEDURE sp1()
702
declare exit handler for sqlstate '00000' set @var1 = 5;
705
declare continue handler for sqlstate '00000' set @var3 = 7;
715
# cleanup 3.1.2.45+50
722
# ------------------------------------------------------------------------------
723
let $message= Testcase 3.1.2.50:;
724
--source include/show_msg.inc
726
# testcase: ensure that a continue handler allows the execution of the stored procedure
727
# to continue once the handler statement has completed its own execution
728
# (that is, once the handler action statement has been executed).
732
DROP PROCEDURE IF EXISTS sp1;
733
DROP PROCEDURE IF EXISTS sp2;
737
CREATE PROCEDURE sp1 (x int, y int)
744
CREATE PROCEDURE sp2 ()
746
declare continue handler for sqlstate '42000' set @x2 = 1;
762
# ------------------------------------------------------------------------------
763
let $message= Testcase 3.2.2.51:;
764
--source include/show_msg.inc
766
Ensure that an EXIT handler causes the execution of the stored procedure to
767
terminate, within its scope, once the handler action statement has been
769
--source include/show_msg80.inc
770
# also tested in 3.1.2.45
773
DROP PROCEDURE IF EXISTS sp1;
774
DROP PROCEDURE IF EXISTS sp2;
778
CREATE PROCEDURE sp1 (x int, y int)
785
CREATE PROCEDURE sp2 ()
787
declare exit handler for sqlstate '42000' set @x2 = 1;
790
SELECT '-1-', @x2, @x;
792
SELECT '-2-', @x2, @x;
797
# Error: 1318 SQLSTATE: 42000 (ER_SP_WRONG_NO_OF_ARGS) Message: Incorrect number of arguments for %s %s; expected %u, got %u
801
SELECT '-3-', @x2, @x;
808
# ------------------------------------------------------------------------------
809
let $message= Testcase 3.1.2.52:;
810
--source include/show_msg.inc
812
Ensure that an EXIT handler does not cause the execution of the stored procedure
813
to terminate outside of its scope.;
814
--source include/show_msg80.inc
815
# tested also above in
818
DROP PROCEDURE IF EXISTS sp1;
819
DROP PROCEDURE IF EXISTS sp2;
823
CREATE PROCEDURE sp1 (x int, y int)
830
CREATE PROCEDURE sp2()
832
declare continue handler for sqlstate '42000' set @x2 = 2;
835
SELECT '-1-', @x2, @x;
837
declare exit handler for sqlstate '42000' set @x2 = 11;
838
SELECT '-2-', @x2, @x;
840
SELECT '-3a', @x2, @x;
842
SELECT '-3b', @x2, @x;
845
SELECT '-4-', @x2, @x;
856
# ------------------------------------------------------------------------------
857
let $message= Testcase 3.1.2.54:;
858
--source include/show_msg.inc
860
Ensure that a handler with a condition defined with an SQLSTATE that begins with
861
�01� is always exactly equivalent in action to a handler with an SQLWARNING
863
--source include/show_msg80.inc
866
DROP PROCEDURE IF EXISTS sp0;
867
DROP PROCEDURE IF EXISTS sp1;
868
DROP PROCEDURE IF EXISTS sp2;
869
DROP PROCEDURE IF EXISTS sp3;
870
DROP PROCEDURE IF EXISTS sp4;
871
DROP TABLE IF EXISTS temp;
874
CREATE TABLE temp( f1 CHAR, f2 CHAR);
877
# 0 - without handler
878
CREATE PROCEDURE sp0()
882
insert into temp values('xxx', 'yy');
886
# 1st one with SQLSTATE + CONTINUE
887
CREATE PROCEDURE sp1()
889
declare continue handler for sqlstate '01000' set @done = 1;
892
insert into temp values('xxx', 'yy');
896
# 2nd one with SQLWARNING + CONTINUE
897
CREATE PROCEDURE sp2()
899
declare continue handler for sqlwarning set @done = 1;
902
insert into temp values('xxx', 'yy');
906
# 3 with SQLSTATE + EXIT
907
CREATE PROCEDURE sp3()
909
declare exit handler for sqlstate '01000' set @done = 1;
912
insert into temp values('xxx', 'yy');
916
# 4 with SQLWARNING + EXIT
917
CREATE PROCEDURE sp4()
919
declare exit handler for sqlwarning set @done = 1;
922
insert into temp values('xxx', 'yy');
927
INSERT INTO temp VALUES('0', NULL);
931
INSERT INTO temp VALUES('1', NULL);
935
INSERT INTO temp VALUES('2', NULL);
939
INSERT INTO temp VALUES('3', NULL);
943
INSERT INTO temp VALUES('4', NULL);
957
# ------------------------------------------------------------------------------
958
let $message= Testcase 3.1.2.56:;
959
--source include/show_msg.inc
961
Ensure that a handler with a condition defined with an SQLSTATE that begins with
962
�02� is always exactly equivalent in action to a handler with a NOT FOUND
964
--source include/show_msg80.inc
967
DROP PROCEDURE IF EXISTS sp0;
968
DROP PROCEDURE IF EXISTS sp1;
969
DROP PROCEDURE IF EXISTS sp2;
970
DROP PROCEDURE IF EXISTS sp3;
971
DROP PROCEDURE IF EXISTS sp4;
975
# 0 - wihtout handler
976
CREATE PROCEDURE sp0()
978
DECLARE f1_value CHAR(20);
979
DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
983
FETCH cur1 INTO f1_value;
985
FETCH cur1 INTO f1_value;
990
# 1st one with SQLSTATE + CONTINUE
991
CREATE PROCEDURE sp1()
993
DECLARE f1_value CHAR(20);
994
DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
995
declare continue handler for sqlstate '02000' set @done = 1;
999
FETCH cur1 INTO f1_value;
1001
FETCH cur1 INTO f1_value;
1006
# 2nd one with NOT FOUND + CONTINUE
1007
CREATE PROCEDURE sp2()
1009
DECLARE f1_value CHAR(20);
1010
DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
1011
declare continue handler for not found set @done = 1;
1015
FETCH cur1 INTO f1_value;
1017
FETCH cur1 INTO f1_value;
1022
# 3 with SQLSTATE + EXIT
1023
CREATE PROCEDURE sp3()
1025
DECLARE f1_value CHAR(20);
1026
DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
1027
declare exit handler for sqlstate '02000' set @done = 1;
1031
FETCH cur1 INTO f1_value;
1033
FETCH cur1 INTO f1_value;
1038
# 4 with NOT FOUND + EXIT
1039
CREATE PROCEDURE sp4()
1041
DECLARE f1_value CHAR(20);
1042
DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
1043
declare exit handler for not found set @done = 1;
1047
FETCH cur1 INTO f1_value;
1049
FETCH cur1 INTO f1_value;
1079
# ------------------------------------------------------------------------------
1080
let $message= Testcase 3.1.2.58:;
1081
--source include/show_msg.inc
1083
Ensure that a handler with a condition defined with an SQLSTATE that begins with
1084
anything other that �01� or �02� is always exactly equivalent in action to a
1085
handler with an SQLEXCEPTION condition.;
1086
--source include/show_msg80.inc
1088
# Error: 1339 SQLSTATE: 20000 (ER_SP_CASE_NOT_FOUND) Message: Case not found for CASE statement
1089
# Error: 1222 SQLSTATE: 21000 (ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT) Message: The used SELECT statements have a different number of columns
1090
# Error: 1326 SQLSTATE: 24000 (ER_SP_CURSOR_NOT_OPEN) Message: Cursor is not open
1093
DROP PROCEDURE IF EXISTS sp0;
1094
DROP PROCEDURE IF EXISTS sp1;
1095
DROP PROCEDURE IF EXISTS sp2;
1096
DROP PROCEDURE IF EXISTS sp3;
1097
DROP PROCEDURE IF EXISTS sp4;
1101
# 0 - without handler
1102
CREATE PROCEDURE sp0()
1104
DECLARE f1_value CHAR(20);
1105
DECLARE cv INT DEFAULT 0;
1106
DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
1109
WHEN 2 THEN SET @x = 2;
1110
WHEN 3 THEN SET @x = 3;
1113
SELECT f1, f2 FROM t2
1115
SELECT f1, f2,3 FROM t2;
1117
FETCH cur1 INTO f1_value;
1121
# 1 - SQLSTATEs - CONTINUE
1122
CREATE PROCEDURE sp1()
1124
DECLARE f1_value CHAR(20);
1125
DECLARE cv INT DEFAULT 0;
1126
DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
1127
DECLARE continue HANDLER FOR SQLSTATE '20000' SELECT '20000' AS 'SQLSTATE';
1128
DECLARE continue HANDLER FOR SQLSTATE '21000' SELECT '21000' AS 'SQLSTATE';
1129
DECLARE continue HANDLER FOR SQLSTATE '24000' SELECT '24000' AS 'SQLSTATE';
1132
WHEN 2 THEN SET @x = 2;
1133
WHEN 3 THEN SET @x = 3;
1136
SELECT f1, f2 FROM t2
1138
SELECT f1, f2,3 FROM t2;
1140
FETCH cur1 INTO f1_value;
1144
# 2 - SQLEXCEPTION matches 2 of 3 conditions - CONTINUE
1145
CREATE PROCEDURE sp2()
1147
DECLARE f1_value CHAR(20);
1148
DECLARE cv INT DEFAULT 0;
1149
DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
1150
DECLARE continue HANDLER FOR SQLEXCEPTION SELECT 'SQLEXCEPTION' AS 'SQLSTATE';
1151
DECLARE continue HANDLER FOR SQLSTATE '24000' SELECT '24000' AS 'SQLSTATE';
1154
WHEN 2 THEN SET @x = 2;
1155
WHEN 3 THEN SET @x = 3;
1158
SELECT f1, f2 FROM t2
1160
SELECT f1, f2,3 FROM t2;
1162
FETCH cur1 INTO f1_value;
1166
# 3 - SQLSTATEs - EXIT
1167
CREATE PROCEDURE sp3()
1169
DECLARE f1_value CHAR(20);
1170
DECLARE cv INT DEFAULT 0;
1171
DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
1172
DECLARE EXIT HANDLER FOR SQLSTATE '20000' SELECT '20000' AS 'SQLSTATE';
1173
DECLARE EXIT HANDLER FOR SQLSTATE '21000' SELECT '21000' AS 'SQLSTATE';
1174
DECLARE EXIT HANDLER FOR SQLSTATE '24000' SELECT '24000' AS 'SQLSTATE';
1177
WHEN 2 THEN SET @x = 2;
1178
WHEN 3 THEN SET @x = 3;
1181
SELECT f1, f2 FROM t2
1183
SELECT f1, f2,3 FROM t2;
1185
FETCH cur1 INTO f1_value;
1189
# 4 - SQLEXCEPTION matches 2 of 3 conditions - EXIT
1190
CREATE PROCEDURE sp4()
1192
DECLARE f1_value CHAR(20);
1193
DECLARE cv INT DEFAULT 0;
1194
DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
1195
DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT 'SQLEXCEPTION' AS 'SQLSTATE';
1196
DECLARE EXIT HANDLER FOR SQLSTATE '24000' SELECT '24000' AS 'SQLSTATE';
1199
WHEN 2 THEN SET @x = 2;
1200
WHEN 3 THEN SET @x = 3;
1203
SELECT f1, f2 FROM t2
1205
SELECT f1, f2,3 FROM t2;
1207
FETCH cur1 INTO f1_value;
1236
# ------------------------------------------------------------------------------
1237
let $message= Testcase 3.1.2.65:;
1238
--source include/show_msg.inc
1240
Ensure that FETCH <cursor name> returns the first row of the cursor_s result set
1241
the first time FETCH is executed, that it returns each subsequent row of the
1242
cursor_s result set each of the subsequent times FETCH is executed, and that it
1243
returns a NOT FOUND warning if it is executed after the last row of the cursor_s
1244
result set has already been fetched.;
1245
--source include/show_msg80.inc
1248
DROP PROCEDURE IF EXISTS sp1;
1249
DROP TABLE IF EXISTS temp;
1260
INSERT INTO temp VALUES(0, 'onip', 'abc', 8760, 'xyz', 10);
1262
# NOT used: declare continue handler for sqlstate '02000' set proceed=0;
1263
# --> warning is shown when procedure is executed.
1265
CREATE PROCEDURE sp1( )
1267
declare proceed int default 1;
1268
declare count integer default 1;
1269
declare f1_value char(20);
1270
declare f2_value char(20);
1271
declare f5_value char(20);
1272
declare f4_value integer;
1273
declare f6_value integer;
1274
declare cur1 cursor for SELECT f1, f2, f4, f5, f6 from t2 limit 3;
1277
SELECT count AS 'loop';
1278
fetch cur1 into f1_value, f2_value, f4_value, f5_value, f6_value;
1279
insert into temp values (count, f1_value, f2_value, f4_value, f5_value, f6_value);
1280
set count = count + 1;
1295
# ------------------------------------------------------------------------------
1296
let $message= Testcase 3.1.2.68:;
1297
--source include/show_msg.inc
1299
Ensure that FETCH <cursor name> fails with an appropriate error message if the
1300
number of columns to be fetched does not match the number of variables specified
1301
by the FETCH statement.;
1302
--source include/show_msg80.inc
1305
DROP PROCEDURE IF EXISTS sp1;
1306
DROP PROCEDURE IF EXISTS sp2;
1310
--echo --> not enough columns in FETCH statement
1311
CREATE PROCEDURE sp1( )
1313
declare newf1 char(20);
1314
declare cur1 cursor for SELECT f1, f2 from t2 limit 10;
1315
declare continue handler for sqlstate '02000' SELECT 'HANDLER executed.' AS '';
1318
fetch cur1 into newf1;
1324
--echo --> too many columns in FETCH statement
1325
CREATE PROCEDURE sp2( )
1327
declare newf1 char(20);
1328
declare newf2 char(20);
1329
declare cur1 cursor for SELECT f1 from t2 limit 10;
1330
declare continue handler for sqlstate '02000' SELECT 'HANDLER executed.' AS '';
1333
fetch cur1 into newf1, newf2;
1334
SELECT newf1, newf2;
1340
--echo --> not enough columns in FETCH statement
1344
--echo --> too many columns in FETCH statement
1353
# ------------------------------------------------------------------------------
1354
let $message= Testcase 3.1.2.75:;
1355
--source include/show_msg.inc
1357
Ensure that, for nested compound statements, a cursor that was declared and
1358
opened during an outer level of the statement is not closed when an inner level
1359
of a compound statement ends.;
1360
--source include/show_msg80.inc
1363
DROP TABLE IF EXISTS temp1;
1364
DROP PROCEDURE IF EXISTS sp1;
1367
create table temp1( f0 char(20), f1 char(20), f2 char(20), f3 int, f4 char(20) );
1369
#Error: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA) Message: No data to FETCH
1371
SELECT f1, f2, f4, f5 from t2;
1374
CREATE PROCEDURE sp1( )
1376
declare count integer;
1377
declare from0 char(20);
1378
declare newf1 char(20);
1379
declare newf2 char(20);
1380
declare newf5 char(20);
1381
declare newf4 integer;
1382
declare cur1 cursor for SELECT f1, f2, f4, f5 from t2 limit 5;
1383
declare cur2 cursor for SELECT f1, f2, f4, f5 from t2 limit 5;
1387
declare continue handler for sqlstate '02000' set count = 1;
1388
fetch cur1 into newf1, newf2, newf4, newf5;
1389
SELECT '-1-', count, newf1, newf2, newf4, newf5;
1390
insert into temp1 values ('cur1_out', newf1, newf2, newf4, newf5);
1394
fetch cur1 into newf1, newf2, newf4, newf5;
1395
SELECT '-2-', count, newf1, newf2, newf4, newf5;
1396
set count = count - 1;
1398
SELECT '-3-', count, newf1, newf2, newf4, newf4;
1401
fetch cur1 into newf1, newf2, newf4, newf5;
1402
SELECT '-4-', newf1, newf2, newf4, newf5;
1403
insert into temp1 values ('cur1_in', newf1, newf2, newf4, newf5);
1405
fetch cur2 into newf1, newf2, newf4, newf5;
1406
SELECT '-5-', newf1, newf2, newf4, newf5;
1407
insert into temp1 values ('cur2', newf1, newf2, newf4, newf5);
1410
fetch cur2 into newf1, newf2, newf4, newf5;
1411
SELECT '-6-', newf1, newf2, newf4, newf5;
1418
SELECT * from temp1;
1425
# ------------------------------------------------------------------------------
1426
let $message= Testcase 3.1.2.76:;
1427
--source include/show_msg.inc
1429
Ensure that all cursors operate asensitively, so that there is no concurrency
1430
conflict between cursors operating on the same, or similar, sets of results
1431
during execution of one or more stored procedures.;
1432
--source include/show_msg80.inc
1435
DROP PROCEDURE IF EXISTS sp1;
1436
drop table IF EXISTS temp1;
1437
drop table IF EXISTS temp2;
1440
create table temp1( f0 char(10), cnt int, f1 char(20), f2 char(20), f3 date, f4 integer );
1441
create table temp2( f0 char(10), cnt int, f1 char(20), f2 char(20), f3 date, f4 integer );
1444
CREATE PROCEDURE sp_inner( )
1446
declare proceed int default 1;
1447
declare i_count integer default 20;
1448
declare i_newf1 char(20);
1449
declare i_newf2 char(20);
1450
declare i_newf3 date;
1451
declare i_newf4 integer;
1452
declare i_newf11 char(20);
1453
declare i_newf12 char(20);
1454
declare i_newf13 date;
1455
declare i_newf14 integer;
1456
declare cur1 cursor for SELECT f1, f2, f3, f4 from t2 limit 4;
1457
declare cur2 cursor for SELECT f1, f2, f3, f4 from t2 limit 3;
1458
declare continue handler for sqlstate '02000' set proceed=0;
1463
fetch cur1 into i_newf1, i_newf2, i_newf3, i_newf4;
1465
insert into temp1 values ('sp_inner', i_count, i_newf1, i_newf2, i_newf3, i_newf4);
1466
fetch cur2 into i_newf11, i_newf12, i_newf13, i_newf14;
1468
insert into temp2 values ('sp_inner', i_count, i_newf11, i_newf12, i_newf13, i_newf14);
1471
set i_count = i_count - 1;
1477
CREATE PROCEDURE sp_outer( )
1479
DECLARE proceed INT DEFAULT 1;
1480
DECLARE o_count INTEGER DEFAULT 20;
1481
DECLARE o_newf1 CHAR(20);
1482
DECLARE o_newf2 CHAR(20);
1483
DECLARE o_newf3 DATE;
1484
DECLARE o_newf4 INTEGER;
1485
DECLARE o_newf11 CHAR(20);
1486
DECLARE o_newf12 CHAR(20);
1487
DECLARE o_newf13 DATE;
1488
DECLARE o_newf14 INTEGER;
1489
DECLARE cur1 CURSOR FOR SELECT f1, f2, f3, f4 FROM t2 LIMIT 5;
1490
DECLARE cur2 CURSOR FOR SELECT f1, f2, f3, f4 FROM t2 LIMIT 5;
1491
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET proceed=0;
1496
FETCH cur1 INTO o_newf1, o_newf2, o_newf3, o_newf4;
1498
INSERT INTO temp1 VALUES ('_sp_out_', o_count, o_newf1, o_newf2, o_newf3, o_newf4);
1500
FETCH cur2 INTO o_newf11, o_newf12, o_newf13, o_newf14;
1502
INSERT INTO temp2 VALUES ('_sp_out_', o_count, o_newf11, o_newf12, o_newf13, o_newf14);
1505
SET o_count = o_count + 1;
1514
SELECT * FROM temp1;
1515
SELECT * FROM temp2;
1518
DROP PROCEDURE sp_outer;
1519
DROP PROCEDURE sp_inner;
1524
# ==============================================================================
1525
# USE the same .inc to cleanup before and after the test
1526
--source suite/funcs_1/storedproc/cleanup_sp_tb.inc
1528
# ==============================================================================
1529
let $message= . +++ END OF SCRIPT +++;
1530
--source include/show_msg80.inc
1531
# ==============================================================================
added
removed
mysql-test/suite/funcs_1/storedproc/storedproc_02.inc
