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 。

　　

原文轉自：http://www.kjueaiud.com