MySQL存储过程和函数(一)

1 什么是存储过程和函数

存储过程和函数是事先经过编译并存储在数据库中的⼀段 SQL 语句的集合，调⽤存储过程和函数可以简化应⽤开发⼈员的很多⼯作，减少数据在数据库和应⽤服务器 之间的传输，对于提⾼数据处理的效率是有好处的。
存储过程和函数的区别在于函数必须有返回值，⽽存储过程没有，存储过程的参 数可以使⽤IN、OUT、INOUT类型，⽽函数的参数只能是IN类型的。如果有函数从其 他类型的数据库迁移到MySQL，那么就可能因此需要将函数改造成存储过程。

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

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) 

上⾯是在使⽤的样例数据库中创建的⼀个过程，该过程⽤来检查film_id和store_id 对应的inventory是否满⾜要求，并且返回满⾜要求的inventory_id以及满⾜要求的记录     数。
通常我们在执⾏创建过程和函数之前，都会通过“DELIMITER $$”命令将语句的 结束符从“;”修改成其他符号，这⾥使⽤的是“$$”，这样在过程和函数中的“;”就不会被 MySQL解释成语句的结束⽽提⽰错误。在存储过程或者函数创建完毕，通 过“DELIMITER   ;”命令再将结束符改回成“;”。
可以看到在这个过程中调⽤了函数    inventory_in_stock()，并且这个过程有两个输
⼊参数和⼀个输出参数。下⾯可以通过调⽤这个过程来看看返回的结果。 如果需要检查  film_id=2  store_id=2对应的  inventory的情况，则⾸先⼿⼯执⾏过程
中的SQL语句，以查看执⾏的效果：

SELECT inventory_id
-> FROM inventory
-> WHERE film_id = 2
-> AND store_id = 2
-> AND inventory_in_stock(inventory_id);
+--------------+

| inventory_id |
+--------------+
| 10 |
| 11 |
+--------------+
2rows in set (0.00 sec)
满⾜条件的记录应该是两条，inventory_id分别是10和11。如果将这个查询封装在 存储过程中调⽤，那么调⽤过程的执⾏情况如下 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;
+------+
| @