新闻资讯  快讯  焦点  财经  政策  社会
互 联 网   电商  金融  数据  计算  技巧
生活百科  科技  职场  健康  法律  汽车
手机百科  知识  软件  修理  测评  微信
软件技术  应用  系统  图像  视频  经验
硬件技术  知识  技术  测评  选购  维修
网络技术  硬件  软件  设置  安全  技术
程序开发  语言  移动  数据  开源  百科
安全防护  资讯  黑客  木马  病毒  移动
站长技术  搜索  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   点击:()  评论:()
声明:本站部分内容来自互联网,内容观点仅代表作者本人,如有任何版权侵犯请与我们联系,我们将立即删除。
▌相关评论
发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表
▌相关推荐
1.拉取oracle10g镜像文件docker pull vkanjilal/oracle10g2.创建挂载目录mkdir -p /data/oracle1og3.创建oracle容器docker run -d -p 1521:1521 -v /data/oracle10g:/data...【详细内容】
2019-09-12   ORACLE  点击:(0)  评论:(0)  加入收藏
1、for update nowait 与 for update 的目的: 锁定表的所有行,排斥其他针对这个表的写操作。确保只有当前事务对指定表进行写操作。例子:for update1 select * from TTable1...【详细内容】
2019-11-15   ORACLE  点击:(7)  评论:(0)  加入收藏
Oracle 基础知识(特别基础)1 如何确定数据库已经启动(1)查看进程Ps &ndash;ef |grep oracleoracle 39452856 1 0 May 23 - 11:59 ora_smon_GJ2oracle 40304850 1 0 May 23 - 3...【详细内容】
2019-11-13   ORACLE  点击:(7)  评论:(0)  加入收藏
概述今天主要介绍Oracle11g 网络访问控制列表--DBA_NETWORK_ACL。一、DBA_NETWORK_ACL_PRIVILEGESDBA_NETWORK_ACL_PRIVILEGES describes the network privileges defined i...【详细内容】
2019-11-07   ORACLE  点击:(14)  评论:(0)  加入收藏
由于SQL Server不常用,所以这里只针对MySQL数据库和Oracle数据库的区别(1) 对事务的提交MySQL默认是自动提交,而Oracle默认不自动提交,需要用户手动提交,需要在写commit;指令...【详细内容】
2019-11-06   ORACLE  点击:(12)  评论:(0)  加入收藏
近期项目需要从虚拟机环境迁移到容器环境,其中有一个项目在迁移到容器环境之后的两天之内出现了2次“死锁(deadlock)”的问题,部分关键日志如下:Found one Java-level deadlock...【详细内容】
2019-11-05   ORACLE  点击:(10)  评论:(0)  加入收藏
oracle按天,周,月,季度,年查询排序天--to_char(t.start_time,&#39;YYYY-MM-DD&#39;)周 --to_char(t.start_time,&#39;YYYY&#39;),to_char(t.start_time,&#39;IW&#39;)月度--to_ch...【详细内容】
2019-10-18   ORACLE  点击:(50)  评论:(0)  加入收藏
概述今天主要总结一下Oracle表空间每日增长和历史情况统计的一些脚本,仅供参考。11g统计表空间的每日增长量SELECT a.snap_id, c.tablespace_name ts_name, to_char(to_date(...【详细内容】
2019-10-14   ORACLE  点击:(54)  评论:(0)  加入收藏
概述Oracle在解析SQL语句的时候,如果在共享池中发现匹配的SQL语句,就可以避免掉解析的大部分开销。在共享池中找到匹配的SQL语句所对应的解析被称为软解析(soft parse)。如果...【详细内容】
2019-10-14   ORACLE  点击:(24)  评论:(0)  加入收藏
出处:墨天轮(https://www.modb.pro/db/7055,复制至浏览器,即可查看)本文为原创文章,如有转载,请标明出处从。导读:怀晓明先生(网名lastwinner),是具有多年数据库开发与项目管理经验的数...【详细内容】
2019-10-11   ORACLE  点击:(15)  评论:(0)  加入收藏
概述物理设计就是根据所选择的关系型数据库的特点对逻辑模型进行存储结构设计。它涉及的内容包含以下4方面:1. 定义数据库、表及字段的命名规范;2. 选择合适的存储引擎或者存...【详细内容】
2019-10-11   ORACLE  点击:(17)  评论:(0)  加入收藏
概述最近在归档某张大表时因需要重建约束,碰到了个ORA-02298类型的报错,这个问题还算比较常见的,这里整理一下一般的处理方法。 思路产生ORA-02298错误一般是你要插入的表A里,有...【详细内容】
2019-10-08   ORACLE  点击:(36)  评论:(0)  加入收藏
Oracle 12C中一个重要特性多租户特性被引入。12C之前,数据库实例(Oracle Instance)与数据库之间为多对一或一对一关系,若服务器上同时运行多个Oracle数据库,必须同时运行多个数据库实例,资源耗费非常大。...【详细内容】
2019-09-27   ORACLE  点击:(10)  评论:(0)  加入收藏
抽空总结一下Oracle 物化视图和物化视图日志的一些概念和相关命令,仅供参考。 一、相关概念物化视图是将查询预先定义在结构中,并手动或者定期刷新将结果存储在物化视图段中,也...【详细内容】
2019-09-26   ORACLE  点击:(88)  评论:(0)  加入收藏
create table student(name varchar2(30),gj varchar2(20),score number(4,1));insert into student (name, gj, score) values (&#39;李二&#39;, &#39;中国&#39;, 90);i...【详细内容】
2019-09-20   ORACLE  点击:(29)  评论:(0)  加入收藏
概述今天主要介绍几个sql,可能很多人就收藏一下但很少会去用,所以结合案例来做一下分析,这样就知道在什么场景用了。下面介绍一下案例。1、监控分析从监控发现物理IO读处于比...【详细内容】
2019-09-16   ORACLE  点击:(32)  评论:(0)  加入收藏
Procedure P_EmployeeAccount( Ri_Ret Out Int,Rv_Msg Out Varchar2) IsRec_Test_2017 Test_2017%Rowtype;type type_tmp is record(A Test_2016.A%type,B Test_2016.B%typ...【详细内容】
2019-09-11   ORACLE  点击:(18)  评论:(0)  加入收藏
一、MySQL 优点:体积小、速度快、总体拥有成本低,开源;支持多种操作系统;是开源数据库,提供的接口支持多种语言连接操作 ;MySQL的核心程序采用完全的多线程编程。线程是轻量级的进...【详细内容】
2019-08-29   ORACLE  点击:(45)  评论:(0)  加入收藏
概述在oracle8i以后提供了一个生成不重复的数据的一个函数sys_guid()一共32位,生成的依据主要是时间和机器码,具有世界唯一性,类似于java中的UUID(都是世界唯一的)。 SYS_GUIDSYS...【详细内容】
2019-08-29   ORACLE  点击:(88)  评论:(0)  加入收藏
概述在ORACLE数据库中,DBA_OBJECTS视图中OBJECT_TYPE为LOB的对象是什么东西呢?其实OBJECT_TYPE为LOB就是大对象(LOB),它指那些用来存储大量数据的数据库字段。Oracle 11gR2 文档:h...【详细内容】
2019-08-28   ORACLE  点击:(49)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条