头疼的科目余额计算

Question

一 表结构及数据: 
1:科目表 KM 
(科目编号   科目名称  科目全称) 
  KMBH     KMMC    KMQC 
  1001     现金     现金 
  1001-01  人民币   现金-人民币 
  1001-02  美圆     现金-美圆 
  1603     在建工程 在建工程 
  1603-01  建筑费   在建工程-建筑费 
  1603-02  安装费   在建工程-安装费 
  1603-03  设备费   在建工程-设备费 
  1603-04  其他费   在建工程-其他费 
2:凭证表 PZ 
  (凭证编号     科目编号  借方金额  贷方金额 摘要   录入日期) 
  PZBH        KMBH    JFJE    DFJE     ZY    LRRQ 
  20091210001 1001-01 1000    0              2009-12-01 
  20091210001 1603-04 0       1000           2009-12-01 二 余额表格式 
   按照科目范围查询某一个月的汇总表,要求如下 
   科目编号,科目名称,期初借方金额,期初贷方金额,本期借方金额,本期贷方金额,累计余额 三 其他说明 
  1:凭证表数据随着时间的增加数据量越来越多 
  2:科目编号有层次要求,如1001-01 和1001-02为1001的2个叶子节点科目,而录入的凭证只有叶子节点科目,所以在汇总余额时会有一个向父节点科目汇总的过程 四 问题 
   原本的设计思路是直接在BO里直接写一个INSERT语句来实现,遇到2个问题:a,数据量大的时候会出现从套接字无法获取更多数据问题;b:因为采用一条INSERT语句来实现所以FROM下对应的是一个复杂的SELECT语句,而ORACLE恰好INSERT对复杂的语句有BUG,会出现数据插入不稳定的现象.针对以上问题放弃了一条语句实现改用存储过程,先在外层构造动态游标,然后循环计算每一个科目的期初借方金额,期初贷方金额,本期借方金额,本期贷方金额,累计余额数据(拿科目表里的KMBH去LIKE凭证表里的KMBH)再INSERT,行到是行就是那个速度啊,太慢. 
   在这里请教各位看有什么别的好方法没,因为本人对ORACLE不是很熟悉,主要是才切换到这个数据库,很多思路还没打开,谢谢各位了! 

Answer 1

计算的部分优化下。
insert用forall实现批量插入，速度会快点。

Answer 2

通过存储过程实现，可以不需要游标的。
思路大致如下：
1、构造临时表（包含科目编号,科目名称,期初借方金额,期初贷方金额,本期借方金额,本期贷方金额,累计余额 等字段）
2、将当前的科目主数据插入到临时表，期初借方金额,期初贷方金额,本期借方金额,本期贷方金额,累计余额 等字段全初始设置为0
3、汇总凭证表中月份小于当前月份的借方金额与贷方金额到期初借方金额,期初贷方金额
update 临时表 a set a.期初借方金额 = (select sum(nvl(b.借方金额,0)) from 凭证表 b
where a.科目编号=b.科目编号 and b.月份<查询月份);
update 临时表 a set a.期初贷方金额 = (select sum(nvl(b.贷方金额,0)) from 凭证表 b
where a.科目编号=b.科目编号 and b.月份<查询月份);
4、汇总凭证表中月份等于当前月份的借方金额与贷方金额到本期借方金额,本期贷方金额
update 临时表 a set a.本期借方金额 = (select sum(nvl(b.借方金额,0)) from 凭证表 b
where a.科目编号=b.科目编号 and b.月份=查询月份);
update 临时表 a set a.本期贷方金额 = (select sum(nvl(b.贷方金额,0)) from 凭证表 b
where a.科目编号=b.科目编号 and b.月份=查询月份);
5、计算累计余额
update 临时表 a  set 累计余额 = nvl(期初借方金额,0)-nvl(期初贷方金额,0)+nvl(a.本期借方金额,0)-
nvl(a.本期贷方金额,0)

Answer 3

select a.凭证编号,sum(b.借方金额-b.贷方金额) ce from 科目表 a,凭证表 b where b.凭证编号like a.凭证编号||'%' group by a.凭证
    a.凭证编号 like b.凭证编号||'%'  会很快,
但是b.凭证编号 like a.凭证编号||'%'  那个速度也是慢,而且似乎没有这样搞的

Answer 4

上面的打错了  "凭证编号"  应该为 "科目编号"select a.科目编号,sum(b.借方金额-b.贷方金额) ce from 科目表 a,凭证表 b where b.科目编号like a.科目编号||'%' group by a.科目编号 
    a.科目编号 like b.科目编号||'%'  会很快, 
但是b.科目编号 like a.科目编号||'%'  那个速度也是慢,而且似乎没有这样搞的