Oracle 存儲過程遞歸生成樹狀編碼：層級動態前綴零填充實現

Oracle 存儲過程遞歸生成樹狀編碼：層級動態前綴零填充實現

業務常需為不定層級的樹狀結構生成編碼（如 1-01-003），規則：頂層節點編碼為 1，二級補 1 個 0（01），三級補 2 個 0（003），以此類推。本文提供純 PL/SQL 遞歸存儲過程，核心技巧：以連字符數量判斷層級、LPAD 動態填充前綴零、遞歸處理子節點。

一、資料模型

CREATE TABLE eval_index_type (
  id         VARCHAR2(32) PRIMARY KEY,  -- UUID
  type_code  VARCHAR2(256),             -- 生成編碼，如 1-01-003
  parent_id  VARCHAR2(32),              -- 父節點 UUID
  sort_num   NUMBER(4)                  -- 同級排序
);

二、核心邏輯解析

1. 層級計算： tree_level := (LENGTH(p_code) - LENGTH(REPLACE(p_code, '-', ''))) + 1
   父編碼 1 含 0 個 - → 層級 1；1-01 含 1 個 - → 層級 2
2. 前綴零填充： LPAD(TO_CHAR(cnt), tree_level, '0')
   層級 2 → LPAD(cnt, 2, '0') 產生 01、02...
   層級 3 → LPAD(cnt, 3, '0') 產生 001、002...
3. 遞歸終止： 子節點數為 0 時停止

三、完整存儲過程代碼

CREATE OR REPLACE PROCEDURE ini_tree_code (v_parent_id IN VARCHAR2) AS
  p_id       VARCHAR2(32);
  p_code     VARCHAR2(256);
  sub_num    NUMBER(4);
  tree_level NUMBER(4);
  cnt        NUMBER(4) := 0;

  CURSOR tree_cur(oid VARCHAR2) IS
    SELECT id, type_code
    FROM eval_index_type
    WHERE parent_id = oid
    ORDER BY sort_num;
BEGIN
  -- 取得父節點資訊
  SELECT id, type_code INTO p_id, p_code
  FROM eval_index_type
  WHERE id = v_parent_id;

  -- 遍歷直接子節點
  FOR cur_row IN tree_cur(p_id) LOOP
    cnt := cnt + 1;

    -- 計算當前層級（父層級 + 1）
    tree_level := (LENGTH(p_code) - LENGTH(REPLACE(p_code, '-', ''))) + 1;

    -- 生成新編碼：父編碼 - 填充後序號
    UPDATE eval_index_type
    SET type_code = p_code || '-' || LPAD(TO_CHAR(cnt), tree_level, '0')
    WHERE id = cur_row.id;

    -- 檢查是否有下一層子節點
    SELECT COUNT(*) INTO sub_num
    FROM eval_index_type
    WHERE parent_id = cur_row.id;

    IF sub_num > 0 THEN
      ini_tree_code(cur_row.id); -- 遞歸處理子樹
    END IF;
  END LOOP;
END ini_tree_code;
/

四、呼叫範例