2
--source suite/funcs_1/storedproc/load_sp_tb.inc
3
--------------------------------------------------------------------------------
5
--source suite/funcs_1/storedproc/cleanup_sp_tb.inc
6
--------------------------------------------------------------------------------
7
DROP DATABASE IF EXISTS db_storedproc;
8
DROP DATABASE IF EXISTS db_storedproc_1;
9
CREATE DATABASE db_storedproc;
10
CREATE DATABASE db_storedproc_1;
12
create table t1(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = innodb;
13
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t1;
14
create table t2(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = innodb;
15
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t2;
16
create table t3(f1 char(20),f2 char(20),f3 integer) engine = innodb;
17
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t3.txt' into table t3;
18
create table t4(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = innodb;
19
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t4;
21
create table t6(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = innodb;
22
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t6;
24
create table t7 (f1 char(20), f2 char(25), f3 date, f4 int) engine = innodb;
25
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t7.txt' into table t7;
27
Warning 1265 Data truncated for column 'f3' at row 1
28
Warning 1265 Data truncated for column 'f3' at row 2
29
Warning 1265 Data truncated for column 'f3' at row 3
30
Warning 1265 Data truncated for column 'f3' at row 4
31
Warning 1265 Data truncated for column 'f3' at row 5
32
Warning 1265 Data truncated for column 'f3' at row 6
33
Warning 1265 Data truncated for column 'f3' at row 7
34
Warning 1265 Data truncated for column 'f3' at row 8
35
Warning 1265 Data truncated for column 'f3' at row 9
36
Warning 1265 Data truncated for column 'f3' at row 10
37
create table t8 (f1 char(20), f2 char(25), f3 date, f4 int) engine = innodb;
38
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t7.txt' into table t8;
40
Warning 1265 Data truncated for column 'f3' at row 1
41
Warning 1265 Data truncated for column 'f3' at row 2
42
Warning 1265 Data truncated for column 'f3' at row 3
43
Warning 1265 Data truncated for column 'f3' at row 4
44
Warning 1265 Data truncated for column 'f3' at row 5
45
Warning 1265 Data truncated for column 'f3' at row 6
46
Warning 1265 Data truncated for column 'f3' at row 7
47
Warning 1265 Data truncated for column 'f3' at row 8
48
Warning 1265 Data truncated for column 'f3' at row 9
49
Warning 1265 Data truncated for column 'f3' at row 10
50
create table t9(f1 int, f2 char(25), f3 int) engine = innodb;
51
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t9.txt' into table t9;
52
create table t10(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = innodb;
53
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t10;
54
create table t11(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = innodb;
55
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t11;
57
Section 3.1.2 - Syntax checks for the stored procedure-specific
58
programming statements BEGIN/END, DECLARE, SET, SELECT/INTO, OPEN, FETCH, CLOSE:
59
--------------------------------------------------------------------------------
64
Ensure that the scope of each BEGIN/END compound statement within a stored
65
procedure definition is properly applied
66
--------------------------------------------------------------------------------
67
DROP PROCEDURE IF EXISTS sp1;
68
CREATE PROCEDURE sp1( )
70
declare x char DEFAULT 'x';
71
declare y char DEFAULT 'y';
75
declare x char DEFAULT 'X';
76
declare y char DEFAULT 'Y';
77
SELECT f1, f2 into x, y from t2 limit 1;
80
declare x char default 'a';
81
declare y char default 'b';
83
declare x char default 'c';
84
declare y char default 'd';
86
declare x char default 'e';
87
declare y char default 'f';
89
declare x char default 'g';
90
declare y char default 'h';
102
SELECT '1.2', @v1, @v2;
118
Warning 1265 Data truncated for column 'x' at row 1
119
Warning 1265 Data truncated for column 'y' at row 1
120
DROP PROCEDURE IF EXISTS sp1;
125
Ensure that the initial value of every variable declared for a stored procedure
126
is either NULL or its DEFAULT value, as appropriate.
127
--------------------------------------------------------------------------------
128
DROP PROCEDURE IF EXISTS sp1;
131
CREATE PROCEDURE sp1( )
133
declare x1 char default 'x';
135
declare x2 tinytext default 'tinytext';
137
declare x3 datetime default '2005-10-03 12:13:14';
139
declare x4 float default 1.2;
141
declare x5 blob default 'b';
143
declare x6 smallint default 127;
145
SELECT x1, x2, x3, x4, x5, x6, y1, y2, y3, y4, y5, y6;
148
x1 x2 x3 x4 x5 x6 y1 y2 y3 y4 y5 y6
149
x tinytext 2005-10-03 12:13:14 1.2 b 127 NULL NULL NULL NULL NULL NULL
155
Ensure that, when a stored procedure is called/executed, every variable always
156
uses the correct value: either the value with which it is initialized or the
157
value to which it is subsequently SET or otherwise assigned, as appropriate.
158
--------------------------------------------------------------------------------
159
DROP PROCEDURE IF EXISTS sp1;
160
CREATE PROCEDURE sp1( IN invar INT, OUT outvar INT )
163
declare y integer default 1;
167
SELECT f1, f2 into @x, @y from t2 where f1='a`' and f2='a`' limit 1;
168
SELECT @x, @y, @z, invar;
172
SET outvar = @x * invar + @z * @f;
175
set @y = null, @z = 'abcd';
181
SET @outvar = @invar;
183
SELECT @x, @y, @z, @invar, @outvar;
184
@x @y @z @invar @outvar
185
NULL NULL NULL 100 100
186
CALL sp1( @invar, @outvar );
193
SELECT @x, @y, @z, @invar, @outvar;
194
@x @y @z @invar @outvar
201
Ensure that the SELECT ... INTO statement properly assigns values to the
202
variables in its variable list.
203
--------------------------------------------------------------------------------
204
DROP PROCEDURE IF EXISTS sp1;
205
CREATE PROCEDURE sp1( )
207
declare x integer; declare y integer;
210
SELECT f4, f3 into @x, @y from t2 where f4=-5000 and f3='1000-01-01' limit 1;
221
Ensure that a SELECT ... INTO statement that retrieves multiple rows is
222
rejected, with an appropriate error message.
223
--------------------------------------------------------------------------------
224
DROP PROCEDURE IF EXISTS sp1;
225
CREATE PROCEDURE sp1( )
227
declare x integer; declare y integer;
230
SELECT f4, f3 into @x, @y from t2;
233
ERROR 42000: Result consisted of more than one row
239
Ensure that a SELECT ... INTO statement that retrieves too many columns for the
240
number of variables in its variable list is rejected, with an appropriate error
242
--------------------------------------------------------------------------------
243
DROP PROCEDURE IF EXISTS sp1;
244
CREATE PROCEDURE sp1( )
246
declare x integer; declare y integer;
249
SELECT f4, f3, f2, f1 into @x, @y from t2;
252
ERROR 21000: The used SELECT statements have a different number of columns
258
Ensure that a SELECT ... INTO statement that retrieves too few columns for the
259
number of variables in its variable list is rejected, with an appropriate error
261
--------------------------------------------------------------------------------
262
DROP PROCEDURE IF EXISTS sp1;
263
CREATE PROCEDURE sp1( )
265
declare x integer; declare y integer; declare z integer;
269
SELECT f4 into @x, @y, @z from t2;
272
ERROR 21000: The used SELECT statements have a different number of columns
278
Ensure that the scope of every condition declared is properly applied.
279
--------------------------------------------------------------------------------
280
DROP PROCEDURE IF EXISTS h1;
281
DROP TABLE IF EXISTS res_t1;
282
create table res_t1(w char unique, x char);
283
insert into res_t1 values('a', 'b');
284
CREATE PROCEDURE h1 ()
286
declare x1, x2, x3, x4, x5, x6 int default 0;
287
SELECT '-1-', x1, x2, x3, x4, x5, x6;
289
declare condname condition for sqlstate '23000';
290
declare continue handler for condname set x5 = 1;
292
insert into res_t1 values ('a', 'b');
294
SELECT '-2-', x1, x2, x3, x4, x5, x6;
298
declare condname condition for sqlstate '20000';
299
declare continue handler for condname set x1 = 1;
302
when 1 then set x2=10;
303
when 2 then set x2=11;
306
SELECT '-3-', x1, x2, x3, x4, x5, x6;
309
declare condname condition for sqlstate '23000';
310
declare exit handler for condname set x3 = 1;
312
SELECT '-4a', x1, x2, x3, x4, x5, x6;
313
insert into res_t1 values ('a', 'b');
315
SELECT '-4b', x1, x2, x3, x4, x5, x6;
317
SELECT '-5-', x1, x2, x3, x4, x5, x6;
319
SELECT '-6-', x1, x2, x3, x4, x5, x6;
321
SELECT '-7-', x1, x2, x3, x4, x5, x6;
323
SELECT 'END', x1, x2, x3, x4, x5, x6;
326
-1- x1 x2 x3 x4 x5 x6
328
-2- x1 x2 x3 x4 x5 x6
330
-3- x1 x2 x3 x4 x5 x6
332
-4a x1 x2 x3 x4 x5 x6
334
-5- x1 x2 x3 x4 x5 x6
336
-6- x1 x2 x3 x4 x5 x6
338
-7- x1 x2 x3 x4 x5 x6
340
END x1 x2 x3 x4 x5 x6
342
DROP TABLE IF EXISTS tnull;
343
DROP PROCEDURE IF EXISTS sp1;
344
CREATE TABLE tnull(f1 int);
345
CREATE PROCEDURE sp1()
347
declare cond1 condition for sqlstate '42S02';
348
declare continue handler for cond1 set @var2 = 1;
350
declare cond1 condition for sqlstate '23000';
351
declare continue handler for cond1 set @var2 = 1;
353
insert into tnull values(1);
364
Ensure that the DECLARE ... HANDLER FOR statement can not declare any handler
365
for a condition declared outside of the scope of the handler.
366
--------------------------------------------------------------------------------
367
DROP PROCEDURE IF EXISTS h1;
368
DROP PROCEDURE IF EXISTS h2;
369
drop table IF EXISTS res_t1;
370
create table res_t1(w char unique, x char);
371
insert into res_t1 values ('a', 'b');
372
CREATE PROCEDURE h1 ()
374
declare x1, x2, x3, x4, x5, x6 int default 0;
376
declare cond_1 condition for sqlstate '23000';
377
declare continue handler for cond_1 set x5 = 1;
379
declare cond_2 condition for sqlstate '20000';
380
declare continue handler for cond_1 set x1 = 1;
382
declare continue handler for cond_2 set x3 = 1;
388
declare continue handler for cond_1 set x1 = 1;
390
declare continue handler for cond_2 set x3 = 1;
396
SELECT x1, x2, x3, x4, x5, x6;
398
ERROR 42000: Undefined CONDITION: cond_2
399
CREATE PROCEDURE h2 ()
401
declare x1, x2, x3, x4, x5, x6 int default 0;
403
declare condname condition for sqlstate '23000';
404
declare continue handler for condname set x5 = 1;
406
declare condname condition for sqlstate '20000';
407
declare continue handler for condname set x1 = 1;
409
declare condname condition for sqlstate '42000';
410
declare continue handler for condname set x3 = 1;
412
insert into res_t1 values ('a', 'b');
419
when 1 then set x2=10;
420
when 2 then set x2=11;
426
when 1 then set x2=10;
427
when 2 then set x2=11;
431
insert into res_t1 values ('a', 'b');
435
SELECT x1, x2, x3, x4, x5, x6;
440
SELECT * FROM res_t1;
449
Ensure that the DECLARE ... HANDLER FOR statement cannot declare a handler for
450
any invalid, or undeclared, condition.
451
--------------------------------------------------------------------------------
452
DROP PROCEDURE IF EXISTS h1;
453
CREATE PROCEDURE h1 ()
455
declare x1, x2, x3, x4, x5, x6 int default 0;
457
declare condname1 condition for sqlstate '23000';
459
declare condname2 condition for sqlstate '20000';
460
declare continue handler for condname1 set x3 = 1;
461
declare continue handler for condname2 set x1 = 1;
465
declare condname3 condition for sqlstate '42000';
466
declare continue handler for condname1 set x3 = 1;
467
declare continue handler for condname2 set x5 = 1;
468
declare continue handler for condname3 set x1 = 1;
471
ERROR 42000: Undefined CONDITION: condname1
472
CREATE PROCEDURE h1 ()
474
DECLARE x1 INT DEFAULT 0;
476
DECLARE condname1 CONDITION CHECK SQLSTATE '23000';
478
DECLARE CONTINUE HANDLER FOR condname1 SET x1 = 1;
480
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHECK SQLSTATE '23000';
482
DECLARE CONTINUE HANDLER FOR condname1 SET x1 = 1;
484
CREATE PROCEDURE h1 ()
486
DECLARE x1 INT DEFAULT 0;
488
DECLARE condname1 CONDITION FOR SQLSTATE 'qwert';
490
DECLARE CONTINUE HANDLER FOR condname1 SET x1 = 1;
492
ERROR 42000: Bad SQLSTATE: 'qwert'
494
Testcase 3.1.2.45 + 3.1.2.50:
495
-----------------------------
497
45. Ensure that the scope of every handler declared is properly applied.
498
50. Ensure that a CONTINUE handler allows the execution of the stored procedure
499
. to continue once the handler statement has completed its own execution (that
500
. is, once the handler action statement has been executed).
501
--------------------------------------------------------------------------------
502
DROP PROCEDURE IF EXISTS p1;
503
DROP PROCEDURE IF EXISTS p1undo;
504
DROP PROCEDURE IF EXISTS h1;
505
DROP PROCEDURE IF EXISTS sp1;
506
drop table IF EXISTS res_t1;
507
==> 'UNDO' is still not supported.
508
create procedure p1undo ()
510
declare undo handler for sqlexception select '1';
514
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'undo handler for sqlexception select '1';
518
create procedure p1 ()
520
declare exit handler for sqlexception select 'exit handler 1';
522
declare exit handler for sqlexception select 'exit handler 2';
524
declare continue handler for sqlexception select 'continue handler 3';
525
drop table if exists tqq;
527
SELECT 'end of BEGIN/END 3';
529
drop table if exists tqq;
531
SELECT 'end of BEGIN/END 2';
534
SELECT 'end of BEGIN/END 1';
546
Note 1051 Unknown table 'tqq'
547
Note 1051 Unknown table 'tqq'
548
create table res_t1(w char unique, x char);
549
insert into res_t1 values ('a', 'b');
550
CREATE PROCEDURE h1 ()
552
declare x1, x2, x3, x4, x5, x6 int default 0;
554
declare continue handler for sqlstate '23000' set x5 = 1;
555
insert into res_t1 values ('a', 'b');
560
declare continue handler for sqlstate '23000' set x1 = 1;
561
insert into res_t1 values ('a', 'b');
565
declare exit handler for sqlstate '23000' set x3 = 1;
567
insert into res_t1 values ('a', 'b');
573
SELECT x1, x2, x3, x4, x5, x6;
578
CREATE PROCEDURE sp1()
580
declare exit handler for sqlstate '00000' set @var1 = 5;
583
declare continue handler for sqlstate '00000' set @var3 = 7;
601
DROP PROCEDURE IF EXISTS sp1;
602
DROP PROCEDURE IF EXISTS sp2;
603
CREATE PROCEDURE sp1 (x int, y int)
607
CREATE PROCEDURE sp2 ()
609
declare continue handler for sqlstate '42000' set @x2 = 1;
627
Ensure that an EXIT handler causes the execution of the stored procedure to
628
terminate, within its scope, once the handler action statement has been
630
--------------------------------------------------------------------------------
631
DROP PROCEDURE IF EXISTS sp1;
632
DROP PROCEDURE IF EXISTS sp2;
633
CREATE PROCEDURE sp1 (x int, y int)
637
CREATE PROCEDURE sp2 ()
639
declare exit handler for sqlstate '42000' set @x2 = 1;
642
SELECT '-1-', @x2, @x;
644
SELECT '-2-', @x2, @x;
648
ERROR 42000: Incorrect number of arguments for PROCEDURE db_storedproc.sp1; expected 2, got 1
652
SELECT '-3-', @x2, @x;
661
Ensure that an EXIT handler does not cause the execution of the stored procedure
662
to terminate outside of its scope.
663
--------------------------------------------------------------------------------
664
DROP PROCEDURE IF EXISTS sp1;
665
DROP PROCEDURE IF EXISTS sp2;
666
CREATE PROCEDURE sp1 (x int, y int)
670
CREATE PROCEDURE sp2()
672
declare continue handler for sqlstate '42000' set @x2 = 2;
675
SELECT '-1-', @x2, @x;
677
declare exit handler for sqlstate '42000' set @x2 = 11;
678
SELECT '-2-', @x2, @x;
680
SELECT '-3a', @x2, @x;
682
SELECT '-3b', @x2, @x;
685
SELECT '-4-', @x2, @x;
700
Ensure that a handler with a condition defined with an SQLSTATE that begins with
701
�01� is always exactly equivalent in action to a handler with an SQLWARNING
703
--------------------------------------------------------------------------------
704
DROP PROCEDURE IF EXISTS sp0;
705
DROP PROCEDURE IF EXISTS sp1;
706
DROP PROCEDURE IF EXISTS sp2;
707
DROP PROCEDURE IF EXISTS sp3;
708
DROP PROCEDURE IF EXISTS sp4;
709
DROP TABLE IF EXISTS temp;
710
CREATE TABLE temp( f1 CHAR, f2 CHAR);
711
CREATE PROCEDURE sp0()
715
insert into temp values('xxx', 'yy');
718
CREATE PROCEDURE sp1()
720
declare continue handler for sqlstate '01000' set @done = 1;
723
insert into temp values('xxx', 'yy');
726
CREATE PROCEDURE sp2()
728
declare continue handler for sqlwarning set @done = 1;
731
insert into temp values('xxx', 'yy');
734
CREATE PROCEDURE sp3()
736
declare exit handler for sqlstate '01000' set @done = 1;
739
insert into temp values('xxx', 'yy');
742
CREATE PROCEDURE sp4()
744
declare exit handler for sqlwarning set @done = 1;
747
insert into temp values('xxx', 'yy');
750
INSERT INTO temp VALUES('0', NULL);
753
Warning 1265 Data truncated for column 'f1' at row 1
754
Warning 1265 Data truncated for column 'f2' at row 1
758
INSERT INTO temp VALUES('1', NULL);
763
INSERT INTO temp VALUES('2', NULL);
768
INSERT INTO temp VALUES('3', NULL);
773
INSERT INTO temp VALUES('4', NULL);
799
Ensure that a handler with a condition defined with an SQLSTATE that begins with
800
�02� is always exactly equivalent in action to a handler with a NOT FOUND
802
--------------------------------------------------------------------------------
803
DROP PROCEDURE IF EXISTS sp0;
804
DROP PROCEDURE IF EXISTS sp1;
805
DROP PROCEDURE IF EXISTS sp2;
806
DROP PROCEDURE IF EXISTS sp3;
807
DROP PROCEDURE IF EXISTS sp4;
808
CREATE PROCEDURE sp0()
810
DECLARE f1_value CHAR(20);
811
DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
815
FETCH cur1 INTO f1_value;
817
FETCH cur1 INTO f1_value;
821
CREATE PROCEDURE sp1()
823
DECLARE f1_value CHAR(20);
824
DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
825
declare continue handler for sqlstate '02000' set @done = 1;
829
FETCH cur1 INTO f1_value;
831
FETCH cur1 INTO f1_value;
835
CREATE PROCEDURE sp2()
837
DECLARE f1_value CHAR(20);
838
DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
839
declare continue handler for not found set @done = 1;
843
FETCH cur1 INTO f1_value;
845
FETCH cur1 INTO f1_value;
849
CREATE PROCEDURE sp3()
851
DECLARE f1_value CHAR(20);
852
DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
853
declare exit handler for sqlstate '02000' set @done = 1;
857
FETCH cur1 INTO f1_value;
859
FETCH cur1 INTO f1_value;
863
CREATE PROCEDURE sp4()
865
DECLARE f1_value CHAR(20);
866
DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
867
declare exit handler for not found set @done = 1;
871
FETCH cur1 INTO f1_value;
873
FETCH cur1 INTO f1_value;
878
ERROR 02000: No data - zero rows fetched, selected, or processed
907
Ensure that a handler with a condition defined with an SQLSTATE that begins with
908
anything other that �01� or �02� is always exactly equivalent in action to a
909
handler with an SQLEXCEPTION condition.
910
--------------------------------------------------------------------------------
911
DROP PROCEDURE IF EXISTS sp0;
912
DROP PROCEDURE IF EXISTS sp1;
913
DROP PROCEDURE IF EXISTS sp2;
914
DROP PROCEDURE IF EXISTS sp3;
915
DROP PROCEDURE IF EXISTS sp4;
916
CREATE PROCEDURE sp0()
918
DECLARE f1_value CHAR(20);
919
DECLARE cv INT DEFAULT 0;
920
DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
923
WHEN 2 THEN SET @x = 2;
924
WHEN 3 THEN SET @x = 3;
927
SELECT f1, f2 FROM t2
929
SELECT f1, f2,3 FROM t2;
931
FETCH cur1 INTO f1_value;
934
CREATE PROCEDURE sp1()
936
DECLARE f1_value CHAR(20);
937
DECLARE cv INT DEFAULT 0;
938
DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
939
DECLARE continue HANDLER FOR SQLSTATE '20000' SELECT '20000' AS 'SQLSTATE';
940
DECLARE continue HANDLER FOR SQLSTATE '21000' SELECT '21000' AS 'SQLSTATE';
941
DECLARE continue HANDLER FOR SQLSTATE '24000' SELECT '24000' AS 'SQLSTATE';
944
WHEN 2 THEN SET @x = 2;
945
WHEN 3 THEN SET @x = 3;
948
SELECT f1, f2 FROM t2
950
SELECT f1, f2,3 FROM t2;
952
FETCH cur1 INTO f1_value;
955
CREATE PROCEDURE sp2()
957
DECLARE f1_value CHAR(20);
958
DECLARE cv INT DEFAULT 0;
959
DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
960
DECLARE continue HANDLER FOR SQLEXCEPTION SELECT 'SQLEXCEPTION' AS 'SQLSTATE';
961
DECLARE continue HANDLER FOR SQLSTATE '24000' SELECT '24000' AS 'SQLSTATE';
964
WHEN 2 THEN SET @x = 2;
965
WHEN 3 THEN SET @x = 3;
968
SELECT f1, f2 FROM t2
970
SELECT f1, f2,3 FROM t2;
972
FETCH cur1 INTO f1_value;
975
CREATE PROCEDURE sp3()
977
DECLARE f1_value CHAR(20);
978
DECLARE cv INT DEFAULT 0;
979
DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
980
DECLARE EXIT HANDLER FOR SQLSTATE '20000' SELECT '20000' AS 'SQLSTATE';
981
DECLARE EXIT HANDLER FOR SQLSTATE '21000' SELECT '21000' AS 'SQLSTATE';
982
DECLARE EXIT HANDLER FOR SQLSTATE '24000' SELECT '24000' AS 'SQLSTATE';
985
WHEN 2 THEN SET @x = 2;
986
WHEN 3 THEN SET @x = 3;
989
SELECT f1, f2 FROM t2
991
SELECT f1, f2,3 FROM t2;
993
FETCH cur1 INTO f1_value;
996
CREATE PROCEDURE sp4()
998
DECLARE f1_value CHAR(20);
999
DECLARE cv INT DEFAULT 0;
1000
DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
1001
DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT 'SQLEXCEPTION' AS 'SQLSTATE';
1002
DECLARE EXIT HANDLER FOR SQLSTATE '24000' SELECT '24000' AS 'SQLSTATE';
1005
WHEN 2 THEN SET @x = 2;
1006
WHEN 3 THEN SET @x = 3;
1009
SELECT f1, f2 FROM t2
1011
SELECT f1, f2,3 FROM t2;
1013
FETCH cur1 INTO f1_value;
1018
ERROR 20000: Case not found for CASE statement
1063
Ensure that FETCH <cursor name> returns the first row of the cursor_s result set
1064
the first time FETCH is executed, that it returns each subsequent row of the
1065
cursor_s result set each of the subsequent times FETCH is executed, and that it
1066
returns a NOT FOUND warning if it is executed after the last row of the cursor_s
1067
result set has already been fetched.
1068
--------------------------------------------------------------------------------
1069
DROP PROCEDURE IF EXISTS sp1;
1070
DROP TABLE IF EXISTS temp;
1078
INSERT INTO temp VALUES(0, 'onip', 'abc', 8760, 'xyz', 10);
1079
CREATE PROCEDURE sp1( )
1081
declare proceed int default 1;
1082
declare count integer default 1;
1083
declare f1_value char(20);
1084
declare f2_value char(20);
1085
declare f5_value char(20);
1086
declare f4_value integer;
1087
declare f6_value integer;
1088
declare cur1 cursor for SELECT f1, f2, f4, f5, f6 from t2
1089
where f4 >=-5000 order by f4 limit 3;
1092
SELECT count AS 'loop';
1093
fetch cur1 into f1_value, f2_value, f4_value, f5_value, f6_value;
1094
insert into temp values (count, f1_value, f2_value, f4_value, f5_value, f6_value);
1095
set count = count + 1;
1107
ERROR 02000: No data - zero rows fetched, selected, or processed
1110
0 onip abc 8760 xyz 10
1111
1 a` a` -5000 a` -5000
1112
2 aaa aaa -4999 aaa -4999
1113
3 abaa abaa -4998 abaa -4998
1120
Ensure that FETCH <cursor name> fails with an appropriate error message if the
1121
number of columns to be fetched does not match the number of variables specified
1122
by the FETCH statement.
1123
--------------------------------------------------------------------------------
1124
DROP PROCEDURE IF EXISTS sp1;
1125
DROP PROCEDURE IF EXISTS sp2;
1126
--> not enough columns in FETCH statement
1127
CREATE PROCEDURE sp1( )
1129
declare newf1 char(20);
1130
declare cur1 cursor for SELECT f1, f2 from t2 limit 10;
1131
declare continue handler for sqlstate '02000' SELECT 'HANDLER executed.' AS '';
1134
fetch cur1 into newf1;
1139
--> too many columns in FETCH statement
1140
CREATE PROCEDURE sp2( )
1142
declare newf1 char(20);
1143
declare newf2 char(20);
1144
declare cur1 cursor for SELECT f1 from t2 limit 10;
1145
declare continue handler for sqlstate '02000' SELECT 'HANDLER executed.' AS '';
1148
fetch cur1 into newf1, newf2;
1149
SELECT newf1, newf2;
1153
--> not enough columns in FETCH statement
1155
ERROR HY000: Incorrect number of FETCH variables
1156
--> too many columns in FETCH statement
1158
ERROR HY000: Incorrect number of FETCH variables
1165
Ensure that, for nested compound statements, a cursor that was declared and
1166
opened during an outer level of the statement is not closed when an inner level
1167
of a compound statement ends.
1168
--------------------------------------------------------------------------------
1169
DROP TABLE IF EXISTS temp1;
1170
DROP PROCEDURE IF EXISTS sp1;
1171
create table temp1( f0 char(20), f1 char(20), f2 char(20), f3 int, f4 char(20) );
1172
SELECT f1, f2, f4, f5 from t2 order by f4;
1176
abaa abaa -4998 abaa
1177
acaaa acaaa -4997 acaaa
1178
adaaaa adaaaa -4996 adaaaa
1179
aeaaaaa aeaaaaa -4995 aeaaaaa
1180
afaaaaaa afaaaaaa -4994 afaaaaaa
1181
agaaaaaaa agaaaaaaa -4993 agaaaaaaa
1182
a^aaaaaaaa a^aaaaaaaa -4992 a^aaaaaaaa
1183
a_aaaaaaaaa a_aaaaaaaaa -4991 a_aaaaaaaaa
1184
CREATE PROCEDURE sp1( )
1186
declare count integer;
1187
declare from0 char(20);
1188
declare newf1 char(20);
1189
declare newf2 char(20);
1190
declare newf5 char(20);
1191
declare newf4 integer;
1192
declare cur1 cursor for SELECT f1, f2, f4, f5 from t2 where f4 >= -5000 order by f4 limit 5;
1193
declare cur2 cursor for SELECT f1, f2, f4, f5 from t2 where f4 >= -5000 order by f4 limit 5;
1197
declare continue handler for sqlstate '02000' set count=1;
1198
fetch cur1 into newf1, newf2, newf4, newf5;
1199
SELECT '-1-', count, newf1, newf2, newf4, newf5;
1200
insert into temp1 values ('cur1_out', newf1, newf2, newf4, newf5);
1204
fetch cur1 into newf1, newf2, newf4, newf5;
1205
SELECT '-2-', count, newf1, newf2, newf4, newf5;
1206
set count = count- 1;
1208
SELECT '-3-', count, newf1, newf2, newf4, newf4;
1211
fetch cur1 into newf1, newf2, newf4, newf5;
1212
SELECT '-4-', newf1, newf2, newf4, newf5;
1213
insert into temp1 values ('cur1_in', newf1, newf2, newf4, newf5);
1215
fetch cur2 into newf1, newf2, newf4, newf5;
1216
SELECT '-5-', newf1, newf2, newf4, newf5;
1217
insert into temp1 values ('cur2', newf1, newf2, newf4, newf5);
1220
fetch cur2 into newf1, newf2, newf4, newf5;
1221
SELECT '-6-', newf1, newf2, newf4, newf5;
1225
-1- count newf1 newf2 newf4 newf5
1226
-1- NULL a` a` -5000 a`
1227
-2- count newf1 newf2 newf4 newf5
1228
-2- 4 aaa aaa -4999 aaa
1229
-2- count newf1 newf2 newf4 newf5
1230
-2- 3 abaa abaa -4998 abaa
1231
-2- count newf1 newf2 newf4 newf5
1232
-2- 2 acaaa acaaa -4997 acaaa
1233
-2- count newf1 newf2 newf4 newf5
1234
-2- 1 adaaaa adaaaa -4996 adaaaa
1235
-3- count newf1 newf2 newf4 newf4
1236
-3- 0 adaaaa adaaaa -4996 -4996
1237
-4- newf1 newf2 newf4 newf5
1238
-4- adaaaa adaaaa -4996 adaaaa
1239
-5- newf1 newf2 newf4 newf5
1241
-6- newf1 newf2 newf4 newf5
1242
-6- aaa aaa -4999 aaa
1243
SELECT * from temp1;
1245
cur1_out a` a` -5000 a`
1246
cur1_in adaaaa adaaaa -4996 adaaaa
1254
Ensure that all cursors operate asensitively, so that there is no concurrency
1255
conflict between cursors operating on the same, or similar, sets of results
1256
during execution of one or more stored procedures.
1257
--------------------------------------------------------------------------------
1258
DROP PROCEDURE IF EXISTS sp1;
1259
drop table IF EXISTS temp1;
1260
drop table IF EXISTS temp2;
1261
create table temp1( f0 char(10), cnt int, f1 char(20), f2 char(20), f3 date, f4 integer );
1262
create table temp2( f0 char(10), cnt int, f1 char(20), f2 char(20), f3 date, f4 integer );
1263
CREATE PROCEDURE sp_inner( )
1265
declare proceed int default 1;
1266
declare i_count integer default 20;
1267
declare i_newf1 char(20);
1268
declare i_newf2 char(20);
1269
declare i_newf3 date;
1270
declare i_newf4 integer;
1271
declare i_newf11 char(20);
1272
declare i_newf12 char(20);
1273
declare i_newf13 date;
1274
declare i_newf14 integer;
1275
declare cur1 cursor for SELECT f1, f2, f3, f4 from t2
1276
where f4>=-5000 order by f4 limit 4;
1277
declare cur2 cursor for SELECT f1, f2, f3, f4 from t2
1278
where f4>=-5000 order by f4 limit 3;
1279
declare continue handler for sqlstate '02000' set proceed=0;
1284
fetch cur1 into i_newf1, i_newf2, i_newf3, i_newf4;
1286
insert into temp1 values ('sp_inner', i_count, i_newf1, i_newf2, i_newf3, i_newf4);
1287
fetch cur2 into i_newf11, i_newf12, i_newf13, i_newf14;
1289
insert into temp2 values ('sp_inner', i_count, i_newf11, i_newf12, i_newf13, i_newf14);
1292
set i_count = i_count - 1;
1297
CREATE PROCEDURE sp_outer( )
1299
DECLARE proceed INT DEFAULT 1;
1300
DECLARE o_count INTEGER DEFAULT 20;
1301
DECLARE o_newf1 CHAR(20);
1302
DECLARE o_newf2 CHAR(20);
1303
DECLARE o_newf3 DATE;
1304
DECLARE o_newf4 INTEGER;
1305
DECLARE o_newf11 CHAR(20);
1306
DECLARE o_newf12 CHAR(20);
1307
DECLARE o_newf13 DATE;
1308
DECLARE o_newf14 INTEGER;
1309
DECLARE cur1 CURSOR FOR SELECT f1, f2, f3, f4 FROM t2
1310
WHERE f4>=-5000 ORDER BY f4 LIMIT 5;
1311
DECLARE cur2 CURSOR FOR SELECT f1, f2, f3, f4 FROM t2
1312
WHERE f4>=-5000 ORDER BY f4 LIMIT 5;
1313
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET proceed=0;
1318
FETCH cur1 INTO o_newf1, o_newf2, o_newf3, o_newf4;
1320
INSERT INTO temp1 VALUES ('_sp_out_', o_count, o_newf1, o_newf2, o_newf3, o_newf4);
1322
FETCH cur2 INTO o_newf11, o_newf12, o_newf13, o_newf14;
1324
INSERT INTO temp2 VALUES ('_sp_out_', o_count, o_newf11, o_newf12, o_newf13, o_newf14);
1327
SET o_count = o_count + 1;
1333
SELECT * FROM temp1;
1335
_sp_out_ 1 a` a` 1000-01-01 -5000
1336
sp_inner 10 a` a` 1000-01-01 -5000
1337
sp_inner 9 aaa aaa 1000-01-02 -4999
1338
sp_inner 8 abaa abaa 1000-01-03 -4998
1339
sp_inner 7 acaaa acaaa 1000-01-04 -4997
1340
_sp_out_ 2 aaa aaa 1000-01-02 -4999
1341
sp_inner 10 a` a` 1000-01-01 -5000
1342
sp_inner 9 aaa aaa 1000-01-02 -4999
1343
sp_inner 8 abaa abaa 1000-01-03 -4998
1344
sp_inner 7 acaaa acaaa 1000-01-04 -4997
1345
_sp_out_ 3 abaa abaa 1000-01-03 -4998
1346
sp_inner 10 a` a` 1000-01-01 -5000
1347
sp_inner 9 aaa aaa 1000-01-02 -4999
1348
sp_inner 8 abaa abaa 1000-01-03 -4998
1349
sp_inner 7 acaaa acaaa 1000-01-04 -4997
1350
_sp_out_ 4 acaaa acaaa 1000-01-04 -4997
1351
sp_inner 10 a` a` 1000-01-01 -5000
1352
sp_inner 9 aaa aaa 1000-01-02 -4999
1353
sp_inner 8 abaa abaa 1000-01-03 -4998
1354
sp_inner 7 acaaa acaaa 1000-01-04 -4997
1355
_sp_out_ 5 adaaaa adaaaa 1000-01-05 -4996
1356
sp_inner 10 a` a` 1000-01-01 -5000
1357
sp_inner 9 aaa aaa 1000-01-02 -4999
1358
sp_inner 8 abaa abaa 1000-01-03 -4998
1359
sp_inner 7 acaaa acaaa 1000-01-04 -4997
1360
SELECT * FROM temp2;
1362
sp_inner 10 a` a` 1000-01-01 -5000
1363
sp_inner 9 aaa aaa 1000-01-02 -4999
1364
sp_inner 8 abaa abaa 1000-01-03 -4998
1365
_sp_out_ 1 a` a` 1000-01-01 -5000
1366
sp_inner 10 a` a` 1000-01-01 -5000
1367
sp_inner 9 aaa aaa 1000-01-02 -4999
1368
sp_inner 8 abaa abaa 1000-01-03 -4998
1369
_sp_out_ 2 aaa aaa 1000-01-02 -4999
1370
sp_inner 10 a` a` 1000-01-01 -5000
1371
sp_inner 9 aaa aaa 1000-01-02 -4999
1372
sp_inner 8 abaa abaa 1000-01-03 -4998
1373
_sp_out_ 3 abaa abaa 1000-01-03 -4998
1374
sp_inner 10 a` a` 1000-01-01 -5000
1375
sp_inner 9 aaa aaa 1000-01-02 -4999
1376
sp_inner 8 abaa abaa 1000-01-03 -4998
1377
_sp_out_ 4 acaaa acaaa 1000-01-04 -4997
1378
sp_inner 10 a` a` 1000-01-01 -5000
1379
sp_inner 9 aaa aaa 1000-01-02 -4999
1380
sp_inner 8 abaa abaa 1000-01-03 -4998
1381
_sp_out_ 5 adaaaa adaaaa 1000-01-05 -4996
1382
DROP PROCEDURE sp_outer;
1383
DROP PROCEDURE sp_inner;
1387
--source suite/funcs_1/storedproc/cleanup_sp_tb.inc
1388
--------------------------------------------------------------------------------
1389
DROP DATABASE IF EXISTS db_storedproc;
1390
DROP DATABASE IF EXISTS db_storedproc_1;
1392
. +++ END OF SCRIPT +++
1393
--------------------------------------------------------------------------------
added
removed
mysql-test/suite/funcs_1/r/innodb_storedproc_02.result
