如何从 Oracle 10G PL/SQL 函数和过程中查找所有表引用？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3321629/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to find all table references from Oracle 10G PL/SQL functions and procedures?
提问by Roman Kagan
How to find all table references from Oracle 10G PL/SQL functions and procedures?
如何从 Oracle 10G PL/SQL 函数和过程中查找所有表引用？
I definitely can execute the following SQL statement:
我绝对可以执行以下 SQL 语句：
select * from dba_source where text like '%tbl_c%'
but I wonder how to find all functions that call functions that refer to table used. For example I can have a function A that calls function B that uses table tbl_c. If I'll execute aforementioned SQL I'll find funciton B but then I have to execute another SQL to find A. As you know the cyclomatic complexity could be 3,4,5 levels deep or even greater.
但我想知道如何找到所有调用引用所用表的函数的函数。例如，我可以有一个函数 A 调用使用表 tbl_c 的函数 B。如果我将执行上述 SQL，我将找到函数 B，但随后我必须执行另一个 SQL 才能找到 A。如您所知，圈复杂度可能是 3、4、5 级甚至更高。
Greatly appreciate in advance for your explanation.
非常感谢您的解释。
回答by Brian
dba_dependenciesis where to start. Example:
dba_dependencies是开始的地方。例子：
SELECT owner
|| '.'
|| NAME
|| ' ('
|| DECODE (TYPE,
'MATERIALIZED VIEW', 'MV',
'DIMENSION', 'DIM',
'EVALUATION CONTXT', 'EVALCTXT',
'PACKAGE BODY', 'PKGBDY',
'CUBE.DIMENSION', 'CUBE.DIM',
TYPE
)
|| ')' objdep,
referenced_name
|| ' ('
|| DECODE (referenced_type,
'EVALUATION CONTXT', 'EVALCTXT',
'NON-EXISTENT CONTXT', 'NO-EXIST',
'PACKAGE BODY', 'PKGBDY',
'CUBE.DIMENSION', 'CUBE.DIM',
referenced_type
)
|| ')' refr
FROM dba_dependencies
WHERE owner = :usn
ORDER BY objdep;
回答by N. Gasparotto
I'd prefer to use ALL_DEPENDENCIESwhen looking for object references rather than ALL_SOURCE. Adding to a hierarchical query, you could get what ever you need.
我更愿意使用ALL_DEPENDENCIES找对象的引用，而不是ALL_SOURCE时。添加到分层查询中，您可以获得所需的任何内容。
