[tomato.git] / release / src / router / mysql / mysql-test / suite / funcs_1 / r / innodb_storedproc_02.result
| blob | 65fc5b5afc921feae95877527e8283c8b525b72a |
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;
11 USE db_storedproc;
12 create table t1(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
13 engine = <engine_to_be_tested>;
14 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t1;
15 create table t2(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
16 engine = <engine_to_be_tested>;
17 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t2;
18 create table t3(f1 char(20),f2 char(20),f3 integer) engine = <engine_to_be_tested>;
19 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t3.txt' into table t3;
20 create table t4(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
21 engine = <engine_to_be_tested>;
22 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t4;
23 USE db_storedproc_1;
24 create table t6(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
25 engine = <engine_to_be_tested>;
26 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6;
27 USE db_storedproc;
28 create table t7 (f1 char(20), f2 char(25), f3 date, f4 int)
29 engine = <engine_to_be_tested>;
30 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t7;
31 Warnings:
32 Warning 1265 Data truncated for column 'f3' at row 1
33 Warning 1265 Data truncated for column 'f3' at row 2
34 Warning 1265 Data truncated for column 'f3' at row 3
35 Warning 1265 Data truncated for column 'f3' at row 4
36 Warning 1265 Data truncated for column 'f3' at row 5
37 Warning 1265 Data truncated for column 'f3' at row 6
38 Warning 1265 Data truncated for column 'f3' at row 7
39 Warning 1265 Data truncated for column 'f3' at row 8
40 Warning 1265 Data truncated for column 'f3' at row 9
41 Warning 1265 Data truncated for column 'f3' at row 10
42 create table t8 (f1 char(20), f2 char(25), f3 date, f4 int)
43 engine = <engine_to_be_tested>;
44 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t8;
45 Warnings:
46 Warning 1265 Data truncated for column 'f3' at row 1
47 Warning 1265 Data truncated for column 'f3' at row 2
48 Warning 1265 Data truncated for column 'f3' at row 3
49 Warning 1265 Data truncated for column 'f3' at row 4
50 Warning 1265 Data truncated for column 'f3' at row 5
51 Warning 1265 Data truncated for column 'f3' at row 6
52 Warning 1265 Data truncated for column 'f3' at row 7
53 Warning 1265 Data truncated for column 'f3' at row 8
54 Warning 1265 Data truncated for column 'f3' at row 9
55 Warning 1265 Data truncated for column 'f3' at row 10
56 create table t9(f1 int, f2 char(25), f3 int) engine = <engine_to_be_tested>;
57 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t9.txt' into table t9;
58 create table t10(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
59 engine = <engine_to_be_tested>;
60 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t10;
61 create table t11(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
62 engine = <engine_to_be_tested>;
63 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t11;
65 Section 3.1.2 - Syntax checks for the stored procedure-specific
66 programming statements BEGIN/END, DECLARE, SET, SELECT/INTO, OPEN, FETCH, CLOSE:
67 --------------------------------------------------------------------------------
69 Testcase 3.1.2.8:
70 -----------------
72 Ensure that the scope of each BEGIN/END compound statement within a stored
73 procedure definition is properly applied
74 --------------------------------------------------------------------------------
75 DROP PROCEDURE IF EXISTS sp1;
76 CREATE PROCEDURE sp1( )
77 begin_label: BEGIN
78 declare x char DEFAULT 'x';
79 declare y char DEFAULT 'y';
80 set x = '1';
81 set y = '2';
82 label1: BEGIN
83 declare x char DEFAULT 'X';
84 declare y char DEFAULT 'Y';
85 SELECT f1, f2 into x, y from t2 limit 1;
86 SELECT '1.1', x, y;
87 label2: BEGIN
88 declare x char default 'a';
89 declare y char default 'b';
90 label3: BEGIN
91 declare x char default 'c';
92 declare y char default 'd';
93 label4: BEGIN
94 declare x char default 'e';
95 declare y char default 'f';
96 label5: BEGIN
97 declare x char default 'g';
98 declare y char default 'h';
99 SELECT 5, x, y;
100 END label5;
101 SELECT 4, x, y;
102 END label4;
103 SELECT 3, x, y;
104 END label3;
105 SELECT 2, x, y;
106 END label2;
107 END label1;
108 set @v1 = x;
109 set @v2 = y;
110 SELECT '1.2', @v1, @v2;
111 END begin_label//
112 CALL sp1();
113 1.1 x y
114 1.1 a a
115 5 x y
116 5 g h
117 4 x y
118 4 e f
119 3 x y
120 3 c d
121 2 x y
122 2 a b
123 1.2 @v1 @v2
124 1.2 1 2
125 Warnings:
126 Warning 1265 Data truncated for column 'x' at row 1
127 Warning 1265 Data truncated for column 'y' at row 1
128 DROP PROCEDURE IF EXISTS sp1;
130 Testcase 3.1.2.26:
131 ------------------
133 Ensure that the initial value of every variable declared for a stored procedure
134 is either NULL or its DEFAULT value, as appropriate.
135 --------------------------------------------------------------------------------
136 DROP PROCEDURE IF EXISTS sp1;
137 set @v1=0;
138 set @v2=0;
139 CREATE PROCEDURE sp1( )
140 BEGIN
141 declare x1 char default 'x';
142 declare y1 char;
143 declare x2 tinytext default 'tinytext';
144 declare y2 tinytext;
145 declare x3 datetime default '2005-10-03 12:13:14';
146 declare y3 datetime;
147 declare x4 float default 1.2;
148 declare y4 float;
149 declare x5 blob default 'b';
150 declare y5 blob;
151 declare x6 smallint default 127;
152 declare y6 smallint;
153 SELECT x1, x2, x3, x4, x5, x6, y1, y2, y3, y4, y5, y6;
154 END//
155 CALL sp1();
156 x1 x2 x3 x4 x5 x6 y1 y2 y3 y4 y5 y6
157 x tinytext 2005-10-03 12:13:14 1.2 b 127 NULL NULL NULL NULL NULL NULL
158 DROP PROCEDURE sp1;
160 Testcase 3.1.2.30:
161 ------------------
163 Ensure that, when a stored procedure is called/executed, every variable always
164 uses the correct value: either the value with which it is initialized or the
165 value to which it is subsequently SET or otherwise assigned, as appropriate.
166 --------------------------------------------------------------------------------
167 DROP PROCEDURE IF EXISTS sp1;
168 CREATE PROCEDURE sp1( IN invar INT, OUT outvar INT )
169 BEGIN
170 declare x integer;
171 declare y integer default 1;
172 set @x = x;
173 set @y = y;
174 set @z = 234;
175 SELECT f1, f2 into @x, @y from t2 where f1='a`' and f2='a`' limit 1;
176 SELECT @x, @y, @z, invar;
177 BEGIN
178 set @x = 2;
179 SELECT @x, @y, @z;
180 SET outvar = @x * invar + @z * @f;
181 SET invar = outvar;
182 BEGIN
183 set @y = null, @z = 'abcd';
184 SELECT @x, @y, @z;
185 END;
186 END;
187 END//
188 SET @invar = 100;
189 SET @outvar = @invar;
190 SET @f = 10;
191 SELECT @x, @y, @z, @invar, @outvar;
192 @x @y @z @invar @outvar
193 NULL NULL NULL 100 100
194 CALL sp1( @invar, @outvar );
195 @x @y @z invar
196 a` a` 234 100
197 @x @y @z
198 2 a` 234
199 @x @y @z
200 2 NULL abcd
201 SELECT @x, @y, @z, @invar, @outvar;
202 @x @y @z @invar @outvar
203 2 NULL abcd 100 2540
204 DROP PROCEDURE sp1;
206 Testcase 3.1.2.31:
207 ------------------
209 Ensure that the SELECT ... INTO statement properly assigns values to the
210 variables in its variable list.
211 --------------------------------------------------------------------------------
212 DROP PROCEDURE IF EXISTS sp1;
213 CREATE PROCEDURE sp1( )
214 BEGIN
215 declare x integer; declare y integer;
216 set @x=x;
217 set @y=y;
218 SELECT f4, f3 into @x, @y from t2 where f4=-5000 and f3='1000-01-01' limit 1;
219 SELECT @x, @y;
220 END//
221 CALL sp1();
222 @x @y
223 -5000 1000-01-01
224 DROP PROCEDURE sp1;
226 Testcase 3.1.2.32:
227 ------------------
229 Ensure that a SELECT ... INTO statement that retrieves multiple rows is
230 rejected, with an appropriate error message.
231 --------------------------------------------------------------------------------
232 DROP PROCEDURE IF EXISTS sp1;
233 CREATE PROCEDURE sp1( )
234 BEGIN
235 declare x integer; declare y integer;
236 set @x=x;
237 set @y=y;
238 SELECT f4, f3 into @x, @y from t2;
239 END//
240 CALL sp1();
241 ERROR 42000: Result consisted of more than one row
242 DROP PROCEDURE sp1;
244 Testcase 3.1.2.33:
245 ------------------
247 Ensure that a SELECT ... INTO statement that retrieves too many columns for the
248 number of variables in its variable list is rejected, with an appropriate error
249 message.
250 --------------------------------------------------------------------------------
251 DROP PROCEDURE IF EXISTS sp1;
252 CREATE PROCEDURE sp1( )
253 BEGIN
254 declare x integer; declare y integer;
255 set @x=x;
256 set @y=y;
257 SELECT f4, f3, f2, f1 into @x, @y from t2;
258 END//
259 CALL sp1();
260 ERROR 21000: The used SELECT statements have a different number of columns
261 DROP PROCEDURE sp1;
263 Testcase 3.1.2.34:
264 ------------------
266 Ensure that a SELECT ... INTO statement that retrieves too few columns for the
267 number of variables in its variable list is rejected, with an appropriate error
268 message.
269 --------------------------------------------------------------------------------
270 DROP PROCEDURE IF EXISTS sp1;
271 CREATE PROCEDURE sp1( )
272 BEGIN
273 declare x integer; declare y integer; declare z integer;
274 set @x=x;
275 set @y=y;
276 set @z=z;
277 SELECT f4 into @x, @y, @z from t2;
278 END//
279 CALL sp1();
280 ERROR 21000: The used SELECT statements have a different number of columns
281 DROP PROCEDURE sp1;
283 Testcase 3.1.2.38:
284 ------------------
286 Ensure that the scope of every condition declared is properly applied.
287 --------------------------------------------------------------------------------
288 DROP PROCEDURE IF EXISTS h1;
289 DROP TABLE IF EXISTS res_t1;
290 create table res_t1(w char unique, x char);
291 insert into res_t1 values('a', 'b');
292 CREATE PROCEDURE h1 ()
293 BEGIN
294 declare x1, x2, x3, x4, x5, x6 int default 0;
295 SELECT '-1-', x1, x2, x3, x4, x5, x6;
296 BEGIN
297 declare condname condition for sqlstate '23000';
298 declare continue handler for condname set x5 = 1;
299 set x6 = 0;
300 insert into res_t1 values ('a', 'b');
301 set x6 = 1;
302 SELECT '-2-', x1, x2, x3, x4, x5, x6;
303 END;
304 begin1_label: BEGIN
305 BEGIN
306 declare condname condition for sqlstate '20000';
307 declare continue handler for condname set x1 = 1;
308 set x2 = 0;
309 case x2
310 when 1 then set x2=10;
311 when 2 then set x2=11;
312 END case;
313 set x2 = 1;
314 SELECT '-3-', x1, x2, x3, x4, x5, x6;
315 begin2_label: BEGIN
316 BEGIN
317 declare condname condition for sqlstate '23000';
318 declare exit handler for condname set x3 = 1;
319 set x4= 1;
320 SELECT '-4a', x1, x2, x3, x4, x5, x6;
321 insert into res_t1 values ('a', 'b');
322 set x4= 2;
323 SELECT '-4b', x1, x2, x3, x4, x5, x6;
324 END;
325 SELECT '-5-', x1, x2, x3, x4, x5, x6;
326 END begin2_label;
327 SELECT '-6-', x1, x2, x3, x4, x5, x6;
328 END;
329 SELECT '-7-', x1, x2, x3, x4, x5, x6;
330 END begin1_label;
331 SELECT 'END', x1, x2, x3, x4, x5, x6;
332 END//
333 CALL h1();
334 -1- x1 x2 x3 x4 x5 x6
335 -1- 0 0 0 0 0 0
336 -2- x1 x2 x3 x4 x5 x6
337 -2- 0 0 0 0 1 1
338 -3- x1 x2 x3 x4 x5 x6
339 -3- 1 1 0 0 1 1
340 -4a x1 x2 x3 x4 x5 x6
341 -4a 1 1 0 1 1 1
342 -5- x1 x2 x3 x4 x5 x6
343 -5- 1 1 1 1 1 1
344 -6- x1 x2 x3 x4 x5 x6
345 -6- 1 1 1 1 1 1
346 -7- x1 x2 x3 x4 x5 x6
347 -7- 1 1 1 1 1 1
348 END x1 x2 x3 x4 x5 x6
349 END 1 1 1 1 1 1
350 DROP TABLE IF EXISTS tnull;
351 DROP PROCEDURE IF EXISTS sp1;
352 CREATE TABLE tnull(f1 int);
353 CREATE PROCEDURE sp1()
354 BEGIN
355 declare cond1 condition for sqlstate '42S02';
356 declare continue handler for cond1 set @var2 = 1;
357 BEGIN
358 declare cond1 condition for sqlstate '23000';
359 declare continue handler for cond1 set @var2 = 1;
360 END;
361 insert into tnull values(1);
362 END//
363 CALL sp1();
364 DROP PROCEDURE h1;
365 drop table res_t1;
366 DROP PROCEDURE sp1;
367 DROP TABLE tnull;
369 Testcase 3.1.2.43:
370 ------------------
372 Ensure that the DECLARE ... HANDLER FOR statement can not declare any handler
373 for a condition declared outside of the scope of the handler.
374 --------------------------------------------------------------------------------
375 DROP PROCEDURE IF EXISTS h1;
376 DROP PROCEDURE IF EXISTS h2;
377 drop table IF EXISTS res_t1;
378 create table res_t1(w char unique, x char);
379 insert into res_t1 values ('a', 'b');
380 CREATE PROCEDURE h1 ()
381 BEGIN
382 declare x1, x2, x3, x4, x5, x6 int default 0;
383 BEGIN
384 declare cond_1 condition for sqlstate '23000';
385 declare continue handler for cond_1 set x5 = 1;
386 BEGIN
387 declare cond_2 condition for sqlstate '20000';
388 declare continue handler for cond_1 set x1 = 1;
389 BEGIN
390 declare continue handler for cond_2 set x3 = 1;
391 set x2 = 1;
392 END;
393 set x6 = 0;
394 END;
395 BEGIN
396 declare continue handler for cond_1 set x1 = 1;
397 BEGIN
398 declare continue handler for cond_2 set x3 = 1;
399 set x2 = 1;
400 END;
401 set x6 = 0;
402 END;
403 END;
404 SELECT x1, x2, x3, x4, x5, x6;
405 END//
406 ERROR 42000: Undefined CONDITION: cond_2
407 CREATE PROCEDURE h2 ()
408 BEGIN
409 declare x1, x2, x3, x4, x5, x6 int default 0;
410 BEGIN
411 declare condname condition for sqlstate '23000';
412 declare continue handler for condname set x5 = 1;
413 BEGIN
414 declare condname condition for sqlstate '20000';
415 declare continue handler for condname set x1 = 1;
416 BEGIN
417 declare condname condition for sqlstate '42000';
418 declare continue handler for condname set x3 = 1;
419 set x6 = 0;
420 insert into res_t1 values ('a', 'b');
421 set x6 = 1;
422 set x4= 0;
423 CALL sp1();
424 set x4= 1;
425 set x2 = 0;
426 case x2
427 when 1 then set x2=10;
428 when 2 then set x2=11;
429 END case;
430 set x2 = 1;
431 END;
432 set x2 = 0;
433 case x2
434 when 1 then set x2=10;
435 when 2 then set x2=11;
436 END case;
437 set x2 = 1;
438 set x6 = 0;
439 insert into res_t1 values ('a', 'b');
440 set x6 = 1;
441 END;
442 END;
443 SELECT x1, x2, x3, x4, x5, x6;
444 END//
445 CALL h2();
446 x1 x2 x3 x4 x5 x6
447 1 1 1 1 1 1
448 SELECT * FROM res_t1;
449 w x
450 a b
451 DROP PROCEDURE h2;
452 drop table res_t1;
454 Testcase 3.1.2.44:
455 ------------------
457 Ensure that the DECLARE ... HANDLER FOR statement cannot declare a handler for
458 any invalid, or undeclared, condition.
459 --------------------------------------------------------------------------------
460 DROP PROCEDURE IF EXISTS h1;
461 CREATE PROCEDURE h1 ()
462 BEGIN
463 declare x1, x2, x3, x4, x5, x6 int default 0;
464 BEGIN
465 declare condname1 condition for sqlstate '23000';
466 BEGIN
467 declare condname2 condition for sqlstate '20000';
468 declare continue handler for condname1 set x3 = 1;
469 declare continue handler for condname2 set x1 = 1;
470 END;
471 END;
472 BEGIN
473 declare condname3 condition for sqlstate '42000';
474 declare continue handler for condname1 set x3 = 1;
475 declare continue handler for condname2 set x5 = 1;
476 declare continue handler for condname3 set x1 = 1;
477 END;
478 END//
479 ERROR 42000: Undefined CONDITION: condname1
480 CREATE PROCEDURE h1 ()
481 BEGIN
482 DECLARE x1 INT DEFAULT 0;
483 BEGIN
484 DECLARE condname1 CONDITION CHECK SQLSTATE '23000';
485 END;
486 DECLARE CONTINUE HANDLER FOR condname1 SET x1 = 1;
487 END//
488 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';
489 END;
490 DECLARE CONTINUE HANDLER FOR condname1 SET x1 = 1;
491 ' at line 5
492 CREATE PROCEDURE h1 ()
493 BEGIN
494 DECLARE x1 INT DEFAULT 0;
495 BEGIN
496 DECLARE condname1 CONDITION FOR SQLSTATE 'qwert';
497 END;
498 DECLARE CONTINUE HANDLER FOR condname1 SET x1 = 1;
499 END//
500 ERROR 42000: Bad SQLSTATE: 'qwert'
502 Testcase 3.1.2.45 + 3.1.2.50:
503 -----------------------------
505 45. Ensure that the scope of every handler declared is properly applied.
506 50. Ensure that a CONTINUE handler allows the execution of the stored procedure
507 . to continue once the handler statement has completed its own execution (that
508 . is, once the handler action statement has been executed).
509 --------------------------------------------------------------------------------
510 DROP PROCEDURE IF EXISTS p1;
511 DROP PROCEDURE IF EXISTS p1undo;
512 DROP PROCEDURE IF EXISTS h1;
513 DROP PROCEDURE IF EXISTS sp1;
514 drop table IF EXISTS res_t1;
515 ==> 'UNDO' is still not supported.
516 create procedure p1undo ()
517 begin
518 declare undo handler for sqlexception select '1';
519 select * from tqq;
520 SELECT 'end of 1';
521 end;//
522 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';
523 select * from tqq;
524 SELECT 'end of 1';
525 ' at line 3
526 create procedure p1 ()
527 begin
528 declare exit handler for sqlexception select 'exit handler 1';
529 begin
530 declare exit handler for sqlexception select 'exit handler 2';
531 begin
532 declare continue handler for sqlexception select 'continue handler 3';
533 drop table if exists tqq;
534 select * from tqq;
535 SELECT 'end of BEGIN/END 3';
536 end;
537 drop table if exists tqq;
538 select * from tqq;
539 SELECT 'end of BEGIN/END 2';
540 end;
541 select * from tqq;
542 SELECT 'end of BEGIN/END 1';
543 end;//
544 call p1()//
545 continue handler 3
546 continue handler 3
547 end of BEGIN/END 3
548 end of BEGIN/END 3
549 exit handler 2
550 exit handler 2
551 exit handler 1
552 exit handler 1
553 Warnings:
554 Note 1051 Unknown table 'tqq'
555 Note 1051 Unknown table 'tqq'
556 create table res_t1(w char unique, x char);
557 insert into res_t1 values ('a', 'b');
558 CREATE PROCEDURE h1 ()
559 BEGIN
560 declare x1, x2, x3, x4, x5, x6 int default 0;
561 BEGIN
562 declare continue handler for sqlstate '23000' set x5 = 1;
563 insert into res_t1 values ('a', 'b');
564 set x6 = 1;
565 END;
566 begin1_label: BEGIN
567 BEGIN
568 declare continue handler for sqlstate '23000' set x1 = 1;
569 insert into res_t1 values ('a', 'b');
570 set x2 = 1;
571 begin2_label: BEGIN
572 BEGIN
573 declare exit handler for sqlstate '23000' set x3 = 1;
574 set x4= 1;
575 insert into res_t1 values ('a', 'b');
576 set x4= 0;
577 END;
578 END begin2_label;
579 END;
580 END begin1_label;
581 SELECT x1, x2, x3, x4, x5, x6;
582 END//
583 CALL h1();
584 x1 x2 x3 x4 x5 x6
585 1 1 1 1 1 1
586 This will fail, SQLSTATE 00000 is not allowed
587 CREATE PROCEDURE sp1()
588 begin1_label:BEGIN
589 declare exit handler for sqlstate '00000' set @var1 = 5;
590 set @var2 = 6;
591 begin2_label:BEGIN
592 declare continue handler for sqlstate '00000' set @var3 = 7;
593 set @var4 = 8;
594 SELECT @var3, @var4;
595 END begin2_label;
596 SELECT @var1, @var2;
597 END begin1_label//
598 ERROR 42000: Bad SQLSTATE: '00000'
599 Verify SP wasn't created
600 CALL sp1();
601 ERROR 42000: PROCEDURE db_storedproc.sp1 does not exist
602 DROP PROCEDURE p1;
603 DROP PROCEDURE h1;
604 DROP PROCEDURE IF EXISTS sp1;
605 DROP TABLE res_t1;
607 Testcase 3.1.2.50:
608 ------------------
609 DROP PROCEDURE IF EXISTS sp1;
610 DROP PROCEDURE IF EXISTS sp2;
611 CREATE PROCEDURE sp1 (x int, y int)
612 BEGIN
613 set @y=0;
614 END//
615 CREATE PROCEDURE sp2 ()
616 BEGIN
617 declare continue handler for sqlstate '42000' set @x2 = 1;
618 set @x=1;
619 SELECT @x2;
620 CALL sp1(1);
621 set @x=2;
622 SELECT @x2, @x;
623 END//
624 CALL sp2();
625 @x2
626 NULL
627 @x2 @x
628 1 2
629 DROP PROCEDURE sp1;
630 DROP PROCEDURE sp2;
632 Testcase 3.2.2.51:
633 ------------------
635 Ensure that an EXIT handler causes the execution of the stored procedure to
636 terminate, within its scope, once the handler action statement has been
637 executed.
638 --------------------------------------------------------------------------------
639 DROP PROCEDURE IF EXISTS sp1;
640 DROP PROCEDURE IF EXISTS sp2;
641 CREATE PROCEDURE sp1 (x int, y int)
642 BEGIN
643 set @x=0;
644 END//
645 CREATE PROCEDURE sp2 ()
646 BEGIN
647 declare exit handler for sqlstate '42000' set @x2 = 1;
648 set @x2=0;
649 set @x=1;
650 SELECT '-1-', @x2, @x;
651 CALL sp1(1);
652 SELECT '-2-', @x2, @x;
653 set @x=2;
654 END//
655 CALL sp1(1);
656 ERROR 42000: Incorrect number of arguments for PROCEDURE db_storedproc.sp1; expected 2, got 1
657 CALL sp2();
658 -1- @x2 @x
659 -1- 0 1
660 SELECT '-3-', @x2, @x;
661 -3- @x2 @x
662 -3- 1 1
663 DROP PROCEDURE sp1;
664 DROP PROCEDURE sp2;
666 Testcase 3.1.2.52:
667 ------------------
669 Ensure that an EXIT handler does not cause the execution of the stored procedure
670 to terminate outside of its scope.
671 --------------------------------------------------------------------------------
672 DROP PROCEDURE IF EXISTS sp1;
673 DROP PROCEDURE IF EXISTS sp2;
674 CREATE PROCEDURE sp1 (x int, y int)
675 BEGIN
676 set @x=0;
677 END//
678 CREATE PROCEDURE sp2()
679 BEGIN
680 declare continue handler for sqlstate '42000' set @x2 = 2;
681 set @x2 = 1;
682 set @x =20;
683 SELECT '-1-', @x2, @x;
684 BEGIN
685 declare exit handler for sqlstate '42000' set @x2 = 11;
686 SELECT '-2-', @x2, @x;
687 CALL sp1(1);
688 SELECT '-3a', @x2, @x;
689 set @x=21;
690 SELECT '-3b', @x2, @x;
691 END;
692 set @x=22;
693 SELECT '-4-', @x2, @x;
694 END//
695 CALL sp2();
696 -1- @x2 @x
697 -1- 1 20
698 -2- @x2 @x
699 -2- 1 20
700 -4- @x2 @x
701 -4- 11 22
702 DROP PROCEDURE sp1;
703 DROP PROCEDURE sp2;
705 Testcase 3.1.2.54:
706 ------------------
708 Ensure that a handler with a condition defined with an SQLSTATE that begins with
709 “01“ is always exactly equivalent in action to a handler with an SQLWARNING
710 condition.
711 --------------------------------------------------------------------------------
712 DROP PROCEDURE IF EXISTS sp0;
713 DROP PROCEDURE IF EXISTS sp1;
714 DROP PROCEDURE IF EXISTS sp2;
715 DROP PROCEDURE IF EXISTS sp3;
716 DROP PROCEDURE IF EXISTS sp4;
717 DROP TABLE IF EXISTS temp;
718 CREATE TABLE temp( f1 CHAR, f2 CHAR);
719 CREATE PROCEDURE sp0()
720 BEGIN
721 set @done=0;
722 set @x=0;
723 insert into temp values('xxx', 'yy');
724 set @x=1;
725 END//
726 CREATE PROCEDURE sp1()
727 BEGIN
728 declare continue handler for sqlstate '01000' set @done = 1;
729 set @done=0;
730 set @x=0;
731 insert into temp values('xxx', 'yy');
732 set @x=1;
733 END//
734 CREATE PROCEDURE sp2()
735 BEGIN
736 declare continue handler for sqlwarning set @done = 1;
737 set @done=0;
738 set @x=0;
739 insert into temp values('xxx', 'yy');
740 set @x=1;
741 END//
742 CREATE PROCEDURE sp3()
743 BEGIN
744 declare exit handler for sqlstate '01000' set @done = 1;
745 set @done=0;
746 set @x=0;
747 insert into temp values('xxx', 'yy');
748 set @x=1;
749 END//
750 CREATE PROCEDURE sp4()
751 BEGIN
752 declare exit handler for sqlwarning set @done = 1;
753 set @done=0;
754 set @x=0;
755 insert into temp values('xxx', 'yy');
756 set @x=1;
757 END//
758 INSERT INTO temp VALUES('0', NULL);
759 CALL sp0();
760 Warnings:
761 Warning 1265 Data truncated for column 'f1' at row 1
762 Warning 1265 Data truncated for column 'f2' at row 1
763 SELECT @done, @x;
764 @done @x
765 0 1
766 INSERT INTO temp VALUES('1', NULL);
767 CALL sp1();
768 SELECT @done, @x;
769 @done @x
770 1 1
771 INSERT INTO temp VALUES('2', NULL);
772 CALL sp2();
773 SELECT @done, @x;
774 @done @x
775 1 1
776 INSERT INTO temp VALUES('3', NULL);
777 CALL sp3();
778 SELECT @done, @x;
779 @done @x
780 1 0
781 INSERT INTO temp VALUES('4', NULL);
782 CALL sp4();
783 SELECT @done, @x;
784 @done @x
785 1 0
786 SELECT * FROM temp;
787 f1 f2
788 0 NULL
789 x y
790 1 NULL
791 x y
792 2 NULL
793 x y
794 3 NULL
795 x y
796 4 NULL
797 x y
798 DROP PROCEDURE sp1;
799 DROP PROCEDURE sp2;
800 DROP PROCEDURE sp3;
801 DROP PROCEDURE sp4;
802 DROP TABLE temp;
804 Testcase 3.1.2.56:
805 ------------------
807 Ensure that a handler with a condition defined with an SQLSTATE that begins with
808 “02“ is always exactly equivalent in action to a handler with a NOT FOUND
809 condition.
810 --------------------------------------------------------------------------------
811 DROP PROCEDURE IF EXISTS sp0;
812 DROP PROCEDURE IF EXISTS sp1;
813 DROP PROCEDURE IF EXISTS sp2;
814 DROP PROCEDURE IF EXISTS sp3;
815 DROP PROCEDURE IF EXISTS sp4;
816 CREATE PROCEDURE sp0()
817 BEGIN
818 DECLARE f1_value CHAR(20);
819 DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
820 SET @done = 0;
821 SET @x = 0;
822 OPEN cur1;
823 FETCH cur1 INTO f1_value;
824 SET @x = 1;
825 FETCH cur1 INTO f1_value;
826 SET @x = 2;
827 CLOSE cur1;
828 END//
829 CREATE PROCEDURE sp1()
830 BEGIN
831 DECLARE f1_value CHAR(20);
832 DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
833 declare continue handler for sqlstate '02000' set @done = 1;
834 SET @done = 0;
835 SET @x = 0;
836 OPEN cur1;
837 FETCH cur1 INTO f1_value;
838 SET @x = 1;
839 FETCH cur1 INTO f1_value;
840 SET @x = 2;
841 CLOSE cur1;
842 END//
843 CREATE PROCEDURE sp2()
844 BEGIN
845 DECLARE f1_value CHAR(20);
846 DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
847 declare continue handler for not found set @done = 1;
848 SET @done = 0;
849 SET @x = 0;
850 OPEN cur1;
851 FETCH cur1 INTO f1_value;
852 SET @x = 1;
853 FETCH cur1 INTO f1_value;
854 SET @x = 2;
855 CLOSE cur1;
856 END//
857 CREATE PROCEDURE sp3()
858 BEGIN
859 DECLARE f1_value CHAR(20);
860 DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
861 declare exit handler for sqlstate '02000' set @done = 1;
862 SET @done = 0;
863 SET @x = 0;
864 OPEN cur1;
865 FETCH cur1 INTO f1_value;
866 SET @x = 1;
867 FETCH cur1 INTO f1_value;
868 SET @x = 2;
869 CLOSE cur1;
870 END//
871 CREATE PROCEDURE sp4()
872 BEGIN
873 DECLARE f1_value CHAR(20);
874 DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
875 declare exit handler for not found set @done = 1;
876 SET @done = 0;
877 SET @x = 0;
878 OPEN cur1;
879 FETCH cur1 INTO f1_value;
880 SET @x = 1;
881 FETCH cur1 INTO f1_value;
882 SET @x = 2;
883 CLOSE cur1;
884 END//
885 CALL sp0();
886 ERROR 02000: No data - zero rows fetched, selected, or processed
887 SELECT @done, @x;
888 @done @x
889 0 1
890 CALL sp1();
891 SELECT @done, @x;
892 @done @x
893 1 2
894 CALL sp2();
895 SELECT @done, @x;
896 @done @x
897 1 2
898 CALL sp3();
899 SELECT @done, @x;
900 @done @x
901 1 1
902 CALL sp4();
903 SELECT @done, @x;
904 @done @x
905 1 1
906 DROP PROCEDURE sp0;
907 DROP PROCEDURE sp1;
908 DROP PROCEDURE sp2;
909 DROP PROCEDURE sp3;
910 DROP PROCEDURE sp4;
912 Testcase 3.1.2.58:
913 ------------------
915 Ensure that a handler with a condition defined with an SQLSTATE that begins with
916 anything other that “01“ or “02“ is always exactly equivalent in action to a
917 handler with an SQLEXCEPTION condition.
918 --------------------------------------------------------------------------------
919 DROP PROCEDURE IF EXISTS sp0;
920 DROP PROCEDURE IF EXISTS sp1;
921 DROP PROCEDURE IF EXISTS sp2;
922 DROP PROCEDURE IF EXISTS sp3;
923 DROP PROCEDURE IF EXISTS sp4;
924 CREATE PROCEDURE sp0()
925 BEGIN
926 DECLARE f1_value CHAR(20);
927 DECLARE cv INT DEFAULT 0;
928 DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
929 SET @x = 1;
930 CASE cv
931 WHEN 2 THEN SET @x = 2;
932 WHEN 3 THEN SET @x = 3;
933 END case;
934 SET @x = 4;
935 SELECT f1, f2 FROM t2
936 UNION
937 SELECT f1, f2,3 FROM t2;
938 SET @x = 5;
939 FETCH cur1 INTO f1_value;
940 SET @x = 6;
941 END//
942 CREATE PROCEDURE sp1()
943 BEGIN
944 DECLARE f1_value CHAR(20);
945 DECLARE cv INT DEFAULT 0;
946 DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
947 DECLARE continue HANDLER FOR SQLSTATE '20000' SELECT '20000' AS 'SQLSTATE';
948 DECLARE continue HANDLER FOR SQLSTATE '21000' SELECT '21000' AS 'SQLSTATE';
949 DECLARE continue HANDLER FOR SQLSTATE '24000' SELECT '24000' AS 'SQLSTATE';
950 SET @x = 1;
951 CASE cv
952 WHEN 2 THEN SET @x = 2;
953 WHEN 3 THEN SET @x = 3;
954 END case;
955 SET @x = 4;
956 SELECT f1, f2 FROM t2
957 UNION
958 SELECT f1, f2,3 FROM t2;
959 SET @x = 5;
960 FETCH cur1 INTO f1_value;
961 SET @x = 6;
962 END//
963 CREATE PROCEDURE sp2()
964 BEGIN
965 DECLARE f1_value CHAR(20);
966 DECLARE cv INT DEFAULT 0;
967 DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
968 DECLARE continue HANDLER FOR SQLEXCEPTION SELECT 'SQLEXCEPTION' AS 'SQLSTATE';
969 DECLARE continue HANDLER FOR SQLSTATE '24000' SELECT '24000' AS 'SQLSTATE';
970 SET @x = 1;
971 CASE cv
972 WHEN 2 THEN SET @x = 2;
973 WHEN 3 THEN SET @x = 3;
974 END case;
975 SET @x = 4;
976 SELECT f1, f2 FROM t2
977 UNION
978 SELECT f1, f2,3 FROM t2;
979 SET @x = 5;
980 FETCH cur1 INTO f1_value;
981 SET @x = 6;
982 END//
983 CREATE PROCEDURE sp3()
984 BEGIN
985 DECLARE f1_value CHAR(20);
986 DECLARE cv INT DEFAULT 0;
987 DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
988 DECLARE EXIT HANDLER FOR SQLSTATE '20000' SELECT '20000' AS 'SQLSTATE';
989 DECLARE EXIT HANDLER FOR SQLSTATE '21000' SELECT '21000' AS 'SQLSTATE';
990 DECLARE EXIT HANDLER FOR SQLSTATE '24000' SELECT '24000' AS 'SQLSTATE';
991 SET @x = 1;
992 CASE cv
993 WHEN 2 THEN SET @x = 2;
994 WHEN 3 THEN SET @x = 3;
995 END case;
996 SET @x = 4;
997 SELECT f1, f2 FROM t2
998 UNION
999 SELECT f1, f2,3 FROM t2;
1000 SET @x = 5;
1001 FETCH cur1 INTO f1_value;
1002 SET @x = 6;
1003 END//
1004 CREATE PROCEDURE sp4()
1005 BEGIN
1006 DECLARE f1_value CHAR(20);
1007 DECLARE cv INT DEFAULT 0;
1008 DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
1009 DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT 'SQLEXCEPTION' AS 'SQLSTATE';
1010 DECLARE EXIT HANDLER FOR SQLSTATE '24000' SELECT '24000' AS 'SQLSTATE';
1011 SET @x = 1;
1012 CASE cv
1013 WHEN 2 THEN SET @x = 2;
1014 WHEN 3 THEN SET @x = 3;
1015 END case;
1016 SET @x = 4;
1017 SELECT f1, f2 FROM t2
1018 UNION
1019 SELECT f1, f2,3 FROM t2;
1020 SET @x = 5;
1021 FETCH cur1 INTO f1_value;
1022 SET @x = 6;
1023 CLOSE cur1;
1024 END//
1025 CALL sp0();
1026 ERROR 20000: Case not found for CASE statement
1027 SELECT '-0-', @x;
1028 -0- @x
1029 -0- 1
1030 CALL sp1();
1031 SQLSTATE
1032 20000
1033 SQLSTATE
1034 21000
1035 SQLSTATE
1036 24000
1037 SELECT '-1-', @x;
1038 -1- @x
1039 -1- 6
1040 CALL sp2();
1041 SQLSTATE
1042 SQLEXCEPTION
1043 SQLSTATE
1044 SQLEXCEPTION
1045 SQLSTATE
1046 24000
1047 SELECT '-2-', @x;
1048 -2- @x
1049 -2- 6
1050 CALL sp3();
1051 SQLSTATE
1052 20000
1053 SELECT '-3-', @x;
1054 -3- @x
1055 -3- 1
1056 CALL sp4();
1057 SQLSTATE
1058 SQLEXCEPTION
1059 SELECT '-4-', @x;
1060 -4- @x
1061 -4- 1
1062 DROP PROCEDURE sp0;
1063 DROP PROCEDURE sp1;
1064 DROP PROCEDURE sp2;
1065 DROP PROCEDURE sp3;
1066 DROP PROCEDURE sp4;
1068 Testcase 3.1.2.65:
1069 ------------------
1071 Ensure that FETCH <cursor name> returns the first row of the cursor_s result set
1072 the first time FETCH is executed, that it returns each subsequent row of the
1073 cursor_s result set each of the subsequent times FETCH is executed, and that it
1074 returns a NOT FOUND warning if it is executed after the last row of the cursor_s
1075 result set has already been fetched.
1076 --------------------------------------------------------------------------------
1077 DROP PROCEDURE IF EXISTS sp1;
1078 DROP TABLE IF EXISTS temp;
1079 CREATE TABLE temp(
1080 cnt INT,
1081 f1 CHAR(20),
1082 f2 CHAR(20),
1083 f3 INT,
1084 f4 CHAR(20),
1085 f5 INT);
1086 INSERT INTO temp VALUES(0, 'onip', 'abc', 8760, 'xyz', 10);
1087 CREATE PROCEDURE sp1( )
1088 BEGIN
1089 declare proceed int default 1;
1090 declare count integer default 1;
1091 declare f1_value char(20);
1092 declare f2_value char(20);
1093 declare f5_value char(20);
1094 declare f4_value integer;
1095 declare f6_value integer;
1096 declare cur1 cursor for SELECT f1, f2, f4, f5, f6 from t2
1097 where f4 >=-5000 order by f4 limit 3;
1098 open cur1;
1099 while proceed do
1100 SELECT count AS 'loop';
1101 fetch cur1 into f1_value, f2_value, f4_value, f5_value, f6_value;
1102 insert into temp values (count, f1_value, f2_value, f4_value, f5_value, f6_value);
1103 set count = count + 1;
1104 END while;
1105 END//
1106 CALL sp1();
1107 loop
1108 1
1109 loop
1110 2
1111 loop
1112 3
1113 loop
1114 4
1115 ERROR 02000: No data - zero rows fetched, selected, or processed
1116 SELECT * FROM temp;
1117 cnt f1 f2 f3 f4 f5
1118 0 onip abc 8760 xyz 10
1119 1 a` a` -5000 a` -5000
1120 2 aaa aaa -4999 aaa -4999
1121 3 abaa abaa -4998 abaa -4998
1122 DROP TABLE temp;
1123 DROP PROCEDURE sp1;
1125 Testcase 3.1.2.68:
1126 ------------------
1128 Ensure that FETCH <cursor name> fails with an appropriate error message if the
1129 number of columns to be fetched does not match the number of variables specified
1130 by the FETCH statement.
1131 --------------------------------------------------------------------------------
1132 DROP PROCEDURE IF EXISTS sp1;
1133 DROP PROCEDURE IF EXISTS sp2;
1134 --> not enough columns in FETCH statement
1135 CREATE PROCEDURE sp1( )
1136 BEGIN
1137 declare newf1 char(20);
1138 declare cur1 cursor for SELECT f1, f2 from t2 limit 10;
1139 declare continue handler for sqlstate '02000' SELECT 'HANDLER executed.' AS '';
1140 BEGIN
1141 open cur1;
1142 fetch cur1 into newf1;
1143 SELECT newf1;
1144 close cur1;
1145 END;
1146 END//
1147 --> too many columns in FETCH statement
1148 CREATE PROCEDURE sp2( )
1149 BEGIN
1150 declare newf1 char(20);
1151 declare newf2 char(20);
1152 declare cur1 cursor for SELECT f1 from t2 limit 10;
1153 declare continue handler for sqlstate '02000' SELECT 'HANDLER executed.' AS '';
1154 BEGIN
1155 open cur1;
1156 fetch cur1 into newf1, newf2;
1157 SELECT newf1, newf2;
1158 close cur1;
1159 END;
1160 END//
1161 --> not enough columns in FETCH statement
1162 CALL sp1();
1163 ERROR HY000: Incorrect number of FETCH variables
1164 --> too many columns in FETCH statement
1165 CALL sp2();
1166 ERROR HY000: Incorrect number of FETCH variables
1167 DROP PROCEDURE sp1;
1168 DROP PROCEDURE sp2;
1170 Testcase 3.1.2.75:
1171 ------------------
1173 Ensure that, for nested compound statements, a cursor that was declared and
1174 opened during an outer level of the statement is not closed when an inner level
1175 of a compound statement ends.
1176 --------------------------------------------------------------------------------
1177 DROP TABLE IF EXISTS temp1;
1178 DROP PROCEDURE IF EXISTS sp1;
1179 create table temp1( f0 char(20), f1 char(20), f2 char(20), f3 int, f4 char(20) );
1180 SELECT f1, f2, f4, f5 from t2 order by f4;
1181 f1 f2 f4 f5
1182 a` a` -5000 a`
1183 aaa aaa -4999 aaa
1184 abaa abaa -4998 abaa
1185 acaaa acaaa -4997 acaaa
1186 adaaaa adaaaa -4996 adaaaa
1187 aeaaaaa aeaaaaa -4995 aeaaaaa
1188 afaaaaaa afaaaaaa -4994 afaaaaaa
1189 agaaaaaaa agaaaaaaa -4993 agaaaaaaa
1190 a^aaaaaaaa a^aaaaaaaa -4992 a^aaaaaaaa
1191 a_aaaaaaaaa a_aaaaaaaaa -4991 a_aaaaaaaaa
1192 CREATE PROCEDURE sp1( )
1193 BEGIN
1194 declare count integer;
1195 declare from0 char(20);
1196 declare newf1 char(20);
1197 declare newf2 char(20);
1198 declare newf5 char(20);
1199 declare newf4 integer;
1200 declare cur1 cursor for SELECT f1, f2, f4, f5 from t2 where f4 >= -5000 order by f4 limit 5;
1201 declare cur2 cursor for SELECT f1, f2, f4, f5 from t2 where f4 >= -5000 order by f4 limit 5;
1202 open cur1;
1203 open cur2;
1204 BEGIN
1205 declare continue handler for sqlstate '02000' set count = 1;
1206 fetch cur1 into newf1, newf2, newf4, newf5;
1207 SELECT '-1-', count, newf1, newf2, newf4, newf5;
1208 insert into temp1 values ('cur1_out', newf1, newf2, newf4, newf5);
1209 set count = 4;
1210 BEGIN
1211 while count > 0 do
1212 fetch cur1 into newf1, newf2, newf4, newf5;
1213 SELECT '-2-', count, newf1, newf2, newf4, newf5;
1214 set count = count - 1;
1215 END while;
1216 SELECT '-3-', count, newf1, newf2, newf4, newf4;
1217 END;
1218 BEGIN
1219 fetch cur1 into newf1, newf2, newf4, newf5;
1220 SELECT '-4-', newf1, newf2, newf4, newf5;
1221 insert into temp1 values ('cur1_in', newf1, newf2, newf4, newf5);
1222 END;
1223 fetch cur2 into newf1, newf2, newf4, newf5;
1224 SELECT '-5-', newf1, newf2, newf4, newf5;
1225 insert into temp1 values ('cur2', newf1, newf2, newf4, newf5);
1226 close cur1;
1227 END;
1228 fetch cur2 into newf1, newf2, newf4, newf5;
1229 SELECT '-6-', newf1, newf2, newf4, newf5;
1230 close cur2;
1231 END//
1232 CALL sp1();
1233 -1- count newf1 newf2 newf4 newf5
1234 -1- NULL a` a` -5000 a`
1235 -2- count newf1 newf2 newf4 newf5
1236 -2- 4 aaa aaa -4999 aaa
1237 -2- count newf1 newf2 newf4 newf5
1238 -2- 3 abaa abaa -4998 abaa
1239 -2- count newf1 newf2 newf4 newf5
1240 -2- 2 acaaa acaaa -4997 acaaa
1241 -2- count newf1 newf2 newf4 newf5
1242 -2- 1 adaaaa adaaaa -4996 adaaaa
1243 -3- count newf1 newf2 newf4 newf4
1244 -3- 0 adaaaa adaaaa -4996 -4996
1245 -4- newf1 newf2 newf4 newf5
1246 -4- adaaaa adaaaa -4996 adaaaa
1247 -5- newf1 newf2 newf4 newf5
1248 -5- a` a` -5000 a`
1249 -6- newf1 newf2 newf4 newf5
1250 -6- aaa aaa -4999 aaa
1251 SELECT * from temp1;
1252 f0 f1 f2 f3 f4
1253 cur1_out a` a` -5000 a`
1254 cur1_in adaaaa adaaaa -4996 adaaaa
1255 cur2 a` a` -5000 a`
1256 DROP PROCEDURE sp1;
1257 drop table temp1;
1259 Testcase 3.1.2.76:
1260 ------------------
1262 Ensure that all cursors operate asensitively, so that there is no concurrency
1263 conflict between cursors operating on the same, or similar, sets of results
1264 during execution of one or more stored procedures.
1265 --------------------------------------------------------------------------------
1266 DROP PROCEDURE IF EXISTS sp1;
1267 drop table IF EXISTS temp1;
1268 drop table IF EXISTS temp2;
1269 create table temp1( f0 char(10), cnt int, f1 char(20), f2 char(20), f3 date, f4 integer );
1270 create table temp2( f0 char(10), cnt int, f1 char(20), f2 char(20), f3 date, f4 integer );
1271 CREATE PROCEDURE sp_inner( )
1272 BEGIN
1273 declare proceed int default 1;
1274 declare i_count integer default 20;
1275 declare i_newf1 char(20);
1276 declare i_newf2 char(20);
1277 declare i_newf3 date;
1278 declare i_newf4 integer;
1279 declare i_newf11 char(20);
1280 declare i_newf12 char(20);
1281 declare i_newf13 date;
1282 declare i_newf14 integer;
1283 declare cur1 cursor for SELECT f1, f2, f3, f4 from t2
1284 where f4>=-5000 order by f4 limit 4;
1285 declare cur2 cursor for SELECT f1, f2, f3, f4 from t2
1286 where f4>=-5000 order by f4 limit 3;
1287 declare continue handler for sqlstate '02000' set proceed=0;
1288 open cur1;
1289 open cur2;
1290 set i_count = 10;
1291 while proceed do
1292 fetch cur1 into i_newf1, i_newf2, i_newf3, i_newf4;
1293 IF proceed THEN
1294 insert into temp1 values ('sp_inner', i_count, i_newf1, i_newf2, i_newf3, i_newf4);
1295 fetch cur2 into i_newf11, i_newf12, i_newf13, i_newf14;
1296 IF proceed THEN
1297 insert into temp2 values ('sp_inner', i_count, i_newf11, i_newf12, i_newf13, i_newf14);
1298 END IF;
1299 END IF;
1300 set i_count = i_count - 1;
1301 END while;
1302 close cur1;
1303 close cur2;
1304 END//
1305 CREATE PROCEDURE sp_outer( )
1306 BEGIN
1307 DECLARE proceed INT DEFAULT 1;
1308 DECLARE o_count INTEGER DEFAULT 20;
1309 DECLARE o_newf1 CHAR(20);
1310 DECLARE o_newf2 CHAR(20);
1311 DECLARE o_newf3 DATE;
1312 DECLARE o_newf4 INTEGER;
1313 DECLARE o_newf11 CHAR(20);
1314 DECLARE o_newf12 CHAR(20);
1315 DECLARE o_newf13 DATE;
1316 DECLARE o_newf14 INTEGER;
1317 DECLARE cur1 CURSOR FOR SELECT f1, f2, f3, f4 FROM t2
1318 WHERE f4>=-5000 ORDER BY f4 LIMIT 5;
1319 DECLARE cur2 CURSOR FOR SELECT f1, f2, f3, f4 FROM t2
1320 WHERE f4>=-5000 ORDER BY f4 LIMIT 5;
1321 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET proceed=0;
1322 OPEN cur1;
1323 OPEN cur2;
1324 SET o_count = 1;
1325 WHILE proceed DO
1326 FETCH cur1 INTO o_newf1, o_newf2, o_newf3, o_newf4;
1327 IF proceed THEN
1328 INSERT INTO temp1 VALUES ('_sp_out_', o_count, o_newf1, o_newf2, o_newf3, o_newf4);
1329 CALL sp_inner();
1330 FETCH cur2 INTO o_newf11, o_newf12, o_newf13, o_newf14;
1331 IF proceed THEN
1332 INSERT INTO temp2 VALUES ('_sp_out_', o_count, o_newf11, o_newf12, o_newf13, o_newf14);
1333 END IF;
1334 END IF;
1335 SET o_count = o_count + 1;
1336 END WHILE;
1337 CLOSE cur1;
1338 CLOSE cur2;
1339 END//
1340 CALL sp_outer();
1341 SELECT * FROM temp1;
1342 f0 cnt f1 f2 f3 f4
1343 _sp_out_ 1 a` a` 1000-01-01 -5000
1344 sp_inner 10 a` a` 1000-01-01 -5000
1345 sp_inner 9 aaa aaa 1000-01-02 -4999
1346 sp_inner 8 abaa abaa 1000-01-03 -4998
1347 sp_inner 7 acaaa acaaa 1000-01-04 -4997
1348 _sp_out_ 2 aaa aaa 1000-01-02 -4999
1349 sp_inner 10 a` a` 1000-01-01 -5000
1350 sp_inner 9 aaa aaa 1000-01-02 -4999
1351 sp_inner 8 abaa abaa 1000-01-03 -4998
1352 sp_inner 7 acaaa acaaa 1000-01-04 -4997
1353 _sp_out_ 3 abaa abaa 1000-01-03 -4998
1354 sp_inner 10 a` a` 1000-01-01 -5000
1355 sp_inner 9 aaa aaa 1000-01-02 -4999
1356 sp_inner 8 abaa abaa 1000-01-03 -4998
1357 sp_inner 7 acaaa acaaa 1000-01-04 -4997
1358 _sp_out_ 4 acaaa acaaa 1000-01-04 -4997
1359 sp_inner 10 a` a` 1000-01-01 -5000
1360 sp_inner 9 aaa aaa 1000-01-02 -4999
1361 sp_inner 8 abaa abaa 1000-01-03 -4998
1362 sp_inner 7 acaaa acaaa 1000-01-04 -4997
1363 _sp_out_ 5 adaaaa adaaaa 1000-01-05 -4996
1364 sp_inner 10 a` a` 1000-01-01 -5000
1365 sp_inner 9 aaa aaa 1000-01-02 -4999
1366 sp_inner 8 abaa abaa 1000-01-03 -4998
1367 sp_inner 7 acaaa acaaa 1000-01-04 -4997
1368 SELECT * FROM temp2;
1369 f0 cnt f1 f2 f3 f4
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_ 1 a` a` 1000-01-01 -5000
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_ 2 aaa aaa 1000-01-02 -4999
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_ 3 abaa abaa 1000-01-03 -4998
1382 sp_inner 10 a` a` 1000-01-01 -5000
1383 sp_inner 9 aaa aaa 1000-01-02 -4999
1384 sp_inner 8 abaa abaa 1000-01-03 -4998
1385 _sp_out_ 4 acaaa acaaa 1000-01-04 -4997
1386 sp_inner 10 a` a` 1000-01-01 -5000
1387 sp_inner 9 aaa aaa 1000-01-02 -4999
1388 sp_inner 8 abaa abaa 1000-01-03 -4998
1389 _sp_out_ 5 adaaaa adaaaa 1000-01-05 -4996
1390 DROP PROCEDURE sp_outer;
1391 DROP PROCEDURE sp_inner;
1392 DROP TABLE temp1;
1393 DROP TABLE temp2;
1395 --source suite/funcs_1/storedproc/cleanup_sp_tb.inc
1396 --------------------------------------------------------------------------------
1397 DROP DATABASE IF EXISTS db_storedproc;
1398 DROP DATABASE IF EXISTS db_storedproc_1;
1400 . +++ END OF SCRIPT +++
1401 --------------------------------------------------------------------------------