Oracle 遞歸查詢判斷人員部門是否屬於指定部門字符串或其子部門

Oracle 遞歸查詢判斷人員部門是否屬於指定部門字符串或其子部門

業務場景：消息推送目標為部門字符串 (如 '329|2'，以 | 分隔)，需判斷某用戶 (如 muyunfei) 所在部門 (如 326) 是否在目標部門清單中，**或屬於目標部門的任意下級子部門**。部門結構採用遞歸關聯 (PARENT_ID 指向父部門)。

表結構假設

CREATE TABLE wx_party (
  party_id   VARCHAR2(20) PRIMARY KEY,  -- 部門 ID
  parent_id  VARCHAR2(20),              -- 父部門 ID，根節點為 NULL
  party_name VARCHAR2(100)
);

CREATE TABLE wx_contacts (
  user_id    VARCHAR2(20) PRIMARY KEY,
  department VARCHAR2(20) REFERENCES wx_party(party_id)
);

解決方案：PL/SQL 函數封裝

CREATE OR REPLACE FUNCTION check_user_in_dept(
  p_target_depts VARCHAR2,  -- 目標部門字符串，如 '329|2'
  p_user_id      VARCHAR2   -- 用戶 ID
) RETURN VARCHAR2 AS
  /*
    功能：判斷用戶所在部門(含遞歸父部門)是否命中目標部門列表
    返回：'1' 命中，'0' 未命中
  */
  v_len      NUMBER;
  v_start    NUMBER := 1;
  v_idx      NUMBER;
  v_part     VARCHAR2(200);
  v_matched  BOOLEAN := FALSE;
BEGIN
  -- 1. 遞歸取得用戶部門及所有祖先部門
  FOR rec IN (
    SELECT party_id FROM wx_party
    START WITH party_id = (SELECT department FROM wx_contacts WHERE user_id = p_user_id)
    CONNECT BY PRIOR party_id = parent_id
  ) LOOP
    -- 2. 解析目標部門字符串 (以 | 分隔)
    v_len := LENGTH(p_target_depts);
    v_start := 1;
    WHILE v_start <= v_len LOOP
      v_idx := INSTR(p_target_depts, '|', v_start);
      IF v_idx = 0 THEN
        v_part := SUBSTR(p_target_depts, v_start);
        v_start := v_len + 1;
      ELSE
        v_part := SUBSTR(p_target_depts, v_start, v_idx - v_start);
        v_start := v_idx + 1;
      END IF;
      -- 3. 比對
      IF v_part = rec.party_id THEN
        RETURN '1';
      END IF;
    END LOOP;
  END LOOP;
  RETURN '0';
END;
/
-- 測試
SELECT check_user_in_dept('329|2', 'muyunfei') FROM dual;  -- 若 muyunfei 部門 329 或其父部門含 329/2 則返回 1

純 SQL 寫法 (11gR2+ 遞歸 WITH)

WITH user_depts AS (
  -- 用戶部門及所有祖先
  SELECT party_id FROM wx_party
  START WITH party_id = (SELECT department FROM wx_contacts WHERE user_id = 'muyunfei')
  CONNECT BY PRIOR party_id = parent_id
),
target_depts AS (
  -- 將 '329|2' 拆分為行
  SELECT TRIM(REGEXP_SUBSTR('329|2', '[^|]+', 1, LEVEL)) AS dept_id
  FROM dual
  CONNECT BY LEVEL <= REGEXP_COUNT('329|2', '\|') + 1
)
SELECT CASE WHEN EXISTS (
  SELECT 1 FROM user_depts ud JOIN target_depts td ON ud.party_id = td.dept_id
) THEN '1' ELSE '0' END AS matched FROM dual;

效能優化建議

• 📌 為 wx_party(parent_id) 建立索引，加速 CONNECT BY 遞歸。
• 📌 若部門樹深度固定 (如 ≤ 5 層)，可考慮 物化路徑 (PATH VARCHAR2(4000) 存儲 /1/329/326/) + LIKE '/%/329/%' 判斷，避免遞歸開銷。
• 📌 目標部門字符串若固定長度，可用 IN 列表改寫：WHERE party_id IN ('329','2')。
• 📌 高頻調用場景建議將函數改為 DETERMINISTIC 或建立 結果緩存 (RESULT_CACHE)。

擴展：返回匹配的部門路徑

CREATE OR REPLACE FUNCTION get_matched_dept_path(
  p_target_depts VARCHAR2, p_user_id VARCHAR2
) RETURN VARCHAR2 AS
  v_path VARCHAR2(4000);
BEGIN
  SELECT SYS_CONNECT_BY_PATH(party_id, '/') INTO v_path
  FROM wx_party
  WHERE party_id IN (SELECT TRIM(REGEXP_SUBSTR(p_target_depts, '[^|]+', 1, LEVEL)) FROM dual
                     CONNECT BY LEVEL <= REGEXP_COUNT(p_target_depts, '\|') + 1)
  START WITH party_id = (SELECT department FROM wx_contacts WHERE user_id = p_user_id)
  CONNECT BY PRIOR party_id = parent_id;
  RETURN v_path;
EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL;
END;
/

總結

利用 Oracle 強大的 CONNECT BY / 遞歸 WITH 配合字符串拆分 (REGEXP_SUBSTR/INSTR+SUBSTR)，可高效解決「部門樹向上匹配目標集合」問題。PL/SQL 函數封裝適合業務層調用；純 SQL 寫法適合報表、ETL 直接嵌入。根據調用頻率、部門樹規模選擇遞歸查詢或物化路徑優化。