memory_storedproc_02.result

来自「视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.」· RESULT 代码 · 共 700 行 · 第 1/2 页

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
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