oracle 数据库 找内容,ORACLE中怎么查找特定对象中的文本内容

当前位置:我的异常网» 数据库 » ORACLE中怎么查找特定对象中的文本内容

ORACLE中怎么查找特定对象中的文本内容

www.myexceptions.net  网友分享于：2013-07-20  浏览：4次

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.

ColumnDatatypeNULLDescription

OWNERVARCHAR2(30)NOT NULLOwner of the object

NAMEVARCHAR2(30)NOT NULLName of the object

TYPEVARCHAR2(12) Type of object: FUNCTION, JAVA SOURCE, PACKAGE, PACKAGE BODY, PROCEDURE, TRIGGER, TYPE, TYPE BODY

LINENUMBERNOT NULLLine number of this line of source

TEXTVARCHAR2(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

文章评论