存储过程中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：创建过程，得到指定球员的所有罚款总额
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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、多变量赋值
例2：创建过程，得到一个给定球员的地址
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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 | +------------+------+--------+--------+ |
注意：
在使用SELECT …INTO语句时，变量名和数据表中的字段名不能相同，否则会出错。
3、拓展：select into outfile 用法、load data infile 用法
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
mysql> create table test_1(id int auto_increment primary key not null,name varchar(20) not null); Query OK, 0 rows affected (0.21 sec) mysql> insert into test_1(name) values('zhangsan'),('lisi'); Query OK, 2 rows affected (0.03 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from test_1; +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | | 2 | lisi | +----+----------+ 2 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, 2 rows affected (0.05 sec) |
[root@mysqlserver tmp]# cat result.txt
1,”zhangsan”
2,”lisi”
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
mysql> create table test_2 like test_1; #建test_2复制test_1表结构 Query OK, 0 rows affected (0.18 sec) mysql> load data infile '/tmp/result.txt' into table test_2; ERROR 1265 (01000): 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, 2 rows affected (0.01 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select * from test_2; +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | | 2 | lisi | +----+----------+ 2 rows in set (0.00 sec) |
注意：如果导出时用到了FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘n’语句，那么LODA时也要加上同样的分隔限制语句。还要注意编码问题。
@author：http://www.cnblogs.com/geaozhang/