数据库：创建和使用存储过程和存储函数

存储过程(Stored Procedure)和存储函数(Stored Function)是在数据库中定义的一些完成特定功能的SQL语句集合，其经编译后存储在数据库中。存储过程和存储函数中可包含流程控制语句及各种SQL语句。它们可以接受参数、输出参数、返回单个或者多个结果。

在MySQL中使用存储过程，而不是使用存储在客户端计算机本地的SQL程序的优点如下：

• 存储过程增加了SQL的功能和灵活性

• 存储过程允许模块化程序设计

• 存储过程能实现较快的执行速度

• 存储过程能够减少网络流量

• 存储过程可作为一种安全机制来充分利用

创建存储过程

在MySQL中，创建存储过程和存储函数必须具有CREATE ROUTINE权限，并且ALTER ROUTINE和EXECUTE权限被自动授予它的创建者。

# 创建存储过程
CREATE PROCEDURE procedure_name([proc_parameter[,...]]) [characteristic[,...]] Routine_body
procedure_name:
    [IN|OUT|INOUT]param_name type
characteristic:
    LANGUAGE SQL、[NOT]DETERMINISTIC、{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}、
    SQL SECURITY{DEFINER|INVOKER}、COMMENT'string'

# 调用存储过程
CALL [dbname.]sp_name([parameter[,...]]);

在创建存储过程时，系统默认指定CONTAINS SQL，表示存储过程中使用了SQL语句。但是，如果存储过程中没有使用SQL语句，最好设置为NO SQL。而且，最好在存储过程的COMMENT部分对存储过程进行简单的注释，以便以后在阅读存储过程的代码时理解代码。

mysql> use gradem;
Database changed
mysql> delimiter //
mysql> create procedure proc_sc()
    -> reads sql data
    -> begin
    -> select student.sno, sname, sum(degree)/count(degree) from student inner join sc 
        on student.sno=sc.sno;
    -> end //
Query OK, 0 rows affected (0.09 sec)
mysql> delimiter;
mysql> 

MySQL中默认的语句结束符为分号(?。存储过程中的SQL语句需要分号来结束。为了避免冲突，首先用“DELIMITER //”将MySQL的结束符设置为//，最后用“DELIMITER ;”将结束符恢复成分号。

创建存储函数

在MySQL中，存储函数的使用方法与MySQL内部函数的使用方法一样。两者唯一的区别在于，存储函数是用户自己定义的，而内部函数是MySQL开发者定义的。

# 创建存储函数
CREATE FUNCTION func_name([func_parameter[,...]]) RETURES type [characteristic[,...]] Routine_body

# 调用存储函数
SELECT [dbname.]func_name([parameter[,...]]);

PROCEDURE可以指定IN、OUT或INOUT类型的参数，而FUNCTION的参数类型默认为IN。RETURNS子句只能包含在FUNCTION中，它用来指定函数的返回类型，而且函数体必须包含一个RETURN value语句。

查看存储过程和存储函数

# 查看存储过程和存储函数的状态
SHOW {PROCEDURE|FUNCTION} STATUS [LIKE 'pattern'];
或
SHOW CREATE {PROCEDURE|FUNCTION} sp_name;
或
SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='sp_name';

SHOW STATUS语句只能查看存储过程或存储函数是操作哪一个数据库，以及存储过程或存储函数的名称、类型、谁定义的、创建和修改时间、字符编码等信息。但是，这个语句不能查询存储过程或存储函数的具体定义。如果就需要查看详细定义，就需要使用SHOW CREATE语句。

删除存储过程和存储函数

DROP {PROCEDURE|FUNCTION} [IF EXISTS] sp_name;

IF EXISTS子句是MySQL的扩展，如果存储过程或存储函数不存在，则它可以防止发生错误，产生一个用SHOW WARNINGS查看的警告。如果另一个存储过程调用某个已被删除的存储过程，则MySQL将在执行调用进程时显示一条错误消息。

（最近更新：2019年09月03日）