Oracle 階層式查詢實戰：CONNECT BY 構建組織樹與遞歸函數/存儲過程

測試資料準備

CREATE TABLE Dept (
  DepartNO  VARCHAR2(10),
  DepartName VARCHAR2(20),
  TopNo     VARCHAR2(10)
);

INSERT INTO Dept VALUES('001','董事會','0');
INSERT INTO Dept VALUES('002','總裁辦','001');
INSERT INTO Dept VALUES('003','財務部','001');
INSERT INTO Dept VALUES('004','市場部','002');
INSERT INTO Dept VALUES('005','公關部','002');
INSERT INTO Dept VALUES('006','銷售部','002');
INSERT INTO Dept VALUES('007','分銷處','006');
INSERT INTO Dept VALUES('008','業務拓展處','004');
INSERT INTO Dept VALUES('009','銷售科','007');

CREATE TABLE TempDept (
  TempDeptNo      CHAR(4),
  TempDeptName    VARCHAR2(20),
  UpperTempDeptNo CHAR(4),
  UpperTempDeptName VARCHAR2(20),
  TempDeptLevel   NUMBER(4)
);

一、CONNECT BY 基礎語法

-- 自頂向下遍歷
SELECT DepartName FROM Dept
START WITH TopNo = '0'
CONNECT BY PRIOR DepartNO = TopNo;

-- 父子關係平鋪
SELECT A.DepartName AS Parent, B.DepartName AS Child
FROM Dept A, Dept B
WHERE A.DepartNO = B.TopNo;

-- 縮排顯示層級
SELECT LPAD(DepartNO, LEVEL*2+LENGTH(DepartNO), ' ') AS TreeView
FROM Dept
START WITH TopNo = '0'
CONNECT BY PRIOR DepartNO = TopNo;

-- 攜帶層級與父節點
SELECT DepartNO, DepartName, LEVEL || '\' || TopNo AS Path
FROM Dept
START WITH TopNo = '0'
CONNECT BY PRIOR DepartNO = TopNo;

二、遞歸函數：取得節點完整路徑

CREATE OR REPLACE FUNCTION GetLevel(p_TempNo VARCHAR2) RETURN VARCHAR2 IS
  v_Result VARCHAR2(4000);
  CURSOR c_Level(p_CurNo VARCHAR2) IS
    SELECT DepartNO FROM Dept
    START WITH TopNo = p_CurNo
    CONNECT BY PRIOR DepartNO = TopNo;
BEGIN
  FOR r IN c_Level(p_TempNo) LOOP
    v_Result := v_Result || '/' || r.DepartNO;
  END LOOP;
  RETURN v_Result;
END;
/

三、存儲過程：生成子樹結構至暫存表

CREATE OR REPLACE PROCEDURE GetAllSubDeptId(p_DeptId VARCHAR2) IS
  v_DeptNo      VARCHAR2(4);
  v_UpperNo     VARCHAR2(4);
  v_UpperNo2    VARCHAR2(4);
  v_UpperNo3    VARCHAR2(4);
  v_Level       NUMBER;
  v_Sql         VARCHAR2(200);
  CURSOR c_Dept IS
    SELECT DepartNO, TopNo FROM Dept WHERE DepartNO != p_DeptId;
BEGIN
  -- 清空暫存表
  EXECUTE IMMEDIATE 'TRUNCATE TABLE TempDept';
  DBMS_OUTPUT.PUT_LINE('Step 0: Truncated');

  -- 根節點
  INSERT INTO TempDept VALUES(p_DeptId, NULL, NULL, NULL, 0);
  DBMS_OUTPUT.PUT_LINE('Step 1: Root inserted');

  OPEN c_Dept;
  LOOP
    FETCH c_Dept INTO v_DeptNo, v_UpperNo;
    EXIT WHEN c_Dept%NOTFOUND;
    
    v_Level := 1;
    v_UpperNo3 := v_UpperNo;
    
    WHILE v_UpperNo IS NOT NULL LOOP
      IF v_UpperNo = p_DeptId THEN
        INSERT INTO TempDept VALUES(v_DeptNo, NULL, v_UpperNo3, NULL, v_Level);
        EXIT;
      ELSE
        v_UpperNo2 := v_UpperNo;
        SELECT TopNo INTO v_UpperNo FROM Dept WHERE DepartNO = v_UpperNo2;
        IF v_UpperNo2 != v_UpperNo THEN
          v_Level := v_Level + 1;
        END IF;
      END IF;
    END LOOP;
  END LOOP;
  CLOSE c_Dept;
  DBMS_OUTPUT.PUT_LINE('Completed');
END;
/

四、現代寫法：遞歸 CTE (11gR2+)

WITH OrgTree(DepartNO, DepartName, TopNo, Lvl, Path) AS (
  SELECT DepartNO, DepartName, TopNo, 1, CAST(DepartNO AS VARCHAR2(4000))
  FROM Dept WHERE TopNo = '0'
  UNION ALL
  SELECT d.DepartNO, d.DepartName, d.TopNo, ot.Lvl+1, ot.Path || '/' || d.DepartNO
  FROM Dept d JOIN OrgTree ot ON d.TopNo = ot.DepartNO
)
SELECT LPAD(' ', (Lvl-1)*2) || DepartName AS TreeView, Path
FROM OrgTree ORDER BY Path;

結語

CONNECT BY 是 Oracle 獨有的階層查詢語法，配合 LEVEL、SYS_CONNECT_BY_PATH、CONNECT_BY_ISLEAF 等偽欄位，可高效處理組織架構、物料清單、帳套層級等樹狀資料。11gR2 引入標準 SQL 遞歸 CTE，跨資料庫移植性更佳，建議新專案優先採用。