如何在oracle程序包中查找依赖项? [英] How to find dependencies inside an oracle package?
问题描述
我的问题是如何通过SQL查询或任何其他内部/外部工具找到内部oracle软件包依赖项.甚至有可能还是我应该仔细阅读代码并找出自我?
My question is how to find inside oracle package dependencies by SQL query or any other internal/external tool. Is it even possible or should I just go trough the code and find out myself?
示例:
- 我有一个软件包，其中包含4个过程
A，B，C，D和1个函数F. -
A是运行B和C过程的'main'过程. -
B和C过程使用功能F. - 过程
D是独立的(在elswhere中使用).
- I have a package which contains 4 procedures
A,B,C,Dand 1 functionF. Ais the 'main' procedure which runsBandCprocedures.- Function
Fis used byBandCprocedures. - Procedure
Dis independent (used elswhere).
现在，我想获得这样的结果:
Now I'd like to obtain something like this as a result:
STATUS PRC/FNC NAME PRC/FNC NAME USED INSIDE
------ ------------ ------------------------
MAIN A B, C
SLAVE B F
SLAVE C F
INDIVIDUAL D -
SLAVE F -
I've searched stack for an answer and the closest I've found would be: How do you programatically identify a stored procedure's dependencies? But it gives me only between-packages dependencies, not a dependencies 'inside' one package.
推荐答案
您可以在包如果您随后重新编译软件包，请在此处根据您的描述使用简单的轮廓:
If you then recompile your package, here using a simple outline based on your description:
create or replace package p42 as
procedure a;
procedure b;
procedure c;
procedure d;
function f return number;
end p42;
/
create or replace package body p42 as
procedure a is
begin
b;
c;
end a;
procedure b is
n number;
begin
n := f;
end b;
procedure c is
n number;
begin
n := f;
end c;
procedure d is
begin
null;
end d;
function f return number is
begin
return 42;
end f;
end p42;
/
然后您可以在user_identifiers视图中查看各种参考.使用文档中的示例获取概述:
then you can see the various references in the user_identifiers view. Using the example from the documentation to get an outline:
WITH v AS (
SELECT Line,
Col,
INITCAP(NAME) Name,
LOWER(TYPE) Type,
LOWER(USAGE) Usage,
USAGE_ID,
USAGE_CONTEXT_ID
FROM USER_IDENTIFIERS
WHERE Object_Name = 'P42'
AND Object_Type = 'PACKAGE BODY'
)
SELECT RPAD(LPAD(' ', 2*(Level-1)) ||
Name, 20, '.')||' '||
RPAD(Type, 20)||
RPAD(Usage, 20)
IDENTIFIER_USAGE_CONTEXTS
FROM v
START WITH USAGE_CONTEXT_ID = 0
CONNECT BY PRIOR USAGE_ID = USAGE_CONTEXT_ID
ORDER SIBLINGS BY Line, Col
/
IDENTIFIER_USAGE_CONTEXTS
-------------------------------------------------------------
P42................. package definition
A................. procedure definition
B............... procedure call
C............... procedure call
B................. procedure definition
N............... variable declaration
Number........ number datatype reference
N............... variable assignment
F............. function call
C................. procedure definition
N............... variable declaration
Number........ number datatype reference
N............... variable assignment
F............. function call
D................. procedure definition
F................. function definition
Number.......... number datatype reference
要更接近所需的内容，您仍然需要一个层次查询来查找每个调用另一个过程/函数的名称，因为您对(例如)赋值步骤不感兴趣，而仅对那些发生的位置感兴趣.
To get something closer to what you want you still need a hierarchical query to find the name of each procedure/function that calls another, since you aren't interested in (for example) the assignment steps, just where those happened.
作为起点，您可以执行以下操作:
As a starting point you could do:
select *
from (
select name, type, connect_by_root(name) as root_name,
connect_by_root(type) as root_type, connect_by_isleaf as isleaf
from user_identifiers
start with object_type = 'PACKAGE BODY'
and object_name = 'P42'
and type in ('FUNCTION', 'PROCEDURE')
and usage = 'DEFINITION'
connect by object_type = prior object_type
and object_name = prior object_name
and usage_context_id = prior usage_id
)
where type in ('FUNCTION', 'PROCEDURE');
NAME TYPE ROOT_NAME ROOT_TYPE ISLEAF
---- ------------------ --------- --------- ----------
A PROCEDURE A PROCEDURE 0
B PROCEDURE A PROCEDURE 1
C PROCEDURE A PROCEDURE 1
B PROCEDURE B PROCEDURE 0
F FUNCTION B PROCEDURE 1
C PROCEDURE C PROCEDURE 0
F FUNCTION C PROCEDURE 1
D PROCEDURE D PROCEDURE 1
F FUNCTION F FUNCTION 0
然后过滤叶节点并组合调用方:
then filter the leaf nodes and combine the callers:
select root_name, root_type,
listagg(case when name = root_name then null else name end, ', ')
within group (order by name) as callers
from (
select name, type, connect_by_root(name) as root_name,
connect_by_root(type) as root_type, connect_by_isleaf as isleaf
from user_identifiers
start with object_type = 'PACKAGE BODY'
and object_name = 'P42'
and type in ('FUNCTION', 'PROCEDURE')
and usage = 'DEFINITION'
connect by object_type = prior object_type
and object_name = prior object_name
and usage_context_id = prior usage_id
)
where type in ('FUNCTION', 'PROCEDURE')
and isleaf = 1
group by root_name, root_type;
ROOT_NAME ROOT_TYPE CALLERS
--------- --------- --------------------
A PROCEDURE B, C
B PROCEDURE F
C PROCEDURE F
D PROCEDURE
但这不显示F；您可以添加一个外部联接以获取该信息:
But that doesn't show F; you can add an outer join to get that:
with ui as (
select * from user_identifiers
where object_type = 'PACKAGE BODY'
and object_name = 'P42'
),
calls as (
select object_type, object_name, name, type, signature,
connect_by_root(name) as root_name,
connect_by_root(type) as root_type,
connect_by_root(signature) as root_signature,
connect_by_isleaf as isleaf
from ui
start with type in ('FUNCTION', 'PROCEDURE')
and usage = 'DEFINITION'
connect by usage_context_id = prior usage_id
and prior usage != 'CALL'
)
select ui.name, ui.type,
listagg(case when c.name = c.root_name then null else c.name end, ', ')
within group (order by c.name) as callers
from ui
left join calls c on c.object_type = ui.object_type
and c.object_name = ui.object_name
and c.root_type = ui.type
and c.root_name = ui.name
and c.root_signature = ui.signature
and c.type in ('FUNCTION', 'PROCEDURE')
and c.isleaf = 1
where ui.type in ('FUNCTION', 'PROCEDURE')
and ui.usage = 'DEFINITION'
group by ui.name, ui.type;
NAME TYPE CALLERS
---- ------------------ --------------------
A PROCEDURE B, C
B PROCEDURE F
C PROCEDURE F
D PROCEDURE
F FUNCTION
我很确定可以简化...
I'm pretty sure that can be simplified...
获取main/slave/independent标志有点棘手；您需要确定每种方式的含义(例如main发出呼叫但没有呼叫进入； independent呼叫没有进入或离开；奴隶其他一切)，可能还需要进一步的联接才能弄清楚.
Getting the main/slave/independent flag is a bit trickier; you need to decide what each of those means (e.g. main has calls going out but no calls coming in; independent has no calls in or out; slave everything else) possibly with further joins to figure things out.
因此，这是一个获取您一些信息的起点，并希望指向您可以探索的事物，以便以所需的格式获取所需的所有信息.
So this is a starting point that gets you some of the information and hopefully points to things to explore to get all the information you need, in the format you need it.
这篇关于如何在oracle程序包中查找依赖项?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
