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.