diff options
Diffstat (limited to 'mysql-test/suite/funcs_1/r/innodb_storedproc_02.result')
| -rw-r--r-- | mysql-test/suite/funcs_1/r/innodb_storedproc_02.result | 1395 |
1 files changed, 1395 insertions, 0 deletions
diff --git a/mysql-test/suite/funcs_1/r/innodb_storedproc_02.result b/mysql-test/suite/funcs_1/r/innodb_storedproc_02.result new file mode 100644 index 00000000000..60ea7393c73 --- /dev/null +++ b/mysql-test/suite/funcs_1/r/innodb_storedproc_02.result @@ -0,0 +1,1395 @@ + +--source suite/funcs_1/storedproc/load_sp_tb.inc +-------------------------------------------------------------------------------- +SET @@global.max_heap_table_size=4294967295; +SET @@session.max_heap_table_size=4294967295; + +--source suite/funcs_1/storedproc/cleanup_sp_tb.inc +-------------------------------------------------------------------------------- +DROP DATABASE IF EXISTS db_storedproc; +DROP DATABASE IF EXISTS db_storedproc_1; +CREATE DATABASE db_storedproc; +CREATE DATABASE db_storedproc_1; +USE db_storedproc; +create table t1(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = innodb; +load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t1; +create table t2(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = innodb; +load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t2; +create table t3(f1 char(20),f2 char(20),f3 integer) engine = innodb; +load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t3.txt' into table t3; +create table t4(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = innodb; +load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t4; +USE db_storedproc_1; +create table t6(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = innodb; +load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t6; +USE db_storedproc; +create table t7 (f1 char(20), f2 char(25), f3 date, f4 int) engine = innodb; +load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t7.txt' into table t7; +Warnings: +Warning 1265 Data truncated for column 'f3' at row 1 +Warning 1265 Data truncated for column 'f3' at row 2 +Warning 1265 Data truncated for column 'f3' at row 3 +Warning 1265 Data truncated for column 'f3' at row 4 +Warning 1265 Data truncated for column 'f3' at row 5 +Warning 1265 Data truncated for column 'f3' at row 6 +Warning 1265 Data truncated for column 'f3' at row 7 +Warning 1265 Data truncated for column 'f3' at row 8 +Warning 1265 Data truncated for column 'f3' at row 9 +Warning 1265 Data truncated for column 'f3' at row 10 +create table t8 (f1 char(20), f2 char(25), f3 date, f4 int) engine = innodb; +load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t7.txt' into table t8; +Warnings: +Warning 1265 Data truncated for column 'f3' at row 1 +Warning 1265 Data truncated for column 'f3' at row 2 +Warning 1265 Data truncated for column 'f3' at row 3 +Warning 1265 Data truncated for column 'f3' at row 4 +Warning 1265 Data truncated for column 'f3' at row 5 +Warning 1265 Data truncated for column 'f3' at row 6 +Warning 1265 Data truncated for column 'f3' at row 7 +Warning 1265 Data truncated for column 'f3' at row 8 +Warning 1265 Data truncated for column 'f3' at row 9 +Warning 1265 Data truncated for column 'f3' at row 10 +create table t9(f1 int, f2 char(25), f3 int) engine = innodb; +load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t9.txt' into table t9; +create table t10(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = innodb; +load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t10; +create table t11(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = innodb; +load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t11; + +Section 3.1.2 - Syntax checks for the stored procedure-specific +programming statements BEGIN/END, DECLARE, SET, SELECT/INTO, OPEN, FETCH, CLOSE: +-------------------------------------------------------------------------------- + +Testcase 3.1.2.8: +----------------- + +Ensure that the scope of each BEGIN/END compound statement within a stored +procedure definition is properly applied +-------------------------------------------------------------------------------- +DROP PROCEDURE IF EXISTS sp1; +CREATE PROCEDURE sp1( ) +begin_label: BEGIN +declare x char DEFAULT 'x'; +declare y char DEFAULT 'y'; +set x = '1'; +set y = '2'; +label1: BEGIN +declare x char DEFAULT 'X'; +declare y char DEFAULT 'Y'; +SELECT f1, f2 into x, y from t2 limit 1; +SELECT '1.1', x, y; +label2: BEGIN +declare x char default 'a'; +declare y char default 'b'; +label3: BEGIN +declare x char default 'c'; +declare y char default 'd'; +label4: BEGIN +declare x char default 'e'; +declare y char default 'f'; +label5: BEGIN +declare x char default 'g'; +declare y char default 'h'; +SELECT 5, x, y; +END label5; +SELECT 4, x, y; +END label4; +SELECT 3, x, y; +END label3; +SELECT 2, x, y; +END label2; +END label1; +set @v1 = x; +set @v2 = y; +SELECT '1.2', @v1, @v2; +END begin_label// +CALL sp1(); +1.1 x y +1.1 a a +5 x y +5 g h +4 x y +4 e f +3 x y +3 c d +2 x y +2 a b +1.2 @v1 @v2 +1.2 1 2 +Warnings: +Warning 1265 Data truncated for column 'x' at row 1 +Warning 1265 Data truncated for column 'y' at row 1 +DROP PROCEDURE IF EXISTS sp1; + +Testcase 3.1.2.26: +------------------ + +Ensure that the initial value of every variable declared for a stored procedure +is either NULL or its DEFAULT value, as appropriate. +-------------------------------------------------------------------------------- +DROP PROCEDURE IF EXISTS sp1; +set @v1=0; +set @v2=0; +CREATE PROCEDURE sp1( ) +BEGIN +declare x1 char default 'x'; +declare y1 char; +declare x2 tinytext default 'tinytext'; +declare y2 tinytext; +declare x3 datetime default '2005-10-03 12:13:14'; +declare y3 datetime; +declare x4 float default 1.2; +declare y4 float; +declare x5 blob default 'b'; +declare y5 blob; +declare x6 smallint default 127; +declare y6 smallint; +SELECT x1, x2, x3, x4, x5, x6, y1, y2, y3, y4, y5, y6; +END// +CALL sp1(); +x1 x2 x3 x4 x5 x6 y1 y2 y3 y4 y5 y6 +x tinytext 2005-10-03 12:13:14 1.2 b 127 NULL NULL NULL NULL NULL NULL +DROP PROCEDURE sp1; + +Testcase 3.1.2.30: +------------------ + +Ensure that, when a stored procedure is called/executed, every variable always +uses the correct value: either the value with which it is initialized or the +value to which it is subsequently SET or otherwise assigned, as appropriate. +-------------------------------------------------------------------------------- +DROP PROCEDURE IF EXISTS sp1; +CREATE PROCEDURE sp1( IN invar INT, OUT outvar INT ) +BEGIN +declare x integer; +declare y integer default 1; +set @x = x; +set @y = y; +set @z = 234; +SELECT f1, f2 into @x, @y from t2 where f1='a`' and f2='a`' limit 1; +SELECT @x, @y, @z, invar; +BEGIN +set @x = 2; +SELECT @x, @y, @z; +SET outvar = @x * invar + @z * @f; +SET invar = outvar; +BEGIN +set @y = null, @z = 'abcd'; +SELECT @x, @y, @z; +END; +END; +END// +SET @invar = 100; +SET @outvar = @invar; +SET @f = 10; +SELECT @x, @y, @z, @invar, @outvar; +@x @y @z @invar @outvar +NULL NULL NULL 100 100 +CALL sp1( @invar, @outvar ); +@x @y @z invar +a` a` 234 100 +@x @y @z +2 a` 234 +@x @y @z +2 NULL abcd +SELECT @x, @y, @z, @invar, @outvar; +@x @y @z @invar @outvar +2 NULL abcd 100 2540 +DROP PROCEDURE sp1; + +Testcase 3.1.2.31: +------------------ + +Ensure that the SELECT ... INTO statement properly assigns values to the +variables in its variable list. +-------------------------------------------------------------------------------- +DROP PROCEDURE IF EXISTS sp1; +CREATE PROCEDURE sp1( ) +BEGIN +declare x integer; declare y integer; +set @x=x; +set @y=y; +SELECT f4, f3 into @x, @y from t2 where f4=-5000 and f3='1000-01-01' limit 1; +SELECT @x, @y; +END// +CALL sp1(); +@x @y +-5000 1000-01-01 +DROP PROCEDURE sp1; + +Testcase 3.1.2.32: +------------------ + +Ensure that a SELECT ... INTO statement that retrieves multiple rows is +rejected, with an appropriate error message. +-------------------------------------------------------------------------------- +DROP PROCEDURE IF EXISTS sp1; +CREATE PROCEDURE sp1( ) +BEGIN +declare x integer; declare y integer; +set @x=x; +set @y=y; +SELECT f4, f3 into @x, @y from t2; +END// +CALL sp1(); +ERROR 42000: Result consisted of more than one row +DROP PROCEDURE sp1; + +Testcase 3.1.2.33: +------------------ + +Ensure that a SELECT ... INTO statement that retrieves too many columns for the +number of variables in its variable list is rejected, with an appropriate error +message. +-------------------------------------------------------------------------------- +DROP PROCEDURE IF EXISTS sp1; +CREATE PROCEDURE sp1( ) +BEGIN +declare x integer; declare y integer; +set @x=x; +set @y=y; +SELECT f4, f3, f2, f1 into @x, @y from t2; +END// +CALL sp1(); +ERROR 21000: The used SELECT statements have a different number of columns +DROP PROCEDURE sp1; + +Testcase 3.1.2.34: +------------------ + +Ensure that a SELECT ... INTO statement that retrieves too few columns for the +number of variables in its variable list is rejected, with an appropriate error +message. +-------------------------------------------------------------------------------- +DROP PROCEDURE IF EXISTS sp1; +CREATE PROCEDURE sp1( ) +BEGIN +declare x integer; declare y integer; declare z integer; +set @x=x; +set @y=y; +set @z=z; +SELECT f4 into @x, @y, @z from t2; +END// +CALL sp1(); +ERROR 21000: The used SELECT statements have a different number of columns +DROP PROCEDURE sp1; + +Testcase 3.1.2.38: +------------------ + +Ensure that the scope of every condition declared is properly applied. +-------------------------------------------------------------------------------- +DROP PROCEDURE IF EXISTS h1; +DROP TABLE IF EXISTS res_t1; +create table res_t1(w char unique, x char); +insert into res_t1 values('a', 'b'); +CREATE PROCEDURE h1 () +BEGIN +declare x1, x2, x3, x4, x5, x6 int default 0; +SELECT '-1-', x1, x2, x3, x4, x5, x6; +BEGIN +declare condname condition for sqlstate '23000'; +declare continue handler for condname set x5 = 1; +set x6 = 0; +insert into res_t1 values ('a', 'b'); +set x6 = 1; +SELECT '-2-', x1, x2, x3, x4, x5, x6; +END; +begin1_label: BEGIN +BEGIN +declare condname condition for sqlstate '20000'; +declare continue handler for condname set x1 = 1; +set x2 = 0; +case x2 +when 1 then set x2=10; +when 2 then set x2=11; +END case; +set x2 = 1; +SELECT '-3-', x1, x2, x3, x4, x5, x6; +begin2_label: BEGIN +BEGIN +declare condname condition for sqlstate '23000'; +declare exit handler for condname set x3 = 1; +set x4= 1; +SELECT '-4a', x1, x2, x3, x4, x5, x6; +insert into res_t1 values ('a', 'b'); +set x4= 2; +SELECT '-4b', x1, x2, x3, x4, x5, x6; +END; +SELECT '-5-', x1, x2, x3, x4, x5, x6; +END begin2_label; +SELECT '-6-', x1, x2, x3, x4, x5, x6; +END; +SELECT '-7-', x1, x2, x3, x4, x5, x6; +END begin1_label; +SELECT 'END', x1, x2, x3, x4, x5, x6; +END// +CALL h1(); +-1- x1 x2 x3 x4 x5 x6 +-1- 0 0 0 0 0 0 +-2- x1 x2 x3 x4 x5 x6 +-2- 0 0 0 0 1 1 +-3- x1 x2 x3 x4 x5 x6 +-3- 1 1 0 0 1 1 +-4a x1 x2 x3 x4 x5 x6 +-4a 1 1 0 1 1 1 +-5- x1 x2 x3 x4 x5 x6 +-5- 1 1 1 1 1 1 +-6- x1 x2 x3 x4 x5 x6 +-6- 1 1 1 1 1 1 +-7- x1 x2 x3 x4 x5 x6 +-7- 1 1 1 1 1 1 +END x1 x2 x3 x4 x5 x6 +END 1 1 1 1 1 1 +DROP TABLE IF EXISTS tnull; +DROP PROCEDURE IF EXISTS sp1; +CREATE TABLE tnull(f1 int); +CREATE PROCEDURE sp1() +BEGIN +declare cond1 condition for sqlstate '42S02'; +declare continue handler for cond1 set @var2 = 1; +BEGIN +declare cond1 condition for sqlstate '23000'; +declare continue handler for cond1 set @var2 = 1; +END; +insert into tnull values(1); +END// +CALL sp1(); +DROP PROCEDURE h1; +drop table res_t1; +DROP PROCEDURE sp1; +DROP TABLE tnull; + +Testcase 3.1.2.43: +------------------ + +Ensure that the DECLARE ... HANDLER FOR statement can not declare any handler +for a condition declared outside of the scope of the handler. +-------------------------------------------------------------------------------- +DROP PROCEDURE IF EXISTS h1; +DROP PROCEDURE IF EXISTS h2; +drop table IF EXISTS res_t1; +create table res_t1(w char unique, x char); +insert into res_t1 values ('a', 'b'); +CREATE PROCEDURE h1 () +BEGIN +declare x1, x2, x3, x4, x5, x6 int default 0; +BEGIN +declare cond_1 condition for sqlstate '23000'; +declare continue handler for cond_1 set x5 = 1; +BEGIN +declare cond_2 condition for sqlstate '20000'; +declare continue handler for cond_1 set x1 = 1; +BEGIN +declare continue handler for cond_2 set x3 = 1; +set x2 = 1; +END; +set x6 = 0; +END; +BEGIN +declare continue handler for cond_1 set x1 = 1; +BEGIN +declare continue handler for cond_2 set x3 = 1; +set x2 = 1; +END; +set x6 = 0; +END; +END; +SELECT x1, x2, x3, x4, x5, x6; +END// +ERROR 42000: Undefined CONDITION: cond_2 +CREATE PROCEDURE h2 () +BEGIN +declare x1, x2, x3, x4, x5, x6 int default 0; +BEGIN +declare condname condition for sqlstate '23000'; +declare continue handler for condname set x5 = 1; +BEGIN +declare condname condition for sqlstate '20000'; +declare continue handler for condname set x1 = 1; +BEGIN +declare condname condition for sqlstate '42000'; +declare continue handler for condname set x3 = 1; +set x6 = 0; +insert into res_t1 values ('a', 'b'); +set x6 = 1; +set x4= 0; +CALL sp1(); +set x4= 1; +set x2 = 0; +case x2 +when 1 then set x2=10; +when 2 then set x2=11; +END case; +set x2 = 1; +END; +set x2 = 0; +case x2 +when 1 then set x2=10; +when 2 then set x2=11; +END case; +set x2 = 1; +set x6 = 0; +insert into res_t1 values ('a', 'b'); +set x6 = 1; +END; +END; +SELECT x1, x2, x3, x4, x5, x6; +END// +CALL h2(); +x1 x2 x3 x4 x5 x6 +1 1 1 1 1 1 +SELECT * FROM res_t1; +w x +a b +DROP PROCEDURE h2; +drop table res_t1; + +Testcase 3.1.2.44: +------------------ + +Ensure that the DECLARE ... HANDLER FOR statement cannot declare a handler for +any invalid, or undeclared, condition. +-------------------------------------------------------------------------------- +DROP PROCEDURE IF EXISTS h1; +CREATE PROCEDURE h1 () +BEGIN +declare x1, x2, x3, x4, x5, x6 int default 0; +BEGIN +declare condname1 condition for sqlstate '23000'; +BEGIN +declare condname2 condition for sqlstate '20000'; +declare continue handler for condname1 set x3 = 1; +declare continue handler for condname2 set x1 = 1; +END; +END; +BEGIN +declare condname3 condition for sqlstate '42000'; +declare continue handler for condname1 set x3 = 1; +declare continue handler for condname2 set x5 = 1; +declare continue handler for condname3 set x1 = 1; +END; +END// +ERROR 42000: Undefined CONDITION: condname1 +CREATE PROCEDURE h1 () +BEGIN +DECLARE x1 INT DEFAULT 0; +BEGIN +DECLARE condname1 CONDITION CHECK SQLSTATE '23000'; +END; +DECLARE CONTINUE HANDLER FOR condname1 SET x1 = 1; +END// +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'; +END; +DECLARE CONTINUE HANDLER FOR condname1 SET x1 = 1; +' at line 5 +CREATE PROCEDURE h1 () +BEGIN +DECLARE x1 INT DEFAULT 0; +BEGIN +DECLARE condname1 CONDITION FOR SQLSTATE 'qwert'; +END; +DECLARE CONTINUE HANDLER FOR condname1 SET x1 = 1; +END// +ERROR 42000: Bad SQLSTATE: 'qwert' + +Testcase 3.1.2.45 + 3.1.2.50: +----------------------------- + +45. Ensure that the scope of every handler declared is properly applied. +50. Ensure that a CONTINUE handler allows the execution of the stored procedure +. to continue once the handler statement has completed its own execution (that +. is, once the handler action statement has been executed). +-------------------------------------------------------------------------------- +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p1undo; +DROP PROCEDURE IF EXISTS h1; +DROP PROCEDURE IF EXISTS sp1; +drop table IF EXISTS res_t1; +==> 'UNDO' is still not supported. +create procedure p1undo () +begin +declare undo handler for sqlexception select '1'; +select * from tqq; +SELECT 'end of 1'; +end;// +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'; +select * from tqq; +SELECT 'end of 1'; +' at line 3 +create procedure p1 () +begin +declare exit handler for sqlexception select 'exit handler 1'; +begin +declare exit handler for sqlexception select 'exit handler 2'; +begin +declare continue handler for sqlexception select 'continue handler 3'; +drop table if exists tqq; +select * from tqq; +SELECT 'end of BEGIN/END 3'; +end; +drop table if exists tqq; +select * from tqq; +SELECT 'end of BEGIN/END 2'; +end; +select * from tqq; +SELECT 'end of BEGIN/END 1'; +end;// +call p1()// +continue handler 3 +continue handler 3 +end of BEGIN/END 3 +end of BEGIN/END 3 +exit handler 2 +exit handler 2 +exit handler 1 +exit handler 1 +Warnings: +Note 1051 Unknown table 'tqq' +Note 1051 Unknown table 'tqq' +create table res_t1(w char unique, x char); +insert into res_t1 values ('a', 'b'); +CREATE PROCEDURE h1 () +BEGIN +declare x1, x2, x3, x4, x5, x6 int default 0; +BEGIN +declare continue handler for sqlstate '23000' set x5 = 1; +insert into res_t1 values ('a', 'b'); +set x6 = 1; +END; +begin1_label: BEGIN +BEGIN +declare continue handler for sqlstate '23000' set x1 = 1; +insert into res_t1 values ('a', 'b'); +set x2 = 1; +begin2_label: BEGIN +BEGIN +declare exit handler for sqlstate '23000' set x3 = 1; +set x4= 1; +insert into res_t1 values ('a', 'b'); +set x4= 0; +END; +END begin2_label; +END; +END begin1_label; +SELECT x1, x2, x3, x4, x5, x6; +END// +CALL h1(); +x1 x2 x3 x4 x5 x6 +1 1 1 1 1 1 +CREATE PROCEDURE sp1() +begin1_label:BEGIN +declare exit handler for sqlstate '00000' set @var1 = 5; +set @var2 = 6; +begin2_label:BEGIN +declare continue handler for sqlstate '00000' set @var3 = 7; +set @var4 = 8; +SELECT @var3, @var4; +END begin2_label; +SELECT @var1, @var2; +END begin1_label// +CALL sp1(); +@var3 @var4 +NULL 8 +@var1 @var2 +NULL 6 +DROP PROCEDURE p1; +DROP PROCEDURE h1; +DROP PROCEDURE sp1; +DROP TABLE res_t1; + +Testcase 3.1.2.50: +------------------ +DROP PROCEDURE IF EXISTS sp1; +DROP PROCEDURE IF EXISTS sp2; +CREATE PROCEDURE sp1 (x int, y int) +BEGIN +set @y=0; +END// +CREATE PROCEDURE sp2 () +BEGIN +declare continue handler for sqlstate '42000' set @x2 = 1; +set @x=1; +SELECT @x2; +CALL sp1(1); +set @x=2; +SELECT @x2, @x; +END// +CALL sp2(); +@x2 +NULL +@x2 @x +1 2 +DROP PROCEDURE sp1; +DROP PROCEDURE sp2; + +Testcase 3.2.2.51: +------------------ + +Ensure that an EXIT handler causes the execution of the stored procedure to +terminate, within its scope, once the handler action statement has been +executed. +-------------------------------------------------------------------------------- +DROP PROCEDURE IF EXISTS sp1; +DROP PROCEDURE IF EXISTS sp2; +CREATE PROCEDURE sp1 (x int, y int) +BEGIN +set @x=0; +END// +CREATE PROCEDURE sp2 () +BEGIN +declare exit handler for sqlstate '42000' set @x2 = 1; +set @x2=0; +set @x=1; +SELECT '-1-', @x2, @x; +CALL sp1(1); +SELECT '-2-', @x2, @x; +set @x=2; +END// +CALL sp1(1); +ERROR 42000: Incorrect number of arguments for PROCEDURE db_storedproc.sp1; expected 2, got 1 +CALL sp2(); +-1- @x2 @x +-1- 0 1 +SELECT '-3-', @x2, @x; +-3- @x2 @x +-3- 1 1 +DROP PROCEDURE sp1; +DROP PROCEDURE sp2; + +Testcase 3.1.2.52: +------------------ + +Ensure that an EXIT handler does not cause the execution of the stored procedure +to terminate outside of its scope. +-------------------------------------------------------------------------------- +DROP PROCEDURE IF EXISTS sp1; +DROP PROCEDURE IF EXISTS sp2; +CREATE PROCEDURE sp1 (x int, y int) +BEGIN +set @x=0; +END// +CREATE PROCEDURE sp2() +BEGIN +declare continue handler for sqlstate '42000' set @x2 = 2; +set @x2 = 1; +set @x =20; +SELECT '-1-', @x2, @x; +BEGIN +declare exit handler for sqlstate '42000' set @x2 = 11; +SELECT '-2-', @x2, @x; +CALL sp1(1); +SELECT '-3a', @x2, @x; +set @x=21; +SELECT '-3b', @x2, @x; +END; +set @x=22; +SELECT '-4-', @x2, @x; +END// +CALL sp2(); +-1- @x2 @x +-1- 1 20 +-2- @x2 @x +-2- 1 20 +-4- @x2 @x +-4- 11 22 +DROP PROCEDURE sp1; +DROP PROCEDURE sp2; + +Testcase 3.1.2.54: +------------------ + +Ensure that a handler with a condition defined with an SQLSTATE that begins with +“01“ is always exactly equivalent in action to a handler with an SQLWARNING +condition. +-------------------------------------------------------------------------------- +DROP PROCEDURE IF EXISTS sp0; +DROP PROCEDURE IF EXISTS sp1; +DROP PROCEDURE IF EXISTS sp2; +DROP PROCEDURE IF EXISTS sp3; +DROP PROCEDURE IF EXISTS sp4; +DROP TABLE IF EXISTS temp; +CREATE TABLE temp( f1 CHAR, f2 CHAR); +CREATE PROCEDURE sp0() +BEGIN +set @done=0; +set @x=0; +insert into temp values('xxx', 'yy'); +set @x=1; +END// +CREATE PROCEDURE sp1() +BEGIN +declare continue handler for sqlstate '01000' set @done = 1; +set @done=0; +set @x=0; +insert into temp values('xxx', 'yy'); +set @x=1; +END// +CREATE PROCEDURE sp2() +BEGIN +declare continue handler for sqlwarning set @done = 1; +set @done=0; +set @x=0; +insert into temp values('xxx', 'yy'); +set @x=1; +END// +CREATE PROCEDURE sp3() +BEGIN +declare exit handler for sqlstate '01000' set @done = 1; +set @done=0; +set @x=0; +insert into temp values('xxx', 'yy'); +set @x=1; +END// +CREATE PROCEDURE sp4() +BEGIN +declare exit handler for sqlwarning set @done = 1; +set @done=0; +set @x=0; +insert into temp values('xxx', 'yy'); +set @x=1; +END// +INSERT INTO temp VALUES('0', NULL); +CALL sp0(); +Warnings: +Warning 1265 Data truncated for column 'f1' at row 1 +Warning 1265 Data truncated for column 'f2' at row 1 +SELECT @done, @x; +@done @x +0 1 +INSERT INTO temp VALUES('1', NULL); +CALL sp1(); +SELECT @done, @x; +@done @x +1 1 +INSERT INTO temp VALUES('2', NULL); +CALL sp2(); +SELECT @done, @x; +@done @x +1 1 +INSERT INTO temp VALUES('3', NULL); +CALL sp3(); +SELECT @done, @x; +@done @x +1 0 +INSERT INTO temp VALUES('4', NULL); +CALL sp4(); +SELECT @done, @x; +@done @x +1 0 +SELECT * FROM temp; +f1 f2 +0 NULL +x y +1 NULL +x y +2 NULL +x y +3 NULL +x y +4 NULL +x y +DROP PROCEDURE sp1; +DROP PROCEDURE sp2; +DROP PROCEDURE sp3; +DROP PROCEDURE sp4; +DROP TABLE temp; + +Testcase 3.1.2.56: +------------------ + +Ensure that a handler with a condition defined with an SQLSTATE that begins with +“02“ is always exactly equivalent in action to a handler with a NOT FOUND +condition. +-------------------------------------------------------------------------------- +DROP PROCEDURE IF EXISTS sp0; +DROP PROCEDURE IF EXISTS sp1; +DROP PROCEDURE IF EXISTS sp2; +DROP PROCEDURE IF EXISTS sp3; +DROP PROCEDURE IF EXISTS sp4; +CREATE PROCEDURE sp0() +BEGIN +DECLARE f1_value CHAR(20); +DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1; +SET @done = 0; +SET @x = 0; +OPEN cur1; +FETCH cur1 INTO f1_value; +SET @x = 1; +FETCH cur1 INTO f1_value; +SET @x = 2; +CLOSE cur1; +END// +CREATE PROCEDURE sp1() +BEGIN +DECLARE f1_value CHAR(20); +DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1; +declare continue handler for sqlstate '02000' set @done = 1; +SET @done = 0; +SET @x = 0; +OPEN cur1; +FETCH cur1 INTO f1_value; +SET @x = 1; +FETCH cur1 INTO f1_value; +SET @x = 2; +CLOSE cur1; +END// +CREATE PROCEDURE sp2() +BEGIN +DECLARE f1_value CHAR(20); +DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1; +declare continue handler for not found set @done = 1; +SET @done = 0; +SET @x = 0; +OPEN cur1; +FETCH cur1 INTO f1_value; +SET @x = 1; +FETCH cur1 INTO f1_value; +SET @x = 2; +CLOSE cur1; +END// +CREATE PROCEDURE sp3() +BEGIN +DECLARE f1_value CHAR(20); +DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1; +declare exit handler for sqlstate '02000' set @done = 1; +SET @done = 0; +SET @x = 0; +OPEN cur1; +FETCH cur1 INTO f1_value; +SET @x = 1; +FETCH cur1 INTO f1_value; +SET @x = 2; +CLOSE cur1; +END// +CREATE PROCEDURE sp4() +BEGIN +DECLARE f1_value CHAR(20); +DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1; +declare exit handler for not found set @done = 1; +SET @done = 0; +SET @x = 0; +OPEN cur1; +FETCH cur1 INTO f1_value; +SET @x = 1; +FETCH cur1 INTO f1_value; +SET @x = 2; +CLOSE cur1; +END// +CALL sp0(); +ERROR 02000: No data - zero rows fetched, selected, or processed +SELECT @done, @x; +@done @x +0 1 +CALL sp1(); +SELECT @done, @x; +@done @x +1 2 +CALL sp2(); +SELECT @done, @x; +@done @x +1 2 +CALL sp3(); +SELECT @done, @x; +@done @x +1 1 +CALL sp4(); +SELECT @done, @x; +@done @x +1 1 +DROP PROCEDURE sp0; +DROP PROCEDURE sp1; +DROP PROCEDURE sp2; +DROP PROCEDURE sp3; +DROP PROCEDURE sp4; + +Testcase 3.1.2.58: +------------------ + +Ensure that a handler with a condition defined with an SQLSTATE that begins with +anything other that “01“ or “02“ is always exactly equivalent in action to a +handler with an SQLEXCEPTION condition. +-------------------------------------------------------------------------------- +DROP PROCEDURE IF EXISTS sp0; +DROP PROCEDURE IF EXISTS sp1; +DROP PROCEDURE IF EXISTS sp2; +DROP PROCEDURE IF EXISTS sp3; +DROP PROCEDURE IF EXISTS sp4; +CREATE PROCEDURE sp0() +BEGIN +DECLARE f1_value CHAR(20); +DECLARE cv INT DEFAULT 0; +DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1; +SET @x = 1; +CASE cv +WHEN 2 THEN SET @x = 2; +WHEN 3 THEN SET @x = 3; +END case; +SET @x = 4; +SELECT f1, f2 FROM t2 +UNION +SELECT f1, f2,3 FROM t2; +SET @x = 5; +FETCH cur1 INTO f1_value; +SET @x = 6; +END// +CREATE PROCEDURE sp1() +BEGIN +DECLARE f1_value CHAR(20); +DECLARE cv INT DEFAULT 0; +DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1; +DECLARE continue HANDLER FOR SQLSTATE '20000' SELECT '20000' AS 'SQLSTATE'; +DECLARE continue HANDLER FOR SQLSTATE '21000' SELECT '21000' AS 'SQLSTATE'; +DECLARE continue HANDLER FOR SQLSTATE '24000' SELECT '24000' AS 'SQLSTATE'; +SET @x = 1; +CASE cv +WHEN 2 THEN SET @x = 2; +WHEN 3 THEN SET @x = 3; +END case; +SET @x = 4; +SELECT f1, f2 FROM t2 +UNION +SELECT f1, f2,3 FROM t2; +SET @x = 5; +FETCH cur1 INTO f1_value; +SET @x = 6; +END// +CREATE PROCEDURE sp2() +BEGIN +DECLARE f1_value CHAR(20); +DECLARE cv INT DEFAULT 0; +DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1; +DECLARE continue HANDLER FOR SQLEXCEPTION SELECT 'SQLEXCEPTION' AS 'SQLSTATE'; +DECLARE continue HANDLER FOR SQLSTATE '24000' SELECT '24000' AS 'SQLSTATE'; +SET @x = 1; +CASE cv +WHEN 2 THEN SET @x = 2; +WHEN 3 THEN SET @x = 3; +END case; +SET @x = 4; +SELECT f1, f2 FROM t2 +UNION +SELECT f1, f2,3 FROM t2; +SET @x = 5; +FETCH cur1 INTO f1_value; +SET @x = 6; +END// +CREATE PROCEDURE sp3() +BEGIN +DECLARE f1_value CHAR(20); +DECLARE cv INT DEFAULT 0; +DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1; +DECLARE EXIT HANDLER FOR SQLSTATE '20000' SELECT '20000' AS 'SQLSTATE'; +DECLARE EXIT HANDLER FOR SQLSTATE '21000' SELECT '21000' AS 'SQLSTATE'; +DECLARE EXIT HANDLER FOR SQLSTATE '24000' SELECT '24000' AS 'SQLSTATE'; +SET @x = 1; +CASE cv +WHEN 2 THEN SET @x = 2; +WHEN 3 THEN SET @x = 3; +END case; +SET @x = 4; +SELECT f1, f2 FROM t2 +UNION +SELECT f1, f2,3 FROM t2; +SET @x = 5; +FETCH cur1 INTO f1_value; +SET @x = 6; +END// +CREATE PROCEDURE sp4() +BEGIN +DECLARE f1_value CHAR(20); +DECLARE cv INT DEFAULT 0; +DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1; +DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT 'SQLEXCEPTION' AS 'SQLSTATE'; +DECLARE EXIT HANDLER FOR SQLSTATE '24000' SELECT '24000' AS 'SQLSTATE'; +SET @x = 1; +CASE cv +WHEN 2 THEN SET @x = 2; +WHEN 3 THEN SET @x = 3; +END case; +SET @x = 4; +SELECT f1, f2 FROM t2 +UNION +SELECT f1, f2,3 FROM t2; +SET @x = 5; +FETCH cur1 INTO f1_value; +SET @x = 6; +CLOSE cur1; +END// +CALL sp0(); +ERROR 20000: Case not found for CASE statement +SELECT '-0-', @x; +-0- @x +-0- 1 +CALL sp1(); +SQLSTATE +20000 +SQLSTATE +21000 +SQLSTATE +24000 +SELECT '-1-', @x; +-1- @x +-1- 6 +CALL sp2(); +SQLSTATE +SQLEXCEPTION +SQLSTATE +SQLEXCEPTION +SQLSTATE +24000 +SELECT '-2-', @x; +-2- @x +-2- 6 +CALL sp3(); +SQLSTATE +20000 +SELECT '-3-', @x; +-3- @x +-3- 1 +CALL sp4(); +SQLSTATE +SQLEXCEPTION +SELECT '-4-', @x; +-4- @x +-4- 1 +DROP PROCEDURE sp0; +DROP PROCEDURE sp1; +DROP PROCEDURE sp2; +DROP PROCEDURE sp3; +DROP PROCEDURE sp4; + +Testcase 3.1.2.65: +------------------ + +Ensure that FETCH <cursor name> returns the first row of the cursor_s result set +the first time FETCH is executed, that it returns each subsequent row of the +cursor_s result set each of the subsequent times FETCH is executed, and that it +returns a NOT FOUND warning if it is executed after the last row of the cursor_s +result set has already been fetched. +-------------------------------------------------------------------------------- +DROP PROCEDURE IF EXISTS sp1; +DROP TABLE IF EXISTS temp; +CREATE TABLE temp( +cnt INT, +f1 CHAR(20), +f2 CHAR(20), +f3 INT, +f4 CHAR(20), +f5 INT); +INSERT INTO temp VALUES(0, 'onip', 'abc', 8760, 'xyz', 10); +CREATE PROCEDURE sp1( ) +BEGIN +declare proceed int default 1; +declare count integer default 1; +declare f1_value char(20); +declare f2_value char(20); +declare f5_value char(20); +declare f4_value integer; +declare f6_value integer; +declare cur1 cursor for SELECT f1, f2, f4, f5, f6 from t2 +where f4 >=-5000 order by f4 limit 3; +open cur1; +while proceed do +SELECT count AS 'loop'; +fetch cur1 into f1_value, f2_value, f4_value, f5_value, f6_value; +insert into temp values (count, f1_value, f2_value, f4_value, f5_value, f6_value); +set count = count + 1; +END while; +END// +CALL sp1(); +loop +1 +loop +2 +loop +3 +loop +4 +ERROR 02000: No data - zero rows fetched, selected, or processed +SELECT * FROM temp; +cnt f1 f2 f3 f4 f5 +0 onip abc 8760 xyz 10 +1 a` a` -5000 a` -5000 +2 aaa aaa -4999 aaa -4999 +3 abaa abaa -4998 abaa -4998 +DROP TABLE temp; +DROP PROCEDURE sp1; + +Testcase 3.1.2.68: +------------------ + +Ensure that FETCH <cursor name> fails with an appropriate error message if the +number of columns to be fetched does not match the number of variables specified +by the FETCH statement. +-------------------------------------------------------------------------------- +DROP PROCEDURE IF EXISTS sp1; +DROP PROCEDURE IF EXISTS sp2; +--> not enough columns in FETCH statement +CREATE PROCEDURE sp1( ) +BEGIN +declare newf1 char(20); +declare cur1 cursor for SELECT f1, f2 from t2 limit 10; +declare continue handler for sqlstate '02000' SELECT 'HANDLER executed.' AS ''; +BEGIN +open cur1; +fetch cur1 into newf1; +SELECT newf1; +close cur1; +END; +END// +--> too many columns in FETCH statement +CREATE PROCEDURE sp2( ) +BEGIN +declare newf1 char(20); +declare newf2 char(20); +declare cur1 cursor for SELECT f1 from t2 limit 10; +declare continue handler for sqlstate '02000' SELECT 'HANDLER executed.' AS ''; +BEGIN +open cur1; +fetch cur1 into newf1, newf2; +SELECT newf1, newf2; +close cur1; +END; +END// +--> not enough columns in FETCH statement +CALL sp1(); +ERROR HY000: Incorrect number of FETCH variables +--> too many columns in FETCH statement +CALL sp2(); +ERROR HY000: Incorrect number of FETCH variables +DROP PROCEDURE sp1; +DROP PROCEDURE sp2; + +Testcase 3.1.2.75: +------------------ + +Ensure that, for nested compound statements, a cursor that was declared and +opened during an outer level of the statement is not closed when an inner level +of a compound statement ends. +-------------------------------------------------------------------------------- +DROP TABLE IF EXISTS temp1; +DROP PROCEDURE IF EXISTS sp1; +create table temp1( f0 char(20), f1 char(20), f2 char(20), f3 int, f4 char(20) ); +SELECT f1, f2, f4, f5 from t2 order by f4; +f1 f2 f4 f5 +a` a` -5000 a` +aaa aaa -4999 aaa +abaa abaa -4998 abaa +acaaa acaaa -4997 acaaa +adaaaa adaaaa -4996 adaaaa +aeaaaaa aeaaaaa -4995 aeaaaaa +afaaaaaa afaaaaaa -4994 afaaaaaa +agaaaaaaa agaaaaaaa -4993 agaaaaaaa +a^aaaaaaaa a^aaaaaaaa -4992 a^aaaaaaaa +a_aaaaaaaaa a_aaaaaaaaa -4991 a_aaaaaaaaa +CREATE PROCEDURE sp1( ) +BEGIN +declare count integer; +declare from0 char(20); +declare newf1 char(20); +declare newf2 char(20); +declare newf5 char(20); +declare newf4 integer; +declare cur1 cursor for SELECT f1, f2, f4, f5 from t2 where f4 >= -5000 order by f4 limit 5; +declare cur2 cursor for SELECT f1, f2, f4, f5 from t2 where f4 >= -5000 order by f4 limit 5; +open cur1; +open cur2; +BEGIN +declare continue handler for sqlstate '02000' set count=1; +fetch cur1 into newf1, newf2, newf4, newf5; +SELECT '-1-', count, newf1, newf2, newf4, newf5; +insert into temp1 values ('cur1_out', newf1, newf2, newf4, newf5); +set count= 4; +BEGIN +while count> 0 do +fetch cur1 into newf1, newf2, newf4, newf5; +SELECT '-2-', count, newf1, newf2, newf4, newf5; +set count = count- 1; +END while; +SELECT '-3-', count, newf1, newf2, newf4, newf4; +END; +BEGIN +fetch cur1 into newf1, newf2, newf4, newf5; +SELECT '-4-', newf1, newf2, newf4, newf5; +insert into temp1 values ('cur1_in', newf1, newf2, newf4, newf5); +END; +fetch cur2 into newf1, newf2, newf4, newf5; +SELECT '-5-', newf1, newf2, newf4, newf5; +insert into temp1 values ('cur2', newf1, newf2, newf4, newf5); +close cur1; +END; +fetch cur2 into newf1, newf2, newf4, newf5; +SELECT '-6-', newf1, newf2, newf4, newf5; +close cur2; +END// +CALL sp1(); +-1- count newf1 newf2 newf4 newf5 +-1- NULL a` a` -5000 a` +-2- count newf1 newf2 newf4 newf5 +-2- 4 aaa aaa -4999 aaa +-2- count newf1 newf2 newf4 newf5 +-2- 3 abaa abaa -4998 abaa +-2- count newf1 newf2 newf4 newf5 +-2- 2 acaaa acaaa -4997 acaaa +-2- count newf1 newf2 newf4 newf5 +-2- 1 adaaaa adaaaa -4996 adaaaa +-3- count newf1 newf2 newf4 newf4 +-3- 0 adaaaa adaaaa -4996 -4996 +-4- newf1 newf2 newf4 newf5 +-4- adaaaa adaaaa -4996 adaaaa +-5- newf1 newf2 newf4 newf5 +-5- a` a` -5000 a` +-6- newf1 newf2 newf4 newf5 +-6- aaa aaa -4999 aaa +SELECT * from temp1; +f0 f1 f2 f3 f4 +cur1_out a` a` -5000 a` +cur1_in adaaaa adaaaa -4996 adaaaa +cur2 a` a` -5000 a` +DROP PROCEDURE sp1; +drop table temp1; + +Testcase 3.1.2.76: +------------------ + +Ensure that all cursors operate asensitively, so that there is no concurrency +conflict between cursors operating on the same, or similar, sets of results +during execution of one or more stored procedures. +-------------------------------------------------------------------------------- +DROP PROCEDURE IF EXISTS sp1; +drop table IF EXISTS temp1; +drop table IF EXISTS temp2; +create table temp1( f0 char(10), cnt int, f1 char(20), f2 char(20), f3 date, f4 integer ); +create table temp2( f0 char(10), cnt int, f1 char(20), f2 char(20), f3 date, f4 integer ); +CREATE PROCEDURE sp_inner( ) +BEGIN +declare proceed int default 1; +declare i_count integer default 20; +declare i_newf1 char(20); +declare i_newf2 char(20); +declare i_newf3 date; +declare i_newf4 integer; +declare i_newf11 char(20); +declare i_newf12 char(20); +declare i_newf13 date; +declare i_newf14 integer; +declare cur1 cursor for SELECT f1, f2, f3, f4 from t2 +where f4>=-5000 order by f4 limit 4; +declare cur2 cursor for SELECT f1, f2, f3, f4 from t2 +where f4>=-5000 order by f4 limit 3; +declare continue handler for sqlstate '02000' set proceed=0; +open cur1; +open cur2; +set i_count = 10; +while proceed do +fetch cur1 into i_newf1, i_newf2, i_newf3, i_newf4; +IF proceed THEN +insert into temp1 values ('sp_inner', i_count, i_newf1, i_newf2, i_newf3, i_newf4); +fetch cur2 into i_newf11, i_newf12, i_newf13, i_newf14; +IF proceed THEN +insert into temp2 values ('sp_inner', i_count, i_newf11, i_newf12, i_newf13, i_newf14); +END IF; +END IF; +set i_count = i_count - 1; +END while; +close cur1; +close cur2; +END// +CREATE PROCEDURE sp_outer( ) +BEGIN +DECLARE proceed INT DEFAULT 1; +DECLARE o_count INTEGER DEFAULT 20; +DECLARE o_newf1 CHAR(20); +DECLARE o_newf2 CHAR(20); +DECLARE o_newf3 DATE; +DECLARE o_newf4 INTEGER; +DECLARE o_newf11 CHAR(20); +DECLARE o_newf12 CHAR(20); +DECLARE o_newf13 DATE; +DECLARE o_newf14 INTEGER; +DECLARE cur1 CURSOR FOR SELECT f1, f2, f3, f4 FROM t2 +WHERE f4>=-5000 ORDER BY f4 LIMIT 5; +DECLARE cur2 CURSOR FOR SELECT f1, f2, f3, f4 FROM t2 +WHERE f4>=-5000 ORDER BY f4 LIMIT 5; +DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET proceed=0; +OPEN cur1; +OPEN cur2; +SET o_count = 1; +WHILE proceed DO +FETCH cur1 INTO o_newf1, o_newf2, o_newf3, o_newf4; +IF proceed THEN +INSERT INTO temp1 VALUES ('_sp_out_', o_count, o_newf1, o_newf2, o_newf3, o_newf4); +CALL sp_inner(); +FETCH cur2 INTO o_newf11, o_newf12, o_newf13, o_newf14; +IF proceed THEN +INSERT INTO temp2 VALUES ('_sp_out_', o_count, o_newf11, o_newf12, o_newf13, o_newf14); +END IF; +END IF; +SET o_count = o_count + 1; +END WHILE; +CLOSE cur1; +CLOSE cur2; +END// +CALL sp_outer(); +SELECT * FROM temp1; +f0 cnt f1 f2 f3 f4 +_sp_out_ 1 a` a` 1000-01-01 -5000 +sp_inner 10 a` a` 1000-01-01 -5000 +sp_inner 9 aaa aaa 1000-01-02 -4999 +sp_inner 8 abaa abaa 1000-01-03 -4998 +sp_inner 7 acaaa acaaa 1000-01-04 -4997 +_sp_out_ 2 aaa aaa 1000-01-02 -4999 +sp_inner 10 a` a` 1000-01-01 -5000 +sp_inner 9 aaa aaa 1000-01-02 -4999 +sp_inner 8 abaa abaa 1000-01-03 -4998 +sp_inner 7 acaaa acaaa 1000-01-04 -4997 +_sp_out_ 3 abaa abaa 1000-01-03 -4998 +sp_inner 10 a` a` 1000-01-01 -5000 +sp_inner 9 aaa aaa 1000-01-02 -4999 +sp_inner 8 abaa abaa 1000-01-03 -4998 +sp_inner 7 acaaa acaaa 1000-01-04 -4997 +_sp_out_ 4 acaaa acaaa 1000-01-04 -4997 +sp_inner 10 a` a` 1000-01-01 -5000 +sp_inner 9 aaa aaa 1000-01-02 -4999 +sp_inner 8 abaa abaa 1000-01-03 -4998 +sp_inner 7 acaaa acaaa 1000-01-04 -4997 +_sp_out_ 5 adaaaa adaaaa 1000-01-05 -4996 +sp_inner 10 a` a` 1000-01-01 -5000 +sp_inner 9 aaa aaa 1000-01-02 -4999 +sp_inner 8 abaa abaa 1000-01-03 -4998 +sp_inner 7 acaaa acaaa 1000-01-04 -4997 +SELECT * FROM temp2; +f0 cnt f1 f2 f3 f4 +sp_inner 10 a` a` 1000-01-01 -5000 +sp_inner 9 aaa aaa 1000-01-02 -4999 +sp_inner 8 abaa abaa 1000-01-03 -4998 +_sp_out_ 1 a` a` 1000-01-01 -5000 +sp_inner 10 a` a` 1000-01-01 -5000 +sp_inner 9 aaa aaa 1000-01-02 -4999 +sp_inner 8 abaa abaa 1000-01-03 -4998 +_sp_out_ 2 aaa aaa 1000-01-02 -4999 +sp_inner 10 a` a` 1000-01-01 -5000 +sp_inner 9 aaa aaa 1000-01-02 -4999 +sp_inner 8 abaa abaa 1000-01-03 -4998 +_sp_out_ 3 abaa abaa 1000-01-03 -4998 +sp_inner 10 a` a` 1000-01-01 -5000 +sp_inner 9 aaa aaa 1000-01-02 -4999 +sp_inner 8 abaa abaa 1000-01-03 -4998 +_sp_out_ 4 acaaa acaaa 1000-01-04 -4997 +sp_inner 10 a` a` 1000-01-01 -5000 +sp_inner 9 aaa aaa 1000-01-02 -4999 +sp_inner 8 abaa abaa 1000-01-03 -4998 +_sp_out_ 5 adaaaa adaaaa 1000-01-05 -4996 +DROP PROCEDURE sp_outer; +DROP PROCEDURE sp_inner; +DROP TABLE temp1; +DROP TABLE temp2; + +--source suite/funcs_1/storedproc/cleanup_sp_tb.inc +-------------------------------------------------------------------------------- +DROP DATABASE IF EXISTS db_storedproc; +DROP DATABASE IF EXISTS db_storedproc_1; + +. +++ END OF SCRIPT +++ +-------------------------------------------------------------------------------- |