如何在oracle包中查找依赖项?

2021-12-24 00:00:00 oracle11g oracle plsql plsqldeveloper

我的问题是如何通过 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' 过程.
  • 函数F被B和C过程使用.
  • 过程 D 是独立的(在 elswhere 中使用).
  • I have a package which contains 4 procedures A, B ,C, D and 1 function F.
  • A is the 'main' procedure which runs B and C procedures.
  • Function F is used by B and C procedures.
  • Procedure D is 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.

推荐答案

您可以获取对包内过程的调用 带PL/Scope，以:

You can get the calls to procedures within a package with PL/Scope, starting with:

alter session set plscope_settings = 'IDENTIFIERS:ALL';

如果你然后重新编译你的包，这里使用一个基于你的描述的简单大纲:

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 有调用出去但没有调用进来；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.

相关文章