04-创建、修改存储过程或者函数
+--------------+
创建、修改存储过程或者函数的语法如下：
CREATE PROCEDURE sp_name ([proc_parameter[,. .]])
[characteristic . .] routine_body
CREATE FUNCTION sp_name ([func_parameter[,. .]])
RETURNS type
[characteristic . .] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
func_parameter:
param_name type
type:
Any valid MySQL data type
characteristic:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
routine_body:
Valid SQL procedure statement or statements
ALTER {PROCEDURE | FUNCTION} sp_name [characteristic . .]
characteristic:
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
调用过程的语法如下：
CALL sp_name([parameter[,. .]])
MySQL的存储过程和函数中允许包含DDL语句，也允许在存储过程中执行提交（Commit，即确认之前的修改）或者回滚（Rollback，即放弃之前的修改），但是存储过程和函数中不允许执行LOAD DATA INFILE语句。此外，存储过程和函数中可以调用其他的过程或者函数。
下面创建了一个新的过程film_in_stock：
mysql> DELIMITER $$
mysql>
mysql> CREATE PROCEDURE film_in_stock(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)
-> READS SQL DATA
-> BEGIN
-> SELECT inventory_id
-> FROM inventory
-> WHERE film_id = p_film_id
-> AND store_id = p_store_id
-> AND inventory_in_stock(inventory_id);
->
-> SELECT FOUND_ROWS() INTO p_film_count;
-> END $$
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> DELIMITER ;
上面是在使用的样例数据库中创建的一个过程，该过程用来检查film_id和store_id对应的inventory是否满足要求，并且返回满足要求的inventory_id以及满足要求的记录数。
通常我们在执行创建过程和函数之前，都会通过“DELIMITER $$”命令将语句的结束符从“;”修改成其他符号，这里使用的是“$$”，这样在过程和函数中的“;”就不会被 MySQL解释成语句的结束而提示错误。在存储过程或者函数创建完毕，通过“DELIMITER ;”命令再将结束符改回成“;”。
可以看到在这个过程中调用了函数 inventory_in_stock()，并且这个过程有两个输入参数和一个输出参数。下面可以通过调用这个过程来看看返回的结果。
如果需要检查 film_id=2 store_id=2对应的 inventory的情况，则首先手工执行过程中的SQL语句，以查看执行的效果：
mysql> SELECT inventory_id
-> FROM inventory
-> WHERE film_id = 2
-> AND store_id = 2
-> AND inventory_in_stock(inventory_id);
+--------------+
| inventory_id |
| 10 |
| 11 |
+--------------+
2 rows in set (0.00 sec)
满足条件的记录应该是两条，inventory_id分别是10和11。如果将这个查询封装在存储过程中调用，那么调用过程的执行情况如下：
mysql> CALL film_in_stock(2,2,@a);
+--------------+
| inventory_id |
+--------------+
| 10 |
| 11 |
+--------------+
2 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> select @a;
+------+
| @a |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
可以看到调用存储过程与直接执行 SQL 的效果是相同的，但是存储过程的好处在于处理逻辑都封装在数据库端，调用者不需要了解中间的处理逻辑，一旦处理逻辑发生变化，只需要修改存储过程即可，而对调用者的程序完全没有影响。
另外，和视图的创建语法稍有不同，存储过程和函数的CREATE语法不支持使用CREATE OR REPLACE对存储过程和函数进行修改，如果需要对已有的存储过程或者函数进行修改，需要执行ALTER语法。
下面对characteristic特征值的部分进行简单的说明。
LANGUAGE SQL：说明下面过程的BODY是使用SQL语言编写，这条是系统默认的，为今后MySQL会支持的除SQL外的其他语言支持的存储过程而准备。
[NOT] DETERMINISTIC：DETERMINISTIC确定的，即每次输入一样输出也一样的程序，NOT DETERMINISTIC非确定的，默认是非确定的。当前，这个特征值还没有被优化程序使用。
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：这些特征值提供子程序使用数据的内在信息，这些特征值目前只是提供给服务器，并没有根据这些特征值来约束过程实际使用数据的情况。CONTAINS SQL表示子程序不包含读或写数据的语句。NO SQL表示子程序不包含SQL语句。READS SQL DATA表示子程序包含读数据的语句，但不包含写数据的语句。MODIFIES SQL DATA表示子程序包含写数据的语句。如果这些特征没有明确给定，默认使用的值是CONTAINS SQL。
SQL SECURITY { DEFINER | INVOKER }：可以用来指定子程序该用创建子程序者的许可来执行，还是使用调用者的许可来执行。默认值是DEFINER。
COMMENT 'string'：存储过程或者函数的注释信息。
下面的例子对比了SQL SECURITY特征值的不同，使用 root用户创建了两个相似的存储过程，分别指定使用创建者的权限执行和调用者的权限执行，然后使用一个普通用户调用这两个存储过程，对比执行的效果。
首先用root用户创建以下两个存储过程film_in_stock_definer和film_in_stock_invoker：
mysql> DELIMITER $$
mysql>
mysql> CREATE PROCEDURE film_in_stock_definer(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)
-> SQL SECURITY DEFINER
-> BEGIN
-> SELECT inventory_id
-> FROM inventory
-> WHERE film_id = p_film_id
-> AND store_id = p_store_id
-> AND inventory_in_stock(inventory_id);
->
-> SELECT FOUND_ROWS() INTO p_film_count;
-> END $$
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> CREATE PROCEDURE film_in_stock_invoker(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)
-> SQL SECURITY INVOKER
-> BEGIN
-> SELECT inventory_id
-> FROM inventory
-> WHERE film_id = p_film_id
-> AND store_id = p_store_id
-> AND inventory_in_stock(inventory_id);
->
-> SELECT FOUND_ROWS() INTO p_film_count;
-> END $$
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> DELIMITER ;
给普通用户lisa赋予可以执行存储过程的权限，但是不能查询inventory表：
mysql> GRANT EXECUTE ON sakila.* TO 'lisa'@'localhost';
Query OK, 0 rows affected (0.00 sec)
使用lisa登录后，直接查询inventory表会提示查询被拒绝：
mysql> select count(*) from inventory;
ERROR 1142 (42000): SELECT command denied to user 'lisa'@'localhost' for table 'inventory'
lisa用户分别调用film_in_stock_definer和film_in_stock_invoker：
mysql> CALL film_in_stock_definer(2,2,@a);
+--------------+
| inventory_id |
+--------------+
| 10 |
| 11 |
+--------------+
2 rows in set (0.03 sec)
Query OK, 0 rows affected (0.03 sec)
mysql> CALL film_in_stock_invoker(2,2,@a);
ERROR 1142 (42000): SELECT command denied to user 'lisa'@'localhost' for table 'inventory'
从上面的例子可以看出，film_in_stock_definer是以创建者的权限执行的，因为是root用户创建的，所以可以访问inventory表的内容，film_in_stock_invoker是以调用者的权限执行的， lisa用户没有访问inventory表的权限，所以会提示权限不足。