存储过程能力

一、简介

存储过程与用户自定义函数类似，是放在服务器端数据库中的子程序，是经编译过的能执行特定功能的SQL语句集合，它将一些复杂的对数据库表的操作集中起来，作为一个单元来处理。

存储过程可以接收和输出参数，返回执行存储过程的状态值，还可以嵌套使用。用户可以像使用函数一样重复调用这些存储过程，实现它所定义的操作。

存储过程的优点主要有：

1）存储过程只在创建时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次，所以使用存储过程可提高数据库执行速度。

2）当对数据库进行复杂操作时，可将此复杂操作用存储过程封装起来，与数据库提供的事务处理结合一起使用。

3）存储过程可以重复使用，可减少数据库开发人员的工作量。

4）安全性高，可设定只有某此用户才具有对指定存储过程的使用权。

二、基本框架

CREATE OR REPLACE FUNCTION name(ids"varchar") --定义存储过程的名字及参数

       RETURNSbool AS  --返回值类型

$BODY$  --主体

DECLARE  --声明所需要的变量

       r RECORD;

     rlt bool;

     sql "varchar";

BEGIN

SQL语句；

     return 1;

END;

$BODY$

LANGUAGE'plpgsql' VOLATILE;

三、步骤

1）需求分析。

对存储过程中需要调整转换的数据进行需求分析，确定其个数和具体内容。

（1）划分层次。

为了方便最终计算，可以把存储过程划分层次，层次结构如下：

（2）提取基础数据（中间数据）。

划分层次之后，结合原始数据，把计算最终数据需要的中间数据提取整理出来，放到一个数据库表（数据闸）中，这样，所有的最终数据的计算都从这个数据库表中取数据。

（3）划分存储过程个数和内容。

存储过程的个数要适当，不要太多，太零散，也不要太少，集成度太大的话，维护起来比较困难。

可以以具体业务模块为维度进行划分，以济钢PDM为例，其存储过程划分维度如下：

①“提取中间数据”（一个存储过程）

②“各种报表”（每种报表一个存储过程）

2）进行概要设计。

   对前面的工作进行总结，编写存储过程概要设计文档。概要设计的内容主要包括存储过程的需求、层次结构、层次划分、各层次间的关系、关键问题分析等。

3）画系统内部数据流程图。

存储过程涉及的数据存储比较复杂，写存储过程之前要画好系统内部数据流程图，可以清楚地看出数据库内部数据的流向。

4）进行详细设计。

根据画好的数据流程图，对每一个存储过程中包含的具体内容进行详细设计，编写存储过程详细设计文档。

详细文档的内容主要包括：每一个存储过程的调用接口、名称、传入参数、返回值、功能及操作内容（主要包括操作数据库的目标列名、目标表名、原列名、原列名所在的表、关键查询条件）。

5）编写代码。

思路：（1）原始数据-->基础数据

首先编写由原始数据到基础数据的存储过程，尽量把这些操作放到一个存储过程中，便于后期维护。

编写时要保证数据计算存储的正确性

（2）基础数据-->最终数据

按照详细文档中的对存储过程的设计，逐个编写计算最终数据的存储过程。并保证数据的计算存储正确无误。

4）编写上层使用文档。

上层使用文档的主要内容是一系列SQL语句，这些SQL语句告知上层开发人员，每一条最终数据对应数据库中的哪一个表、哪一个列，或是经过简单的计算就可以由数据库中的哪一个表、哪一个列，得到最终数据。

这样不仅可以验证存储过程的正确性，而且提供给上层开发人员使用存储过程的接口。