欢迎关注格物致知 。 正在加载今日诗词....

with as 在oracle中的运用

Oracle gavin 167℃

语法:

with tempName as (select ....)
select ...

–针对一个别名
with tmp as (select * from tb_name)

–针对多个别名
with
tmp as (select * from tb_name),
tmp2 as (select * from tb_name2),
tmp3 as (select * from tb_name3),

–相当于建了个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;

with as 相当于虚拟视图。

with as短语,也叫做子查询部分(subquery factoring),可以让你做很多事情,定义一个sql片断,该sql片断会被整个sql语句所用到。

有的时候,是为了让sql语句的可读性更高些,也有可能是在union all的不同部分,作为提供数据的部分。

特别对于union all比较有用。

因为union all的每个部分可能相同,但是如果每个部分都去执行一遍的话,则成本太高,所以可以使用with as短语,则只要执行一遍即可。

如果with as短语所定义的表名被调用两次以上,则优化器会自动将with as短语所获取的数据放入一个temp表里,如果只是被调用一次,则不会。

而提示materialize则是强制将with as短语里的数据放入一个全局临时表里。

很多查询通过这种方法都可以提高速度。

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

由于with as是内存中的table所以还是比较快的。如果数据比较大的时候建议不要用with as这样的话变得很慢。

WITH AS 与增删改查结合用法
注意:1.with必须紧跟引用的select语句
2.with创建的临时表必须被引用,否则报错
select 与 with as 连用
with tmp as (select * from 表名)  select * from tmp
insert into 与with as 连用
insert into 表名(字段名) with  tmp as (select * from 表) select * from tmp
delete 与with as 连用
delete from 表名 where exists (with tmp as select * from 表  select  * from tmp)
update 与 with as 连用
update 表名 a  set a.字段= (with tmp as select * from 表 select 字段 from tmp where tmp.字段 = a.字段)

 

转载请注明:格物致知 » with as 在oracle中的运用

喜欢 (1)