ALL_PROCEDURES视图未显示PROCEDURE_NAME [英] ALL_PROCEDURES view doesn't show the PROCEDURE_NAME

问题描述

为什么在user_procedures视图中看不到我的程序?为什么all_procedures视图上的procedure_name过滤器不返回任何行.

Why can't I see my procedure in user_procedures view? Why is the procedure_name filter on all_procedures view returns no rows.

这个问题主要是为了帮助那些正在寻找类似问题的人.我希望任何寻求此类问题的人都能在这里找到答案.

This question is primarily to aid those who are searching for a similar question. I hope that anyone looking for such question would find a answer here.

测试用例:

SQL> show user
USER is "LALIT"
SQL> CREATE OR REPLACE
  2  PROCEDURE new_proc
  3  AS
  4  BEGIN
  5    NULL;
  6  END;
  7  /

Procedure created.

SQL>
SQL> SELECT owner,
  2    object_name,
  3    procedure_name,
  4    object_type
  5  FROM all_procedures
  6  WHERE owner='LALIT'
  7  AND procedure_name='NEW_PROC';

no rows selected

SQL>

推荐答案

来自文档，

ALL_PROCEDURES列出了所有功能和过程，以及 相关属性.例如，ALL_PROCEDURES指示是否 功能是否已管线化，并行启用或聚合 功能.如果函数是流水线函数或聚合函数，则 相关的实现类型(如果有)也被识别.

ALL_PROCEDURES lists all functions and procedures, along with associated properties. For example, ALL_PROCEDURES indicates whether or not a function is pipelined, parallel enabled or an aggregate function. If a function is pipelined or an aggregate function, the associated implementation type (if any) is also identified.

它并不清楚是否会以相同的方式列出 STAND ALONE PROCEDURE (单独程序)和包裹在 PACKAGE 中的过程，还是会以不同的方式列出.由于procedure_name不会列出上述问题的测试用例中看到的独立过程的名称.

It doesn't clarify whether it would list a STAND ALONE PROCEDURE and a procedure wrapped in a PACKAGE the same way or does it consider it differently. Since, the procedure_name would not list the name of a stand alone procedure as seen in the test case in the question above.

PROCEDURE_NAME列将仅具有属于PACKAGE的过程的过程名称.对于STAND ALONE PROCEDURES，您需要使用 OBJECT_NAME .

PROCEDURE_NAME column will only have the procedure name for the procedures which are part of a PACKAGE. For STAND ALONE PROCEDURES you need to use OBJECT_NAME.

SQL> show user
USER is "LALIT"
SQL> CREATE OR REPLACE
  2  PROCEDURE new_proc
  3  AS
  4  BEGIN
  5    NULL;
  6  END;
  7  /

Procedure created.

SQL>
SQL> SELECT owner,
  2    object_name,
  3    procedure_name,
  4    object_type
  5  FROM all_procedures
  6  WHERE owner='LALIT'
  7  AND object_name='NEW_PROC';

OWNER OBJECT_NAME     PROCEDURE_NAME  OBJECT_TYPE
----- --------------- --------------- ---------------
LALIT NEW_PROC                        PROCEDURE

SQL>

只有将procedure_name包装在程序包中，您才能使用procedure_name获取过程列表.

You could get the list of procedures using procedure_name only if it is wrapped in a package.

SQL> -- package
SQL> CREATE OR REPLACE
  2  PACKAGE new_pack
  3  IS
  4    PROCEDURE new_proc;
  5  END new_pack;
  6  /

Package created.

SQL>
SQL> -- package body with a procedure
SQL> CREATE OR REPLACE
  2  PACKAGE BODY new_pack
  3  IS
  4  PROCEDURE new_proc
  5  IS
  6  BEGIN
  7    NULL;
  8  END;
  9  END new_pack;
 10  /

Package body created.

SQL> SELECT owner,
  2    object_name,
  3    procedure_name,
  4    object_type
  5  FROM all_procedures
  6  WHERE owner='LALIT'
  7  AND procedure_name='NEW_PROC';

OWNER OBJECT_NAME     PROCEDURE_NAME  OBJECT_TYPE
----- --------------- --------------- -----------
LALIT NEW_PACK        NEW_PROC        PACKAGE

SQL>

现在您可以看到procedure_name是实际的过程，而object_name是package_name.

Now you could see the procedure_name as the actual procedure, and the object_name as the package_name.

当然，大多数情况下，在生产系统中，我们将拥有包装，而不是独立的程序.但是，在进行测试和演示时，我们会编译并运行独立的过程.因此，很高兴知道Oracle如何在 * _ PROCEDURES视图中维护信息.

Of course, mostly in production systems we would have packages, and not stand alone procedures. But, while testing and demos, we do compile and run stand alone procedures. So, it is good to know how Oracle maintains the information in *_PROCEDURES views.

这篇关于ALL_PROCEDURES视图未显示PROCEDURE_NAME的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！