存储过程

存储过程是事先经过编译并存储在数据库中的一段（多条）SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务之间的传输，对于提高数据处理的效率是有好处的。

存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用

特点：

  1. 封装、复用
  2. 可以接收参数，也可以返回数据
  3. 减少网络交互，效率提升

创建/调用

# 需要修改语句结束定界符
DELIMITER $$

CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
	-- SQL语句
END$$

# 把结束定位符改回来，否则一直是$$
DELIMITER ;

-- 调用
CALL 存储过程名称([参数列表]);

-- 查看存储过程
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='XX'; -- 查看指定数据库的存储过程及状态
SHOW CREATE PROCEDURE 存储过程名称; -- 查看某个存储过程的定义

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

语法

变量

系统变量

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

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

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

-- 以上操作在MySQL重启后将恢复到原始的状态
-- 想要永久的生效，那么得要配置系统文件了my.cnf

用户自定义变量

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

# 赋值
SET @varName = expr[, @varName = expr ...];		# = 可以作为赋值，也是比较值
SET @varName := expr[, @varName := expr ...];	# 推荐
# 将 字段名的结果 赋值给 @varName
SELECT 字段名 INTO @varName FROM 表名;

# 使用
SELECT @varName;

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

局部变量

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

DECLARE varName 变量类型 [DEFAULT varVal]; # default 默认值

# 赋值
SET varName := varVal;
# 将 字段名的结果 赋值给 @varName
SELECT 字段名 INTO @varName FROM 表名;

变量类型就是数据库字段类型：INT、BIGINT、CHAR、VARCHAR、DATA、TIME等

IF

IF 条件1 THEN
	-- 操作1
ELSEIF 条件2 THEN
	-- 操作2
ELSE
	-- 操作3
END IF;

参数

类型

含义

备注

IN

该类参数作为输入，也就是需要调用时传入值

默认

OUT

该类参数作为输出，也就是该参数可以作为返回值

INOUT

即可以作为输入参数，也可以作为输出参数

CREATE PROCEDURE 存储过程名称([IN / OUT / INOUT] 参数名 参数类型, [IN / OUT / INOUT] 参数名 参数类型)
BEGIN
	-- SQL语句
END;

# 使用
# 如果参数是out返回值，那么可以自己定义变量来接收，可以直接写，不需要先定义
call 存储过程名称(parm1, @parm2);
# 有了值之后就可以查到了
select @parm2;

-- 如果是 inout，那么需要先定义，再传，里面可以覆盖掉

CASE

-- 第一种格式
CASE case_value
	WHEN when_value1 THEN sql语句
	[WHEN when_value2 THEN sql语句]
	[ELSE sql语句]
END CASE;

-- 第二种格式
CASE 
	WHEN 判断条件 THEN sql语句
	[WHEN 判断条件 THEN sql语句]
	[ELSE 语句]
END CASE;

循环

WHILE

WHILE 条件 DO
  -- sql语句
END WHILE;

REPEAT

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

-- 先执行一次逻辑，然后判断逻辑是否满足，如果满足，则退出，如果不满足，则继续下一次循环
REPEAT
	-- sql语句
	UNTIL
	-- 条件
END REPEAT;
-- 类似于java的 do...while...但进入循环体的规则相反

LOOP

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

  • LEAVE: 配合循环使用，退出循环
  • ITERATE: 必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环
[BEGIN_label:]
LOOP
-- sql语句
END LOOP
[END_label];

LEAVE label; -- 退出指定标记的循环体
ITERATE label;  -- 直接进入下一次循环

游标 cursor

自定义的变量是单行单列的

游标是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH和CLOSE，其语法分别如下

-- 声明游标
DECLARE 游标名称 CURSOR FOR sql查询语句;

-- 打开游标，记得关闭
OPEN 游标名称;

--  获取游标记录
FETCH 游标名称 INTO 变量1[,变量2];

-- 关闭游标
CLOSE 游标名称;


# 读取游标的数据可以在循环中读取，读取完一个会删除一个。是从 从左到右 再从上到下 的读

条件处理程序

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

DECLARE handler_action HANDLER FOR condition_value [, condition_value] sql操作;
  • handler_action
  1. CONTINUE: 继续执行当前程序
  2. EXIT: 终止执行当前程序
  • condition_value
  1. SQLSTATE ‘sqlState_value’: 状态码，如 02000
  2. SQLWARNING: 所有以01开头的SQLSTATE代码简写
  3. NOT FOUND: 所有以02开头的SQLSTATE代码简写
  4. SQLEXCEPTION: 所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的简写

存储函数

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

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

characteristic（存储参数的特性）说明：

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