user_source视图使用&&查找包和过程中的特定内容

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26451536/viewspace-748698/