MySQL存储过程详解及应用

一、存储过程概述

SQL 语句需要先编译然后执行， 而存储过程（ Stored Procedure） 是一组为了完成特定功能的 SQL 语句集， 经编译后存储在数据库中， 用户通过指定存储过程的名字并给定参数（ 如果该存储过程带有参数） 来调用执行它。

存储过程是可编程的函数， 在数据库中创建并保存， 可以由 SQL 语句和控制结构组成。 当想要在不同的应用程序或平台上执行相同的函数， 或者封装特定功能时， 存储过程是非常有用的。 数据库中的存储过程可以看做是对编程中面向对象方法的模拟， 它允许控制数据的访问方式。

MySQL 5.0 以前并不支持存储过程， MySQL 5.0 开始支持存储过程， 大大提高数据库的处理速度， 同时也提高了数据库编程的灵活性。

二、为什么需要存储过程？

1、增强语言的功能和灵活性
存储过程可以用控制语句编写， 有很强的灵活性， 可以完成复杂的判断和较复杂的运算。

2、标准组件式编程
存储过程被创建后， 可以在程序中被多次调用， 而不必重新编写该存储过程的 SQL 语句。 而且数据库专业人员可以随时对存储过程进行修改， 对应用程序源代码毫无影响。

3、较快的执行速度
如果某一操作包含大量的 Transaction-SQL 代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。
因为存储过程是预编译的。 在首次运行一个存储过程时查询， 优化器对其进行分析优化， 并且给出最终被存储在系统表中的执行计划。 而批处理的 Transaction-SQL 语句在每次运行时都要进行编译和优化， 速度相对要慢一些。

4、减少网络流量
针对同一个数据库对象的操作（ 如查询、 修改） ， 如果这一操作所涉及的 Transaction-SQL 语句被组织进存储过程， 那么当在客户计算机上调用该存储过程时， 网络中传送的只是该调用语句， 从而大大减少网络流量并降低了网络负载。

5、作为一种安全机制来充分利用
通过对执行某一存储过程的权限进行限制， 能够实现对相应的数据的访问权限的限制， 避免了非授权用户对数据的访问， 保证了数据的安全。

三、语法

1）创建存储过程

CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体>
[过程参数[,…] ] 格式
[ IN | OUT | INOUT ] <参数名> <类型>

2）调用存储过程

CALL PROCEDURE <过程名> ( [过程参数[,…] ] )

3）删除存储过程

DROP PROCEDURE <过程名>

四、参数类型

存储过程根据需要可能会有：输入、输出、输入输出参数，如果有多个参数用“，”分割开。
MySQL存储过程的参数用在存储过程中的定义，共有3种参数类型：IN，OUT，INOUT。

IN： 参数的值必须在调用存储过程时指定， 在存储过程中修改该参数的值不能被返回， 为默认值
OUT： 该值可在存储过程内部被改变， 并可返回
INOUT： 调用时指定， 并且可被改变和返回

1）过程名

存储过程的名称， 默认在当前数据库中创建。 若需要在特定数据库中创建存储过程， 则要在名称前面加上数据库的名称， 即 db_name.sp_name。 需要注意的是， 名称应当尽量避免选取与 MySQL 内置函数相同的名称， 否则会发生错误。

2）过程参数

存储过程的参数列表。 其中， <参数名>为参数名， <类型>为参数的类型（ 可以是任何有效的MySQL 数据类型） 。 当有多个参数时， 参数列表中彼此间用逗号分隔。 存储过程可以没有参数（ 此时存储过程的名称后仍需加上一对括号） ， 也可以有 1 个或多个参数。

MySQL 存储过程支持三种类型的参数， 即输入参数、 输出参数和输入/输出参数， 分别用 IN、OUT 和 INOUT 三个关键字标识。

其中，

• 输入参数可以传递给一个存储过程
• 输出参数用于存储过程需要返回一个操作结果的情形
• 输入/输出参数既可以充当输入参数也可以充当输出参数。

需要注意的是， 参数的取名不要与数据表的列名相同， 否则尽管不会返回出错信息， 但是存储过程的 SQL 语句会将参数名看作列名， 从而引发不可预知的结果。

3）过程体

存储的主题部分，也称为存储过程体，包含在过程调用的时候必须执行的SQL语句。这个部分以关键字BEGIN开始，以关键字END结束。若存储过程体中只有一条SQL语句，则可以省略BEGIN-END标志。

五、存储过程实例

注意： mysql 默认是以 ; 作为结束执行语句， 与触发器中需要的分行起冲突， 为解决此问题可用 DELIMITER， 如： DELIMITER //，可以将结束符号变成//。 当触发器创建完成后， 可以用 DELIMITER ;来将结束符号变成。

1、IN参数示例

1）存储过程

ysql> DELIMITER //
mysql> CREATE PROCEDURE in_param(IN p_in int)
-> BEGIN
-> SELECT p_in;
-> SET p_in=2;
-> SELECT p_in;
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;

2）调用

mysql> SET @p_in=1;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL in_param(@p_in);
+------+
| p_in |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
+------+
| p_in |
+------+
| 2 |
+------+
1 row in set (0.00 sec)

2、OUT参数示例

1）存储过程

mysql> DELIMITER //
mysql> CREATE PROCEDURE out_param(OUT p_out int)
-> BEGIN
-> SELECT p_out;
-> SET p_out=2;
-> SELECT p_out;
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;

2）调用

mysql> SET @p_out=1;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL out_param(@p_out);
+-------+
| p_out |
+-------+
| NULL |
+-------+
1 row in set (0.00 sec)
+-------+
| p_out |
+-------+
| 2 |
+-------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @p_out;
+--------+
| @p_out |
+--------+
| 2 |
+--------+
1 row in set (0.00 sec)

3、INOUT参数示例

1）存储过程

mysql> DELIMITER //
mysql> CREATE PROCEDURE inout_param(INOUT p_inout int)
-> BEGIN
-> SELECT p_inout;
-> SET p_inout=2;
-> SELECT p_inout;
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;

2）调用

mysql> SET @p_inout=1;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL inout_param(@p_inout) ;
+---------+
| p_inout |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)
+---------+
| p_inout |
+---------+
| 2 |
+---------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @p_inout;
+----------+
| @p_inout |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)

建议：输入值用in参数，输出值用out参数，inout参数尽量少用。