（十一）MySQL笔记（存储过程、存储函数、触发器）

一、存储过程

1、介绍

存储过程是事先及格过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的许多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。
存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用。

特点：
封装，复用
可以接受参数，也可以返回数据
减少网络交互，效率提升

创建

CREATE PROCEDURE 存储过程名称（[参数列表]）
BEGIN

    --SQL语句
END;

调用

CALL 名称（[参数]）;

查看

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

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

删除：

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

注意：在命令行中，执行创建存储过程的SQL是，需要通过关键字delimiter指定SQL语句的结束符。

2、变量

1）系统变量
是MySQL服务器提供，不是用户定义的吗，属于服务区层面。分为全局变量（GLOBAL）、会话变量（SESSION）。

查看系统变量

SHOW [ SESSION|GLOBAL ] VARIABLES; --查看所有系统变量
SHOW [ SESSION|GlOBAL ] VARIABLES LIKE '......';  --可以通过LIKE模糊匹配方式查找变量
SELECT @@[SESSION|GLOBAL] 系统变量名;  --查看指定变量的值

设置系统变量

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

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

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

赋值

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

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

使用

SELECT @var_name;

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

声明：

DECLARE 变量名 变量类型 [DEFAULT...];

变量类型就是数据库字段类型： INT、BEGINT、CHAR、CARCHAR、DATE、TIME等。

赋值
SET 变量名 = 值；
SET 变量名 := 值;
SELECT 字段名 INTO 变量名 FROM 表名…;

3、关键语法

1. if
   语法：

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

2）参数
IN 该类参数作为输入，也就是需要调用时传入值
OUT 该类参数作为输出，也就是该参数可以作为返回值
INOUT 既可以作为输入参数，也可以作为输出参数

用法：

CREATE PROCEDOURE 存储过程名称（[IN/OUT/INOUT 参数名 参数类型]）
BEGIN 
		--SQL语句
END;

3）case
语法一：

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

语法二：

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

4）while
while循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句。具体语法为：

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

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

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

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

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

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

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

7）cursor(游标)
是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH和CLOSE
声明游标

DECLARE  游标名称  CURSOR  FOR  查询语句;

打开游标

OPEN 游标名称;

获取游标记录

FETCH 游标名称 INTO 变量[,变量 ]; 

关闭游标

CLOSE 游标名称;  

8）条件处理程序
定义在流程控制结构执行过程中遇到问题时相应的处理步骤。

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代码的简写

二、存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的。具体语法如下：

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

案例：

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


select	fun1(n: 100);

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

三、触发器

1、介绍

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

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

2、语法

创建:

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，默认为当前数据库。