关于MySQL5存储过程的使用心得（一）概述

关于MySQL5存储过程的使用心得（一）概述

MySQL版本5出现的存储过程意味着这个开源数据库有了真正意义上的企业特性。笔者在使用MySQL的过程中，发现关于存储过程的资料相对缺乏。相对于一些成熟的商业数据库或开源数据库如PostgreSQL等，MySQL的存储过程支持的特性较少。一方面，MySQL存储过程支持的返回数据类型较少，另一方面，存储过程中只能使用有限的控制流。

笔者开发的一个报表项目要求使用Excel对后台数据进行分析。但是，Excel的使用者缺乏编程知识。因此，笔者需要直接将数据传给前端，然后由用户用PivotalChart对数据进行透视操作。因此，笔者使用基于MySQL存储过程的方案。用户可以通过Microsoft Query直接呼叫MySQL的存储过程，查询结果可以载入到Excel的表格中。对于用户的每个查询需求，笔者都实现了相应的存储过程。这些存储过程可以携带一或多个参数。

MySQL存储过程分Function和Procedure两种，其中前者可以返回MySQL支持的数据类型，后者则可以直接改变参数（out和inout）类型。显然，这两种方式都只能返回一个标量（scalar）值，其灵活性很有限。

如果在Procedure的最后一句中使用了Select语句，其数据集将直接返回，类似一般的SQL语句。因此，笔者几乎所以实现的存储过程都使用Procedure类型，并将结果用Select返回。

但是，如何将计算结果放到表格中并用Select返回？笔者研究了若干MySQL的表格类型，发现基于Memory的表格数据可以在存储过程结束后自动销毁。因此，笔者将所有计算的中间结果全部存于基于Memory的临时表格中。当存储过程返回时，这些表格占用的内存就会被回收。

使用temporary表格的另一个好处是，表格是一个非常强大的数据结构，并且MySQL已有的SQL操作语句可以对表格中的数据进行各种操作。表格几乎可以完全替代其他编程语句中的数据结构（数组，哈希表）。从这个意义上讲，虽然MySQL的存储过程相比其他数据库实现的特性相对较少，但是，MySQL的存储过程可以实现所有我们需要的数据操作。

在笔者实现的存储过程中，大致的流程是这样的

1）声明所有要用到的变量。

2）声明所有要用到的游标。

3）声明所有要用到的temporary表格，其engine等于memory。

4）计算并将中间结果存在temporary表格中。

5）删除所有temporary表格，除了最后一个需要返回的数据所在的表格。

6）用select语句将最后一个temporary表格中的数据返回。

在（二）中笔者将用一个实例来描述整个过程。

Jack

Feb 16, 2009