MySQL存储过程

1.视图

1.1视图介绍

视图是一种虚拟存在的表，视图中的行和列来自于查询定义的SQL，视图中的数据是在使用时动态生成的，视图只保存了逻辑SQL，并不保存查询数据的结果集。只有当视图中的数据行和原表中的数据一一对应的时候才能进行更新，否则会更新失败。

1.2视图的创建语法

ceate [or replace] view 视图名称 AS select语句 [ with [cascaded]|local]  check option]

1.3 查看创建视图的语法

SHOW CREATE VIEW 视图名称;

1.4删除视图

DROP VIEW 视图名称;

1.5 修改视图

1.ceate [or replace] view 视图名称 AS select语句 [ with[cascaded]|local] check option];
2. alter view 视图名称 AS select语句 [ with[cascaded]|local] check option];

1.6 with option cascaded

当使用 with cascaded check option 的时候，往视图中插入数据会判断插入的数据是否符合视图的查询条件，如果视图存在依赖关系，当某一个视图使用cascaded选项，则此视图依赖的视图都会进行条件判断

ceate [or replace] view 视图名称 AS select语句  with cascaded check option;

1.7 with option local

当使用 with cascaded check local的时候，往视图中插入数据会判断插入的数据是否符合视图的查询条件，如果视图存在依赖关系，当某视图使用local选项，则此视图会进行条件判断，通过后，检查其依赖的视图是否有check选项，有则检查，没有则不检查。

ceate [or replace] view 视图名称 AS select语句  with local check option;

2.存储过程

2.1 存储过程介绍

存储过程就是将很多SQL片段进行封装，存储在数据库服务器中，减少SQL在应用之间传输带来的开销，存储过程的优点是提升程序的灵活性，可以直接修改存储过程，而不用重新发布应用程序，存储过程也有缺点，缺点就是存储过程出现问题很难进行排查，无法定位问题出现在哪儿。

2.2 存储过程的创建和调用

参数类型：

类型	含义
IN	作为输入，调用时需要传入值
OUT	作为输出，该参数作为返回值
INOUT	既可以输入参数，也可以输出参数

1.创建存储过程格式：
    
CREATE PROCEDURE 存储过程名称( IN 参数名称1 参数数据类型， OUT 参数名称2 参数数据类型,  INOUT 参数名称3 参数数据类型 )
BEGIN
    SQL语句...
END;

2.存储过程的调用：
CALL 存储过程名称(输入参数，@输出参数)	

2.3 查看和删除存储过程

查看:SHOW CREATE PROCEDURE 存储过程名称;

SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = '数据库名称' AND SPECIFIC_NAME='存储过程名称';

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

如果在命令行中执行需要通过 DELIMITER 关键字指定SQL语句分隔符

2.4 存储过程变量

2.4.1 系统变量

系统变量是MySQL内置的变量，不是用户自定义的，基于服务器层面，分为全局变量(GLOBAL)和会话变量(SESSION),每次和数据库连接一次就是一次新的会话。

查看系统变量：

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

通过LIKE关键字模糊匹配变量： SHOW [SESSION|GLOBAL] VARIABLES LIKE ...;

查看指定变量的值： SELECT @@[SESSION|GLOBAL].系统变量名称; (如果不指定SESSION或GLOBAL默认是SESSION)

设置系统变量：

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

2.4.2 用户自定义变量

用户字段变量是根据用户自定义的变量，变量不用提前声明，在使用的时候直接用 @变量名,作用域为当前会话连,如果这个变量没有声明直接使用，并不会报错，只是值是NULL。

为用户自定义变量进行赋值:

SET @变量名=值;
SET @变量名:=值;

SELECT 字段名 INTO @变量名 FROM 表名;
SELECT @变量名:=值;

2.4.3 局部变量

具备变量使用之前需要先声明，在begin和end这个范围之内进行使用。

局部变量的声明：

局部变量的语法声明:

DECLARE 变量名 变量类型;

DECLARE 变量名 变量类型 DEFAULT 值;

局部变量的赋值：

SET 变量名=值;

SET 变量名:=值;

SELECT 字段名 INTO 变量名 FROM 表名;

2.5 IF

IF 条件1 THEN
		代码1
ELSEIF 条件2 THEN #可选		
		代码2
ELSE #可选				
		代码3
END IF;

2.6 case

CASE 
		WHEN 表达式1 THEN 
            代码1
		WHEN 表达式2 THEN 
            代码2
		.....
		ELSE
			代码3
END CASE;

2.7 while

先满足条件，然后再执行循环体

WHILE 条件 DO
    SQL逻辑代码
END WHILE;

2.8 repeat

先执行一次逻辑，然后判定逻辑是否满足，如果满足则退出，否则继续

REPEAT
	SQL逻辑代码
	UNTIL 条件
END REPEAT;

2.9 loop

loop实现简单的循环，如果不在sql逻辑中增加退出循环的条件，可以用其来实现简单的死循环，loop可以配合以下两个语句使用

1. leave ：配合循环使用，退出循环
2. iterate ：必须用在循环中，作用continue相同，跳过当前循环，进入下一次循环

标记名:LOOP
	SQL逻辑代码
    LEAVE LABLE;   #退出指定标记的循环体
    ITERATE LABEL;  #直接进入下一次循环
END LOOP 标记名;

2.10 游标的使用

声明游标：
		declare 游标名 cursor for 查询语句;
打开游标：				#使用游标之前需要先打开游标
		open 游标名;
获取游标记录：
		fetch 游标名 into 变量;
关闭游标：
		close 游标名; 

案例：将log_info_表的数据拷贝到log_info_copy；

CREATE  PROCEDURE `procedure_demo`( )
BEGIN

	# 声明退出标识
	DECLARE done INT DEFAULT 0;
	
	# 声明表结构相关数据
	DECLARE varId INT(11) DEFAULT 0;
	DECLARE varName VARCHAR(255) DEFAULT '';
  DECLARE varMsg VARCHAR(255) DEFAULT '';
	DECLARE varDate datetime ;



	#声明游标
	DECLARE	logCursor CURSOR FOR SELECT id AS varId,name AS varName ,msg AS varMsg,date AS varDate FROM log_info;
	

	#当没有数据抛出异常的时候关闭游标
	DECLARE EXIT HANDLER FOR  SQLSTATE '02000' CLOSE logCursor;
	
	#打开游标
	OPEN logCursor;
	#使用循环从游标中获取出数据
   WHILE TRUE DO	
	  # 从表中查询出数据
		FETCH logCursor INTO varId,varName,varMsg,varDate;
		# 将取出来的数据插入到新表中
		INSERT INTO log_info_copy(`id`,`name`,`msg`,`date` )VALUE(varId,varName,varMsg,varDate );
		
	END WHILE ;
	#关闭游标
	CLOSE logCursor;

END

2.11 存储过程异常处理

DECLARE handler_action HANDLER FOR condition_value   需要执行的操作  ;

handler_action：
-- CONTINUE：执行执行当前程序
-- EXIT:退出当前程序

condition_value:

SQLSTATE sql_state_value:状态码 如02000
SQLWARNING: 所有01开头的SQLSTATE代码的简写
NOT FOUND: 所有02开头的SQLSTATE代码的简写
SQLEXCEPTION:所有没有被SQLWARNING和NOT FOUND代码的简写

mysql官网状态码解释：https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html

3. 存储函数

整体的语法结构和流程控制都是和存储过程差不多一样的,存储函数的参数只能是in类型的，存储函数必须有返回值

3.1 存储函数语法

CREATE FUNCTION 存储函数名(参数列表) RETURNS TYPE [CHARACTERISTIC...]
BEGIN
    SQL语句
		RETURN ...;
END;

characteristic说明：
				deterministic：相同的输入参数总是产生相同的结果
				no sql 		：不包含sql语句
				reads sql data： 包含读取数据的语句，但不包含写入数据的语句

3.2 存储函数的使用

存储函数的使用和mysql系统的函数使用方法是一样的。

select 存储函数名(10);

4.触发器

4.1触发器的介绍

触发器是和数据库表有关联的，是数据库表有修改，删除，新增的时候。之前或之后，我们可以监听到这个数据变化。

触发器类型	new和old
inset触发器	new表示将要新增的数据或者已经更新的数据
update触发器	old表示修改之前的数据，new表示修改后的数据
delete触发器	old表示将要删除的数据

4.2 触发器的创建语法

CREATE TRIGGER 触发器名称 [BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON 表名称 FROM EACH ROW
BEGIN
   # OLD,NEW 根据这个两个关键字进行处理
   # 如果要修改当前变动的这条数据的其他字段只能使用这种方式更新  SET new.字段名 = 新值 ;
END;

4.3 触发器的删除和查看

# 查看触发器
SHOW TRIGGER 触发器名称;

# 删除触发器

DROP TRIGGER 触发器名称;

4.4 触发器案例

我们有一张表 product_info，他有一个version字段，我们想在这条数据有有变动的时候就将这个version字段+1，因为这张表的数据会定时的被别人所拉取，别人发现version字段和他本地的不一样，那么他就进行更新操作。

CREATE TRIGGER product_info_trigger AFTER  UPDATE ON product_info FROM EACH ROW
BEGIN
 SET new.version = IF(ISNULL(new.version),0,new.version)+1 ;
END;