storedproc_02.inc

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

message.;
--source include/show_msg80.inc

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

delimiter //;
CREATE PROCEDURE sp1( )
BEGIN
    declare x integer; declare y integer;
    set @x=x;
    set @y=y;
    SELECT f4, f3, f2, f1 into @x, @y from t2;
END//
delimiter ;//

--error ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT
CALL sp1();

# cleanup 3.1.2.33
DROP PROCEDURE sp1;


# ------------------------------------------------------------------------------
let $message= Testcase 3.1.2.34:;
--source include/show_msg.inc
let $message=
Ensure that a SELECT ... INTO statement that retrieves too few columns for the
number of variables in its variable list is rejected, with an appropriate error
message.;
--source include/show_msg80.inc

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

delimiter //;
CREATE PROCEDURE sp1( )
BEGIN
    declare x integer; declare y integer; declare z integer;
    set @x=x;
    set @y=y;
    set @z=z;
    SELECT f4 into @x, @y, @z from t2;
END//
delimiter ;//

--error ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT
CALL sp1();

# cleanup 3.1.2.34
DROP PROCEDURE sp1;


# ------------------------------------------------------------------------------
let $message= Testcase 3.1.2.38:;
--source include/show_msg.inc
let $message=
Ensure that the scope of every condition declared is properly applied.;
--source include/show_msg80.inc

--disable_warnings
DROP PROCEDURE IF EXISTS h1;
DROP TABLE IF EXISTS res_t1;
--enable_warnings

create table res_t1(w char unique, x char);

insert into res_t1 values('a', 'b');

# Error: SQLSTATE: 20000 (ER_SP_CASE_NOT_FOUND)
#        Message: Case not found for CASE statement
# Error: SQLSTATE: 23000 (ER_DUP_KEY)
#        Message: Can't write; duplicate key in table '%s'

delimiter //;
CREATE PROCEDURE h1 ()
BEGIN
   declare x1, x2, x3, x4, x5, x6 int default 0;
   SELECT '-1-', x1, x2, x3, x4, x5, x6;
   BEGIN
      declare condname condition for sqlstate '23000';
      declare continue handler for condname set x5 = 1;
      set x6 = 0;
      insert into res_t1 values ('a', 'b');
      set x6 = 1;
      SELECT '-2-', x1, x2, x3, x4, x5, x6;
   END;
   begin1_label: BEGIN
      BEGIN
         declare condname condition for sqlstate '20000';
         declare continue handler for condname set x1 = 1;
         set x2 = 0;
         case x2
            when 1 then set x2=10;
            when 2 then set x2=11;
         END case;
         set x2 = 1;
         SELECT '-3-', x1, x2, x3, x4, x5, x6;
         begin2_label: BEGIN
            BEGIN
               declare condname condition for sqlstate '23000';
               declare exit handler for condname set x3 = 1;
               set x4= 1;
               SELECT '-4a', x1, x2, x3, x4, x5, x6;
               insert into res_t1 values ('a', 'b');
               set x4= 2;
               SELECT '-4b', x1, x2, x3, x4, x5, x6;
            END;
            SELECT '-5-', x1, x2, x3, x4, x5, x6;
         END begin2_label;
         SELECT '-6-', x1, x2, x3, x4, x5, x6;
      END;
      SELECT '-7-', x1, x2, x3, x4, x5, x6;
   END begin1_label;
   SELECT 'END', x1, x2, x3, x4, x5, x6;
END//
delimiter ;//

CALL h1();

# and a 2nd test
--disable_warnings
DROP TABLE IF EXISTS tnull;
DROP PROCEDURE IF EXISTS sp1;
--enable_warnings

CREATE TABLE tnull(f1 int);

delimiter //;
CREATE PROCEDURE sp1()
BEGIN
    declare cond1 condition for sqlstate '42S02';
    declare continue handler for cond1 set @var2 = 1;
    BEGIN
      declare cond1 condition for sqlstate '23000';
      declare continue handler for cond1 set @var2 = 1;
    END;
          insert into tnull values(1);
END//
delimiter ;//

CALL sp1();

# cleanup 3.1.2.38
DROP PROCEDURE h1;
drop table res_t1;
DROP PROCEDURE sp1;
DROP TABLE tnull;


# ------------------------------------------------------------------------------
let $message= Testcase 3.1.2.43:;
--source include/show_msg.inc
let $message=
Ensure that the DECLARE ... HANDLER FOR statement can not declare any handler
for a condition declared outside of the scope of the handler.;
--source include/show_msg80.inc

--disable_warnings
DROP PROCEDURE IF EXISTS h1;
DROP PROCEDURE IF EXISTS h2;
drop table IF EXISTS res_t1;
--enable_warnings

create table res_t1(w char unique, x char);
insert into res_t1 values ('a', 'b');

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

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

CALL h2();
SELECT * FROM res_t1;

# cleanup 3.1.2.43
DROP PROCEDURE h2;
drop table res_t1;


# ------------------------------------------------------------------------------
let $message= Testcase 3.1.2.44:;
--source include/show_msg.inc
let $message=
Ensure that the DECLARE ... HANDLER FOR statement cannot declare a handler for
any invalid, or undeclared, condition.;
--source include/show_msg80.inc

--disable_warnings
DROP PROCEDURE IF EXISTS h1;
--enable_warnings

delimiter //;
# Error: SQLSTATE: 42000 (ER_SP_COND_MISMATCH)
#        Message: Undefined CONDITION: %s
--error ER_SP_COND_MISMATCH
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: SQLSTATE: 42000 (ER_PARSE_ERROR)
#        Message: %s near '%s' at line %d
--error ER_PARSE_ERROR
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: SQLSTATE: 42000 (ER_SP_BAD_SQLSTATE)
#        Message: Bad SQLSTATE: '%s'
--error ER_SP_BAD_SQLSTATE
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//
delimiter ;//

# cleanup 3.1.2.44
#DROP PROCEDURE h1;


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

# RefMan: For an EXIT handler, execution of the current BEGIN...END compound
#         statement is terminated.

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

--echo ==> 'UNDO' is still not supported.
delimiter //;
--error ER_PARSE_ERROR
create procedure p1undo ()
begin
   declare undo handler for sqlexception select '1';
   select * from tqq;
   SELECT 'end of 1';
end;//

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

create table res_t1(w char unique, x char);
insert into res_t1 values ('a', 'b');

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

CALL h1();


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

CALL sp1();

# cleanup 3.1.2.45+50