实验环境

搭建平台：VMware Workstation

OS：RHEL 6.10

Grid&DB：Oracle 11.2.0.4

SQL参考

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 = 'TEST'; --用户名

ITPub博客

【TUNE_ORACLE】列出LOOP套LOOP的PL/SQL代码SQL参考