财政专户管理问题的解决方法

财政专户管理问题的解决方法　浦发银行郑州分行　朱国强　浦发银行数据仓库分行绩效考核系统上线后，各分　行员工可以及时了解自己的营销业绩，但对部分客户经　理来讲，却产生一些问题，其原因是这些客户经理有部　分业务发生在财政专户上，这部分业绩之前采用的是人　工台账，没有进入分行绩效系统。目前，财政专户因其　自身特点在管理上有一定的难度，为此，我们使用ＳＱＬ　Ｓｅｒｖｅｒ　２００５的ＣＬＲ集成特性开发的存储过程较好的解决　了这个难题。　一、财政专户的现状　１．财政专户的特点　（１）一个账户，对应多个客户经理，但财政专户　金额很大，对每位相关的客户经理都很重要。　（２）来账分配方式多样：大量没有明确归宿的直　接划拔给营业部虚拟柜员；部分特定账户的来账要划拔　给指定的客户经理；部分特定账户的来账按比例划拔给　多个客户经理；部分特定账户的来账要保障特定某客户　经理的固定金额。　（３）按比例走账，走账发生时按各客户经理名下　的余额占账户总余额的比例进行。　（４）若某日既发生来账又发生走账，先计算走账　再计算来账。台账管理员需到核心系统中逐日查询财政　专户的流水并逐条处理。　（５）台账管理员需进行大量计算算出相关客户经　理的每日余额。　（６）部分没有明确归宿的来账可能在数刷后才有　客户经理提出应划到自己名下，台账管理员若不重新计　算则导致客户经理业绩误差，若重新计算则是噩梦。　（７）每位客户经理的业绩日均计算也较为繁琐。　２．人工台账管理缺陷　（１）台账管理员工作量巨大。　（２）客户经理不能及时了解自己的业绩及变化情　况，若要查询须打电话到台账管理员，其所属部门不能　及时了解该部分业务情况，容易出错。　３．解决方案的实现目标　通过寻找最佳解决方案，以减少台账管理员的工作　量。同时为客户经理提供对账页面，客户经理能实时了解　自己的业绩余额变化及当月日均。部门负责人能够查询本　部门每位客户经理的业绩及部门总体业绩。另外，允许台　账管理员调整某笔来账的归宿，即使时问已过去数周，系　统仍能从业务发生之日重新计算每个客户经理的业绩。　我们利用ＳＱＬ　Ｓｅｒｖｅｒ２００５的ＣＬＲ集成特性编写高　效的存储过程，能够完成如下事项：每日通过ＦＴＰｆｌＪ￣　定服务器提取流水文件；将流水文件导入系统；处理　走账；将大量没有明确归宿的来账划拔给营业部虚拟柜　员；在发生业绩调整时能自动重新计算。同时，在ＳＱＬ　Ｓｅｒｖｅｒ２００５建立合适的作业计划，并为使用者提供简洁友　好的用户界面使台账管理员能快分配来账，允许调账。　

二、ＣＬＲ集成的概述　目前，大家熟知的存储过程是ＴｒａｎｓａｃＴ—ＳＱＬ（Ｔ—　ＳＱＬ），在ＳＱＬ　Ｓｅｒｖｅｒ　２００５中，微软开始支持ＣＬＲ集成　（也称ＣＬＲ代码托管），这是一个全新的特性，它意　味着用户可以使用任意．ＮＥＴ　Ｆｒａｍｅｗｏｒｋ语言（包括　

２０１０．１２／中国金融电脑４１　Ｍｉｃｒｏｓｏｆｔ　Ｖｉｓｕａｌ　Ｂａｓｉｃ．ＮＥＴ和Ｍｉｃｒｏｓｏｆｔ　Ｖｉｓｕａｌ　Ｃ＃）编　写存储过程、触发器、用户定义类型、用户定义函数、　用户定义聚合函数以及流处理表值函数。　在财政专户管理中，由于要从指定服务器提取流　水文件并且在数据处理过程中要用到文件访问、数组　操作、字符串操作、高级数学运算等功能，使用ＣＬＲ　集成成为最佳选择　此外，使用ＣＬＲ￣ｔ＝发的存储过程　由ＳＱＬ　Ｓｅｒｖｅｒ　２００５数据库引擎直接控制，数据库引擎　能根据实际情况优化内存和线程的管理，ＣＬＲ代码可　以充分利用服务器的处理能力。这就意味着在以数据　库操作为主要功能的应用程序的执行效率较ＣＬＲ托管　代码相差很远：　开发的应用程序若要定时运行，需要使用操作系统　的任务计划或开发常驻１人ｊ存的伺服程序定时渊用，而　ＳＱＬ　Ｓｅｒｖｅｒ从最甲．版本开始就使用了ＳＱＬ　Ｓｅｒｖｅｒ　Ａｇｅｎｔ　服务，可以轻松实现任务定制和任务的优先级控制。　开发的存储过程正常情况下要从ＦＴＰＪ］Ｅ务器上逐日　提取指定日期的流水文件，但若出现异常，要支持提取　任意日期的文件，因此其参数值是有变化的。应用程序　自身实现可变参数调用难度较大，必须要开发伺服程序　进行调用，而ＳＱＬ　Ｓｅｒｖｅｒ　Ａｇｅｎｔ可以使用ＳＱＬ　Ｓｅｒｖｅｒ支　持的日期函数，轻易就能实现这一点。　

三、解决管理问题的具体方案　如何用ＣＬＲ集成解决财政专户台账管理问题？前端　页面在这里不作介绍，在后台实际上只用了两个存储过　程，下面我们就介绍这两个存储过程。　第一个存储过程逻辑上相对简单，主要实现从指定　眼务器上下载流水文件并导入数据库，同时将没有明确　归宿的来账直接划拨给营业部虚拟柜员　、但这个过程突　显了ＣＬＲ代码不同于传统Ｔ—ＳＱＬ存储过程的不少特征，　下面来看这个存储过程的部分代码，为了方便理解，以　下采用类似于ＶＳ开发环境巾的注释方式进行ｉ兑明。　对于从事过Ｖｉｓｕａｌ　Ｓｔｕｄｉｏ开发的人都知道，下面　４２　中国金融电脑／２０１０　１２　数行是运行该存储过程所需的命名空间，这是传统　Ｔ—ｓＱＬ过程所没有的，这意味着你可以使用．ＮＥＴ　Ｆｒａｍｅｗｏｒｋ库中提供的数千个预生成的类和例程，要　注意是的最后两行，存储过程竞然可以访问数据库以　外的资源。　Ｉｍｐｏｒｔｓ　Ｓｙｓｔｅｍ　Ｉｍｐｏｒｔｓ　Ｓｙｓｔｅｍ．Ｄａｔａ　Ｉｍｐｏｒｔｓ　Ｓｙｓｔｅｍ．Ｄａｔａ．ＳＱＬＣｌｉｅｎｔ　Ｉｍｐｏｒｔｓ　Ｓｙｓｔｅｍ．Ｄａｔａ．ＳＱＬＴｙｐｅｓ　Ｉｍｐｏｒｔｓ　Ｍｉｃｒｏｓｏｆｔ．ＳＱＬＳｅｒｖｅｒ．Ｓｅｒｖｅｒ　Ｉｍｐｏｒｔｓ　Ｓｙｓｔｅｍ．ＩＯ　Ｉｍｐｏｒｔｓ　Ｓｙｓｔｅｍ．Ｔｅｘｔ　下面是在自己创建的Ｖｉ　ｓｕａｌ　Ｓｔｕｄｉｏ　ＳＱＬ　Ｓｅｒｖｅｒ项　目模板中选择添加存储过程后自动创建的，说明使用　者想编写的程序类型，当然也可以创建自定义函数或　触发器等。　Ｐａｒｔｉａｌ　Ｐｕｂｌｉｃ　Ｃｌａｓｓ　ＳｔｏｒｅｄＰｒｏｃｅｄｕｒｅｓ　＜Ｍｉｃｒｏｓｏｆｔ．ＳＱＬＳｅｒｖｅｒ．Ｓｅｒｖｅｒ．ＳＱＬＰｒｏｃｅｄｕｒｅ（）＞一　这个存储过程是带参数的，参数的声明和传统　Ｔ—ＳＱＬ存储过程很类似。　Ｐｕｂｌｉｃ　Ｓｈａｒｅｄ　Ｓｕｂ　ｓｐｉｍｐｏｒｔ——ｃｚｔｘｔ（Ｏｐｔｉｏｎａｌ　ＢｙＶａｌ　ｓｔｄａｔｅ　Ａｓ　Ｓｔｒｉｎｇ＝”１９０（）ＯｌＯｌＩｔ．Ｏｐｔｉｏｎａｌ　ＢｙＶａｌ　ｅｎｄｄａｔｅ　Ａｓ　Ｓｔｒｉｎｇ＝”１９０００１０１”、　ＦＴＰｃｌａｓｓ是创建的一个类，其功能是从指定服务器　上下载文件，重要的不是这个类能做什么，而是它以这　种方式出现在一个存储过程里。　Ｄｉｍ　ＦＴＰｃｌａｓｓ　Ａｓ　Ｎｅｗ　ＦＴＰＣｌａｓｓ　需要声明与数据库的连接采用的是上下文方式，即在　创建ｔ￣ＪＶｉｓｕａｌ　Ｓｔｕｄｉｏ　ＳＱＬ　ＳｅｒｖｅｒＪ＇１）￣目时要求使用者必须建立　的与数据库的连接。建议采用Ｗｉｎｄｏｗｓ集成安全认证方式　进行连接，这样做有利于此后的调试工作。　Ｄｉｍ　ｃｏｎｎｅｃｔｉＯｎ　Ａ　Ｓ　ＳＱＬＣ０ｎｎｅｃｔｉｏｎ＝Ｎｅｗ　ＳＱＬＣｏｎｎｅｃｔｉｏｎ（”ｃｏｎｔｅｘｔ　ｃｏｎｎｅｃｔｉｏｎ＝ｔｒｕｅ”）　Ｄｉｍ　ＳＱＬＳｔｒ　Ａｓ　Ｓｔｒｉｎｇ　Ｄｉｍ　ＳＱＬＣｏｍｍａｎｄ　Ａｓ　Ｎｅｗ　ＳＱＬＣｏｍｍａｎｄ　Ｄｉｍ　ｍｙｄｓ　Ａｓ　Ｎｅｗ　ＤａｔａＳｅｔ　Ｄｉｍ　ｌｏｃａｌｆｉｌｅ　Ａｓ　Ｓｔｒｉｎｇ　Ｄｉｍ　ｓｒ　Ａｓ　ＳｔｒｅａｍＲｅａｄｅｒ　Ｄｉｍ　ｌｉｎｅ　Ａｓ　Ｓｔｒｉｎｇ　Ｄｉｍ　ｓｔｒＡｒｒＯ　Ａｓ　Ｓｔｒｉｎｇ　Ｄｉｍ　ｉｎｔｘ　Ａｓ　Ｉｎｔｅｇｅｒ　Ｔｒｙ　在下面的代码中使用的多个传统Ｔ—ＳＱＬ－Ｉ＇￣￣过程不会　出现的字符串函数，可以充分体验ｃＬｆｔ集成的威力。　ｌｏｃａｌｆｉｌｅ＝”ｅ：＼ｃｚｚｈ—ｄｅａｌｌｉｓｔ—ｔｘｔ＼６０６５”＆ｓｔｄａｔｅ＆　ｅｎｄｄａｔｅ＆”．ｔｘｔ”　Ｉｆ　ＦＴＰｃｌａｓｓ．ＤｏｗｎｌｏａｄＦｉｌｅ（”／ｕｓｒ／ｇｍｇｌ／ＦＴＰ／６０６５”　＆ｓｔｄａｔｅ＆ｅｎｄｄａｔｅ＆”．ｔｘｔ”．１ｏｃａｌｆｉｌｅ．Ｆａｌｓｅ）Ｔｈｅｎ　ｓｒ＝Ｎｅｗ　ＳｔｒｅａｍＲｅａｄｅｒ（１ｏｃａｌｆｉｌｅ，Ｓｙｓｔｅｍ．Ｔｅｘｔ．　Ｅｎｃｏｄｉｎｇ．Ｄｅｆａｕｌｔ）　Ｄｏ　Ｗｈｉｌｅ　ｓｒ．Ｐｅｅｋ＞０　ｌｉｎｅ＝ｓｒ．ＲｅａｄＬｉｎｅ　ｓｔｒＡｒｒ＝Ｓｐｌｉｔ（１ｉｎｅ，”　Ｆｏｒ　ｉｎｔｘ＝０　Ｔｏ　ｓｔｒＡｒｒ．Ｌｅｎｇｔｈ一３　Ｉｆ　ｓｔｒＡｒｒ（ｉｎｔｘ）Ｉｓ　Ｎｏｔｈｉｎｇ　Ｔｈｅｎ　ｓｔｒＡｒｒ（ｉｎｔｘ）＝’’’　ＥｎｄＩｆ　Ｎｅｘｔ　ＳＱＬＳｔｒ＝”ｉｎｓｅｒｔ　ｉｎｔｏ　ｉｎ—ｄｅａｌｌｉｓｔ—ｃｚｚｈ—ｔｍｐ　ｖａｌｕｅｓ（”’　＆ｓｔｒＡｒｒ（８）＆…，”　＆ｓｔｒＡｒｒ（０）＆…，…＆ｓｔｒＡｒｒ（３）＆”’．…＆　ｓｔｒＡｒｒ（４）＆…，…＆ｓｔｒＡｒｒ（６）＆…，…＆ｓｔｒＡｒｒ（７）＆…，”）”　ＳＱＬＣｏｍｍａｎｄ．ＣｏｍｍａｎｄＴｙｐｅ＝ＣｏｍｍａｎｄＴｙｐｅ．Ｔｅｘｔ　ＳＱＬＣｏｍｍａｎｄ．ＣｏｍｍａｎｄＴｅｘｔ＝ＳＱＬＳｔｒ　ＳＱＬＣｏｎｔｅｘｔ类表示调用方上下文的抽象，即上文　所讲的数据库连接；Ｐｉｐｅ属性允许调用方将结果集、消　息和命令的执行结果发送回客户端的管道对象。　ＳＱＬＣｏｎｔｅｘｔ．Ｐｉｐｅ．ＥｘｅｃｕｔｅＡｎｄＳｅｎｄ（ＳＱＬＣｏｍｍａｎｄ）　Ｌｏｏｐ　ｓｒ．Ｃｌｏｓｅ（）　ｓｒ＝Ｎｏｔｈｉｎｇ　

Ｃａｔｃｈ　ｅｘ　Ａｓ　Ｅｘｃｅｐｔｉｏｎ　ＥｎｄＴｒｙ　Ｅｎｄ　Ｓｕｂ　Ｅｎｄ　Ｃｌａｓｓ　第二个存储过程的逻辑相当复杂，当账户某日没有　业务发生时，程序要依据上日数据自动生成所有相关客户　经理的余额和积数；当账户该日有业务发生时，按前文提　到的规则进行走账和来账的处理；若之问有新的客户经理　介入，程序也能自动对其初始化；为了支持台账管理员　的调账，程序总是从数据库中未处理业务的最小日期开　始逐日计算每位客户经理的业绩直至当前日期。如果要　用传统的Ｔ—ＳＱＬ来实现存储过程来编写的话，基本上很　难完成，但现在使用ＣＬＲ集成方式开发的存储过程，运行　状况都非常稳定。下面是该存储过程具体的实现思路。　如果首次运行，先做初始化：从流水表插入余额表，　此时各工号余额、发生额、积数均为０。　事务开始　从流水表选择待处理的记录中最小的业务日期　ｍｉｎｄａｔｅ　以ｍｉｎｄａｔｅ作为循环开始直到当前日期（ＬＯＯＰｌ　ｂｅｇｉｎ）　清除余额表中业务日期＝ｍｉｎｄａｔｅ的记录　从余额表中选择业务日期＝ｍｉｎｄａｔｅ—ｌ的数据插入到　余额表作为ｍｉｎｄａｔｅ日的基础数据　从余额表中选择业务日期＝ｍｉｎｄａｔｅ的余额并汇总　从流水表中选择业务日期＝ｍｉｎｄａｔｅ的走账流水并汇　总金额