MySQL declare statement usage

The MySQL declare statement is a commonly used statement. The following describes the usage of the MySQL declare statement. It is helpful for you to learn how to use the MySQL declare statement.

The MySQL declare statement is a command that declares variables in a composite statement.

(1) Example with two DECLARE statements
Instances of two DECLARE statements

 
 

  
  
CREATE PROCEDURE p8 ()   
  
  
BEGIN   
  
  
DECLARE 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 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 (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 with DEFAULT clause
Example with DEFAULT clause

 
 

  
  
CREATE PROCEDURE p10 ()   
  
  
BEGIN   
  
  
DECLARE a, b INT DEFAULT 5;   
  
  
INSERT INTO t VALUES (a);   
  
  
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; //   
 
 

Top 10 most noteworthy MySQL Variables

Use of MySQL System Variables

Difference between NULL and MySQL NULL strings

In-depth study of MySQL result strings

MySQL string truncation Function Method