Oracle 如何在包中找到包含特定文本的过程列表？

如何在包中找到包含特定文本的过程列表？

示例: 表: emp有以下列
empno
ename
萨尔
工作
通信
deptno


我的要求是我需要在使用 “deptno” 列的程序包中找到过程列表。


假设我有一个包，其中包含10个过程，包中只有10个过程
其中使用 “deptno” 列。

Pkg _ emp_detail

Prop1
Prop1
proc3
...
...
...
...
...
Prop10


我想要查询，它将只显示包的2个proc

sayProp1 and proc2 其中使用 “deptno” 列。

我们不会公开该级别，但是您可以对源代码进行一些基本的数据挖掘。例如，让我们说我的代码是:

SQL> create or replace
  2  package PKG is
  3    type t is table of number;
  4
  5    g1 int;
  6
  7    function F return number;
  8
  9    procedure P(p in number,
 10                q in out scott.emp.deptno%type);
 11
 12    cursor c is select * from dual;
 13  end;
 14  /

Package created.

SQL> create or replace
  2  package body PKG is
  3    type t is table of number;
  4
  5    g int;
  6
  7    function F return number is
  8    begin
  9      null;
 10      null;
 11      null;
 12      return 1;
 13    end;
 14
 15    procedure P(p in number,
 16                q in out scott.emp.deptno%type) is
 17    begin
 18      for i in ( select * from scott.emp )
 19      loop
 20         null;
 21      end loop;
 22
 23      select deptno into q from scott.emp;
 24
 25    end;
 26
 27  begin
 28    select max(deptno)
 29    into   g
 30    from scott.emp;
 31
 32  end;
 33  /

Package body created.

SQL> create or replace
  2  package PKG2 is
  3    type t is table of number;
  4
  5    g1 int;
  6
  7    function F1 return number;
  8
  9    procedure P2(p in number,
 10                q in out scott.emp.deptno%type);
 11
 12    cursor c is select * from dual;
 13  end;
 14  /

Package created.

SQL> create or replace
  2  package body PKG2 is
  3    type t is table of number;
  4
  5    g int;
  6
  7    function F1
  8    return number is
  9    begin
 10      null;
 11      null;
 12      null;
 13      return 1;
 14    end;
 15
 16    procedure P2(p in number,
 17                q in out scott.emp.deptno%type) is
 18    begin
 19      for i in ( select * from scott.emp )
 20      loop
 21         null;
 22      end loop;
 23
 24      select deptno into q from scott.emp;
 25
 26    end;
 27
 28  begin
 29    select max(deptno)
 30    into   g
 31    from scott.emp;
 32
 33  end;
 34  /

Package body created.

SQL>
SQL>

我可以写一些东西来将每一行代码分配 (松散地) 给它的父子例程，例如

SQL> with t as
  2  ( select
  3    case
  4      when line = 1 then name
  5      when ltrim(lower(text)) like 'function%' then regexp_substr(substr(ltrim(text),10),'[[:alnum:]]+')
  6      when ltrim(lower(text)) like 'procedure%' then regexp_substr(substr(ltrim(text),11),'[[:alnum:]]+')
  7    end tag,
  8    u.*
  9  from user_source u where name in ( 'PKG','PKG2')
 10  )
 11  select last_value(tag ignore nulls) over ( partition by name, type order by line ) as routine,
 12         text
 13  from   t;

ROUTINE    TEXT
---------- ------------------------------------------------------------
PKG        package PKG is
PKG          type t is table of number;
PKG
PKG          g1 int;
PKG
F            function F return number;
F
P            procedure P(p in number,
P                        q in out scott.emp.deptno%type);
P
P            cursor c is select * from dual;
P          end;
PKG        package body PKG is
PKG          type t is table of number;
PKG
PKG          g int;
PKG
F            function F return number is
F            begin
F              null;
F              null;
F              null;
F              return 1;
F            end;
F
P            procedure P(p in number,
P                        q in out scott.emp.deptno%type) is
P            begin
P              for i in ( select * from scott.emp )
P              loop
P                 null;
P              end loop;
P
P              select deptno into q from scott.emp;
P
P            end;
P
P          begin
P            select max(deptno)
P            into   g
P            from scott.emp;
P
P          end;
PKG2       package PKG2 is
PKG2         type t is table of number;
PKG2
PKG2         g1 int;
PKG2
F1           function F1 return number;
F1
P2           procedure P2(p in number,
P2                       q in out scott.emp.deptno%type);
P2
P2           cursor c is select * from dual;
P2         end;
PKG2       package body PKG2 is
PKG2         type t is table of number;
PKG2
PKG2         g int;
PKG2
F1           function F1
F1           return number is
F1           begin
F1             null;
F1             null;
F1             null;
F1             return 1;
F1           end;
F1
P2           procedure P2(p in number,
P2                       q in out scott.emp.deptno%type) is
P2           begin
P2             for i in ( select * from scott.emp )
P2             loop
P2                null;
P2             end loop;
P2
P2             select deptno into q from scott.emp;
P2
P2           end;
P2
P2         begin
P2           select max(deptno)
P2           into   g
P2           from scott.emp;
P2
P2         end;

现在我可以查询到它来定位相关的行

SQL> with t as
  2  ( select
  3    case
  4      when line = 1 then name
  5      when ltrim(lower(text)) like 'function%' then regexp_substr(substr(ltrim(text),10),'[[:alnum:]]+')
  6      when ltrim(lower(text)) like 'procedure%' then regexp_substr(substr(ltrim(text),11),'[[:alnum:]]+')
  7    end tag,
  8    u.*
  9  from user_source u where name in ( 'PKG','PKG2')
 10  )
 11  select *
 12    from (
 13    select last_value(tag ignore nulls) over ( partition by name, type order by line ) as routine,
 14           name,
 15           type,
 16           text
 17    from   t
 18  )
 19  where  lower(text) like '%deptno%';

ROUTINE    NAME       TYPE                 TEXT
---------- ---------- -------------------- ------------------------------------------------------------
P          PKG        PACKAGE                            q in out scott.emp.deptno%type);
P          PKG        PACKAGE BODY                       q in out scott.emp.deptno%type) is
P          PKG        PACKAGE BODY             select deptno into q from scott.emp;
P          PKG        PACKAGE BODY           select max(deptno)
P2         PKG2       PACKAGE                            q in out scott.emp.deptno%type);
P2         PKG2       PACKAGE BODY                       q in out scott.emp.deptno%type) is
P2         PKG2       PACKAGE BODY             select deptno into q from scott.emp;
P2         PKG2       PACKAGE BODY           select max(deptno)