抓出 LOOP 套 LOOP 的 PSQL

此脚本不依赖统计信息。

在编写 PLSQL 的时候，我们应该尽量避免 LOOP 套 LOOP，因为双层循环，最内层循环类似笛卡儿积。假设外层循环返回 1 000 行数据，内层循环返回 1 000 行数据，那么内层循环里面的代码就会执行 1000*1000 次。以下脚本可以抓出 LOOP 套 LOOP 的 PLSQL。

with x as
(select /*+ materialize */ owner,name,type,line,text,rownum rn from dba_source where (upper(text) like '%END%LOOP%' or upper(text) like '%FOR%LOOP%'))
select a.owner,a.name,a.type from x a,x b
where ((upper(a.text) like '%END%LOOP%'
and upper(b.text) like '%END%LOOP%'
and a.rn+1=b.rn)
or (upper(a.text) like '%FOR%LOOP%'
and upper(b.text) like '%FOR%LOOP%'
and a.rn+1=b.rn))
and a.owner=b.owner
and a.name=b.name
and a.type=b.type
and a.owner='SCOTT';

我们在 Scott 账户中创建 LOOP 套 LOOP 的存储过程。

create or replace procedure p_99 is
begin
  for i in 1 .. 9 loop
    dbms_output.put_line('');
    for x in 1 .. 9 loop
      if (i >= x) then
        dbms_output.put('  ' || i || ' x ' || x || ' = ' || i * x);
      end if;
    end loop;
    dbms_output.put_line('');
  end loop;
end;

我们通过脚本将以上的存储过程抓出。

 with x as
  (select /*+ materialize */ owner,name,type,line,text,rownum rn from   dba_source
  where (upper(text) like '%END%LOOP%' or upper(text) like '%FOR%LOOP%'))
  select distinct a.owner,a.name,a.type from x a,x b
  where ((upper(a.text) like '%END%LOOP%'
  and upper(b.text) like '%END%LOOP%'
  and a.rn+1=b.rn)
  or (upper(a.text) like '%FOR%LOOP%'
 and upper(b.text) like '%FOR%LOOP%'
 and a.rn+1=b.rn))
  and a.owner=b.owner
  and a.name=b.name
  and a.type=b.type
 and a.owner='SCOTT';

OWNER           NAME            TYPE
--------------- --------------- ---------------
SCOTT           P_99            PROCEDURE