Oracle SQL 實戰：遞迴查詢欄位樹並依權限過濾無效節點

需求背景

在 CMS 系統中，需根據用戶權限動態生成欄位導航樹。需求包含三層邏輯：

1. 取得用戶有權限的欄位（np_cms_column_security 中 action_1 = '1'）
2. 排除權限樹下被明確拒絕的節點（action_1 = '0'）
3. 處理斷層節點，建立虛擬父子關係以利前端渲染

核心 SQL 結構解析

視圖 1：基礎權限樹 V_NP_CTREE_BS

使用 Oracle CONNECT BY 遞迴查詢，結合 LAG() 分析函數標記根節點：

CREATE OR REPLACE VIEW V_NP_CTREE_BS AS
SELECT B.* FROM (
  SELECT A.*, LAG(A.column_id) OVER (PARTITION BY A.column_id ORDER BY 0) RK
  FROM (
    SELECT /*+CHOOSE */ t.*
    FROM np_cms_column t
    WHERE t.is_active = '1'
    CONNECT BY PRIOR t.column_id = t.parent_id
    START WITH t.column_id IN (
      SELECT t.column_id
      FROM np_cms_column_security t
      WHERE t.subject_id = 'mazj'
        AND t.action_1 = '1'
    )
  ) A
) B
WHERE NOT EXISTS (
  SELECT 0 FROM (
    SELECT DISTINCT d.column_id
    FROM np_cms_column d
    CONNECT BY PRIOR d.column_id = d.parent_id
    START WITH d.column_id IN (
      SELECT t.column_id
      FROM np_cms_column_security t
      WHERE t.subject_id = 'mazj'
        AND t.action_1 = '0'
        AND EXISTS (
          SELECT 0 FROM (
            SELECT DISTINCT d.column_id
            FROM np_cms_column d
            CONNECT BY PRIOR d.column_id = d.parent_id
            START WITH d.column_id IN (
              SELECT t.column_id
              FROM np_cms_column_security t
              WHERE t.subject_id = 'mazj'
                AND t.action_1 = '1'
            )
          ) C1
          WHERE C1.column_id = t.column_id
        )
    )
    AND d.is_active = '1'
  ) C
  WHERE C.column_id = B.column_id AND B.RK IS NULL
) AND B.RK IS NULL
UNION ALL
SELECT c.*, 0 RK FROM np_cms_column c WHERE c.parent_id = 0;

視圖 2：虛擬父節點 V_NP_CTREE_PA

解決欄位樹斷層問題，為無父節點的項目指定虛擬父 ID（預設為 1）：

CREATE OR REPLACE VIEW V_NP_CTREE_PA AS
SELECT B.*,
  (CASE B.column_id WHEN 1 THEN 0 ELSE NVL(B.father, 1) END) VFA
FROM (
  SELECT v.*,
    (SELECT vv.column_id FROM V_NP_CTREE_BS vv WHERE vv.column_id = v.parent_id) FATHER
  FROM V_NP_CTREE_BS v
) B;

視圖 3：最終輸出樹 V_NP_CTREE_RS

使用 SYS_CONNECT_BY_PATH 生成完整路徑，配合 LPAD 產生縮排，便於前端樹狀顯示：

CREATE OR REPLACE VIEW V_NP_CTREE_RS AS
SELECT D.*, LPAD(' ', 2 * LEVEL - 1) || SYS_CONNECT_BY_PATH(D.COLUMN_NAME, '/') "Path"
FROM (
  SELECT c.* FROM V_NP_CTREE_PA c
  ORDER BY c.VFA, c.disorder DESC, c.column_id DESC
) D
CONNECT BY PRIOR D.column_id = D.VFA
START WITH D.column_id = 1;

關鍵技術點總結

• 分層視圖設計：將權限過濾、斷層處理、格式化輸出分離，便於維護
• NOT EXISTS 反向過濾：精準排除權限樹下被拒絕的子樹
• 虛擬父節點機制：確保所有節點掛載到單一根節點，避免前端渲染異常
• 分析函數輔助：LAG() 識別根節點，NVL() 提供預設值