Use of declare in MySQL5 statements

In the new SQL statement, the command for declaring the variable in the composite statement is DECLARE.

(1) Example of two DECLARE statements: create procedure p8 () BEGINDECLARE a INT; DECLARE B INT; SET a = 5; SET B = 5; insert into t VALUES (a); SELECT s1 * a FROM t WHERE s1> = B; END; // * I won't CALL this */

Variables defined in the process are not really defined. You just defined them in the BEGIN/END block ). 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 of Example with no DEFAULT clause and SET statement without DEFAULT clauses and SET statements create procedure p9 () BEGINDECLARE 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 (a); 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 of Example with DEFAULT clause containing the DEFAULT clause create procedure p10 () BEGINDECLARE 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 calling mysql> CALL p10 () // + -------- + | s1 * a | + -------- + | 25 | 25 | + -------- + 2 rows in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)

The results show that the process works normally (5) Scope create procedure p11 () BEGINDECLARE x1 CHAR (5) DEFAULT 'outer'; BEGINDECLARE 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.

Procedure 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.