Oracle 中使用层次查询方便处理财务报表
摘要：本文介绍了如何使用Oracle 中的Connect by 子句，并结合一个实例，完成了对一张资产负债表的计算。
Oracle 中Connect By 子句对在关系表上表现层次关系提供了方便。
使用Connect by 子句需要在表中定义两个字段，一个是父节点字段，一个是节点字段。其中节点字段一般来说是主键。
例如我们作一张资产负债表
数据来源：http://www.adbc.com.cn/XXLR1.ASP?ID=5211
资 产
|
期末余额
|
负债及所有者权益
|
期末余额
---|---|---|---
流动资产
|
4256.45
|
流动负债
|
7453.74
现金
|
2.00
|
短期存款
|
305.54
存放中央银行款项
|
160.77
|
财政性存款
|
411.80
存放同业款项
|
18.34
|
向中央银行借款
|
6485.05
短期贷款
|
4103.41
|
同业存放款项
|
2.15
其他流动资产
|
71.93
|
其他流动负债
|
249.20
长期资产
|
3287.75
|
长期负债
|
0.07
中长期贷款
|
3262.89
|
发行长期债券
|
减：贷款呆账准备
|
73.71
|
其他长期负债
|
0.07
固定资产净值
|
77.58
|
|
其他长期资产
|
20.99
|
|
无形、递延及其它资产
|
0.52
|
所有者权益
|
190.91
|
|
其中：实收资本
|
165.15
资产总计
|
7644.72
|
负债及所有者权益合计
|
7644.72
Create table balance_sheet (BS_ID INTEGER ,BS_PID INTEGER ,BS_NAME VARCHAR2(100) ,BS_VALUE NUMBER(10) );
BS_ID 项目代码 BS_PID 项目父代码 BS_Name 项目名称 BS_VALUE 数据列
插入测试数据
insert into balance_sheet values(1,0,'流动资产',4256.45);
insert into balance_sheet values(2,1,'现金',2.00);
insert into balance_sheet values(3,1,'存放中央银行款项',160.77);
insert into balance_sheet values(4,1,'存放同业款项',18.34);
insert into balance_sheet values(5,1,'短期贷款', 4103.41);
insert into balance_sheet values(6,1,'其他流动资产',71.93);
insert into balance_sheet values(7,0,'长期资产',3287.75);
insert into balance_sheet values(8,7,'中长期贷款', 3262.89);
insert into balance_sheet values(9,7,'减：贷款呆账准备',73.71);
insert into balance_sheet values(10,7,'固定资产净值',77.58);
insert into balance_sheet values(11,7,'其他长期资产',20.99);
insert into balance_sheet values(12,0,'无形、递延及其它资产',0.52);
insert into balance_sheet values(13,0,'资产总计',7644.72);
insert into balance_sheet values(14,0,'流动负债',7453.74);
insert into balance_sheet values(15,14,'短期存款',305.54);
insert into balance_sheet values(16,14,'财政性存款',411.80);
insert into balance_sheet values(17,14,'向中央银行借款',6485.05);
insert into balance_sheet values(18,14,'同业存放款项',2.15);
insert into balance_sheet values(19,14,'其他流动负债',249.20);
insert into balance_sheet values(20,0,'长期负债',0.07);
insert into balance_sheet values(21,20,'发行长期债券',null);
insert into balance_sheet values(22,20,'其他长期负债', 0.07);
insert into balance_sheet values(23,0,'所有者权益',190.91);
insert into balance_sheet values(24,23,'其中：实收资本',165.15);
insert into balance_sheet values(25,0,'负债及所有者权益合计',7644.72);
commit;
显示全部数据：
select bs_name,bs_value from balance_sheet
connect by prior bs_id = bs_pid
start with bs_pid = 0 – 可以省略 __
__
流动资产
|
4256.45
---|---
现金
|
2
存放中央银行款项
|
160.77
存放同业款项
|
18.34
短期贷款
|
4103.41
其他流动资产
|
71.93
长期资产
|
3287.75
中长期贷款
|
3262.89
减：贷款呆账准备
|
73.71
固定资产净值
|
77.58
其他长期资产
|
20.99
无形、递延及其它资产
|
0.52
资产总计
|
7644.72
流动负债
|
7453.74
短期存款
|
305.54
财政性存款
|
411.8
向中央银行借款
|
6485.05
同业存放款项
|
2.15
其他流动负债
|
249.2
长期负债
|
0.07
发行长期债券
|
其他长期负债
|
0.07
所有者权益
|
190.91
其中：实收资本
|
165.15
负债及所有者权益合计
|
7644.72
__
显示一个节点的数据
select bs_name,bs_value from balance_sheet
connect by prior bs_id = bs_pid
start with bs_pid = 1
其中 connect by 定义父子连接关系 __
start with 定义开始节点，这个子句可以省略，表示自动将全部节点展开 __
流动资产
|
4256.45
---|---
现金
|
2
存放中央银行款项
|
160.77
存放同业款项
|
18.34
短期贷款
|
4103.41
其他流动资产
|
71.93
__
( 流动资产节点数据 )
__
显示层次结构
select (case when level = 1 then ' '||bs_name
when level = 2 then ' '||bs_name
end ) bs_name
,bs_value from balance_sheet
connect by prior bs_id = bs_pid
start with bs_pid = 0
其中引用了level字段，表示层次,它是每张表默认的字段，其他默认的字段还有rownum
流动资产
|
4256.45
---|---
现金
|
2
存放中央银行款项
|
160.77
存放同业款项
|
18.34
短期贷款
|
4103.41
其他流动资产
|
71.93
长期资产
|
3287.75
中长期贷款
|
3262.89
减：贷款呆账准备
|
73.71
固定资产净值
|
77.58
其他长期资产
|
20.99
无形、递延及其它资产
|
0.52
资产总计
|
7644.72
流动负债
|
7453.74
短期存款
|
305.54
财政性存款
|
411.8
向中央银行借款
|
6485.05
同业存放款项
|
2.15
其他流动负债
|
249.2
长期负债
|
0.07
发行长期债券
|
其他长期负债
|
0.07
所有者权益
|
190.91
其中：实收资本
|
165.15
负债及所有者权益合计
|
7644.72
（根据层次来实现缩进风格）
--以下功能 9i 及以上版本支持
层次内排序
select (case when level = 1 then ' '||bs_name
when level = 2 then ' '||bs_name
end ) bs_name
,bs_value from balance_sheet
connect by prior bs_id = bs_pid
start with bs_id = 1 or bs_id = 7
ORDER SIBLINGS BY bs_value desc
流动资产
|
4256.45
---|---
短期贷款
|
4103.41
存放中央银行款项
|
160.77
其他流动资产
|
71.93
存放同业款项
|
18.34
现金
|
2
长期资产
|
3287.75
中长期贷款
|
3262.89
固定资产净值
|
77.58
减：贷款呆账准备
|
73.71
其他长期资产
|
20.99
-- 取遍历路径
select
ltrim(sys_connect_by_path( BS_Name,'|'),'|') path,
(case when level = 1 then ' '||bs_name
when level = 2 then ' '||bs_name
end ) bs_name
,bs_value
from balance_sheet
connect by prior bs_id = bs_pid
start with bs_pid = 0
流动资产
|
流动资产
|
4256.45
---|---|---
流动资产|现金
|
现金
|
2
流动资产|存放中央银行款项
|
存放中央银行款项
|
160.77
流动资产|存放同业款项
|
存放同业款项
|
18.34
流动资产|短期贷款
|
短期贷款
|
4103.41
流动资产|其他流动资产
|
其他流动资产
|
71.93
层次计算
这里层次计算是指根据父子节点关系进行汇总，也就是说 父节点 = SUM（子节点）。
但是在财务报表父指标，不一定是子指标的叠加，也可能是几个子指标减去另外几个子指标。
例如：
长期资产 = 中长期贷款 – 贷款呆账准备 +固定资产净值 +其他长期资产。
为了实现这种情况，我们建一个字段BS_Dir来表示加减方向 1表示 加，-1表示减
这样 父节点 = SUM（子节点 * Direction）
SELECT
SUBSTR (PATH, 0, INSTR (PATH, '|', -1, 1) - 1) Par_path ,
sum(bs_value * bs_dir)
FROM (SELECT BS_ID,BS_PID, LTRIM (SYS_CONNECT_BY_PATH (bs_name, '|'), '|') PATH,
bs_value,bs_dir
FROM balance_sheet
CONNECT BY PRIOR bs_id = bs_pid
START WITH bs_pid = 0)
group by SUBSTR (PATH, 0, INSTR (PATH, '|', -1, 1) - 1)
长期负债
|
0.07
---|---
长期资产
|
3287.75
流动负债
|
7453.74
流动资产
|
4356.45
所有者权益
|
165.15
|
30478.88
竟然有意外的收获，原表中的数据流动资产是错的！！！也许是我对业务知识了解不够。如果您知道原因，还清指点。
数据的最后一行是对所有原表第一层节点的叠加，如果希望得到资产和负债的总计数据，需要对节点顺序进行重新调整，我的想法是建立一个表示汇总关系的逻辑的BS_LID 和
BS_LParID 。
结束。
[email protected] [email protected]
于 19:45 2005-6-14