关于oracle with table as的用法

前端之家收集整理的这篇文章主要介绍了关于oracle with table as的用法前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

最近在论坛经常看到有人使用with table as语句,一般都是构建一个临时表,用于测试,经研究此语句的用法我理解有以下好处:

1) 不用实际建表,可以轻松构建一个临时表,通过对这个表的处理测试一些功能

例如:with tas (

select '010-82696948'telfrom dualunionall

select'020 82167684'from dualunion all

select'010-62102147\62104404'from dualunion all

select'0860476-82321383'from dualunion all

select'020-28876096'from dualunion all

select'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=xxx

with 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;

=============================================================

注意:这个临时表只能用于查询,不能用于更新,如:

ith tt as ( select to_char(sysdate,'yyyymmdd') rq from dual )
update song_temp set code=( select rq from tt) where code='11';

这个语句会报错:ora-00928: 缺失select关键字!


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

原文链接:https://www.f2er.com/oracle/206256.html

猜你在找的Oracle相关文章