存储过程中使用select……into

在MySQL存储过程中使用SELECT …INTO语句为变量赋值：

　　用来将查询返回的一行的各个列值保存到局部变量中。

要求：

　　查询的结果集中只能有1行。

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

使用SELECT …INTO语句在数据库中进行查询，并将得到的结果赋值给变量。

　　①col_name：要从数据库中查询的列字段名；

　　②var_name：变量名，列字段名按照在列清单和变量清单中的位置对应，将查询得到的值赋给对应位置的变量；

　　③table_expr：SELECT语句中的其余部分，包括可选的FROM子句和WHERE子句。

1、单一变量赋值

例1：创建过程，得到指定球员的所有罚款总额

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

2、多变量赋值

例2：创建过程，得到一个给定球员的地址

mysql> DELIMITER $$
mysql> CREATE  PROCEDURE get_address(
    ->      IN p_playerno SMALLINT,
    ->      OUT p_street VARCHAR(),
    ->      OUT p_houseno VARCHAR(),
    ->      OUT p_town VARCHAR(),
    ->      OUT p_postcode VARCHAR())
    ->  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(,@s,@h,@t,@p);
mysql> select @s,@h,@t,@p;
+------------+------+--------+--------+
| @s         | @h   | @t     | @p     |
+------------+------+--------+--------+
| Long Drive |   | Eltham | 8457DK |
+------------+------+--------+--------+

注意：

　　在使用SELECT …INTO语句时，变量名和数据表中的字段名不能相同，否则会出错。

3、拓展：select into outfile 用法、load data infile 用法

mysql> create table test_1(id int auto_increment primary key not null,name varchar() not null);
Query OK,  rows affected (0.21 sec)mysql> insert into test_1(name) values('zhangsan'),('lisi');
Query OK,  rows affected (0.03 sec)
Records:   Duplicates:   Warnings: mysql> select * from test_1;
+----+----------+
| id | name     |
+----+----------+
|   | zhangsan |
|   | lisi     |
+----+----------+
 rows in set (0.01 sec)mysql> select * into outfile '/tmp/result.txt'
    -> fields terminated by ',' optionally enclosed by '"'
    -> lines terminated by '\n'
    -> from test_1;
Query OK,  rows affected (0.05 sec)

[root@mysqlserver tmp]# cat result.txt
1,”zhangsan”
2,”lisi”

mysql> create table test_2 like test_1;　　#建test_2复制test_1表结构
Query OK,  rows affected (0.18 sec)mysql> load data infile '/tmp/result.txt' into table test_2;
ERROR  (): Data truncated for column 'id' at row 1
mysql> load data infile '/tmp/result.txt' into table test_2
    -> fields terminated by ',' optionally enclosed by '"'
    -> lines terminated by '\n';
Query OK,  rows affected (0.01 sec)
Records:   Deleted:   Skipped:   Warnings: mysql> select * from test_2;
+----+----------+
| id | name     |
+----+----------+
|   | zhangsan |
|   | lisi     |
+----+----------+
 rows in set (0.00 sec)

注意：如果导出时用到了FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’语句，那么LODA时也要加上同样的分隔限制语句。还要注意编码问题。