Oracle 遞歸查詢包依賴關係：PL/SQL 獲取指定 Package 所有參考物件

需求：給定一個 Package 名稱，遞歸找出其直接/間接依賴的所有 Package、Synonym、Table、Sequence。本文提供基於 DBA_DEPENDENCIES 的 PL/SQL 實作，採用暫存表記錄已遍歷節點與層級，避免無限迴圈。

核心邏輯

1. 建立暫存表 cux_common_imports_temp(attribute1 VARCHAR2(30), attribute2 NUMBER) 存放 (物件名, 層級)。
2. 將根 Package 寫入第 0 層。
3. 迴圈：讀取目前層級所有節點，查詢其依賴（REFERENCED_TYPE IN ('PACKAGE','SYNONYM','TABLE','SEQUENCE') 且 TYPE IN ('PACKAGE','PACKAGE BODY')）。
4. 若依賴為 Package 且暫存表無此名稱，寫入下一層。
5. 直到某層無新節點為止。

完整 PL/SQL 程式碼

DECLARE
  -- 取得指定 Package 的直接依賴
  CURSOR p_cur(p_name VARCHAR2) IS
    SELECT dd.name, dd.type, dd.referenced_name, dd.referenced_type
    FROM dba_dependencies dd
    WHERE dd.referenced_type IN ('PACKAGE','SYNONYM','TABLE','SEQUENCE')
      AND dd.type IN ('PACKAGE','PACKAGE BODY')
      AND dd.name = p_name;

  -- 讀取暫存表某層級的節點
  CURSOR p_temp(l_level NUMBER) IS
    SELECT attribute1, attribute2
    FROM cux_common_imports_temp
    WHERE attribute2 = l_level;

  p_root_name VARCHAR2(30);
  p_level     NUMBER := 0;
  p_count     NUMBER := 0;
BEGIN
  -- 1. 根節點入表
  p_root_name := 'CUX_SBU_COMMON';  -- 目標 Package
  INSERT INTO cux_common_imports_temp (attribute1, attribute2)
  VALUES (p_root_name, p_level);
  p_count := 1;

  -- 2. 逐層展開
  WHILE p_count != 0 LOOP
    FOR p_loop IN p_temp(p_level) LOOP
      p_level := p_level + 1;
      p_root_name := p_loop.attribute1;

      FOR p_rec IN p_cur(p_root_name) LOOP
        IF p_rec.referenced_type = 'PACKAGE' THEN
          -- 去重
          SELECT COUNT(*) INTO p_count
          FROM cux_common_imports_temp
          WHERE attribute1 = p_rec.referenced_name;

          IF p_count = 0 THEN
            INSERT INTO cux_common_imports_temp (attribute1, attribute2)
            VALUES (p_rec.referenced_name, p_level);
          END IF;

          -- 輸出層級結構
          DBMS_OUTPUT.PUT_LINE(p_level || '-Name:' || p_rec.referenced_name
                               || '-Type:' || p_rec.referenced_type);
        END IF;
      END LOOP;
    END LOOP;

    -- 檢查下一層是否為空
    SELECT COUNT(*) INTO p_count
    FROM cux_common_imports_temp
    WHERE attribute2 = p_level;
  END LOOP;
END;
/

執行結果範例

1-Name:CU_GLOBAL_UTILS-Type:PACKAGE
1-Name:CU_SECURITY_PKG-Type:PACKAGE
2-Name:CU_AUDIT_PKG-Type:PACKAGE
2-Name:CU_ENCRYPT_PKG-Type:PACKAGE
3-Name:CU_KEY_MGMT-Type:PACKAGE

優化方向

• 改用 WITH RECURSIVE (11gR2+) 純 SQL 遞歸，效能更佳。
• 加入 DEPTH 參數限制最大遞歸深度，防止異常鏈條。
• 輸出轉存永久表，供下游影響分析報表使用。

純 SQL 版本（11gR2+）

WITH dep_tree(obj_name, ref_name, ref_type, lvl) AS (
  SELECT name, referenced_name, referenced_type, 1
  FROM dba_dependencies
  WHERE name = 'CUX_SBU_COMMON'
    AND type IN ('PACKAGE','PACKAGE BODY')
    AND referenced_type IN ('PACKAGE','SYNONYM','TABLE','SEQUENCE')
  UNION ALL
  SELECT d.name, d.referenced_name, d.referenced_type, dt.lvl + 1
  FROM dba_dependencies d
  JOIN dep_tree dt ON d.name = dt.ref_name
  WHERE d.type IN ('PACKAGE','PACKAGE BODY')
    AND d.referenced_type IN ('PACKAGE','SYNONYM','TABLE','SEQUENCE')
    AND dt.lvl < 10  -- 防無限遞歸
)
SEARCH DEPTH FIRST BY obj_name SET order_col
SELECT lvl, ref_name, ref_type FROM dep_tree;

適用場景

• 上線前影響範圍評估。
• 重構舊系統，梳理模組耦合度。
• 自動化文件產生：Package 依賴圖譜。