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

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，看看里面返回的结果就知道了。

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，你随便写什么都可以，只是个标记而已。

 

===============================================================================

一个朋友提供了种简便点的写法

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

 

不过还有一个没有解决的问题，那就是如果需要查找的内容被分行输入的话，怎么办？

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

 

呵呵，大家可以回复讨论讨论~