Using SELECT in MySQL stored procedures... The INTO statement assigns values to variables:

Used to save the column values of a row returned by the query to a local variable.

Requirement:

There can be only 1 row in the result set of the query.

SELECT col_name[,...] INTO var_name[,...] table_expr

Use SELECT... The INTO statement queries in the database and assigns the result to the variable.

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 variable list, and assigns the value obtained from the query to the variable in the corresponding position;

③ table expr: the rest of the SELECT statement, including the optional FROM clause and WHERE clause.

 

1. Single variable assignment

Example 1: create a process to get the total amount of all fines for a designated player

mysql> delimiter $$
mysql> create procedure total_penalties_player(
    -> 　　in p_playerno int,
    -> 　　out total_penalties dec(8,2))
    -> begin
    -> 　　select sum(amount)
    -> 　　into total_penalties
    -> 　　from PENALTIES
    -> 　　where playerno=p_playerno;
    -> end $$
mysql> delimiter ;
mysql> call total_penalties_player(27,@total);
mysql> select @total;
+--------+
| @total |
+--------+
| 175.00 |
+--------+

 

2. Multivariable assignment

Example 2: create a process to get the address of a given player

mysql> DELIMITER $$
mysql> CREATE  PROCEDURE get_address(
    ->      IN p_playerno SMALLINT,
    ->      OUT p_street VARCHAR(30),
    ->      OUT p_houseno VARCHAR(4),
    ->      OUT p_town VARCHAR(30),
    ->      OUT p_postcode VARCHAR(6))  
    ->  BEGIN    
    ->      SELECT street, houseno, town, postcode
    ->      INTO p_street, p_houseno, p_town, p_postcode
    ->      FROM PLAYERS
    ->      WHERE playerno = p_playerno;           
    ->  END$$
mysql> DELIMITER ;
mysql> call get_address(27,@s,@h,@t,@p);
mysql> select @s,@h,@t,@p;
+------------+------+--------+--------+
| @s         | @h   | @t     | @p     |
+------------+------+--------+--------+
| Long Drive | 804  | Eltham | 8457DK |
+------------+------+--------+--------+

Be careful:

Using SELECT... In the INTO statement, the variable name and the field name in the data table cannot be the same, otherwise an error will occur.