在ERP系统中存储过程的研讨开发

摘要:由于ERP系统是大型的管理信息系统，其数据库的安全性以及企业业务逻辑规则的保密性要求很高，基于这方面的考虑，分析了MS SQL存储过程的特性，研究了如何利用存储过程编写业务规则，以提高系统的安全性和运行效率。
关键词：ERP；存储过程；SQL Server；信息系统开发

1.引言
存储过程是数据库系统中一组预先编译好的SQL语句集合，具有很多的优点，而ERP系统对于数据库的安全性以及企业业务逻辑规则的保密性要求很高，如果在ERP系统开发过程中能够抓住存储过程的特点，充分发挥系统存储过程的作用，利用它的优点，就可以提高整个ERP系统的运行效率、安全性和可维护性。
2. ERP系统存储过程模型
ERP系统使用存储过程的模型如图2.1所示，使用该模型的好处有以下三点：
1.提高系统安全性：存储过程通过限制应用程序对数据库内部表的直接访问来保  护数据，屏蔽数据库内部复杂的关系。如果我们不能将表、字段等的一些权限授予某些用户，但是在计算过程中他们又需要这些表或字段的权限，我们只要将计算过程写成存储过程，然后将其“执行”权限授予这些用  户，用户就可得到需要的结果。虽然存储过程并不能作为维护安全性的主要方法，但却是增强系统安全性的一个主要的辅助手段。                    
    2.提高系统效率：我们通过把ERP系统中侧重于计算的任务写成存储过程的形式， 


图2.1
作者简介：王春光，男，1981.4.8出生，河南平顶山人，广西大学机械工程学院硕士研究生，研究方向为制造业信息化，e-mail：wcg810408@163.com。 


用户就可以反复调用，而不用重新编写代码，且存储过程所访问的数据就在本地，不必等待网络的传输，而客户机只需调用存储过程即可启动它的执行，待其一次执行完毕之后，再将结果返回给客户机，因而通讯量和等待时间大大减少，信息处理速度极为迅速，提高了系统运行效率。
3.提高系统的可维护性和灵活性：企业的管理规则很多，而ERP系统也是随着企业的不断变化而变化的，若将一些规则以存储过程的形式写在数据库中，修改时就只需修改相应的存储过程，或者将其删去，重新编写一个，就可很快完成规则替换，而不需要在客户端程序中重新编写、重新编译、重新链接，然后在更新数量很大的客户机，这样就大大方便了系统维护。
3.ERP系统存储过程的具体应用研究
三层分布式C/S结构ERP系统，客户端程序、应用程序服务器与数据库分布在性能相差较大的PC机和服务器上，在用户接口程序中大量使用SQL 程序访问数据库，客户端连接的数目一旦过多时，会产生无故跳回桌面的情况，且运行效率很低。因此，我们使用了很多用户存储过程来进行数据表的事务逻辑处理，但是并不是所有数据表的事务逻辑处理都用存储过程来实现，我们在使用存储过程时一般从以下几个方面来考虑。
3.1重复调用、需要一定运行效率的逻辑与运算处理
在采购管理子系统中，这样的情况很多，例如供应商资料的使用频率就非常高，在申购转采购单、申购转询价、询价单、采购单、收料单、退货单等单据资料维护中都要用到，而且供应商资料的主表和子表中的字段也很多，每次通过在客户端程序中编写SQL语句来调用就很麻烦，写成存储过程，在需要时通过存储过程名和变量名来调用，就可很快返回需要的结果集，下面程序是从厂商查询存储过程中抽取的一段。
----厂商查询存储过程
CREATE Procedure Pur_Sup_View(
@FmSupId varchar(6)=null,@ToSupId Varchar(6)=null, --厂商代号
@SupName Varchar(40)=null, --厂商全称
@FmBuyerId varchar(10)=null,
@ToBuyerId varchar(10)=null, --采购员编号
@ABCGrade varchar(10)=null, --ABC等级
--其它字段声明省略）
WITH ENCRYPTION（加密该存储过程）
As
declare @sw varchar(1000) (Pur_Supplier 厂商资料表变量)
declare @cw varchar(1000) （Pur_Compare 物料厂商资料表变量）
Set @sw=''
if ( IsNull(@FmSupId,'')<>'' ) Set @sw = @sw + ' and S.SupId >= '''+ @FmSupId +''''
if ( IsNull(@ToSupId,'')<>'' ) Set @sw = @sw + ' and S.SupId <= '''+ @ToSupId +''''
if ( IsNull(@SupName,'')<>'' ) Set @sw = @sw + ' and S.SupName like ''%'+ @SupName +'%'' '
if ( IsNull(@FmBuyerId,'')<>'' ) Set @sw = @sw + ' and S.BuyerId >= '''+ @FmBuyerId +''' '
---其它字段是否为空条件判断省略
---省略Set @cw=''这一段
---根据条件提取表中数据
exec ('Select S.SupId,S.ShortName,S.SupType,S.SupName,S.TelNo,S.FaxNo,S.Email,S.Address,S.BuyerId,S.ABCGrade,B.BuyerName,LSupType =Case S.SupType
           When 1 Then ''供应商''
           When 2 Then ''加工商''
           else '''' end
--其它一些字段及其显示情况省略
From Pur_Supplier S(nolock)
Left Join Pur_Buyer B(nolock) on  S.BuyerId=B.BuyerId ––左连接采购员资料表
Left Join Smt_User U(nolock) on S.EdtByUserId=U.UserId '––左连接登录用户资料表
+ @sw + @cw)
GO
其中，WITH ENCRYPTION这一句是将存储过程加密，即便连管理员身份都不能查看该存储过程，这样就大大加强了企业业务逻辑的安全性。
3.2需要集中管理和控制的逻辑与运算处理
在ERP系统中，各子系统之间数据的交互是非常频繁的，采用存储过程处理一些各模块通用的业务逻辑，不仅可以提高开发效率而且便于系统版本的管理和维护。例如BOM资料的展开，在采购管理中根据生产单或者订单生成采购计划时，就要对照每种产品的产品结构树的展开，再结合物料的库存情况，算出产品BOM中每种子物料的不足量，从而生成采购计划，BOM资料的展开在系统其它地方也经常用到，写成存储过程进行集中管理，可以方便每个子系统的程序调用，下面是BOM展开存储过程：
--Bom 顺展存储过程
CREATE PROCEDURE Bom_ListView (
@ItemId varchar(18),--产品编号
@PItemId Varchar(18),--主件编号
@Layer int, --当前层
@IsReturnDateSet bit  --是否初始化最终BOM展开之数量临时表)
WITH ENCRYPTION—加密该存储过程
AS
Set NoCount On  --关闭对话环境
Declare @NowItemId Varchar(18)  --当前新增的子件物料号
Declare @MaxItem Varchar(4) --当前BOM子件最大序号
Declare @NowItem Varchar(4) --当前处理的BOM子件序号
If @IsReturnDateSet = 0
begin
CREATE TABLE [dbo].[#Temp_BomOpen_Dtl] ([PItemId] [Varchar] (18), --产品编号
[LItemId] [Varchar] (18), --当前子件的上一级物料编号
[BItemId] [Varchar] (18), --子件编号
[Layer] [int])
end
Select Item Into #Temp_BomOpen From Bom_Cslst_Dtl(nolock) Where PItemId = @PItemId Order By Item
Select @MaxItem = Max(Item) From #Temp_BomOpen
Set @Layer = @Layer+1
Set @NowItem = '' 
While @NowIt