1. 存储过程基本用法
1.1 概念
存储过程，也叫做存储程序，是一条或者多条 SQL 语句的集合，可以视为批量处理，但是其作用不仅仅局限于批量处理。
其中针对存储过程也有多种操作：如何创建存储过程，以及如何调用、查看、修改、删除存储过程。存储过程也可以调用其他存储过程。(类似于 Java 函数之间的相互调用)
存储过程和函数是：事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据库和应用之间的传输，对于提高数据处理的效率是非常有好处的。
函数：是一个有返回值的过程；过程：是一个没有返回值的函数
存储过程和自定义函数的区别：
1.2 创建存储过程
1.2.1 语法格式
# 这个地方其实是用来声明SQL语句的结束符号的delimiter //
# 这个地方此时真正的用来创建一个存储过程的create procedure 存储过程名称(参数列表)begin -- sql语句end//
# 当创建完一个存储过程之后再将分隔符替换为分号，为了不影响其他的操作delimiter ;
复制代码
1.2.2 语法介绍
delimiter : 用于设置 sql 语句分割符，默认为分号。因为在 MySQL 中每一条 SQL 语句都必须以 ；进行结束，当我们换行的时候就会执行这条 SQL 语句，但是我们此时的存储过程并没有结束，就会造成直接执行没有写完的存储过程造成报错，所以此时需要声明其他的结束符，不让其使用默认的分隔符结束 SQL 语句。
sql 语句 ：在这个部分编写 sql 语句，编写的语句需要以分号结尾，此时回车会直接执行，所以要创建存储过程前需要指定其他符号作为分割符，此时使用 // , 也可以使用其它字符。
// : 声明结束符号，这个符号可以是任意的，是自定义的。相当于就是把 分号替换为 //
创建存储过程查询学生信息
# 将SQL语句的结束符号分隔符替换为//delimiter //
create procedure proc_stu()beginselect * from students; # 此时这个分号并不会结束这个语句,存储过程中的SQL语句还是用分隔符进行分隔end // # 这里使用这个结束符号代表这个存储过程创建完成
delimiter; # 创建完一个存储过程之后将结束符号替换为分号，防止进行其他操作的时候有问题
复制代码
1.3 调用存储过程
语法格式：
call proc_stu(); # 调用的时候需要加上括号，因为可能存在参数
复制代码
1.4 查看存储过程
语法格式：
# 查询 studnet 数据库中的所有的存储过程select name from mysql.proc where db='studnet';
# 查询存储过程的状态信息show procedure status;
复制代码
1.5 删除存储过程
语法格式：
drop procedure proc_stu; # 删除的时候不要加小括号，直接给定存储过程的名字即可。drop procedure if exists proc_stu; # 如果存储删除，不存在不删除并且不会报错
复制代码
2. 存储过程中的语法结构
2.1 变量的声明以及赋值
2.1.1 DECLARE 声明变量
DECLARE : 通过 DECLARE 关键字可以定义一个局部变量，该变量的作用范围只能在 BEGIN..,END 块中。
语法格式：
DECLARE 变量名[,...] type [DEFAULT value]
复制代码
注意：声明变量的时候可以一次性声明多个，使用逗号隔开。
示例：
delimiter $
create procedure proc_stu()begin declare num int default 5; select num + 10; # 输出结果为15end $
delimiter ;
复制代码
2.1.2 SET 变量赋值
SET : 直接赋值使用 SET 关键字，可以赋常量或者是表达式，具体语法如下：
SET 变量名 = 变量值 [,变量名 = 变量值] ...
复制代码
注意：一次可以给多个变量赋值，中间使用逗号隔开。
delimiter $
create procedure proc_stu()begin declare name varchar(20); # 可以指定变量类型以及变量的范围 set name = 'MySQL'; # 给变量直接赋值 select name; # 输出结果为：MySQLend $
delimiter ;
复制代码
2.1.3 select...into 赋值
delimiter $
create procedure proc_student()begin declare count_num int(10); select count(*) into count_num from student; select count_num;end $
delimiter ;
复制代码
2.2 条件判断
2.2.1 if 条件判断
语法结构：
# 只有满足差选条件才会执行 then 后面的SQL语句if search_condition(查询条件) then statement_list(SQL语句) [else if search_condition(查询条件) then statement_list(SQL语句)]... [else statement_list(SQL语句)]end if;
复制代码
需求：
根据身高，判断当前身高所属的身材类型 180及以上 --------> 身材高挑 170 - 180 --------> 标准身材 170以下 ----------> 一般身材
复制代码
实现这个简单的逻辑
delimiter $
create procedure pro_figure()begin # 定义一个身高的变量 declare height int(11) default 175; # 定义一个存储身高类型的变量 declare figure varchar(50) default ''; # 使用 if 语句判断身材类型 if height >= 180 then set figure = '身材高挑'; else if height < 180 and height >= 170 then set figure = '标准身材'; else set figure = '一般身材'; end if; # 输出结果 select concat(height + '身高的身材为：' + figure);end $
delimiter ;
复制代码
2.3 传递参数
语法格式：
delimiter $
# 我们可以不指定 [in/out/inout] , 默认为 in,输入参数 create procedure pro_name([in/out/inout]参数名 参数类型)begin -- sql语句end $
delimiter ;
# in : 该参数可以作为输入，调用该存储过程需要传入的值，默认# out : 该参数作为输出，调用该存储过程之后返回的值。# inout : 既可以作为输入参数也可以作为输出参数。
复制代码
2.3.1 IN - 输入参数
需求：
根据输入的身高变量的值，判断当前身高对应的身材类型
复制代码
实现：
delimiter $
# 此时调用者在调用这个存储过程的时候必须传递身高的变量值create procedure pro_name(in height int(11))begin # 定义一个存储身高类型的变量 declare figure varchar(50) default ''; # 使用 if 语句判断身材类型 if height >= 180 then set figure = '身材高挑'; else if height < 180 and height >= 170 then set figure = '标准身材'; else set figure = '一般身材'; end if; # 输出结果 select concat(height + '身高的身材为：' + figure);end $
delimiter ;
复制代码
调用：
# 调用该存储过程。需要传递其中的身高值call pro_name(175); # 输出结果为：
复制代码
2.3.2 out - 输出参数
需求：
实现：
delimiter $
create procedure pro_output(in height int(11) , out figure varchar(100))begin # 使用 if 语句判断身材类型 if height >= 180 then set figure = '身材高挑'; else if height < 180 and height >= 170 then set figure = '标准身材'; else set figure = '一般身材'; end if;end $
delimiter ;
复制代码
调用
# @标识符：在MySQL中代表的就是用户定义的一个变量，这里我们使用这个变量来接收这个存储过程的返回值call pro_output(175 , @figure)；
# 查看存储过程返回的结果select @figure;
复制代码
@标识符的作用
2.4 case 结构
语法格式：
# 方式一case case_value(判断的值) when when_value(比较的值) then statement_list(SQL语句) [when when_value(比较的值) then statement_list(SQL语句)]... [else statement_list(SQL语句)]end case;
# 方式二case when search_condition(查询条件) then statement_list(SQL语句) [when search_condition(查询条件) then statement_list(SQL语句)]... [else statement_list(SQL语句)]end case;
复制代码
需求：
实现
delimiter $
create procedure pro_quarter(in mon int(11))begin # 定义存储季度的变量 declare result varchar(10); case when mon >= 1 and mon <= 3 then set result = '第一季度'; when mon >= 4 and mon <= 6 then set result = '第一季度'; when mon >= 7 and mon <= 9 then set result = '第一季度'; else set result = '第四季度'; end case; # 输出结果 select result; end $
delimiter ;
复制代码
2.5 while 循环
有条件的循环控制语句，当满足条件的时候进入循环，不满足条件的时候退出循环。
语法结构：
# 只要查询条件一直成立就会一直指定do后面的SQL语句，当查询条件不成立的时候直接跳出while循环while search_condition(查询条件) do statement_list(SQL语句)end while;
复制代码
需求：
实现：
delimiter $
create procedure pro_sum(in num int(11))begin # 定义存储总数的变量 declare total int(255) default 0; # 定义存储循环次数的数量 declare number int(255) default 1; while number <= num do set total = total + number; set number = number + 1; end while; select total;end $
delimiter ;
复制代码
2.6 repeat 循环
有条件的循环控制语句，当不满足条件的时候进入循环，满足条件的时候跳出循环。他和 while 循环是反着的
语法结构：
repeat statement_list(SQL语句) until search_condition(查询添加)end repeat;
复制代码
需求：
实现：
delimiter $
create procedure pro_sum(in num int(11))begin # 定义存储总数的变量 declare total int(255) default 0; repeat set total = total + number; set num = num - 1; # 注意：这个 unti 后的查询条件不要加分号，加分号会报错。 until num = 0 end repeat; select total;end $
delimiter ;
复制代码
2.7 loop 循环
loop 实现简单的循环，退出循环的条件需要使用其他的语句定义，通常可以使用 leave 语句实现，具体语法如下：
语法格式：
[begin_label:] loop statement_listend loop [end_label]
复制代码
如果不在 statement_list 中增加退出循环的语句，那么 loop 语句可以永安里实现简单的死循环。
3.8 leave 语句
用来从标注的流程构造中退出，通常和 begin...end 或循环一起使用。下面是一个使用 loop 和 leave 的简单例子，退出循环：
需求：
计算从1加到n的值 ---> 使用loop...leave的形式进行退出循环
复制代码
实现：
delimiter $
create procedure pro_sum(in num int(11))begin # 定义存储总数的变量 declare total int(255) default 0; c(该循环的别名):loop set total = total + num; set num = num - 1; # 借助leave组织退出条件 if num <= 0 then leave c; end if; end loop c; select total;end $
delimiter ;
复制代码
3. 游标
3.1 游标介绍
声明光标：
DECLARE cursor_name(游标变量) CURSOR(指定类型，这个就代表游标) FRO select_statement(SQL语句);# 此时这个游标就存储了这个SQL语句查询的所有结果集
复制代码
OPEN(打开)光标：
FETCH(操作)光标：
FETCH cursor_name INTO var_name [,var_name]...# FETCH每次会抓取结果集中的一个结果，然后将结果集中的指针向后移动一位。
复制代码
CLOSE(关闭)光标：
4. 存储函数(自定义函数)
关于函数体：
重点：自定义的函数不能重名，类似于定义了一个全局变量，变量名不能一致。
4.1 定义存储函数
语法格式：
create function 函数名(参数列表)returns type(返回值类型)begin --SQL语句end;
复制代码
需求：
实现：
delimiter $
create function fun(countryId int)returns int begin # 定义一个存储总数据条数的变量 declare cum int default 0; # 查询等于传递参数的全部的数据数，然后将其赋值给定义的变量 select count(*) into cum from city where country_id = countryId; # 返回结果值。存储函数必须有返回值 return cum;end $
delimiter ;
复制代码
4.2 调用存储函数
语法格式：
注意：调用存储过程的时候使用的是 call 关键字，但是在调用存储函数的时候直接使用 select 即可，就和调用 MySQL 一个普通的聚合函数的方式一样即可。
select fun(1);# 这里和存储过程一样，调用的时候需要加小括号和参数，但是在删除的时候指定函数名即可。
复制代码
4.3 删除存储函数
语法格式
drop [if exists] function fun;
复制代码
5. 触发器
5.1 触发器介绍
触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的 SQL 集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性、日志记录以及数据校验等操作。
只有增加、删除、修改的时候才可以使用触发器，查询的时候不可以使用触发器。
使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似对的。MySQL 触发器还只支持行级触发，不支持语句级触发。oracle 支持行级和语句级触发器都支持。
OLD、NEW 这两个变量又叫做行记录变量。可以通过这个两个变量来获取即将要操作的数据表中的数据。
5.2 创建触发器
语法格式
create trigger(触发器) trigger_name(触发器名称)before/after insert/update/deleteon tab_name(表名)[for each row](行级触发器)begin trigger_stmt;(触发器的逻辑)end;
复制代码
需求
通过触发器记录 emp 表的数据变更日志 emp_logs ，其中包含增加、修改、删除
复制代码
实现
分析：一个触发器只能操作一种数据的操作类型，不可以同时完成增加、修改、删除的操作。所以此时需要定义多个触发器来完成这个日志记录的任务。
因为 MySQL 中是行级操作的触发器，所以 new 以及 old 中存储的都是一整行数据。
创建执行 insert 的触发器
使用 new 关键字可以获取到操作的数据，在 insert 模式下，new 变量中存储的就是即将插入的数据
使用的是 after ，在执行完表 emp 的新增之后执行这个触发器记录日志。
这个触发器什么时候执行与两点有关：
必须操作的是 emp 这个表，也就是 on 后面声明的这个表。
必须执行的 insert 操作。
创建执行 update 的触发器
此时 old 变量中存储的是被修改前的数据，new 变量中存储的是修改之后的数据
创建执行 delete 的触发器
此时的 old 变量中存储的即将删除的数据
测试：测试都必须是操作的 emp 表，这样才会触发上边定义的触发器。
5.3 删除触发器
语法结构：
drop trigger [schema_name.](数据库名)trigger_name(触发器名);
复制代码
如果没有指定 schema_name(数据库名)，默认为当前数据库。
5.4 查看触发器
可以通过执行 show triggers 命令查看触发器的状态、语法等信息。
语法结构：
评论