user_source檢視使用&&查詢包和過程中的特定內容

今天將以前的工作整理了一下，想要檢查一下以前開發的procedure、package（body）是否正確。
處於業務上的考慮，我要確認一下程式碼中是否有'au'這個schema名稱沒有替換的。

經過查詢，使用如下語句：
select * from user_source
where text like '%au%'

SQL> desc user_source
Name Type           Nullable Default Comments
---- -------------- -------- ------- ------------------------------------------------------------------------------------------------------------------------
NAME VARCHAR2(30)   Y                Name of the object
TYPE VARCHAR2(12)   Y                Type of the object: "TYPE", "TYPE BODY", "PROCEDURE", "FUNCTION",
"PACKAGE", "PACKAGE BODY", "LIBRARY" or "JAVA SOURCE"
LINE NUMBER         Y                Line number of this line of source
TEXT VARCHAR2(4000) Y                Source text

通過這個表方便地得到了我想要的內容，可以直接找到是哪個物件下的哪行沒有修改。

顯示的結果中包含編譯未通過的物件，而且內容是一模一樣的。

嘗試在原始碼中新增空行，再次查詢後，結果也會多一個空行。

用這種方式查詢，發現結果中含有'default'這個我不需要的內容，也過濾出來了。
這時我希望得到like '%au%' 但不是'%default%'的內容。

最先想到：
select * from user_source
where text like '%au%' and text not like '%default%' ;

然後在儲存過程中新增了三行：
--au （第一行）
--default （第二行）
--au default （第三行）

查詢結果只能得到第一行

通過dba_source可以看到所有使用者的包和儲存過程的內容，當然，不包括經過加密處理的。

select * from user_source where instr(text,'au')<>0;

Execution Plan
----------------------------------------------------------
Plan hash value: 4266197181

Statistics
----------------------------------------------------------
      24091 recursive calls
          0 db block gets
     106929 consistent gets
        303 physical reads
          0 redo size
    3337619 bytes sent via SQL*Net to client
       4259 bytes received via SQL*Net from client
        351 SQL*Net roundtrips to/from client
          2 sorts (memory)
          0 sorts (disk)
       5238 rows processed

SQL> select * from user_source where text like '%au%' ;

5238 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3439453968

Statistics
----------------------------------------------------------
      24113 recursive calls
          0 db block gets
     106942 consistent gets
          0 physical reads
          0 redo size
    3337619 bytes sent via SQL*Net to client
       4259 bytes received via SQL*Net from client
        351 SQL*Net roundtrips to/from client
          3 sorts (memory)
          0 sorts (disk)
       5238 rows processed

“instr”方式進行了三百多次的物理讀操作，而“like”方式沒有。因此like方式比較好。

如果當前有如下內容：
NAME TYPE LINE TEXT
1 P_TEST PROCEDURE 4 " --au
"
2 P_TEST PROCEDURE 5 " --default
"
3 P_TEST PROCEDURE 6 " --au default
"
4 P_TEST PROCEDURE 7 " --default au
"
5 P_TEST PROCEDURE 8 " --defaull
"

想要得到：
1 P_TEST PROCEDURE 4 " --au"
3 P_TEST PROCEDURE 6 " --au default"
4 P_TEST PROCEDURE 7 " --default au"
5 P_TEST PROCEDURE 8 " --defaull"

應該如何編寫語句呢？
注意到：只要au的數量和default的數量不一樣，就是滿足條件的。
那麼怎樣得到數量呢？

從這個網址得到了啟示：http://www.itpub.net/thread-1164382-1-1.html

select length(str)-length(replace(str,'s_tr','')) from table
是從str欄位中取出s_tr的個數

其思路是將s_tr從欄位中刪掉，並通過原欄位長度和修改後欄位的長度差值得到非零值。
但這得到的並不是個數，需要在除以待統計子字串的長度。

我們來統計一下au的個數：
SQL> select text,(length(text)-length(replace(text,'au','')))/length('au')
2 from user_source where name='P_TEST';

TEXT                                                                             (LENGTH(TEXT)-LENGTH(REPLACE(T
-------------------------------------------------------------------------------- ------------------------------
procedure p_test is                                                                                           0
begin                                                                                                         0
null;                                                                                                       0
--au                                                                                                        1
--default                                                                                                   1
--au default                                                                                                2
--default au                                                                                                2
--defaull                                                                                                   1
end ;                                                                                                         0

9 rows selected

那麼得到需要的結果，只需要在存在‘au’子字元時，比較‘au’和‘default’個數不等就行了。

SQL> select text,(length(text)-length(replace(text,'au','')))/length('au'),(length(text)-length(replace(text,'default','')))/length('default')
2 from user_source where name='P_TEST'
3 ;

TEXT                    (LENGTH(TEXT)-LENGTH(REPLACE(T (LENGTH(TEXT)-LENGTH(REPLACE(T
----------------------- ------------------------------ ------------------------------
procedure p_test is                                  0                              0
begin                                                0                              0
null;                                              0                              0
--au                                               1                              0
--default                                          1                              1
--au default                                       2                              1
--default au                                       2                              1
--defaull                                          1                              0
end ;                                                0                              0

9 rows selected

結果如我們預期的那樣。
那麼得到想要的內容，語句為：
select text from (select text,(length(text)-length(replace(text,'au','')))/length('au') a,(length(text)-length(replace(text,'default','')))/length('default') b
from user_source where name='P_TEST') c
where c.a<>c.b;

或許還有更好的方法，以後討論。

寫sql，應該學會一種轉化，如本次思考中：
獲取特定子字串-》獲取子字串個數-》獲取子字串與所在字串的長度差

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26451536/viewspace-748698/，如需轉載，請註明出處，否則將追究法律責任。

user_source檢視使用&&查詢包和過程中的特定內容

相關文章