memory_storedproc_02.result

来自「这个文件是windows mysql源码」· RESULT 代码 · 共 1,399 行 · 第 1/3 页

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