SQL查询-包x的公共和私有对象 [英] SQL query - public and private objects of package x

问题描述

我想构建一个查询，该查询列出PL/SQL包的可用对象(过程和函数)，并带有附加列privacy_level，以显示该对象在body和spec中是否都存在(是公共的)，或者仅在身体中(是私人的).

I want to build a query that lists the available objects(procedures and functions) of a PL/SQL package, with an additional column, privacy_level, that shows whether that object exists in both body and spec(is public), or in the body only(is private).

浏览一小段之后，我仅找到此查询(不返回任何行)

After browsing a little, I only found this query (which returns no rows)

select name, 
       type,
       decode(usage,'DECLARATION', 'body only', 'DEFINITION', 'spec and body', usage) defined_on,
       line body_line
  from all_identifiers ui
  where type in ('PROCEDURE', 'FUNCTION')
  and usage_context_id = (select usage_id
    from user_identifiers
      where object_name = ui.object_name
        and object_type = ui.object_type
        and usage_context_id = 0)
  --and object_name = 'PACKAGE_NAME'
  --and object_type = 'PACKAGE BODY'
  order by name;

谢谢.

推荐答案

您的查询是针对all_identifiers的，而all_identifiers是

Your query is against all_identifiers, which is part of the PL/Scope tool. That system view only shows data for objects that were compiled with the appropriate settings:

默认情况下，PL/Scope不收集PL/SQL源程序中标识符的数据.要让PL/Scope为PL/SQL源程序中的所有标识符(包括程序包主体中的标识符)收集数据，请将PL/SQL编译参数PLSCOPE_SETTINGS设置为'IDENTIFIERS:ALL'.

如果我使用默认设置创建包含私有和公共子程序的软件包:

If I create a package with private and public subprograms with the default settings:

create package p42 as
  procedure public_proc;
  function public_func return number;
end p42;
/

create package body p42 as
  procedure public_proc is
  begin
    null;
  end public_proc;

  function public_func return number is
  begin
    return 42;
  end public_func;

  procedure private_proc is
  begin
    null;
  end private_proc;

  function private_func return number is
  begin
    return 42;
  end private_func;
end p42;
/

然后您使用查询无法找到任何内容:

then your using your query finds nothing:

select name, 
...
  order by name;

no rows selected

如果在创建它们之前设置了PLSCOPE_SETTINGS，则将显示它们；假设您不想完全重新创建所有内容，则可以在更改设置后重新编译软件包:

If I had set PLSCOPE_SETTINGS before creating them then they would be shown; assuming you don't want to fully recreate everything you can recompile your packages after changing the setting:

alter session set plscope_settings = 'IDENTIFIERS:ALL';

Session altered.

alter package p42 compile;

Package P42 altered.

现在您的查询得到:

NAME                           TYPE               DEFINED_ON     BODY_LINE
------------------------------ ------------------ ------------- ----------
PRIVATE_FUNC                   FUNCTION           body only             17
PRIVATE_PROC                   PROCEDURE          body only             12
PUBLIC_FUNC                    FUNCTION           body only              3
PUBLIC_FUNC                    FUNCTION           spec and body          7
PUBLIC_PROC                    PROCEDURE          body only              2
PUBLIC_PROC                    PROCEDURE          spec and body          2

6 rows selected. 

利用私有子程序仅作为主体的一部分出现在表中这一事实，类似这样的事情可能更接近于您想要实现的目标:

Something like this might be closer to what you said you were trying to achieve though, using the fact that private subprograms only appear in the table as part of the body:

select object_name, type, name,
  case min(object_type) when 'PACKAGE BODY' then 'PRIVATE' else 'PUBLIC' end as privacy_level
from user_identifiers
where object_type in ('PACKAGE', 'PACKAGE BODY')
and type in ('FUNCTION', 'PROCEDURE')
group by object_name, type, name
order by object_name, name;

OBJECT_NAME     TYPE               NAME            PRIVACY
--------------- ------------------ --------------- -------
P42             FUNCTION           PRIVATE_FUNC    PRIVATE
P42             PROCEDURE          PRIVATE_PROC    PRIVATE
P42             FUNCTION           PUBLIC_FUNC     PUBLIC 
P42             PROCEDURE          PUBLIC_PROC     PUBLIC 

如果要查找所有对象的此信息，则可以分别重新编译它们，也可以重新编译整个模式:

If you want to find this information for all objects you can either recompile them individually, or recompile the whole schema:

exec dbms_utility.compile_schema(user);

但是请注意，如果您的任何程序包都处于状态，则现有会话在调用它们时可能会出错(因此您可能希望在停机期间执行此操作)，并且如果任何人在不显式更改其会话的情况下进行了任何编译，则这些对象将不会被查询显示(除非您在数据库级别进行设置，这可能会导致过多的开销).

But be aware that if any of your package has state then existing sessions may error when they call them (so you might want to do that during downtime), and if anyone compiles anything without explicitly altering their session then those objects will not be shown by you query (unless you set it at database level, which may be too much overhead).

这篇关于SQL查询-包x的公共和私有对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！