oracle 如何检查存储过程是否存在？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5721474/
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
How to check if a stored procedure exist?
提问by Paulo Santos
I have searched the net and I've found a postthat uses the following snippet to check if a stored procedure exists:
我在网上搜索过，发现有一篇文章使用以下代码片段来检查存储过程是否存在：
select *
from USER_SOURCE
where type='PROCEDURE'
and name='my_stored_procedure.'
Is there any other way to check if a procedure exists?
有没有其他方法可以检查程序是否存在？
Edited to add:
编辑添加：
Before posting SQL Server ways, please I'm looking for ORACLE ways.
在发布 SQL Server 方式之前，请我寻找 ORACLE 方式。
回答by Chandu
Alternatives:
备择方案：
USER_PROCEDURES:
用户程序：
SELECT *
FROM USER_PROCEDURES
WHERE object_name = 'MY_STORED_PROCEDURE'
USER_OBJECTS:
USER_OBJECTS：
SELECT *
FROM USER_OBJECTS
WHERE object_type = 'PROCEDURE'
AND object_name = 'MY_STORED_PROCEDURE'
回答by Waqar Alamgir
Something that worked for me!
对我有用的东西！
SELECT text
FROM all_source
WHERE name = 'MY_SP_NAME'
ORDER BY line;
Alternatively you can try calling SP like this:
或者，您可以尝试像这样调用 SP：
CALL MY_SP_NAME();
You might end up error like this, but that confirms you have SP defined there:
您最终可能会出现这样的错误，但这证实您在那里定义了 SP：
OCI Statement Execution failure.ORA-06553: PLS-306: wrong number or types of arguments in call to 'MY_SP_NAME'
回答by user3484328
Execute the query below in SQL*PLUS, ODBC Test,...
在 SQL*PLUS、ODBC 测试、...
SELECT text FROM all_source WHERE name='MY_PROCEDURE' ORDER BY line
SELECT text FROM all_source WHERE name='MY_PROCEDURE' ORDER BY 行
where MY_PROCEDURE is the stored procedure name.
其中 MY_PROCEDURE 是存储过程名称。
Below is sample output:
下面是示例输出：
Get Data All:
"TEXT"
"PROCEDURE Usp_Get_Blob
"(
"P_DOC_ID INT,
"P_DOC_TEXT OUT BLOB)
"as
"begin
" select B1
into p_doc_text
" from blobtest
" where ID = p_doc_id;
"end; "
Get Data All: "TEXT" "PROCEDURE Usp_Get_Blob
"(
"P_DOC_ID INT,
"P_DOC_TEXT OUT BLOB)
"as
"begin
" select B1 into p_doc_text
" from blobtest
" where ID = p_doc_id;
"end; ”
回答by ik_zelf
The only way to see if a procedure exists in the database is though querying DBA_OBJECTS. The disadvantage here is that only a dba has access to this view. Second best is using all_objects. ALL_OBJECTSshows you the objects for which you have somehow a privilege. USER_OBJECTSonly shows you your own objects.
查看数据库中是否存在过程的唯一方法是查询DBA_OBJECTS。这里的缺点是只有 dba 才能访问此视图。其次是使用 all_objects。ALL_OBJECTS向您展示您拥有某种特权的对象。USER_OBJECTS只显示你自己的对象。
回答by FrenkyB
I was not able to find stored procedure with any of the methods above.
我无法使用上述任何方法找到存储过程。
Reason: stored procedure was inside package. Oracle uses packages to gather several stored procedures in one module.
原因：存储过程在包内。Oracle 使用包在一个模块中收集多个存储过程。
回答by user3137391
select * from USER_SOURCE where type='PROCEDURE' and name='my_stored_procedure.'
select * from USER_SOURCE where type='PROCEDURE' and name='my_stored_procedure.'
