抓出带有自定义函数的 SQL

此脚本不依赖统计信息。

在开发过程中，我们应该避免在 SQL 语句中调用自定义函数。我们可以通过以下 SQL 语句抓出 SQL 语句中调用了自定义函数的 SQL。

select distinct sql_id, sql_text, module
  from V$SQL,
       (select object_name
          from DBA_OBJECTS O
         where owner = 'SCOTT'
           and object_type in ('FUNCTION', 'PACKAGE'))
 where (instr(upper(sql_text), object_name) > 0)
   and plsql_exec_time > 0
   and regexp_like(upper(sql_fulltext), '^[SELECT]')
   and parsing_schema_name = 'SCOTT';

我们在 Scott 账户中创建如下函数。

create or replace function f_getdname(v_deptno in number) return varchar2 as
  v_dname dept.dname%type;
begin
  select dname into v_dname from dept where deptno = v_deptno;
  return v_dname;
end f_getdname;

然后我们在 Scott 账户中运行如下 SQL。

select empno,sal,f_getdname(deptno) dname from emp;

     EMPNO        SAL DNAME
---------- ---------- -------------------------
      7369        800 RESEARCH
      7499       1600 SALES
      7521       1250 SALES
      7566       2975 RESEARCH
      7654       1250 SALES
      7698       2850 SALES
      7782       2450 ACCOUNTING
      7788       3000 RESEARCH
      7839       5000 ACCOUNTING
      7844       1500 SALES
      7876       1100 RESEARCH
      7900        950 SALES
      7902       3000 RESEARCH
      7934       1300 ACCOUNTING

我们通过脚本抓出刚执行过的 SQL 语句。

 select distinct sql_id, sql_text, module
    from V$SQL,
         (select object_name
            from DBA_OBJECTS O
           where owner = 'SCOTT'
             and object_type in ('FUNCTION', 'PACKAGE'))
   where (instr(upper(sql_text), object_name) > 0)
     and plsql_exec_time > 0
      and regexp_like(upper(sql_fulltext), '^[SELECT]')
     and parsing_schema_name = 'SCOTT';

SQL_ID          SQL_TEXT                                                MODULE
--------------- ------------------------------------------------------- ---------
2ck71xc69j49u   select empno,sal,f_getdname(deptno) dname from emp      SQL*Plus