首页 文章

如何在oracle包中找到依赖项？

提问于
浏览 次
2

我的问题是如何通过SQL查询或任何其他内部/外部工具查找内部oracle包依赖项 . 它是否可能，或者我应该通过代码并找出自己？

示例案例：

  • 我有一个包含4个程序的包 A ， B ， C ， D 和1个函数 F .

  • A 是'main'程序，它运行 B 和 C 程序 .

  • 函数 F 由 B 和 C 程序使用 .

  • 过程 D 是独立的（在elswhere使用） .

现在我想得到这样的结果：

STATUS      PRC/FNC NAME  PRC/FNC NAME USED INSIDE
------      ------------  ------------------------
MAIN        A             B, C
SLAVE       B             F
SLAVE       C             F
INDIVIDUAL  D             -
SLAVE       F             -

我发现've searched stack for an answer and the closest I'将是：How do you programatically identify a stored procedure's dependencies?但它只给我一个包之间的依赖关系，而不是依赖关系'inside'一个包 .

oracle plsql oracle11g plsqldeveloper

1 回答

  • 2

    您可以从包with PL/Scope中获取对程序的调用，从以下开始：

    alter session set plscope_settings = 'IDENTIFIERS:ALL';

    如果您随后重新编译包，请根据您的描述使用简单的大纲：

    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 视图中看到各种引用 . 使用文档中的示例来获取大纲：

    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

    为了得到更接近你想要的东西，你仍然需要一个分层查询来查找调用另一个的每个过程/函数的名称，因为你不感兴趣（例如）分配步骤，就在那些发生的地方 .

    作为起点，您可以：

    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

    然后过滤叶节点并组合调用者：

    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 ;你可以添加一个外部联接来获得：

    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

    我很确定可以简化......

    获得主/从/独立标志有点棘手;你需要决定每一个意味着什么（例如，主要呼叫已经呼出，但没有呼叫进入;独立呼叫进出;其他所有其他呼叫）可能需要进一步加入以解决问题 .

    因此，这是一个起点，可以为您提供一些信息，并希望以您需要的格式指出要探索的内容以获取所需的所有信息 .

    回复于

相关问题