实验10 存储过程和函数
存储过程是SQL语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理。存储过程存储在数据库内，可由应用程序调用执行，而且允许用户拥有声明变量、有条件执行以及其他强大的编程功能。
同存储过程类似，存储函数被预先优化和编译并且可以作为一个单元来进行调试。它和存储过程的主要区别在于返回结果的方式。为了能支持多种不同的返回值，它比存储过程有更多的限制。
在MySQL中，创建存储过程和函数使用的语句分别是CREATE PROCEDURE和CREATE FUNCTION。使用CALL语句来调用存储过程，只能用输出变量返回值。函数可以从语句外调用（引用函数名），也能返回标量值。存储过程可以调用其他存储过程。
【实验目的】
①理解存储过程和函数的概念和功能。
②掌握创建存储过程的方法。
③掌握执行存储过程的方法。
④掌握查看、修改、删除存储过程的方法。
⑤掌握存储函数的创建、修改和删除方法。
【知识要点】
（1）存储过程的优点
①直接在数据库层运行，减少网络带宽的占用和减少查询任务执行的延迟。
②提高代码的复用性和可维护性，聚合业务规则，加强一致性并提高安全性。
③允许更快执行。存储过程只在第一次执行时需要编译且被存储在存储器内，其他次执行不必由数据引擎再编译，从而提高了执行速度。
④可作为安全机制使用。对于没有直接执行存储过程中语句权限的用户，可授予他们执行该存储过程的权限。
（2）存储过程的功能
MySQL中的存储过程与其他编程语言中的过程类似：
①可以以输入参数的形式引用存储过程以外的参数。
②可以以输出参数的形式将多个值返回给调用它的过程或批处理。
③存储过程中可以包含有执行数据库操作的编程语句，也可调用其他存储过程。
（3）创建存储过程的语法格式
CREATE
[DEFINER = user]
PROCEDURE [IF NOT EXISTS] sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
type:
Any valid MySQL data type
characteristic: {
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
}
routine_body:
Valid SQL routine statement
说明：
①proc_parameter为指定存储过程的参数列表，其中，IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出的参数；param_name表示参数名称，type为参数类型。
②characteristic为指定存储过程的特性，有以下取值：
• LANGUAGE SQL：说明routine_body部分是由SQL语句组成的，当前系统支持的语言为SQL。
• ［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。
③routine_body是SQL代码的内容，可以用BEGIN...END来表示SQL代码的开始和结束。(https://www.daowen.com)
（4）执行存储过程的语法格式
CALL sp_name ( [parameter[...]] )
存储过程是通过CALL语句进行调用的。其中，sp_name为存储过程的名称，parameter为存储过程的参数。
（5）修改存储过程的语法格式
ALTER PROCEDURE proc_name [characteristic ...]
characteristic: {
COMMENT 'string'
| LANGUAGE SQL
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
}
可以通过ALTER PROCEDURE语句修改存储过程的特性。
其中characteristic指定存储函数的特性：
①COMMENT 'string' 表示注释信息。
②CONTAINS SQL 表示子程序包含SQL语句，但不包含读或写数据的语句。
③NO SQL 表示子程序中不包含SQL语句。
④READS SQL DATA 表示子程序中包含读数据的语句。
⑤MODIFIES SQL DATA 表示子程序中包含写数据的语句。
⑥SQL SECURITY ｛ DEFINER | INVOKER ｝ 指明谁有权限来执行。
• DEFINER 表示只有定义者自己才能够执行。
• INVOKER 表示调用者可以执行。
（6）删除存储过程的语法格式
DROP { PROCEDURE } [ IF EXISTS ] sp_name
其中，sp_name为存储过程的名称；IF EXISTS子句是MySQL的扩展，如果存储过程不存在，它可以防止发生错误，产生一个用SHOW WARNINGS查看的警告。
（7）函数优点
①允许模块化程序设计。只需创建一次函数并将其存储在数据库中，以后便可以在程序中调用任意次。用户定义函数可以独立于程序源代码进行修改。
②执行速度更快。用户定义函数时无须重新解析和重新优化，从而缩短了执行时间。
③减少网络流量。某种无法用单一标量的表达式表示的复杂约束可以表示为函数。此函数可以在 WHERE子句中调用，以减少发送至客户端的数字或行数。
（8）创建函数的语法格式
CREATE
[DEFINER = user]
FUNCTION [IF NOT EXISTS] sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
func_parameter:
param_name type
type:
Any valid MySQL data type
characteristic: {
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
}
routine_body:
Valid SQL routine statement
其中，sp_name表示函数的名称；func_parameter为函数的参数；RETURNS type语句表示函数返回数据的类型；characteristic指定函数的特性，与存储过程相同。
