storedproc_02.inc

来自「这个文件是windows mysql源码」· INC 代码 · 共 1,656 行 · 第 1/4 页

DROP PROCEDURE p1;
DROP PROCEDURE h1;
DROP PROCEDURE sp1;
DROP TABLE res_t1;


# ------------------------------------------------------------------------------
let $message= Testcase 3.1.2.50:;
--source include/show_msg.inc

# Testcase: 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).


--disable_warnings
DROP PROCEDURE IF EXISTS sp1;
DROP PROCEDURE IF EXISTS sp2;
--enable_warnings

delimiter //;
CREATE PROCEDURE sp1 (x int, y int)
BEGIN
    set @y=0;
END//
delimiter ;//

delimiter //;
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//
delimiter ;//

CALL sp2();

# cleanup
DROP PROCEDURE sp1;
DROP PROCEDURE sp2;


# ------------------------------------------------------------------------------
let $message= Testcase 3.2.2.51:;
--source include/show_msg.inc
let $message=
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.;
--source include/show_msg80.inc
# also tested in 3.1.2.45

--disable_warnings
DROP PROCEDURE IF EXISTS sp1;
DROP PROCEDURE IF EXISTS sp2;
--enable_warnings

delimiter //;
CREATE PROCEDURE sp1 (x int, y int)
BEGIN
    set @x=0;
END//
delimiter ;//

delimiter //;
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//
delimiter ;//

# Error: SQLSTATE: 42000 (ER_SP_WRONG_NO_OF_ARGS)
#        Message: Incorrect number of arguments for %s %s; expected %u, got %u
--error ER_SP_WRONG_NO_OF_ARGS
CALL sp1(1);
CALL sp2();
SELECT '-3-', @x2, @x;

# cleanup 3.1.2.51
DROP PROCEDURE sp1;
DROP PROCEDURE sp2;


# ------------------------------------------------------------------------------
let $message= Testcase 3.1.2.52:;
--source include/show_msg.inc
let $message=
Ensure that an EXIT handler does not cause the execution of the stored procedure
to terminate outside of its scope.;
--source include/show_msg80.inc
# tested also above in

--disable_warnings
DROP PROCEDURE IF EXISTS sp1;
DROP PROCEDURE IF EXISTS sp2;
--enable_warnings

delimiter //;
CREATE PROCEDURE sp1 (x int, y int)
BEGIN
    set @x=0;
END//
delimiter ;//

delimiter //;
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//
delimiter ;//

CALL sp2();

# cleanup 3.1.2.52
DROP PROCEDURE sp1;
DROP PROCEDURE sp2;


# ------------------------------------------------------------------------------
let $message= Testcase 3.1.2.54:;
--source include/show_msg.inc
let $message=
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.;
--source include/show_msg80.inc

--disable_warnings
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;
--enable_warnings

CREATE TABLE temp( f1 CHAR, f2 CHAR);

delimiter //;
# 0 - without handler
CREATE PROCEDURE sp0()
BEGIN
   set @done=0;
   set @x=0;
   insert into temp values('xxx', 'yy');
   set @x=1;
END//

# 1st one with SQLSTATE + CONTINUE
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//

# 2nd one with SQLWARNING + CONTINUE
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//

# 3 with SQLSTATE + EXIT
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//

# 4 with SQLWARNING + EXIT
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//
delimiter ;//

INSERT INTO temp VALUES('0', NULL);
CALL sp0();
SELECT @done, @x;

INSERT INTO temp VALUES('1', NULL);
CALL sp1();
SELECT @done, @x;

INSERT INTO temp VALUES('2', NULL);
CALL sp2();
SELECT @done, @x;

INSERT INTO temp VALUES('3', NULL);
CALL sp3();
SELECT @done, @x;

INSERT INTO temp VALUES('4', NULL);
CALL sp4();
SELECT @done, @x;

SELECT * FROM temp;

# cleanup 3.1.2.54
DROP PROCEDURE sp1;
DROP PROCEDURE sp2;
DROP PROCEDURE sp3;
DROP PROCEDURE sp4;
DROP TABLE temp;


# ------------------------------------------------------------------------------
let $message= Testcase 3.1.2.56:;
--source include/show_msg.inc
let $message=
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.;
--source include/show_msg80.inc

--disable_warnings
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;
--enable_warnings

delimiter //;
# 0 - wihtout handler
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//

# 1st one with SQLSTATE + CONTINUE
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//

# 2nd one with NOT FOUND + CONTINUE
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//

# 3 with SQLSTATE + EXIT
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//

# 4 with NOT FOUND + EXIT
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//
delimiter ;//

--error ER_SP_FETCH_NO_DATA
CALL sp0();
SELECT @done, @x;

CALL sp1();
SELECT @done, @x;

CALL sp2();
SELECT @done, @x;

CALL sp3();
SELECT @done, @x;

CALL sp4();
SELECT @done, @x;

# cleanup 3.1.2.56
DROP PROCEDURE sp0;
DROP PROCEDURE sp1;
DROP PROCEDURE sp2;
DROP PROCEDURE sp3;
DROP PROCEDURE sp4;


# ------------------------------------------------------------------------------
let $message= Testcase 3.1.2.58:;
--source include/show_msg.inc
let $message=
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.;
--source include/show_msg80.inc

# Error: SQLSTATE: 20000 (ER_SP_CASE_NOT_FOUND)
#        Message: Case not found for CASE statement
# Error: SQLSTATE: 21000 (ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT)
#        Message: The used SELECT statements have a different number of columns
# Error: SQLSTATE: 24000 (ER_SP_CURSOR_NOT_OPEN)
#        Message: Cursor is not open

--disable_warnings
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;
--enable_warnings

delimiter //;
# 0 - without handler
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//

# 1 - SQLSTATEs - CONTINUE
CREATE PROCEDURE sp1()
BEGIN
   DECLARE f1_value CHAR(20);
   DECLARE cv INT DEFAULT 0;