Ben Forta《MySQL必知必会》 小孩子4919《MySQL是怎样使用的：从零蛋开始学习MySQL》
存储过程介绍
存储过程概念
迄今为止，使用的大多数SQL语句都是针对一个或多个表的单条语句。并非所有操作都这么简单，经常会有一个完整的操作需要多条语句才能完成。例如，考虑以下的情形。
为了处理订单，需要核对以保证库存中有相应的物品。
如果库存有物品，这些物品需要预定以便不将它们再卖给别的人，并且要减少可用的物品数量以反映正确的库存量。
库存中没有的物品需要订购，这需要与供应商进行某种交互。
关于哪些物品入库（并且可以立即发货）和哪些物品退订，需要通知相应的客户。
执行这个处理需要针对许多表的多条MySQL语句。此外，需要执行的具体语句及其次序也不是固定的，它们可能会（和将）根据哪些物品在库存中哪些不在而变化。
那么，怎样编写此代码？可以单独编写每条语句，并根据结果有条件地执行另外的语句。在每次需要这个处理时（以及每个需要它的应用中）都必须做这些工作。
可以创建存储过程。存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件，虽然它们的作用不仅限于批处理。
存储过程属于存储例程，都是对某些语句的一个封装。函数侧重于执行这些语句并返回一个值，而存储过程更侧重于单纯的去执行这些语句。
为什么使用存储过程
既然我们知道了什么是存储过程，那么为什么要使用它们呢？有许多理由，下面列出一些主要的理由。
通过把处理封装在容易使用的单元中，简化复杂的操作（正如前面例子所述）。
由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果所有开发人员和应用程序都使用同一（试验和测试）存储过程，则所使用的代码都是相同的。这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。
简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。
这一点的延伸就是安全性。通过存储过程限制对基础数据的访问减少了数据讹误（无意识的或别的原因所导致的数据讹误）的机会。
提高性能。因为使用存储过程比使用单独的SQL语句要快。
存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。
换句话说，使用存储过程有3个主要的好处，即简单、安全、高性能。显然，它们都很重要。不过，在将SQL代码转换为存储过程前，也必须知道它的一些缺陷。
一般来说，存储过程的编写比基本SQL语句复杂，编写存储过程需要更高的技能，更丰富的经验。
你可能没有创建存储过程的安全访问权限。许多数据库管理员限制存储过程的创建权限，允许用户使用存储过程，但不允许他们创建存储过程。
尽管有这些缺陷，存储过程还是非常有用的，并且应该尽可能地使用。
使用存储过程
创建存储过程
存储过程的定义语句：
CREATE PROCEDURE 存储过程名称([参数列表])BEGIN 需要执行的语句END复制
与上文中函数最直观的不同点就是，存储过程的定义不需要声明返回值类型。我们先定义一个存储过程看看：
DELIMITER $$CREATE PROCEDURE t1_operation( m1_value INT, n1_value CHAR(1))BEGIN SELECT * FROM t1; INSERT INTO t1(m1, n1) VALUES(m1_value, n1_value); SELECT * FROM t1;END $$DELIMITER ;复制
我们建立了一个名叫t1_operation的存储过程，它接收两个参数，一个是INT类型的，一个是CHAR(1)类型的。这个存储过程做了3件事儿，一件是查询一下t1表中的数据，第二件是根据接收的参数来向t1表中插入一条语句，第三件是再次查询一下t1表中的数据。
存储过程的调用
函数执行语句并返回一个值，所以常用在表达式中。存储过程偏向于执行某些语句，并不能用在表达式中，我们需要显式的使用CALL语句来调用一个存储过程：
CALL 存储过程([参数列表]);复制
我们先来准备一张t1表：
CREATE TABLE t1 ( m1 INT NOT NULL, n1 CHAR(1) DEFAULT NULL) ENGINE = InnoDB;INSERT INTO t1(m1, n1) VALUES(1, 'a');INSERT INTO t1(m1, n1) VALUES(2, 'b');INSERT INTO t1(m1, n1) VALUES(3, 'c');SELECT * FROM t1;复制
比方说我们调用一下t1_operation存储过程可以这么写：
CALL t1_operation(4, 'd');复制
只有查询语句才会产生结果集，其他语句是不产生结果集的。
查看和删除存储过程
与函数类似，存储过程也有相似的查看和删除语句，我们下边只列举一下相关语句，就不举例子了。查看当前数据库中创建的存储过程都有哪些的语句：
SHOW PROCEDURE STATUS [LIKE 需要匹配的存储过程名称]复制
查看某个存储过程具体是怎么定义的语句：
SHOW CREATE PROCEDURE 存储过程名称复制
删除存储过程的语句：
DROP PROCEDURE 存储过程名称复制
存储过程中的语句
MySQL之FUNCTION函数中使用到的各种语句，包括变量的使用、判断、循环结构都可以被用在存储过程中，这里就不再赘述了。
存储过程的参数前缀
参数类型 [IN | OUT | INOUT] 参数名 数据类型复制
可以看到可选的前缀有下边3种：
| 前缀 | 实际参数是否必须是变量 | 描述 |
|---|---|---|
| IN | 否 | 用于调用者向存储过程传递数据，如果IN参数在过程中被修改，调用者不可见。 |
| OUT | 是 | 用于把存储过程运行过程中产生的数据赋值给OUT参数，存储过程执行结束后，调用者可以访问到OUT参数。 |
| INOUT | 是 | 综合IN和OUT的特点，既可以用于调用者向存储过程传递数据，也可以用于存放存储过程中产生的数据以供调用者使用。 |
IN参数：
先定义一个参数前缀是IN的存储过程p_in：
DELIMITER $$CREATE PROCEDURE p_in ( IN arg INT)BEGIN SELECT arg; SET arg = 123;END $$DELIMITER ;复制
这个p_in存储过程只有一个参数arg，它的前缀是IN。这个存储过程实际执行两个语句，第一个语句是用来读取参数arg的值，第二个语句是给参数arg赋值。我们调用一下p_in：
SET @a = 1;CALL p_in(@a);SELECT @a;复制
CALL p_in(1);复制
OUT参数：
DELIMITER $$CREATE PROCEDURE p_out ( OUT arg INT)BEGIN SELECT arg; SET arg = 123;END $$DELIMITER ;复制
这个p_out存储过程只有一个参数arg，它的前缀是OUT，p_out存储过程也有两个语句，一个用于读取参数arg的值，另一个用于为参数arg赋值，我们调用一下p_out：
SET @b = 2;CALL p_out(@b);SELECT @b;复制
我们定义了一个变量b并把整数2赋值赋值给它，然后把它当作参数传给p_out存储过程。从结果中可以看出，第一个读取语句并没有获取到参数的值，也就是说OUT参数的值默认为NULL。在存储过程执行完毕之后，再次读取变量b的值，发现它的值已经被设置成123，说明在过程中对该变量的赋值对调用者是可见的！这也就是说：OUT参数只能用于赋值，对它赋值是可以被调用者看到的。
另外，由于OUT参数只是为了用于将存储过程执行过程中产生的数据赋值给它后交给调用者查看，那么在调用存储过程时，实际的参数就不允许是常量！
INOUT参数：
DELIMITER $$CREATE PROCEDURE p_inout ( INOUT arg INT)BEGIN SELECT arg; SET arg = 123;END $$DELIMITER ;复制
我们调用一下p_inout：
SET @c = 3;CALL p_inout(@c);SELECT @c;复制
从结果中可以看出：INOUT参数既能用于赋值，也可以被调用者看到。
需要注意的是，如果我们不写明参数前缀的话，默认的前缀是IN！
由于存储过程可以传入多个OUT或者INOUT类型的参数，所以我们可以在一个存储过程中获得多个结果，比如这样：
DELIMITER $$CREATE PROCEDURE get_price_data( OUT max_price DOUBLE, OUT min_price DOUBLE, OUT avg_price DOUBLE, s VARCHAR(100))BEGIN SELECT MAX(prod_price), MIN(prod_price), AVG(prod_price) FROM products WHERE prod_name REGEXP s INTO max_price, min_price, avg_price;END $$DELIMITER ;复制
我们定义的这个get_price_data存储过程接受4个参数，前三个参数都是OUT参数，第四个参数没写前缀，默认就是IN参数。存储过程的内容是将指定商品的最高价格、最低价格、平均价格分别赋值给三个OUT参数。在这个存储过程执行完之后，我们可以通过访问这几个OUT参数来获得相应的最高价、最低价以及平均价：
CALL get_price_data(@a, @b, @c, 'anvil');SELECT @a, @b, @c;复制
存储过程和函数的不同点
函数在定义时需要显式用RETURNS语句标明返回的数据类型，而且在函数体中必须使用RETURN语句来显式指定返回的值，存储过程不需要。
函数只支持IN参数，而存储过程支持IN参数、OUT参数、和INOUT参数。
函数只能返回一个值，而存储过程可以通过设置多个OUT参数或者INOUT参数来返回多个结果。
函数执行过程中产生的结果集并不会被显示到客户端，而存储过程执行过程中产生的结果集会被显示到客户端。
函数直接在表达式中调用，而存储过程只能通过CALL语句来显式调用。
小结
1、存储过程属于存储例程，都是对某些语句的一个封装。函数侧重于执行这些语句并返回一个值，而存储过程更侧重于单纯的去执行这些语句；
2、使用存储过程有3个主要的好处，即简单、安全、高性能。也存在编写较SQL复杂、需要设置创建存储过程的安全访问权限等缺陷；
3、存储过程的的创建、调用、查看和删除；
4、存储过程在定义参数的时候可以选择添加一些前缀：
IN参数：IN参数只能被用于读取，对它赋值是不会被调用者看到的
OUT参数：OUT参数只能用于赋值，对它赋值是可以被调用者看到的
INOUT参数：INOUT参数既能用于赋值，也可以被调用者看到
5、存储过程和函数的5个不同点。
今天主要学习了MySQL的PROCEDURE存储过程，实操的内容比较多，大家下来多加练习，存储过程是很重要的，一定要掌握。站在巨人肩膀上，每天进步一点点。
end
