Oracle PL/SQL之WITH查詢

為什麼要用WITH？

1. 如果需要在一段複雜查詢裡多次應用同一個查詢，用WITH可實現代碼重用；

2. WITH查詢類似將查詢結果保留到使用者暫存資料表裡，在大的複雜查詢中可以減少IO，有一定的效能最佳化作用。

 

WITH查詢有何限制與特性？

1. 如果當前schema下有與WITH查詢別名相同的表，查詢中WITH查詢產生的表優先；

2. 只能用於select 語句；

3. WITH可包含一個或多個查詢；

4. WITH查詢可被其它查詢或WITH查詢引用。

 

樣本：

duzz$scott@orcl>select * from dept;</p><p> DEPTNO DNAME LOC<br />---------- --------------- ----------<br /> 10 ACCOUNTING NEW YORK<br /> 20 RESEARCH DALLAS<br /> 30 SALES CHICAGO<br /> 40 OPERATIONS BOSTON</p><p>Elapsed: 00:00:00.00<br />duzz$scott@orcl>with dept as (select 1 a from dual) select * from dept;</p><p> A<br />----------<br /> 1</p><p>Elapsed: 00:00:00.00<br />duzz$scott@orcl>with dept as (select 1 a from dual) delete from dept where a=1;<br />with dept as (select 1 a from dual) delete from dept where a=1<br /> *<br />ERROR at line 1:<br />ORA-00928: missing SELECT keyword</p><p>Elapsed: 00:00:00.01<br />duzz$scott@orcl>with wt1 as (select 1 a, 2 b from dual), wt2 as (select 1 c,3 d from dual) select * from wt1,wt2 where wt1.a=wt2.c;</p><p> A B C D<br />---------- ---------- ---------- ----------<br /> 1 2 1 3</p><p>Elapsed: 00:00:00.00<br />duzz$scott@orcl>with wt1 as (select 10 a, 2 b from dual), wt2 as (select deptno,loc from dept,wt1 where deptno=a) select loc from wt2;</p><p>LOC<br />---------------------------------------<br />NEW YORK</p><p>Elapsed: 00:00:00.00<br />duzz$scott@orcl><br />