Oracle 中使用層次查詢方便處理財務報表

　　Oracle　中Connect By 子句對在關系表上表現層次關系提供了方便
　　使用Connect by 子句需要在表中定義兩個字段一個是父節點字段一個是節點字段其中節點字段一般來說是主鍵
　　例如我們作一張資產負債表
　　數據來源
　　資　　產 期末余額 負債及所有者權益 期末余額
　　流動資產　　　　流動負債　
　　現金　　　　　　　短期存款　　
　　存放中央銀行款項　　　　　　財政性存款　　
　　存放同業款項　　 　　　　向中央銀行借款　
　　短期貸款　 　　　　同業存放款項　　　
　　其他流動資產　　 　　　　其他流動負債　　
　　長期資產　 　　長期負債　　　
　　中長期貸款　 　　　　發行長期債券
　　減貸款呆賬准備　　 　　　　其他長期負債　　　
　　固定資產淨值　　
　　其他長期資產　　
　　無形遞延及其它資產　　　　　所有者權益　　
　　其中實收資本　　
　　資產總計　 　 負債及所有者權益合計　　
　　
　　Create table balance_sheet (BS_ID INTEGER BS_PID INTEGER BS_NAME VARCHAR() BS_VALUE NUMBER() );
　　BS_ID　項目代碼　BS_PID 項目父代碼 BS_Name 項目名稱 BS_VALUE 數據列
　　插入測試數據
　　insert into balance_sheet values(流動資產);
　　insert into balance_sheet values(現金);
　　insert into balance_sheet values(存放中央銀行款項);
　　insert into balance_sheet values(存放同業款項);
　　insert into balance_sheet values(短期貸款 );
　　insert into balance_sheet values(其他流動資產);
　　insert into balance_sheet values(長期資產);
　　insert into balance_sheet values(中長期貸款 );
　　insert into balance_sheet values(減貸款呆賬准備);
　　insert into balance_sheet values(固定資產淨值);
　　insert into balance_sheet values(其他長期資產);
　　insert into balance_sheet values(無形遞延及其它資產);
　　insert into balance_sheet values(資產總計);
　　insert into balance_sheet values(流動負債);
　　insert into balance_sheet values(短期存款);
　　insert into balance_sheet values(財政性存款);
　　insert into balance_sheet values(向中央銀行借款);
　　insert into balance_sheet values(同業存放款項);
　　insert into balance_sheet values(其他流動負債);
　　insert into balance_sheet values(長期負債);
　　insert into balance_sheet values(發行長期債券null);
　　insert into balance_sheet values(其他長期負債 );
　　insert into balance_sheet values(所有者權益);
　　insert into balance_sheet values(其中實收資本);
　　insert into balance_sheet values(負債及所有者權益合計);
　　commit;
　　顯示全部數據
　　select bs_namebs_value　from balance_sheet
　　connect by prior bs_id = bs_pid
　　start with bs_pid = –可以省略
　　　
　　顯示一個節點的數據
　　select bs_namebs_value　from balance_sheet
　　connect by prior bs_id = bs_pid
　　start with bs_pid =
　　其中connect by 定義父子連接關系
　　start with 定義開始節點這個子句可以省略表示自動將全部節點展開
　　　
　　(流動資產節點數據)
　　
　　顯示層次結構
　　select (case when level = then 　　　　||bs_name
　　when level = then　　　　　　　　　||bs_name
　　end ) bs_name
　　bs_value from balance_sheet
　　connect by prior bs_id = bs_pid
　　start with bs_pid =
　　
　　其中引用了level字段表示層次它是每張表默認的字段其他默認的字段還有rownum
　　　
　　（根據層次來實現縮進風格）
　　以下功能 i 及以上版本支持
　　
　　層次內排序
　　select (case when level = then 　　　　||bs_name
　　when level = then　　　　　　　　　||bs_name
　　end ) bs_name
　　bs_value from balance_sheet
　　connect by prior bs_id = bs_pid
　　start with bs_id = or bs_id =
　　ORDER SIBLINGS BY bs_value desc
　　　
　　 取遍歷路徑
　　select
　　ltrim(sys_connect_by_path( BS_Name|)|) path
　　(case when level = then 　　　　||bs_name
　　when level = then　　　　　　　　　||bs_name
　　end ) bs_name
　　bs_value
　　from balance_sheet
　　connect by prior bs_id = bs_pid
　　start with bs_pid =
　　　
　　層次計算
　　這裡層次計算是指根據父子節點關系進行匯總也就是說　父節點 = SUM（子節點）
　　但是在財務報表父指標不一定是子指標的疊加也可能是幾個子指標減去另外幾個子指標
　　例如
　　長期資產 = 中長期貸款 – 貸款呆賬准備 +固定資產淨值 +其他長期資產
　　為了實現這種情況我們建一個字段BS_Dir來表示加減方向 表示 加表示減
　　這樣 父節點 = SUM（子節點 * Direction）
　　SELECT
　　SUBSTR (PATH INSTR (PATH | ) ) Par_path
　　sum(bs_value * bs_dir)
　　FROM　 (SELECT　BS_IDBS_PID　 LTRIM (SYS_CONNECT_BY_PATH (bs_name |) |) PATH
　　bs_valuebs_dir
　　FROM balance_sheet
　　CONNECT BY PRIOR bs_id = bs_pid
　　START WITH bs_pid = )
　　group by　 SUBSTR (PATH INSTR (PATH | ) )
　　
　　竟然有意外的收獲原表中的數據流動資產是錯的！！！也許是我對業務知識了解不夠如果您知道原因還清指點
　　數據的最後一行是對所有原表第一層節點的疊加如果希望得到資產和負債的總計數據需要對節點順序進行重新調整我的想法是建立一個表示匯總關系的邏輯的BS_LID 和
　　BS_LParID
From:http://tw.wingwit.com/Article/program/Oracle/201311/18351.html