storedproc_02.inc

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

#### suite/funcs_1/storedproc/storedproc_02.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.2 Syntax checks for the stored procedure-specific programming statements
#       BEGIN/END, DECLARE, SET, SELECT/INTO, OPEN, FETCH, CLOSE:
#
#-  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 every BEGIN statement is coupled with a terminating
#      END statement.
##  8. Ensure that the scope of each BEGIN/END compound statement within a
#      stored procedure definition is properly applied.
#-  9. Ensure that the labels enclosing each BEGIN/END compound statement
#      must match.
#- 10. Ensure that it is possible to put a beginning label at the start of
#      a BEGIN/END compound statement without also requiring an ending label
#      at the end of the same statement.
#- 11. Ensure that it is not possible to put an ending label at the end of
#      a BEGIN/END compound statement without also requiring a matching
#      beginning label at the start of the same statement.
#- 12. Ensure that every beginning label must end with a colon (:).
#- 13. Ensure that every beginning label with the same scope must be unique.
#- 14. Ensure that the variables, cursors, conditions, and handlers declared
#      for a stored procedure (with the DECLARE statement) may only be
#      properly defined.
#- 15. Ensure that the variables, cursors, conditions, and handlers declared for
#      a stored procedure (with the DECLARE statement) may only be defined in
#      the correct order.
#- 16. Ensure that every possible type of variable -- utilizing every data type
#      definition supported by the MySQL server in combination with both no
#      DEFAULT subclause and with DEFAULT subclauses that set the variable’s
#      default value to a range of appropriate values -- may be declared for
#      a stored procedure.
#- 17. Ensure that the DECLARE statement can declare multiple variables both
#      separately and all at once from a variable list.
#- 18. Ensure that invalid variable declarations are rejected, with an
#      appropriate error message.
#- 19. Ensure that every possible type of cursor may be declared for a
#      stored procedure.
#- 20. Ensure that invalid cursor declarations are rejected, with an appropriate
#      error message.
#- 21. Ensure that every possible type of condition may be declared for
#      a stored procedure.
# -22. Ensure that invalid condition declarations are rejected, with an
#      appropriate error message.
#- 23. Ensure that every possible type of handler may be declared for a
#      stored procedure.
#- 24. Ensure that invalid handler declarations are rejected, with an
#      appropriate error message.
#- 25. Ensure that the scope of every variable, cursor, condition, and handler
#      declared for a stored procedure (with the DECLARE statement) is
#      properly applied.
## 26. Ensure that the initial value of every variable declared for a stored
#      procedure is either NULL or its DEFAULT value, as appropriate.
#- 27. Ensure that the SET statement can assign a value to every local variable
#      declared within a stored procedure’s definition, as well as to every
#      appropriate global server variable.
#- 28. Ensure that the SET statement can assign values to variables either
#      separately or to multiple variables in a list.
#- 29. Ensure that the SET statement may assign only those values to a variable
#      that are appropriate for that variable’s data type definition.
## 30. Ensure that, when a stored procedure is called/executed, every variable
#      always uses the correct value: either the value with which it is
#      initialized or the value to which it is subsequently SET or otherwise
#      assigned, as appropriate.
## 31. Ensure that the SELECT ... INTO statement properly assigns values to the
#      variables in its variable list.
## 32. Ensure that a SELECT ... INTO statement that retrieves multiple rows is
#      rejected, with an appropriate error message.
## 33. Ensure that a SELECT ... INTO statement that retrieves too many columns
#      for the number of variables in its variable list is rejected, with an
#      appropriate error message.
## 34. 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.
#- 35. Ensure that a SELECT ... INTO statement that retrieves column values
#      with inappropriate data types for the matching variables in its variable
#      list is rejected, with an appropriate error message.
#- 36. Ensure that the DECLARE ... CONDITION FOR statement can declare a
#      properly-named condition for every possible SQLSTATE and MySQL-specific
#      error code.
#- 37. Ensure that no two conditions declared with the same scope may have the
#      same condition name.
## 38. Ensure that the scope of every condition declared is properly applied.
#- 39. Ensure that every SQLSTATE value declared with a DECLARE ... CONDITION
#      FOR statement is a character string that is 5 characters long.
#- 40. Ensure that the DECLARE ... CONDITION FOR statement cannot declare a
#      condition for an invalid SQLSTATE.
#- 41. Ensure that the DECLARE ... CONDITION FOR statement cannot declare a
#      condition for the “successful completion SQLSTATE: “00000“.
#- 42. Ensure that the DECLARE ... HANDLER FOR statement can declare a CONTINUE,
#      EXIT, and UNDO handler for every condition declared (with a DECLARE ...
#      CONDITION FOR statement), within the scope of the handler, for a stored
#      procedure, as well as for every possible SQLSTATE and MySQL-specific
#      error code, as well as for the predefined conditions SQLWARNING,
#      NOT FOUND, and SQLEXCEPTION.
## 43. Ensure that the DECLARE ... HANDLER FOR statement can not declare any
#      handler for a condition declared outside of the scope of the handler.
## 44. Ensure that the DECLARE ... HANDLER FOR statement cannot declare a
#      handler for any invalid, or undeclared, condition.
## 45. Ensure that the scope of every handler declared is properly applied.
#- 46. Ensure that, within the same scope, no two handlers may be declared for
#      the same condition.
#- 47. Ensure that every SQLSTATE value declared with a DECLARE ... HANDLER FOR
#      statement is a character string that is 5 characters long.
#- 48. Ensure that the DECLARE ... HANDLER FOR statement cannot declare a
#      condition for an invalid SQLSTATE.
#- 49. Ensure that the DECLARE ... HANDLER FOR statement cannot declare a
#      condition for the “successful completion SQLSTATE: “00000“.
## 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).
## 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.
## 52. Ensure that an EXIT handler does not cause the execution of the stored
#      procedure to terminate outside of its scope.
#- 53. Ensure that a handler condition of SQLWARNING takes the same action as
#      a handler condition defined with an SQLSTATE that begins with “01“.
## 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.
#- 55. Ensure that a handler condition of NOT FOUND takes the same action as a
#      handler condition defined with an SQLSTATE that begins with “02“.
## 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.
#- 57. Ensure that a handler condition of SQLEXCEPTION takes the same action
#      as a handler condition defined with an SQLSTATE that begins with
#      anything other that “01“ or “02“.
## 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.
#- 59. Ensure that no two cursors in a stored procedure can have the same name.
#- 60. Ensure that a cursor declaration may not include a SELECT ... INTO
#      statement.
#- 61. Ensure that a cursor declaration that includes an ORDER BY clause may
#      not be an updatable cursor.
#- 62. Ensure that OPEN <cursor name> fails unless a cursor with the same name
#      has already been declared.
#- 63. Ensure that OPEN <cursor name> fails if the same cursor is currently
#      already open.
#- 64. Ensure that FETCH <cursor name> fails unless a cursor with the same name
#      is already open.
## 65. Ensure that FETCH <cursor name> returns the first row of the cursor’s
#      result set the first time FETCH is executed, that it returns each
#      subsequent row of the cursor’s result set each of the subsequent
#      times FETCH is executed, and that it returns a NOT FOUND warning if it
#      is executed after the last row of the cursor’s result set has already
#      been fetched.
#- 66. Ensure that FETCH <cursor name> fails with an appropriate error message
#      if it is executed before the cursor has been opened.
#- 67. Ensure that FETCH <cursor name> fails with an appropriate error message
#      if it is executed after the cursor has been closed.
## 68. Ensure that FETCH <cursor name> fails with an appropriate error message
#      if the number of columns to be fetched does not match the number of
#      variables specified by the FETCH statement.
#- 69. Ensure that FETCH <cursor name> fails with an appropriate error message
#      if the data type of the column values being fetched are not appropriate
#      for the matching FETCH variables to which the data is being assigned.
#- 70. Ensure that CLOSE <cursor name> fails unless a cursor with the same name
#      is already open.
#- 71. Ensure that all cursors are closed when a transaction terminates with
#      a COMMIT statement.
#- 72. Ensure that all cursors are closed when a transaction terminates with
#      a ROLLBACK statement.
#- 73. Ensure that the result set of a cursor that has been closed is not
#      longer available to the FETCH statement.
#- 74. Ensure that every cursor declared within a compound statement is closed
#      when that compound statement ends.
## 75. Ensure that, for nested compound statements, a cursor that was declared
#      and opened during an outer level of the statement is not closed when an
#      inner level of a compound statement ends.
## 76. Ensure that all cursors operate asensitively, so that there is no
#      concurrency conflict between cursors operating on the same, or similar,
#      sets of results during execution of one or more stored procedures.
# 77.  Ensure that multiple cursors, nested within multiple compound statements
#      within a stored procedure, always act correctly and return the
#      expected result.
#
# ==============================================================================
let $message= Section 3.1.2 - Syntax checks for the stored procedure-specific
programming statements BEGIN/END, DECLARE, SET, SELECT/INTO, OPEN, FETCH, CLOSE:;
--source include/show_msg80.inc


# ------------------------------------------------------------------------------
let $message= Testcase 3.1.2.8:;
--source include/show_msg.inc
let $message=
Ensure that the scope of each BEGIN/END compound statement within a stored
procedure definition is properly applied;
--source include/show_msg80.inc

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

delimiter //;
CREATE PROCEDURE sp1( )
begin_label: BEGIN
   declare x char DEFAULT 'x';
   declare y char DEFAULT 'y';
   set x = '1';
   set y = '2';
   label1: BEGIN
      declare x char DEFAULT 'X';
      declare y char DEFAULT 'Y';
      SELECT f1, f2 into x, y from t2 limit 1;
      SELECT '1.1', x, y;
      label2: BEGIN
         declare x char default 'a';
         declare y char default 'b';
         label3: BEGIN
            declare x char default 'c';
            declare y char default 'd';
            label4: BEGIN
               declare x char default 'e';
               declare y char default 'f';
               label5: BEGIN
                  declare x char default 'g';
                  declare y char default 'h';
                  SELECT 5, x, y;
               END label5;
               SELECT 4, x, y;
            END label4;
            SELECT 3, x, y;
         END label3;
         SELECT 2, x, y;
      END label2;
   END label1;
   set @v1 = x;
   set @v2 = y;
   SELECT '1.2', @v1, @v2;
END begin_label//
delimiter ;//

CALL sp1();

#cleanup
DROP PROCEDURE IF EXISTS sp1;


# ------------------------------------------------------------------------------
let $message= Testcase 3.1.2.26:;
--source include/show_msg.inc
let $message=
Ensure that the initial value of every variable declared for a stored procedure
is either NULL or its DEFAULT value, as appropriate.;
--source include/show_msg80.inc

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

set @v1=0;
set @v2=0;

delimiter //;
CREATE PROCEDURE sp1( )
BEGIN
   declare x1 char default 'x';
   declare y1 char;
   declare x2 tinytext default 'tinytext';
   declare y2 tinytext;
   declare x3 datetime default '2005-10-03 12:13:14';
   declare y3 datetime;
   declare x4 float default 1.2;
   declare y4 float;
   declare x5 blob default 'b';
   declare y5 blob;
   declare x6 smallint default 127;
   declare y6 smallint;
   SELECT x1, x2, x3, x4, x5, x6, y1, y2, y3, y4, y5, y6;
END//
delimiter ;//

CALL sp1();

# cleanup
DROP PROCEDURE sp1;


# ------------------------------------------------------------------------------
let $message= Testcase 3.1.2.30:;
--source include/show_msg.inc
let $message=
Ensure that, when a stored procedure is called/executed, every variable always
uses the correct value: either the value with which it is initialized or the
value to which it is subsequently SET or otherwise assigned, as appropriate.;
--source include/show_msg80.inc

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

delimiter //;
CREATE PROCEDURE sp1( IN invar INT, OUT outvar INT )
BEGIN
    declare x integer;
    declare y integer default 1;
    set @x = x;
    set @y = y;
    set @z = 234;
    SELECT f1, f2 into @x, @y from t2 where f1='a`' and f2='a`' limit 1;
    SELECT @x, @y, @z, invar;
    BEGIN
      set @x = 2;
      SELECT @x, @y, @z;
      SET outvar = @x * invar + @z * @f;
      SET invar = outvar;
      BEGIN
        set @y = null, @z = 'abcd';
        SELECT @x, @y, @z;
      END;
    END;
END//
delimiter ;//

SET @invar  = 100;
SET @outvar = @invar;
SET @f      = 10;

SELECT @x, @y, @z, @invar, @outvar;

CALL sp1( @invar, @outvar );

SELECT @x, @y, @z, @invar, @outvar;

# cleanup
DROP PROCEDURE sp1;


# ------------------------------------------------------------------------------
let $message= Testcase 3.1.2.31:;
--source include/show_msg.inc
let $message=
Ensure that the SELECT ... INTO statement properly assigns values to the
variables in its variable list.;
--source include/show_msg80.inc
# also tested in a lot of other testcases

--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 into @x, @y from t2 where f4=-5000 and f3='1000-01-01' limit 1;
   SELECT @x, @y;
END//
delimiter ;//

CALL sp1();

# cleanup 3.1.2.31
DROP PROCEDURE sp1;


# ------------------------------------------------------------------------------
let $message= Testcase 3.1.2.32:;
--source include/show_msg.inc
let $message=
Ensure that a SELECT ... INTO statement that retrieves multiple rows 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;
   set @x=x;
   set @y=y;
   SELECT f4, f3 into @x, @y from t2;
END//
delimiter ;//

# Error: SQLSTATE: 42000 (ER_TOO_MANY_ROWS)
#        Message: Result consisted of more than one row
--error ER_TOO_MANY_ROWS
CALL sp1();

# cleanup 3.1.2.32
DROP PROCEDURE sp1;


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