(8)Mysql存储过程

1.Mysql存储过程简介

今天我们来学习存储过程，学习了Mysql之后，我们发现会经常进行数据表的增删改查操作，当我们成功输入SQL命令以后，MYSQL的引擎首先会对我们所输入的命令进行语法分析，来查看一下我们所输入的SQL语句是否正确，如果语法正确，它再进行编译，编译成MYSQL引擎可以识别的命令，最后开始执行，并且将执行的结果返回给客户端，这就是MYSQL的执行流程。

我们思考一下，如果将MYSQL的过程简化一下，也就是说如果我们省略了这其中的语法分析以及编译环节，Mysql的执行效率就可以提高，那么我们如何来实现这个任务，就需要这个存储过程，首先我们来了解一下什么是存储过程。
存储过程是是SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理。存储过程存储在数据库内，可以由应用程序调用执行，而且允许用户声明变量以及进行流程控制，存储过程可以接收参数，可以接收输入类型的参数，也可以接收输出类型的参数，并且可以存在多个返回值。通过这个简单的介绍我们可以发现，存储过程的效率要比我们单一的执行SQL语句的效率要高，原因在于我们以前假设这两个SQL语句，Mysql的引擎会对这两个语句逐一的进行语法分析，逐一的进行编译再逐一的去执行，而我们采用了存储过程之后，只有在第一次才进行语法分析和编译，以后我们客户端再去调用直接调用编译的结果就可以了，这样就直接省略了两个环节，效率比以前要好。
存储过程的优点：
1.增强SQL语句的功能和灵活性（存储过程内 可以编写控制语句，有很强的灵活性，可以完成复杂的判断以及较复杂的运算）
2.实现较快的执行速度（如果有大量SQL语句逐句语法分析编译执行效率会很低，而存储过程第一次编译时会载入内存，之后客户端再调用存储过程的时候会直接从内存中来执行，调用第一次编译的结果）
3.减少了网络流量（单独发送SQL语句让服务器来执行的话，那么通过HTTP协议所提交的数据量较大，假设我们现在删除users表中id为3的记录，我们来想一下DELETE FROM users where id=3;，整个字符量大约要快30个，假设有一个过程是DELETE user，我们只需要调用它把id传过去就可以了，我们发现我们只需要传递存储过程的名字以及id就可以了。所以说它提交给服务器的字符数量相对较少，也减少了网络的流量。）

2.Mysql存储过程语法结构分析

创建存储过程

CREATE [DEFINER={user|CURRENT_USER}] PROCEDURE sp_name([proc_parameter[,...]]) [characteristic...] routine_bodyproc_parameter:[IN|OUT|INOUT] param_name type

与创建自定义函数的结构基本上类似，最大的不同就是参数部分了。其中DEFINER就是创建者，如果省略就是当前默认用户，当前登陆的这个mysql客户端的用户。sp_name就是存储过程的名字，它也可以带有0个到多个参数。这里我们看到参数前面可以带 in、out 或者inout，那么这三个参数所代表的含义分别是：

• IN，表示该参数的值必须在调用存储过程时指定，调用时指定且存储过程当中这个值是不能被返回的，也就是只能进不能出。
• OUT，表示该参数的值可以被存储过程改变，并且可以返回，OUT指的是输出。
• INOUT，表示该参数在调用时指定，并且可以被存储过程的过程体改变，而且可以返回给我们调用者。

characteristic 代表特性，特性的含义如图所示。

既然自定义函数有函数体，那么存储过程也有过程体。过程体的内容要求基本上和函数体的要求一样：

• 过程体由合法的SQL语句构成；
• 过程体可以是任意的SQL语句；（但实际上这个任意是加引号的任意，我们不可能通过存储过程去创建数据库或者数据表，我们所谓的任意主要指的是对记录的增删改查，以及多表的连接）
• 过程体如果为复合结构则使用BEGIN…END语句，将复合结构包含唉其中；
• 复合结构可以包含声明，循环以及控制结构；

3.Mysql创建不带参数的存储过程

下面我们就来创建一个简单的，没有参数的存储过程。假设它叫做sp1，我们用它可以来完成获取当前Mysql版本的这项功能。

CREATE PROCEDURE sp1() SELECT VERSION();

下面我们来马上调用它，调用存储过程一共有两种结构：

• CALL sp_name([parameter[,…]])
• CALL sp_name[()]

都通过CALL关键字，我们发现可以带有参数也可以不带有参数，它们俩区别是存储过程在封装的时候没有参数的话，那么小括号带有不带有都可以；如果存储过程带有参数，则小括号不能省略。
所以上面这个无参存储过程调取就有两种CALL sp1; CALL sp1();

4.Mysql 创建带有IN类型参数的存储过程

假设我们从数据表当中经常要删除记录，一般的情况下我们在删除记录的时候都是需要根据id来删除。where id=x，因为我们经常要写这个语句，所以可以把它封装成一个存储过程。id每次不固定，所以需要我们在调用的过程中进行传递，所以现在我们需要带有IN类型的参数。而根据上节课的知识我们知道即使我们函数体或者方法体目前只有一行语句，我们也可以带有begin end 语句，另外我们也要习惯性的修改Mysql默认的结束符 DELIMITER // 具体的sql语句如下

DELIMITER //CREATE PROCEDURE removeT6ById(IN id INT UNSIGNED)BEGINDELETE FROM t6 WHERE id=id;END//DELIMITER ;

然而调用之后发现所有的记录都被删除了，原因就是id重名之后过程体会认为id=id是两个字段名。这时就需要我们来修改这个存储过程。存储过程的修改和上节课我们修改当前的自定义函数相同，它只能修改这几个简单的选项，像注释，像我们当前的内容的类型等等，并不能修改我们的过程体，如果说想修改过程体，只能是先将存储过程删除然后再进行重建。
删除存储过程：DROP PROCEDURE [IF EXISTS] sp_name
重新构建：

DELIMITER //CREATE PROCEDURE removeT6ById(IN id INT UNSIGNED)BEGINDELETE FROM t6 WHERE id=id;END//DELIMITER ;

5.Mysql创建带有IN和OUT类型参数的存储过程

我们准备在t6表中删除记录并且返回剩余的记录数，那么在这当中就需要两个语句，一个是delete语句，一个是获取剩余的记录总数的语句，所以说我们就必须要添加 begin end语句。而且我们可以发现，第二个参数是需要来返回的，那么它的类型就必须是OUT类型。

DELIMITER //CREATE PROCEDURE removeUserAndReturnUserNums(IN p_id INT UNSIGNED,OUT userNums INT UNSIGNED)BEGINDELETE FROM t6 WHERE id=p_id;SELECT count(id) FROM t6 INTO userNums;END//DELIMITER ;

这时就可以测验该存储过程了，首先我们先查看一下这个表当中到底有多少记录：SELECT COUNT(id) FROM t6;之后调用存储过程，这里注意因为第二参数存储的是过程返回给我们的值，所以第二个参数我们不能传定值，只能接收这个值，既然要接收这个值我们就要传递一个变量：CALL removeUserAndReturnUserNums(5,@nums); SELECT @nums;这样就完成了，@nums就是变量，需要注意的是局部变量只在begin…end…语句块中有效，这个语句块运行完之后变量也就随之消失了，DECLARE 只能用在BEGIN ….END 之间.BEGIN..END 之间的变量,被称为是局部变量。而像我们这次用的则是用户变量，以@符号开头，除了select….insert…以外，也可以写成 SET @i=7也是可以的，用户变量是跟mysql的客户端绑定的，通过这种方法所设置的变量，只对当前用户所使用的当前客户端生效，也就是说这是我们的客户端，在我们的客户端是有效的。

6.Mysql创建多个OUT类型参数的存储过程

创建一个存储过程，这个过程可以根据用户的年龄来删除用户，这个参数应该是IN类型的参数，但是这个过程返回的值有两个，一个是要返回删除的用户数，第二个是要返回剩余的用户数，也就代表着需要三个参数，后两个则是OUT输出类型。
在这里首先介绍一个函数 ROW_COUNT();是来得到插入删除以及更新的被影响的记录总数。下面演示一下：

INSERT test(username) VALUES('A'),('B'),('C');SELECT ROW_COUNT();此时这个方法返回的结果就是3，添加的总数。UPDATE test SET username=CONCATE(username，‘--imooc’) WHERE id<=2;SELECT ROW_COUNT();此时这个方法返回的结果就是2，被跟新的总数。

删除的方法同理，接下来我们就可以进行封装我们的存储过程，封装一个自定义函数根据年龄删除记录，并且可以返回被删除的已经剩余的用户数

DELIMITER //CREATE PROCEDURE removeUserByAgeAndReturnInfos (IN p_age SMALLINT UNSIGNED,OUT deleteUsers SMALLINT UNSIGNED,OUT userCounts SMALLINT UNSIGNED)BEGINDELETE FROM users WHERE age=p_age;SELECT ROW_COUNT() INTO deleteUsers;SELECT COUNT(id) FROM users INTO userCounts;END//DELIMITER ;SELECT * FROM users;例子返回16条记录SELECT COUNT(id) FROM users WHERE age=20;例子中返回3CALL removeUserByAgeAndReturnInfos(20,@a,@b);SELECT @a,@b;

7. Mysql 存储过程与自定义函数的区别

我们用上节的自定义函数也能删除这几个记录，那么什么时候去使用存储过程，什么时候去使用自定义函数呢？

存储过程与自定义函数的区别：

• 存储过程实现的功能要复杂一些，而函数的针对性更强。实际的工作过程当中，我们很少用函数来针对表做操作，但存储过程会经常对于表做操作。
• 存储过程可以返回多个值，而函数只能有一个返回值
• 存储过程一般的独立来执行，而函数可以作为 sql 语句的组成部分来出现，就像我们内置的函数是相同的。

另外，假设和其他程序交互时，存储过程也会比通过API接口调用程序要快，因为API利用的是其他程序的引擎，然后将SQL语句转给Mysql的引擎，不如Mysql直接调用自己的引擎来的直接。所以我们可以在以后的开发中把一些常用的操作封装成存储过程，会更加有效率。
修改存储过程，只能简单的修改一些属性，并不能修改过程体，如要修改只能删除。语法结构如图：