一、简介
存储过程与用户自定义函数类似,是放在服务器端数据库中的子程序,是经编译过的能执行特定功能的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语句告知上层开发人员,每一条最终数据对应数据库中的哪一个表、哪一个列,或是经过简单的计算就可以由数据库中的哪一个表、哪一个列,得到最终数据。
这样不仅可以验证存储过程的正确性,而且提供给上层开发人员使用存储过程的接口。