（九）MySQL存储过程

2017-07-24 本文已影响130人黒猫

1、MySQL存储过程简介

在对数据库进行增、删、改、查等操作时，实际上输入的SQL语句会经由MySQL引擎分析语法是否正确，之后再编译成MySQL可识别的命令，最后执行该语句并将结果返回至客户端。

假如能够对该流程进行简化，省略语法分析和编译的环节，那么MySQL的执行效率将会大大提高。因此首先需要了解什么是存储过程：

存储过程是SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理。

存储过程会被储存在数据库内，可以由应用程序调用执行，而且允许用户声明变量以及进行流程控制；存储过程可以接受参数，包括输入类型的参数，或输出类型的参数，并且可以存在多个返回值。

因此使用存储过程的效率要比使用单一的SQL语句执行的效率要高，例如：编写了两条SQL的语句，MySQL会对这两条语句逐一的进行分析、编译，再逐一执行；而采用存储过程以后，只有在第一次执行时才进行语法分析和编译，以后当客户端再去调用，会直接调用编译完成的结果，通过省略再次进行语法分析和编译而提高了执行效率。

存储过程的优点：

增强SQL语句的功能和灵活性
在存储过程内可以写控制语句，从而可以完成复杂的判断以及较复杂的运算，因此具有很强的灵活性；
实现较快的执行速度
例如执行某个复杂的操作，当包含大量的sql语句时，虽然首次执行与不使用存储过程的效率无差，但以后客户端再次调用时便直接从内存中来执行编译好的结果，从而实现了更高的执行速度。
减少了网络流量
当需要通过客户端发送SQL语句来使服务器执行某些操作时，如果每次都是发送单独的SQL语句，那么通过http协议所提交的数据量相对而言会比较大；而使用存储过程则可以减少数据量，从而减少了网络流量。

2、存储过程语法结构解析

创建存储过程
CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,……]])
[characteristic ……] routine_body

proc_parameter:
[ IN | OUT | INOUT ] param_name type

COMMENT ' STRING ':
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER }

可以理解为：
　创建存储过程
　创建者，省略即为当前登录到MySQL的用户
　过程名　前置选项　参数
　特性　过程体

前置选项：

IN，表示该参数的值必须在调用存储过程时指定，在存储过程中不能被返回；
OUT，表示该参数的值可以被存储过程改变，并且可以返回；
INOUT，表示该参数在在调用存储过程时指定，并且可以被存储过程改变和返回。

关于特性：

CONTAINS SQL：包含SQL语句，但不包含读或写数据的语句；
NO SQL：不包含SQL语句；
READS SQL DATA：包含读数据的语句；
MODIFIES SQL DATA：包含写数据的语句；
SQL SECURITY { DEFINER | INVOKER }：指明谁有权限来执行。

关于过程体：

过程体由合法的SQL语句构成；
过程体可以是近乎任意的SQL语句；
过程体如果为复合结构，则使用BEGIN……END语句包含；
复合结构可以包含声明、循环、控制结构。

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

以函数VERSION()为例，创建可以返回版本信息的存储过程：

之后就可以调用该存储过程了，调用的方式有两种：

CALL sp_name([parameter[,……]])
CALL sp_name[()]
区别：如果存储过程在封装时没有参数，“()”有或没有都可以；但是当存储过程带有参数，就必须有“()”。

因此对于不带有参数的存储过程sp1而言，两种调用方式都可以：

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

创建数据表users：

此次共填入了18条记录，手动输入过于繁琐，因此提供源码，点击下载使用。：

之后需要使用带有IN类型的存储过程来删除用户指定的“id”号的用户数据，按照惯例，首先需要修改分隔符，之后创建存储过程：

单行过程体也可以使用BEGIN……END语句包含，而过程体中第一个“id”是指数据表中的字段，第二个“id”是传入存储过程的参数。
　　将分隔符改回默认的“；”，因为存储过程有参数，因此使用带有“()”的调用方式，希望删除“id”为3的用户记录，此时“神奇”的事情发生了，数据表被清空了，18条记录全部被删除：

其实出现这种情况的原因很简单，虽然我们知道过程体中两个“id”的区别，但是对于系统而言无法区分，认为两个都是字段，因此就全部删除了，这也说明要注意过程体中的参数名不能与数据表中的字段名相同，而对于存储过程而言，只能修改如下几个简单的选项：

ALTER PROCEDURE sp_name [characteristic ……]

COMMENT ' STRING ':
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER }

由于不能修改过程体，因此只能删除存储过程再重写，删除的语法结构如下：

DROP PROCEDURE [IF EXISTS] sp_name

此时就可以执行删除存储过程操作，并使用提供下载的原码将数据库记录导入了：

重新创建存储过程：

修改分隔符并查看数据表数据：

因为数据被清空的缘故，“id”编号会继续排序，此时选择删除“id”为23的用户记录：

再次搜索“id”为23的用户显示数据为空，表明已成功删除。

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

对之前的“removeUserById”这个存储过程进行升级，删除指定的“id”且返回剩余的“id”数量：

恢复分隔符并查看数据表中现有的“id”数量：

此时调用该存储过程并删除“id”为27的用户记录，另一个参数“@num”为用户变量，用来记录剩余的“id”数量，通过查询可见剩余“id”数量为16：

关于变量的相关内容，可参考（十）MySQL中的变量。

6、创建带有多个OUT类型参数的存储过程

除了可以通过指定“id”来删除用户记录之外，还可以通过例如年龄、性别等等其他字段来删除：

通过查看记录发现会有很多年龄相同的用户，因此创建一个可以根据年龄来删除用户记录的存储过程，且该过程不仅能返回剩余的用户数量，还能返回删除的记录数量。不过在此之前，先简单介绍一个能实现该存储过程的系统函数ROW_COUNT()：

该函数实际上是返回最近增加、删除、更新等操作影响的行数，例如以数据表“test”为例：

现向表中再插入两条记录后调用函数ROW_COUNT()：

此时显示被影响的行数为2，了解其功能后，再来创建存储过程：

修改分隔符，查询原数据表的总记录数为13，以及将要删除的年龄为23的用户记录数为4：

理论上执行完存储过程后，剩余的记录数应为9：

其中变量@var1为删除记录数，变量@var2为剩余记录数，可见该存储过程已成功执行。

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

存储过程实现的功能要复杂一些，而函数的针对性更强；
存储过程可以返回多个值，而函数只能有一个返回值；
存储过程一般独立的执行，而函数可以作为其他SQL语句的组成部分出现。

8、MySQL存储过程的SQL语句汇总：

创建存储过程
CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,……]])
[characteristic ……] routine_body

调用存储过程
CALL sp_name([parameter[,……]])
CALL sp_name[()]
区别：如果存储过程在封装时没有参数，“()”有或没有都可以；但是当存储过程带有参数，就必须有“()”。
简单修改存储过程
ALTER PROCEDURE sp_name [characteristic ……]
COMMENT ' STRING ':
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER }
删除存储过程
DROP PROCEDURE [IF EXISTS] sp_name

版权声明：欢迎转载，欢迎扩散，但转载时请标明作者以及原文出处，谢谢合作！             ↓↓↓