萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 遞歸查詢包依賴:臨時表+層級遍歷獲取完整引用鏈

需求

給定一個 Package(如 CUX_SBU_COMMON),遞歸查找其依賴的所有 Package、Synonym、Table、Sequence,含多層級子依賴。

實作邏輯

  • 臨時表 CUX_COMMON_IMPORTS_TEMP 存放已遍歷節點(ATTRIBUTE1=物件名,ATTRIBUTE2=層級)。
  • 游標 P_CUR 查 DBA_DEPENDENCIES 取直接依賴(類型過濾)。
  • 游標 P_TEMP 讀取某層級節點。
  • 循環逐層展開,去重後插入臨時表,DBMS_OUTPUT 輸出層級結構。

核心代碼

DECLARE
  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
  P_ROOT_NAME := 'CUX_SBU_COMMON';
  INSERT INTO CUX_COMMON_IMPORTS_TEMP (ATTRIBUTE1, ATTRIBUTE2) VALUES (P_ROOT_NAME, P_LEVEL);
  P_COUNT := 1;

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

優化方向

  • 改用 CONNECT BY 或遞歸 CTE(11gR2+)單條 SQL 完成,效能更好。
  • 擴展依賴類型(VIEW、FUNCTION、PROCEDURE 等)。
  • 加入 ALL_DEPENDENCIES 兼容無 DBA 權限場景。
copyright © 萬盛學電腦網 all rights reserved