ALL_PROCEDURES 视图不显示 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 获取过程列表.
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.
相关文章