MYSQL的存储过程

背景

    近期在公司做了一次数据割接出现了一个小障碍，我需要通过关联查询出的结果进行对每张关联表修改或添加，当我进行第一次修改的时候会改变之前的查询结果，影响我的第二次修改，所以想与java中设置变量的方式一样进行对关联查询出的结果进行暂时的保存而使用的存储过程，并对存储过程进行更深入的学习。

介绍

    MYSQL的存储过程是在MYSQL5.0的版本才可以使用，它与表、视图都一样属于MYSQL的一种数据库对象，其作用相当于是对SQL的打包与公用方法的抽取。

什么时候可以使用到存储过程

    在《阿里巴巴JAVA开发手册》明确提出不允许使用存储过程，其原因是当我们将业务逻辑从代码里放到存储过程的时候会对数据库的压力非常大，而且当出现业务逻辑错误的时候也是无法找到问题出现的原因，无法调试成为一个业务开发中的很大问题。当进行对数据库的升级时，可能会导致存储过程错误，并且在分库分表的情况下也是无法使用存储过程的。
    上面描述了很多的存储过程的缺点，但是对于我们这些小公司来说做数据割接、清洗等工作的时候使用存储过程是一个很不错的选择。

语法

基础语法

delimiter $$
CREATE PROCEDURE procedure_name ( IN param INT ) BEGIN
-- 执行内容
END;
$$
delimiter;

参数部分

( IN param INT ) 

输入参数与输出参数

• IN 输入参数，可以为字面值或变量，为缺省值，调用者向过程传值，传入的是全局变量的时候，过程无法改变全局变量

• OUT 输出参数，只能是变量，过程向调用者传值，传入的是全局变量的时候，过程可以改变全局变量的值

  SELECT @abc; -- null
  delimiter $$
  CREATE PROCEDURE my_procedure ( OUT abc INT ) BEGIN
  SET abc = 1;
  END;
  $$ delimiter;
  CALL my_procedure (@abc);
  SELECT @abc; -- 1
  

• INOUT 输入输出参数，只能是变量，调用者向过程传值，过程也可以向调用者传值

  注释: 以@开头的变量为用户变量，即全局变量。

声明局部

delcare efg int default 0;

条件语句

-- if-then-else
delimiter $$
CREATE PROCEDURE my_procedure ( ) BEGIN
	DECLARE efg INT DEFAULT 1;
	IF efg > 0 THEN SELECT efg; 
	ELSE SELECT 123;
	END IF;
END;
$$ 
delimiter;
结果为: 1
-- case when
delimiter //
CREATE PROCEDURE my_procedure ( ) BEGIN
	DECLARE efg INT DEFAULT 2;
	CASE
      WHEN efg = 1 THEN SELECT 1; 
	  WHEN efg = 2 THEN SELECT 2;  -- 2
	  ELSE SELECT 3;
	END CASE;
END;// 
delimiter;
结果为: 2

普通循环语句

-- while 先判断条件在执行循环内容
delimiter //
CREATE PROCEDURE my_procedure ( ) BEGIN
	DECLARE efg INT DEFAULT 1;
	WHILE efg < 10 --循环条件
	DO SET efg = efg + 1; --执行内容
	END WHILE; --结束循环
	SELECT efg;
END;// 
delimiter;
结果为: 10

-- repeat 先执行内容在进行判断循环条件
delimiter //
CREATE PROCEDURE my_procedure ( ) BEGIN
	DECLARE efg INT DEFAULT 1;
	REPEAT SET efg = efg + 1; --执行内容
	UNTIL efg > 10 --循环条件
	END REPEAT; --结束循环
	SELECT efg;
END;
// 
delimiter;
结果为: 11

使用游标进行循环(对SELECT的结果进行循环)

DELIMITER //
CREATE PROCEDURE MY_PROCEDURE ( ) BEGIN
	-- 使用declare声明时，定义的顺序为 -->变量-->游标-->handler
	DECLARE COUNT_NUM INT DEFAULT 0; 
	DECLARE _DO INT DEFAULT 1;
	DECLARE ABC INT DEFAULT 0;
	DECLARE EFG INT DEFAULT 0;
	DECLARE CURSOR_1 CURSOR FOR (SELECT YEAR,SALE FROM STUDY.SALES); -- 声明游标 通过SELECT的查询结果进行赋值
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET _DO = 0; -- 声明处理器 当遍历游标结束时 处理器捕获NOT FOUND 并设置 _DO 为 0 终止循环 此语句下节将具体描述
	OPEN CURSOR_1; -- 开启游标
	WHILE _DO > 0 DO 
	FETCH CURSOR_1 INTO ABC,EFG; -- 每一次遍历游标的时候 将游标中的值赋到变量上面
	SET COUNT_NUM = COUNT_NUM + 1;
	END WHILE; 
	CLOSE CURSOR_1; -- 关闭游标
	SELECT COUNT_NUM;
END;
// 
DELIMITER;
结果为: 6

声明处理器（DECLARE…HANDLER）

官方文档的语法定义

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

handler_action

可选值有: CONTINUE , EXIT，UNDO

• CONTINUE 表示当SQL在执行过程中出现某种状态，程序不退出继续执行
• EXIT 表示当SQL在执行过程中出现某种状态，程序退出布局需执行
• UNDO 具体用途不详

condition_value

支持一到多个，值为SQLSTATE或MYSQL的错误编号
SQLSTATE 就是SQL的执行状态: 官方文档上有具体的编号对应的意思，这里只说几个特殊的。

• SQLSTATE 00 表示成功执行SQL
• SQLSTATE 01xx 表示SQL的一些警告，可使用SQLWARNING代替
• SQLSTATE 02xx 常使用在游标中 当游标遍历结束无数据的时候 ，可使用NOT FOUND代替
• SQLSTATE 除00 01xx 02xx 以外的可使用 SQLEXCEPTION代替

statement

复合语句，与BEGIN…END之间可写的内容相同

事务

DELIMITER //
CREATE PROCEDURE MY_PROCEDURE ( ) BEGIN
	DECLARE ERROR_FLAG INT DEFAULT 0; -- 异常标识位
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET ERROR_FLAG = 1; -- 出现异常时处理器赋值异常标志位为1
	SET AUTOCOMMIT = 0; -- 开启事务
	INSERT INTO STUDY.SALES VALUES (1998 , 2000);
	INSERT INTO STUDY.SALES VALUES (1999 , 2000);
	INSERT INTO STUDY.SALES VALUES (1998 , 2000); -- 出现主键冲突
	IF ERROR_FLAG = 1 THEN ROLLBACK; -- 进行回滚
	ELSE COMMIT; 
  END IF;
	SELECT ERROR_FLAG; -- 0
END;
// 
DELIMITER;

使用主键返回

delimiter $$
CREATE PROCEDURE n_procedure () BEGIN 
DECLARE param_id INT DEFAULT 0;
INSERT INTO xxx VALUES (NULL,xxx); -- 插入数据
SELECT LAST_INSERT_ID() INTO param_id; -- 获取最后一次插入的主键，并赋值到param_id
INSERT INTO xxx VALUES (NULL,param_id,xxx); -- 使用最后一次的主键
END; $$

删除与执行存储过程

-- 存储过程执行语句
CALL procedure_name();
-- 存储过程删除语句
DROP PROCEDURE procedure_name;