新闻资讯  快讯  焦点  财经  政策  社会
互 联 网   电商  金融  数据  计算  技巧
生活百科  科技  职场  健康  法律  汽车
手机百科  知识  软件  修理  测评  微信
软件技术  应用  系统  图像  视频  经验
硬件技术  知识  技术  测评  选购  维修
网络技术  硬件  软件  设置  安全  技术
程序开发  语言  移动  数据  开源  百科
安全防护  资讯  黑客  木马  病毒  移动
站长技术  搜索  SEO  推广  媒体  移动
财经百科  股票  知识  理财  财务  金融
教育考试  育儿  小学  高考  考研  留学
您当前的位置:首页 > IT > 数据库 > Oracle

ORACLE WITH AS 用法详解

时间:2019-06-11 10:06:19  来源:  作者:

概述

WITH AS短语,也叫做子查询部分(subquery factoring),可以做很多事情,定义一个SQL片断,该SQL片断会被整个SQL语句所用到。有的时候,是为了让SQL语句的可读性更高些,也有可能是在UNION ALL的不同部分,作为提供数据的部分。

特别对于UNION ALL比较有用。因为UNION ALL的每个部分可能相同,但是如果每个部分都去执行一遍的话,则成本太高,所以可以使用WITH AS短语,则只要执行一遍即可。如果WITH AS短语所定义的表名被调用两次以上,则优化器会自动将WITH AS短语所获取的数据放入一个TEMP表里,如果只是被调用一次,则不会。而提示materialize则是强制将WITH AS短语里的数据放入一个全局临时表里。


01

with as 语法

单个语法:

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

多个语法:

with tempName1 as (select ....),
tempName2 as (select ....),
tempName3 as (select ....) ...
select ...

With查询语句不是以select开始的,而是以“WITH”关键字开头

可认为在真正进行查询之前预先构造了一个临时表TT,之后便可多次使用它做进一步的分析和处理。


02

WITH AS实例

例:从1-19中得到11-14。一般的sql如下:

select * from ( --模拟生一个20行的数据
SELECT LEVEL AS lv FROM DUAL CONNECT BY LEVEL < 20
) tt WHERE tt.lv > 10 AND tt.lv < 15
ORACLE WITH AS 用法详解

 


ORACLE WITH AS 用法详解

 

使用With as 的SQL为:

with TT as( SELECT LEVEL AS lv FROM DUAL CONNECT BY LEVEL < 20 ) select lv from TT WHERE lv > 10 AND lv < 15;
ORACLE WITH AS 用法详解

 

多个临时表实例:

WITH
T3 AS
(SELECT T1.ID, T1.CODE1, T2.DESCRIPTION
FROM TB_DATA T1, TB_CODE T2
WHERE T1.CODE1 = T2.CODE
),
T4 AS
(SELECT T1.ID, T1.CODE2, T2.DESCRIPTION
FROM TB_DATA T1, TB_CODE T2
WHERE T1.CODE2 = T2.CODE
)
SELECT T3.ID, T3.DESCRIPTION, T4.DESCRIPTION
FROM T3, T4
WHERE T3.ID = T4.ID
ORDER BY ID;

03

WITH Clause方法的优点

增加了SQL的易读性,如果构造了多个子查询,结构会更清晰;更重要的是:“一次分析,多次使用”,这也是为什么会提供性能的地方,达到了“少读”的目标。

上面第一种使用子查询的方法表被扫描了两次,而使用WITH Clause方法,表仅被扫描一次。这样可以大大的提高数据分析和查询的效率。

另外,观察WITH Clause方法执行计划,其中“SYS_TEMP_XXXX”便是在运行过程中构造的中间统计结果临时表。


04

WITH AS 与增删改查结合用法

4.1与select查询语句结合使用

查询同一个单据编号对应的借款单和核销单中,借款金额不相等的单据

with verificationInfo as
 (select ment.fnumber from shenzhenjm.t_finance_expenseremburseitem t
 left join shenzhenjm.t_finance_expenserembursement ment
 on ment.fid = t.fkrembursementid
 where 1 = 1
 group by ment.fnumber),
 
loanInfo as
 (select ment.fnumber
 from shenzhenjm.t_finance_expenseremburseitem2 t
 left join shenzhenjm.t_finance_expenserembursement ment
 on ment.fid = t.fkrembursementid
 where 1 = 1
 group by ment.fnumber)
 
select *
 from verificationInfo v, loanInfo l
 where l.fnumber = v.fnumber;

4.2与insert结合使用

如下的with as语句,不能放在insert前,而是放在紧接着要调用的地方前

insert into T_finance_ExpenseRemburseItem2
 (FID,
 FKREMBURSEMENTID,
 FAMOUNT,
 FKCREATEBYID,
 FCREATETIME,
 FKCUID,
 FKCOSTTYPEID,
 FCOSTTYPENAME)
 
 with verificationInfo as
 (select ment.fnumber,
 sum(t.famount) vLoanSum,
 ment.fnumber "单据编号",
 sum(t.famount) "核销单中借款总额"
 from shenzhenjm.t_finance_expenseremburseitem t
 left join shenzhenjm.t_finance_expenserembursement ment
 on ment.fid = t.fkrembursementid
 where 1 = 1
 group by ment.fnumber),
 
 loanInfo as
 (select ment.fnumber,
 sum(t.famount) loanSum,
 ment.fnumber "单据编号",
 sum(t.famount) "借款单中借款总额"
 from shenzhenjm.t_finance_expenseremburseitem2 t
 left join shenzhenjm.t_finance_expenserembursement ment
 on ment.fid = t.fkrembursementid
 where 1 = 1
 group by ment.fnumber)
 
 select sys_guid(),
 ment.fid,
 t.famount,
 ment.fkcreatebyid,
 ment.fcreatetime,
 ment.fkcuid,
 t.fkcosttypeid,
 t.fcosttypename
 from T_finance_ExpenseRemburseItem t
 left join t_finance_expenserembursement ment
 on ment.fid = t.fkrembursementid
 where 1 = 1
 and exists (select *
 from verificationInfo v, loanInfo l
 where l.fnumber = v.fnumber
 and l.loanSum != v.vLoanSum
 and v.fnumber = ment.fnumber);

4.3 与delete删除结合使用

delete from t_finance_expenseremburseitem2 item2
 where exists(with temp as (select t.fnumber,
 sum(item1.famount) vloanSum,
 sum(item1.frealityamount) vSum,
 sum(item2.famount) loanSum
 from t_finance_expenserembursement t
 left join t_finance_expenseremburseitem item1
 on item1.fkrembursementid = t.fid
 left join t_finance_expenseremburseitem2 item2
 on item2.fkrembursementid = t.fid
 where 1 = 1
 and t.frembursementtype = 'LOAN_REPORT'
 and to_char(t.fcreatetime, 'yyyy') > '2017'
 group by t.fnumber
 order by t.fnumber asc)
 
 select 1
 from temp t
 left join t_finance_expenserembursement ment
 on t.fnumber = ment.fnumber
 left join t_finance_expenseremburseitem2 item
 on item.fkrembursementid = ment.fid
 where t.vloanSum != t.loanSum
 and item.fid = item2.fid);

4.4与update结合使用

update dest b
 set b.NAME =
 (with t as (select * from temp)
 select a.NAME from temp a where a.ID = b.ID)

其实就是把一大堆重复用到的SQL语句放在with as 里面,取一个别名,后面的查询就可以用它,这样对于大批量的SQL语句起到一个优化的作用,而且清楚明了。后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注一下
 



Tags:ORACLE   点击:()  评论:()
声明:本站部分内容来自互联网,如有任何版权侵犯或其他问题请与我们联系,我们将立即删除或处理。
▌相关评论
发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表
▌相关推荐
概述WITH AS短语,也叫做子查询部分(subquery factoring),可以做很多事情,定义一个SQL片断,该SQL片断会被整个SQL语句所用到。有的时候,是为了让SQL语句的可读性更高些,也有可能是在U...【详细内容】
2019-06-11 ORACLE  点击:(8)  评论:(0)  加入收藏
概述有朋友问了Oracle跟mysql的一些区别的地方,所以这里做个简单总结,大的架构就不讲了,这里主要从主键、索引、分页、组函数和单引号来说明下。 1、主键Mysql一般使用自动增长...【详细内容】
2019-05-07 Oracle,Mysql  点击:(14)  评论:(0)  加入收藏
推荐资讯
相关文章
栏目更新
栏目热门
'); })();