pl/sql原始碼掃描sql(10g)

想知道某一個表是否在資料庫的pl/sql程式碼中被使用了，有兩種辦法：

一種是透過資料庫檢視：user_dependencies查詢，可以很清楚的知道哪個物件被依賴或哪個物件依賴哪些物件。但是，這個檢視也有不足之處，它描述的是靜態程式碼中依賴的物件，我稱之為硬關聯，這個編譯時就得存在，否則會報錯。因此，其依賴關係也就是在編譯的時候就確定了。但是，如果要知道動態語句中涉及到的表等，這個檢視就無法解決了，因為這個是在執行時才呼叫的物件，而不是在編譯時就知道的。

於是，我寫了一個sql，利用user_source這個檢視，將原始碼中含有相應物件的語句找出來，然後透過正規表示式將物件名稱提取出來。其缺點是目前只支援單行出現一次的情況，如果出現兩次，就無法正確匹配了。

sql如下：

with tmp as(select replace(lower('TMAILBILL_INVEST_LOCAL'),'_','\_') tt from dual)
select a.name, a.text,
       regexp_substr(regexp_replace(lower(a.text),
                                        '[^a-z|A-Z|_| |,|''|0-9]',--將非數字英文字元下劃線逗號的字元替換為空格（主要是為了過濾中文）
                                        ' '
                                    ),
                        '[^ |,|.|''|(]*' || replace(b.tt,'\') || '[^ |,|''|)]*'--找出物件名
                     ) full_table_name
  from user_source a, tmp b
 where lower(text) like '%' || b.tt || '%' escape '\'
   and a.text not like '%--%';

這裡有個非常有意思的現象，對escape的使用我之前不是很瞭解，以為是放在sql語句最後的一個指示器，用來指定轉義符，但是發現一個我一開始認為是個bug的問題：

下面這個sql居然沒有結果：

select * from (select '    aaaa_bbb' aa from dual) a
 where a.aa like '%' || 'aaaa\_bbb' || '%'
   and a.aa not like '%--%'
   escape '\'

按理講，a.aa not like '%--%'這個肯定是成立的，
a.aa like '%' || 'aaaa\_bbb' || '%'這個也是成立的，因為有escape指定的轉義符進行轉義，但是結果卻是空的。

一開始沒想明白，後來嘗試將escape放到a.aa like '%' || 'aaaa\_bbb' || '%'這個語句後面：

select * from (select '    aaaa_bbb' aa from dual) a
 where a.aa like '%' || 'aaaa\_bbb' || '%' escape '\'
   and a.aa not like '%--%'

發現居然也是可以的。於是立即想到，這個子句的作用域是當前條件，而不是整個where子句。所以放到最後的時候，其實是對a.aa not like '%--%'起作用的。