Definition and value assignment of variables in mysql stored procedures

I. Definition of Variables
Mysql uses declare to define a local variable. The scope of use of this variable can only be in begin... used in the end block, the variable must be defined at the beginning of the composite statement, and can declare multiple variables at the same time before other statements. If necessary, you can use default to assign the default value.
The syntax for defining a variable is as follows:
Declare var_name [,...] type [default value] to view a variable definition instance
Declare last date; 2. mysql Stored Procedure variable assignment
You can directly assign values to variables or assign values to queries. You can use set to directly assign values. It can be a constant or expression.
Copy codeThe Code is as follows:
Set var_name = [, var_name expr]... the method for assigning values to the last variable above is as follows:
Set last = date_sub (current_date (), interval 1 month); The following describes how to assign values to the variables through the query. The results returned by the query must be one row. The specific operations are as follows:
Select col into var_name [,...] table_expr we will assign values to v_pay through the query.
Create function get _ cost (p_custid int, p_eff datetime)
Return decimal (5, 2)
Deterministic
Reads SQL data
Begin
Declare v_pay decimail (5, 2 );
Select ifnull (sum (pay. amount), 0) into vpay from payment where pay. payd <= p_eff and pay. custid = pid
Reutrn v_rent + v_over-v_pay;
End $

Now, this simple tutorial on variable definition assignment in the stored procedure is here. Next we will talk about the definition and processing of conditions in the myql stored procedure.

The following is a supplement from other netizens:

You can use a variable in the MySQL stored procedure to save the values in the processing procedure.

Use the DECLARE statement to define variables. The syntax format is as follows:

DECLARE var_name [,...] type [DEFAULT value]

Var_name is the variable name and type is any data type supported by MySQL. The optional [DEFAULT value] specifies the DEFAULT value for the variable. Multiple variables of the same type can be defined at a time. Variable names are separated by commas.

Pay attention to the following points when defining and using variables:

◆ The DECLARE statement must be used in DEGIN... The END statement block must appear in the DEGIN... The beginning of the END statement block, that is, before other statements.

◆ The scope of the variables defined by DECLARE is limited to the DEGIN... Other DEGIN within the END block and nested in the block... END Block.

◆ Variable names in stored procedures are case-insensitive.

The Defined variables are assigned values using the SET statement. The syntax format is as follows:

SET var_name = expr [, var_name = expr]...

Here, var_name is the variable name, expr is the expression of the value or return value, which can be any expression supported by MySQL. You can assign values to multiple variables at a time. Multiple "variable name = value" pairs are separated by commas.

For example:
Copy codeThe Code is as follows:
Begin
Declare no varchar (20 );
Declare title varchar (30 );
Set no = '000000', title = 'variables and assignments defined in Stored Procedures ';
End

Tip: All keywords in the stored procedure are case-insensitive. For example, BEGIN can be written.