MySQL的存储过程和函数

存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之前的传输，对于提高数据处理的效率是有好处的。

存储过程和函数的区别在于函数必须有返回值，而存储过程没有，存储过程的参数可以使用IN、OUT、INOUT类型，而函数的参数只能是IN类型的。如果有函数从其他类型的数据库迁移到MySQL，那么就可能因此需要将函数改造成存储过程。

创建、修改存储过程或者函数

在对存储过程或函数进行操作时，需要首先确认用户是否具有相应的权限。例如，创建存储过程或者函数需要CREATE ROUTINE权限，修改或者删除存储过程或者函数需要ALTER ROUTINE权限，执行存储过程或者函数需要EXECUTE权限。

创建存储过程或者函数的语法如下：

CREATE PROCEDURE sp_name ([proc_parameter[,…]]) [characteristic] routine_body

CREATE FUNCTION sp_name ( [proc_parameter[,…]]) RETURNS type [characteristic] routine_body

proc_parameter：[IN\OUT\INOUT] param_name type

func_parameter：param_name_type

type：Any vaild MySQL data type

characteristic：
LANGUAGE SQL
|[NOT] DETERMINISTIC|{CONTAINS SQL\NO SQL\READS SQL\MODIFIES SQL DATA}
|SQL SECURITY{DEFINED|INVOKER}
|COMMENT ‘string’

routine body：Valid SQL procedure statement or statements

修改存储过程或者函数的语法如下：

ALTER {PROCEDURE\FUNCTION} sp_name [characteristic…]

characteristic：
|[NOT] DETERMINISTIC|{CONTAINS SQL\NO SQL\READS SQL\MODIFIES SQL DATA}
|SQL SECURITY{DEFINED|INVOKER}
|COMMENT ‘string’

调用存储过程的语法如下：

CALL sp_name([parameter[…]])

MySQL的存储过程和函数允许包含DDL语句，也允许在存储过程执行提交（Commit）或者回滚（Rollback），但是存储过程不允许执行LOAD DATA INFILE语句。此外，存储过程和函数中可以调用其他的存储过程或者函数。

DELIMITER //CREATE PROCEDURE file_in_stock(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)READS SQL DATABEGIN    SELECT invertory_id    FROM inventory    WHERE film_id = p_film_id    AND store_id = p_store_id    AND inventory_in_stock(invertory_id);    SELECT FOUND_ROWS() AS p_film_count;END//DELIMITER ;

LANGUAGE SQL：说明下面过程的BODY是使用SQL语言编写，这条是系统默认的，为今后MySQL会支持的SQL外的其他语言支持的存储过程准备；

[NOT]DETERMINISTIC：DETERMINISTIC确定的，即每次输入一样输出也一样的程序，NOT DETERMINISTIC非确定的，默认是非确定的。当前，这个特征值还没有被优化存储过程使用；

{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFY SQL DATA}：这些特征值提供子程序使用数据的内在信息，这些特征值目前只是提供给服务器，并没有根据这些特征值来约束过程实际使用数据的情况。CONTAINS
SQL表示子程序不包含读或写数据的语句。NO SQL表示子程序不包含SQL语句。READS SQL DATA表示子程序包含读数据的语句，但不包含写数据的语句。MODIFIES SQL DATA表示子程序包含写数据的语句。如果这些特城值没有明确给定，默认值时CONTAINS SQL

SQL SECURITY{DEFINER|INVOKER}：DEFINER表示是以创建者的权限执行的，INVOKER表示是以调用者的权限来执行，默认是DEFINER

COMMENT ‘string’：存储过程或者函数的注释信息。

删除存储过程或者函数

一次只能删除一个存储过程或者函数，删除存储过程或者函数需要有该过程或者函数的ALTER ROUTINE权限，具体的语法如下：

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

例：DROP PROCEDURE IF EXISTS file_in_stock;

查看存储过程或者函数

查看存储过程或者函数的状态
SHOW {PROCEDURE|FUNCTION} STATUS [LIKE ‘pattern’]

查看存储过程或者函数的定义
SHOW CREATE {PROCEDURE|FUNCTION}} sp_name

通过查看information_schema.Routines了解存储过程或者函数的信息
SELECT * FROM routines WHERE ROUTINE_NAME = sp_name