纵有疾风起
人生不言弃

存储过程中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(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:创建过程,得到一个给定球员的地址

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 用法

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: 0mysql> 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”

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: 0mysql> 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时也要加上同样的分隔限制语句。还要注意编码问题。

文章转载于:https://www.cnblogs.com/geaozhang/p/6814310.html

原著是一个有趣的人,若有侵权,请通知删除

未经允许不得转载:起风网 » 存储过程中SELECT INTO的使用

分享到: 生成海报
avatar

评论 抢沙发

评论前必须登录!

立即登录   注册

切换注册

登录

忘记密码 ?

切换登录

注册

我们将发送一封验证邮件至你的邮箱, 请正确填写以完成账号注册和激活