oracle 獲取某個(gè)包 依賴(lài)的所有對(duì)象包括其子對(duì)象

使用了一個(gè)一個(gè)臨時(shí)表 記錄了已經(jīng)遍歷的 節(jié)點(diǎn)

同時(shí) 使用了層數(shù)來(lái)記錄已經(jīng)遍歷的 包

效率一般，可以改動(dòng)性大

declare

-- 獲取相應(yīng)的 某個(gè)程序包 所需要應(yīng)用的包

cursor p_cur(p_name varchar2) is

select dd.name, dd.type, dd.referenced_name, dd.referenced_type

from dba_dependencies dd

where 1 = 1

and 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

--將根節(jié)點(diǎn) 放入到表中

p_root_name := 'CUX_SBU_COMMON';

insert into cux_common_imports_temp

(attribute1, attribute2)

values

(p_root_name, p_level);

p_count := 1;

--循環(huán)這一層的 節(jié)點(diǎn) 并獲得其子節(jié)點(diǎn)

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

--如果表內(nèi) 沒(méi)有這個(gè)程序就 加入到 臨時(shí)表中

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;

--輸出相應(yīng)的 子節(jié)點(diǎn)信息

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;

更多信息請(qǐng)查看IT技術(shù)專(zhuān)欄