Oracle 遞歸存儲過程實現樹結構展示：父子遞歸與層級縮進完整案例

業務場景

表 IT 存儲層級數據（如論壇回覆、組織架構、菜單目錄），需按父子關係以樹形縮進格式輸出。

1. 建表與測試數據

CREATE TABLE IT (
  id     NUMBER PRIMARY KEY,
  cont   VARCHAR2(255),
  pid    NUMBER,           -- 父節點 ID，根節點 pid=0
  isleaf NUMBER(1),        -- 0: 非葉子(有子節點), 1: 葉子
  grade  NUMBER(2)         -- 層級深度（可選，由遞歸計算）
);

INSERT INTO IT VALUES (1, '大家好', 0, 0, 0);      -- 根
INSERT INTO IT VALUES (2, '你也好', 1, 0, 1);      -- 根的子
INSERT INTO IT VALUES (3, '願大家學業有成', 2, 1, 2); -- 子的子
COMMIT;

2. 遞歸存儲過程

CREATE OR REPLACE PROCEDURE p (
  v_pid   IN IT.pid%TYPE,
  v_level IN BINARY_INTEGER
) IS
  CURSOR c IS SELECT * FROM IT WHERE pid = v_pid;
  v_preStr VARCHAR2(1024) := '';
BEGIN
  -- 構建層級縮進字符串
  FOR i IN 1..v_level LOOP
    v_preStr := v_preStr || '**$';
  END LOOP;

  -- 遍歷子節點
  FOR v_IT IN c LOOP
    DBMS_OUTPUT.PUT_LINE(v_preStr || v_IT.cont);

    -- 遞歸處理非葉子節點
    IF v_IT.isleaf = 0 THEN
      p(v_IT.id, v_level + 1);
    END IF;
  END LOOP;
END p;
/

3. 執行與輸出

SQL> SET SERVEROUTPUT ON
SQL> EXEC p(0, 0);

大家好
**$你也好
**$**$願大家學業有成

PL/SQL procedure successfully completed.

核心技術點解析

擴展應用：生成 HTML `

• ` 樹形菜單

CREATE OR REPLACE PROCEDURE p_html (v_pid IN NUMBER, v_level IN INT) IS
  CURSOR c IS SELECT * FROM IT WHERE pid = v_pid;
BEGIN
  IF v_level = 0 THEN DBMS_OUTPUT.PUT_LINE('
'); END IF;

  FOR r IN c LOOP
    DBMS_OUTPUT.PUT_LINE('
' || r.cont);
    IF r.isleaf = 0 THEN
      p_html(r.id, v_level + 1);
    END IF;
    DBMS_OUTPUT.PUT_LINE('
');
  END LOOP;

  IF v_level = 0 THEN DBMS_OUTPUT.PUT_LINE('
'); END IF;
END;
/

注意事項

• 深度過大（> 1000）可能導致 ORA-06500: PL/SQL: storage error，生產環境建議改用 CONNECT BY 或迭代邏輯。
• 大量數據時 DBMS_OUTPUT 緩衝區可能溢出，調用 DBMS_OUTPUT.ENABLE(1000000) 擴大緩衝。