存储过程

在日常开发过程中，接触到存储过程的机会或许不多，通常只需要关注业务逻辑sql就可以很好的完成工作。但在某些情况下，例如报表、数据分析等，使用存储过程，能够简化一系列的复杂sql，执行速度更快（预编译过），出错率低。综合来看，存储过程是我们应该必须掌握的一个技能。

MySQL中存储程序分为存储过程和函数，它们本质上都是存储程序。函数只能通过return来返回结果并且不能使用临时表和某些函数，所以函数使用相对较少，这里只关注存储过程的使用。

初识存储过程

创建存储过程，需要使用CREATE PROCEDURE关键字，语法如下：

CREATE PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body

针对语法中的一些内容，总结如下表：

关键词	含义	具体内容
sp_name	存储过程名称	存储过程的名称，操作存储过程时使用。
proc_parameter	参数列表	用于定义存储过程所需参数，有三种参数：IN、OUT、INOUT 。IN表示输入参数； OUT表示输出参数； INOUT既可以表示输入参数也可以表示输出参数； 语法为：[IN|OUT|INOUT] param_name type 其中type可以使用mysql所支持的所有类型。
characteristic	指定存储过程的特性	分为以下几种类型： COMMENT 'string'：注释信息； LANGUAGE SQL：说明routine_body部分是由SQL语句组成; [NOT] DETERMINISTIC：指明存储过程的结果是否确定。DETERMINISTIC表明存储过程在相同的输入有相同的输出。NOT DETERMINISTIC表明结果是不确定的。默认采用NOT DETERMINISTIC; { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }:指明子程序使用SQL语句的限制。CONTAINS SQL表明子程序包含SQL语句，但是不包含读写数据语句。NO SQL表明子程序不包含SQL语句；READS SQL DATA表明子程序包含读数据的语句。 MODIFIES SQL DATA表明子程序包含写数据的语句。默认采用CONTAINS SQL; SQL SECURITY { DEFINER | INVOKER }:指定存储过程谁有权限执行；DEFINER表明定义者才能执行。INVOKER表示拥有权限的调用者可以执行。默认采用DEFINER；
routing_body	存储过程体	具体的SQL内容，一般使用BEGIN...END来表示执行开始和结束

文字太多，不免有点困意。那就废话少说，开始写我们的第一个存储过程，例：

假设我们现在有一个book表，表结构如下：

CREATE TABLE `book` (
  `BookID` int(5) NOT NULL AUTO_INCREMENT,
  `BookName` varchar(20) NOT NULL,
  `BookAuthor` varchar(20) NOT NULL,
  `BookNumber` int(5) DEFAULT NULL,
  `Comment` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`BookID`)
) ENGINE=InnoDB AUTO_INCREMENT=84 DEFAULT CHARSET=utf8;

假设我们现在想要计算图书的总数，使用存储过程实现如下：

create PROCEDURE BookCount()
BEGIN
    select SUM(BookNumber) from book;
END;

这是一个最简单的存储过程，既没有入参，也没有出参，其中BEGIN和END是用来限制存储过程体。存储过程调用也非常简单，如下：

call BookCount();

使用CALL关键字就能够调用存储过程。

参数的使用

上述根据存储过程的语法就可以看出，使用存储过程是可以带有入参和出参的，方便我们更好的使用。示例如下：

create PROCEDURE BookNumberByBookId(IN id INT,OUT number INT)
BEGIN
    select BookNumber INTO number from book where bookId=id;
END;

其中INTO关键字用来给变量赋值，在调用的时候，还是直接使用call关键字，例如：

call BookNumberByBookId(75,@number);
select @number;

如果想要知道出参的结果，我们可以使用select 出参变量；这里的出参结果通过@number接收。注意必须使用@符号。

变量的使用

在编写存储过程的时候，我们也可以使用变量，变量的作用域是在存储过程体（BEGIN...END）之中。合理的使用变量，可以写出易懂和更加强大的存储过程。

存储过程中使用DECLARE定义变量，语法如下：

DECLARE var_name[,varname]... date_type [DEFAULT value]；

var_name:变量名称；date_type 代表数据类型；[DEFAULT value] 用于给变量赋默认值；

例如：DECLARE param1 INT DEFAULT 10;

既然我们能够定义一个变量，那么肯定就能给变量赋值，赋值语法格式如下：

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

例如：SET param1 = 20;

除此之外，我们还可以通过查询语句赋值，例如：

select k.BookName,k.BookName INTO param1 from book k WHERE k.BookID=1;

流程控制

流程控制语句是用来控制语句的执行，类似于编程语言中的if、while等等。MySQL中的流程控制语句关键字有：IF、CASE、LOOP、LEAVE、ITERATE、REPEAT、WHILE。流程控制是存储过程中的重点，是我们必须掌握的内容。

IF语句

IF语句用来进行条件判断，根据判断的结果为TRUE|FALSE执行相应的语句，示例如下：

IF name = '张三' -- 进行条件判断 (TRUE|FALSE)
	THEN select '张三'; -- 如果TRUE，执行该逻辑
	ELSE select '陌生人';-- 否则，执行该逻辑
END IF;--结束IF语句

CASE语句

CASE也是进行条件判断，本身具备两种格式，示例如下：

-- 第一种格式
CASE name
    WHEN '张三' THEN select '张三';-- 当name=张三，执行该语句
	ELSE select '陌生人';-- 否则执行该条语句
END CASE;
-- 第二种格式
CASE 
    WHEN name='张三' THEN select '张三';
    ELSE select '陌生人';
END CASE;

LOOP、LEAVE语句

LOOP用来循环执行某些语句，不会进行条件判断。LEAVE通常和循环语句结合使用，用来退出循环。

DECLARE number INT DEFAULT 10;
incr_loop:LOOP
	SET number = number+1;
	IF number >=100 THEN LEAVE incr_loop;
	END IF;
END LOOP incr_loop;

REPEAT语句

REPEAT也是开启一个循环，只是它有条件判断，每一次循环都会对条件进行判断。

DECLARE number INT DEFAULT 10;
REPEAT
	SET number = number+1;
UNTIL number >= 100;
END REPEAT;

WHILE语句

它和REPEAT语句类似，也是创建一个带条件的循环，只是它是先判断再循环，而REPEAT语句是先循环再判断。

DECLARE number INT DEFAULT 10;
WHILE number <= 100 DO
    SET number = number+1;
END WHILE;

ITERATE语句

该语句用在循环体内，将语句执行顺序跳转到循环开头。也就是再次循环的意思。

DECLARE number INT DEFAULT 10;
incr_while:WHILE number<=100 DO
	SET number=number+1;
	IF number <= 50 THEN ITERATE incr_while;
	ELSEIF number >= 100 THEN LEAVE incr_while;
	END IF;
END WHILE incr_while;

条件的使用和处理

在存储过程执行的过程中，可能遇到问题或错误，如何处理这些状况，是继续执行下去还是退出，我们可以定义条件和处理逻辑来进行解决。

定义条件的语法：

DECLARE cond_name CONDITION FOR [cond_type]
[cond_type]：
SQLSTATE [VALUE] sqlstate_value | mysql_erroe_code

其中，cond_name代表条件名称；cond_type代表条件类型，其中cond_type分为两种类型：SQLSTATE（sqlstate_value长度为5的字符串错误码） 和 mysql_erroe_code（数值类型错误码）；

现在我们定义一个 “ERROR 1148(42000)”错误，应该如何定义呢？如下：

-- 使用SQLSTATE 
DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000';
 -- 使用mysql_error_code 
DECLARE command_not_allowd CONDITION FOR 1148;

我们已经定义好了条件，遇到这些问题如何处理呢？下面我们就来说说如何定义处理程序，语法如下：

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement

    handler_type : 错误处理方式。分为三种类型：

• CONTINUE：遇到错误不处理，继续执行；
• EXIT：遇到错误立即退出；
• UNDO（MySql暂时不支持）：遇到错误撤回之前的操作；

    condition_value代表错误类型，该错误类型有六种取值方式：

• SQLSTATE [VALUE] sqlstate_value：含有5个字符的字符串错误码；
• cond_name：定义的条件名称，例如上述 “command_not_allowed”；
• SQLWARNING：匹配所有以01开头的SQLSTATE错误码；
• NOT FOUND：匹配所有以02开头的SQLSTATE错误码；
• MySQL_error_code：匹配数值类型的错误码；
• SQLEXCEPTION：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误码；

sp_statement表示遇到所需处理的错误时，应该执行的存储过程或函数。

关于处理逻辑，我们也来一个示例，如下：

-- 采用我们上述定义的条件，遇到该条件时，程序退出并输出command_not_allowd信息
DECLARE EXIT HANDLER FOR command_not_allowd SET @info='command_not_allowd';

删除存储过程

在MySql中，如果我们想要修改存储过程的内容，是不能直接做到的。我们只能先删除存储过程，然后重新编写，或者创建新的存储过程。输出示例如下：

drop PROCEDURE IF EXISTS BookCount;

IF EXISTS不是必须的，只是为了防止存储过程不存在抛出错误。IF EXISTS子句可以用来防止错误发生，例如删表，删视图等。

总结

存储过程就是一系列SQL语句的集合，我们可以使用CALL语句调用，另外我们也可以在存储过程语句体中调用其它的存储过程，但不能在其中删除存储过程。另外，我们在使用入参和出参的时候，最好不要与数据表里的字段一样，即使是大小写不一样也不行，否则会出现奇葩的结果，类似出参结果为NULL，大家可以自行尝试。若有不对的地方，望大家不吝赐教，共同进步，谢谢。