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

本是古典何须时尚 2022-05-29 07:39 355阅读 0赞

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)