15_MySQL中的变量与函数

0. 概述

存储过程和函数是 事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

​ 即可以在数据库中书写存储过程与函数,使用JDBC时就可以直接调用这俩者,而不用再使用一条条的SQL语句增加服务器与数据库 的传输

​ 存储过程和函数的区别在于函数必须有返回值，而存储过程没有。

​ 函数 ： 是一个有返回值的过程 ；

​ 过程 ： 是一个没有返回值的函数 ；

1. 变量

在MySQL中变量分为系统变量和自定义变量，它们又有划分：

• 系统变量
  • 全局变量
  • 会话变量
• 自定义变量
  • 用户变量
  • 局部变量

1.1 系统变量

系统变量就是由MySQL系统提供的变量，不属于用户定义，属于服务器层面。

注意：全局变量需要添加GLOBAL关键字，会话变量需要添加SESSION关键字，而如果不写关键字，那么默认是会话级变量。

系统变量使用的基本语法如下：

-- 查看所有系统变量
SHOW GLOBAL|[SESSION] VARIABLES;# GLOBAL表示查看系统全局变量，SESSION表示查看系统会话变量，[SESSION]是一个可选项，如果不写则默认是会话变量
-- 查看满足部分条件的系统变量
SHOW GLOBAL|[SESSION] VARIABLES LIKE '%匹配字符%';# LIKE '%char'的语法与MySQL模糊查询语法一致，使用通配符匹配
-- 查看指定的系统变量的值
SELECT @@GLOBAL|[SESSION].系统变量名;# 例如：select @@global.hostname;#查看主机名
-- 为某个系统变量赋值
# 方式一
SET GLOBAL|[SESSION] 系统变量名=值;
# 方式二
SET @@GLOBAL|[SESSION].系统变量名=值;# 例如：set @@global.autocommit=1;

1.1.1全局变量

全局变量的作用域是针对于所有会话（连接）有效，但不能跨重启（即重启数据库服务）。所谓的会话就是一次连接

全局变量的常用语法如下：

-- 查看所有全局变量
SHOW GLOABL VARIABLES;# 必须写GLOBAL，表示全局
-- 查看满足条件的部分全局变量
SHOW GLOBAL VARIABLES LIKE '%匹配字符%';
-- 查看指定全局变量的值
SELECT @@GLOBAL.全局变量名;# 也可以添加上GLOBAL
-- 为某个全局变量赋值
SET @@GLOBAL.全局变量名='新值';
SET GLOBAL 全局变量名='新值';

1.1.2 会话变量

会话变量只针对于当前会话（连接）有效。会话变量的常用语法为：

-- 查看所有会话变量
SHOW [SESSION] VARIABLES;# 即使不写session，默认也是会话级变量
-- 查看满足条件的部分会话级变量
SHOW [SESSION] VARIABLES LIKE '%匹配字符%';
-- 查看指定会话变量的值
SELECT @@会话级变量名;# 默认就是会话级变量
SELECT @@SESSION.会话级变量名;# 也可以添加上SESSION
-- 为某个会话变量赋值
SET @@SESSION.会话级变量名='新值';
SET SESSION 会话级变量名='新值';

1.2 自定义变量

自定义变量顾名思义就是用户自己定义的变量，而不是由系统提供的。

使用自定义变量需要先声明再赋值，最后才能使用（查看、比较、运算等）。

1.2.1 用户变量

用户变量只针对于当前会话（连接）有效，作用域等同于会话变量。

用户变量的使用语法如下：

-- 第一步：声明并初始化用户变量，有下面三种方式
SET @变量名=值;# 方式一
SET @变量名:=值;# 方式二
SELECT @变量名:=值;# 方式三
-- 第二步：赋值（也可以更新变量的值），有两种方式
# 方式一（下面这三种都可以为变量赋值）
SET @变量名=值;# 方式一，也使用较多
SET @变量名:=值;# 方式一
SELECT @变量名:=值;# 方式一
# 方式二
SELECT 字段 INTO @变量名 FROM 表名;# 注，也使用较多
-- 第三步：使用（查看变量的值）
SELECT @变量名;

简单演示:

-- 示例：声明两个变量，求和并打印
SET @n1=5;
SET @n2=10;
SET @sum=@n1+@n2;
SELECT @sum;

1.2.3 局部变量

局部变量仅仅作用在定义它的BEGIN END块中有效，并且要用在BEGIN END块中的第一句话。

通过 DECLARE 可以定义一个局部变量,直接赋值使用 SET，可以赋常量或者赋表达式,也可以通过select … into 方式进行赋值操作

它的使用语法如下:

-- 第一步：声明局部变量
DECLARE 变量名 数据类型;
DECLARE 变量名 数据类型 [DEFAULT 值];# 可以添加默认约束
-- 第二步：赋值（更新变量的值）
# 方式一（下面三种都能赋值）
SET 局部变量名=值;
SET 局部变量名:=值;
SELECT 局部变量名:=值;
# 方式二
SELECT 字段 INTO 局部变量名 FROM 表名;
-- 第三步：使用（查看变量的值）
SELECT 局部变量名;

简单示例:

-- 示例：声明两个变量，求和并打印
DECLARE n1 INT DEFAULT 10;
DECLARE n2 INT;
SET n2=5;
DECLARE sum INT;
SET sum=n1+n2;
SELECT sum;

1.2.4 局部变量与用户变量的区别:

	用户变量	局部变量
作用域	当前会话	定义它的BEGIN END块中
定义位置	会话的任何地方	BEGIN END块的第一句话
语法	加@符号	一般不用加@，需要指定类型
示例	SET @n1=5;	DECLARE n1 INT DEFAULT 5;

1.3 游标/光标

游标是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用光标对结果集进行循环的处理。类似JDBC中的ResultSet(又或许ResultSet的设计就是基于游标)

光标的使用包括光标的声明、OPEN、FETCH 和 CLOSE，其语法分别如下。

声明光标：

DECLARE 游标名 CURSOR FOR select语句;
-- CURSOR(游标),用来声明这是一个游标类型

OPEN 光标：

OPEN cursor_name ;

FETCH 光标：

FETCH cursor_name INTO var_name [, var_name] ...
-- 可以将游标看成指针

• 每调用一次fetch指令,获取该行数据,并且指针向下移动一行
• 接着可以使用INTO将游标中的数据赋值给变量
• 和ResultSet中的指针游标不同的是mysql的游标一开始就指向第一行数据

CLOSE 光标：

CLOSE cursor_name ;

示例 :

初始化脚本:

create table emp(
  id int(11) not null auto_increment ,
  name varchar(50) not null comment '姓名',
  age int(11) comment '年龄',
  salary int(11) comment '薪水',
  primary key(`id`)
)engine=innodb default charset=utf8 ;

insert into emp(id,name,age,salary) values(null,'金毛狮王',55,3800),(null,'白眉鹰王',60,4000),(null,'青翼蝠王',38,2800),(null,'紫衫龙王',42,1800);

-- 查询emp表中数据, 并逐行获取进行展示
create procedure pro_test11()
begin
  declare e_id int(11);
  declare e_name varchar(50);
  declare e_age int(11);
  declare e_salary int(11);
  declare emp_result cursor for select * from emp;
  
  open emp_result;
  
  fetch emp_result into e_id,e_name,e_age,e_salary;
  select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary);
  
  fetch emp_result into e_id,e_name,e_age,e_salary;
  select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary);
  
  fetch emp_result into e_id,e_name,e_age,e_salary;
  select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary);
  
  fetch emp_result into e_id,e_name,e_age,e_salary;
  select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary);
  
  fetch emp_result into e_id,e_name,e_age,e_salary;
  select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary);
  
  close emp_result;
end$

通过循环结构 , 获取游标中的数据 :

DELIMITER $

create procedure pro_test12()
begin
  DECLARE id int(11);
  DECLARE name varchar(50);
  DECLARE age int(11);
  DECLARE salary int(11);
  -- 定义边界变量
  DECLARE has_data int default 1;
  
  DECLARE emp_result CURSOR FOR select * from emp;
  -- 使用MySQL提供的句柄机制设置当没有数据时边界变量为0
  -- 必须就写在游标下方,中间不能有其他语句隔开
  DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;
  
  open emp_result;
  
  repeat
    fetch emp_result into id , name , age , salary;
    select concat('id为',id, ', name 为' ,name , ', age为 ' ,age , ', 薪水为: ', salary);
    until has_data = 0
  end repeat;
  
  close emp_result;
end$

DELIMITER ; 

2. 函数

函数同存储过程一样，都是一组预先编译好的SQL语句的集合，可以理解成批处理语句。

函数与存储过程的区别：

存储过程：可以有0个返回值，也可以有多个返回值，诗号做批量插入、批量更新。

函数：有且只有1个返回值（必须有一个返回值并且只能有一个返回值），适合处理数据后返回一个结果的情况。

2.1 创建函数

语法:

CREATE FUNCTION 函数名(参数名 参数类型,...) RETURNS 返回值类型
BEGIN# 相当于Java方法中的大括号{}
	函数体
END

注意:

1. 和java不同,mysql的函数参数列表中先写参数名再写参数类型
2. 函数体，一定要有return语句，如果没有就会报错。如果return语句没有放在函数体的最后一行虽然不会报错，但不建议这么做
3. 函数体如果只有一句话，那么就可以省略BEGIN END
4. 使用DELIMITER语句设置结束标记
5. 注意，定义的函数名一定不能和系统已有的函数同名，否则会报错，但根据提示你可能察觉不到是函数名的问题

2.2 调用函数

使用select调用:

SELECT 函数名(参数列表);

2.3 查看函数

查看函数的创建过程:

-- 语法
SHOW CREATE FUNCTION 函数名; -- 查看函数的创建过程
-- 示例
SHOW CREATE FUNCTION myf1;

2.4 删除函数

-- 语法
DROP FUNCTION 函数名;
-- 示例
DROP FUNCTION myf1;

2.5 函数示例:

2.5.1 无参函数:

-- 示例1：返回公司的员工个数
# 创建函数
DELIMITER $ -- 将"$"符号作为结束标记
CREATE FUNCTION myf1() RETURNS INT
BEGIN
    DECLARE c INT DEFAULT 0; -- 定义局部变量，存储员工个数
    SELECT COUNT(*) INTO c -- 赋值
    FROM employees;
    RETURN c;
END$
​
# 调用函数
DELIMITER ; -- 将分号恢复为结束标记
SELECT myf1();

2.5.2 有参函数:

-- 示例1：根据员工名，返回它的工资
# 创建函数
DELIMITER $ -- 将"$"符号作为结束标记
CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	SET @salary=0; -- 定义用户变量，存储员工工资
	SELECT salary INTO @salary -- 赋值
	FROM employees
	WHERE last_name=empName;
	RETURN @salary; -- 返回值
END$
 
# 调用函数
DELIMITER ; -- 将分号恢复为结束标记
SELECT myf2('张飞');