Oracle PL/SQL with Query

Why use?

1. If you need to apply the same query multiple times in a complex query, use with to implement code reuse;

2. With query is similar to retaining the query results to the user's temporary table. Io can be reduced in large and complex queries, and performance optimization is required.

 

What are the restrictions and features of with query?

1. If the current schema has the same table as the with query alias, the tables generated by the with query in the query take precedence;

2. It can only be used for select statements;

3. With can contain one or more queries;

4. The with query can be referenced by other queries or with queries.

 

Example:

Duzz $ Scott @ orcl> select * from Dept; </P> <p> deptno dname loc <br/> ---------- --------------- ---------- <br/> 10 Accounting New York <br/> 20 research Dallas <br/> 30 Chicago sales <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/>