【MySQL】存储过程、存储函数、触发器

存储过程介绍
技术背景
存储过程的作用与优势
- 存储过程跟自定义函数很像。它们的区别是：
存储过程的缺点
存储过程的特性
基本存储过程使用
- 1.创建
- - 语法
  - 语法说明：
  - 使用案例
  - - 1.创建获取新闻类别数量的存储过程
    - 2.创建获取指定新闻类别ID下新闻数量的存储过程
- 2.调用
- - 语法
  - 示例
- 3.查看
- 4.删除
存储过程中的语法构造
- 1.变量
- - 1.1系统变量
  - - 查看系统变量
    - 设置系统变量
    - 示例
    - 备注
  - 1.2用户定义变量
  - - 语法
    - 使用
    - 演示
  - 1.3局部变量
  - - 局部变量的声明
    - 示例
    - 局部变量的赋值
    - - SET :
      - SELECT INTO：
- 2.条件判断
- - 2.1if判断
  - - 语法
    - 示例
  - 2.2 case
  - - 语法一
    - 语法二
    - 示例
3.循环结构
- 3.1while循环
- - 语法：
  - 示例：计算从1累加到n的值，n为传入的参数值
- 3.2repeat循环
- - 语法
  - 示例：计算从1累加到n的值，n为传入的参数值
- 3.3loop循环
- - 语法
  - 示例1：计算从1累加到n的值，n为传入的参数值
  - 示例2：计算从1到n之间的偶数累加的值，n为传入的参数值
- 3.4游标（循环遍历）
- - 语法
  - 示例：定义存储过程，完成如下需求
  - 问题
4.条件处理程序（Handler）
- 语法
- 示例
存储函数
- 语法
- 示例：计算从1累加到n的值，n为传入的参数值。
触发器（trigger）
- 触发器类型
- 语法
- 示例
- - INSERT型触发器
  - update型触发器
  - delete型触发器

存储过程介绍

存储过程是一组为了完成特定功能的 SQL 语句集合。
使用存储过程的目的是将常用或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因此称为存储过程。
当以后需要数据库提供与已定义好的存储过程的功能相同的服务时，只需调用“CALL 存储过程名字”即可自动完成。（类似调用Java里面的函数）

技术背景

我们通常使用的SQL 语句都是针对一个表或几个表的单条 SQL 语句，但是在数据库的实际操作中，并非所有操作都那么简单，有时候一个完整的操作需要多条 SQL 语句处理多个表才能完成。

例如：为了确认学生能否毕业，需要同时查询学生档案表、成绩表和综合表。此时就需要使用多条 SQL 语句来针对几个数据表完成这个处理要求。存储过程可以有效地完成这个数据库操作。

常用操作数据库的 SQL 语句在执行的时候需要先编译，然后执行。存储过程则采用另一种方式来执行 SQL 语句。一个存储过程是一个可编程的函数，它在数据库中创建并保存，一般由 SQL 语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的特定功能时，存储过程尤为合适。

存储过程的作用与优势

封装性。
存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的 SQL 语句，并且数据库专业人员可以随时对存储过程进行修改，而不会影响到调用它的应用程序源代码
可增强 SQL 语句的功能和灵活性。
存储过程可以用流程控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算
可减少网络流量。
这个不难理解，以往我们使用单条sql，每次调用都需要发起一次IO请求将完整的IO请求发送过去，但是现在只需要【CALL 存储过程】就可以了。确切的说：由于存储过程是在服务器端运行的，且执行速度快，因此当客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而可降低网络负载
高性能。
存储过程执行一次后，产生的二进制代码就驻留在缓冲区，在以后的调用中，只需要从缓冲区中执行二进制代码即可，从而提高了系统的效率和性能
提高数据库的安全性和数据的完整性。
使用存储过程可以完成所有数据库操作，并且可以通过编程的方式控制数据库信息访问的权限

存储过程跟自定义函数很像。它们的区别是：

存储过程实现的功能要复杂一些；而函数的针对性更强。
存储过程可以返回多个值；函数只能有一个返回值。
存储过程一般独立的来执行；而函数可以作为其他SQL语句的组成部分实现出来。

存储过程的缺点

如果使用大量的存储过程，那么使用这些存储过程的每个连接的内存使用量将大大增加。此外，如果在存储过程中过度使用大量的逻辑操作，那么CPU的使用率也在增加，因为MySQL数据库最初的设计就侧重于高效的查询，而不是逻辑运算。
存储过程的构造使得开发具有了复杂的业务逻辑的存储过程变得困难。
很难调试存储过程。只有少数数据库管理系统允许调试存储过程。不幸的是，MySQL不提供调试存储过程的功能。
开发和维护存储过程都不容易。开发和维护存储过程通常需要一个不是所有应用程序开发人员拥有的专业技能。这可能导致应用程序开发和维护阶段的问题。
对数据库依赖程度较高，移值性差。

存储过程的特性

有输入输出参数，可以声明变量，有if/else, case,while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能；
函数的普遍特性：模块化，封装，代码复用；
速度快，只有首次执行需经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤；

基本存储过程使用

1.创建

语法

create procedure 储存名([ in ,out ,inout ] 参数名 数据类形...)
begin
  sql语句
end 自定义的结束符合

语法说明：

上面的CREATE PROCEDURE，BEGIN和END是固定的
储存名：存储过程的名字。我们在调用的时候，就是使用CALL + 过程名调用。另外，尽量避免过程名跟Mysql已存在的函数名相同导致冲突
参数列表-参数类型：存储过程可以没有参数，也可以有多个参数，参数的声明是【参数名+参数类型】声明的。
存储过程中的参数分别是 in，out，inout三种类型；
- in代表输入参数（默认情况下为in参数），表示该参数的值必须由调用程序指定。
- ou代表输出参数，表示该参数的值经存储过程计算后，将out参数的计算结果返回给调用程序。
- inout代表即时输入参数，又是输出参数，表示该参数的值即可有调用程序制定，又可以将inout参数的计算结果返回给调用程序。

使用案例

1.创建获取新闻类别数量的存储过程

CREATE PROCEDURE getAllNewsTypeCount() BEGIN
	SELECT
		tname,
		count 
	FROM
		news_category c
		JOIN ( SELECT ntid, count( ntid ) count FROM news_detail GROUP BY ntid ) d ON c.tid = d.ntid 
	ORDER BY
		count DESC;
END

2.创建获取指定新闻类别ID下新闻数量的存储过程

CREATE PROCEDURE getNewsTypeCountByTid(in tid int) BEGIN
	SELECT
		tname,
		count 
	FROM
		news_category c
		JOIN ( SELECT ntid, count( ntid ) count FROM news_detail where ntid=tid ) d ON c.tid = d.ntid 
	ORDER BY
		count DESC;
END ;

2.调用

语法

CALL 存储过程名称([参数]);

示例

call getAllNewsTypeCount();

在这里插入图片描述

call getAllNewsTypeCount();

在这里插入图片描述

3.查看

-- 查询指定数据库的存储过程及状态信息
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = '数据库名称';

-- 查询整个存储过程的定义
SHOW CREATE PROCEDURE 存储过程名称;

# 查询存储过程的状态信息
show procedure status;

在这里插入图片描述

4.删除

DROP PROCEDURE [IF EXISTS] 存储过程名称;

在这里插入图片描述

存储过程中的语法构造

存储过程是可以编程的，意味着可以使用变量、表达式、控制语句来完成比较复杂的功能

1.变量

1.1系统变量

系统变量时MySQL服务器提供，不是用户定义的，属于服务器层面。
可分为：全局变量（GLOBAL）、会话变量（SESSION）。
全局变量在所有会话中有效，会话变量仅在当前会话中有效。

查看系统变量

-- 查看所有系统变量
SHOW [SESSION | GLOBAL] VARIABLES;

-- 可以通过like模糊匹配方式查找变量
SHOW [SESSION | GLOBAL] VARIABLES LIKE '...';

-- 查看指定变量的值
SELECT @@[SESSION. | GLOBAL.] 系统变量名;

设置系统变量

SET [SESSION | GLOBAL] 系统变量名 = 值;
SET @@[SESSION. | GLOBAL.]系统变量名 = 值;

示例

-- 变量：系统变量
-- 查看系统变量
show session variables;
show session variables like 'auto%';
show global variables like 'auto%';
select @@global.autocommit;

-- 设置系统变量
set session autocommit = 1;
set global autocommit = 1;
set @@global.autocommit = 1;

备注

如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量。
mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在/etc/my.cnf中设置。

1.2用户定义变量

用户定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用“@变量名”使用就可以。其作用域为当前连接（会话）。

语法

SET @var_name = expr[, @var_name = expr] ...;
SET @var_name := expr[, @var_name := expr] ...;

SELECT @var_name := expr[, @var_name := expr] ...;
SELECT 字段名 INTO @var_name FROM 表名;

使用

SELECT @var_name;

演示

-- 变量：用户定义变量
-- 赋值
set @myname = 'itcast';
set @myage := 10;
set @mygender := '女', @myhobby := 'mysql';

select @mycolor := 'red';
select count(*) into @mycount from tb_user;

-- 使用
select @myname, @myage, @mygender;

select @mycolor, @mycount;

用户定义的变量无需对其进行声明或初始化，只不过获取的值为NULL

1.3局部变量

局部变量是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN … END块。

局部变量的声明

DECLARE 变量名[,...] type [DEFAULT value]

声明变量的时候可以一次性声明多个，使用逗号隔开。

示例

CREATE PROCEDURE calculate ()
BEGIN
	DECLARE num1 INT;
	DECLARE num2 INT DEFAULT 1;
	SELECT num1 + num2;
END

注意上面示例num1是没有默认值的，所以SELECT num1 + num2;是一个null值。

局部变量的赋值

变量的赋值有2种方式，分别为：SET赋值，以及SELECT INTO赋值。

SET :

直接赋值使用SET关键字，可以赋常量或者是表达式，具体语法如下：

-- 注意：一次可以给多个变量赋值，中间使用逗号隔开。
SET 变量名 = 变量值 [,变量名 = 变量值] ...

示例：

 CREATE PROCEDURE calculate ()
	BEGIN
		DECLARE num1 INT;
		DECLARE num2 INT DEFAULT 1;
		SET num1=2,num2=3;
		SELECT num1 + num2;
	END

SELECT INTO：

语法格式如下

	SELECT <column | 聚合函数> INTO 变量名;

示例

CREATE PROCEDURE getNewsTypeCountByTid(in tid int) BEGIN
		DECLARE count int;
		DECLARE num INT DEFAULT 1;
		SELECT count( ntid ) into count FROM news_detail where ntid=tid;
		select count + num;
	END ;

在这里插入图片描述

2.条件判断

2.1if判断

语法

IF 条件1 THEN
  ...
ELSEIF 条件2 THEN    -- 可选
  ...
ELSE                -- 可选
  ...
END IF;

示例

DROP PROCEDURE IF EXISTS getNewsHotStatus;
CREATE PROCEDURE getNewsHotStatus(in tid int,out hotStatus VARCHAR(10)) BEGIN
	DECLARE count int;
	SELECT count( ntid ) into count FROM news_detail where ntid=tid;
	IF count>10 THEN
		set hotStatus='火爆';
	ELSEIF count>5 THEN    
		set hotStatus='一般';
	ELSE                
		set hotStatus='冷门';
	END IF;
END ;

set @hotStatus='';
call getNewsHotStatus(6,@hotStatus);
select @hotStatus;

在这里插入图片描述

2.2 case

语法一

CASE case_value
     WHEN when_value1 THEN statement_list1
     [WHEN when_value2 THEN statement_list2]...
     [ELSE statement_list]
END CASE;

语法二

CASE
     WHEN search_condition1 WHEN statement_list1
     [WHEN search_condition2 WHEN statement_list2]...
     [ELSE statement_list]
END CASE;

示例

-- 创建存储过程
create procedure getQuarter(in month int)
begin
  declare result varchar(10);
  
  case
    when month >= 1 and month <= 3 then
      set result := '第一季度';
    when month >= 4 and month <= 6 then
      set result := '第二季度';
    when month >= 7 and month <= 9 then
      set result := '第三季度';
    when month >= 10 and month <= 12 then
      set result := '第四季度';
    else 
      set result := '非法传输';
  end case;
  
  select concat('您输入的月份为：',month,"，所属的季度为：",result);
end;

-- 调用
call getQuarter(8);

3.循环结构

3.1while循环

while循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句。

语法：

# 先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
WHILE 条件 DO
   SQL逻辑...
END WHILE;

示例：计算从1累加到n的值，n为传入的参数值

-- A.定义局部变量，记录累加之后的值
-- B.每循环一次，就会对n进行减1，如果n减到0，则退出循环

create procedure whileSum(in n int) 
begin
  declare total int default 0;
  
  while n > 0 do 
     set total := total + n;
     set n := n - 1;
  end while;
	
	select total;
end;

-- 调用
call whileSum(100);

3.2repeat循环

repeat是有条件的循环控制语句，当满足条件的时候退出循环

语法

# 先执行一次逻辑，然后判定逻辑是否满足，如果满足，则退出。如果不满足，则继续下一次循环
REPEAT 
   SQL逻辑...
UNTIL 条件
END REPEAT;

示例：计算从1累加到n的值，n为传入的参数值

-- A.定义局部变量，记录累加之后的值
-- B.每循环一次，就会对n进行减1，如果n减到0，则退出循环

create procedure repeatSum(in n int) 
begin
  declare total int default 0;
  
  repeat 
     set total := total + n;
     set n := n - 1;
  until n <= 0
  end repeat;
	
	select total;
end;

-- 调用
call repeatSum(100);

3.3loop循环

LOOP实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。
LOOP可以配合以下两个语句使用：
- LEAVE：配合循环使用，退出循环。
- ITERATE：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。

语法

[begin_label:] LOOP
   SQL逻辑...
END LOOP [end_label];

-- 退出指定标记的循环体
LEAVE label;

-- 直接进入下一次循环
ITERATE label;

示例1：计算从1累加到n的值，n为传入的参数值

-- loop
-- A.定义局部变量，记录累加之后的值
-- B.每循环一次，就会对n进行减1，如果n减到0，则退出循环
create procedure loopSum(in n int)
begin
   declare total int default 0;
  
   sum:loop
	   if n <= 0 then 
		    leave sum;
	   end if;
		 
	   set total := total + n;
       set n := n - 1;
	end loop sum;
	
	select total;
end;

-- 调用
call loopSum(100);

示例2：计算从1到n之间的偶数累加的值，n为传入的参数值

-- A. 定义局部变量, 记录累加之后的值;
-- B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环 ----> leave xx
-- C. 如果当次累加的数据是奇数, 则直接进入下一次循环. --------> iterate xx
 
create procedure evenSum(in n int)
begin
    declare total int default 0;
 
    sum:loop
        if n<=0 then
            leave sum;
        end if;
 
        if n%2 = 1 then
            set n := n - 1;
            iterate sum;
        end if;
 
        set total := total + n;
        set n := n - 1;
    end loop sum;
 
    select total;
end;
 
-- 调用
call evenSum(100);

3.4游标（循环遍历）

游标（cursor）是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。

语法

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

#声明游标
DECLARE 游标名称 CURSOR FOR 查询语句;
#打开游标
OPEN 游标名称;
#获取游标记录
FETCH 游标名称 INTO 变量[,变量];
#关闭游标
CLOSE 游标名称;
#释放游标
DEALLOCATE PREPARE 游标名称

示例：定义存储过程，完成如下需求

根据传入的参数uage，来查询用户表tb_user中，所有用户年龄小于等于uage的用户姓名(name)和专业(profession)，并将用户的姓名和专业插入到所创建的一张新表(id,name,profession)中。

-- 逻辑实现
-- A.声明游标，存储查询结果集
-- B.准备：创建表结构
-- C.开启游标
-- D.循环获取游标中的记录
-- E.插入数据到新表中
-- F.关闭游标

create procedure loopSave(in uage int)
begin
  declare uname varchar(100);
	declare uprofession varchar(100);
	declare u_cursor cursor for select name,profession from tb_user where age <= uage;
	
	drop table if exists tb_user_pro;
	create table if not exists tb_user_pro(
			id int primary key auto_increment,
			name varchar(100),
			profession varchar(100)
	);
	
	open u_cursor;
	
	while true do
			fetch u_cursor into uname, uprofession;
			insert into tb_user_pro values(null, uname, uprofession);
	end while;
	
	close u_cursor;
end;

-- 调用
call loopSave(40);

声明变量在声明游标之前

问题

上述的功能，虽然我们实现了，但是逻辑并不完善，而且程序执行完毕，获取不到数据，数据库还报错。接下来，我们就需要来完成这个存储过程，并且解决这个问题。

要想解决这个问题，就需要通过MySQL中提供的条件处理程序Handler来解决。

4.条件处理程序（Handler）

条件处理程序（Handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤

语法

DECLARE handler_action HANDLER FOR condition_value[,condition_value]... statement;

handler_action
- CONTINUE：继续执行当前程序
- EXIT：终止执行当前程序
condition_value
- SQLSTATE sqlstate_value：状态码，如02000
  - SQLWARNING：所有以01开头的SQLSTATE代码的简写
  - NOT FOUND：所有以02开头的SQLSTATE代码的简写
  - SQLEXCEPTION：所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的简写

示例

create procedure loopHandlerSave(in uage int)
begin
  declare uname varchar(100);
	declare uprofession varchar(100);
	declare u_cursor cursor for select name,profession from tb_user where age <= uage;
	-- declare exit handler for SQLSTATE '02000' close u_cursor;
	declare exit handler for not found close u_cursor;
	
	drop table if exists tb_user_pro;
	create table if not exists tb_user_pro(
			id int primary key auto_increment,
			name varchar(100),
			profession varchar(100)
	);
	
	open u_cursor;
	
	while true do
			fetch u_cursor into uname, uprofession;
			insert into tb_user_pro values(null, uname, uprofession);
	end while;
	
	close u_cursor;
end;

call loopHandlerSave(40);

存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的。

语法

CREATE FUNCTION 存储函数名称([参数列表])
RETURNS type [characteristics ...]
BEGIN
	-- SQL语句
	RETURN ...;
END;

characteristics的说明：

DETERMINISTIC：相同的输入参数总是产生相同的结果
NO SQL：不包含SQL语句
READS SQL DATA：包含读取数据的语句，但不包含写入数据的语句。

示例：计算从1累加到n的值，n为传入的参数值。

-- 存储函数
-- 从1到n的累加
create function funCalc(n int) 
returns int deterministic
begin
	declare total int default 0;
	
	while n > 0 do
		set total := total + n;
		set n := n - 1;
	end while;
	
	return total;
end;

-- 调用存储函数，并显示结果
select funCalc(100);

触发器（trigger）

触发器是与表有关的数据库对象，指在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。
使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

触发器类型

触发器类型	NEW和OLD
INSERT型触发器	NEW表示将要或者已经新增的数据
UPDATE型触发器	OLD表示修改之前的数据；NEW表示将要或已经修改后的数据
DELETE型触发器	OLD表示将要或已经删除的数据

行级触发：比如执行一条update语句，影响了5行数据，此时涉及的触发器触发5次，该触发器称为行级触发器。
语句级触发：比如执行一条update语句，不管影响了多少行数据，此时涉及的触发器触发1次，该触发器称为语句级触发器。

语法

#创建
CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl_name FOR EACH ROW -- 行级触发器
BEGIN
	trigger_stmt;
END;
#查看
SHOW TRIGGERS;
#删除
DROP TRIGGER [schema_name.]trigger_name; -- 如果没有指定schema_name，默认为当前数据库

示例

通过触发器记录tb_user表的数据变更日志，将变更日志插入到日志表user_logs中，包含增加，修改，删除；

create table user_logs(
	id int(11) not null auto_increment,
    operation varchar(20) not null comment'操作类型,insert/update/delete',
    operate_time datetime not null comment'操作时间',
    operate_id int(11) not null comment'操作的ID',
    operate_params varchar(50) not null comment'操作参数',
    primary key(`id`)
)engine=innodb default charset=utf8;

INSERT型触发器

-- 创建insert型触发器
create trigger tb_user_insert_trigger 
	after insert on tb_user for each row
begin
	insert into user_logs(id, operation, operate_time, operate_id, operate_params) values
	(null, 'insert', now(), new.id, concat('插入的数据内容为：id=',new.id,', name=',new.name,', phone=',new.phone,', email=',new.email,', profession=',new.profession));
end;

-- 查看触发器
show triggers;

-- 删除触发器
drop trigger tb_user_insert_trigger;

-- 向tb_user表中插入数据
insert into tb_user(id, name, phone, email, profession, age, gender, status, createtime) 
values(25, '二皇子', '18908823412', 'ehz@email.com', '软件工程', 23, '1', '1', now());
insert into tb_user(id, name, phone, email, profession, age, gender, status, createtime) 
values(26, '三皇子', '18908823413', 'shz@email.com', '软件工程', 22, '1', '1', now());

update型触发器

-- 修改数据触发器
create trigger tb_user_update_trigger
	after update on tb_user for each row
begin
	insert into user_logs(id, operation, operate_time, operate_id, operate_params) values
	(null, 'update', now(), new.id, 
	concat('更新之前的数据内容为：id=',old.id,', name=',old.name,', phone=',old.phone,', email=',old.email,', profession=',old.profession,
	' | 更新之后的数据内容为：id=',new.id,', name=',new.name,', phone=',new.phone,', email=',new.email,', profession=',new.profession));
end;

-- 查看触发器
show triggers;

-- 修改tb_user表中的数据
update tb_user set age = 32 where id = 25;
update tb_user set profession = '计算机科学与技术' where id = 26;
update tb_user set profession = '会计学' where id <= 5;

delete型触发器

-- 删除数据触发器
create trigger tb_user_delete_trigger 
	after delete on tb_user for each row
begin
	insert into user_logs(id, operation, operate_time, operate_id, operate_params) values
	(null, 'delete', now(), old.id, concat('删除之前的数据内容为：id=',old.id,', name=',old.name,', phone=',old.phone,', email=',old.email,', profession=',old.profession));
end;

-- 查看触发器
show triggers;

-- 删除tb_user表中的数据
delete from tb_user where id = 25;
delete from tb_user where id = 26;