給定一個 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 完成,效能更好。ALL_DEPENDENCIES 兼容無 DBA 權限場景。