MySQL stored procedures using SELECT ... Into statement assigns a value to a variable

Use SELECT ... Into statement assigns a value to a variable

In the MySQL stored procedure, you can use SELECT ... The INTO statement assigns a value to the variable, which is queried in the database and assigns the resulting result to the variable. SELECT ... The syntax format for the INTO statement is as follows:

1. SELECT col_name[,...] Into var_name[,...] table_expr

Col_name: The name of the column field to query from the database;

Var_name: Variable name, the column field name corresponds to the position in the column list and the variable list, assigns the value of the query to the corresponding position variable;

The remainder of the Table_expr:select statement, including an optional from clause and a WHERE clause.

It is important to note that the use of select ... Into statement, the variable name cannot be the same as the field name in the datasheet, or an error occurs. Example statements:

1. CREATE PROCEDURE Getmsg
2. ()
3. Begin
4. DECLARE v_title varchar (30);
5. DECLARE v_content varchar (100);
6. Select Title,content into V_title,v_content from news where artid=333;
7. End

Returns the value of a variable to the caller

Variables defined in a stored procedure, after a series of processing, the resulting value may need to be returned to the stored procedure caller. So how do you return it? It is convenient to use the SELECT statement to return the variable as a result set, so, on the basis of the preceding paragraph of code, add one sentence:

1. CREATE PROCEDURE Getmsg
2. ()
3. Begin
4. DECLARE v_title varchar (30);
5. DECLARE v_content varchar (100);
6. Select Title,content into V_title,v_content from news where artid=333;
7. Select v_title,v_content;
8. End

MySQL stored procedures using SELECT ... Into statement assigns a value to a variable