【Oracle】再帰WITH句の使い方｜SEARCH・CYCLE・CONNECT BYとの違い

WITH query_name (id, parent_id, depth) AS (
  -- アンカー部: 探索の起点
  SELECT id, parent_id, 1
  FROM target_table
  WHERE parent_id IS NULL

  UNION ALL

  -- 再帰部: 1階層下へ進む
  SELECT child.id, child.parent_id, parent.depth + 1
  FROM target_table child
  JOIN query_name parent
    ON child.parent_id = parent.id
)
SELECT id, parent_id, depth
FROM query_name;

CREATE TABLE app.employees (
  employee_id   NUMBER       CONSTRAINT pk_employees PRIMARY KEY,
  manager_id    NUMBER,
  employee_name VARCHAR2(100) NOT NULL,
  CONSTRAINT fk_employees_manager
    FOREIGN KEY (manager_id)
    REFERENCES app.employees (employee_id)
);

CREATE INDEX app.idx_employees_manager
    ON app.employees (manager_id);

INSERT INTO app.employees VALUES (1, NULL, '社長');
INSERT INTO app.employees VALUES (2, 1,    '営業部長');
INSERT INTO app.employees VALUES (3, 1,    '開発部長');
INSERT INTO app.employees VALUES (4, 2,    '営業担当A');
INSERT INTO app.employees VALUES (5, 2,    '営業担当B');
INSERT INTO app.employees VALUES (6, 3,    '開発担当A');
COMMIT;

WITH org_tree (
  employee_id,
  manager_id,
  employee_name,
  root_employee_id,
  depth,
  path_text
) AS (
  SELECT
    e.employee_id,
    e.manager_id,
    e.employee_name,
    e.employee_id,
    1,
    CAST(e.employee_name AS VARCHAR2(4000))
  FROM app.employees e
  WHERE e.manager_id IS NULL

  UNION ALL

  SELECT
    child.employee_id,
    child.manager_id,
    child.employee_name,
    parent.root_employee_id,
    parent.depth + 1,
    parent.path_text || ' > ' || child.employee_name
  FROM app.employees child
  JOIN org_tree parent
    ON child.manager_id = parent.employee_id
)
SEARCH DEPTH FIRST BY employee_name, employee_id SET search_order
CYCLE employee_id SET is_cycle TO 'Y' DEFAULT 'N'
SELECT
  employee_id,
  manager_id,
  employee_name,
  root_employee_id,
  depth,
  path_text,
  is_cycle
FROM org_tree
WHERE is_cycle = 'N'
ORDER BY search_order;

WITH org_tree (employee_id, manager_id, depth) AS (
  SELECT employee_id, manager_id, 1
  FROM app.employees
  WHERE employee_id = :start_employee_id

  UNION ALL

  SELECT
    child.employee_id,
    child.manager_id,
    parent.depth + 1
  FROM app.employees child
  JOIN org_tree parent
    ON child.manager_id = parent.employee_id
  WHERE parent.depth < 100
)
CYCLE employee_id SET is_cycle TO 'Y' DEFAULT 'N'
SELECT employee_id, manager_id, depth, is_cycle
FROM org_tree
ORDER BY depth, employee_id;

WITH bom_tree (
  root_item,
  item,
  required_qty,
  depth,
  path_text
) AS (
  SELECT
    b.parent_item,
    b.child_item,
    b.quantity,
    1,
    CAST(
      b.parent_item || '/' || b.child_item
      AS VARCHAR2(4000)
    )
  FROM app.bom b
  WHERE b.parent_item = :root_item

  UNION ALL

  SELECT
    parent.root_item,
    b.child_item,
    parent.required_qty * b.quantity,
    parent.depth + 1,
    parent.path_text || '/' || b.child_item
  FROM app.bom b
  JOIN bom_tree parent
    ON b.parent_item = parent.item
  WHERE parent.depth < 50
)
CYCLE item SET is_cycle TO 'Y' DEFAULT 'N'
SELECT
  tree.root_item,
  SUM(tree.required_qty * cost.unit_cost) AS rolled_up_cost
FROM bom_tree tree
JOIN app.item_cost cost
  ON cost.item = tree.item
WHERE tree.is_cycle = 'N'
  AND NOT EXISTS (
    SELECT 1
    FROM app.bom child
    WHERE child.parent_item = tree.item
  )
GROUP BY tree.root_item;

WITH approval_route (
  employee_id,
  role_code,
  next_employee_id,
  depth,
  path_text
) AS (
  SELECT
    f.employee_id,
    f.role_code,
    f.next_employee_id,
    1,
    CAST('/' || f.employee_id || '/' AS VARCHAR2(4000))
  FROM app.approval_flow f
  WHERE f.employee_id = :requester_id

  UNION ALL

  SELECT
    next_step.employee_id,
    next_step.role_code,
    next_step.next_employee_id,
    current_step.depth + 1,
    current_step.path_text || next_step.employee_id || '/'
  FROM approval_route current_step
  JOIN app.approval_flow next_step
    ON next_step.employee_id = current_step.next_employee_id
  WHERE current_step.role_code <> 'APPROVER'
    AND current_step.depth < 20
)
CYCLE employee_id SET is_cycle TO 'Y' DEFAULT 'N'
SELECT employee_id, role_code, depth, path_text
FROM (
  SELECT
    route.*,
    ROW_NUMBER() OVER (
      ORDER BY depth, employee_id
    ) AS row_no
  FROM approval_route route
  WHERE role_code = 'APPROVER'
    AND is_cycle = 'N'
)
WHERE row_no = 1;

-- CONNECT BYで組織ツリーを展開する例
SELECT
  employee_id,
  manager_id,
  employee_name,
  LEVEL AS depth,
  CONNECT_BY_ROOT employee_id AS root_employee_id,
  SYS_CONNECT_BY_PATH(employee_name, ' > ') AS path_text
FROM app.employees
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR employee_id = manager_id
ORDER SIBLINGS BY employee_name, employee_id;

WITH ancestors (
  employee_id,
  manager_id,
  employee_name,
  distance_from_start
) AS (
  SELECT
    employee_id,
    manager_id,
    employee_name,
    0
  FROM app.employees
  WHERE employee_id = :employee_id

  UNION ALL

  SELECT
    manager.employee_id,
    manager.manager_id,
    manager.employee_name,
    child.distance_from_start + 1
  FROM ancestors child
  JOIN app.employees manager
    ON manager.employee_id = child.manager_id
)
CYCLE employee_id SET is_cycle TO 'Y' DEFAULT 'N'
SELECT employee_id, employee_name, distance_from_start
FROM ancestors
WHERE is_cycle = 'N'
ORDER BY distance_from_start;

-- 上司から部下へたどる結合を支える
CREATE INDEX app.idx_employees_manager
    ON app.employees (manager_id);

-- 親部品から子部品へたどる結合を支える
CREATE INDEX app.idx_bom_parent
    ON app.bom (parent_item);