Oracle As with语法

with查询语句

不是以select的查询语句，在真正查询之前预先构造一个临时表，之后可以对它进一步的分析和处理
优点：增加sql易读性，若存在多个子查询，结构会更加清晰
提升性能，实现“一次分析，多次使用”

语法介绍

## 整体语法：with tempName as (select ...)
## 针对一个别名：with tmp as(select * from table)
## 针对多个别名：with
                    tmp1 as(select * from table) ,
                    tmp2 as(select * from table),
                    tmp3 as(select * from table)

实际案例

##相当于建了一个临时表e
with e as (select * from scott.emp e where e.empno=7499)
select * from e;
 
##相当于建了e、d临时表
with
     e as (select * from scott.emp),
     d as (select * from scott.dept)
select * from e, d where e.deptno = d.deptno;

总结

## 1. 将大量重复查询语句通过with as取一个别名，后面的查询就可以用到它
## 2. 定义全局sql片段，实际是放到一个全局临时表中
## 3. 对于union all特别有用，提高速度
with
    sql1 as (select to_char(a) s_name from test_tempa),
    sql2 as (select to_char(b) s_name from test_tempb where not exists (select s_name from sql1 where rownum=1))
select * from sql1
union all
select * from sql2
union all
select 'no records' from dual
       where not exists (select s_name from sql1 where rownum=1)
       and not exists (select s_name from sql2 where rownum=1);