(九)MySQL存储过程


1、MySQL存储过程简介

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

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

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

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

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

存储过程的优点:

  1. 增强SQL语句的功能和灵活性
    在存储过程内可以写控制语句,从而可以完成复杂的判断以及较复杂的运算,因此具有很强的灵活性;
  2. 实现较快的执行速度
    例如执行某个复杂的操作,当包含大量的sql语句时,虽然首次执行与不使用存储过程的效率无差,但以后客户端再次调用时便直接从内存中来执行编译好的结果,从而实现了更高的执行速度。
  3. 减少了网络流量
    当需要通过客户端发送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 }:指明谁有权限来执行。

关于过程体:

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

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、存储过程与自定义函数的区别

  1. 存储过程实现的功能要复杂一些,而函数的针对性更强;
  2. 存储过程可以返回多个值,而函数只能有一个返回值;
  3. 存储过程一般独立的执行,而函数可以作为其他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


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

推荐阅读更多精彩内容