您当前的位置:首页 > 电脑百科 > 数据库 > 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   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
安装环境Linux服务器:Centos 6 64位Oracle服务器:Oracle11gR2 64位 系统要求说明:内存必须高于1G的物理内存;交换空间,一般为内存的2倍(1G的内存可以设置swap 分区为3G大小);硬...【详细内容】
2021-12-27  Tags: ORACLE  点击:(2)  评论:(0)  加入收藏
1、 登陆CentOS 2、 查看备份脚本,脚本放在\usr\local目录下 3、 编写数据库备份脚本,内容如下 前提是需要在Oracle创建directory文件,如果没有,可以create创建并授权给相应的用...【详细内容】
2021-12-10  Tags: ORACLE  点击:(14)  评论:(0)  加入收藏
之前写了个Oracle12c在Redhat6.8下的静默安装,操作系统版本太老了而且没配插图,今天重新写个centos 7 下静默安装。 准备环境:centos 7 虚拟机一台,Oracle12c安装包。1、检查防...【详细内容】
2021-10-12  Tags: ORACLE  点击:(29)  评论:(0)  加入收藏
JDK 17 正式发布+免费牛逼啊,JDK 16 刚发布半年(2021/03/16),JDK 17 又如期而至(2021/09/14),这个时间点牛逼啊,蹭苹果发布会的热度?记得当年 JDK 15 的发布也是同天,巧了。。虽然 iPho...【详细内容】
2021-09-16  Tags: ORACLE  点击:(88)  评论:(0)  加入收藏
2021年免费vps服务器申请,oracle永久免费使用。Oracle 云免费套餐只需注册 Oracle 云帐户即可获取,不仅包含丰富的 Always Free 云服务,还提供 300 美元的免费试用储值,让您可以...【详细内容】
2021-09-08  Tags: ORACLE  点击:(109)  评论:(0)  加入收藏
Oracle日志文件是Oracle数据库存储信息的重要文件,主要用来存储数据库变化的操作信息。Oracle日志文件可以分为两种:重做日志文件(redo log file)、归档日志文件,其中重做日志文...【详细内容】
2021-08-19  Tags: ORACLE  点击:(101)  评论:(0)  加入收藏
本例子在真实环境应用过,获得了很好的体验,特将其分享出来供各位朋友使用。后续我会持续把相关的知识一点一点的更新,请各位多多关注,多多支持。测试速度:导出速度大概800M/s,如...【详细内容】
2021-08-17  Tags: ORACLE  点击:(88)  评论:(0)  加入收藏
天冒险重启下Oracle rac,分三个步骤,停实例---停集群---重启服务器。 1、关闭实例检查状态su - gridcrsctl status res -t 注意:只在一个节点执行就行,或者为了保险期间,使用shut...【详细内容】
2021-07-16  Tags: ORACLE  点击:(128)  评论:(0)  加入收藏
两个字符串相似度的比较:SYS.UTL_MATCH.edit_distance_similarity用法:select SYS.UTL_MATCH.edit_distance_similarity(&#39;河北沧州东塑股份有限公司&#39;,&#39;河北沧州东...【详细内容】
2021-07-07  Tags: ORACLE  点击:(112)  评论:(0)  加入收藏
现在有一个用户A,想把这个用户A下面的所有的表查询权限都赋予用户X,该怎么做?同样的问题,如果有4个用户A、B、C、D,需要把这4个用户下面所有的表的只读权限赋予用户X、Y、Z三个用...【详细内容】
2021-06-23  Tags: ORACLE  点击:(103)  评论:(0)  加入收藏
▌简易百科推荐
安装环境Linux服务器:Centos 6 64位Oracle服务器:Oracle11gR2 64位 系统要求说明:内存必须高于1G的物理内存;交换空间,一般为内存的2倍(1G的内存可以设置swap 分区为3G大小);硬...【详细内容】
2021-12-27  守护梦想2022    Tags:Oracle   点击:(2)  评论:(0)  加入收藏
1、 登陆CentOS 2、 查看备份脚本,脚本放在\usr\local目录下 3、 编写数据库备份脚本,内容如下 前提是需要在Oracle创建directory文件,如果没有,可以create创建并授权给相应的用...【详细内容】
2021-12-10  JasonTang    Tags:Oracle   点击:(14)  评论:(0)  加入收藏
之前写了个Oracle12c在Redhat6.8下的静默安装,操作系统版本太老了而且没配插图,今天重新写个centos 7 下静默安装。 准备环境:centos 7 虚拟机一台,Oracle12c安装包。1、检查防...【详细内容】
2021-10-12  it运维小土豆  今日头条  Tags:Oracle   点击:(29)  评论:(0)  加入收藏
JDK 17 正式发布+免费牛逼啊,JDK 16 刚发布半年(2021/03/16),JDK 17 又如期而至(2021/09/14),这个时间点牛逼啊,蹭苹果发布会的热度?记得当年 JDK 15 的发布也是同天,巧了。。虽然 iPho...【详细内容】
2021-09-16  Java技术栈  掘金  Tags:Oracle   点击:(88)  评论:(0)  加入收藏
Oracle日志文件是Oracle数据库存储信息的重要文件,主要用来存储数据库变化的操作信息。Oracle日志文件可以分为两种:重做日志文件(redo log file)、归档日志文件,其中重做日志文...【详细内容】
2021-08-19  free教程    Tags:Oracle日志   点击:(101)  评论:(0)  加入收藏
本例子在真实环境应用过,获得了很好的体验,特将其分享出来供各位朋友使用。后续我会持续把相关的知识一点一点的更新,请各位多多关注,多多支持。测试速度:导出速度大概800M/s,如...【详细内容】
2021-08-17  大树唛包    Tags:oracle   点击:(88)  评论:(0)  加入收藏
天冒险重启下Oracle rac,分三个步骤,停实例---停集群---重启服务器。 1、关闭实例检查状态su - gridcrsctl status res -t 注意:只在一个节点执行就行,或者为了保险期间,使用shut...【详细内容】
2021-07-16  死磕IT    Tags:Oracle rac   点击:(128)  评论:(0)  加入收藏
两个字符串相似度的比较:SYS.UTL_MATCH.edit_distance_similarity用法:select SYS.UTL_MATCH.edit_distance_similarity(&#39;河北沧州东塑股份有限公司&#39;,&#39;河北沧州东...【详细内容】
2021-07-07  Onceagain75783189    Tags:比较函数   点击:(112)  评论:(0)  加入收藏
现在有一个用户A,想把这个用户A下面的所有的表查询权限都赋予用户X,该怎么做?同样的问题,如果有4个用户A、B、C、D,需要把这4个用户下面所有的表的只读权限赋予用户X、Y、Z三个用...【详细内容】
2021-06-23  程序猿集锦    Tags:Oracle   点击:(103)  评论:(0)  加入收藏
oarcle数据库真正存放数据的是数据文件(data files),Oarcle表空间(tablespaces)实际上是一个逻辑的概念,他在物理上是并不存在的,那么把一组data files 捻在一起就成为一个表空间。...【详细内容】
2021-04-27  SQL小帅哥  今日头条  Tags:Oracle   点击:(200)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条