【ORACLE】Oracle提高篇之WITH AS

WITH AS 的含义

WITH AS 短语，也叫做子查询部分（subquery factoring），可以定义一个sql片断，该片断会被整个sql语句所用到，特别对于UNION ALL比较有用。因为UNION ALL 的每个部分可能相同，但是如果每个部分都去执行一遍的话，成本太高，所以可以使用WITH AS语句，只需要执行一遍即可。

WITH AS 的优点

1.增加了sql的易读性，如果构造了多个子查询，结构会更清晰
2.提高效率，一次构建，多次使用。如果with as 短语定义的表名被调用两次以上，则优化器会自动把with as 短语所获取的数据放入临时表中，如果只是被调用一次，则不会。提示materialize则是强制将with as短语中的数据放到一个全局临时表中。在执行计划中 SYS_TEMP_XXXXX就是临时表+

WITH AS 的语法

-- 单个表
with tmpTableName as (select * from tb_name)
select * from tmpTableName
-- 加materialize
with tmpTableName as 
(select  /*+ materialize */ t.* from tb_name t)
select * from tmpTableName
-- 多个表    
with tmp1 as (select * from tb_1),
     tmp2 as (select * from tb_2)
select * from tmp1,tmp2  where tmp1.id  = tmp2.id

WITH AS 与增删改查结合

学生表：

成绩表：

1. select查询

with t1 as
 (select * from STUDENT),
t2 as
 (select * from score)
select * from t1, t2 where t1.stuid = t2.stuid

2. insert插入
插入语句中 with as 要放在insert的后面

insert into STUDENT
  with t1 as
   (select * from STUDENT t)
  select t1.stuid + 10 as stuid,
         t1.stuname || '1' as stuname,
         t1.stusex,
         t1.stuclass
    from t1
   where t1.stuid = '5'

3. delete删除
同上，删除的时候也不能把with as 放在前面

delete from STUDENT t
 where t.stuid in (with t1 as (select * from STUDENT t)
                    select t1.stuid from t1 where t1.stuid = '15'
                   )

4. update更新

update STUDENT t
   set t.stuname =
       (with t1 as (select * from STUDENT t)
         select t1.stuname || '55555555'
           from t1
          where t1.stuid = '5')
          where t.stuid = '5'

总结

基本上，我使用with as用得最多的地方是在select查询上，其他地方看你的情况了，例子比较简单，理解就好。