Oracle 階層查詢 CONNECT BY 處理財務報表彙總實戰 (層次排序、路徑、遞迴計算)

資產負債表資料結構

CREATE TABLE balance_sheet (
  bs_id   INTEGER PRIMARY KEY,   -- 節點代碼
  bs_pid  INTEGER,               -- 父節點代碼
  bs_name VARCHAR2(100),         -- 專案名稱
  bs_value NUMBER(10),          -- 金額
  bs_dir  NUMBER(1) DEFAULT 1    -- 彙總方向：1=加, -1=減
);

基礎查詢：完整樹狀展開

SELECT bs_name, bs_value
FROM balance_sheet
CONNECT BY PRIOR bs_id = bs_pid
START WITH bs_pid = 0;  -- 根節點父代碼為 0

縮進顯示層次 (使用 LEVEL 偽欄)

SELECT 
  CASE WHEN level = 1 THEN '  ' || bs_name
       WHEN level = 2 THEN '    ' || bs_name
  END AS bs_name,
  bs_value
FROM balance_sheet
CONNECT BY PRIOR bs_id = bs_pid
START WITH bs_pid = 0;

9i+ 新功能：同層排序 (ORDER SIBLINGS BY)

SELECT ... 
FROM balance_sheet
CONNECT BY PRIOR bs_id = bs_pid
START WITH bs_id IN (1,7)  -- 流動資產、長期資產
ORDER SIBLINGS BY bs_value DESC;

取得完整路徑 (SYS_CONNECT_BY_PATH)

SELECT LTRIM(SYS_CONNECT_BY_PATH(bs_name, '|'), '|') AS path,
       bs_name, bs_value
FROM balance_sheet
CONNECT BY PRIOR bs_id = bs_pid
START WITH bs_pid = 0;

遞迴彙總計算：父節點 = Σ(子節點 × 方向)

SELECT SUBSTR(path, 1, INSTR(path, '|', -1, 1)-1) AS parent_path,
       SUM(bs_value * bs_dir) AS calculated_value
FROM (
  SELECT bs_id, bs_pid,
         LTRIM(SYS_CONNECT_BY_PATH(bs_name, '|'), '|') AS path,
         bs_value, bs_dir
  FROM balance_sheet
  CONNECT BY PRIOR bs_id = bs_pid
  START WITH bs_pid = 0
)
GROUP BY SUBSTR(path, 1, INSTR(path, '|', -1, 1)-1);

實戰發現

彙總計算結果顯示原表「流動資產」金額有誤 (應為 4356.45 而非 4256.45)，證明階層查詢可作為財務報表自動核對工具。

擴充建議

• 新增 bs_lid (邏輯層級)、bs_lpid (邏輯父節點) 區分「顯示層次」與「計算邏輯」
• 支援多重彙總規則 (加/減/乘/除) 可擴充 bs_op 欄位