在 oracle 中的存储过程的源代码 (DDL) 中查找字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1928104/
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
Find a string within the source code (DDL) of a stored procedure in oracle
提问by RRUZ
I need to find a string in the source code (DDL) for all stored procedures in Oracle schema.
我需要在源代码 (DDL) 中为 Oracle 模式中的所有存储过程找到一个字符串。
I use this query to perform the task, but I think can be improved
我使用这个查询来执行任务，但我认为可以改进
SELECT T0.OBJECT_NAME
FROM USER_PROCEDURES T0
WHERE T0.OBJECT_TYPE='PROCEDURE'
AND INSTR( (SELECT DBMS_METADATA.GET_DDL('PROCEDURE',T0.OBJECT_NAME,'MySCHEMA')
FROM DUAL), 'TheStringToSearch' )>0
is there a way of accomplishing this task in a more optimal and fast way?
有没有办法以更优化、更快速的方式完成这项任务？
thanks in advance.
提前致谢。
回答by Tony Andrews
Yes, use USER_SOURCE:
是的，使用 USER_SOURCE：
select distinct name
from user_source
where type = 'PROCEDURE'
and lower(text) like lower('%the_text_you_want%');
