【PL/SQL】再帰WITH句と階層問い合わせの実用パターン

データモデルに潜む入れ子構造や親子関係を、アプリケーションのループや一時表に頼らずSQLで直接表現するためには、Oracleの階層問い合わせ（CONNECT BY）と再帰WITH句（Recursive Subquery Factoring）が有効である。両者は似て非なる機能であり、CONNECT BYは長年の実績と専用擬似列による手軽さが強みで、再帰WITHは表現力と最適化の自由度で勝る。本稿では、実務で頻出する組織階層や部品展開、経路・パス生成、サイクル検出、枝刈り、ボトムアップ集計などを、両機能の使い分けとともに設計・性能・検証の観点で示す。

使い分けの指針と前提

同一の問題は多くの場合どちらでも解けるが、要件次第で選択が変わる。再帰WITHはUNION ALLのアンカー部と再帰部を明示し、SEARCH/CYCLE句や途中停止条件を柔軟に埋め込めるため、複合的な条件や複数テーブルを跨いだ探索に向く。CONNECT BYはCONNECT_BY_ROOT、SYS_CONNECT_BY_PATH、CONNECT_BY_ISLEAFなどの専用機能が豊富で、単一テーブルの親子関係をスピーディに扱うとき有利である。性能面では起点行の絞り込みと、親子の結合キーに対する適切な索引付けが共通の土台になる。

再帰WITHの基本構文と組織ツリーの展開

アンカー部で起点行を定義し、再帰部で親子関係を辿る。LEVELに相当する深さはカウンタとして自前で保持し、パスや根ノードも列として構築する。

-- 社員テーブル emp(emp_id, mgr_id, emp_name, dept_id) を想定
WITH emptree (emp_id, mgr_id, emp_name, depth, path) AS (
  SELECT e.emp_id, e.mgr_id, e.emp_name, 1,
         CAST('/' || e.emp_name AS VARCHAR2(4000))
    FROM emp e
   WHERE e.mgr_id IS NULL
  UNION ALL
  SELECT c.emp_id, c.mgr_id, c.emp_name, p.depth + 1,
         p.path || '/' || c.emp_name
    FROM emp c
    JOIN emptree p
      ON p.emp_id = c.mgr_id
)
SELECT emp_id, mgr_id, emp_name, depth, path
  FROM emptree
 ORDER BY path;

探索順序とサイクル検出、途中停止の制御

深さ優先か幅優先かで結果の並びと一部の計算結果が変わるため、SEARCH句で順序を明示する。サイクルの可能性がある場合はCYCLE句で検出し、循環を切って安全に返す。再帰部に制約を置くことで不必要な経路を早期に枝刈りできる。

WITH emptree(emp_id, mgr_id, emp_name) AS (
  SELECT emp_id, mgr_id, emp_name FROM emp WHERE mgr_id IS NULL
  UNION ALL
  SELECT c.emp_id, c.mgr_id, c.emp_name
    FROM emp c
    JOIN emptree p ON p.emp_id = c.mgr_id
)
SEARCH DEPTH FIRST BY emp_name SET ord
CYCLE emp_id SET is_cycle TO 'Y' DEFAULT 'N'
SELECT emp_id, mgr_id, emp_name, ord, is_cycle
  FROM emptree
 WHERE is_cycle = 'N';

-- 最大深さを5に制限する枝刈り（深さ列を持つ版）
WITH emptree(emp_id, mgr_id, emp_name, depth) AS (
  SELECT emp_id, mgr_id, emp_name, 1 FROM emp WHERE mgr_id IS NULL
  UNION ALL
  SELECT c.emp_id, c.mgr_id, c.emp_name, p.depth + 1
    FROM emp c
    JOIN emptree p ON p.emp_id = c.mgr_id
   WHERE p.depth < 5
)
SELECT * FROM emptree;

ボトムアップ集計と部品表（BOM）のコストロールアップ

部品構成 bom(parent_item, child_item, qty) と品目原価 item_cost(item, unit_cost) から、親品目の合算原価を求める場合、再帰で展開しつつパスごとの必要数量を累積するのが定石である。

WITH RECURSIVE bom_tree (root_item, item, qty_path) AS (
  SELECT b.parent_item AS root_item, b.child_item AS item, b.qty AS qty_path
    FROM bom b
   WHERE b.parent_item = :root
  UNION ALL
  SELECT t.root_item, b.child_item,
         t.qty_path * b.qty
    FROM bom b
    JOIN bom_tree t ON t.item = b.parent_item
)
SELECT t.root_item,
       SUM(t.qty_path * ic.unit_cost) AS rolled_cost
  FROM bom_tree t
  JOIN item_cost ic ON ic.item = t.item
 GROUP BY t.root_item;

パス生成と条件付き経路フィルタ

承認ワークフローのように、特定のロールに到達するまでの経路だけが欲しい場面では、パス列を構築しながら再帰部で停止条件を設ける。到達済みノードの再訪を禁止するために訪問集合を文字列や配列で保持すると、重複経路の爆発を抑えられる。

WITH flow(emp_id, role, next_emp_id) AS (SELECT ...),
pathwalk (start_emp, emp_id, role, path) AS (
  SELECT f.emp_id, f.emp_id, f.role, CAST('/' || f.emp_id AS VARCHAR2(4000))
    FROM flow f
   WHERE f.emp_id = :requester
  UNION ALL
  SELECT p.start_emp, n.emp_id, n.role,
         p.path || '/' || n.emp_id
    FROM pathwalk p
    JOIN flow n ON n.emp_id = p.emp_id
   WHERE INSTR(p.path, '/'||n.next_emp_id||'/') = 0
)
SELECT *
  FROM pathwalk
 WHERE role = 'APPROVER'
   AND ROWNUM = 1;

CONNECT BYの即効パターンと専用擬似列

親子が同一テーブルで完結しており、深さや末端判定、根の値の複写、パスの文字列化が欲しいだけならCONNECT BYの表現が簡潔である。専用擬似列LEVEL、CONNECT_BY_ROOT、SYS_CONNECT_BY_PATH、CONNECT_BY_ISLEAF、PRIOR句とSTART WITHの組み合わせで多くの要件を素直に満たせる。

-- 組織ツリーの基本
SELECT LEVEL AS depth,
       CONNECT_BY_ROOT emp_name AS root_name,
       emp_name,
       SYS_CONNECT_BY_PATH(emp_name, '/') AS path,
       CONNECT_BY_ISLEAF AS is_leaf
  FROM emp
 START WITH mgr_id IS NULL
CONNECT BY PRIOR emp_id = mgr_id
 ORDER SIBLINGS BY emp_name;

-- NOCYCLEで循環を許容しつつ重複経路を抑制
SELECT emp_id, emp_name, LEVEL, SYS_CONNECT_BY_PATH(emp_name, '/') AS path
  FROM emp
 START WITH mgr_id IS NULL
CONNECT BY NOCYCLE PRIOR emp_id = mgr_id;

部分木の合計と代表行抽出を階層SQLで完結させる

根を固定して部分木配下の集計を出したい場合、CONNECT_BY_ROOTで根のキーを引き回し、GROUP BYでまとめる。最新行の代表抽出は、階層式よりも分析関数とサブクエリの組み合わせが明快なことが多いが、単純なケースならCONNECT BYでも完結する。

-- ある部門を根とする配下社員の給与合計
SELECT CONNECT_BY_ROOT dept_id AS root_dept,
       SUM(salary) AS total_salary
  FROM emp
 START WITH dept_id = :root_dept
CONNECT BY PRIOR emp_id = mgr_id
 GROUP BY CONNECT_BY_ROOT dept_id;

階層＋分析関数の合成で柔軟性を高める

階層で展開した結果に対し、同一部分木内の順位や累積を付与するとレポートが簡潔になる。階層から得た根IDと深さをパーティションキーに採用するのがコツである。

WITH t AS (
  SELECT CONNECT_BY_ROOT emp_id AS root_emp,
         emp_id,
         emp_name,
         LEVEL AS depth
    FROM emp
   START WITH mgr_id IS NULL
 CONNECT BY PRIOR emp_id = mgr_id
)
SELECT t.*,
       ROW_NUMBER() OVER (PARTITION BY root_emp ORDER BY depth, emp_name) AS rn_in_tree
  FROM t;

性能設計の勘所と落とし穴の回避

起点行が多いほど探索空間は指数的に膨らみやすいため、START WITHやアンカー部での絞り込みを最優先で行う。親子結合に使う列には整合した索引を用意し、親キー側はPRIMARYまたはUNIQUE、子の外部キー側はB*Treeの単列または複合索引で支えると、階層の各ステップでランダムI/Oを抑制できる。再帰WITHでは再帰部の結合条件に冗長な列を増やすと行爆発を起こすため、業務条件はできる限りアンカー側で削る。サイクルの可能性があるモデルではCYCLE句やNOCYCLEを必ず付与し、深さ上限を明示してクエリの暴走を防ぐ。パスの文字連結は可読性に優れる一方で長大化してソート・ハッシュ領域を圧迫するため、業務キーの配列化や数値ハッシュの併用でメモリ効率を保つと安定する。

テストと検証の観点

小さな人工データで根・葉・孤立ノード・循環候補を意図的に混在させ、深さ上限やCYCLE検出が狙い通りに働くかを確認する。部分木集計は、基準となる非階層SQL（例えば単純なJOIN＋GROUP BY）と比較し、件数と合計が一致することを先に検証してから本番データへ適用する。探索順序に依存する要件では、SEARCH句やORDER SIBLINGS BYの有無で結果が変わるため、並びが機能要件の一部であるならテスト期待値も順序付きで固定化しておくと安全である。

まとめ

再帰WITHは探索の構造を明示して複合条件や停止制御を緻密に記述でき、CONNECT BYは専用擬似列で階層の定型処理を高速に表現できる。どちらを選んでも、起点の絞り込み、親子キーの索引、サイクル対策、深さ制限、そして必要十分な列だけを持ち回る節約が、正しさと性能の両立に直結する。分析関数や集計との合成を前提に設計すれば、ロジックをSQL一発に収斂させ、PL/SQL側の逐次処理や一時表を減らしながら、読みやすく保守しやすい階層ロジックを標準化できる。