深入理解和使用Oracle中with as语句以及与增删改查的结合使用

最新推荐文章于 2023-11-02 11:19:58 发布

fhl12395

最新推荐文章于 2023-11-02 11:19:58 发布

阅读量1.3k

点赞数 1

原文链接：https://blog.csdn.net/baidu_37107022/article/details/79619809

版权

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 as 语法

单个语法：

 with
  tempName 
 as
  (
 select
  ....)

 select
  ...

多个语法：

 with
  tempName1 
 as
  (
 select
  ....)，

 tempName2 
 as
  (
 select
  ....)，

 tempName3 
 as
  (
 select
  ....) ...

 select
  ...

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

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

二、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

使用With as 的SQL为：

 with
  TT 
 as
 ( 
 --模拟生一个20行的数据
  
 SELECT
  
 LEVEL
  
 AS
  lv 
 FROM
  DUAL CONNECT 
 BY
  
 LEVEL
  
 <
  
 20
  ) 
 select
  lv 
 from
  TT
 WHERE
  lv 
 >
  
 10
  
 AND
  lv 
 <
  
 15

多个临时表实例：

  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;

三、WITH Clause方法的优点

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

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

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

四、WITH AS 与增删改查结合用法

注意：1.
with必须紧跟引用的select语句

2.with创建的临时表必须被引用，否则报错

4.1与select查询语句结合使用

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

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 *
  from verificationInfo v, loanInfo l
 where l.fnumber = v.fnumber
   and l.loanSum != v.vLoanSum;

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)

fhl12395

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
深入理解和使用Oracle中with as语句以及与增删改查的结合使用

WITH AS短语，也叫做子查询部分（subquery factoring），可以做很多事情，定义一个SQL片断，该SQL片断会被整个SQL语句所用到。有的时候，是为了让SQL语句的可读性更高些，也有可能是在UNION ALL的不同部分，作为提供数据的部分。特别对于UNION ALL比较有用。因为UNION ALL的每个部分可能相同，但是如果每个部分都去执行一遍的话，则成本太高，所以可以使用WITH A...
复制链接

扫一扫