|
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_pidstart 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_pidstart 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 = 7ORDER 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[1] [2] 下一页
[系统软件]EXP-00008: ORACLE error 904 encountered的解决方… [常用软件]PB7 连接 Oracle 的配置方法
[Web开发]oracle Export and Import 简介 [Web开发]ADO访问Oracle结果集的心得
[JAVA开发]JDBC+Hibernate将Blob数据写入Oracle [JAVA开发]J2EE应用中与Oracle数据库的连接
[JAVA开发]Oracle Application Serve_ [其他]HP-UXrx2600B.11.22Uia64安装oracle9i9.2foria64手…
[其他]在RedhatEnterpriseserver3上安装oracle9iR2的注意… [其他]PROC＋＋批量导入导出ORACLE数据库表
|
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
