ORACLE中如何查找特定对象中的文本内容~

[c-sharp] view plaincopy
SQL> select dbms_metadata.get_ddl('PROCEDURE','PRO2','SCOTT') text from dual;

TEXT
----------------------------------------

CREATE OR REPLACE PROCEDURE "SCOTT"."P
RO2"
is
begin
dbms_output.put_line('wangpeng up');
end;



SQL> select dbms_metadata.get_ddl('PROCEDURE','PRO1','SCOTT') text from dual;

TEXT
----------------------------------------

CREATE OR REPLACE PROCEDURE "SCOTT"."P
RO1"
is
begin
dbms_output.put_line('wanghai up');
end;

SQL> select * from(
2 SELECT NAME , LINE , 'Create or Replace ' || TEXT as text
3 FROM USER_SOURCE
4 WHERE TYPE = 'PROCEDURE'
5 AND LINE = 1
6 UNION
7 SELECT NAME, LINE, TEXT as text
8 FROM USER_SOURCE
9 WHERE TYPE = 'PROCEDURE'
10 AND LINE > 1
11 UNION
12 SELECT NAME, 999999 , '/' as text
13 FROM USER_SOURCE
14 WHERE TYPE = 'PROCEDURE'
15 AND LINE = 1
16 ORDER BY 1, 2
17 ) c
18 where UPPER(c.text) like '%WANGHAI%';

NAME LINE TEXT
---------- ------- ----------------------------------------
PRO1 4 dbms_output.put_line('wanghai up');

SQL> select * from(
2 SELECT NAME , LINE , 'Create or Replace ' || TEXT as text
3 FROM USER_SOURCE
4 WHERE TYPE = 'PROCEDURE'
5 AND LINE = 1
6 UNION
7 SELECT NAME, LINE, TEXT as text
8 FROM USER_SOURCE
9 WHERE TYPE = 'PROCEDURE'
10 AND LINE > 1
11 UNION
12 SELECT NAME, 999999 , '/' as text
13 FROM USER_SOURCE
14 WHERE TYPE = 'PROCEDURE'
15 AND LINE = 1
16 ORDER BY 1, 2
17 ) c
18 where UPPER(c.text) like '%WANGPENG%';

NAME LINE TEXT
---------- ------- ----------------------------------------
PRO2 4 dbms_output.put_line('wangpeng up');

SQL> select * from(
2 SELECT NAME , LINE , 'Create or Replace ' || TEXT as text
3 FROM USER_SOURCE
4 WHERE TYPE = 'PROCEDURE'
5 AND LINE = 1
6 UNION
7 SELECT NAME, LINE, TEXT as text
8 FROM USER_SOURCE
9 WHERE TYPE = 'PROCEDURE'
10 AND LINE > 1
11 UNION
12 SELECT NAME, 999999 , '/' as text
13 FROM USER_SOURCE
14 WHERE TYPE = 'PROCEDURE'
15 AND LINE = 1
16 ORDER BY 1, 2
17 ) c
18 where c.text like '%dbms_output.put_line%';

NAME LINE TEXT
---------- ------- ----------------------------------------
PRO1 4 dbms_output.put_line('wanghai up');
PRO2 4 dbms_output.put_line('wangpeng up');

今天在论坛中一个帖子中的回帖里看到这个方法的，以前确实没想过还有这种方法，呵呵，真是各种牛人都有啊。
顺便学习了一下USER_SOURCE视图，以前确实没有用过这个
ALL_SOURCE
ALL_SOURCE describes the text source of the stored objects accessible to the current user.
Related Views
DBA_SOURCE describes the text source of all stored objects in the database.
USER_SOURCE describes the text source of the stored objects owned by the current user. This view does not display the OWNER column.
Column Datatype NULL Description
OWNER VARCHAR2(30) NOT NULL Owner of the object
NAME VARCHAR2(30) NOT NULL Name of the object
TYPE VARCHAR2(12) Type of object: FUNCTION, JAVA SOURCE, PACKAGE, PACKAGE BODY, PROCEDURE, TRIGGER, TYPE, TYPE BODY
LINE NUMBER NOT NULL Line number of this line of source
TEXT VARCHAR2(4000) Text source of the stored object
SQL> select distinct type from user_source;
TYPE
------------------------
PROCEDURE
PACKAGE
PACKAGE BODY
TYPE BODY
TRIGGER
FUNCTION
已选择6行。

可以看出来，如果像在上面这六种类型的对象中查找特定的字符串的话，前面的方法应该还是挺通用的。
SQL> select name,line,text from all_source where owner='SCOTT' and type='PROCEDU
RE';
NAME LINE TEXT
---------- ------- ----------------------------------------
PRO1 1 procedure pro1
PRO1 2 is
PRO1 3 begin
PRO1 4 dbms_output.put_line('wanghai up');
PRO1 5 end;
PRO2 1 procedure pro2
PRO2 2 is
PRO2 3 begin
PRO2 4 dbms_output.put_line('wangpeng up');
PRO2 5 end;
已选择10行。
对比前面的查询语句中两个地方需要解释一下
select * from(
SELECT NAME , LINE, 'Create or Replace ' || TEXT as text
FROM USER_SOURCE
WHERE TYPE = 'PROCEDURE'
AND LINE = 1
UNION
SELECT NAME , LINE, TEXT as text
FROM USER_SOURCE
WHERE TYPE = 'PROCEDURE'
AND LINE > 1
UNION
SELECT NAME , 999999, '/' as text
FROM USER_SOURCE
WHERE TYPE = 'PROCEDURE'
AND LINE = 1
ORDER BY 1, 2
) c
where UPPER(c.text) like '%WANGHAI%';
可以看到，从USER_SOURCE中直接查询出的第一行只有procedure pro1而已，和我们创建过程的语句相差Create or Replace 。
而且我们一般创建过程的结尾会加一个/，这就是第二处标记红色部分的作用。我们取掉外层的SELECT，看看里面返回的结果就知道了。
[c-sharp] view plaincopy
SQL> SELECT NAME , LINE, 'Create or Replace ' || TEXT as text
2 FROM USER_SOURCE
3 WHERE TYPE = 'PROCEDURE'
4 AND LINE = 1
5 UNION
6 SELECT NAME , LINE, TEXT as text
7 FROM USER_SOURCE
8 WHERE TYPE = 'PROCEDURE'
9 AND LINE > 1
10 UNION
11 SELECT NAME , 999999, '/' as text
12 FROM USER_SOURCE
13 WHERE TYPE = 'PROCEDURE'
14 AND LINE = 1
15 ORDER BY 1, 2;

NAME LINE TEXT
---------- ------- ----------------------------------------
PRO1 1 Create or Replace procedure pro1
PRO1 2 is
PRO1 3 begin
PRO1 4 dbms_output.put_line('wanghai up');
PRO1 5 end;
PRO1 999999 /
PRO2 1 Create or Replace procedure pro2
PRO2 2 is
PRO2 3 begin
PRO2 4 dbms_output.put_line('wangpeng up');
PRO2 5 end;

NAME LINE TEXT
---------- ------- ----------------------------------------
PRO2 999999 /

已选择12行。

至于那个999999，你随便写什么都可以，只是个标记而已。

===============================================================================
一个朋友提供了种简便点的写法
[c-sharp] view plaincopy
SQL> select distinct name from user_source
2 where type='PROCEDURE'
3 and lower(text) like '%wanghai%'
4 ;

NAME
----------
PRO1

SQL> select distinct name from user_source
2 where type='PROCEDURE'
3 and lower(text) like '%wangpeng%';

NAME
----------
PRO2


不过还有一个没有解决的问题，那就是如果需要查找的内容被分行输入的话，怎么办？
[c-sharp] view plaincopy
SQL> create procedure pro3
2 is
3 begin
4 dbms_output.put_line('wang
5 hai
6 up');
7 end;
8 /

过程已创建。

SQL> select distinct name from user_source
2 where type='PROCEDURE'
3 and lower(text) like '%wang%hai%'; --这里在wang和hai之间加了%也不行

NAME
----------
PRO1

【转载】http://blog.csdn.net/wh62592855/article/details/4866178