搜索
您的当前位置：首页正文

MySQL 变量和条件

2023-11-09 来源：哗拓教育
  

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

 mysql版本：5.6

变量定义和赋值  
#创建数据库DROP DATABASE IF EXISTS Dpro;CREATE DATABASE DproCHARACTER 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 DATASQL SECURITY INVOKERBEGINDECLARE 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 DATASQL SECURITY INVOKERBEGINDECLARE 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 DATASQL SECURITY INVOKERBEGINDECLARE 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 DATASQL SECURITY INVOKERBEGINDECLARE 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 DATASQL SECURITY INVOKERBEGINSET @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 DATASQL SECURITY INVOKERBEGIN#定义条件名称，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 DATASQL SECURITY INVOKERBEGIN#定义条件名称，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 DATASQL SECURITY INVOKERBEGIN#引用前面定义的条件名称并做赋值处理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

本站点所有随笔都是原创，欢迎大家转载；但转载时必须注明文章来源，且在文章开头明显处给明链接。

《欢迎交流讨论》

MySQL 变量和条件

标签：

小编还为您整理了以下内容，可能对您也有帮助：

mysql 存储过程中变量的定义与赋值操作

一、变量的定义
mysql中变量定义用declare来定义一局部变量，该变量的使用范围只能在begin...end
块中使用,变量必须定义在复合语句的开头，并且是在其它语句之前，也可以同时申明多个变量，如果需要，可以使用default赋默认值。
定义一个变量语法如下:
declare
var_name[,...]
type[default
value]看一个变量定义实例
declare
last
date;二、mysql存储过程变量赋值
变量的赋值可直接赋值与查询赋值来操作，直接赋值可以用set来操作，可以是常量或表达式如果下
复制代码
代码如下:
set
var_name=
[,var_name
expr]...给上面的last变量赋值方法如下
set
last
=
date_sub(
current_date(),interval
1
month);下面看通过查询给变量赋值，要求查询返回的结果必须为一行，具体操作如下
select
col
into
var_name[,...]
table_expr我们来通过查询给v_pay赋值。
create
function
get
_cost(p_custid
int,p_eff
datetime)
return
decimal(5,2)
deterministic
reads
sql
data
begin
declare
v_pay
decimail(5,2);
select
ifnull(
sum(pay.amount),0)
into
vpay
from
payment
where
pay.payd<=p_eff
and
pay.custid=pid
reutrn
v_rent
+
v_over
-
v_pay;
end
$$
好了，这篇简单的存储过程中变量的定义赋值教程就到这里了，下面我们会接着讲关于myql存储过程的条件的定义与处理。
以下是其它网友的补充
在MySQL的存储过程中，可以使用变量，它用于保存处理过程中的值。
定义变量使用DECLARE语句，语法格式如下：
DECLARE
var_name[,...]
type
[DEFAULT
value]
其中，var_name为变量名称，type为MySQL支持的任何数据类型，可选项[DEFAULT
value]为变量指定默认值。一次可以定义多个同类型的变量，各变量名称之间以逗号“,”隔开。
定义与使用变量时需要注意以下几点：
◆
DECLARE语句必须用在DEGIN…END语句块中，并且必须出现在DEGIN…END语句块的最前面，即出现在其他语句之前。
◆
DECLARE定义的变量的作用范围仅限于DECLARE语句所在的DEGIN…END块内及嵌套在该块内的其他DEGIN…END块。
◆
存储过程中的变量名不区分大小写。
定义后的变量采用SET语句进行赋值，语法格式如下：
SET
var_name
=
expr
[,var_name
=
expr]
...
其中，var_name为变量名，expr为值或者返回值的表达式，可以使任何MySQL支持的返回值的表达式。一次可以为多个变量赋值，多个“变量名=值”对之间以逗号“,”隔开。
例如：
复制代码
代码如下:
begin
declare
no
varchar(20);
declare
title
varchar(30);
set
no='101010',title='存储过程中定义变量与赋值';
end
提示：存储过程中所有的关键字也是不区分大小写的，如BEGIN可以写出begin。

mysql 存储过程中变量的定义与赋值操作

一、变量的定义
mysql中变量定义用declare来定义一局部变量，该变量的使用范围只能在begin...end
块中使用,变量必须定义在复合语句的开头，并且是在其它语句之前，也可以同时申明多个变量，如果需要，可以使用default赋默认值。
定义一个变量语法如下:
declare
var_name[,...]
type[default
value]看一个变量定义实例
declare
last
date;二、mysql存储过程变量赋值
变量的赋值可直接赋值与查询赋值来操作，直接赋值可以用set来操作，可以是常量或表达式如果下
复制代码
代码如下:
set
var_name=
[,var_name
expr]...给上面的last变量赋值方法如下
set
last
=
date_sub(
current_date(),interval
1
month);下面看通过查询给变量赋值，要求查询返回的结果必须为一行，具体操作如下
select
col
into
var_name[,...]
table_expr我们来通过查询给v_pay赋值。
create
function
get
_cost(p_custid
int,p_eff
datetime)
return
decimal(5,2)
deterministic
reads
sql
data
begin
declare
v_pay
decimail(5,2);
select
ifnull(
sum(pay.amount),0)
into
vpay
from
payment
where
pay.payd<=p_eff
and
pay.custid=pid
reutrn
v_rent
+
v_over
-
v_pay;
end
$$
好了，这篇简单的存储过程中变量的定义赋值教程就到这里了，下面我们会接着讲关于myql存储过程的条件的定义与处理。
以下是其它网友的补充
在MySQL的存储过程中，可以使用变量，它用于保存处理过程中的值。
定义变量使用DECLARE语句，语法格式如下：
DECLARE
var_name[,...]
type
[DEFAULT
value]
其中，var_name为变量名称，type为MySQL支持的任何数据类型，可选项[DEFAULT
value]为变量指定默认值。一次可以定义多个同类型的变量，各变量名称之间以逗号“,”隔开。
定义与使用变量时需要注意以下几点：
◆
DECLARE语句必须用在DEGIN…END语句块中，并且必须出现在DEGIN…END语句块的最前面，即出现在其他语句之前。
◆
DECLARE定义的变量的作用范围仅限于DECLARE语句所在的DEGIN…END块内及嵌套在该块内的其他DEGIN…END块。
◆
存储过程中的变量名不区分大小写。
定义后的变量采用SET语句进行赋值，语法格式如下：
SET
var_name
=
expr
[,var_name
=
expr]
...
其中，var_name为变量名，expr为值或者返回值的表达式，可以使任何MySQL支持的返回值的表达式。一次可以为多个变量赋值，多个“变量名=值”对之间以逗号“,”隔开。
例如：
复制代码
代码如下:
begin
declare
no
varchar(20);
declare
title
varchar(30);
set
no='101010',title='存储过程中定义变量与赋值';
end
提示：存储过程中所有的关键字也是不区分大小写的，如BEGIN可以写出begin。

mysql %_20%什么意思

获取某列含有 _20 的行。

select * from tbl_name where col_name = '%_20%'; #找出col_name列里含有 _20 的行。

mysql 怎么定义变量

下面是一个简单的 存储过程的例子.

DECLARE v_index INT;

定义一个 名称为 v_index 的变量, 类型为 INT

MYSQL 变量定义应该只能在 存储过程, 函数里面定义.

不像 Oracle / SQL Server ， 一个 BEGIN / END 里面就可以定义/执行了。

mysql> DELIMITER //

mysql> CREATE PROCEDURE TestWhile()

-> BEGIN

-> DECLARE v_index INT;

->

-> SET v_index = 0;

->

-> WHILE v_index < 5 DO

-> SET v_index = v_index + 1;

-> SELECT v_index;

-> END WHILE;

->

-> END//

Query OK, 0 rows affected (0.00 sec)

Copyright © 2019- huatuo2.com 版权所有
湘ICP备2023021991号-2

Top