ORACLE中如何尋找特定對象中的常值內容~

SQL> select dbms_metadata.get_ddl('PROCEDURE','PRO2','SCOTT') text from dual;</p><p>TEXT<br />----------------------------------------</p><p> CREATE OR REPLACE PROCEDURE "SCOTT"."P<br />RO2"<br />is<br />begin<br />dbms_output.put_line('wangpeng up');<br />end;</p><p>SQL> select dbms_metadata.get_ddl('PROCEDURE','PRO1','SCOTT') text from dual;</p><p>TEXT<br />----------------------------------------</p><p> CREATE OR REPLACE PROCEDURE "SCOTT"."P<br />RO1"<br />is<br />begin<br />dbms_output.put_line('wanghai up');<br />end;</p><p>SQL> select * from(<br /> 2 SELECT NAME , LINE , 'Create or Replace ' || TEXT as text<br /> 3 FROM USER_SOURCE<br /> 4 WHERE TYPE = 'PROCEDURE'<br /> 5 AND LINE = 1<br /> 6 UNION<br /> 7 SELECT NAME, LINE, TEXT as text<br /> 8 FROM USER_SOURCE<br /> 9 WHERE TYPE = 'PROCEDURE'<br /> 10 AND LINE > 1<br /> 11 UNION<br /> 12 SELECT NAME, 999999 , '/' as text<br /> 13 FROM USER_SOURCE<br /> 14 WHERE TYPE = 'PROCEDURE'<br /> 15 AND LINE = 1<br /> 16 ORDER BY 1, 2<br /> 17 ) c<br /> 18 where UPPER(c.text) like '%WANGHAI%';</p><p>NAME LINE TEXT<br />---------- ------- ----------------------------------------<br />PRO1 4 dbms_output.put_line('wanghai up');</p><p>SQL> select * from(<br /> 2 SELECT NAME , LINE , 'Create or Replace ' || TEXT as text<br /> 3 FROM USER_SOURCE<br /> 4 WHERE TYPE = 'PROCEDURE'<br /> 5 AND LINE = 1<br /> 6 UNION<br /> 7 SELECT NAME, LINE, TEXT as text<br /> 8 FROM USER_SOURCE<br /> 9 WHERE TYPE = 'PROCEDURE'<br /> 10 AND LINE > 1<br /> 11 UNION<br /> 12 SELECT NAME, 999999 , '/' as text<br /> 13 FROM USER_SOURCE<br /> 14 WHERE TYPE = 'PROCEDURE'<br /> 15 AND LINE = 1<br /> 16 ORDER BY 1, 2<br /> 17 ) c<br /> 18 where UPPER(c.text) like '%WANGPENG%';</p><p>NAME LINE TEXT<br />---------- ------- ----------------------------------------<br />PRO2 4 dbms_output.put_line('wangpeng up');</p><p>SQL> select * from(<br /> 2 SELECT NAME , LINE , 'Create or Replace ' || TEXT as text<br /> 3 FROM USER_SOURCE<br /> 4 WHERE TYPE = 'PROCEDURE'<br /> 5 AND LINE = 1<br /> 6 UNION<br /> 7 SELECT NAME, LINE, TEXT as text<br /> 8 FROM USER_SOURCE<br /> 9 WHERE TYPE = 'PROCEDURE'<br /> 10 AND LINE > 1<br /> 11 UNION<br /> 12 SELECT NAME, 999999 , '/' as text<br /> 13 FROM USER_SOURCE<br /> 14 WHERE TYPE = 'PROCEDURE'<br /> 15 AND LINE = 1<br /> 16 ORDER BY 1, 2<br /> 17 ) c<br /> 18 where c.text like '%dbms_output.put_line%';</p><p>NAME LINE TEXT<br />---------- ------- ----------------------------------------<br />PRO1 4 dbms_output.put_line('wanghai up');<br />PRO2 4 dbms_output.put_line('wangpeng up');<br />

今天在論壇中一個文章中的回帖裡看到這個方法的，以前確實沒想過還有這種方法，呵呵，真是各種牛人都有啊。

順便學習了一下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<br /> 2 FROM USER_SOURCE<br /> 3 WHERE TYPE = 'PROCEDURE'<br /> 4 AND LINE = 1<br /> 5 UNION<br /> 6 SELECT NAME , LINE, TEXT as text<br /> 7 FROM USER_SOURCE<br /> 8 WHERE TYPE = 'PROCEDURE'<br /> 9 AND LINE > 1<br /> 10 UNION<br /> 11 SELECT NAME , 999999, '/' as text<br /> 12 FROM USER_SOURCE<br /> 13 WHERE TYPE = 'PROCEDURE'<br /> 14 AND LINE = 1<br /> 15 ORDER BY 1, 2;</p><p>NAME LINE TEXT<br />---------- ------- ----------------------------------------<br />PRO1 1 Create or Replace procedure pro1<br />PRO1 2 is<br />PRO1 3 begin<br />PRO1 4 dbms_output.put_line('wanghai up');<br />PRO1 5 end;<br />PRO1 999999 /<br />PRO2 1 Create or Replace procedure pro2<br />PRO2 2 is<br />PRO2 3 begin<br />PRO2 4 dbms_output.put_line('wangpeng up');<br />PRO2 5 end;</p><p>NAME LINE TEXT<br />---------- ------- ----------------------------------------<br />PRO2 999999 /</p><p>已選擇12行。<br />

至於那個999999，你隨便寫什麼都可以，只是個標記而已。

 

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

一個朋友提供了種簡便點的寫法

SQL> select distinct name from user_source<br /> 2 where type='PROCEDURE'<br /> 3 and lower(text) like '%wanghai%'<br /> 4 ;</p><p>NAME<br />----------<br />PRO1</p><p>SQL> select distinct name from user_source<br /> 2 where type='PROCEDURE'<br /> 3 and lower(text) like '%wangpeng%';</p><p>NAME<br />----------<br />PRO2<br />

 

不過還有一個沒有解決的問題，那就是如果需要尋找的內容被分行輸入的話，怎麼辦？

SQL> create procedure pro3<br /> 2 is<br /> 3 begin<br /> 4 dbms_output.put_line('wang<br /> 5 hai<br /> 6 up');<br /> 7 end;<br /> 8 /</p><p>過程已建立。</p><p>SQL> select distinct name from user_source<br /> 2 where type='PROCEDURE'<br /> 3 and lower(text) like '%wang%hai%'; --這裡在wang和hai之間加了%也不行</p><p>NAME<br />----------<br />PRO1<br />

 

呵呵，大家可以回複討論討論~