summaryrefslogtreecommitdiff
path: root/mysql-test/suite/funcs_1/r/ndb_storedproc_02.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/suite/funcs_1/r/ndb_storedproc_02.result')
-rw-r--r--mysql-test/suite/funcs_1/r/ndb_storedproc_02.result1398
1 files changed, 0 insertions, 1398 deletions
diff --git a/mysql-test/suite/funcs_1/r/ndb_storedproc_02.result b/mysql-test/suite/funcs_1/r/ndb_storedproc_02.result
deleted file mode 100644
index 3e2d084aa0c..00000000000
--- a/mysql-test/suite/funcs_1/r/ndb_storedproc_02.result
+++ /dev/null
@@ -1,1398 +0,0 @@
-
---source suite/funcs_1/storedproc/load_sp_tb.inc
---------------------------------------------------------------------------------
-
---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 = <engine_to_be_tested>;
-load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t1;
-create table t2(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
-engine = <engine_to_be_tested>;
-load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t2;
-create table t3(f1 char(20),f2 char(20),f3 integer) engine = <engine_to_be_tested>;
-load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t3.txt' into table t3;
-create table t4(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
-engine = <engine_to_be_tested>;
-load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/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 = <engine_to_be_tested>;
-load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6;
-USE db_storedproc;
-create table t7 (f1 char(20), f2 char(25), f3 date, f4 int)
-engine = <engine_to_be_tested>;
-load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/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 = <engine_to_be_tested>;
-load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/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 = <engine_to_be_tested>;
-load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t9.txt' into table t9;
-create table t10(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
-engine = <engine_to_be_tested>;
-load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t10;
-create table t11(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
-engine = <engine_to_be_tested>;
-load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/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
-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
-This will fail, SQLSTATE 00000 is not allowed
-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//
-ERROR 42000: Bad SQLSTATE: '00000'
-Verify SP wasn't created
-CALL sp1();
-ERROR 42000: PROCEDURE db_storedproc.sp1 does not exist
-DROP PROCEDURE p1;
-DROP PROCEDURE h1;
-DROP PROCEDURE IF EXISTS 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 +++
---------------------------------------------------------------------------------
View Lorry Controller Status