Oracle with as 的用法不赘述，我理解有两方面的好处

1）  复杂的查询会产生很大的sql，with as语法显示一个个中间结果，显得有条理些，可读性提高

2）  前面的中间结果可以被语句中的select或后面的中间结果表引用，类似于一个范围仅限于本语句的临时表，在需要多次查询某中间结果时可以提升效率    

     比如 with a as (select* from dba_objects where 某些查询条件),
     b as (select * from a where 某些查询条件)
     select * from b , a  where 某些查询条件; 

     可以看到中间表a被两次引用，如果不用with as ，中间查询结果a就要运行2次

 我的oracle版本为10.2.0.4.0 ，这次单讲with as中间表多次被引用时触发ddl trigger的尴尬 

SQL> create table test_ws (c varchar2(1));

Table created

SQL> insert into test_ws values('1');

1 row inserted

SQL> select * from test_ws;

C

-

1

建个表，插条数  

SQL> with a as (select* from test_ws  ),

  2       b as (select * from a  )

  3       select * from b , a  ;

C C

- -

1 1

两次使用中间表a的with as 语句，正常运行  

SQL> create or replace TRIGGER lihao2372_test

  2  before ddl on database

  3  begin

  4   raise_application_error(-20908,'这是ddl');

  5  end;

  6  /

Trigger created

 DDL trigger建起来 

SQL> create table  ttt (c varchar2(1));

create table  ttt (c varchar2(1))

ORA-00604: error occurred at recursive SQL level 1

ORA-20908: 这是ddl

ORA-06512: at line 2 

不能建表了，ddL trigger 是ok的 

SQL> with a as (select* from test_ws  where rownum=1),

  2      b as (select * from a  )

  3         select * from b , a  ;

with a as (select* from test_ws  where rownum=1),

    b as (select * from a  )

       select * from b , a

ORA-00604: error occurred at recursive SQL level 2

ORA-20908: 这是ddl

ORA-06512: at line 2

    咦，with as怎么被当成DDL在处理了？

     普通的with as

加入一个where （rownum=1）条件

产生了新的系统临时对象，数据库对象的产生意味着DDL

总结：为保证生产库的稳定运行，往往在上班时间限制DDL。恰恰限制了最有价值的那部分with as的应用。这正是我们无法大力推广with as的尴尬之处。 

另外还发现复杂的多层with as容易在10.2.0.3.0版本抛出ORA-21780: Maximum number of object durations exceeded.实际上代码中并未发现无穷递归，将报错时的参数保存下来重新运行不能再现。运行不定长的时间后抛出报错 

网上有人说是oracle的bug ，要升级到oracle 10.2.0.4 才能解决，同样代码在10.2.0.4 环境运行无此现象，故从之搜到一份Bugs fixed in the 10.2.0.4 Patch Set 

其PL/SQL部分：

5924384 ORA-21780 in PLSQL

6061892Memory Leak on ‘PLS non-lib hp’ by recursive PLSQL calls / ORA-4030 AND ORA-21780

您可能感兴趣的文章: