oracle with as 用法ITeye - AG环亚娱乐

oracle with as 用法ITeye

2019年03月28日10时34分42秒 | 作者: 梦秋 | 标签: 一个,短语,部分 | 浏览: 288

with as语法
–针对一个别号
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;
其实便是把一大堆重复用到的sql句子放在with as里边,取一个别号,后边的查询就能够用它,这样关于大批量的sql句子起到一个优化的效果,并且清楚明晰。

向一张表刺进数据的with as用法

insert into table2
with
  s1 as (select rownum c1 from dual connect by rownum = 10),
  s2 as (select rownum c2 from dual connect by rownum = 10)
select a.c1, b.c2 from s1 a, s2 b where...;
select s1.sid, s2.sid from s1 ,s2需求有相关条件,否则成果会是笛卡尔积。
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长处
增加了sql的易读性,假如结构了多个子查询,结构会更明晰;
更重要的是:“一次剖析,屡次运用”,这也是为什么会供给功能的当地,达到了“少读”的方针
版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表AG环亚娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章