storedproc_03.inc

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

#### suite/funcs_1/storedproc/storedproc_03.inc
#
--source suite/funcs_1/storedproc/load_sp_tb.inc

# ==============================================================================
# (numbering from requirement document TP v1.0, Last updated: 25 Jan 2005 01:00)
#
# 3.1.3 Syntax checks for the stored procedure-specific flow control statements IF, CASE, LOOP, LEAVE, ITERATE, REPEAT, WHILE:
#
#-  1. Ensure that all subclauses that should be supported are supported.
#-  2. Ensure that all subclauses that should not be supported are disallowed with an appropriate error message.
#-  3. Ensure that all supported subclauses are supported only in the correct order.
#-  4. Ensure that an appropriate error message is returned if a subclause is out-of-order in a stored procedure definition.
#-  5. Ensure that all subclauses that are defined to be mandatory are indeed required to be mandatory by the MySQL server and tools.
#-  6. Ensure that any subclauses that are defined to be optional are indeed treated as optional by the MySQL server and tools.
##  7. Ensure that the IF statement acts correctly for all variants, including cases where statements are nested.
##  8. Ensure that the CASE statement acts correctly for all variants, including cases where statements are nested.
##  9. Ensure that the LOOP statement acts correctly for all variants, including cases where statements are nested.
#- 10. Ensure that the labels enclosing each LOOP statement must match.
#- 11. Ensure that it is possible to put a beginning label at the start of a LOOP statement without also requiring an ending label at the end of the same statement.
#- 12. Ensure that it is not possible to put an ending label at the end of a LOOP statement without also requiring a matching beginning label at the start of the same statement.
#- 13. Ensure that every beginning label must end with a colon (:).
#- 14. Ensure that every beginning label with the same scope must be unique.
## 15. Ensure that the LEAVE statement acts correctly for all variants, including cases where statements are nested.
## 16. Ensure that the ITERATE statement acts correctly for all variants, including cases where statements are nested.
#- 17. Ensure that the ITERATE statement fails, with an appropriate error message, if it appears in any context other than within LOOP, REPEAT, or WHILE statements.
## 18. Ensure that the REPEAT statement acts correctly for all variants, including cases where statements are nested.
#- 19. Ensure that the labels enclosing each REPEAT statement must match.
#- 20. Ensure that it is possible to put a beginning label at the start of a REPEAT statement without also requiring an ending label at the end of the same statement.
#- 21. Ensure that it is not possible to put an ending label at the end of a REPEAT statement without also requiring a matching beginning label at the start of the same statement.
#- 22. Ensure that every beginning label must end with a colon (:).
#- 23. Ensure that every beginning label with the same scope must be unique.
## 24. Ensure that the WHILE statement acts correctly for all variants, including cases where statements are nested.
#- 25. Ensure that the labels enclosing each WHILE statement must match.
#- 26. Ensure that it is possible to put a beginning label at the start of a WHILE statement without also requiring an ending label at the end of the same statement.
#- 27. Ensure that it is not possible to put an ending label at the end of a WHILE statement without also requiring a matching beginning label at the start of the same statement.
#- 28. Ensure that every beginning label must end with a colon (:).
#- 29. Ensure that every beginning label with the same scope must be unique.
## 30. Ensure that multiple cases of all possible combinations of the control flow statements, nested within multiple compound statements within a stored procedure, always act correctly and return the expected result.
#
# ==============================================================================
let $message= Section 3.1.3 - Syntax checks for the stored procedure-specific flow
control statements IF, CASE, LOOP, LEAVE, ITERATE, REPEAT, WHILE:;
--source include/show_msg80.inc

#FIXME # 3.1.3: enhance syntax checks with very complicated checks

# ------------------------------------------------------------------------------
let $message= Testcase 3.1.3.7:;
--source include/show_msg.inc
let $message=
Ensure that the IF statement acts correctly for all variants, including cases
where statements are nested.;
--source include/show_msg80.inc

--disable_warnings
DROP TABLE IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
DROP PROCEDURE IF EXISTS sp9;
--enable_warnings

CREATE TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 CHAR(20), f2 VARCHAR(20), f3 SMALLINT);

delimiter //;
CREATE PROCEDURE sp9( action char(20), subaction char(20) )
BEGIN
   if action = 'action' then
      if subaction = 'subaction' then
         insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'subaction' , 1);
      else
         insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'none' , 2);
      END if;
   else
      if subaction = 'subaction'
      then
         insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'subaction' , 3);
      elseif subaction = 'subaction1'
      then
         BEGIN
            insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values ('none', 'subaction1', 4);
         END;
      else
         insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'none' , 5);
      END if;
   END if;
END//
delimiter ;//

CALL sp9( 'action', 'subaction' );
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=1;

CALL sp9( 'temp', 'subaction' );
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=3;

CALL sp9( 'temp', 'subaction1' );
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=4;

CALL sp9( 'action', 'temp' );
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=2;

CALL sp9( 'temp', 'temp' );
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=5;

# cleanup 3.1.3.7
DROP PROCEDURE sp9;
DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;


# ------------------------------------------------------------------------------
let $message= Testcase 3.1.3.8.:;
--source include/show_msg.inc
let $message=
Ensure that the CASE statement acts correctly for all variants, including cases
where statements are nested.;
--source include/show_msg80.inc

--disable_warnings
drop table IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
DROP PROCEDURE IF EXISTS sp10;
--enable_warnings

create table res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 char(20), f2 varchar(20), f3 smallint);

delimiter //;
CREATE PROCEDURE sp10( action char(20), subaction char(20) )
BEGIN
   case action
   when 'action' then
      case
      when subaction = 'subaction_1' then
         insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'subaction_2' , 1);
      when subaction = 'subaction_2' then
         insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'subaction_2' , 2);
      else
         insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'none' , 3);
      END case;
    else
      case
      when subaction = 'subaction_1' then
         insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'subaction_1' , 4);
      when subaction = 'subaction_2' then
         insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'subaction_2' , 5);
      else
         insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'none' , 6);
      END case;
   END case;
END//
delimiter ;//

CALL sp10( 'action', 'subaction_1' );
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;

CALL sp10( 'action', 'subaction_2' );
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;

CALL sp10( 'temp', 'subaction_1' );
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;

CALL sp10( 'temp', 'subaction_2' );
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;

CALL sp10( 'action', 'temp' );
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;

CALL sp10( 'temp', 'temp' );
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;

# cleanup 3.1.3.8
DROP PROCEDURE sp10;
DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;


# ------------------------------------------------------------------------------
let $message= Testcase 3.1.3.9 + 3.1.3.15:;
--source include/show_msg.inc
let $message=
09. Ensure that the LOOP statement acts correctly for all variants, including
.   cases where statements are nested.
15. Ensure that the LEAVE statement acts correctly for all variants, including
.   cases where statements are nested.;
--source include/show_msg80.inc

--disable_warnings
DROP TABLE IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
DROP PROCEDURE IF EXISTS sp11;
--enable_warnings

CREATE TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 CHAR(20), f2 VARCHAR(20), f3 SMALLINT);

delimiter //;
CREATE PROCEDURE sp11( )
BEGIN
   declare count1 integer default 1;
   declare count2 integer default 1;
   label1: loop
      if count2 > 3 then leave label1;
      END if;
      set count1 = 1;
      label2: loop
         if count1 > 4 then leave label2;
         END if;
         insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'xyz' , 'pqr', count1);
         set count1 = count1 + 1;
         iterate label2;
      END loop label2;
      set count2 = count2 + 1;
      iterate label1;
   END loop label1;
END//
delimiter ;//

CALL sp11();
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;

# cleanup 3.1.3.9
DROP PROCEDURE sp11;
DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;


# ------------------------------------------------------------------------------
let $message= Testcase 3.1.3.16:;
--source include/show_msg.inc
let $message=
Ensure that the ITERATE statement acts correctly for all variants, including
cases where statements are nested.
(tests for this testcase are also included in other testcases);
--source include/show_msg80.inc

--disable_warnings