MySQL 变量和条件

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用版 2核4GB 50GB
简介:
2016-02-20 17:21 by pursuer.chen, 2990 阅读, 3 评论, 收藏, 编辑

概述  

 变量在存储过程中会经常被使用,变量的使用方法是一个重要的知识点,特别是在定义条件这块比较重要。

 mysql版本:5.6

变量定义和赋值  

复制代码
#创建数据库
DROP DATABASE IF EXISTS Dpro;
CREATE  DATABASE Dpro
CHARACTER SET utf8
;

USE Dpro;

#创建部门表
DROP TABLE IF EXISTS Employee;
CREATE TABLE Employee
(id INT NOT NULL PRIMARY KEY COMMENT '主键',
 name VARCHAR(20) NOT NULL COMMENT '人名',
 depid INT NOT NULL COMMENT '部门id'
);

INSERT INTO Employee(id,name,depid) VALUES(1,'',100),(2,'',101),(3,'',101),(4,'',102),(5,'',103);
复制代码

declare定义变量

在存储过程和函数中通过declare定义变量在BEGIN...END中,且在语句之前。并且可以通过重复定义多个变量

注意:declare定义的变量名不能带‘@’符号,mysql在这点做的确实不够直观,往往变量名会被错成参数或者字段名。

DECLARE var_name[,...] type [DEFAULT value]

例如:

复制代码
DROP PROCEDURE IF EXISTS Pro_Employee;
DELIMITER $$
CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT )
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
DECLARE pname VARCHAR(20) DEFAULT '陈';
SELECT COUNT(id) INTO pcount FROM Employee WHERE depid=pdepid;

END$$
DELIMITER ;
复制代码

SET变量赋值 

SET除了可以给已经定义好的变量赋值外,还可以指定赋值并定义新变量,且SET定义的变量名可以带‘@’符号,SET语句的位置也是在BEGIN ....END之间的语句之前。

1.变量赋值

SET var_name = expr [, var_name = expr] ...
复制代码
DROP PROCEDURE IF EXISTS Pro_Employee;
DELIMITER $$
CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT )
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
DECLARE pname VARCHAR(20) DEFAULT '陈';
SET pname='王';
SELECT COUNT(id) INTO pcount FROM Employee WHERE depid=pdepid AND name=pname;

END$$
DELIMITER ;

CALL Pro_Employee(101,@pcount);

  SELECT @pcount;

复制代码

 2.通过赋值定义变量

复制代码
DROP PROCEDURE IF EXISTS Pro_Employee;
DELIMITER $$
CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT )
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
DECLARE pname VARCHAR(20) DEFAULT '';
SET pname='';
SET @ID=1;
SELECT COUNT(id) INTO pcount FROM Employee WHERE depid=pdepid AND name=pname;
SELECT @ID;

END$$
DELIMITER ;

CALL Pro_Employee(101,@pcount);
复制代码

SELECT ... INTO语句赋值

 通过select into语句可以将值赋予变量,也可以之间将该值赋值存储过程的out参数,上面的存储过程select into就是之间将值赋予out参数。

复制代码
DROP PROCEDURE IF EXISTS Pro_Employee;
DELIMITER $$
CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT )
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
DECLARE pname VARCHAR(20) DEFAULT '';
DECLARE Pid INT;
SELECT COUNT(id) INTO Pid FROM Employee WHERE depid=pdepid AND name=pname;
SELECT Pid;

END$$
DELIMITER ;

CALL Pro_Employee(101,@pcount);
复制代码

这个存储过程就是select into将值赋予变量;

 

表中并没有depid=101 and name='陈'的记录。 

条件  

条件的作用一般用在对指定条件的处理,比如我们遇到主键重复报错后该怎样处理。 

定义条件

 定义条件就是事先定义某种错误状态或者sql状态的名称,然后就可以引用该条件名称开做条件处理,定义条件一般用的比较少,一般会直接放在条件处理里面。

DECLARE condition_name CONDITION FOR condition_value
 
condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | mysql_error_code

1.没有定义条件:

复制代码
DROP PROCEDURE IF EXISTS Pro_Employee_insert;
DELIMITER $$
CREATE PROCEDURE Pro_Employee_insert()
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN
SET @ID=1;
INSERT INTO Employee(id,name,depid) VALUES(1,'',100);
SET @ID=2;
INSERT INTO Employee(id,name,depid) VALUES(6,'',100);
SET @ID=3;

END$$
DELIMITER ;

#执行存储过程
CALL Pro_Employee_insert();

#查询变量值
SELECT @ID,@X;
复制代码

 报主键重复的错误,其中1062是主键重复的错误代码,23000是sql错误状态

2.定义处理条件

复制代码
DROP PROCEDURE IF EXISTS Pro_Employee_insert;
DELIMITER $$
CREATE PROCEDURE Pro_Employee_insert()
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN
#定义条件名称,
DECLARE reprimary CONDITION FOR 1062;
#引用前面定义的条件名称并做赋值处理
DECLARE EXIT HANDLER FOR reprimary SET @x=1;
SET @ID=1;
INSERT INTO Employee(id,name,depid) VALUES(1,'',100);
SET @ID=2;
INSERT INTO Employee(id,name,depid) VALUES(6,'',100);
SET @ID=3;

END$$
DELIMITER ;

CALL Pro_Employee_insert();

SELECT @ID,@X;
复制代码

在执行存储过程的步骤中并没有报错,但是由于我定义的是exit,所以在遇到报错sql就终止往下执行了。

接下来看看continue的不同

复制代码
DROP PROCEDURE IF EXISTS Pro_Employee_insert;
DELIMITER $$
CREATE PROCEDURE Pro_Employee_insert()
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN
#定义条件名称,
DECLARE reprimary CONDITION FOR SQLSTATE '23000';
#引用前面定义的条件名称并做赋值处理
DECLARE CONTINUE HANDLER FOR reprimary SET @x=1;
SET @ID=1;
INSERT INTO Employee(id,name,depid) VALUES(1,'',100);
SET @ID=2;
INSERT INTO Employee(id,name,depid) VALUES(6,'',100);
SET @ID=3;

END$$
DELIMITER ;

CALL Pro_Employee_insert();

SELECT @ID,@X;
复制代码

其中红色标示的是和上面不同的地方,这里定义条件使用的是SQL状态,也是主键重复的状态;并且这里使用的是CONTINUE就是遇到错误继续往下执行。

条件处理

条件处理就是之间定义语句的错误的处理,省去了前面定义条件名称的步骤。

复制代码
DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement
 
handler_type:
    CONTINUE| EXIT| UNDO
 
condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
  | mysql_error_code
复制代码

handler_type:遇到错误是继续往下执行还是终止,目前UNDO还没用到。

CONTINUE:继续往下执行

EXIT:终止执行

condition_values:错误状态

SQLSTATE [VALUE] sqlstate_value:就是前面讲到的SQL错误状态,例如主键重复状态SQLSTATE '23000'

condition_name:上面讲到的定义条件名称;

SQLWARNING:是对所有以01开头的SQLSTATE代码的速记,例如:DECLARE CONTINUE HANDLER FOR SQLWARNING。

NOT FOUND:是对所有以02开头的SQLSTATE代码的速记。

SQLEXCEPTION:是对所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的速记。

mysql_error_code:是错误代码,例如主键重复的错误代码是1062,DECLARE CONTINUE HANDLER FOR 1062

 

语句:

复制代码
DROP PROCEDURE IF EXISTS Pro_Employee_insert;
DELIMITER $$
CREATE PROCEDURE Pro_Employee_insert()
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN
#引用前面定义的条件名称并做赋值处理 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @x=2;
#开始事务必须在DECLARE之后
START TRANSACTION ; SET @ID=1; INSERT INTO Employee(id,name,depid) VALUES(7,'陈',100); SET @ID=2; INSERT INTO Employee(id,name,depid) VALUES(6,'陈',100); SET @ID=3; IF @x=2 THEN ROLLBACK; ELSE COMMIT; END IF; END$$ DELIMITER ;
复制代码
#执行存储过程
CALL Pro_Employee_insert();
#查询
SELECT @ID,@X;

通过SELECT @ID,@X可以知道存储过程已经执行到了最后,但是因为存储过程后面有做回滚操作整个语句进行了回滚,所以ID=7的符合条件的记录也被回滚了。

总结  

变量的使用不仅仅只有这些,在光标中条件也是一个很好的功能,刚才测试的是continue如果使用EXIT的话语句执行完“SET @ID=2;”就不往下执行了,后面的IF也不被执行整个语句不会被回滚,但是使用CONTINE当出现错误后还是会往下执行如果后面的语句还有很多的话整个回滚的过程将会很长,在这里可以利用循环,当出现错误立刻退出循环执行后面的if回滚操作,在下一篇讲循环语句会写到,欢迎关注。

 

 




本文转自pursuer.chen(陈敏华)博客园博客,原文链接:http://www.cnblogs.com/chenmh/p/5203429.html,如需转载请自行联系原作者


相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
1月前
|
存储 SQL 关系型数据库
15. Mysql 变量的使用
15. Mysql 变量的使用
22 1
|
2月前
|
存储 关系型数据库 MySQL
【MySQL进阶之路 | 基础篇】变量
【MySQL进阶之路 | 基础篇】变量
|
1月前
|
存储 SQL 关系型数据库
MySQL数据库——存储过程-变量(系统变量、用户定义变量、局部变量)
MySQL数据库——存储过程-变量(系统变量、用户定义变量、局部变量)
21 0
|
1月前
|
存储 关系型数据库 MySQL
Mysql存储过程查询结果赋值到变量
Mysql存储过程查询结果赋值到变量
36 0
|
2月前
|
关系型数据库 MySQL
mysql 动态变量,可以在线修改
7.1.9.2 Dynamic System Variables
25 0
|
11月前
|
关系型数据库 MySQL
|
2月前
|
存储 SQL 关系型数据库
MySQL存储过程 if、case、while、loop、游标、变量、条件处理程序
MySQL存储过程 if、case、while、loop、游标、变量、条件处理程序
73 0
|
2月前
|
存储 关系型数据库 MySQL
认识MySQL数据库中用户自定义变量
认识MySQL数据库中用户自定义变量。
63 0
|
7月前
|
存储 SQL 关系型数据库
MySQL基础-变量/流程控制/游标/触发器
MySQL基础-变量/流程控制/游标/触发器
|
2月前
|
存储 SQL 关系型数据库
⑩⑤【DB】详解MySQL存储过程:变量、游标、存储函数、循环,判断语句、参数传递..
⑩⑤【DB】详解MySQL存储过程:变量、游标、存储函数、循环,判断语句、参数传递..
63 0