Usage of the new MySQL5 statement declare _ MySQL

In MySQL5, the new statement declare is used. in the composite statement, the variable declaration command is DECLARE.
(1) Example with two DECLARE statements

Two DECLARE statements

Create procedure p8 ()

BEGIN

DECLARE a INT;

DECLARE B INT;

SET a = 5;

SET B = 5;

Insert into t VALUES ();

SELECT s1 * a FROM t WHERE s1> = B;

END; // * I won't CALL this */The variables defined in the process are not really defined. you just defined them in the BEGIN/END block: that is, the form parameter ). Note that these variables are different from session variables. you cannot use the modifier @. you must clearly declare the variables and their types in the BEGIN/END block. Once a variable is declared, you can use it wherever you can use session variables, text, and column names.
(2) Example with no DEFAULT clause and SET statement

Example of no default clause or set statement

Create procedure p9 ()

BEGIN

DECLARE a INT/* there is no DEFAULT clause */;

DECLARE B INT/* there is no DEFAULT clause */;

SET a = 5;/* there is a SET statement */

SET B = 5;/* there is a SET statement */

Insert into t VALUES ();

SELECT s1 * a FROM t WHERE s1> = B;

END; // * I won't CALL this */

There are many methods for initializing variables. If no default clause exists, the initial value of the variable is NULL. You can use the SET statement to assign values to variables at any time.

(3) Example with DEFAULT clause

Example with DEFAULT clause

Create procedure p10 ()

BEGIN

DECLARE a, B INT DEFAULT 5;

Insert into t VALUES ();

SELECT s1 * a FROM t WHERE s1> = B;

END ;//

We have made some changes here, but the results are the same. Here, the DEFAULT clause is used to SET the initial value, which does not need to be separated from the implementation of the DECLARE and SET statements.
(4) Example of CALL

Call example

Mysql> CALL p10 ()//

+ -------- +

| S1 * a |

+ -------- +

| 25 |

| 25 |

+ -------- +

2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

The result shows that the process works properly.

(5) Scope

Scope

Create procedure p11 ()

BEGIN

DECLARE x1 CHAR (5) DEFAULT 'outer ';

BEGIN

DECLARE x1 CHAR (5) DEFAULT 'inner ';

SELECT x1;

END;

SELECT x1;

END ;//

Now let's discuss the scope issue. Nested BEGIN/END blocks exist in this example. Of course this is legal. It also contains two variables whose names are both x1, which is legal. Internal variables have higher priority in their scopes. When the END statement is executed, the internal variable disappears and is no longer visible outside its scope. Therefore, the declared variable cannot be found out of the stored procedure, however, you can use the OUT parameter or assign the value to the session variable to save the value.
　

The process of calling the scope example:

Mysql> CALL p11 ()//

+ ------- +

| X1 |

+ ------- +

| Inner |

+ ------- +

+ ------- +

| X1 |

+ ------- +

| Outer |

+ ------- +

When we see the result, the first SELECT statement retrieves the innermost variable, and the second retrieves the second variable.