Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what is the method of assigning values to variables in mysql stored procedures". In daily operation, I believe that many people have doubts about the method of assigning values to variables in mysql stored procedures. Xiaobian consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts of "what is the method of assigning variables in mysql stored procedures?" Next, please follow the editor to study!

Mysql stored procedure to assign values to variables: 1, the use of the DECLARE keyword, syntax "DECLARE variable name type DEFAULT default value;"; 2, the use of the SET keyword, syntax "SET variable name = assignment expression;"

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Variables can be defined and used in both stored procedures and functions in mysql.

Users can use the DECLARE keyword to define variables, after which they can assign values to variables. The scope of these variables is in the BEGIN...END program segment.

1. Assign values when defining variables

You can use the DECLARE keyword in MySQL to define variables and set default values, syntax:

DECLARE variable name type DEFAULT default

Note: the DEFAULT clause is used to set the default value of a variable. If the DEFAULT clause is omitted, the default value is NULL.

Example:

Define the variable my_sql, the data type is INT, and the default value is 10. The SQL statement is as follows:

DECLARE my_sql INT DEFAULT 10

2. Assign a value after defining a variable

1) use the SET keyword to assign values to variables

Syntax:

SET variable name = assignment expression

Note: a SET statement can assign values to multiple variables at the same time, and the assignment statements of each variable are separated by commas.

Example: assign the variable my_sql to 30. The SQL statement is as follows:

SET my_sql=30

2) use SELECT..INTO statement to assign values to variables

Syntax:

SELECT col_name [...] INTO var_name [,...] FROM table_name WEHRE condition

Where:

The col_name parameter represents the field name of the query

The var_name parameter is the name of the variable

The table_name parameter refers to the name of the table

The condition parameter refers to the query condition.

Note: when assigning a query result to a variable, the query statement can only return a single row.

Example:

Query the record with id 2 from the tb_student table and assign the id value of the record to the variable my_sql. The SQL statement is as follows:

SELECT id INTO my_sql FROM tb_student WEHRE id=2; at this point, the study on "what is the method of assigning values to variables in mysql stored procedures" is over. I hope I can solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.