关于Oracle with table as的用法

最近在论坛经常看到有人使用with table as语句，一般都是构建一个临时表，用于测试，经研究此语句的用法我理解有以下好处：1）不用实际建表，可以轻松构建一个临时表，通过对这个表的处理测试一些功能；例如：with t as （select "010-82696948" telfrom dualunionallselect"020 82167684"from dualunion allselect"010-6210214762104404"from dualunion allselect"0860476-82321383"from dualunion allselect"020-28876096"from dualunion allselect"010-67260464-分机"from dual）select "086-0"||regexp_replace（replace（regexp_substr（tel,"[0-9]+[- ][0-9]{7}",1,1）,"","-"）,"^[0]*86[0]|^0",""）from t;--对各种格式电话号码做规范化处理2）复杂的查询会产生很大的sql，with table as语法可以把一些公共查询提出来，也可以显示一个个中间结果，可以使整个sql语句显得有条理些，可读性提高；3）前面的中间结果可以被语句中的select或后面的中间结果表引用，类似于一个范围仅限于本语句的临时表，在需要多次查询某中间结果时可以提升效率，特别是对一些大数据量的表做多项统计时，可以大大提高效率。例如： with a as （select * from dba_objects where 某些查询条件）,
     b as （select * from a where 某些查询条件）
     select * from b , a where 其它查询条件; 再比如：with tb as （select * from dba_objects where 某些查询条件）,
          select count（*） from tb where 其它查询条件1          union          select count（*） from tb where 其它查询条件2          union          select count（*） from tb where 其它查询条件3;
1、with table as 相当于建个临时表（用于一个语句中某些中间结果放在临时表空间的SQL语句），Oracle 9i 新增WITH语法，可以将查询中的子查询命名，放到SELECT语句的最前面。语法就是
with tempname as （select ....）
select ...例子：
with t as （select * from emp where depno=10）
select * from t where empno=xxxwith wd as （select did,arg（salary）平均工资 from work group by did）,
em as （select emp.*,w.salary from emp left join work w on emp.eid = w.eid）
select * from wd,em where wd.did =em.did and wd.平均工资>em.salary; 2、何时被清除
临时表不都是会话结束就自动被PGA清除嘛！但with as临时表是查询完成后就被清除了！
23:48:58 SCOTT@orcl> with aa as（select * from dept）
23:57:58   2 select * from aa;    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON已用时间: 00: 00: 00.12
23:58:06 SCOTT@orcl> select * from aa;
select * from aa
              *
第 1 行出现错误:
ORA-00942: 表或视图不存在
已用时间: 00: 00: 00.02
23:58:14 SCOTT@orcl>3、举例假定有张很大的表，有几年来的经营数据，数据量很大。如果要统计一段时间内的邮件状态，如果都从总表中统计，效率一定不高，而采用with tablename as 语句，先将一段时间内的数据取出来，再进行统计就会简单的多。 with tb as （select b.city,a.mail_num,a.rcv_area from tb_evt_mail_clct a, tb_jg b         where a.clct_date = to_date（"20110816", "yyyymmdd"）           and （a.rcv_area like "23%" or a.rcv_area like "24%"）           and a.clct_bureau_org_code = b.zj_code           and not exists （select 1 from tb_evt_dlv c                 where c.mail_num = a.mail_num                   and c.dlv_sts_code = "I"）                   ）   -- 提取出查询数据select aa.city 收寄城市, aa.wtt 未妥投, bb.wtd 未投递, cc.wkc 未开拆 from （select tb.city, count（*） wtt          from tb         group by tb.city） aa -- 统计1 left join （select tb.city, count（*） wtd               from tb                where not exists              （select 1 from tb_evt_dlv c                      where c.mail_num = tb.mail_num                        and （c.dlv_sts_code = "H" or c.dlv_sts_code = "I"））              group by tb.city） bb on bb.city = aa.city -- 统计2 left join （select tb.city, count（*） wkc                from tb              where not exists              （select 1 from tb_evt_dlv c                      where c.mail_num = tb.mail_num                        and （c.dlv_sts_code = "H" or c.dlv_sts_code = "I"））                and not exists              （select 1 from tb_evt_bag_mail_rela e                      where e.mail_num = tb.mail_num                        and e.bag_actn_code = "2"                        and e.deal_org_code like                            substr（tb.rcv_area, 1, 4） || "%"）              group by tb.city） cc on cc.city = aa.city -- 统计3如何将多个SQL查询统计结果一次显示出来数据库定时备份脚本相关资讯 Oracle基础教程

Oracle块编程返回结果集详解（11/10/2013 10:45:58）
Oracle基础教程之设置系统全局区（08/22/2013 14:24:00）
Oracle基础教程知识点总结（06/18/2013 07:43:32）

Oracle基础教程之tkprof程序详解（10/22/2013 11:49:50）
Oracle基础教程之sqlplus汉字乱码（07/18/2013 16:30:00）
Oracle 管理之 Linux 网络基础（02/16/2013 18:37:35）

本文评论查看全部评论（0）