SQL 在 Oracle 存储过程中搜索文本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5033803/
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
Searching for Text within Oracle Stored Procedures
提问by PseudoToad
I need to search through all of the stored procedures in an Oracle database using TOAD. I am looking for anywhere that the developers used MAX + 1 instead of the NEXTVAL on the sequence to get the next ID number.
我需要使用 TOAD 搜索 Oracle 数据库中的所有存储过程。我正在寻找开发人员在序列上使用 MAX + 1 而不是 NEXTVAL 来获取下一个 ID 号的任何地方。
I've been doing SQL Server for years and know several ways to do it there but none are helping me here.
我多年来一直在做 SQL Server 并且知道几种方法来做到这一点，但没有人在这里帮助我。
I've tried using
我试过使用
SELECT * FROM user_source
WHERE UPPER(text) LIKE '%blah%'
Results are returned but only for my default schema and not for the schema I need to be searching in.
结果将返回，但仅针对我的默认架构，而不是我需要搜索的架构。
I also tried the below but it just errors
我也尝试了以下但它只是错误
SELECT * FROM SchemaName.user_source
WHERE UPPER(text) LIKE '%blah%'
回答by Shannon Severance
SELECT * FROM ALL_source WHERE UPPER(text) LIKE '%BLAH%'
EDITAdding additional info:
编辑添加附加信息：
SELECT * FROM DBA_source WHERE UPPER(text) LIKE '%BLAH%'
The difference is dba_source will have the text of all stored objects. All_source will have the text of all stored objects accessible by the user performing the query. Oracle Database Reference 11g Release 2 (11.2)
不同之处在于 dba_source 将拥有所有存储对象的文本。All_source 将包含执行查询的用户可访问的所有存储对象的文本。Oracle 数据库参考 11g 第 2 版 (11.2)
Another difference is that you may not have access to dba_source.
另一个区别是您可能无法访问 dba_source。
回答by Guest1
I allways use UPPER(text)like UPPER('%blah%')
我总是使用UPPER(text)像UPPER('%blah%')
回答by ANM KABIR
If you use UPPER(text), the like '%lah%'will always return zero results. Use '%LAH%'.
如果使用UPPER(text)，like '%lah%'将始终返回零结果。使用'%LAH%'.
