Mysql数据库基础第六章：变量、存储过程与函数

1.1 系统变量

系统变量是在启动mysql服务时会给系统变量赋值，定义了当前mysql服务实例的一些特征，例如在事务中的autocommit 系统变量分为：
全局变量（global）：全局变量对所有的连接有效，但是重启会失效。
会话变量（session，mysql默认）：会话变量，不能跨连接
1.1.1查看系统变量

查看所有或者部分系统变量
# 查看所有全局变量
show global variables;
# 查看所有会话变量
show variables;
#查看部分需要的变量
show variables like '%aa%'
查看指定系统变量，使用@@
# 1.查看指定系统变量的值
select @@global.autocommit;
# 2.查看指定的会话变量的值
SELECT @@变量名;
修改系统变量值
set @@global.变量名 = 值;
set global 变量名=值
set @@session.变量名 = 值;
set session 变量名=值
1.2 自定义变量
自定义变量是用户自己定义的，根据其使用范围分为用户变量和局部变量
1.2.1 用户变量
#1. 使用set
set @用户变量 = value;
#2. 使用select
select @用户变量 := 表达式;
select 表达式 Into @用户变量
set @用户变量 = value
select @用户变量
1.2.2 局部变量
只能使用在begin end中，且只能放在第一个语句
declare 变量 类型 [default 默认值]
set 变量名 = 值
select 值 into 变量名 from
select 局部变量
案例：查询两个变量的和
1.使用用户变量
SET @m = 1;
SET @n = 1;
SET @sum = @m+@n;
SELECT @sum;
2.使用局部变量
DELIMITER $
CREATE PROCEDURE sum_2()
BEGIN
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 1;
DECLARE SUM INT;
SET  SUM= m+n;
SELECT SUM;
END$
DELIMITER ;
1.2.3 区别
			定义			作用域				定义位置
用户变量 	需要用@			当前会话				会话中的任何位置
局部变量	    不需要@			只用于begin-end中	begin-end的第一句话 
2.存储过程
类似于python中的方法，将一组预先编译好的sql语句进行封装。
-1、存储过程可以一次编译多次使用。存储过程只在创建时进行编译，之后的使用都不需要重新编译，这就提升了 SQL 的执行效率。
-2、可以减少开发工作量。将代码 封装 成模块，实际上是编程的核心思想之一，这样可以把复杂的问题拆解成不同的模块，然后模块之间可以 重复使用 ，在减少开发工作量的同时，还能保证代码的结构清晰。
-3、存储过程的安全性强。我们在设定存储过程的时候可以 设置对用户的使用权限 ，这样就和视图一样具有较强的安全性。
-4、可以减少网络传输量。因为代码封装到存储过程中，每次使用只需要调用存储过程即可，这样就减少了网络传输量。
-5、良好的封装性。在进行相对复杂的数据库操作时，原本需要使用一条一条的 SQL 语句，可能要连接多次数据库才能完成的操作，现在变成了一次存储过程，只需要 连接一次即可 。
- 1、可移植性差。存储过程不能跨数据库移植，比如在 MySQL、Oracle 和 SQL Server 里编写的存储过程，在换成其他数据库时都需要重新编写。
- 2、调试困难。只有少数 DBMS 支持存储过程的调试。对于复杂的存储过程来说，开发和维护都不容易。虽然也有一些第三方工具可以对存储过程进行调试，但要收费。
- 3、存储过程的版本管理很困难。比如数据表索引发生变化了，可能会导致存储过程失效。我们在开发软件的时候往往需要进行版本管理，但是存储过程本身没有版本控制，版本迭代更新的时候很麻烦。
- 4、它不适合高并发的场景。高并发的场景需要减少数据库的压力，有时数据库会采用分库分表的方
式，而且对可扩展性要求很高，在这种情况下，存储过程会变得难以维护， 增加数据库的压力 ，显然就不适用了。
2.1 分类
存储过程包含三类
1.没有参数
2.in:参数输入
3.out:参数输出
4.inout:参数既可以输入也可以输出
2.2 语法
2.2.1 创建存储过程
create procedure 存储过程名字(参数)
begin
sql语句
注意事项：
1.BEGIN…END：BEGIN…END 中间包含了多个语句，每个语句都以（;）号为结束符。
2.DECLARE：DECLARE 用来声明变量，使用的位置在于 BEGIN…END 语句中间，而且需要在其他语句使用之前进行变量的声明。
3. SET：赋值语句，用于对变量进行赋值。
4. SELECT… INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值。
5.存储过程的结尾可以使用 delimiter 重新设置
2.2.2 代码案例
1.不带任何参数：查询employees表中的所有数据
delimiter $ # 设置结束符
create procedure select_all() # 创建存储过程
begin
select * from employees;
end $ # 结束
delimiter ;
call select_all(); #调用存储过程
2.返回员工的平均工资
delimiter $ # 设置结束符
create procedure avg_salary() # 创建存储过程
begin
select avg(salary) from employees;
end $ # 结束
delimiter ;
call avg_salary();
3.out类型的存储过程：将employees表中最低工资通过ms参数输出
delimiter $ # 设置结束符
create procedure min_salary(out ms double) # 创建out类型存储过程，ms为参数
begin
select min(salary) into ms
from employees;
end $ # 结束
delimiter ;
call min_salary(@ms);
select @ms;
4.in类型的存储过程,查看某个员工的工资，参数ename
delimiter $ # 设置结束符
create procedure name_salary(in ename varchar(20)) # 创建in类型存储过程，ename为参数
begin
select salary from employees
where last_name = ename;
end $ # 结束
delimiter ;
call name_salary('Abel');
5.同时带in和out类型的存储过程。（1）查看某个员工的薪资（2）用out参数esalary输出
delimiter $ # 设置结束符
create procedure name_salary2(in ename varchar(20), out esalary decimal(10,2)) # 创建in-out类型存储过程，ename,esalary为参数
begin
select salary into esalary #存入到额salary中
from employees
where last_name = ename;
end $ # 结束
delimiter ;
call name_salary2('Abel',@esalary);
select @esalary;
6.带inout类型的存储过程,查询某个员工的管理者名字
delimiter $ # 设置结束符
create procedure  manager_name(inout ename varchar(20)) # 创建inout类型存储过程，ename为参数
begin
select last_name into ename # 传入到inout参数
from employees
where employee_id  = (
select manager_id from employees 
where last_name = ename 
); # 使用子查询得到管理者的id
end $ # 结束
delimiter ;
call manager_name('Abel',@ename);
select @ename;
2.2.3 调用存储过程
# 1.in类型
call sp('值')
#2.out类型
call sp(@name);
select @name;
#3.inout类型
set @name = '值';
call sp(@name);
select @name;
7.创建一个存储过程，计算1+...+n的和
DELIMITER $ # 设置结束符
CREATE PROCEDURE  sum_n(IN n INT ) # 创建in,n为参数
BEGIN
# 定义局部变量
DECLARE i INT; 
DECLARE SUM INT;
SET i = 1;
SET SUM = 0;
WHILE i<=n DO # 开启循环
SET SUM = SUM + i;
SET i = i + 1;
END WHILE;#结束while循环
SELECT SUM;
END $ # 结束
DELIMITER ;
CALL sum_n(100);
函数一组预先编译好的SQL语句的集合，理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
与存储过程的区别：
存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
函数：有且仅有1 个返回，适合做处理数据后返回一个结果
3.1函数创建
create function func_name(参数) return 返回类型
[characteristics]
begin
3.2注意事项：
1.参数列表 包含两部分：
参数名 参数类型，函数默认是in参数
2.函数体：肯定会有return语句，如果没有会报错
如果return语句没有放在函数体的最后也不报错，但不建议
return 值;
3.函数体中仅有一句话，则可以省略begin end
4.使用 delimiter语句设置结束标记
3.3函数调用
函数调用方法和系统函数一样，案例如下
1.空参数，名称为email_by_name()，该函数查询Abel的email，并返回，数据类型为字符串型。
delimiter $
create function email_by_name() returns varchar(20)
begin
return (select email from employees where last_name = 'Abel');
end $
delimiter ;
如果出现 you might want to use the less safe log_bin_trust_function_creators variable错误，将系统变量设置SET GLOBAL log_bin_trust_function_creators = 1;即可，也可以增加函数特性
2.名称为email_by_id()，参数传入emp_id，该函数查询emp_id的email，并返回，数据类型为字符串型。
delimiter $
create function email_by_id(emp_id int) returns varchar(20)
begin
return (select email from employees where employee_id = emp_id);
end $
delimiter ;
select email_by_id(101);
3.创建存储函数count_by_id()，参数传入dept_id，该函数查询dept_id部门的员工人数，并返回，数据类型为整型。
delimiter $
create function count_by_id(dept_id int) returns int
begin
return (select count(*) from employees where department_id = dept_id);
end $
delimiter ;
select count_by_id(50);
3.4 函数查看
方式1. 使用SHOW CREATE语句查看存储过程和函数的创建信息
SHOW CREATE PROCEDURE show_mgr_name;
SHOW CREATE FUNCTION count_by_id;
方式2. 使用SHOW STATUS语句查看存储过程和函数的状态信息
SHOW PROCEDURE STATUS;
SHOW PROCEDURE STATUS LIKE 'show_max_salary';
SHOW FUNCTION STATUS LIKE 'email_by_id';
3.5 函数删除
DROP FUNCTION IF EXISTS count_by_id;