Definition and assignment operation of variables in mysql stored procedure

1. Definition of variables

Variable definition in mysql uses declare to define a local variable. The scope of use of this variable can only be used in the begin...end block. The variable must be defined at the beginning of the compound statement and in other Before the statement, you can also declare multiple variables at the same time. If necessary, you can use default to assign a default value.

The syntax for defining a variable is as follows:

declare var_name[,...] type[default value] See a variable definition example

declare last date; 2. Assignment of mysql stored procedure variables

The assignment of variables can be directly assigned with Query assignment is used to operate, direct assignment can be operated with set, which can be a constant or an expression. If

set var_name = [,var_name expr]... The method of assigning value to the last variable above is as follows

set last = date_sub( current_date (),interval 1 month); Let's look at assigning values ​​to variables through queries. The result returned by the query must be one row. The specific operations are as follows

select col into var_name[,...] table_expr Let's assign values ​​to v_pay through queries.

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 $$

The above is the definition of variables in the mysql stored procedure Assignment operation content, please pay attention to the PHP Chinese website (www.php.cn) for more related content!