一、存储过程和函数概述
1.1 什么是存储过程和函数
存储过程（Stored Procedure）和函数（Stored Function）是在数据库中定义一些完成特定功能的 SQL 语句集合，经过编译后存储在数据库中。存储过程和函数中可以包含流程控制语句以及各种 SQL 语句。他们可以接受参数、输出参数、返回单个或多个结果。
1.2 存储过程的优点
在 MySQL 中使用存储过程，而不是用存储在客户端计算机本地的 SQL 程序，相比有以下几点优点。
（1）存储过程增强了 SQL 语言的功能和灵活性。存储过程可以使用流程控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的计算。
（2）存储过程允许标准组件是编程。存储过程被创建后，可以在程序中被多次调用，而不用重新编写该存储过程的 SQL 语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。
（3）存储过程能实现较快的执行速度。如果某一操作包含大量的 SQL 代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多，因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被储存在系统表中的执行计划。而批处理的 SQL 语句在每次运行时都要进行编译和优化，速度相对要慢。
（4） 存储过程能够减少网络流量。针对同一个数据库对象的操作（比如查询、修改），如果这一操纵所涉及的 SQL 语句被组织成存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句从而大大减少了网络流量并降低了网络负载。
（5）存储过程可被当作一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制，能够实现对响应的数据的访问权限的限制，避免了非授权用户对数据的访问。
二、创建存储过程和函数
在MySQL中，创建存储过程和函数必须具有 CREATE ROUTINE 权限，并且 ALTER ROUTINE 和EXECUTE 权限被自动授予它的创建者。
2.1 创建存储过程
使用 CREATE PROCEDURE 语句创建存储过程，语法如下所示。
create procedure procedure_name ([proc_parameter[,...]])
[characteristic[,...]]
Routine_body
其中，procedure_name是存储过程的名称；proc_parameter：存储过程的参数列表，其形式如下：
[IN | OUT | INOUT]param_name type
其中，IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出，默认为IN。param_name表示参数名称。Type表示参数的类型。可以声明一个或多个参数。
Routine_body是包含在存储过程中的 SQL 语句块，可以用 BEGIN...END 来表示 SQL 代码的开始与结束。
characteristic该参数有多个取值，其取值说明如下。
a. LANGUAGE SQL：说明sql_statements部分是由 SQL 语言的语句组成，这也是数据库系统默认的语言。
其他类型后续再补充
例：创建一个存储过程，从数据库gradem的student表中检索出所有籍贯是青岛的学生的学号、姓名、班级号及家庭住址等信息。
mysql>USE gradem; mysql>delimiter // mysql>create procedure:proc_stud() ->reads sql data ->begin -> select sno,sname,classno,saddress from student -> where saddress like '%青岛%' order by sno; ->end// Query OK, 0 rows affected mysql>delimiter ;
执行存储过程 "proc_stud"，返回所有青岛籍的学生信息。
MySQL 中默认语句结束符为分号（;）。存储过程中的 SQL 语句需要分号来结束。为了避免冲突，首先用"DELIMITER//"将 MySQL 的结束符设置为 //。最后再用"DELIMITER;"将结束符恢复成分号。
例：创建一个名为 num_sc 的存储过程，统计某位同学的考试门数，代码如下。
mysql>delimiter // mysql>create procedure num_sc ( in tmp_sno char(10), out count_num int ) ->reads sql data ->begin -> select count(*) into count_num from sc -> where sno=tmp_sno; ->end// Query OK, 0 affected mysql>delimiter ;
上述存储过程中，输入变量为 tmp_num，输出变量为account_num，SELECT 语句用 ACCOUNT(*)计算某位同学的考试门数，最后将计算结果存入 account_num中。
2.2 创建存储函数
使用 CREATE FUNCTION 语句创建。在 MySQL 中，存储函数的使用方法与 MySQL 内部函数的使用方法是一样的。换而言之，用户自己定义的存储函数与 MySQL 内部函数是一个性质的。区别在于，存储函数是用户定义的，MySQL 内部函数是开发者定义的。
在 MySQL 中，创建存储函数的基本语法如下。
create function func_name ([func_parameter[,...]])
returns type
[characteristic[,...]]
Rountine_body
其中各参数含义如下：
（1）func_name：存储函数名称。
（2）func_parameter：存储函数中的参数列表。其形式与存储过程相同，在此不再赘述。
（3）returns type：指定返回值的类型。
（4）Routine_body：是包含在存储函数中的 SQL 语句块，可以用 BEGIN...END 来表示 SQL 代码的开始与结束。
（5）characteristic：指定存储函数的特性，该参数的取值与存储过程的取值一样。
例：创建一个名为funct_name的存储函数返回某班级的辅导员姓名
mysql> delimiter && mysql> create function func_name (class_no varchar(8)) -> returns varchar(8) -> begin -> return ( select header from class -> where classno=class_no); -> end && Query OK, 0 rows affected mysql> delimiter ;
上述代码中，该函数的参数为 class_no，返回值为 varchar 类型。 select 语句从 class 表查询 classno 值等于 class_no 的记录，并将该记录的 header 字段的值返回。执行结果显示，存储函数已经创建成功。该函数的使用和 MySQL 内部函数的使用方法一样。
指定参数为 IN、0UT 或 INOUT 只对 procedure 合法，function 中总是默认为 IN 参数。 returns 子句只能对 function 做指定，对函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个 return value 语句。
2.3 调用存储过程和函数
在 MySQL 系统中，因为存储过程和数据库相关，如果要执行其他数据库的存储过程，需要打开相应的数据库或指定数据库名称。存储函数的调用与 MySQL 内部函数的调用方式相同。
使用 CALL 命令调用存储过程，语法格式如下：
call [dbname.]spname ([parameter[,...]]);
其中，dbname 是数据库名称，默认为当前数据库。sp_name 是存储过程的名称，parameter是指存储过程的参数。
例：调用存储过程 name_sc
mysql> call num_sc ('2020030101', @num); # 调用存储过程 Query OK, 0 rows affected mysql> select @num; # 查询返回结果
例：调用存储函数 func_teater
mysql> select func_teater('李斯'); # 调用存储函数
三、管理存储过程和函数
3.1 查看存储过程和函数
查看存储过程的状态信息，可以使用 SHOW STATUS 语句或 SHOW CREATE 语句来查看，也可以通过查询 information_schema 数据库下的 Routines 来查看存储过程和函数的信息。
（1）利用 SHOW STATUS 语句查看。
show {procedure | function} STATUS [LIKE 'PATTERN'];
例：查询名为bum_sc 存储过程的状态
show procedure status like 'num_sc';
（2）利用 SHOW CREATE 语句查看。
show create [procedure | function] sp_name; # sp_name表示存储过程或函数的名字
SHOW STATUS 语句只能查看存储过程或函数是操作哪一个数据库、存储过程或函数的名称、名称、类型、谁定义的、创建和修改时间、字符编码等信息。但是，这个语句不能查询存储过程或函数的具体定义。如果需要查询详细定义，需要使用 SHOW CREATE 语句。
（3）从information_schema.Routines 表中查看。
存储过程和函数的信息储存在Information数据库下的 Routines 表中。
select * from information_schema.Routines where routin_name = 'sp_name'
3.2 修改存储过程和函数
修改存储过程和函数是指修改已经定义好的存储过程和函数。MySQL 中通过 ALTER PROCEDURE 语句来修改存储过程。通过 ALTER FUNCTION 语句来修改存储函数。
alter [procedure | function] sp_name [characteristic ...];
characteristic 参数指定存储函数的特性，可能的取值与创建存储过程的参数说明相同。
例：修改存储过程 num_sc 的定义。将读写权限改为 modifies sql data，并指明调用者可以执行，代码如下。
mysql> alter procedure num_sc -> modifies sql data -> sql security invoker; Query OK, 0 affected
3.3 删除存储过程和函数
可以使用 DROP PROCEDURE 语法从当前的数据库删除用户定义的存储过程和函数。
drop [procedure | function] [if exists] sp_name;
if exists子句是 MySQL 扩展，如果存储过程或函数不存在，它可以防止发生错误，产生一个用 SHOW WARNING查看的警告。
结束！