storedproc_03.inc

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

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
DROP PROCEDURE IF EXISTS sp31316;
--enable_warnings

delimiter //;

# wrong label at iterate
# Error: SQLSTATE: 42000 (ER_SP_LILABEL_MISMATCH)
#        Message: %s with no matching label: %s
--error ER_SP_LILABEL_MISMATCH
CREATE PROCEDURE sp31316( )
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 temp values( count1, count2);
         set count1 = count1 + 1;
         iterate label3;
      END loop label2;
      set count2 = count2 + 1;
      iterate label1;
   END loop label1;
END//
delimiter ;//

# cleanup 3.1.3.16
#DROP PROCEDURE sp31316;


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

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

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

delimiter //;
CREATE PROCEDURE sp17( )
BEGIN
   declare count1 integer default 1;
   declare count2 integer default 1;
   repeat
      set count1 = count1 + 1;
      set count2 = 1;
      label1: repeat
         set count2 = count2 + 1;
         insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'xyz' , 'pqr', count1);
         until count2 > 3
      END repeat label1;
      until count1 > 3
   END repeat;
END//
delimiter ;//

CALL sp17();
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;

# cleanup 3.1.3.18
DROP PROCEDURE sp17;
DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;



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

--disable_warnings
drop table IF EXISTS res_t21;
DROP PROCEDURE IF EXISTS sp21;
--enable_warnings

create table res_t21(name text(10), surname blob(20), age_averylongfieldname_averylongname_1234569 smallint);
insert into res_t21 values('ashwin', 'mokadam', 25);

delimiter //;
CREATE PROCEDURE sp21( )
BEGIN
   declare count1 integer default 0;
   declare count2 integer default 0;
   while count1 < 3 do
      BEGIN
         declare ithisissamevariablename int default 100;
         SELECT ithisissamevariablename;
         BEGIN
            declare ithisissamevariablename int default 200;
            SELECT ithisissamevariablename;
         END;
         set count2 = 0;
         label1: while count2 < 3 do
            BEGIN
               declare count1 integer default 7;
               set count2 = count2 + 1;
               insert into res_t21 values( 'xyz' , 'pqr', count2);
               label2: while count1 < 10 do
                  set count1 = count1 + 1;
                  insert into res_t21 values( 'xyz' , 'pqr', count1);
               END while label2;
            END;
         END while label1;
         set count1 = count1 + 1;
      END;
   END while;
END//
delimiter ;//

CALL sp21();
SELECT * from res_t21;

# cleanup 3.1.3.
DROP PROCEDURE sp21;
drop table res_t21;


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

--disable_warnings
DROP TABLE IF EXISTS res_tbl;
DROP PROCEDURE IF EXISTS sp31330;
--enable_warnings

create table res_tbl (f1 int, f2 text, f3 blob, f4 date,
      f5 set('one', 'two', 'three', 'four', 'five') default 'one');

delimiter //;
#FIXME: can be enhanced more and more ...
CREATE PROCEDURE sp31330 (path int)
BEGIN
   declare count int default 1;
   declare var1 text;
   declare var2 blob;
   declare var3 date;
   declare var4 set('one', 'two', 'three', 'four', 'five') DEFAULT 'five';
   case
      when path=1 then
         set var3 = '2000-11-09';
         set var1 = 'flowing through case 1';
         label1: loop
            if count > 5 then
               if var4=1000 then
                  set var2 = 'exiting out of case 1 - invalid SET';
               END if;
               if var4='two' then
                  set var2 = 'exiting out of case 1';
               END if;
               insert into res_tbl values (1, var1, var2, var3, (count-2));
               leave label1;
            elseif count = 5 then
               set count= count + 2;
               set var4='two';
               iterate label1;
            else
               set count= count + 1;
            END if;
            set var4='one';
         END loop label1;
      when path=2 then
         set var3 = '1989-11-09';
         set var1 = 'flowing through case 2';
         set @count3=0;
         label2: repeat
            set count=count + 1;
            set @count2=1;
            while @count2 <= 5 do
               set @count2 = @count2 + 1;
            END while;
            SELECT @count2;
            set @count3=@count3 + @count2;
            until count > 5
         END repeat label2;
         set var2 = 'exiting out of case 2';
         set var4 = count-3;
         SELECT @count3;
         insert into res_tbl values (2, var1, var2, var3, var4);
      ELSE BEGIN
         set @error_opt='undefined path specified';
         SELECT @error_opt;
      END;
   END case;
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 sp31330();

CALL sp31330(1);
SELECT * from res_tbl;

CALL sp31330(2);
SELECT * from res_tbl;

CALL sp31330(4);

# cleanup 3.1.3.30
DROP PROCEDURE sp31330;
drop table res_tbl;



# ==============================================================================
# USE the same .inc to cleanup before and after the test
--source suite/funcs_1/storedproc/cleanup_sp_tb.inc

# ==============================================================================
--echo
--echo .                               +++ END OF SCRIPT +++
--echo --------------------------------------------------------------------------------
# ==============================================================================