Oracle 樹狀結構遞迴查詢實戰：利用預存程序實現層級縮排顯示

前言

在關係型資料庫中儲存樹狀結構（如組織架構、選單分類、評論回覆）是常見需求。Oracle 雖支援 CONNECT BY 與遞迴 CTE（11gR2+），但透過 PL/SQL 預存程序配合 DBMS_OUTPUT 實現靈活的層級縮排輸出，對於理解遞迴邏輯、報表產生或教學演示極具價值。本文完整示範四步驟建立樹狀資料並以預存程序遞迴顯示。

步驟一：建立樹狀資料表

關鍵欄位說明：

• ID：節點唯一識別碼（主鍵）
• CONT：節點顯示內容
• PID：父節點 ID（根節點 PID=0 或 NULL）
• ISLEAF：0=非葉子節點（有子節點），1=葉子節點
• GRADE：層級深度（根節點為 0）

CREATE TABLE IT (
    ID      NUMBER PRIMARY KEY,
    CONT    VARCHAR2(255),
    PID     NUMBER,
    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;

-- 驗證資料
SELECT * FROM IT;

步驟三：建立遞迴顯示預存程序

核心邏輯：

1. 接收父節點 ID (v_pid) 與當前層級 (v_level)
2. 根據層級產生縮排字串 (v_preStr)
3. 查詢該父節點下的所有子節點
4. 輸出節點內容；若為非葉子節點 (ISLEAF=0)，遞迴呼叫自身處理下一層

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;
/

步驟四：啟用輸出並執行

在 SQL*Plus、SQL Developer 或 PL/SQL Developer 的 Command Window 執行：

SET SERVEROUTPUT ON;  -- 必須開啟，否則看不到輸出
EXEC p(0, 0);         -- 從根節點 (PID=0) 第 0 層開始

執行結果預期

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

進階與替代方案

1. 純 SQL 寫法（Oracle 11gR2+ 推薦）

使用遞迴 CTE (WITH ... RECURSIVE) 或 CONNECT BY 更高效、可直接整合應用層：

-- 使用 CONNECT BY (Oracle 傳統語法)
SELECT LPAD(' ', 2*(LEVEL-1)) || CONT AS TREE_VIEW
FROM IT
START WITH PID = 0
CONNECT BY PRIOR ID = PID
ORDER SIBLINGS BY ID;

2. 大數據量效能優化

• 遞迴程序屬逐行處理（Row-by-row），大樹結構效能較差。
• 生產環境建議：物化路徑、巢狀集合 或 遞迴 CTE 配合索引。
• 若必須用 PL/SQL，考慮 BULK COLLECT 減少 Context Switch。

總結

透過四個步驟完成樹狀資料的建立、資料填充、遞迴程序編寫與執行輸出。此範例清晰展示 PL/SQL 遞迴邏輯與 DBMS_OUTPUT 的配合使用，適合教學、小規模報表或原型驗證。生產環境大樹結構請優先考慮集合式 SQL 寫法。