【Mysql上分之路】第十四篇：MySQL的存储过程与函数

存储过程和函数

MySQL存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程和存储函数能减少数据在数据库和应用服务器之间的传输，能提高对数据的处理效率

存储过程

存储过程是一组为了完成某个特定功能的SQL语句集合，是将常用或复杂的的工作预先用SQL语句编写好然后用一个指定名称存储起来，经过编译和优化之后存储在数据库服务器中

一个存储过程就是一个可编程的函数，一般由SQL语句和一些特殊的控制结构组成，存储过程可以用来转换数据、数据迁移、制作报表，一次执行成功就可以随时被调用以便于完成指定的功能操作，同时因为存储过程是将预先用的SQL语句进行编译和优化，所以存储过程不仅可以提高数据库的访问效率，同时也可以提高数据库使用的安全性

存储过程的优点

封装性

存储过程可以把多条SQL语句包含到一个独立的单元中，使外界看不到复杂的SQL语句，只需要调用存储过程就可以达到实现完成一个逻辑功能

增强SQL灵活性

存储过程中可以用流程控制语句编写，具有很强的灵活性，可以完成复杂的判断和较为复杂的运算

减少网络流量

由于存储过程是SQL语句经过编译和优化之后存储在服务器端运行的，并且执行速度快，所以客户端调用该存储过程时，网络中传送的调用语句，可以降低网络负载

数据库的安全性和完整性

存储过程可以通过SQL语句对某些表做相关操作，然后存储过程作为接口提供给外部程序，所以从外部是不能直接操作数据的，只能通过存储过程来操作相对应的表，因此提高了数据的安全性

创建存储过程

创建存储过程是通过关键语句CREATE PROCEDURE实现

语法格式

CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体>
[过程参数[,…] ] 格式
[ IN | OUT | INOUT ] <参数名> <类型>
语句解析：
过程名：存储过程的名称，默认在当前数据库中创建，若要在指定数据库中创建存储过程则要在前面加上数据库名称即：dataBaseName.过程名
过程参数：存储过程的参数列表，类型为参数类型，当有多个参数时，参数列表用逗号分隔
[IN | OUT | INOUT]：表示存储过程支持三种类型的参数，分别对应代表着输入参数、输出参数和输入输出参数
输入参数：表示可以传递给一个存储过程
输出参数：表示存储过程需要返回一个操作结果
输入输出参数：表示即可以充当输入参数也可以充当输出参数
过程体：存储过程的主体部分，包含在过程调用的时候必须执行的SQL语句，这部分以BEGIN开始，以关键字END结束，若只有一条SQL语句则可以省略BEGIN-END

创建存储过程之前

在执行创建存储过程和存储函数之前，需要通过DELIMITER命令将MySQL结束命令修改为其他字符

在MySQL中，服务器默认处理SQL语句的结束标志是";"，然而在存储过程中会包含多条SQL语句，这些SQL语句如果仍以分号作为语句结束符，“DELIMITER // " 命令是将SQL语句的结束标志由’;‘修改成’//’，这样使得在存储过程和存储函数中的SQL语句的”;"就不会被解释成语句的结束而提示错误

DELIMITER //
语句解析：
//：表示用户自定义的结束符,可以是其他符号@@、$$

创建存储过程之后

创建存储过程之后需要将自定义的SQL结束标志修改为默认的结束符";"，使用语句"DELIMITER ；"

查看存储过程

当存储过程和函数创建好之后要进行查看存储过程状态时需要使用语句"SHOW PROCEDURE STATUS"，查看存储过程定义时需要使用语句"SHOW CREATE"

查看存储过程状态

语句格式

SHOW PROCEDURE STATUS LIKE ‘存储过程名’
语句解析：
LIKE：表示用来匹配存储过程的关键字，不能省略
存储过程名：表示要查看状态的存储过程名称

SHOW PROCEDURE STATUS语句只能用来查看存储过程所属的数据库、存储过程名称、定义、修改时间等，但是其中Definer与Security_type字段值涉及存储过程的权限审核

Definer：表示是创建存储用户的过程

Security_type：该字段值主要为DEFINER或者INVOKER，表示为审核调用存储过程的安全审核

Security_type字段值为DEFINER则表示创建存储过程的用户需要存在、并且有调用存储过程以及访问存储过程里面涉及对象的权限，每次调用时都会对DEFINER进行审核，看用户是否存在相对应的权限

Security_type字段值为INVOKER则表示每次调用存储过程时不会去审核DEFINER字段对应的用户是否存在，只需要该用户具有执行存储过程以及访问存储过程里面对象的权限即可

查看存储过程定义

语法格式

SHOW CREATE PROCEDURE 存储过程名

查看存储过程信息

存储过程的信息都存储在information_schema数据库的Routines表中，可以根据存储过程名对该表进行存储过程信息的查询

修改存储过程

存储过程是为了完成特定逻辑的SQL集合，但是在开发过程中可能会出现逻辑需求的改变，这时一般会涉及对存储过程的修改，修改主要是通过语句"ALTER PROCEDURE"

语法格式

ALTER PROCEDURE 存储过程名 [ characters ... ]
语句解析：
CONTAINS SQL：表示存储过程中的子程序包含 SQL 语句，但不包含读或写数据的语句。
NO SQL ：表示存储过程中的子程序中不包含 SQL 语句。
READS SQL DATA ：表示存储过程中的子程序中包含读数据的语句。
MODIFIES SQL DATA：表示存储过程中的子程序中包含写数据的语句。
SQL SECURITY { DEFINER |INVOKER } ：指明谁有权限来执行。
DEFINER ：表示只有定义者自己才能够执行。
INVOKER ：表示调用者可以执行。
COMMENT 'string' ：表示注释信息

ALTER PROCEDURE 语句用于修改存储过程的某些特征。如果要修改存储过程的内容，可以先删除原存储过程，再以相同的命名创建新的存储过程；如果要修改存储过程的名称，可以先删除原存储过程，再以不同的命名创建新的存储过程

删除存储过程

存储过程创建之后是被创建在数据库服务器上，所以当存储过程被废弃的时候为了保证数据库服务器的性能需要及时对其进行删除

语法格式

DROP PROCEDURE [ IF EXISTS ] <过程名>
语句解析：
过程名：表示要进行删除的存储过程
IF EXISTS：指定这个关键字，用于防止因删除不存在的存储过程而引发的错误

调用存储过程

存储过程和存储函数都是存储在数据库服务器上，当需要使用时通过调用CALL的方式来使用这些已经定义好的存储过程函数和函数

存储过程使用CALL语句调用时需要拥有对存储过程和函数的执行权限，调用时数据库服务器将执行存储过程中的SQL语句然后将结果返回输出值

语句格式

CALL sp_name([parameter[...]]);

存储函数

存储函数和存储过程一样，都是在数据库中定义一些 SQL 语句的集合。存储函数可以通过 return 语句返回函数值，主要用于计算并返回一个值。而存储过程没有直接返回值，主要用于执行操作

语句格式

CREATE FUNCTION sp_name ([func_parameter[...]])
RETURNS type
[characteristic ...] routine_body
语句解析：
sp_name 参数：表示存储函数的名称；
func_parameter：表示存储函数的参数列表；
RETURNS type：指定返回值的类型；
characteristic 参数：指定存储函数的特性，该参数的取值与存储过程是一样的；
routine_body 参数：表示 SQL 代码的内容，可以用 BEGIN...END 来标示 SQL 代码的开始和结束。

存储函数与存储过程的创建、修改、查看、删除基本一致，只是关键字发生了变化，是将 PROCEDURE 替换成了 FUNCTION

存储过程与存储函数的区别？

含义

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

函数：是由一个或多个 SQL 语句组成的子程序，可用于封装代码以便重新使用。 函数限制比较多，如不能用临时表，只能用表变量等

条件

存储过程：可以在单个存储过程中执行一系列 SQL 语句。而且可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句

函数：自定义函数诸多限制，有许多语句不能使用，许多功能不能实现。函数可以直接引用返回值，用表变量返回记录集。但是，用户定义函数不能用于执行一组修改全局数据库状态的操作

执行

存储过程：存储过程可以返回参数，如记录集，函数只能返回值或者表对象。存储过程的参数有in，out，inout三种，存储过程声明时不需要返回类型

函数：函数参数只有in，而函数需要描述返回类型，且函数中必须包含一个有效的return语句

总结

MySQL存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程和存储函数能减少数据在数据库和应用服务器之间的传输，能提高对数据的处理效率

存储过程是一组为了完成某个特定功能的SQL语句集合，是将常用或复杂的的工作预先用SQL语句编写好然后用一个指定名称存储起来，经过编译和优化之后存储在数据库服务器中

存储函数和存储过程一样，都是在数据库中定义一些 SQL 语句的集合。存储函数可以通过 return 语句返回函数值，主要用于计算并返回一个值。而存储过程没有直接返回值，主要用于执行操作