我需要使用什么 SQL 来列出 Oracle 数据库上的所有存储过程？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/955927/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What SQL would I need to use to list all the stored procedures on an Oracle database?
提问by Mike McQuaid
What SQL would I need to use to list all the stored procedures on an Oracle database?
我需要使用什么 SQL 来列出 Oracle 数据库上的所有存储过程？
If possible I'd like two queries:
如果可能，我想要两个查询：
- list all stored procedures by name
- list the code of a stored procedure, given a name
- 按名称列出所有存储过程
- 列出存储过程的代码，给定名称
回答by Adam Paynter
The DBA_OBJECTSview will list the procedures (as well as almost any other object):
该DBA_OBJECTS视图将列出过程（以及几乎所有其他对象）：
SELECT owner, object_name
FROM dba_objects
WHERE object_type = 'PROCEDURE'
The DBA_SOURCEview will list the lines of source code for a procedure in question:
该DBA_SOURCE视图将列出相关过程的源代码行：
SELECT line, text
FROM dba_source
WHERE owner = ?
AND name = ?
AND type = 'PROCEDURE'
ORDER BY line
Note:Depending on your privileges, you may not be able to query the DBA_OBJECTSand DBA_SOURCEviews. In this case, you can use ALL_OBJECTSand ALL_SOURCEinstead. The DBA_views contain allobjects in the database, whereas the ALL_views contain only those objects that you may access.
注意：根据您的权限，您可能无法查询DBA_OBJECTS和DBA_SOURCE视图。在这种情况下，您可以使用ALL_OBJECTSandALL_SOURCE代替。该DBA_视图包含所有数据库中的对象，而ALL_观点仅包含那些你可以访问对象。
回答by Mark Harrison
If you want to get all the calls to introspect stored procedures (parameters, etc), you can pull it out of this open source package:
如果你想获得所有调用自省存储过程（参数等），你可以把它从这个开源包中拉出来：
http://code.google.com/p/orapig
http://code.google.com/p/orapig
OraPIG is the Oracle Python Interface Generator. It introspects oracle packages and generates python wrappers for them.
OraPIG 是 Oracle Python 接口生成器。它内省 oracle 包并为它们生成 python 包装器。
回答by Peter Teoh
I think enumerating from DBA_OBJECTS may have missed out a lot of procedures: (I am on Oracle 12c, login as SYS)
我认为从 DBA_OBJECTS 枚举可能遗漏了很多过程：（我在 Oracle 12c 上，以 SYS 身份登录）
select count(*) from dba_objects where object_type = 'PROCEDURE';
202
It really looked impossible that the entire ORACLE database can have only 202 procedures.
整个ORACLE数据库只能有202个程序，这看起来真的是不可能的。
And querying from DBA_PROCEDURES:
并从 DBA_PROCEDURES 查询：
select owner||'-'||object_name || '-'||procedure_name from
dba_procedures WHERE PROCEDURE_NAME IS NOT NULL;
26539 rows selected.
Now focusing on SYS schema, which comes default for every database (not unique to mine):
现在关注 SYS 模式，它是每个数据库的默认模式（不是我独有的）：
Querying for ANONYMOUS stored procedures (http://www.praetoriate.com/t_high_perform_calling_procedures.htm) belonging to SYS:
查询属于 SYS 的匿名存储过程 ( http://www.praetoriate.com/t_high_perform_calling_procedures.htm)：
select owner||'-'||object_name || '-'||procedure_name from dba_procedures WHERE PROCEDURE_NAME IS NULL and owner = 'SYS';
994 rows selected.
And SYS's non-anonymous stored procedures have 15K:
而SYS的非匿名存储过程有15K：
select owner||'-'||object_name || '-'||procedure_name from dba_procedures WHERE PROCEDURE_NAME IS NOT NULL and owner = 'SYS';
15408 rows
回答by kajibu
Here is a simpler SQL SELECT * FROM User_Procedures;
这是一个更简单的SQL SELECT * FROM User_Procedures;
