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
9
# BEGIN/END, DECLARE, SET, SELECT/INTO, OPEN, FETCH, CLOSE:
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
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
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
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
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
42
#- 16. Ensure that every possible type of variable -- utilizing every data type
43
# definition supported by the MySQL server in combination with both no
44
# DEFAULT subclause and with DEFAULT subclauses that set the variableĀs
45
# default value to a range of appropriate values -- may be declared for
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
53
#- 20. Ensure that invalid cursor declarations are rejected, with an appropriate
55
#- 21. Ensure that every possible type of condition may be declared for
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
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
66
## 26. Ensure that the initial value of every variable declared for a stored
67
# procedure is either NULL or its DEFAULT value, as appropriate.
68
#- 27. Ensure that the SET statement can assign a value to every local variable
69
# declared within a stored procedureĀs definition, as well as to every
70
# appropriate global server variable.
71
#- 28. Ensure that the SET statement can assign values to variables either
72
# separately or to multiple variables in a list.
73
#- 29. Ensure that the SET statement may assign only those values to a variable
74
# that are appropriate for that variableĀs data type definition.
75
## 30. Ensure that, when a stored procedure is called/executed, every variable
76
# always uses the correct value: either the value with which it is
77
# initialized or the value to which it is subsequently SET or otherwise
78
# assigned, as appropriate.
79
## 31. Ensure that the SELECT ... INTO statement properly assigns values to the
80
# variables in its variable list.
81
## 32. Ensure that a SELECT ... INTO statement that retrieves multiple rows is
82
# rejected, with an appropriate error message.
83
## 33. Ensure that a SELECT ... INTO statement that retrieves too many columns
84
# for the number of variables in its variable list is rejected, with an
85
# appropriate error message.
86
## 34. Ensure that a SELECT ... INTO statement that retrieves too few columns
87
# for the number of variables in its variable list is rejected, with an
88
# appropriate error message.
89
#- 35. Ensure that a SELECT ... INTO statement that retrieves column values
90
# with inappropriate data types for the matching variables in its variable
91
# list is rejected, with an appropriate error message.
92
#- 36. Ensure that the DECLARE ... CONDITION FOR statement can declare a
93
# properly-named condition for every possible SQLSTATE and MySQL-specific
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
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
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
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
157
#- 64. Ensure that FETCH <cursor name> fails unless a cursor with the same name
159
## 65. Ensure that FETCH <cursor name> returns the first row of the cursorĀs
160
# result set the first time FETCH is executed, that it returns each
161
# subsequent row of the cursorĀs result set each of the subsequent
162
# times FETCH is executed, and that it returns a NOT FOUND warning if it
163
# is executed after the last row of the cursorĀs result set has already
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
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
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
201
# ------------------------------------------------------------------------------
202
let $message= Testcase 3.1.2.8:;
203
--source include/show_msg.inc
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
210
DROP PROCEDURE IF EXISTS sp1;
214
CREATE PROCEDURE sp1( )
216
declare x char DEFAULT 'x';
217
declare y char DEFAULT 'y';
221
declare x char DEFAULT 'X';
222
declare y char DEFAULT 'Y';
223
SELECT f1, f2 into x, y from t2 limit 1;
226
declare x char default 'a';
227
declare y char default 'b';
229
declare x char default 'c';
230
declare y char default 'd';
232
declare x char default 'e';
233
declare y char default 'f';
235
declare x char default 'g';
236
declare y char default 'h';
248
SELECT '1.2', @v1, @v2;
255
DROP PROCEDURE IF EXISTS sp1;
258
# ------------------------------------------------------------------------------
259
let $message= Testcase 3.1.2.26:;
260
--source include/show_msg.inc
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
267
DROP PROCEDURE IF EXISTS sp1;
274
CREATE PROCEDURE sp1( )
276
declare x1 char default 'x';
278
declare x2 tinytext default 'tinytext';
280
declare x3 datetime default '2005-10-03 12:13:14';
282
declare x4 float default 1.2;
284
declare x5 blob default 'b';
286
declare x6 smallint default 127;
288
SELECT x1, x2, x3, x4, x5, x6, y1, y2, y3, y4, y5, y6;
298
# ------------------------------------------------------------------------------
299
let $message= Testcase 3.1.2.30:;
300
--source include/show_msg.inc
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
308
DROP PROCEDURE IF EXISTS sp1;
312
CREATE PROCEDURE sp1( IN invar INT, OUT outvar INT )
315
declare y integer default 1;
319
SELECT f1, f2 into @x, @y from t2 where f1='a`' and f2='a`' limit 1;
320
SELECT @x, @y, @z, invar;
324
SET outvar = @x * invar + @z * @f;
327
set @y = null, @z = 'abcd';
335
SET @outvar = @invar;
338
SELECT @x, @y, @z, @invar, @outvar;
340
CALL sp1( @invar, @outvar );
342
SELECT @x, @y, @z, @invar, @outvar;
348
# ------------------------------------------------------------------------------
349
let $message= Testcase 3.1.2.31:;
350
--source include/show_msg.inc
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
358
DROP PROCEDURE IF EXISTS sp1;
362
CREATE PROCEDURE sp1( )
364
declare x integer; declare y integer;
367
SELECT f4, f3 into @x, @y from t2 where f4=-5000 and f3='1000-01-01' limit 1;
378
# ------------------------------------------------------------------------------
379
let $message= Testcase 3.1.2.32:;
380
--source include/show_msg.inc
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
387
DROP PROCEDURE IF EXISTS sp1;
391
CREATE PROCEDURE sp1( )
393
declare x integer; declare y integer;
396
SELECT f4, f3 into @x, @y from t2;
400
# Error: SQLSTATE: 42000 (ER_TOO_MANY_ROWS)
401
# Message: Result consisted of more than one row
402
--error ER_TOO_MANY_ROWS
409
# ------------------------------------------------------------------------------
410
let $message= Testcase 3.1.2.33:;
411
--source include/show_msg.inc
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
416
--source include/show_msg80.inc
419
DROP PROCEDURE IF EXISTS sp1;
423
CREATE PROCEDURE sp1( )
425
declare x integer; declare y integer;
428
SELECT f4, f3, f2, f1 into @x, @y from t2;
432
--error ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT
439
# ------------------------------------------------------------------------------
440
let $message= Testcase 3.1.2.34:;
441
--source include/show_msg.inc
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
446
--source include/show_msg80.inc
449
DROP PROCEDURE IF EXISTS sp1;
453
CREATE PROCEDURE sp1( )
455
declare x integer; declare y integer; declare z integer;
459
SELECT f4 into @x, @y, @z from t2;
463
--error ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT
470
# ------------------------------------------------------------------------------
471
let $message= Testcase 3.1.2.38:;
472
--source include/show_msg.inc
474
Ensure that the scope of every condition declared is properly applied.;
475
--source include/show_msg80.inc
478
DROP PROCEDURE IF EXISTS h1;
479
DROP TABLE IF EXISTS res_t1;
482
create table res_t1(w char unique, x char);
484
insert into res_t1 values('a', 'b');
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'
492
CREATE PROCEDURE h1 ()
494
declare x1, x2, x3, x4, x5, x6 int default 0;
495
SELECT '-1-', x1, x2, x3, x4, x5, x6;
497
declare condname condition for sqlstate '23000';
498
declare continue handler for condname set x5 = 1;
500
insert into res_t1 values ('a', 'b');
502
SELECT '-2-', x1, x2, x3, x4, x5, x6;
506
declare condname condition for sqlstate '20000';
507
declare continue handler for condname set x1 = 1;
510
when 1 then set x2=10;
511
when 2 then set x2=11;
514
SELECT '-3-', x1, x2, x3, x4, x5, x6;
517
declare condname condition for sqlstate '23000';
518
declare exit handler for condname set x3 = 1;
520
SELECT '-4a', x1, x2, x3, x4, x5, x6;
521
insert into res_t1 values ('a', 'b');
523
SELECT '-4b', x1, x2, x3, x4, x5, x6;
525
SELECT '-5-', x1, x2, x3, x4, x5, x6;
527
SELECT '-6-', x1, x2, x3, x4, x5, x6;
529
SELECT '-7-', x1, x2, x3, x4, x5, x6;
531
SELECT 'END', x1, x2, x3, x4, x5, x6;
539
DROP TABLE IF EXISTS tnull;
540
DROP PROCEDURE IF EXISTS sp1;
543
CREATE TABLE tnull(f1 int);
546
CREATE PROCEDURE sp1()
548
declare cond1 condition for sqlstate '42S02';
549
declare continue handler for cond1 set @var2 = 1;
551
declare cond1 condition for sqlstate '23000';
552
declare continue handler for cond1 set @var2 = 1;
554
insert into tnull values(1);
567
# ------------------------------------------------------------------------------
568
let $message= Testcase 3.1.2.43:;
569
--source include/show_msg.inc
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
576
DROP PROCEDURE IF EXISTS h1;
577
DROP PROCEDURE IF EXISTS h2;
578
drop table IF EXISTS res_t1;
581
create table res_t1(w char unique, x char);
582
insert into res_t1 values ('a', 'b');
585
--error ER_SP_COND_MISMATCH
586
CREATE PROCEDURE h1 ()
588
declare x1, x2, x3, x4, x5, x6 int default 0;
590
declare cond_1 condition for sqlstate '23000';
591
declare continue handler for cond_1 set x5 = 1;
593
declare cond_2 condition for sqlstate '20000';
594
declare continue handler for cond_1 set x1 = 1;
596
declare continue handler for cond_2 set x3 = 1;
602
declare continue handler for cond_1 set x1 = 1;
604
declare continue handler for cond_2 set x3 = 1;
610
SELECT x1, x2, x3, x4, x5, x6;
613
CREATE PROCEDURE h2 ()
615
declare x1, x2, x3, x4, x5, x6 int default 0;
617
declare condname condition for sqlstate '23000';
618
declare continue handler for condname set x5 = 1;
620
declare condname condition for sqlstate '20000';
621
declare continue handler for condname set x1 = 1;
623
declare condname condition for sqlstate '42000';
624
declare continue handler for condname set x3 = 1;
626
insert into res_t1 values ('a', 'b');
633
when 1 then set x2=10;
634
when 2 then set x2=11;
640
when 1 then set x2=10;
641
when 2 then set x2=11;
645
insert into res_t1 values ('a', 'b');
649
SELECT x1, x2, x3, x4, x5, x6;
654
SELECT * FROM res_t1;
661
# ------------------------------------------------------------------------------
662
let $message= Testcase 3.1.2.44:;
663
--source include/show_msg.inc
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
670
DROP PROCEDURE IF EXISTS h1;
674
# Error: SQLSTATE: 42000 (ER_SP_COND_MISMATCH)
675
# Message: Undefined CONDITION: %s
676
--error ER_SP_COND_MISMATCH
677
CREATE PROCEDURE h1 ()
679
declare x1, x2, x3, x4, x5, x6 int default 0;
681
declare condname1 condition for sqlstate '23000';
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;
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;
696
# Error: SQLSTATE: 42000 (ER_PARSE_ERROR)
697
# Message: %s near '%s' at line %d
698
--error ER_PARSE_ERROR
699
CREATE PROCEDURE h1 ()
701
DECLARE x1 INT DEFAULT 0;
703
DECLARE condname1 CONDITION CHECK SQLSTATE '23000';
705
DECLARE CONTINUE HANDLER FOR condname1 SET x1 = 1;
708
# Error: SQLSTATE: 42000 (ER_SP_BAD_SQLSTATE)
709
# Message: Bad SQLSTATE: '%s'
710
--error ER_SP_BAD_SQLSTATE
711
CREATE PROCEDURE h1 ()
713
DECLARE x1 INT DEFAULT 0;
715
DECLARE condname1 CONDITION FOR SQLSTATE 'qwert';
717
DECLARE CONTINUE HANDLER FOR condname1 SET x1 = 1;
725
# ------------------------------------------------------------------------------
726
let $message= Testcase 3.1.2.45 + 3.1.2.50:;
727
--source include/show_msg.inc
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
735
# RefMan: For an EXIT handler, execution of the current BEGIN...END compound
736
# statement is terminated.
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;
746
--echo ==> 'UNDO' is still not supported.
748
--error ER_PARSE_ERROR
749
create procedure p1undo ()
751
declare undo handler for sqlexception select '1';
756
create procedure p1 ()
758
declare exit handler for sqlexception select 'exit handler 1';
760
declare exit handler for sqlexception select 'exit handler 2';
762
declare continue handler for sqlexception select 'continue handler 3';
763
drop table if exists tqq;
765
SELECT 'end of BEGIN/END 3';
767
drop table if exists tqq;
769
SELECT 'end of BEGIN/END 2';
772
SELECT 'end of BEGIN/END 1';
778
create table res_t1(w char unique, x char);
779
insert into res_t1 values ('a', 'b');
782
CREATE PROCEDURE h1 ()
784
declare x1, x2, x3, x4, x5, x6 int default 0;
786
declare continue handler for sqlstate '23000' set x5 = 1;
787
insert into res_t1 values ('a', 'b');
792
declare continue handler for sqlstate '23000' set x1 = 1;
793
insert into res_t1 values ('a', 'b');
797
declare exit handler for sqlstate '23000' set x3 = 1;
799
insert into res_t1 values ('a', 'b');
805
SELECT x1, x2, x3, x4, x5, x6;
811
--echo This will fail, SQLSTATE 00000 is not allowed
812
--ERROR ER_SP_BAD_SQLSTATE
814
CREATE PROCEDURE sp1()
816
declare exit handler for sqlstate '00000' set @var1 = 5;
819
declare continue handler for sqlstate '00000' set @var3 = 7;
827
--echo Verify SP wasn't created
828
--ERROR ER_SP_DOES_NOT_EXIST
831
# cleanup 3.1.2.45+50
835
DROP PROCEDURE IF EXISTS sp1;
840
# ------------------------------------------------------------------------------
841
let $message= Testcase 3.1.2.50:;
842
--source include/show_msg.inc
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).
850
DROP PROCEDURE IF EXISTS sp1;
851
DROP PROCEDURE IF EXISTS sp2;
855
CREATE PROCEDURE sp1 (x int, y int)
862
CREATE PROCEDURE sp2 ()
864
declare continue handler for sqlstate '42000' set @x2 = 1;
880
# ------------------------------------------------------------------------------
881
let $message= Testcase 3.2.2.51:;
882
--source include/show_msg.inc
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
887
--source include/show_msg80.inc
888
# also tested in 3.1.2.45
891
DROP PROCEDURE IF EXISTS sp1;
892
DROP PROCEDURE IF EXISTS sp2;
896
CREATE PROCEDURE sp1 (x int, y int)
903
CREATE PROCEDURE sp2 ()
905
declare exit handler for sqlstate '42000' set @x2 = 1;
908
SELECT '-1-', @x2, @x;
910
SELECT '-2-', @x2, @x;
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
920
SELECT '-3-', @x2, @x;
927
# ------------------------------------------------------------------------------
928
let $message= Testcase 3.1.2.52:;
929
--source include/show_msg.inc
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
937
DROP PROCEDURE IF EXISTS sp1;
938
DROP PROCEDURE IF EXISTS sp2;
942
CREATE PROCEDURE sp1 (x int, y int)
949
CREATE PROCEDURE sp2()
951
declare continue handler for sqlstate '42000' set @x2 = 2;
954
SELECT '-1-', @x2, @x;
956
declare exit handler for sqlstate '42000' set @x2 = 11;
957
SELECT '-2-', @x2, @x;
959
SELECT '-3a', @x2, @x;
961
SELECT '-3b', @x2, @x;
964
SELECT '-4-', @x2, @x;
975
# ------------------------------------------------------------------------------
976
let $message= Testcase 3.1.2.54:;
977
--source include/show_msg.inc
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
982
--source include/show_msg80.inc
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;
993
CREATE TABLE temp( f1 CHAR, f2 CHAR);
996
# 0 - without handler
997
CREATE PROCEDURE sp0()
1001
insert into temp values('xxx', 'yy');
1005
# 1st one with SQLSTATE + CONTINUE
1006
CREATE PROCEDURE sp1()
1008
declare continue handler for sqlstate '01000' set @done = 1;
1011
insert into temp values('xxx', 'yy');
1015
# 2nd one with SQLWARNING + CONTINUE
1016
CREATE PROCEDURE sp2()
1018
declare continue handler for sqlwarning set @done = 1;
1021
insert into temp values('xxx', 'yy');
1025
# 3 with SQLSTATE + EXIT
1026
CREATE PROCEDURE sp3()
1028
declare exit handler for sqlstate '01000' set @done = 1;
1031
insert into temp values('xxx', 'yy');
1035
# 4 with SQLWARNING + EXIT
1036
CREATE PROCEDURE sp4()
1038
declare exit handler for sqlwarning set @done = 1;
1041
insert into temp values('xxx', 'yy');
1046
INSERT INTO temp VALUES('0', NULL);
1050
INSERT INTO temp VALUES('1', NULL);
1054
INSERT INTO temp VALUES('2', NULL);
1058
INSERT INTO temp VALUES('3', NULL);
1062
INSERT INTO temp VALUES('4', NULL);
1076
# ------------------------------------------------------------------------------
1077
let $message= Testcase 3.1.2.56:;
1078
--source include/show_msg.inc
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
1083
--source include/show_msg80.inc
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;
1094
# 0 - wihtout handler
1095
CREATE PROCEDURE sp0()
1097
DECLARE f1_value CHAR(20);
1098
DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
1102
FETCH cur1 INTO f1_value;
1104
FETCH cur1 INTO f1_value;
1109
# 1st one with SQLSTATE + CONTINUE
1110
CREATE PROCEDURE sp1()
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;
1118
FETCH cur1 INTO f1_value;
1120
FETCH cur1 INTO f1_value;
1125
# 2nd one with NOT FOUND + CONTINUE
1126
CREATE PROCEDURE sp2()
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;
1134
FETCH cur1 INTO f1_value;
1136
FETCH cur1 INTO f1_value;
1141
# 3 with SQLSTATE + EXIT
1142
CREATE PROCEDURE sp3()
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;
1150
FETCH cur1 INTO f1_value;
1152
FETCH cur1 INTO f1_value;
1157
# 4 with NOT FOUND + EXIT
1158
CREATE PROCEDURE sp4()
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;
1166
FETCH cur1 INTO f1_value;
1168
FETCH cur1 INTO f1_value;
1174
--error ER_SP_FETCH_NO_DATA
1198
# ------------------------------------------------------------------------------
1199
let $message= Testcase 3.1.2.58:;
1200
--source include/show_msg.inc
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
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
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;
1223
# 0 - without handler
1224
CREATE PROCEDURE sp0()
1226
DECLARE f1_value CHAR(20);
1227
DECLARE cv INT DEFAULT 0;
1228
DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
1231
WHEN 2 THEN SET @x = 2;
1232
WHEN 3 THEN SET @x = 3;
1235
SELECT f1, f2 FROM t2
1237
SELECT f1, f2,3 FROM t2;
1239
FETCH cur1 INTO f1_value;
1243
# 1 - SQLSTATEs - CONTINUE
1244
CREATE PROCEDURE sp1()
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';
1254
WHEN 2 THEN SET @x = 2;
1255
WHEN 3 THEN SET @x = 3;
1258
SELECT f1, f2 FROM t2
1260
SELECT f1, f2,3 FROM t2;
1262
FETCH cur1 INTO f1_value;
1266
# 2 - SQLEXCEPTION matches 2 of 3 conditions - CONTINUE
1267
CREATE PROCEDURE sp2()
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';
1276
WHEN 2 THEN SET @x = 2;
1277
WHEN 3 THEN SET @x = 3;
1280
SELECT f1, f2 FROM t2
1282
SELECT f1, f2,3 FROM t2;
1284
FETCH cur1 INTO f1_value;
1288
# 3 - SQLSTATEs - EXIT
1289
CREATE PROCEDURE sp3()
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';
1299
WHEN 2 THEN SET @x = 2;
1300
WHEN 3 THEN SET @x = 3;
1303
SELECT f1, f2 FROM t2
1305
SELECT f1, f2,3 FROM t2;
1307
FETCH cur1 INTO f1_value;
1311
# 4 - SQLEXCEPTION matches 2 of 3 conditions - EXIT
1312
CREATE PROCEDURE sp4()
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';
1321
WHEN 2 THEN SET @x = 2;
1322
WHEN 3 THEN SET @x = 3;
1325
SELECT f1, f2 FROM t2
1327
SELECT f1, f2,3 FROM t2;
1329
FETCH cur1 INTO f1_value;
1358
# ------------------------------------------------------------------------------
1359
let $message= Testcase 3.1.2.65:;
1360
--source include/show_msg.inc
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
1370
DROP PROCEDURE IF EXISTS sp1;
1371
DROP TABLE IF EXISTS temp;
1382
INSERT INTO temp VALUES(0, 'onip', 'abc', 8760, 'xyz', 10);
1384
# NOT used: declare continue handler for sqlstate '02000' set proceed=0;
1385
# --> warning is shown when procedure is executed.
1387
CREATE PROCEDURE sp1( )
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;
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;
1408
--error ER_SP_FETCH_NO_DATA
1418
# ------------------------------------------------------------------------------
1419
let $message= Testcase 3.1.2.68:;
1420
--source include/show_msg.inc
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
1428
DROP PROCEDURE IF EXISTS sp1;
1429
DROP PROCEDURE IF EXISTS sp2;
1433
--echo --> not enough columns in FETCH statement
1434
CREATE PROCEDURE sp1( )
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 '';
1441
fetch cur1 into newf1;
1447
--echo --> too many columns in FETCH statement
1448
CREATE PROCEDURE sp2( )
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 '';
1456
fetch cur1 into newf1, newf2;
1457
SELECT newf1, newf2;
1463
--echo --> not enough columns in FETCH statement
1464
--error ER_SP_WRONG_NO_OF_FETCH_ARGS
1467
--echo --> too many columns in FETCH statement
1468
--error ER_SP_WRONG_NO_OF_FETCH_ARGS
1476
# ------------------------------------------------------------------------------
1477
let $message= Testcase 3.1.2.75:;
1478
--source include/show_msg.inc
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
1486
DROP TABLE IF EXISTS temp1;
1487
DROP PROCEDURE IF EXISTS sp1;
1490
create table temp1( f0 char(20), f1 char(20), f2 char(20), f3 int, f4 char(20) );
1492
# Error: SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)
1493
# Message: No data to FETCH
1495
SELECT f1, f2, f4, f5 from t2 order by f4;
1498
CREATE PROCEDURE sp1( )
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;
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);
1518
fetch cur1 into newf1, newf2, newf4, newf5;
1519
SELECT '-2-', count, newf1, newf2, newf4, newf5;
1520
set count = count - 1;
1522
SELECT '-3-', count, newf1, newf2, newf4, newf4;
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);
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);
1534
fetch cur2 into newf1, newf2, newf4, newf5;
1535
SELECT '-6-', newf1, newf2, newf4, newf5;
1542
SELECT * from temp1;
1549
# ------------------------------------------------------------------------------
1550
let $message= Testcase 3.1.2.76:;
1551
--source include/show_msg.inc
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
1559
DROP PROCEDURE IF EXISTS sp1;
1560
drop table IF EXISTS temp1;
1561
drop table IF EXISTS temp2;
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 );
1568
CREATE PROCEDURE sp_inner( )
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;
1589
fetch cur1 into i_newf1, i_newf2, i_newf3, i_newf4;
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;
1594
insert into temp2 values ('sp_inner', i_count, i_newf11, i_newf12, i_newf13, i_newf14);
1597
set i_count = i_count - 1;
1603
CREATE PROCEDURE sp_outer( )
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;
1624
FETCH cur1 INTO o_newf1, o_newf2, o_newf3, o_newf4;
1626
INSERT INTO temp1 VALUES ('_sp_out_', o_count, o_newf1, o_newf2, o_newf3, o_newf4);
1628
FETCH cur2 INTO o_newf11, o_newf12, o_newf13, o_newf14;
1630
INSERT INTO temp2 VALUES ('_sp_out_', o_count, o_newf11, o_newf12, o_newf13, o_newf14);
1633
SET o_count = o_count + 1;
1642
SELECT * FROM temp1;
1643
SELECT * FROM temp2;
1646
DROP PROCEDURE sp_outer;
1647
DROP PROCEDURE sp_inner;
1652
# ==============================================================================
1653
# USE the same .inc to cleanup before and after the test
1654
--source suite/funcs_1/storedproc/cleanup_sp_tb.inc
1656
# ==============================================================================
1658
--echo . +++ END OF SCRIPT +++
1659
--echo --------------------------------------------------------------------------------
1660
# ==============================================================================
added
removed
mysql-test/suite/funcs_1/storedproc/storedproc_02.inc
